Công thức Excel: Tối đa nếu nhiều tiêu chí
Công thức Excel: Tối đa nếu nhiều tiêu chí

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à một 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 như sau phạm vi được đặt tên: “color” = B6: B14, “item” = C6: C14 và “price” = E6: E14. Mục đích 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ỉ có khi kết quả của hai mảng đầu tiên ở trên là TRUE cho các mục ở 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, công thức này 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

Các Hàm MAXIFS, được giới thiệu trong Excel 2016, được thiết kế để tính toán số 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 Làm lơ ô 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í.

https://exceljet.net/formula/maximum-if-multiple-criteria