Hướng dẫn dùng hàm MAXIFS hoặc AGGREGATE để tìm giá trị lớn nhất có điều kiện và đối tượng tương ứng với giá trị đó

Trong thực tế, có rất nhiều bài toán quản trị cần tìm giá trị lớn nhất thỏa mãn một hoặc nhiều điều kiện, và tìm ra đối tượng tương ứng với giá trị đó. Ví dụ như:

- Tìm ra đơn hàng có doanh số lớn nhất và của khách hàng nào, vào thời gian nào

- Tìm ra giá bán cao nhất của mặt hàng nào, thời gian bán hàng tương ứng

- Tìm ra chi phí lớn nhất là bao nhiêu và của loại chi phí nào

-....

(Hình ảnh dữ liệu phục vụ cho các ví dụ minh họa bên dưới)

HƯỚNG DẪN TÌM GIÁ TRỊ LỚN NHẤT CÓ ĐIỀU KIỆN

Tình huống 1: Tìm ra doanh số lớn nhất của KH004

Công thức dành cho mọi bản Office:

=MAX(IF(B4:B15="KH004",C4:C15))

=> Nhấn Ctrl+Shift+Enter để chạy công thức. Với Office 365 thì nhấn phím Enter bình thường

Công thức dành cho Office 2010 trở lên:

=AGGREGATE(14,6,C4:C15/(B4:B15="KH004"),1)

Công thức dành cho Office 2016 trở lên

=MAXIFS(C4:C15,B4:B15,"KH004")

Tình huống 2: Tìm ra doanh số lớn nhất của KH004 trước ngày 15/01/2021

Công thức cho mọi bản Office:

=MAX(IF((B4:B15="KH004")*(A4:A15<=Date(2021,1,15)),C4:C15))

=> Nhấn Ctrl+Shift+Enter để chạy công thức. Với Office 365 thì nhấn phím Enter bình thường

Công thức dành cho Office 2010 trở lên: 

=AGGREGATE(14,6,C4:C15/(B4:B15="KH004")/(A4:A15<=Date(2021,1,15)),1)

Công thức dành cho Office 2016 trở lên:

=MAXIFS(C4:C15,B4:B15,"KH004",A4:A15,"<="&Date(2021,1,15))

Tình huống 3: Tìm ra khách hàng có doanh số lớn nhất

=LOOKUP(2,1/(C4:C15=MAX(C4:C15)),B4:B15)

Trường hợp có nhiều hơn 1 khách hàng có doanh số lớn nhất thì phải dùng hàm TEXTJOIN để liệt kê ra danh sách khách hàng có doanh số lớn nhất. Hàm TEXTJOIN có trong Office 2019 trở lên và Office 365

==TEXTJOIN(",",TRUE,IF(C4:C15=MAX(C4:C15),B4:B15,""))

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp
{Đt+Zalo} - 038 696 1334