Trưởng phòng kinh doanh thường hỏi ai bán cao nhất tháng này, còn bộ phận mua hàng cần biết nhà cung cấp nào báo giá thấp nhất. Bài viết này hướng dẫn bạn dùng hàm MAX MIN trong Excel để trả lời những câu hỏi đó, từ cả bảng tới từng khu vực, từng mặt hàng.
Hàm MAX trả về số lớn nhất, còn hàm MIN trả về số nhỏ nhất trong một vùng. Với bảng doanh số ở cột C, bạn gõ =MAX(C2:C11) vào ô F2 và nhận 236,000,000, gõ =MIN(C2:C11) vào ô F3 và nhận 87,900,000. Khi cần số lớn nhất của riêng khu vực Bắc, bạn gõ =MAXIFS($C$2:$C$11,$B$2:$B$11,E8) vào ô F8, với ô E8 chứa chữ "Bắc", kết quả là 205,300,000. Hàm MINIFS làm việc tương tự cho số nhỏ nhất.
Ảnh trong bài được chụp trên Excel 365 giao diện tiếng Anh. Các ô màu vàng nhạt là nơi bạn gõ công thức. Excel 365 dùng trong bài cài dấu phẩy ngăn cách hàng nghìn. Nếu Excel của bạn báo lỗi khi gõ công thức có dấu phẩy, bạn thay dấu phẩy ngăn tham số bằng dấu chấm phẩy, ví dụ =MAXIFS($C$2:$C$11;$B$2:$B$11;E8).
Hàm MAX và MIN trong Excel có cú pháp thế nào?
=MAX(number1, [number2], ...)
=MIN(number1, [number2], ...)
Tham số | Ý nghĩa |
|---|---|
number1 | Tham số này là vùng ô, ô hoặc số cần so sánh. Bạn bắt buộc điền tham số này. |
number2, ... | Các tham số này là vùng ô hoặc số thêm vào. Bạn bỏ trống được. |
Mỗi hàm nhận tối đa 255 tham số. Bạn có thể đưa vào nhiều vùng rời nhau, mỗi vùng cách nhau bằng dấu phẩy.
Bạn tìm doanh số cao nhất và thấp nhất bằng MAX, MIN như thế nào?
Bảng ví dụ ở sheet Doanh số có tên nhân viên ở cột A, khu vực ở cột B và doanh số tháng 10 ở cột C, từ dòng 2 đến dòng 11. Ô C6 để trống vì nhân viên Hoàng Minh Đức mới vào làm. Ô C8 ghi chữ "Nghỉ phép".
Bạn chọn ô F2, gõ công thức sau rồi nhấn Enter:
=MAX(C2:C11)

Tiếp theo, bạn chọn ô F3, gõ công thức sau rồi nhấn Enter:
=MIN(C2:C11)


