Cách Sử Dụng Hàm Max Kèm Điều Kiện Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Có thể bạn quan tâm: Sửa Lỗi “name Conflict” Trong Excel: Nguyên Nhân, Cách Khắc Phục Và Những Lưu Ý Quan Trọng

Mở Đầu

Trong công việc phân tích dữ liệu, việc tìm giá trị lớn nhất (MAX) trong một tập hợp dữ liệu là một trong những nhu cầu cơ bản nhất. Tuy nhiên, trong thực tế, chúng ta thường cần tìm giá trị lớn nhất điều kiện (ví dụ: lớn nhất trong một nhóm, lớn nhất khi thỏa mãn một tiêu chí nào đó). Excel cung cấp nhiều cách để thực hiện việc này, từ các hàm truyền thống như MAXIFS (đối với Excel 2026 trở lên) cho tới việc kết hợp các hàm ARRAY (mảng) như MAX, IF, SUMPRODUCT, AGGREGATE, FILTER… Bài viết này sẽ đưa ra hướng dẫn chi tiết, từng bước, từ cơ bản đến nâng cao, giúp bạn thành thạo việc lấy giá trị MAX với điều kiện trong Excel.

Có thể bạn quan tâm: Cách Định Dạng Dấu Chấm Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

1. Tổng Quan Về Hàm MAX và Các Hàm Liên Quan

1.1 Hàm MAX

• Cú pháp: MAX(number1, number2, …)
• Mô tả: Trả về giá trị lớn nhất trong một tập hợp các số.
• Ví dụ: =MAX(A1:A10) trả về số lớn nhất trong cột A từ hàng 1 tới 10.

1.2 Giới hạn khi chỉ dùng MAX

• Hàm MAX chỉ trả về giá trị lớn nhất không có điều kiện. Khi cần lọc dữ liệu dựa trên tiêu chí nào đó, chúng ta phải kết hợp thêm các hàm khác.

1.3 Các hàm hỗ trợ điều kiện

Có thể bạn quan tâm: Lỗi “excel Has Stopped Working” – Nguyên Nhân, Phân Tích Và Giải Pháp Chi Tiết

2. Sử Dụng Hàm MAXIFS (Excel 2026 trở lên)

2.1 Cú pháp

MAXIFS(max_range, criteria_range1, criteria1, criteria_range2, criteria2, …)

• max_range: Vùng chứa các giá trị cần lấy max.
• criteria_rangeX: Vùng cần kiểm tra điều kiện.
• criteriaX: Điều kiện (có thể là số, văn bản, biểu thức, …).

2.2 Ví dụ thực tế

Giả sử bảng dữ liệu bán hàng:

Mục tiêu: Tìm doanh thu lớn nhất của nhân viên Ngọc trong khu vực Bắc.

Công thức:

=MAXIFS(D2:D6, B2:B6, "Ngọc", C2:C6, "Bắc")

Kết quả: 1800 (doanh thu lớn nhất của Ngọc ở khu vực Bắc).

2.3 Khi có nhiều điều kiện (>=, <=, <>)

• Điều kiện lớn hơn hoặc bằng: ">=1000"
• Điều kiện không bằng: "<>Bắc"

Ví dụ: Tìm max doanh thu không ở khu vực Bắc và lớn hơn hoặc bằng 2000:

=MAXIFS(D2:D6, C2:C6, "<>Bắc", D2:D6, ">=2000")

Kết quả: 2500 (Hùng ở khu vực Bắc nhưng không thỏa điều kiện khu vực, nên chỉ còn Hùng ở khu vực Nam 2300, và Hùng 2500 ở Bắc bị loại, nên max = 2300? Kiểm tra lại). Thực tế, công thức sẽ trả về 2300 vì chỉ còn Hùng ở Nam.

3. Khi Không Có MAXIFS: Sử Dụng Công Thức Mảng (Array Formula)

Trong các phiên bản Excel cũ (2026, 2026, 2026…) không hỗ trợ hàm MAXIFS, bạn có thể dùng công thức mảng.

