Hôm nay hãy để tôi giới thiệu đến bạn cách sử dụng hàm Large để làm nổi bật 3 giá trị lớn nhất trong mỗi hàng trong Google Sheets.
Một thời gian trước đây, tôi đã giải thích cho bạn cách sử dụng hàm Max để định dạng điều kiện cho giá trị lớn nhất trong mỗi hàng.
Đây là liên kết, nếu bạn chưa xem – Cách làm nổi bật giá trị lớn nhất trong một hàng trong Google Sheets.
Với Large, chúng ta có thể làm nổi bật ‘n’ giá trị lớn nhất trong mỗi hàng. Tôi chỉ đặt là 3 trong tiêu đề thay vì ‘n’ để giải thích quy tắc làm nổi bật.
Dưới đây là một ví dụ về việc sử dụng hàm Large để trả về giá trị lớn nhất 1, 2, 3… từ một hàng.
| A | B | C | D |
|---|---|---|---|
Ở đây, công thức =large(A1:D1, 1) sẽ trả về 50, và =large(A1:D1, 2) sẽ trả về 45.
Bây giờ hãy xem cú pháp.
=large(data, n)
Để lấy giá trị lớn thứ 3 từ hàng số 1 ở trên, bây giờ bạn có thể biết phải thay đổi gì trong công thức. Nếu không, hãy xem công thức Large bên dưới.
=large(A1:D1, 3)
Theo tiêu đề, điều chúng ta muốn đạt được là tô màu các ô chứa 3 số lớn nhất trong mỗi hàng, đúng không?
Hãy để tôi giải thích điều đó theo từng bước dưới đây.
Công thức tùy chỉnh để làm nổi bật giá trị lớn nhất và 3 giá trị lớn nhất trong mỗi hàng – Google Sheets
Hãy xem các số được làm nổi bật trong mỗi hàng để hiểu những gì tôi đang nói.
Trong loại định dạng có điều kiện này, bạn sẽ gặp ba vấn đề. Vì vậy, hãy giải quyết những vấn đề đó theo hướng dẫn từng bước dưới đây.
Thường thì để làm nổi bật 3 giá trị lớn nhất trong mỗi hàng, bạn có thể sử dụng công thức sau trong sheets.
=B2:B>=large($B2:$K2,3)
Để áp dụng, hãy chọn phạm vi B2:K, sau đó đi đến Định dạng > Định dạng có điều kiện và áp dụng công thức trên vào trường công thức tùy chỉnh.
Hãy xem các cài đặt định dạng có điều kiện trên và một trong các vấn đề của công thức này dưới đây.
Vấn đề #1: Không có tô màu khi có ít hơn ba giá trị lớn nhất
Vui lòng tham khảo hình ảnh này để biết cài đặt định dạng.
Công thức có một vấn đề phản ánh trong hàng cuối cùng B5:K5.
Vấn đề đó là gì?
Phạm vi B5:K5 không bị tác động bởi việc làm nổi bật!
Vì chỉ có hai giá trị trong hàng đã nêu, hàm Large không trả về giá trị lớn thứ 3. Bạn có thể thử nó trên bảng của bạn.
=large(B5:K5,3)
Kết quả là nó chỉ trả về lỗi NUM!. Vì vậy, không có lý do nào để ngờ rằng định dạng có điều kiện sẽ hoạt động đúng trong hàng đó. Chúng ta có thể giải quyết vấn đề này như sau.
Vấn đề #2: Lỗi NUM! của hàm Large
Hãy điền các ô trống trong phạm vi B2:K5 bằng 0. Vì vậy, công thức ở trên sẽ trả về 0 làm giá trị lớn thứ ba thay vì lỗi NUM!.
Điều đó là hợp lý trong định dạng có điều kiện nhưng không thực tế trong một phạm vi ngày càng mở rộng. Trang tính sẽ trở nên xấu xí nếu bạn điền các số 0 trong tất cả các ô trống trong một phạm vi như B2:K.
Hãy để tôi giải thích cách giải quyết vấn đề này bằng cách sửa đổi công thức được sử dụng ở trên để làm nổi bật 3 giá trị lớn nhất trong mỗi hàng.
Để xử lý lỗi NUM! của hàm Large, ở đây chúng ta có thể bọc ‘data’ bằng hàm N như sau.
=B2:B>=large(arrayformula(n($B2:$K2)),3)
Tôi đã sửa đổi $B2:$K2 thành arrayformula(n($B2:$K2)). Hàm N được sử dụng không chỉ với một ô duy nhất mà còn với một phạm vi. Vì vậy, chúng ta không được quên sử dụng ArrayFormula cùng với nó.
Hãy xem kết quả bây giờ và sẵn sàng giải quyết một vấn đề mới!
Vấn đề #3: Làm nổi bật 3 giá trị lớn nhất trong mỗi hàng ảnh hưởng đến các hàng trống
Khi chúng ta giải quyết một vấn đề về định dạng có điều kiện, một vấn đề khác lại nổi lên. Hy vọng rằng đây là vấn đề cuối cùng.
Ai đó có thể đoán tại sao tất cả các hàng trống đều được làm nổi bật?
Đó là vì hàm N được sử dụng trong ví dụ trên làm ô trống được điền bằng 0.
Nếu tất cả các giá trị trong một hàng đều là 0 có nghĩa là ba giá trị lớn nhất đều là 0. Vì vậy, rõ ràng cả hàng đó đều được làm nổi bật.
Làm thế nào để giải quyết câu đố làm nổi bật này?
Chúng ta có thể sử dụng toán tử logic AND như sau.
Cú pháp:
AND (biểu_thức_logic1, [biểu_thức_logic2, …])
Trong đó, sử dụng công thức phía trên làm biểu_thức_logic2. Sử dụng len($B2:$K2) làm biểu_thức_logic1.
Vậy công thức cuối cùng để làm nổi bật 3 giá trị lớn nhất trong mỗi hàng trong Google Sheets sẽ như sau.
=and(len($B2:$K2),B2:B>=large(arrayformula(n($B2:$K2)),3))
Tài nguyên:
- Định dạng có điều kiện cho hai giá trị lớn nhất dựa trên tiêu chí trong Google Sheets
- AND, OR, hoặc NOT trong định dạng có điều kiện trong Google Sheets
- Làm nổi bật toàn bộ hàng trong định dạng có điều kiện trong Google Sheets
- Vai trò của hàm Indirect trong định dạng có điều kiện trong Google Sheets
- Cách làm nổi bật mỗi hàng hoặc cột thứ N trong Google Sheets
- Làm nổi bật những giá trị trùng nhau hoặc khác nhau trong hai danh sách trong Google Sheets
- Làm nổi bật giá trị giao nhau trong Google Sheets trong Two-way Lookup
- Google Sheets – Làm nổi bật giá trị lớn nhất trong mỗi nhóm
- Làm nổi bật giá trị Min khác 0 và ô trống trong Google Sheets
