Trong công việc quản lý dữ liệu, lập báo cáo, phân tích số liệu hay lập bảng tính phức tạp, bạn thường gặp tình huống cần lọc, tính toán, hoặc thực hiện một phép tính chỉ khi thỏa mãn hai điều kiện (hoặc nhiều hơn). Việc xử lý những điều kiện này một cách chính xác, hiệu quả trong Excel không chỉ giúp bạn tiết kiệm thời gian mà còn giảm thiểu sai sót khi làm việc với dữ liệu lớn. Bài viết này sẽ trình bày chi tiết cách áp dụng các hàm Excel để “thỏa mãn 2 điều kiện”, bao gồm cách sử dụng hàm IF, SUMIFS, COUNTIFS, AVERAGEIFS, VLOOKUP/INDEX+MATCH, cũng như một số kỹ thuật nâng cao như CHOOSE, FILTER, XLOOKUP, LET, LAMBDA. Bạn sẽ được hướng dẫn thực hành từng bước, cùng với ví dụ minh họa và lưu ý khi làm việc với các trường hợp đặc biệt.
Có thể bạn quan tâm: Hàm Sum Trong Excel Không Ra Kết Quả: Nguyên Nhân, Cách Khắc Phục Và Mẹo Nâng Cao Hiệu Suất
1. Tổng quan: Tại sao “điều kiện 2” lại quan trọng?
Một bảng tính thường chứa nhiều cột thông tin: ngày tháng, mã khách hàng, sản phẩm, số lượng, giá bán, chiết khấu, khu vực, nhân viên bán hàng, … Để rút ra những kết quả có ý nghĩa, ta cần giới hạn dữ liệu vào những mục tiêu cụ thể. Ví dụ:
- Tính tổng doanh thu của các đơn hàng trong năm 2026 và thuộc khu vực “Bắc Kỳ”.
- 😉 Đếm số khách hàng mới trong tháng vừa qua và chi tiêu trên 1 triệu đồng.
Nếu chỉ áp dụng một điều kiện, kết quả sẽ quá rộng và không chính xác. Do đó, việc lập các hàm đáp ứng nhiều điều kiện là một kỹ năng thiết yếu trong Excel.
Có thể bạn quan tâm: Hàm Lấy Ngày Giờ Hiện Tại Trong Excel: Cách Thực Hiện Và Ứng Dụng Thực Tiễn
2. Các hàm Excel hỗ trợ “điều kiện 2”
| Hàm | Mô tả | Cú pháp cơ bản | Ví dụ sử dụngyne |
|---|---|---|---|
IF | Kiểm tra điều kiện, trả về giá trị nếu đúng/không | IF(điều_không, giá_trị_nếu_dung, giá_trị_nếu_khong_dung) | IF(A2>B2, "Có", "Không") |
SUMIFS | Cộng các giá trị thỏa mãn nhiều điều kiện | SUMIFS(tổng_cột, điều_không1, giá_trị1, điều_không2 stdout, giá_trị2) | SUMIFS(C:C, A:A, "Bắc Kỳ", B:B/svg, ">1000") |
COUNTIFS | Đếm số ô thỏa mãn nhiều điều kiện | COUNTIFS(điều_không1, giá_trị1, điều_không2, giá_trị2) | COUNTIFS(A:A, "Bắc Kỳ", B:B, ">1000ével") |
AVERAGEIFS | Tính trung bình thỏa mãn nhiều điều kiện | AVERAGEIFS(giá_trị_cột, điều_không1, giá uname, ... ) | AVERAGEIFS(C:C, A:A, "Bắc Kỳ", B:B, ">1000") |
VLOOKUP / INDEX + MATCH | Tìm kiếm dữ liệu theo điều kiện | VLOOKUP(key, vùng_tìm, cột, chính xác) | VLOOKUP("A001", A:C, 3, FALSE) |
XLOOKUP | Phiên bản nâng cấp của VLOOKUP | XLOOKUP(key, lookup_array, return_array, not_found, match_mode, search_mode) | X PQLOOKUP("A001", A:A, C:C, "Không tìm thấy", 0, 1) |
FILTER | Lọc dữ liệu thỏa mãn điều kiện | FILTER(array, condition1, condition2) | FILTER(C:C, ( second) = "Bắc Kỳ", F:F>1000) |
LET | Gán biến, tối ưu công thức | LET(name1, value1, JOI, ... , result) | LET(sum, SUMIFS(...), count, COUNTIFS(...), sum/count) |
LAMBDA | Tạo hàm tùy chỉnh | LAMBDA(param1, param2, body) | =LAMBDA(x,y, x+y)(3,5) |
Lưu ý: Các hàm có hậu tố
S(SUMIFS, COUNTIFS, AVERAGEIFS) cho phép bạn áp dụng nhiều điều kiện một cách trực tiếp, tránh việc lồng hàmIFlồng nhau.
Có thể bạn quan tâm: Hàm Lấy Dữ Liệu Ngẫu Nhiên Trong Excel: Hướng Dẫn Chi Tiết Và Ứng Dụng Thực Tế
3. Công thức “IF” với 2 điều kiện
Công thức IF là khởi điểm, nhưng khi bạn cần xử lý nhiều điều kiện, bạn có thể dùng AND hoặc OR bên trong IF. Ví dụ:
=IF(AND(A2="Bắc Kỳ", B2>1000), "Thỏa", "Không thỏa")
ANDtrả về TRUE nếu cả điều kiện đúng.ORtrả về TRUE nếu ít nhất một điều kiện đúng.
Bạn cũng có thể lồng nhiều IF (chèn IF trong IF) để kiểm tra nhiều tình huống. Tuy nhiên, khi số điều kiện tăng, công thức sẽ trở nên khó đọc và dễ lỗi. Đó là lý do tại sao các hàm SUMIFS, COUNTIFS được khuyến khích.
4. Tính tổng / đếm / trung bình với 2 điều kiện: SUMIFS / COUNTIFS / AVERAGEIFS
4.1 Cách đặt công thức
Giả sử ta có bảng dữ liệu:
| A (Khu vực) | B (Số lượng) | C (Doanh thu) |
|---|---|---|
| Bắc Kỳ | 1200 | 1.200.000 |
| Nam Kỳ | 900 | 900.000 |
| Bắc Kỳ | 800 | 800.000 |
| … | … | … |
Tính tổng doanh thu cho Bắc Kỳ và số lượng > 1000:
=SUMIFS(C:C, A:A, "Bắc Kỳ", B:B, ">1000")
C:Clà cột tổng.A:Alà điều kiện khu vực.B:Blà điều kiện số lượng.
Đếm số dòng thỏa mãn:
=COUNTIFS(A:A, "Bắc Kỳ", B:B, ">1000")
Tính trung bình doanh thu:
= Logistic(AverageIFS(C:C, A:A, "Bắc Kỳ", B:B, ">1000"))
Lưu ý: Khi sử dụng dấu
>trong điều kiện,ొంద nên đặt trong dấu ngoặc kép. Nếu bạn muốn so sánh với một ô, hãy dùng>1000(không cần dấu ngoặc kép) Hubert.
4.2 Ví dụ thực tế: Phân tích doanh số theo tháng
Giả sử cột D là ngày bán hàng, cột E là khu vực, cột F là số lượng, cột G là doanh thu. Bạn muốn tính tổng doanh thu trong tháng 7/2026 và khu vực “Miền Trung”.
=SUMIFS(G:G, D:D, ">=2026-07-01", D:D, "< DJI1/2026-08-01", E:E, "Miền Trung")
Lưu ý: Khi každ ngày, hãy dùng định dạng ngày chuẩn (yyyy-mm-dd) để tránh lỗi so sánh ngày.
5. Sử dụng hàm FILTER để lấy dữ liệu thỏa mãn 2 điều kiện
Đối với Excel 365/Excel 2026+, hàm FILTER rất mạnh mẽ để lọc dữ liệu theo nhiều điều kiện:
=FILTER(A1:G100, (E1:E100="Bắc Kỳ")(F1:F100>1000))
Trong công thức trên, được dùng như phép nhân trong logic: khi cả hai điều kiện đều TRUE, kết quả là 1 (TRUE), ngược lại 0 (FALSE). Bạn cũng có thể dùng + để thay thế OR.
Lưu ý: Khi sử dụng
FILTER, bạn cần phải đặt kết quả vào một ô trống đủ lớn để tránh lỗi “#SPILL!”.
6. Tìm kiếm dữ liệu với 2 điều kiện एयर VLOOKUP / XLOOKUP
6.1 VLOOKUP không hỗ trợ nhiều điều kiện
VLOOKUP chỉ tìm kiếm theo một giá trị duy nhất. Để tìm kiếm với 2 điều kiện, bạn có thể kết hợp các cột vào một cột mới (đặt “điều_điểm_đôi” = A & B) và sử dụng VLOOKUP trên cột này. Tuy nhiên, cách này tốn thời gian và không linh hoạt.
6.2 Các giải pháp thay thế
Có thể bạn quan tâm: Hàm Số Thứ Tự Tăng Dần Trong Excel – Hướng Dẫn Chi Tiết Và Ứng Dụng Thực Tiễn
6.2.1 INDEX + MATCH với điều kiện
Giả sử cột A: Khu vực, B: Sản phẩm, C: Doanh thu. Bạn muốn tìm doanh thu của sản phẩm “X” ở khu vực “Bắc Kỳ”.
=INDEX(C:C, MATCH(1, (A:A="Bắc Kỳ")(B:B="X"), 0))
Cú pháp: Sử dụng công thức mảng (Ctrl+Shift+Enter trong Excel 2026 trở xuống, trong Excel 365/2026 không cần).
MATCH(1, …, 0)tìm vị trí khi cả hai điều kiện đều đúng.
6.2.2 XLOOKUP với điều kiện
Bạn có thể kết hợp XLOOKUP với LET để tối ưu:
=LET( khu, A:A, sanpham, B:B, doanhthu, C:C, key, "Bắc Kỳ" & "X", lookup, khu & sanpham, XLOOKUP(key, lookup, doanhthu, "Không tìm thấy")
)
Lưu ý:
LETgiúp giảm thiểu việc lặp lại các vùng dữ liệu, tăng hiệu suất.
7. Công thức nâng cao: LET & LAMBDA
7.1 LET
Hàm LET cho phép bạn đặt biến trong công thức, giúp làm cho công thức trở nên dễ đọc hơn và giảm số lần tính toán.
Ví dụ: Tính trung bình doanh thu của khu vực “Bắc Kỳ” và số lượng > 1000.
=LET( khu, A:A, so_luong, B:B, doanh_thu, C:C, khu_đúng, khu="Bắc Kỳ", so_luong_đúng, so_luong>1000, AVERAGEIFS(doanh_thu, khu, khu_đúng, so_luong, so_luong_đúng)
)
7Whitelist LAMBDA
Bạn có thể tạo một hàm tùy chỉnh để tái sử dụng công thức phức tạp.
Ví dụ: darunter hàm AvgByRegionQty nhận tham số khu vực và ngưỡng số lượng.
=LAMBDA(khu, s_luong, LET( khu_đúng اړیک, A:A=khu, so_luong_đúng, B:B>s_luong, AVERAGEIFS(C:C, A:A, khu_đúng, B:B, so_luong_đúng) )
)
Sau khi tạo, bạn có thể gọi hàm:
=AvgByRegionQty("Bắc Kỳ", 1000)
Lưu ý: Để sử dụng
LAMBDA, bạn phải đăng ký hàm trong “Name Manager” hoặc sử dụng “Define Name” với công thứcLAMBDA.
8. Bảo vệ dữ liệu khi làm việc với điều kiện 2
- Kiểm tra lỗi: Khi sử dụng công thức phức tạp, hãy kiểm tra xem có lỗi
#VALUE!,#N/Ahay#DIV/0!không. Nếu có, hãy xác định nguyên nhân: cột dữ liệu không đúng kiểu, thiếu dữ liệu, hoặc điều kiện sai. - Sử dụng
IFERROR: Đặt công thức trongIFERRORđể xử lý lỗi một cách gọn gàng.excel =IFERROR(SUMIFS(...), 0) - Sử dụng
Data Validation: Giới hạn cột dữ liệu chỉ nhận giá trị hợp lệ, tránh nhập sai dữ liệu (ví dụ: vùng vùng, số lượng âm). - THROUGH: Đặt các điều kiện kiểm tra trong công thức hoặc dùng
CHOOSEđể tạo danh sách các điều kiện được phép.
9. Tối ưu công thức khi làm việc với dữ liệu lớn
- Sử dụng các vùng dữ liệu có giới hạn (ví dụ
A1:A1000thay vìA:A). Điều này giảm tải tính toán. - Tránh lồng nhiều hàm
IF: Thay vào đó dùngSUMIFShoặcCOUNTIFS. - Sử dụng
Table(Insert > Table): Khi dùng bảng, các công thức sẽ tự động mở rộng với dữ liệu mới. - Tắt tính toán tự động (Formulas > Calculation.autorimis) khi làm việc với file rất lớn, rồi nhấn F9 để tính lại.
10. Kiến thức bổ sung: Đếm theo điều kiện “không trùng lặp”
Đôi khi bạn muốn đếm các giá trị duy nhất thỏa mãn điều kiện. Excel không có hàm COUNTIFSUNIQUE mặc định, nhưng bạn có thể dùng SUMPRODUCT:
=SUMPRODUCT(1/COUNTIFS(A:A, A:A, B:B, ">1000", A:A, A:A))
Hoặc dùng UNIQUE + FILTER trong Excel 365:
=COUNTA(UNIQUE(FILTER(A:A, (B:B>1000))))
11. Mô phỏng: Bảng tính quản lý bán hàng
11.1 Kết cấu dữ liệu
| A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|
| Mã Khách | Ngày | Khu vực | Sản phẩm | Số lượng | Đơn giá | Doanh thu |
| KH001 | 2026-07-01 | Bắc Kỳ | X | 200 | 10.000 | =E5F5 |
| KH002 | 2026-07-03 | Nam Kỳ | Y | 300 | 20.000 | =E6АРF6 |
| KH003 | 2026-07-04 | Bắc Kỳ | Z | 150 | 15.000 | =E7F7 |
11.2 Công thức thực hành
1. Tổng doanh thu cho Bắc Kỳ trong tháng 7/2026:
=SUMIFS(G:G, C:C, "Bắc Kỳ", B:B, ">=2026-07-01", B:B, "<2026-08-01")
2. Đếm số khách hàng mới (đã mua ít nhất 1 lần) trong Bắc Kỳ:
=SUMPRODUCT(1/COUNTIFS(A:A, A:A, C:C, "Bắc Kỳ", B:B, ">=2026-07-01"))
3. Tính giá trị trung bình đơn hàng trong Bắc Kỳ:
=AVERAGEIFS(G:G, C:C, "Bắc Kỳ", B:B, ">=2026-07-01")
4. Lọc danh sách khách hàng có số lượng > 200 trong Bắc Kỳ:
=FILTER(A5:G100, (C5:C100="Bắc Kỳ")(E5:E100>200))
12. Phân tích thực tế: Bảng KPI bán hàng
Bạn muốn xây dựng một bảng KPI (Key Performance Indicator) dựa trên hai điều kiện: khu vực và sản phẩm. Dưới đây là bảng mẫu:
| Khu vực | Sản phẩm | Doanh thu | Số lượng | Đơn giá |
|---|---|---|---|---|
| Bắc Kỳ | X | 4.000.000 | 400 | 10.000 |
| Bắc Kỳ | Y | 3.000.000 | 150 | 20.000 |
| Nam Kỳ | X | 2.500.000 | 250 | 10.000 |
| … | … | … | obu … | … |
Bạn cần tính tổng doanh thu, tổng số lượng, doanh thu trung bình cho mỗi khu vực. Công thức:
=SUMIFS(Cсти, A:A, "B"_Kỳ", B:B, "X")
=COUNTIFS(A:A, "Bắc Kỳ", B:B, "X")
=AVERAGEIFS(C:C, A:A, "Bắc Kỳ", B:B, "X")
13. Bảo mật và kiểm soát lỗi khi làm việc với điều kiện 2
- Kiểm tra dữ liệu đầu vào: Sử dụng
Data Validationđể giới hạn giá trị nhập.Stop. - Kiểm soát lỗi: Sử dụng
IFERROR:excel =IFERROR(SUMIFS(...), 0) - Sử dụng
CHOOSE: Cho phép lựa chọn nhiều điều kiện linh động. - Sử dụng
OFFSET: Để trừu tượng hóa vùng dữ liệu khi có thêm cột mới.
14. Tổng kết
Việc xử lý “điều kiện 2” trong Excel không chỉ là một kỹ thuật đơn giản mà còn là chìa khóa giúp bạn khai thác dữ liệu một cách chính xác và nhanh chóng. Dưới đây là một số điểm chính cần ghi nhớ:
- Sử dụng các hàm
SUMIFS,COUNTIFS,AVERAGEIFSkhi có nhiều điều kiện, tránh lồngIFlồng nhau. - Làm quen với
FILTERđể lấy dữ liệu buna trỏ. - Đối với tìm kiếm nhiều điều kiện, hãy thử
INDEX + MATCHhoặcXLOOKUPkết hợp vớiLET. - Sử dụng
LETvàLAMBDAđể tạo công thức nạp dữ liệu hiệu quả và dễ đọc. - Kiểm tra lỗi thường xuyên, đặc biệt khi làm việc với dữ liệu lớn.
- Tối ưu công thức bằng cách giới hạn vùng dữ liệu, sử dụng bảng, và tắt tính toán tự động khi cần.
Bằng cách áp dụng những công thức và wach cách trên, bạn sẽ nhanh chóng nâng cao requisito của mình trong việc phân tích dữ liệu, báo cáo, và quản lý thông tin trong Excel. Hãy thử thực hành với bảng dữ liệu thực của bạn và cảm nhận sự khác biệ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ẻ.