3.1 Cú pháp cơ bản

=MAX(IF(criteria_range=criteria, max_range))

Lưu ý: Nhấn Ctrl+Shift+Enter (CSE) để công thức trở thành công thức mảng (Excel sẽ tự thêm {} quanh công thức). Trong Excel 365, công thức mảng động không cần CSE.

3.2 Ví dụ thực tế

Áp dụng cùng bảng trên, tìm max doanh thu của Hùng:

=MAX(IF(B2:B6="Hùng", D2:D6))

• Nhấn Ctrl+Shift+Enter → Kết quả 2500.

3.3 Nhiều điều kiện

Bạn có thể lồng nhiều hàm IF hoặc dùng toán tử logic (AND) và + (OR).

3.3.1 AND (cùng lúc thỏa mãn tất cả)

=MAX(IF((B2:B6="Hùng")(C2:C6="Bắc"), D2:D6))

Giải thích: (B2:B6="Hùng") và (C2:C6="Bắc") trả về mảng TRUE/FALSE → Khi nhân () chúng, chỉ những hàng thỏa cả hai mới trả về 1 (TRUE). Sau đó IF lấy giá trị D tương ứng.

Kết quả: 2500 (Hùng ở Bắc).

3.3.2 OR (thỏa một trong các điều kiện)

=MAX(IF((C2:C6="Bắc")+(C2:C6="Nam"), D2:D6))

Ở đây + hoạt động như OR. Kết quả: 2500 (các khu vực Bắc hoặc Nam).

3.4 Xử lý lỗi khi không có dữ liệu thỏa điều kiện

Nếu không có giá trị nào thỏa, hàm IF trả về FALSE và MAX sẽ trả về 0, gây hiểu lầm. Để tránh, dùng:

=IFERROR(MAX(IF(...)), "Không có dữ liệu")

Hoặc trong Excel 365:

=LET( data, IF((B2:B6="Ngọc")(C2:C6="Bắc"), D2:D6), IF(COUNT(data)=0, "Không có dữ liệu", MAX(data))
)

4. Sử Dụng SUMPRODUCT Để Tránh Công Thức Mảng

SUMPRODUCT là một hàm mạnh mẽ cho phép thực hiện các phép tính có điều kiện mà không cần CSE.

4.1 Cú pháp cơ bản

SUMPRODUCT(array1, array2, array3, …)

Khi các mảng chứa TRUE/FALSE, Excel tự động chuyển chúng thành 1/0.

4.2 Tìm MAX có điều kiện với SUMPRODUCT

Công thức:

=MAX(SUMPRODUCT((B2:B6="Ngọc")(C2:C6="Bắc")D2:D6, (B2:B6="Ngọc")(C2:C6="Bắc")0))

Tuy nhiên, cách trên không thực sự chuẩn. Thay vào:

=MAX(IF((B2:B6="Ngọc")(C2:C6="Bắc"), D2:D6))

Nhưng muốn tránh CSE, chúng ta dùng:

=AGGREGATE(14,6,D2:D6/((B2:B6="Ngọc")(C2:C6="Bắc")),1)

Trong đó:

• 14 là hàm LARGE (lấy thứ k).
• 6 là tùy chọn bỏ qua lỗi.
• D2:D6/((B2:B6="Ngọc")(C2:C6="Bắc")) sẽ tạo ra mảng chứa giá trị chia cho 1 (nếu thỏa) hoặc chia cho 0 → lỗi #DIV/0!. AGGREGATE bỏ qua các lỗi này và trả về giá trị lớn nhất.

4.3 Ưu điểm

Có thể bạn quan tâm: Lỗi “sharing Violation” Trong Excel: Nguyên Nhân, Hậu Quả Và Giải Pháp Chi Tiết

• Không cần nhấn CSE.
• Tốc độ tính toán nhanh hơn khi dữ liệu lớn.

5. Hàm AGGREGATE – Lựa Chọn Thông Minh Khi Xử Lý Lỗi