Ô F3 trả về 87,900,000 chứ không trả về 0, dù ô C6 để trống. Hàm MIN bỏ qua ô trống và ô chữ "Nghỉ phép", nên bạn không cần xóa các ô đó trước khi tính.
Hàm MAX, MIN xử lý ô trống, ô chữ và số 0 như thế nào?
Bảng sau tóm tắt cách 2 hàm đối xử với từng loại dữ liệu. Bạn có thể tự thử 3 trường hợp số 0, số lưu dạng chữ và ô báo lỗi bằng cách sửa ô C6, rồi xem lại ô F2 và F3. Thử xong, bạn xóa ô C6 cho trống lại, vì các mục sau vẫn dùng ô C6 trống.
Dữ liệu trong vùng | Hàm MAX, MIN làm gì | Ví dụ |
|---|---|---|
Ô trống | Hàm bỏ qua ô trống. | Ô |
Ô chữ | Hàm bỏ qua ô chữ. | Ô "Nghỉ phép" không làm hàm báo lỗi. |
Số 0 | Hàm tính số 0 như mọi số khác. | Bạn gõ |
Số lưu dạng chữ | Hàm bỏ qua ô này, kể cả khi ô trông như số. | Bạn gõ |
Ô báo lỗi | Hàm báo lỗi theo ô đó. | Bạn gõ |
Vùng không có số nào | Hàm trả về 0. | Cột tên nhân viên |
Các ví dụ sau bạn gõ vào ô trống H2 của sheet Doanh số rồi nhấn Enter, mỗi lần một công thức.
Hàm xử lý khác khi bạn gõ giá trị thẳng vào hàm thay vì chọn vùng ô. Công thức =MAX(5,"10") trả về 10, vì Excel đổi chữ "10" thành số. Công thức =MAX(5,"abc") báo lỗi #VALUE!, vì Excel không đổi được "abc" thành số. Giá trị TRUE gõ thẳng vào hàm được tính là 1, nên =MAX(TRUE,0) trả về 1.
Hàm MAXA và MINA coi ô chữ trong vùng là 0. Với bảng trong bài, =MINA(C2:C11) trả về 0 vì ô "Nghỉ phép" được tính là 0. Bạn chỉ dùng MAXA, MINA khi muốn ô chữ được tính là 0.
Nếu vùng có ô báo lỗi, bạn dùng hàm AGGREGATE để bỏ qua lỗi. Công thức =AGGREGATE(4,6,C2:C11) tìm số lớn nhất, còn =AGGREGATE(5,6,C2:C11) tìm số nhỏ nhất. Số 4 là MAX, số 5 là MIN, số 6 bảo hàm bỏ qua ô lỗi. Với bảng hiện tại không có ô lỗi, 2 công thức trả về 236,000,000 và 87,900,000, giống MAX và MIN.
Hàm MAXIFS, MINIFS có trong phiên bản Excel nào?
Theo trang hỗ trợ của Microsoft, hàm MAXIFS và MINIFS có trên Microsoft 365, Excel 2024, Excel 2021 và Excel 2019, cả Windows và Mac.
Excel 2016, 2013, 2010 bản mua một lần không có 2 hàm này. Nếu bạn gõ MAXIFS trên các bản đó, ô báo lỗi #NAME?. Bạn không cài thêm hàm MAXIFS vào Excel 2016 được. Bạn cần nâng cấp lên Excel 2019 trở lên, dùng Microsoft 365, hoặc dùng công thức MAX(IF) ở mục bên dưới.
Google Sheets cũng có hàm MAXIFS và MINIFS với cú pháp giống Excel.
Bạn tìm doanh số cao nhất theo khu vực bằng MAXIFS, MINIFS như thế nào?
Hàm MAXIFS, MINIFS có cú pháp thế nào?
=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
=MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Tham số | Ý nghĩa |
|---|---|
max_range hoặc min_range | Tham số này là vùng chứa số cần tìm lớn nhất hoặc nhỏ nhất, ví dụ cột doanh số. |
criteria_range1 | Tham số này là vùng chứa điều kiện, ví dụ cột khu vực. |
criteria1 | Tham số này là điều kiện, ví dụ ô chứa chữ "Bắc", hoặc biểu thức như ">0". |
criteria_range2, criteria2 | Các cặp này là điều kiện thêm. Bạn bỏ trống được, và mỗi hàm nhận tối đa 126 cặp điều kiện. |
Hàm chỉ lấy dòng thỏa mọi điều kiện. Vùng số và các vùng điều kiện phải có cùng số dòng, số cột.
Ví dụ: Bạn tìm doanh số cao nhất, thấp nhất của từng khu vực
Bảng nhỏ ở E7:G10 của sheet Doanh số có tên 3 khu vực ở cột E. Bạn chọn ô F8, gõ công thức sau rồi nhấn Enter:
=MAXIFS($C$2:$C$11,$B$2:$B$11,E8)

Dấu $ giữ nguyên 2 vùng dữ liệu khi bạn kéo công thức xuống. Riêng ô E8 không có dấu $, nên khi kéo xuống, công thức tự đổi sang E9, E10.
Tiếp theo, bạn chọn ô G8, gõ công thức sau rồi nhấn Enter:
=MINIFS($C$2:$C$11,$B$2:$B$11,E8)

Sau đó, bạn chọn 2 ô F8:G8 và kéo xuống dòng 10.

