Công thức Excel: Đánh dấu 3 giá trị nhỏ nhất với tiêu chí
Công thức Excel: Đánh dấu 3 giá trị nhỏ nhất với tiêu chí

Công thức chung
=AND(A1=criteria,B1<=SMALL(IF(criteria,values),3))
Tóm lược

Để đánh dấu 3 giá trị nhỏ nhất đáp ứng các tiêu chí cụ thể, bạn có thể sử dụng công thức mảng dựa trên các hàm AND và SMALL. Trong ví dụ được hiển thị, công thức được sử dụng để định dạng có điều kiện là:

=AND($B5=$E$5,$C5<=SMALL(IF(color=$E$5,amount),3))

Trường hợp “màu sắc” là phạm vi được đặt tên B5: B12 và “số lượng” là phạm vi được đặt tên là C5: C12.

Giải trình

Bên trong Hàm AND có hai tiêu chí hợp lý. Đầu tiên là đơn giản và đảm bảo rằng chỉ các ô phù hợp với màu trong E5 mới được đánh dấu:

$B3=$E$5

Thử nghiệm thứ hai phức tạp hơn:

$C3<=SMALL(IF(color=$E$5,amount),3)

Ở đây, chúng tôi lọc số lượng để đảm bảo rằng chỉ các giá trị được liên kết với màu trong E5 (xanh lam) được giữ lại. Quá trình lọc được thực hiện với hàm IF như sau:

IF(color=$E$5,amount)

Mảng kết quả trông như thế này:

{FALSE;100;FALSE;200;FALSE;300;FALSE;400;FALSE;500}

Lưu ý rằng giá trị từ cột số tiền chỉ tồn tại nếu màu là “xanh lam”. Các số tiền khác bây giờ là FALSE.

Tiếp theo, mảng này đi vào Chức năng NHỎ với giá trị ak là 3 và SMALL trả về giá trị “nhỏ nhất thứ 3”, 300. Logic của bài kiểm tra logic thứ hai giảm xuống:

$C3<=300

Khi cả hai điều kiện logic đều trả về TRUE, định dạng có điều kiện sẽ được kích hoạt và các ô được đánh dấu.

Lưu ý: đây là một công thức mảng, nhưng không làm yêu cầu điều khiển + ca + nhập.

https://exceljet.net/formula/highlight-3-smallest-values-with-criteria