3 cách tìm MIN/MAX theo 1 hoặc nhiều điều kiện

vào

 MIN/MAX theo điều kiện : Tìm giá trị lớn nhất/nhỏ nhất của 1 cột theo 1 hoặc nhiều điều điện.

Ví dụ: 

  • Gía bán thấp nhất của  sản phẩm A.
  • Gía bán thấp nhất của sản phẩm A theo Khách hàng B
  • Ngày hoàn thành mẫu Item X sớm nhất
  • Ngày giao hàng sớm nhất của sản phẩm Y mà nhà máy Z có thể.

Có 3 cách:

Cách 1. Đối với Excel phiên bản 2019 hoặc Office 365:Dùng hàm MINIFS

=MINIFS(cột cần tìm MIN, Cột chứa điều kiện 1, Điều kiện 1, Cột chứa điều kiện 2, Điều kiện 2, ...)


=MINIFS($S$3:$S$3001,$D$3:$D$3001,$Z3,$B$3:$B$3001,Y3)
Bảng trên yêu cầu tìm Total cost nhỏ nhất theo District và Service.

**Tìm giá trị lớn nhất là MAXIFS có cách dùng tương tự.

Cách 2. Đối với Excel các phiên bản cũ:Dùng công thức mảng {MIN/MAX(If())}


={MIN(IF( Cột chứa điều kiện 1=Điều kiện 1, cột cần tìm MIN)} 
=>Ctrl+Shift+Enter

Như công thức ở trên thì chỉ dùng được với 1 cột điều kiện , nếu yêu cầu từ 2 điều kiện trở lên, ta có thể thêm 1 cột bên bảng data. (Không dùng được If(and() hay If(or() đâu nhé )

Trong bảng dưới, mình đã thêm cột W -Cột chứa điều kiện dò ,với giá trị là cột D&cột B. 

Cách 3: Dùng Vlookup hoặc Index+Match kết hợp PivotTable

  • Bản chất của các hàm dò tìm là chúng sẽ dò từ đầu range đến khi nào đụng trúng kết quả thỏa mãn đầu tiên thì sẽ lấy kết quả đó. Cá nhân mình thì ưu tiên cách này hơn vì hai cách trên load kết quả khá chậm (Hoặc máy mình cùi)
  • PivotTable có chức năng Sort từ Min->Max hoặc Old->New. Nên Min hoặc Max của cột theo điều kiện sẽ nằm đầu tiên. 
  • Khi dùng cách này để tìm Ngày sớm nhất/cũ nhất, hãy lưu ý tới những ngày Blank hoặc 1900, xử lý các ngày Blank trong bảng Data gốc trước khi tạo Pivot Table nhé. Nếu bảng Data lớn, làm thêm bước copy+paste Value bảng Pivot đã tạo qua 1 file khác . Copy và Paste Value có vẻ phiền nhưng đảm bảo an toàn , nhanh và tiện lợi. 
  • Nếu bạn thường phải làm báo cáo Min/Max với 1 bảng data hàng ngày/tuần/tháng, bạn nên tạo sẵn Template+Macro để khi có data mới thì bảng sẽ tự động Refresh/Copy+Paste Value
Cách làm:
Nếu chỉ cần Min/Max 1 điều kiện thì tạo Pivot Table là đủ, nhưng nếu làm nhiều điều kiện thì cần tạo thêm 1 cột Hỗ trợ giá trị của 2 cột điều kiện đó, như ví dụ dưới là cột AF. Lưu ý, PitvotTable có Repeat All Items Labels nhé.
  • Với Vlookup, nên tạo cột ở Phía bên trái của bảng


  • Với Index+Match hoặc Vlookup+Choose, ta tạo cột Hỗ trợ (AJ)ở đâu cũng được.














Nhận xét

Đăng nhận xét