Khu vực Nam có ô C6 trống, nhưng hàm MINIFS vẫn trả về 87,900,000. Hàm MAXIFS, MINIFS bỏ qua ô trống và ô chữ trong vùng số giống như MAX, MIN.
Điều kiện chữ không phân biệt chữ hoa, chữ thường, nên "bắc" và "Bắc" cho cùng kết quả. Nếu không dòng nào khớp điều kiện, ví dụ khu vực "Tây", 2 hàm trả về 0 chứ không báo lỗi.
Bạn tìm giá nhập thấp nhất bỏ qua số 0 như thế nào?
Sheet Giá nhập có bảng báo giá của 3 nhà cung cấp cho 3 mặt hàng văn phòng phẩm. Ô C3 và ô C7 ghi 0 vì nhà cung cấp chưa báo giá.
Bạn chọn ô F2, gõ công thức sau rồi nhấn Enter:
=MIN(C2:C10)


Kết quả 0 không phải giá nhập thật. Bạn dùng hàm MINIFS với điều kiện ">0" để chỉ xét các ô lớn hơn 0. Bạn chọn lại ô F2, gõ công thức sau rồi nhấn Enter:
=MINIFS(C2:C10,C2:C10,">0")
0")" loading="lazy">
Bạn cũng có thể viết điều kiện là "<>0", nghĩa là khác 0. Với bảng giá chỉ có số dương, 2 điều kiện cho cùng kết quả 48,000. Nếu bảng có số âm, ví dụ một dòng giảm giá -2,000, điều kiện "<>0" trả về -2,000, còn ">0" vẫn trả về 48,000.
Ví dụ: Bạn tìm giá thấp nhất của từng mặt hàng bằng 2 điều kiện
Bảng nhỏ ở E4:G7 có tên 3 mặt hàng ở cột E. Mỗi mặt hàng cần 2 điều kiện: đúng tên mặt hàng và đơn giá lớn hơn 0. Bạn chọn ô F5, gõ công thức sau rồi nhấn Enter:
=MINIFS($C$2:$C$10,$A$2:$A$10,E5,$C$2:$C$10,">0")
0")" loading="lazy">Sau đó, bạn kéo công thức từ ô F5 xuống ô F7.

Nếu bạn bỏ cặp điều kiện ">0", Giấy A4 và Bút bi đều trả về 0, vì mỗi mặt hàng này có một nhà cung cấp chưa báo giá.
Excel 2016 không có MAXIFS thì bạn dùng công thức nào?
Bạn dùng hàm MAX lồng hàm IF. Hàm IF giữ lại doanh số của các dòng đúng khu vực, rồi hàm MAX tìm số lớn nhất trong phần còn lại. Công thức này chạy được trên mọi phiên bản Excel.
Ví dụ: Bạn tìm doanh số cao nhất theo khu vực bằng MAX(IF)
Sheet Excel cũ có cùng bảng doanh số và bảng nhỏ ở E1:G4. Bạn chọn ô F2 và gõ công thức sau, chưa nhấn Enter:
=MAX(IF($B$2:$B$11=E2,$C$2:$C$11))

Bạn giữ phím Ctrl và Shift, rồi nhấn Enter. Excel tự thêm cặp ngoặc nhọn {} bao quanh công thức để đánh dấu đây là công thức mảng. Bạn không tự gõ cặp ngoặc này. Sau đó, bạn kéo công thức từ ô F2 xuống ô F4.

Kết quả khớp đúng với hàm MAXIFS ở sheet Doanh số. Bạn cần nhớ 2 điểm về phím tắt:
Trên Excel 2019 trở về trước, bạn bắt buộc nhấn Ctrl + Shift + Enter. Nếu chỉ nhấn Enter, công thức không báo lỗi mà trả về số sai. Với bảng này, ô
F2ra 236,000,000 là doanh số cao nhất cả công ty, còn ôF3vàF4ra 0.Trên Microsoft 365, Excel 2021 và Excel 2024, bạn chỉ cần nhấn Enter. Nhấn Ctrl + Shift + Enter vẫn cho cùng kết quả.
Vì sao MIN(IF) trả về 0 khi vùng có ô trống?
Bạn chọn ô G2, gõ công thức sau rồi nhấn Ctrl + Shift + Enter:
=MIN(IF($B$2:$B$11=E2,$C$2:$C$11))

