Công thức chung
(=MAX(IF(rng1=criteria1,IF(rng2=criteria2,values))))
Tóm lược
Để nhận giá trị lớn nhất trong tập dữ liệu dựa trên nhiều tiêu chí, bạn có thể sử dụng công thức mảng dựa trên các hàm MAX và IF. Trong ví dụ được hiển thị, công thức trong I6 là:
(=MAX(IF(color=G6,IF(item=H6,price))))
Với màu "đỏ" và mục "mũ", kết quả là $ 11,00
Lưu ý: Đây là công thức mảng và phải được nhập bằng Ctrl + Shift + đã nhập
Giải trình
Ví dụ này sử dụng các phạm vi được đặt tên sau: "color" = B6: B14, "item" = C6: C14 và "price" = E6: E14. Mục tiêu là tìm giá tối đa cho một màu và mặt hàng nhất định.
Công thức này sử dụng hai hàm IF lồng nhau, được bao bọc bên trong MAX để trả về giá tối đa với hai tiêu chí. Bắt đầu với kiểm tra logic của câu lệnh IF đầu tiên, color = G6, các giá trị trong dải ô được đặt tên là "color" (B6: B14) được kiểm tra so với giá trị trong ô G6, "red". Kết quả là một mảng như thế này:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Trong kiểm tra logic cho câu lệnh IF thứ hai, mục = H6, các giá trị trong mục phạm vi được đặt tên (C6: C14) được kiểm tra so với giá trị trong ô H6, "hat". Kết quả là một mảng như thế này:
(TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE)
"Giá trị nếu đúng" cho câu lệnh IF thứ 2 là phạm vi được đặt tên "giá" (E6: E14), là một mảng như sau:
(11;8;9;12;9;10;9;8;7)
Giá được trả lại cho mỗi mặt hàng trong phạm vi này chỉ khi kết quả của hai mảng đầu tiên ở trên là TRUE cho các mặt hàng ở vị trí tương ứng. Trong ví dụ được hiển thị, mảng cuối cùng bên trong MAX trông giống như sau:
(11;8;9;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Lưu ý giá duy nhất "tồn tại" là giá ở vị trí có màu "đỏ" và mặt hàng là "mũ".
Sau đó, hàm MAX trả về giá cao nhất, tự động bỏ qua các giá trị FALSE.
Cú pháp thay thế sử dụng logic boolean
Bạn cũng có thể sử dụng công thức mảng sau, chỉ sử dụng một hàm IF cùng với logic boolean:
(=MAX(IF((color=G6)*(item=H6),price)))
Ưu điểm của cú pháp này là dễ dàng thêm các tiêu chí bổ sung mà không cần thêm các hàm IF lồng nhau bổ sung. Nếu bạn cần logic HOẶC, hãy sử dụng phép cộng thay vì phép nhân giữa các điều kiện.
Với MAXIFS
Hàm MAXIFS, được giới thiệu trong Excel 2016, được thiết kế để tính giá trị tối đa dựa trên một hoặc nhiều tiêu chí mà không cần công thức mảng. Với MAXIFS, công thức trong I6 là:
=MAXIFS(price,color,G6,item,H6)
Lưu ý: MAXIFS sẽ tự động bỏ qua các ô trống đáp ứng tiêu chí. Nói cách khác, MAXIFS sẽ không coi các ô trống đáp ứng tiêu chí là 0. Mặt khác, MAXIFS sẽ trả về không (0) nếu không có ô nào phù hợp với tiêu chí.
