Trong môi trường làm việc hiện đại, Excel đã trở thành công cụ quan trọng để xử lý dữ liệu, phân tích và thống kê. Một trong những nhiệm vụ thường gặp khi làm việc với bảng tính là đếm số ô trống trong một vùng dữ liệu. Tuy nhiên, trong thực tiễn, chúng ta thường cần đếm ô trống điều kiện: chỉ đếm những ô trống mà đáp ứng một tiêu chí nhất định, chẳng hạn như ô trống trong cột ngày tháng, ô trống trong cột số lượng, hoặc ô trống khi một cột liên quan có giá trị nhất định.
Bài viết này sẽ hướng dẫn chi tiết cách thực hiện việc đếm ô trống có điều kiện trong Excel bằng nhiều phương pháp khác nhau, từ công thức đơn giản như COUNTBLANK và COUNTIF đếnնել ấn sâu hơn với COUNTIFS, mảng công thức, VBA, Power Query, và các công cụ nâng cao khác. Ngoài ra, chúng ta sẽ khám phá các tình huống thực tiễn, mẹo vặt, và cách tối ưu hiệu suất khi làm việc với tập dữ liệu lớn.
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
1. Giới thiệu khái quát về ô trống trong Excel
1.1. Loại ô trống trong Excel
- Ô trống hoàn toàn: Không chứa dữ liệu, không chứa công thức, không chứa công thức trả về chuỗi rỗng (
""). - Ô trống giả: Ghi lại chuỗi rỗng (
"") từ công thức, mặc dù nhìn trông trống, nhưng trong thực tế chứa dữ liệu. - Ô có giá trị “0” nhưng trống trong giao diện: Khi một công thức trả về
0và được định dạng làGeneral, ô sẽ hiển thị0. Khi định dạng làCustom(#,##0.00;Red-#,##0.00) thì0sẽ hiển thị trống.
1.2. Khi nào cần đếm ô trống có điều kiện?
- Kiểm tra tính đầy đủ dữ liệu: Đếm ô trống trong một cột quan trọng (ví dụ: ngày giao hàng) có điều kiện theo trạng thái đơn hàng.
- Tạo báo cáo thống kê: Đếm số ô trống trong các phần của bảng dữ liệu để báo cáo tình hình.
- Lọc dữ liệu: Đếm ô trống trong cột so với một cột khác để xác định mức độ thiếu hụt.
- Tối ưu hóa công thức: Khi tính tổng hoặc trung bình, cần biết số ô trống để tránh sai số.
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
2. Công thức cơ bản để đếm ô trống
2.1. COUNTBLANK
Công thức COUNTBLANK đếm số ô trống trong một vùng dữ liệu. Cú pháp:
COUNTBLANK(range)
Ví dụ: Đếm ô trống trong cột A từ A2 đến A100:
=COUNTBLANK(A2:A100)
Nhưng COUNTBLANK không hỗ trợ điều kiện. Để đếm ô trống có điều kiện, ta cần kết hợp với các công thức khác.
2.2. COUNTIF và COUNTIFS
2.2.1. COUNTIF
Đếm số ô thỏa mãn điều kiện duy nhất. Cú pháp:
COUNTIF(range, criteria)
Nếu muốn đếm ô trống, ta viết:
=COUNTIF(A2:A100, "")
Tuy nhiên, điều này sẽ đếm cả ô trống giả (có công thức trả về ""). Để tránh, ta cần dùng TRIM hoặc LEN.
2.2.2. COUNTIFS
Đếm số ô thỏa mãn nhiều điều kiện. Cú pháp:
COUNTIFS(range1, criteria1, range2, criteria2, ...)
Ví dụ: Đếm ô trống trong cột A khi cột B có giá trị “Hoàn thành”:
=COUNTIFS(A2:A100, "", B2:B100, "Hoàn thành")
Trong trường hợp cột A chứa công thức trả về chuỗi rỗng, ta có thể dùng TRIM/LEN để loại bỏ:
=COUNTIFS(A2:A100, "", B2:B100, "Hoàn thành") // Nếu A2:A100 chứa chuỗi rỗng
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
3. Đếm ô trống có điều kiện nâng cao
3.1. Đếm ô trống khi cột liên quan có giá trị số > 0
Giả sử cột A chứa ngày giao hàng (có thể trống), cột B chứa số lượng. Đếm số ô trống trong A nhưng số lượng trong B > 0:
=COUNTIFS(A2:A100, "", B2:B100, ">0")
Nếu muốn tínhunik tính với AND hay OR, ta có thể dùng SUMPRODUCT.
3.2. Sử dụng SUMPRODUCT để đếm ô trống có điều kiện phức tạp
SUMPRODUCT cho phép tính tổng các giá trị logic, rất mạnh trong việc xử lý điều kiện phức tạp.
Ví dụ: Đếm ô trống trong cột A khi cột B là “Hoàn thành” và cột C > 10:
=SUMPRODUCT(--(A2:A100=""), --(B2:B100="Hoàn thành"), --(C2:C100>10))
Giải thích:
– --(A2:A100="") chuyển mảng logic TRUE/FALSE thành 1/0.
– SUMPRODUCT tính tổng các tích, tức là đếm số dòng thỏa mãn cả ba điều kiện.
3.3. Đếm ô trống jub vì chuỗi rỗng giả
Nếu cột A chứa công thức trả về "", nhưng ta muốn đếm chỉ ô trống thực sự, ta dùng LEN:
=COUNTIFS(A2:A100, "", B2:B100, "Hoàn thành") // Đếm cả ô trống giả
=SUMPRODUCT(--(LEN(A2:A100)=0), --(B2:B100="Hoàn thành")) // Đếm ô trống thực sự
3.4. Đếm ô trống khi có điều kiện dựa trên dữ liệu trong một bảng tham chiếu
Giả sử chúng ta có bảng tham chiếu TableStatus với các trạng thái hợp lệ. Cần đếm ô trống trong cột A khi trạng thái trong cột B nằm trong danh sách TableStatus.
Công thức:
=SUMPRODUCT(--(A2:A100=""), --(ISNUMBER(MATCH(B2:B100, TableStatusStatus, 0))))
MATCHtrả về vị trí trong danh sách nếu tìm thấy.ISNUMBERchuyển thànhTRUE/FALSE.SUMPRODUCTđếm.
4. Đếm ô trống có điều kiện bằng công thức mảng
Trong Excel 365 và 2026, bạn có thể dùng công thức mảng động (Dynamic Array).
Ví dụ: Đếm ô trống trong cột A khi cột B > 0:
=SUM(--(A2:A100=""), --(B2:B100>0))
Nhấn Ctrl+Shift+Enter trong phiên bản Excel cũ, hoặc nhấn Enter trong Excel 365.
Công thức mảng FILTER cũng có thể được dùng:
=COUNTA(FILTER(A2:A100, (A2:A100="")(B2:B100>0)))
Nếu muốn đếm ô trống trong cột A bằng FILTER:
=COUNTA(FILTER(A2:A100, (A2:A100="")(B2:B100>0)))
5. Đếm ô trống có điều kiện bằng VBA
Trong những trường hợp phức tạp hoặc khi cần tự động hóa, VBA là lựa chọn tốt.
5.1. Macro đơn giản
Function CountBlankConditional(rngData As Range, rngCond As Range, condValue As Variant) As Long Dim i As Long, counter As Long For i = 1 To rngData.Rows.Count If Len(rngData.Cells(i, 1).Value) = 0 Then If rngCond.Cells(i, 1).Value = condValue Then counter = counter + 1 End If End If Next i CountBlankConditional = counter
End Function
Sử dụng trong ô:
=CountBlankConditional(A2:A100, B2:B100, "Hoàn thành")
5.2. Macro nâng cao
Nếu muốn đếm ô trống khi có nhiều điều kiện:
Function CountBlankMulti(rngData As Range, ParamArray criteria() As Variant) As Long Dim i As Long, j As Long, match As Boolean, counter As Long For i = 1 To rngData.Rows.Count If Len(rngData.Cells(i, 1).Value) = 0 Then match = True For j = LBound(criteria) To UBound(criteria) Step 2 Dim rngCond As Range: Set rngCond = criteria(j) Dim condVal As Variant: condVal = criteria(j + 1) If rngCond.Cells(i, 1).Value <> condVal Then match = False Exit For End If Next j If match Then counter = counter + 1 End If Next i CountBlankMulti = counter
End Function
Sử dụng:
=CountBlankMulti(A2:A100, B2:B100, "Hoàn thành", C2:C100, ">10")
6. Đếm ô trống có điều kiện qua Power Query
Power Query (Get & Transform) là công cụ mạnh để xử lý dữ liệu lớn. Bạn có thể lọc, đếm và tính toán mà không cần công thức.
6.1. Bước 1: Nhập dữ liệu vào Power Query
Có thể bạn quan tâm: Đếm Số Lượng Có Điều Kiện Trong Excel: Hướng Dẫn Chi Tiết Và Thực Hành
- Chọn vùng dữ liệu, vào
Data > From Table/Range. - Đảm bảo dữ liệu có tiêu đề.
6.2. Bước 2: Thêm cột điều kiện
Giả sử bạn muốn đếm ô trống trong cột Ngày Giao khi Trạng thái = “Hoàn thành”.
- Sử dụng
Add Column > Custom Column. - Đặt tên:
IsBlankAndComplete. - Công thức:
m if Text.Trim(Ngày Giao) = "" and Trạng thái = "Hoàn thành" then 1 else 0
6.3. Bước 3: Tính tổng
Group By: Không nhóm, chỉ tính tổng.Add Aggregation:Sumcủa cộtIsBlankAndComplete.
Kết quả là số ô trống có điều kiện.
6.4. Lưu và tải dữ liệu trở lại Excel
Close & Load-> Tải kết quảvada.
7. Đếm ô trống có điều kiện trong PivotTable
PivotTable thường dùng để Ianđếm tổng, nhưng bạn cũng có thể đếm ô trống.
- Tạo PivotTable từ dữ liệu gốc.
- Kéo cột cần đếm vào vùng
Rows. - Kéo cột điều kiện vào
Filters. - Kéo cột cần đếm vào
Valuesvà chọnCount. - Trong cột điều kiện, chọn giá trị cần lọc.
Ví dụ: Đếm số ô trống trong cột Ngày Giao khi Trạng thái = “Hoàn thành”.
- Kéo
Ngày Giaovào Rows. - Kéo
Trạng tháivào Filters, chọn “Hoàn thành”. - Kéo
Ngày Giaovào Values, chọnCount(hoặcCountBlanknếu có tùy chọn).
PivotTable sẽ hiển thị số lượng ô trống trong cột Ngày Giao.
8. Kiểm tra và xử lý ô trống giả
8.1. Ô trống giả có thể gây nhầm lẫn trong công thức
Nếu một ô chứa công thức =IF(A1="", "", B1) thì ô đó trông trống nhưng thực chất chứa chuỗi rỗng. COUNTBLANK sẽ không đếm được nó.
8.2. Kiểm tra ô trống giả bằng LEN
=IF(LEN(A1)=0, "Trống", "Không trống")
8.3. Xóa ô trống giả
Bạn có thể dùng Find & Replace:
– Ctrl+H -> Find what = "" (chuỗi rỗng).
– Replace with = (rỗng).
– Replace All.
9. Mẹo vặt và tối ưu hiệu suất
9.1. Tránh tính toán lặp lại
- Sử dụng
OFFSEThoặcINDEXthay vìVLOOKUPnhiều lần. - Dùng bảng tham chiếu (
Table) để tự động cập nhật phạm vi.
9.2. Sử dụng FILTER và COUNTA trong Excel 365
=COUNTA(FILTER(A2:A100, (A2:A100="")(B2:B100="Hoàn thành")))
FILTERtạo mảng theo điều kiện.COUNTAđếm số dòng trong mảng.
9.3. Đặt tên vùng dữ liệu
- Đặt tên cho vùng dữ liệu bằng
Name ManagerhoặcDefine Name. - Ví dụ:
DataRange,StatusList. - Dùng trong công thức:
=COUNTIFS(DataRange’usage, “”, StatusListStatus, “Hoàn thành”)`.
9.4. Sử dụng IFERROR để tránh lỗi
Nếu có ô lỗi trong dữ liệu, COUNTIFS có thể trả về lỗi. Bọc vào IFERROR:
=IFERROR(COUNTIFS(A2:A100, "", B2:B100, "Hoàn thành"), 0)
9.5. Các hàm tương đương
SUBTOTAL(103, range)đếm ô trống trong vùng đã lọc.AGGREGATE(3, options, range)có thể đếm ô trống, bỏ qua lỗi.
10. Tình huống thực tiễn: Đếm ô trống trong báo cáo doanh thu
10.1. Dữ liệu mẫu
| Mã Hàng | Tên Hàng | Số Lượng | Ngày Giao | Trạng thái |
|---|---|---|---|---|
| 001 | A | 10 | 01/01/2026 | Hoàn thành |
| 002 | B | 5 | Đang xử lý | |
| 003 | C | 0 | Hoàn thành | |
| 004 | D | 7 | 03/01/2026 | Hoàn thành |
| 005 | E | 2 | Hoàn thành | |
| 변 |
10.2. Câu hỏi: Đếm số đơn hàng chưa có ngày giao nhưng đã hoàn thành
Công thức COUNTIFS:
=COUNTIFS(D2:D6, "", E2:E6, "Hoàn thành")
Kết quả: 2 (Đơn hàng 003 và 005).
10.3. Câu hỏi: Đếm số đơn hàng chưa có ngày giao nhưng số lượng > 0
Công thức SUMPRODUCT:
=SUMPRODUCT(--(D2:D6=""), --(C2:C6>0))
Kết quả: 1 (Đơn hàng 001).
11. Tổng kết
Đếm ô trống có điều kiện trong Excel không chỉ đơn thuần là sử dụng პროდუქ COUNTBLANK. Nó đòi hỏi sự hiểu biết sâu rộng về công thức, mảng, vàប thức điều kiện. Bằng cách kết hợp COUNTIF, COUNTIFS, SUMPRODUCT, FILTER, và thậm chí là VBA hoặc Power Query, bạn có thể thực hiện mọi bài toán墙 đếm ô trống có điều kiện một cách nhanh chóng và chính xác.
Đừng quên kiểm tra các tình huống ô trống giả, tối ưu hiệu suất, và sử dụng bảng dữ liệu để tránh việc làm việc với phạm vi cố định. Khi làm việc với dữ liệu lớn, Power Query và PivotTable sẽ giúp bạn tiết kiệm thời gian đáng kể.
Hy vọng bài viết này đã cung cấp cho bạn các công cụ và kiến thức cần thiết để thực hiện việc đếm ô trống có điều kiện trong Excel một cách hiệu quả và linh hoạt. Happy Excel!
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ẻ.