Sau đó, bạn kéo công thức từ ô G2 xuống ô G4.

Khi hàm IF lấy giá trị từ ô trống C6, ô trống biến thành số 0, nên hàm MIN trả về 0. Hàm MINIFS không gặp lỗi này. Bạn thêm một hàm IF nữa để bỏ các ô trống. Bạn chọn lại ô G2, gõ công thức sau rồi nhấn Ctrl + Shift + Enter:
=MIN(IF($B$2:$B$11=E2,IF($C$2:$C$11<>"",$C$2:$C$11)))
"",$C$2:$C$11)))" loading="lazy">Sau đó, bạn kéo lại công thức từ ô G2 xuống ô G4.

Để bỏ số 0 trên Excel cũ, ví dụ ở sheet Giá nhập, bạn gõ =MIN(IF(C2:C10>0,C2:C10)) vào ô F2 rồi nhấn Ctrl + Shift + Enter. Kết quả là 48,000, giống hàm MINIFS.
Bạn lấy tên người có doanh số cao nhất bằng INDEX MATCH hoặc XLOOKUP như thế nào?
Ví dụ: Bạn lấy tên nhân viên cao nhất bằng INDEX MATCH
Bạn quay lại sheet Doanh số. Ô F2 đã có doanh số cao nhất. Hàm MATCH tìm vị trí của số đó trong cột C, rồi hàm INDEX lấy tên ở cùng vị trí trong cột A. Bạn chọn ô F4, gõ công thức sau rồi nhấn Enter:
=INDEX(A2:A11,MATCH(F2,C2:C11,0))


Công thức INDEX MATCH chạy được trên mọi phiên bản Excel.
Ví dụ: Bạn lấy tên nhân viên thấp nhất bằng XLOOKUP
Hàm XLOOKUP làm cùng việc bằng một hàm. Bạn chọn ô F5, gõ công thức sau rồi nhấn Enter:
=XLOOKUP(F3,C2:C11,A2:A11)


Hàm XLOOKUP có trên Microsoft 365, Excel 2021 và Excel 2024. Bài hàm XLOOKUP trong Excel có thêm 10 ví dụ tra cứu khác.
Vì sao XLOOKUP trả sai tên nhà cung cấp khi có điều kiện?
Bạn quay lại sheet Giá nhập và muốn biết nhà cung cấp nào báo giá thấp nhất cho từng mặt hàng. Bạn chọn ô G5, gõ công thức sau rồi nhấn Enter:
=XLOOKUP(F5,$C$2:$C$10,$B$2:$B$10)

Sau đó, bạn kéo công thức từ ô G5 xuống ô G7.

Ô G7 sai vì giá 48,000 xuất hiện 2 lần trong cột C. Lần đầu ở dòng 6 là giá Bút bi của Tân Tiến, lần sau ở dòng 8 là giá Bìa còng của Minh Phát. Hàm XLOOKUP lấy dòng đầu tiên khớp giá, nên trả về Tân Tiến.
Bạn cần tìm theo cả mặt hàng và giá. Bạn chọn lại ô G5, gõ công thức sau rồi nhấn Enter:
=XLOOKUP(1,($A$2:$A$10=E5)*($C$2:$C$10=F5),$B$2:$B$10)

Mỗi điều kiện trong ngoặc trả về TRUE hoặc FALSE cho từng dòng. Phép nhân đổi TRUE thành 1 và FALSE thành 0, nên chỉ dòng thỏa cả 2 điều kiện có kết quả bằng 1. Hàm XLOOKUP tìm số 1 đó và trả về tên nhà cung cấp. Sau đó, bạn kéo lại công thức từ ô G5 xuống ô G7.