5.1 Cú pháp AGGREGATE

AGGREGATE(function_num, options, array, k)

• function_num: 1‑19 (AVERAGE, COUNT, MAX, MIN, …, LARGE, SMALL…).
• options: 0‑7 (bỏ qua lỗi, ẩn, …).
• k: Đối với LARGE/SMALL, vị trí thứ k.

5.2 Lấy MAX có điều kiện

=AGGREGATE(4,6,D2:D6/((B2:B6="Ngọc")(C2:C6="Bắc")),1)

• 4 = hàm MAX.
• 6 = bỏ qua lỗi và các giá trị ẩn.
• D2:D6/((...)) tạo ra lỗi #DIV/0! cho các hàng không thỏa.
• 1 = chỉ cần giá trị max (đối với hàm MAX, k không cần, nhưng để thống nhất).

Kết quả: 1800.

5.3 Khi muốn lấy TOP N

Nếu muốn lấy TOP 3 doanh thu của nhân viên Hùng:

=AGGREGATE(14,6,D2:D6/((B2:B6="Hùng")),3)

• 14 = hàm LARGE (lấy giá trị lớn thứ k).
• 3 = thứ ba.

Kết quả: 2300 (vì các giá trị Hùng: 2300, 2500 → TOP3 trả về 0? Actually only 2 values, hàm sẽ trả về lỗi; dùng IFERROR để xử lý).

6. Hàm FILTER (Excel 365, Excel 2026)

6.1 Cú pháp

FILTER(array, include, if_empty)

• array: Dữ liệu gốc.
• include: Mảng điều kiện.
• if_empty: Giá trị trả về nếu không có dữ liệu thỏa.

6.2 Kết hợp với MAX

=MAX(FILTER(D2:D6, (B2:B6="Ngọc")(C2:C6="Bắc")))

• Nếu không có dữ liệu thỏa, FILTER sẽ trả lỗi #CALC!. Để tránh:

=IFERROR(MAX(FILTER(D2:D6, (B2:B6="Ngọc")(C2:C6="Bắc"))), "Không có dữ liệu")

6.3 Lợi thế

• Công thức động, không cần CSE.
• Dễ đọc, dễ bảo trì.
• Hỗ trợ đa điều kiện phức tạp, bao gồm OR bằng +.

6.4 Ví dụ thực tế: Lọc theo ngày

Giả sử muốn lấy doanh thu lớn nhất trong tháng 1/2026 cho khu vực Bắc:

=MAX(FILTER(D2:D100, (C2:C100="Bắc")(MONTH(A2:A100)=1)(YEAR(A2:A100)=2026)))

7. Các Trường Hợp Thực Tế Phổ Biến

7.1 Lấy max trong từng nhóm (Group By)

Ví dụ: Tìm doanh thu cao nhất của mỗi nhân viên.

7.1.1 Dùng Pivot Table

• Chèn Pivot Table → Rows: Nhân viên → Values: Max của Doanh thu.

7.1.2 Dùng công thức (Excel 365)

=LET( emp, UNIQUE(B2:B100), result, MAP(emp, LAMBDA(e, MAX(FILTER(D2:D100, B2:B100=e)))), HSTACK(emp, result)
)

• UNIQUE lấy danh sách nhân viên duy nhất.
• MAP áp dụng hàm tính max cho mỗi nhân viên.
• HSTACK ghép kết quả thành bảng 2 cột.

7.1.3 Dùng công thức mảng truyền thống

=IFERROR(INDEX(B$2:B$100, MATCH(0, COUNTIF($F$1:F1, B$2:B$100),0)), "")

Sau đó dùng công thức MAXIF cho mỗi nhân viên.

7.2 Max với điều kiện “có ít nhất N lần xuất hiện”

Giả sử muốn tìm giá trị lớn nhất xuất hiện ít nhất 3 lần trong cột A.

=MAX(FILTER(A2:A100, COUNTIF(A2:A100, A2:A100)>=3))

