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))
Trong đó "màu" là phạm vi được đặt tên là B5: B12 và "lượng" là phạm vi được đặt tên C5: C12.
Giải trình
Bên trong hàm AND có hai tiêu chí logic. Đầ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 hàm SMALL với giá trị ak là 3 và SMALL trả về giá trị "nhỏ nhất thứ 3", 300. Logic cho phép thử logic thứ hai giảm xuống:
$C3<=300
Khi cả hai điều kiện lôgic đề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à công thức mảng, nhưng không yêu cầu control + shift + enter.