Trên Excel 2019 trở về trước, bạn dùng =INDEX($B$2:$B$10,MATCH(1,($A$2:$A$10=E5)*($C$2:$C$10=F5),0)) gõ vào ô G5 rồi nhấn Ctrl + Shift + Enter. Công thức này cho cùng kết quả.
Khi nhiều dòng cùng khớp, XLOOKUP và INDEX MATCH chỉ trả về dòng đứng trước trong bảng. Ví dụ, giá 48,000 ở ô F2 là giá thấp nhất cả bảng và thuộc về 2 nhà cung cấp. Để thấy cả 2 tên, bạn dùng hàm FILTER trên Microsoft 365, Excel 2021 hoặc Excel 2024. Bạn gõ =TEXTJOIN(", ",TRUE,FILTER(B2:B10,C2:C10=F2)) vào ô E9, ô này trả về "Tân Tiến, Minh Phát".
Công thức ở ô E9 chỉ lọc theo giá, nên 2 tên trên thuộc 2 mặt hàng khác nhau. Khi cần mọi nhà cung cấp cùng báo giá thấp nhất cho một mặt hàng, bạn thêm điều kiện mặt hàng. Bạn gõ =TEXTJOIN(", ",TRUE,FILTER($B$2:$B$10,($A$2:$A$10=E5)*($C$2:$C$10=F5))) vào ô H5 rồi kéo xuống ô H7. Bảng mẫu không có mặt hàng nào trùng giá thấp nhất, nên cột H trả về đúng một tên mỗi dòng giống cột G. Nếu Tân Tiến cũng báo Bìa còng 48,000, ô H7 sẽ trả về "Minh Phát, Tân Tiến".
Bạn hay gặp lỗi gì khi dùng MAX, MIN, MAXIFS, MINIFS?
Lỗi | Nguyên nhân | Cách sửa |
|---|---|---|
Ô báo | Excel 2016 bản mua một lần và các bản cũ hơn không có 2 hàm này. | Bạn dùng MAX(IF) hoặc MIN(IF) với Ctrl + Shift + Enter. |
MAXIFS, MINIFS trả về 0 | Không dòng nào khớp điều kiện, ví dụ gõ sai tên khu vực, hoặc ô điều kiện để trống. | Bạn kiểm tra chính tả điều kiện và xem ô điều kiện có dữ liệu chưa. |
MAXIFS bỏ sót một dòng | Ô điều kiện trong bảng có dấu cách thừa, ví dụ "Bắc " có dấu cách ở cuối. Khi ô | Bạn xóa dấu cách thừa bằng hàm TRIM hoặc Find and Replace. |
MAXIFS, MINIFS báo | Vùng số và vùng điều kiện khác số dòng, ví dụ | Bạn sửa để các vùng có cùng dòng bắt đầu và dòng kết thúc. |
MAX trả về số nhỏ hơn số lớn nhất bạn thấy | Ô chứa số lớn nhất là số lưu dạng chữ, thường có dấu tam giác xanh ở góc ô. | Bạn đổi chữ thành số theo bài chuyển text thành số trong Excel. |
MIN trả về 0 | Vùng có ô bằng 0, ví dụ nhà cung cấp chưa báo giá. | Bạn dùng |
MIN(IF) trả về 0 | Vùng số có ô trống, hàm IF đổi ô trống thành 0. | Bạn thêm |
MAX(IF) trả về số sai trên Excel cũ | Bạn chỉ nhấn Enter thay vì Ctrl + Shift + Enter. | Bạn chọn lại ô, nhấn F2 rồi nhấn Ctrl + Shift + Enter. |
MAX, MIN báo | Vùng có ô báo lỗi. | Bạn sửa ô lỗi, hoặc dùng |
Điều kiện của MAXIFS, MINIFS viết giống hệt điều kiện của SUMIFS, COUNTIFS và AVERAGEIFS. Bài tính trung bình có điều kiện trong Excel có thêm cách viết điều kiện theo ngày và theo ký tự đại diện.
Bạn muốn học Excel bài bản hơn?
Nếu muốn học bài bản cùng người hướng dẫn, bạn có thể tham khảo khóa 30 ngày học và thực hành Excel của Nghề Excel, với 30 buổi luyện tập, hơn 500 bài tập thực tế lấy từ doanh nghiệp và coach đồng hành cùng bạn trong từng buổi.
Ảnh chụp màn hình sản phẩm Microsoft: Used with permission from Microsoft.