7.3 Max trong một khoảng thời gian động

Lấy doanh thu lớn nhất trong 30 ngày gần nhất so với ngày hiện tại (hôm nay là ô TODAY()).

=MAX(FILTER(D2:D100, (A2:A100>=TODAY()-30)(A2:A100<=TODAY())))

7.4 Max khi thỏa mãn nhiều tiêu chí văn bản (OR)

Ví dụ: Tìm max doanh thu khi khu vực là Bắc hoặc Nam.

=MAX(FILTER(D2:D100, (C2:C100="Bắc")+(C2:C100="Nam")))

8. Tối Ưu Hóa Hiệu Năng Khi Dữ Liệu Lớn

Khi làm việc với hàng ngàn, hàng triệu dòng, một số kỹ thuật sau giúp giảm thời gian tính toán:

1. Giảm phạm vi tính toán: Đừng dùng toàn cột (A:A) nếu dữ liệu chỉ tới hàng 5000. Sử dụng bảng Excel (Ctrl+T) để tự động mở rộng.
2. Sử dụng bảng tính (Table) và cấu trúc tham chiếu: =MAXIFS(Table1Doanh thu, Table1Nhân viên, "Ngọc").
3. Tránh lồng nhiều hàm mảng: Ưu tiên MAXIFS hoặc FILTER vì chúng tận dụng tính toán động.
4. Sử dụng AGGREGATE để bỏ qua lỗi thay vì IFERROR trên toàn mảng.
5. Tối ưu công thức dạng SUMPRODUCT: Đảm bảo các mảng có cùng kích thước, tránh tính toán không cần thiết.

9. So Sánh Các Phương Pháp

10. Thực Hành: Bài Tập Tự Làm

1. Bài 1: Trong bảng chi phí, tìm chi phí lớn nhất cho dự án “A” trong tháng 3/2026.
2. Bài 2: Tìm giá trị cao nhất của cột “Điểm” mà “Tên” bắt đầu bằng chữ “L”.
3. Bài 3: Sử dụng AGGREGATE để lấy top 5 doanh thu của khu vực “Trung” mà ngày bán hàng nằm trong năm 2026.
4. Bài 4: Dùng FILTER để lấy danh sách các nhân viên có doanh thu trung bình > 2000 và sau đó tính max doanh thu của họ.
5. Bài 5: Viết công thức mảng không dùng CSE để lấy max giá trị trong cột “Số lượng” khi “Trạng thái” = “Hoàn thành”.

Gợi ý: Áp dụng các công thức đã học ở phần 2‑7.

11. Kết Luận

Việc tìm MAX với điều kiện trong Excel không còn là thách thức khi bạn nắm vững các công cụ sẵn có. Từ hàm MAXIFS hiện đại, tới các công thức mảng truyền thống, SUMPRODUCT, AGGREGATE, và FILTER, mỗi phương pháp đều có ưu, nhược điểm riêng phù hợp với môi trường làm việc và phiên bản Excel của bạn.

• Nếu bạn dùng Excel 2026/Office 365: Hãy ưu tiên MAXIFS hoặc FILTER vì chúng ngắn gọn, nhanh và không cần CSE.
• Nếu bạn đang làm việc trên phiên bản cũ: Kết hợp MAX + IF (CSE) hoặc AGGREGATE để đạt hiệu suất tốt.
• Khi dữ liệu lớn: Sử dụng bảng (Table), giới hạn phạm vi, và ưu tiên các hàm không tạo lỗi (AGGREGATE, FILTER).

Cuối cùng, hãy luôn kiểm tra độ chính xác (IFERROR, IFNA) và độ linh hoạt (có bao nhiêu điều kiện) để công thức của bạn luôn ổn định trong mọi tình huống thực tế.

Chúc bạn thành công trong việc khai thác dữ liệu và đưa ra những quyết định thông minh dựa trên giá trị MAX có điều kiện trong Excel!

Tiến sĩ Lê Thống Nhất

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ẻ.