Bạn có muốn làm cho bảng tính Google Sheets trở nên sinh động hơn bằng cách sử dụng màu sắc xen kẽ cho các nhóm dòng?
Đúng vậy, bạn có thể áp dụng định dạng có điều kiện để sử dụng màu sắc xen kẽ cho các nhóm dòng trong Google Sheets. Tuy nhiên, điều này cũng gặp một vấn đề liên quan đến việc lọc bộ lọc và ẩn các dòng.
Trong bài viết này, tôi sẽ chia sẻ với bạn một workaround, hay nghĩa là một giải pháp tạm thời, cho vấn đề phức tạp này bằng cách sử dụng một tab trợ giúp và vài cột trợ giúp.
Cách giải quyết vấn đề bộ lọc liên quan đến màu sắc xen kẽ cho các nhóm trong Google Sheets
Bước 1: Tạo danh sách trong cột A
Trước tiên, hãy tạo danh sách của bạn trong cột A trên ‘Sheet1’. Bạn có thể sử dụng các giá trị trên để thử nghiệm, nhưng đừng lo, các giải thích của tôi dưới đây sẽ dựa trên giá trị đó.
Bước 2: Tạo các cột trợ giúp
Đặt nhãn ô E1 là “Trợ giúp 1” và nhãn ô F1 là “Trợ giúp 2”.
Bước 3: Áp dụng công thức cho ô E2
Trong ô E2, sao chép và dán công thức sau đây và kéo xuống cho đến hàng cuối cùng chứa giá trị trong cột A của bạn (theo ví dụ của tôi, kéo xuống đến E12).
=subtotal(103,A2)Ghi chú: Tôi sẽ giải thích công thức này và các công thức tiếp theo ở đầu mục tiêu trong bài viết này.
Bước 4: Thêm Tab trợ giúp
Thêm một tab mới và đặt tên cho nó là ‘Helper Tab’.
Bước 5: Điền vào công thức bộ lọc
Trên ‘Helper Tab’, tại ô A1, nhập công thức bộ lọc sau đây:
=filter({row(Sheet1!A1:A),Sheet1!A1:A},Sheet1!E1:E=1)Công thức này sẽ tự động điền vào một số giá trị trong cột A và B trên ‘Helper Tab’
Bước 6: Điền vào công thức cho cột C trên ‘Helper Tab’
Trong ô C1 cùng tab, điền công thức sau:
=ArrayFormula(IFNA(match(B1:B,unique(B1:B),0)))Bước 7: Sử dụng Vlookup trên Sheet1
Quay trở lại ‘Sheet1’ và nhập công thức Vlookup sau vào ô F2:
=ArrayFormula(IFNA(vlookup(row(A2:A),'Helper Tab'!A1:C,3,0)))Bước 8: Áp dụng định dạng có điều kiện
Trên ‘Sheet1’, chọn phạm vi A2:A. Sau đó, điều hướng đến Định dạng có điều kiện (trong menu Format). Trong đó, nhập các quy tắc công thức tùy chỉnh dưới đây trong trường công thức tùy chỉnh:
Rule 1:
=and(isodd($F2),not(isblank($F2)))
Rule 2:
=and(iseven($F2),not(isblank($F2)))Đừng quên chọn hai màu khác nhau cho hai quy tắc trên.
Sau khi hoàn thành tất cả các bước trên, bạn có thể ẩn các hàng và bộ lọc mà không ảnh hưởng đến màu sắc xen kẽ cho các nhóm trong Google Sheets.
Mục đích của các cột và tab trợ giúp
Hãy bắt đầu với cột trợ giúp E trên ‘Sheet1’, tiếp theo là ‘Helper Tab’ và sau đó là cột trợ giúp F trên ‘Sheet1’.
Subtotal như Counta
Công thức Subtotal sử dụng hàm số 103 trong ô E2 tương đương với Counta. Tuy nhiên, có một điểm khác biệt. Công thức này đếm ô A2 nếu hàng đó hiển thị.
Vì chúng ta đã sao chép công thức này sang E3:E12, các công thức sẽ trả về 1 trong tất cả các hàng trừ các hàng bị ẩn (nếu có).
Nếu có bất kỳ hàng ẩn nào trong phạm vi, giá trị tương ứng trong cột E sẽ là 0.
Chúng ta sẽ sử dụng cột này làm cột tiêu chí trong công thức Bộ lọc trên ‘Helper Tab’.
Công thức Bộ lọc trên ‘Helper Tab’
Ô A1 trên ‘Helper Tab’ có một công thức Bộ lọc lọc các mục hiển thị từ danh sách trong cột A của ‘Sheet1’. Bên cạnh danh sách, công thức trả về các số hàng tương ứng. Số hàng này sẽ hữu ích sau này.
Tiêu chí trong Bộ lọc là Sheet1!E1:E = 1, nghĩa là nếu hàng không bị ẩn.
Công thức Tab trợ giúp 2 – Công thức mảng cho màu sắc xen kẽ cho các nhóm
Trên cùng ‘Helper Tab’, có một công thức khác ở ô C1. Công thức này có thể được sử dụng để áp dụng định dạng có điều kiện cho màu sắc xen kẽ cho các nhóm bằng cách sử dụng các hàm ISODD/ISEVEN, nhưng chúng ta không muốn có bất kỳ đánh dấu nào trên tab trợ giúp này.
Công thức mảng trong ô C1 gán số thứ tự tuần tự dựa trên các nhóm.
Nếu bạn hỏi tôi làm thế nào, đây là một hướng dẫn chi tiết – Gán cùng số thứ tự tuần tự cho các mục trùng lặp trong danh sách Google Sheets. Chỉ nhấp vào liên kết nếu bạn có đủ thời gian rảnh ngay bây giờ.
Nếu bạn muốn, bạn có thể áp dụng màu sắc xen kẽ cho các nhóm trong cột B trên ‘Helper Tab’.
Làm thế nào?
Vì các số trong cột C là tuần tự, các số này tạo ra một mẫu lạ lẫm như: odd, even, odd, even… cho các nhóm.
Chúng ta có thể áp dụng màu cho tất cả các hàng chứa số lẻ bằng một màu và số chẵn bằng một màu khác.
Tab trợ giúp này không cần bất kỳ đánh dấu nào. Vậy làm thế nào?
Vlookup trên ‘Sheet1’ – Gán số thứ tự tuần tự cho các hàng hiển thị
Công thức Vlookup trong ô F2 (trên ‘Sheet1’) sử dụng các số hàng làm khóa tìm kiếm và khớp các số hàng tương tự trong cột A trên ‘Helper Tab’.
Nếu khớp, nó sẽ trả về các số thứ tự tuần tự từ cột C trên ‘Helper Tab’. Bằng cách này, luôn có các số thứ tự tuần tự chính xác (không bị đứt) trong cột F trên ‘Sheet1’.
Điều đó có nghĩa là nếu bạn ẩn bất kỳ hàng nào, điều đó sẽ không làm gián đoạn chuỗi số trong cột F. Bởi vì ‘Helper Tab’ không chứa hàng đó.
Các quy tắc định dạng có điều kiện ISODD và ISEVEN của chúng tôi, cụ thể là màu sắc xen kẽ cho các nhóm, dựa trên cột F này. Vì vậy, bộ lọc sẽ không ảnh hưởng đến việc sử dụng màu sắc này.
Như vậy, bạn đã biết cách sử dụng màu sắc xen kẽ cho các nhóm mà không gặp vấn đề với bộ lọc trong Google Sheets.
Hãy thử ngay!
