Trong bài viết này, chúng ta sẽ tìm hiểu cách tô sắc vẽ giá trị lớn nhất hoặc Max trong mỗi hàng trong Google Sheets mà không tô sắc những giá trị trùng lặp. Điều này sẽ giúp bạn tạo ra các báo cáo hấp dẫn và dễ đọc.
Ví dụ để hiểu vấn đề (Tô sắc vẽ)
Hãy xem qua các giá trị trong khoảng B2:H2 trong bảng 1.
Bảng 1:
B | C | D | E | F | G | H
2
Trong đó, giá trị lớn nhất hoặc Max là 319. Vì vậy không có vấn đề gì trong việc tô sắc.
Hãy chọn khoảng B2:H2 và áp dụng công thức LARGE dưới đây để tô sắc giá trị Max hoặc lớn nhất.
=B2=LARGE($B2:$H2,1)
Đây là một phương pháp thay thế cho LARGE 1.
=B2=MAX($B2:$H2)
Nhưng giá trị lớn thứ hai (LARGE 2) là 318 và lặp lại ở các ô E2 và G2.
Vì vậy, thông thường, công thức sau sẽ tô sắc cả hai ô này nếu bạn tô sắc giá trị LARGE 2 (Max 2).
=B2=LARGE($B2:$H2,2)
Tuy nhiên, tôi chỉ muốn tô sắc ô E2 là ô xuất hiện đầu tiên của giá trị lớn thứ hai trong hàng đó.
Tương tự, nếu giá trị lớn thứ ba cũng lặp lại, tôi chỉ muốn tô sắc ô lặp lại đó. Xem hàng 3 của bảng trên.
Bảng 2:
B | C | D | E | F | G | H
3
Công thức sau (công thức sử dụng thông thường hoặc tiêu chuẩn) sẽ tô sắc cả ô D3 và E3. Tôi chỉ muốn tô sắc ô D3 (xuất hiện đầu tiên).
=B2=LARGE($B2:$H2,3)
Trong bài viết hướng dẫn Google Sheets này, bạn sẽ được tìm hiểu 3 quy tắc (công thức định dạng có điều kiện) để tô sắc giá trị Max 1, Max 2 và Max 3 trong mỗi hàng mà không tô sắc những giá trị trùng lặp.
Nếu bạn sử dụng cả 3 quy tắc, sẽ có điều gì đó mà tôi muốn làm rõ hơn. Đây là điều đó.
Giá trị Max không trùng lặp – Những điều cần biết
Nếu tôi muốn tô sắc giá trị Max 1, Max 2 và Max 3, theo hàng 3 (bảng thứ hai), các ô cần được tô sắc là B3, C3 và D3. Trong hàng 2 (bảng thứ nhất), nó sẽ là B2, E2, G2.
Điều đó có nghĩa là;
Khi tôi nói, tô sắc giá trị lớn nhất mà không trùng lặp, điều đó có nghĩa là, không trùng lặp của Max 1, Max 2 và Max 3 cá nhân.
Nếu bạn chỉ muốn tô sắc Max 2, nếu Max 1 và Max 2 có cùng giá trị, ô thứ hai chứa giá trị sẽ được tô sắc và giá trị thứ ba của cùng giá trị sẽ bị bỏ qua.
Điều đó có nghĩa nếu bạn muốn sử dụng ba quy tắc để tô sắc Max 1, Max 2 và Max 3 mà không có những giá trị trùng lặp, thì quy tắc tô sắc sẽ giới hạn trong 3 ô trong hàng đó. Các giá trị có thể là trùng lặp hoặc không.
Xin xem hình ảnh dưới đây để làm rõ hơn.
Các bước để tô sắc giá trị Max không trùng lặp trong Google Sheets
Để tô sắc giá trị Max không trùng lặp như trên, chúng ta có thể áp dụng phương pháp khớp địa chỉ ô. Chúng ta sẽ khớp địa chỉ ô của khoảng B2:H2 với địa chỉ ô giá trị Max hoặc lớn nhất được yêu cầu.
Bạn có thể tìm hiểu cách làm điều đó trong 5 bước dưới đây. Trong bước thứ 5, bạn sẽ nhận được các công thức để tô sắc giá trị Max không trùng lặp trong Google Sheets.
Tìm giá trị Max | Large (Bước 1)
Ghi chú: Để biết dữ liệu mẫu, vui lòng tham khảo hình ảnh ở trên. Khoảng là B2:H. Chỉ giữ lại những hàng mà bạn cần cho dự án của mình để cải thiện hiệu suất.
Vì chúng ta sẽ sử dụng công thức trong định dạng có điều kiện, chúng ta chỉ muốn viết công thức cho hàng đầu tiên (B2:H2).
Khi áp dụng công thức, chỉ cần chọn khoảng B2:H trong “Áp dụng cho khoảng” trong bảng định dạng có điều kiện để áp dụng cùng một quy tắc cho các hàng tiếp theo.
Công thức Bước 1:
=LARGE($B2:$H2,1)
Nó sẽ trả về 319, đó là giá trị Large 1 (Max) trong khoảng B2:H2.
Trích xuất địa chỉ ô từ hàng (Bước 2)
Như tôi đã giải thích, để tô sắc giá trị Max không trùng lặp, chúng tôi sẽ áp dụng khớp địa chỉ ô.
Chúng ta có thể sử dụng hàm ADDRESS như sau (dưới dạng công thức mảng) cho việc này.
Công thức Bước 2:
=ArrayFormula(address(row(),column($B$2:$H$2)))
Trong công thức trên, tôi không chỉ định bất kỳ địa chỉ ô nào trong hàm ROW vì tôi muốn công thức trên trả về địa chỉ ô của hàng mà nó nhập.
Nếu bạn chèn công thức trên ô K2, nó sẽ trả về các địa chỉ ô dưới đây trong K2:Q2.
Nếu nó ở vị trí K4, nó sẽ là;
Vì vậy, công thức cho khoảng B2:H2 sẽ đủ cho một khoảng mở như B2:H trong việc tô sắc giá trị Max không trùng lặp trong Google Sheets.
Lọc ID ô của ô Max hoặc các ô (Bước 3)
Để lọc địa chỉ ô giá trị Large 1 hoặc Max 1 trong một hàng, chúng ta có thể sử dụng công thức FILTER chung sau (theo ví dụ của tôi).
=FILTER( Step_2_Formula, $B2:$H2=Step_1_Formula:, len($B2:$H2) )
Vì vậy, công thức sẽ là;
Công thức Bước 3:
=FILTER( address(row(),column($B$2:$H$2)), $B2:$H2=LARGE($B2:$H2,1), len($B2:$H2) )
Lưu ý: Tôi đã loại bỏ hàm ArrayFormula khỏi công thức Step_2 ở đây vì nó không cần thiết khi sử dụng FILTER.
Công thức sẽ trả về $B$2 trong hàng 2, $B$3 (nếu bạn kéo công thức từ hàng 2 đến hàng 3) trong hàng 3 …. $B$7 | $C$7 | $D$7 | $E$7 | $F$7 trong hàng 7 …
Hình ảnh trên chỉ để giải thích. Bạn không cần phải nhập công thức như trên ô J2 và sao chép xuống.
Để tô sắc giá trị Max không trùng lặp và cũng theo hàng, chúng ta chỉ muốn sử dụng công thức step_5_formula của chúng ta trong định dạng có điều kiện. Bạn sẽ nhận được nó sau một bước nữa.
Index để trích xuất đúng Địa chỉ ô theo Giá trị Max thứ N để tô sắc (Bước 4)
Trên hình ảnh trên, bạn có thể thấy rằng có nhiều giá trị Max 1 (trùng) trong hàng 7. Vì vậy nhiều địa chỉ ô trong kết quả Lọc.
Chúng ta phải đảm bảo rằng chúng ta chỉ sử dụng một địa chỉ ô duy nhất trong định dạng có điều kiện. Index là tốt nhất cho việc này.
Liên quan: Chọn chỉ cột cần thiết từ kết quả mảng trong Google Sheets.
Index cho Max 1 (Large 1):
Sử dụng INDEX, chúng ta có thể trích xuất địa chỉ ô đầu tiên từ kết quả bước_3.
Large_1_Formula:
=index( FILTER( address(row(),column($B$2:$H$2)), $B2:$H2=LARGE($B2:$H2,1), len($B2:$H2) ), 0,1 )
Số 0 ở phần cuối cùng có nghĩa là tất cả các hàng và 1 có nghĩa là 1 cột.
Index cho Max 2 (Large 2):
Nếu bạn muốn tô sắc giá trị Large 2 (Max 2) mà không có giá trị trùng lặp, trong công thức trên, hãy sử dụng LARGE như dưới đây.
LARGE($B2:$H2,2)
Hơn nữa, bạn phải thay đổi đối số cột trong Index từ 1 thành công thức IF dưới đây.
if(large($B2:$H2,2)=large($B2:$H2,1),2,1)
Nghĩa là nếu Max 2 bằng Max 1, trả lại cột thứ hai từ địa chỉ ô, nếu không trả lại cột đầu tiên từ địa chỉ ô.
Large_2_Formula:
=index( FILTER( address(row(),column($B$2:$H$2)), $B2:$H2=LARGE($B2:$H2,2), len($B2:$H2) ), 0,if(large($B2:$H2,2)=large($B2:$H2,1),2,1) )
Index cho Max 3 (Large 3):
Tương tự để tô sắc Max 3 bỏ qua các giá trị trùng lặp, sử dụng công thức Large dưới đây và công thức IF logic sau đó.
LARGE($B2:$H2,3)
IF Logical (Index Column Argument):
=IF( large($B2:$H2,3)=large($B2:$H2,1),3, if(large($B2:$H2,3)=large($B2:$H2,2),2,1 ) )
Nếu Max 3 bằng Max 1, công thức này sẽ trả về 3, nếu Max 3 bằng Max 2, nó sẽ trả về 2, nếu không trả về 1.
Con số (3, 2 hoặc 1) được trả về bởi công thức này là cột để trích xuất trong Index.
Large_3_Formula:
=index( FILTER( address(row(),column($B$2:$H$2)), $B2:$H2=LARGE($B2:$H2,3), len($B2:$H2) ), 0,IF( large($B2:$H2,3)=large($B2:$H2,1),3, if(large($B2:$H2,3)=large($B2:$H2,2),2,1 ) ) )
Quy tắc định dạng để tô sắc giá trị Max | Large không trùng lặp (Bước 5)
Chúng ta đã sẵn sàng áp dụng quy tắc tô sắc để tô sắc giá trị Max 1, Max 2 và Max 3 không trùng lặp trong Google Sheets.
Chúng ta có 3 công thức. Chúng là Large_1_Formula, Large_2_Formula và Large_3_Formula. Để áp dụng các quy tắc, hãy điều hướng đến Định dạng (menu) > Định dạng có điều kiện.
Trên bảng “Quy tắc định dạng có điều kiện” ở bên phải, nhập B2:H vào “Áp dụng cho khoảng”. “Quy tắc định dạng” phải là “Công thức tùy chỉnh”.
Bây giờ, trong trường được cung cấp (sẽ trống), sao chép-dán công thức Large_1_Formula theo công thức chung dưới đây.
=cell(“address”,B2)=Large_1_Formula
Đặt màu nền fill màu xanh lá cây và lưu lại. Tương tự, thêm hai quy tắc khác.
Fill màu: Xanh dương.
=cell(“address”,B2)=Large_2_Formula
Fill màu: Đỏ.
=cell(“address”,B2)=Large_3_Formula
Lưu ý: Thay thế Large_1_Formula, Large_2_Formula và Large_3_Formula bằng các công thức tương ứng.
Tôi hy vọng bạn hiểu cách tô sắc giá trị Max không trùng lặp trong hàng đó trong Google Sheets. Nếu không, hãy tạo một bản sao của bảng ví dụ từ liên kết bên dưới và xem các công thức.
Cảm ơn đã đọc. Chúc bạn thành công!
