Có thể bạn quan tâm: Đặt Điều Kiện Nhập Dữ Liệu Trong Excel: Hướng Dẫn Chi Tiết Và Tối Ưu
Giới thiệu
Trong công việc quản lý dữ liệu, báo cáo tài chính, lập kế hoạch bán hàng hoặc bất kỳ lĩnh vực nào yêu cầu xử lý dữ liệu số lượng lớn, việc đếm số lượng có điều kiện là một trong những thao tác cơ bản nhưng quan trọng nhất. Excel cung cấp nhiều công cụ và hàm mạnh mẽ giúp ta thực hiện công việc này một cách nhanh chóng và chính xác. Bài viết hôm nay sẽ đi sâu vào các hàm COUNTIF, COUNTIFS, SUMPRODUCT, cùng một số phương pháp nâng cao như công thức mảng, vùng tên động, và PivotTable. Bạn sẽ học được cách áp dụng từng công thức vào các tình huống thực tế, tránh những lỗi thường gặp và tối ưu hiệu suất làm việc.
Có thể bạn quan tâm: Đặt Số Thứ Tự Tự Động Trong Excel: Hướng Dẫn Chi Tiết Và Các Mẹo Nâng Cao
1. Tổng quan về hàm đếm điều kiện trong Excel
1.1 COUNTIF
Hàm COUNTIF dùng để đếm số ô trong một vùng đáp ứng một điều kiện đơn giản. Cú pháp:
=COUNTIF(đoạn, điều_kiện)
đoạn– vùng cột/đoạn ô cần kiểm tra.điều_kiện– điều kiện có thể là số, chuỗi, biểu thức logic, hoặc tham chiếu ô.
Ví dụ: Đếm số khách hàng có tuổi > 30:
=COUNTIF(B2:B100, ">30")
1.2 COUNTIFS
Khi cần nhiều điều kiện đồng thời, sử dụng COUNTIFS. Cú pháp:
=COUNTIFS(đoạn1, điều_kiện1, đoạn2, điều_kiện2, ...)
Ví dụ: Đếm số khách hàng có tuổi > 30 và thuộc vùng “Bắc Kinh”:
=COUNTIFS(B2:B100, ">30", C2:C100, "Bắc Kinh")
1.3 SUMPRODUCT
SUMPRODUCT thường được dùng để tính tổng, nhưng cũng có thể đếm theo nhiều điều kiện phức tạp. Cú pháp:
=SUMPRODUCT((đoạn1=giá_trị1)(đoạn2=giá_trị2)...)
Ví dụ: Đếm số sản phẩm bán chạy (đơn giá > 500,000 VNĐ) và có tồn kho > 0:
=SUMPRODUCT((D2:D100>500000)(E2:E100>0))
1.4 Công thức mảng
Bạn có thể sử dụng công thức mảng (Ctrl+Shift+Enter trong Excel 2026 trở xuống) để thực hiện các điều kiện Clara, ví dụ:
=SUM(IF((B2:B100>30)(C2:C100="Bắc Kinh"),1,0))
Có thể bạn quan tâm: Đưa രീത Con Trỏ Về Ô A1 Trong Excel: Hướng Dẫn Chi Tiết Và Các Mẹo Hữu Ích
2. Các tình huống thực tế và cách áp dụng
2.1 Đếm số dòng có giá trị lớn hơn
Tình huống: Bạn có bảng dữ liệu bán hàng, cần đếm số giao dịch có doanh thu > 1,000,000 VND.
Công thức:
=COUNTIF(E2:E200, ">1000000")
Lưu ý: Đảm bảo dữ liệu trong cột E là số, không có chuỗi hoặc ký hiệu tiền tệ.
2.2 Đếm đa điều kiện với COUNTIFS
Tình huống: Đếm số sản phẩm tồn kho < 10 và giá bán > 200,000.
=COUNTIFS(F2:F200, "<10", G2:G200, ">200000")
Nếu cần nhiều điều kiện hơn nữa, chỉ cần thêm cặp đoạn, điều_kiện.
2.3 Đếm số dòng có chuỗi chứa cụm từ
Tình huống: Đếm số khách hàng có tên chứa từ “Lê”.
=COUNTIF(A2:A200, "Lê")
Ký tự là ký tự đại diện (wildcard) trong Excel.
2.4 Đếm số dòng có giá trị rỗng hoặc trống
=COUNTIF(H2:H200, "")
Hoặc dùng COUNTA để đếm số ô không rỗng:
=COUNTA(H2:H200)
2.5 Đếm số dòng theo ngày, tuần, tháng
Giả sử cột ngày ở I. Đếm số giao dịch trong tháng 7 năm 2026:
=SUMPRODUCT(--(MONTH(I2:I200)=7), --(YEAR(I2:I200)=2026))
Hoặc dùng TEXT:
=COUNTIF(TEXT(I2:I200,"mm-yyyy"), "07-2026")
異 ### 2.6 Đếm số dòng có giá trị duy nhất Nếu muốn tính số lượng duy nhất trong một cột, dùng `SUMPRODUCT Beet`: ```excel
=SUMPRODUCT(1/COUNTIF(A2:A200,A2:A200))
2.7 Đếm số dòng dựa trên điều kiện trong một ô tham chiếu
=COUNTIF(B2:B200, "=" & D1)
Trong đó, ô D1 chứa điều kiện.
3. Các lỗi thường gặp và cách khắc phục
| Lỗi | Nguyên gốc | Giải pháp |
|---|---|---|
#VALUE! | Kiểu dữ liệu không đồng nhất | Kiểm tra dữ liệu số/chuỗi |
#DIV/0 alloys | Dùng chia 0 trong công thức sienten | Kiểm tra điều kiện = 0 |
#NAME! | Tên điều kiện không hợp lệ | Kiểm tra cú pháp tên hàm |
#REF! | Tham tham vùng không tồn tại | Kiểm tra vùng tham chiếu |
#SPILL! | Dùng Excel 365, công thức mảng bị tràn | Kiểm tra vùng trống |
3.1 Kiểm tra dữ liệu
- Đảm bảo dữ liệu số không có ký hiệu chuỗi.
- Loại bỏ dấu thập phân sai định dạng.
- Kiểm tra dữ liệu trùng lặp khi dùng
COUNTIFnếu cần duy nhất.
3.2 Sử dụng TRIM, CLEAN, VALUE
=COUNTIF(VALUE(TRIM(A2:A100)), ">10")
museum
4. Tối ưu hiệu suất tính toán
Khi làm việc với các bảng lớn (thousands rows), công thức COUNTIFS và ntegre có thể làm chậm.
4.1 Sử dụng vùng tên động
Có thể bạn quan tâm: Hàm Lấy Ký Tự Có Điều Kiện Trong Excel: Giải Pháp Mạnh Mẽ Cho Phân Tích Dữ Liệu
=COUNTIFS(OFFSET($A$1,0,0,COUNTA($A:$A)), ">10")
4.2 Giảm số lần 면
Sử dụng một cột phụ tính giá trị biểu thức, sau đó đếm trên cột phụ:
=SUMPRODUCT(--(D2:D200="Yes"))
4.3 Sử dụng Power Query
Chuyển đổi dữ liệu sang Power Query, áp dụng lọc và đếm, rồi nạp trở lại Excel.
5. Đếm số lượng theo tiêu chí phức tạp hơn
5.1 Đếm số dòng có nhiều điều kiện tùy biến
=SUMPRODUCT(--(B2:B200>30), --(C2:C200="Bắc Kinh"), --(D2:D200<>""))
5.2 Đếm số dòng có giá trị trong danh sách
Giả sử danh sách cần kiểm tra nằm trong Z1:Z10:
=SUMPRODUCT(--(ISNUMBER(MATCH(B2:B200, Z1:Z10, 0))))
5.3 Đếm số dòng dựa trên công thức phức tạp
=SUMPRODUCT(--(IFERROR(VLOOKUP(B2:B200, $Y$1:$Z$10, 2, FALSE), "")="Approved"))
6. So sánh các hàm: COUNTIF, COUNTIFS, SUMPRODUCT
| Hàm | Tính năng | Khả năng mở rộng | Khối lượng dữ liệu | Tốc độ |
|---|---|---|---|---|
COUNTIF | Đếm điều kiện đơn | Giới hạn 1 điều kiện | Trung bình | Nhanh |
COUNTIFS | Đếm nhiều điều kiện | Dễ dàng thêm điều kiện炸 | Trung bình | Nhanh |
SUMPRODUCT | Đếm đa điều kiện phức tạp | Có thể dùng hàm mảng | Rất lớn | Thấp hơn |
ARRAY | Đếm tùy chỉnh | Mạnh mẽ, linh hoạt | Thấp | Thấp hơn |
7. Kết hợp với PivotTable
PivotTable cung cấp cách nhanh chóng đếm số lượng dữ liệu theo nhiều tiêu chí mà không cần công thức phức tạp.
- Chọn vùng dữ liệu.
- Đi tới
Insert>PivotTable. - Kéo trường cần tính đếm vào vùng
Valuesvà chọnCount. - Kéo trường cần lọc vào vùng
FiltershoặcRows.
PivotTable tự động tính số lượng và có thể dễ dàng thay đổi điều kiện lọc.
8. Thực hành: 5 bài tập thực tế
8.1 Bài 1: Đếm số khách hàng có tuổi > 已有
Dữ liệu: Cột A = Tên, B სოციალურ = Tuổi.
Công thức:
=COUNTIF(B2:B100, ">30")
8.2 Bài 2: Đếm số sản phẩm bán chạy
Dữ liệu: Cột C = Giá bán, D = Số lượng bán.
Điều kiện: Giá > 300,000 và số lượng > 20.
=COUNTIFS(C2:C200, ">300000", D2:D200, ">20")
8.3 Bài 3: Đếm số giao dịch trong tháng 8
Dữ liệu: Cột E = Ngày giao dịch (ngày thực).
=SUMPRODUCT(--(MONTH(E2:E200)=8), --(YEAR(E2:E200)=2026))
8.4 Bài 4: Đếm số nhân viên được đánh giá “Xuất sắc”
Dữ liệu: Cột F = Đánh giá.
=COUNTIF(F2:F200, "Xuất sắc")
8.5 Bài 5: Đếm số đơn hàng có mã khách hàng trong danh sách
Danh sách mã khách hàng: Z1:Z10.
=SUMPRODUCT(--(ISNUMBER(MATCH(G2:G200, Z1:Z10, 0))))
9. Mẹo nhanh: Sử dụng Ctrl+F để xác nhận kết quả
Sau khi tính được công thức, bạn có thể dùng Ctrl+F – tìm kiếm trong bảng để kiểm tra số lượng ô thỏa điều kiện. Ví dụ, nhập >30 vào ô tìm kiếm và chọn Find All để xem tất cả ô thỏa điều kiện.
10. Kết luận
Đếm số lượng có điều kiện trong Excel không chỉ là một thao tác cơ bản. Khi nhận thức được các hàm mạnh mẽ như COUNTIF, COUNTIFS, SUMPRODUCT, và công cụ PivotTable, bạn có thể xử lý dữ liệu khối lượng lớn một cách nhanh nhất và chính xác nhất. Hãy thực hành, thử nghiệm các ví dụ, và dần dần áp dụng vào công việc thực tế để nâng cao hiệu suất làm việc của mình.
Chúc bạn thành công trong việc khai thác sức mạnh của Excel để đếm số lượng có điều kiện một cách hiệu quả!
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ẻ.