Trong công việc quản lý dữ liệu, phân tích báo cáo hay lập kế hoạch tài chính, việc tìm giá trị lớn nhất trong một tập dữ liệu thường xuyên được thực hiện. Tuy nhiên, khi dữ liệu của bạn được phân loại theo nhiều tiêu chí, nhu cầu thực tế lại không chỉ là “tìm giá trị lớn nhất”, mà còn cần “lớn nhất trong một trường hợp cụ thể” – tức là tìm số lớn nhất có điều kiện. Excel cung cấp nhiều công cụ mạnh mẽ để thực hiện điều này, từ hàm đơn giản MAXIFS cho đến các phương pháp nâng cao hơn như dùng hàm SUMPRODUCT, hàm FILTER kết hợp với MAX, hoặc thậm chí là công thức mảng (array formulas). Bài viết này sẽ hướng dẫn chi tiết các cách tiếp cận, ví dụ thực tế, ưu nhược điểm của từng phương pháp, cũng như những mẹo làm việc hiệu quả hơn trong Excel 365, Excel 2026, và các phiên bản cũ hơn.
Có thể bạn quan tâm: Tách Họ Và Tên Trong Excel Không Dùng Hàm – Tất Cả Các Phương Pháp Hiệu Quả
Dassarđề mục
- Nhu cầu tìm số lớn nhất có điều kiện
- Phương pháp 1: Hàm MAXIFS (Excel 2026+)
- Phương pháp 2: Hàm MAX kết hợp với IF (công thức mảng)
- Phương pháp 3: Hàm SUMPRODUCT
- Phương pháp 4: Hàm FILTER + MAX (Excel 365)
- Phương pháp 5: Công thức động (XLOOKUP + FILTER)
- So sánh các phương pháp
- Mẹo tối ưu và lưu ý khi làm việc
- Kết luận
Có thể bạn quan tâm: Thủ Thuật Excel Từ Cơ Bản Đến Nâng Cao
1. Nhu cầu tìm số lớn nhất có điều kiện
Giả sử bạn đang quản lý dữ liệu bán hàng của một cửa hàng. Cột A là Mã sản phẩm, cột B là Tên sản phẩm, cột C là Số lượng bán, cột D là Ngày bán, và cột E là Doanh thu. Bạn muốn biết:
- Sản phẩm nào bán được nhiều nhất trong tháng frem?
- Giá trị doanh thu cao nhất của một danh mục sản phẩm cụ thể?
- Giá trị lớn nhất của một trường hợp khi có nhiều điều kiện (ví dụ: sản phẩm “X” bán trong ngày 2026-07-01 có doanh thu cao nhất là bao nhiêu?).
Để trả lời những câu hỏi này, chúng ta cần một công thức linh hoạt cho phép lọc dữ liệu theo một hay nhiều điều kiện, sau đó tìm giá trị lớn nhất trong phạm vi đã lọc.
Lưu ý: Trong Excel, “điều kiện” có thể là một giá trị xác định, một dải ô, hoặc một biểu thức phức tạp (ví dụ:
A:A="X",C:C>100,Including a condition like “greater than or equal to X”).
Có thể bạn quan tâm: Thuyết Minh Bctc Theo Thông Tư 200: Hướng Dẫn Sử Dụng File Excel
2. Phương pháp 1: Server MAXIFS (Excel 2026+)
2.1 Cú pháp
MAXIFS(max_range, criteria_range1, criteria1, criteria_range2, criteria2, …)
- max_range: Dải ô chứa giá trị cần tìm lớn nhất.
- criteria_rangeX: Dải ô chứa điều kiện.
- criteriaX: Giá trị điều kiện (có thể là số, chuỗi, biểu thức).
2.2 Ví dụ thực tế
Giả sử dữ liệu nằm trong bảng A2:E100. Bạn muốn tìm doanh thu lớn nhất của sản phẩm “Laptop” trong tháng 07/2026.
| A (Mã) | B (Tên) | C (Số lượng) | D (Ngày) | E (Doanh thu) |
|---|---|---|---|---|
| P001 | Laptop | 10 | 2026-07-01 | 500,000 |
| P002 | Laptop | 5 | 2026-07-02 | 300,000 |
| P003 | Tablet | 8 | 2026-07-01 | 200,000 |
Công thức:
=MAXIFS(E2:E100, B2:B100, "Laptop", D2:D100,有码>=DATE(2026,7,1), D2:D100, D2:D◇<DATE(2026,8,1))
E2:E100là phạm vi doanh thu cần tìm lớn nhất.B2:B100, "Laptop"lọc theo tên sản phẩm.D2:D100,>=DATE(2026,7,1)vàD2:D100,<DATE(2026,8,1)giới hạn ngày trong tháng 07/2026.
Kết quả: 500,000.
2.3 Ưu điểm
- Đơn giản, trực quan.
- Hỗ trợ nhiều điều kiện cùng lúc.
- Dễ đọc và bảo trì.
2.4 Nhược điểm
- Chỉ có sẵn trong Excel 2026 trở lên.
- Không hỗ trợ điều kiện phức tạp như “số lượng > 100 hoặc doanh thu > 1 triệu”.
3. Phương pháp 2: Hàm MAX kết hợp với IF (công thức mảng)
3.1 Cú pháp
=MAX(IF(criteria_range1 = criteria1, IF(criteria_range2 = criteria2, max_range, ""), ""))
- Khi dùng trong Excel кнопку Mảng (Array Formula), bạn cần nhấn
Ctrl+Shift+Enter(CSE) trong phiên bản cũ, hoặc chỉEntertrong Excel gur.
3.2 Ví dụ thực tế
Tìm doanh thu lớn nhất của sản phẩm “Laptop” trong tháng 07/2026:
=MAX(IF(B2:B100="Laptop", IF((D2:D100>=DATE(2026,7,1))(D2:D100<DATE(2026,8,1)), E2:E100, ""), ""))
生là điều kiện 2: ngày trong tháng 07/2026.là phép nhân, biểu diễn “và” (AND).
Kết quả: 500,000.
3.3 Ưu điểm
- Hỗ trợ bất kỳ phiên bản Excel nào (đòi hỏi CSE).
- Có thể kết hợp nhiều điều kiện phức tạp, kể cả điều kiện “hoặc” (OR) bằng cách sử dụng dấu cộng
+.
3.4 Nhược điểm ctxt
- Công thức dài, khó đọc.
- Cần nhấn
Ctrl+Shift+Entertrong Excel 2026 trở xuống. - Hiệu suất giảm khi làm việc với dữ liệu lớn (thousand/millions rows).
4. Phương pháp 3: Hàm SUMPRODUCT
4.1 Cú pháp
=MAX(SUMPRODUCT((criteria_range1=criteria1)(criteria_range2=criteria2)max_range))
SUMPRODUCTtính tổng các sản phẩm của các mảng.- Khi kết hợp với
MAX, ta có thể lấy giá trị lớn nhất.
4.2 Ví dụ thực tế
=MAX(SUMPRODUCT((B2:B100="Laptop")(D2:D100>=DATE(2026,7,1))(D2:D100<DATE(2026,8,1))E2:E100))
Kết quả: 500,000.
4.3 Ưu điểm
Có thể bạn quan tâm: Tìm Giá Trị Xuất Hiện Nhiều Nhất Trong Excel
- Không cần công thức Salon.
պահին giúp dễ tính nhanh hơn trong Excel 2026-2026. - Hỗ trợ điều kiện phức tạp (AND/OR) bằng cách thêm hoặc trừ.
4.4 Nhược điểm
- Việc tính toán có thể chậm khi dữ liệu lớn.
- Công thức có thể trở nên rối rắm nếu có nhiều điều kiện.
5. Phương pháp 4: Hàm FILTER + MAX (Excel 365)
5.1 Cú pháp
=MAX(FILTER(max_range, (criteria_range1=criteria1)(criteria_range2=criteria2)...))
FILTERtrả về một mảng chứa các giá trị thoả mãn điều kiện.MAXlấy giá trị lớn nhất từ mảng đó.
5.2 Ví dụ thực tế
=MAX(FILTER(E2:E100, (B2:B verwerkt="Laptop")(D2:D100>=DATE(2026,7,1))(D2:D100<DATE(2026,8,1))))
Kết quả: 500,000.
5.3 Ưu điểm
- Đơn giản, dễ hiểu.
- Không cần công thức mảng đặc biệt.
- Tích hợp chặt chẽ với tính năng động “Dynamic Arrays”.
5.4 Nhược điểm
- Chỉ có sẵn trong Excel 365 (Office 365) hoặc Excel 2026+ với tính năng Dynamic Arrays.
- Nếu không có dữ liệu phù hợp, hàm FILTER sẽ trả về lỗi #CALC! – cần xử lý với IFERROR.
6. Phương pháp 5: Công thức động (XLOOKUP + FILTER)
6.1 Mục đích
Đôi khi bạn muốn tìm điểm cao nhất nhưng cũng cần giá trị liên quan (ví dụ: ngày bán, mã sản phẩm). Bạn có thể dùng XLOOKUP kết hợp với FILTER để lấy dòng(scanner) có giá trị lớn nhất.
6.2 Cú pháp
XLOOKUP(MAX(FILTER(max_range, criteria_range1=criteria1)), max_range, return_range)
6.3 Ví dụ thực tế
Giả sử bạn muốn lấy ngày bán (cột D) của doanh thu lớn nhất cho sản phẩm “Laptop” trong tháng 07/2026:
=XLOOKUP( MAX(FILTER(E2:E100, (B2:B100="Laptop")(D2:D100>=DATE(2026,7,1))(D2:D100<DATE(2026,8,1)))), E2:E100, D2:D100
)
Kết quả: 2026-07-01.
6.4 Ưu điểm
- Trả về thông tin liên quan một cách nhanh chóng.
- Không cần nhiều công thức lồng nhau.
6.5 Nhược điểm
- Phụ thuộc vào Excel 365.
- Nếu cóウト nhiều dòng có cùng giá trị lớn nhất, XLOOKUP sẽ trả về giá trị đầu tiên.
7. So sánh các phương pháp
| Phương pháp | Phiên bản Excel | Độ phức tạp | Độ linh hoạt | Khả năng mở rộng | Mẹo tối ưu |
|---|---|---|---|---|---|
| MAXIFS | 2026+ | Thấp | Trung bình | Tốt | Sử dụng để lọc nhanh |
| MAX+IF (mảng) | Tất cả | Trung bình | Cao | Tốt | Nhấn CSE trong Excel cũ |
| SUMPRODUCT | Tất cả | Trung bình | Cao | Tốt | Giảm số phép nhân |
| FILTER+MAX | 365 | Thấp | Cao | Tốt | Xử lý lỗi #CALC! |
| XLOOKUP+FILTER | 365 | Thấp | Cao | Tốt | Kết hợp nhiều dữ liệu |
- Nếu bạn đang dùng Excel 365:
FILTER + MAXlà lựa chọn tối ưu nhất vì công thức ngắn gọn, dễ hiểu. - Nếu bạn đang dùng Excel 2026+:
MAXIFSlà giải pháp dễ dàng và nhanh nhất. - Nếu bạn phải làm việc với Excel 2026 hoặc cũ hơn: dùng công thức mảng
MAX+IFhoặcSUMPRODUCT. - Nếu bạn cần lấy thêm dữ liệu liên quan: kết hợp
XLOOKUPvớiFILTER.
8. Mẹo tối ưu và lưu ý khi làm việc
- Sử dụng dải ô rõ ràng: Thay vì
A:A, hãy giới hạn phạm viA2:A1000để tránh tính toán trên hàng trống, giúp giảm thời gian tính toán. - Kiểm tra lỗi: Khi dùng
FILTER, nếu không có dữ liệu phù hợp, hàm trả về lỗi #CALC! – có thể dùngIFERROR(...,"")để tránh lỗi hiển thị. - Tránh lặp lại công thức: Nếu bạn cần áp dụng công thức nhiều lần, hãy đặt nó vào một ô riêng và dùng tham chiếu đến ô đó.
- Sử dụng tính năng “Dynamic Arrays”: Đối với Excel 365, khi dùng
FILTER, kết quả được tự động phân mảng, giúp tránh việc nhập công thức mảng. - Kiểm soát kích thước dữ liệu: Nếu dữ liệu lớn hơn 1 triệu hàng, hãy cân nhắc sử dụng Power Query để xử lý dữ liệu trước khi đưa vào bảng tính.
- Kiểm tra tính đúng đắn của điều kiện: Đảm bảo rằng các điều kiện (e.g.,
>=DATE(2026,7,1)) được viết đúng định dạng ngày, tránh lỗi dữ liệu. - Sử dụng tên phạm vi: Đ reduz lỗi khi di chuyển dữ liệu. Ví dụ, đặt
DoanhThucho dảiE2:E1000. Sau đó dùng=MAXIFS(DoanhThu, ... ). - Sử dụng công thức “OR”: Khi cần tìm giá trị lớn nhất trong nhiều điều kiện “hoặc”, dùng
+trong công thức mảng:IF((cond1)+(cond2), ... ). - Tối ưu hóa công thức mảng: Tránh sử dụng
IFERRORbên trong mảng, vì nó làm tăng độ phức tạp. - Kiểm tra lại dữ liệu: Đảm bảo không có giá trị
#N/A,#DIV/0!trong vùng dữ liệu, vì chúng sẽ làmMAXtrả về lỗi.
9. Kết luận
Việc tìm số lớn nhất có điều kiện trong Excel không chỉ là một thao tác đơn giản mà còn là một kỹ năng cơ bản nhưng rất quan trọng trong công việc phân tích dữ liệu. Tùy thuộc vào phiên bản Excel và yêu cầu tính toán, bạn có thể lựa chọn:
- MAXIFS: nhanh, dễ đọc, cho các phiên bản hiện đại.
- MAX+IF: linh hoạt achterkant, phù hợp với Excel cũ.
- SUMPRODUCT: phù hợp khi cần tính toán phức tạp mà không muốn dùng công thức mảng.
- FILTER + MAX: tối ưu cho Excel 365, công thức ngắn gọn, dễ bảo trì.
- XLOOKUP + FILTER: hữu ích khi cần lấy thông tin liên quan tới giá trị lớn nhất.
Bằng cách nắm vững các công thức trên và áp dụng những mẹo tối ưu đã nêu, bạn sẽ có thể xử lý dữ liệu một cách nhanh chóng, chính xác và hiệu quả hơn. Hãy thử áp dụng các công thức vào bảng dữ liệu thực tế của mình và cảm nhận sự khác biệt trong quy trình làm việc!
Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.
Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.
Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.