Trong quá trình làm việc với Excel, việc quản lý dữ liệu sạch và chính xác luôn là một trong những yếu tố then chốt để đưa ra các quyết định đúng đắn. Một trong những thách thức thường gặp là đếm số ô trống (blank cells) trong một vùng dữ liệu. Dù bạn đang thực hiện phân tích dữ liệu, tạo báo cáo, hay chuẩn bị dữ liệu cho các công cụ khác, việc biết được có bao nhiêu ô trống sẽ giúp bạn:
- Xác định mức độ đầy đủ của dữ liệu.
- Phát hiện các lỗi nhập liệu hoặc thiếu sót.
- Áp dụng các công thức hoặc quy trình xử lý dữ liệu một cách chính xác.
- Tối ưu hoá báo cáo và biểu đồ, tránh hiển thị thông tin không cần thiết.
Bài viết này sẽ cung cấp cho bạn hướng dẫn chi tiết, đầy đủ và thực tế về cách đếm ô trống trong Excel, bao gồm:
- Khái niệm cơ bản về ô trống và các loại dữ liệu.
- Sử dụng hàm COUNTBLANK – hàm chuẩn và nhanh nhất.
- Kết hợp COUNTBLANK với các hàm khác (IF, SUMPRODUCT, FILTER, SUBTOTAL…) để đáp ứng các yêu cầu phức tạp.
- Đếm ô trống trong các phạm vi không liên tục (multiple ranges) và trong bảng (Excel Table).
- Sử dụng công cụ “Go To Special” và “Find” để đếm nhanh mà không cần công thức.
- Sử dụng PivotTable để thống kê ô trống.
- Đếm ô trống trong các ô chứa công thức trả về chuỗi rỗng (“” ).
- Sử dụng Power Query cho các tập dữ liệu lớn.
- Sử dụng VBA (Macro) để tự động đếm ô trống.
- Mẹo tối ưu hoá và tránh các lỗi thường gặp khi đếm ô trống.
Lưu ý: Bài viết này được viết cho người dùng Excel từ mức độ cơ bản đến nâng cao, vì vậy dù bạn chưa từng dùng hàm COUNTBLANK, hay đã là một chuyên gia Excel, bạn vẫn sẽ tìm thấy những thông tin hữu ích.
Có thể bạn quan tâm: Cách Copy Đảo Ngược Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
1. Ô Trống Là Gì? Phân Loại Các Loại “Rỗng” Trong Excel
Trước khi đi vào cách đếm, chúng ta cần hiểu rõ “ô trống” trong Excel có thể mang nhiều nghĩa khác nhau:
| Loại ô | Mô tả | Cách Excel nhận diện |
|---|---|---|
| Ô thực sự rỗng | Không chứa dữ liệu, không có công thức, không có định dạng đặc biệt. | ISBLANK() trả về TRUE. |
| Ô chứa công thức trả về chuỗi rỗng (“”) | Công thức như =IF(A1>0,"",A1) trả về “”. | ISBLANK() trả về FALSE, nhưng hàm LEN() trả về 0. |
| Ô chứa giá trị lỗi (#N/A, #DIV/0!…) | Thông thường không được tính là “trống”. | ISERROR() hoặc ISNA() kiểm tra. |
| Ô có khoảng trắng (space) hoặc ký tự không hiển thị | Nhập “ ” (space) hoặc CHAR(160) (non‑breaking space). | TRIM() hoặc CLEAN() cần xử lý. |
| Ô có định dạng “Number” nhưng không nhập giá trị | Khi nhập công thức = "" và định dạng là Text, Excel vẫn coi là chuỗi rỗng. | Cần kiểm tra LEN(TRIM(cell))=0. |
Khi chúng ta nói “đếm ô trống”, thường chúng ta muốn đếm các ô thực sự rỗng (không có dữ liệu, không có công thức). Tuy nhiên, trong một số trường hợp, bạn có thể muốn tính cả các ô có công thức trả về chuỗi rỗng hoặc các ô chỉ chứa khoảng trắng. Bài viết sẽ chỉ ra cách xử lý cho từng trường hợp.
Có thể bạn quan tâm: Hướng Dẫn Copy Sheet Trong Excel: Từ Cơ Bản Đến Nâng Cao
2. Hàm COUNTBLANK – Cách Đơn Giản Nhất
2.1. Cú pháp
COUNTBLANK(range)
- range: Vùng ô bạn muốn đếm. Có thể là một dải liên tục (A1:A10) hoặc một vùng không liên tục (A1:A10, C1:C10).
2.2. Ví dụ cơ bản
| A | B | C | |
|---|---|---|---|
| 1 | 10 | =A12 | |
| 2 | 5 | ||
| 3 | =B2 | 15 | |
| 4 | =IF(A1>5,””,””) |
- Công thức
=COUNTBLANK(A1:C4)sẽ trả về 8. Giải thích: - Ô thực sự rỗng: A2, A4, B1, B3, B4, C2, C3, C4 → 8 ô.
- Ô C1 chứa công thức
=A12→ không tính. - Ô A3 chứa công thức
=B2(B2 rỗng) → công thức trả về 0 → không tính là trống. - Ô C4 chứa công thức
=IF(A1>5,"","")trả về “”.COUNTBLANKkhông tính ô này là trống vì có công thức.
2.3. Lưu ý khi dùng COUNTBLANK
| Tình huống | COUNTBLANK hành vi | Cách khắc phục |
|---|---|---|
| Ô có công thức trả về “ ” (chuỗi rỗng) | Không tính là trống | Dùng công thức tùy chỉnh (xem phần 4). |
| Ô chứa khoảng trắng | Không tính là trống | Dùng TRIM hoặc SUBSTITUTE để loại bỏ khoảng trắng trước khi đếm. |
| Vùng không liên tục | Hỗ trợ, ví dụ COUNTBLANK(A1:A10, C1:C10) | Đảm bảo mỗi dải được ngăn cách bằng dấu phẩy. |
| Bảng (Excel Table) | Có thể dùng COUNTBLANK(ColumnName) | Áp dụng trực tiếp vào cột bảng. |
Có thể bạn quan tâm: Cách Sao Chép Giá Trị (copy Value Only) Trong Excel: Hướng Dẫn Chi Tiết Và Thực Hành
3. Kết Hợp COUNTBLANK Với Các Hàm Khác
3.1. Đếm ô trống cùng với các điều kiện (COUNTIFS)
Giả sử bạn muốn đếm số ô trống trong cột B nhưng chỉ trong các hàng mà cột A có giá trị > 100.
=SUMPRODUCT(--(A2:A100>100), --(COUNTBLANK(B2:B100)>0))
Hoặc cách ngắn gọn hơn:
=COUNTIFS(A2:A100,">100",B2:B100,"")
COUNTIFS tự động coi "" là ô trống (không có dữ liệu) và không tính các ô có công thức trả về “”.
3.2. Đếm ô trống trong một dải được lọc (SUBTOTAL)
Khi dữ liệu được lọc (filter), hàm COUNTBLANK vẫn đếm toàn bộ ô, kể cả ô ẩn. Để chỉ đếm ô trống trong các hàng hiển thị:
=SUBTOTAL(103, B2:B100) // 103 = COUNTA (đếm không trống) trong dải hiển thị
=ROWS(B2:B100) - SUBTOTAL(103, B2:B100) // Đếm ô trống trong dải hiển thị
Hoặc dùng AGGREGATE:
=AGGREGATE(3,5,B2:B100) // 3 = COUNTA, 5 = ignore hidden rows
=ROWS(B2:B100)-AGGREGATE(3,5,B2:B100)
3.3. Đếm ô trống trong một dải được tạo động (OFFSET + COUNTA)
Nếu dải dữ liệu thay đổi kích thước thường xuyên:
=COUNTBLANK(OFFSET(A1,0,0,COUNTA(A:A),1))
OFFSETtạo một dải bắt đầu tại A1, chiều cao bằng số ô không rỗng trong cột A, chiều rộng 1.COUNTBLANKsau đó đếm ô trống trong dải này.
4. Đếm Ô Trống Bao Gồm Các Ô Chứa Công Thức Trả Về Chuỗi Rỗng (“”)
Như đã đề cập, COUNTBLANK không tính các ô có công thức trả về “”. Để bao gồm chúng, chúng ta có thể dùng một công thức mảng (array formula) hoặc hàm SUMPRODUCT.
4.1. Công thức mảng (Office 365/Excel 2026 trở lên)
=SUM(--(LEN(TRIM(A1:C10))=0))
LEN(TRIM(...))trả về độ dài chuỗi sau khi loại bỏ khoảng trắng.- Khi độ dài = 0 → ô được coi là “trống” (bao gồm ô thực sự rỗng và ô có công thức trả về “”).
Cách nhập: Đối với Excel phiên bản cũ, nhấn Ctrl+Shift+Enter. Với Office 365, công thức động tự động tính mảng.
4.2. Sử dụng SUMPRODUCT (không cần công thức mảng)
=SUMPRODUCT(--(LEN(TRIM(A1:C10))=0))
4.3. Kết hợp với các điều kiện
Nếu muốn đếm ô trống và thỏa mãn một điều kiện ở cột khác:
=SUMPRODUCT(--(LEN(TRIM(B2:B100))=0), --(A2:A100>0))
5. Đếm Ô Trống Trong Các Vùng Không Liên Thục (Multiple Ranges)
Có thể bạn quan tâm: Mẫu Excel Phân Tích Chi Phí – Lợi Ích (cost Benefit Analysis) – Hướng Dẫn Chi Tiết Từ A Đến Z
Bạn có thể truyền nhiều dải vào COUNTBLANK:
=COUNTBLANK(A1:A10, C1:C10, E1:E10)
Nếu muốn tính cả các ô có công thức trả về “”, hãy dùng SUMPRODUCT:
=SUMPRODUCT(--(LEN(TRIM(A1:A10))=0))
+SUMPRODUCT(--(LEN(TRIM(C1:C10))=0))
+SUMPRODUCT(--(LEN(TRIM(E1:E10))=0))
Hoặc tạo một dải ảo bằng hàm CHOOSE:
=SUMPRODUCT(--(LEN(TRIM(CHOOSE({1,2,3},A1:A10,C1:C10,E1:E10)))=0))
6. Đếm Ô Trống Trong Bảng (Excel Table)
Khi dữ liệu được chuyển thành Table (Insert → Table), mỗi cột có tên riêng và công thức có thể tham chiếu bằng cấu trúc.
Giả sử bảng có tên SalesData, cột Quantity cần đếm ô trống:
=COUNTBLANK(SalesDataQuantity)
Nếu muốn tính cả công thức trả về “”:
=SUMPRODUCT(--(LEN(TRIM(SalesDataQuantity))=0))
Lợi ích của Table: Khi thêm hoặc xóa dòng, công thức tự động mở rộng, không cần điều chỉnh dải.
7. Sử Dụng “Go To Special” và “Find” Để Đếm Nhanh Không Cần Công Thức
7.1. Go To Special
- Chọn vùng dữ liệu (hoặc toàn bộ sheet bằng
Ctrl+A). - Nhấn
F5→ Special → Blanks → OK. - Excel sẽ chọn tất cả các ô trống thực sự (không tính ô có công thức trả về “”).
- Kiểm tra thanh trạng thái (Status Bar) – Excel hiển thị Count: X (số ô được chọn). Nếu không, bạn có thể dùng
=ROWS(Selection)trong một ô tạm để đếm.
7.2. Find (Ctrl+F)
- Mở Find (
Ctrl+F), để Find what trống. - Click Options → Look in: Formulas (để tìm cả ô có công thức rỗng) hoặc Values (chỉ tìm giá trị rỗng).
- Click Find All – danh sách sẽ hiển thị số lượng kết quả ở dưới.
Cách này nhanh, không cần viết công thức, thích hợp cho việc kiểm tra nhanh.
8. Đếm Ô Trống Bằng PivotTable
PivotTable không có trường “Blank Count” trực tiếp, nhưng bạn có thể:
- Thêm cột phụ trợ trong dữ liệu gốc, ví dụ IsBlank:
excel =IF(LEN(TRIM(A2))=0,1,0) - Tạo PivotTable, kéo IsBlank vào Values, đặt Summarize Values By → Sum. Kết quả là tổng số ô trống (bao gồm cả công thức trả về “”).
Nếu muốn chỉ tính ô trống thực sự, công thức phụ trợ sẽ là:
=IF(ISBLANK(A2),1,0)
9. Đếm Ô Trống Bằng Power Query (Get & Transform)
Power Query rất mạnh khi làm việc với dữ liệu lớn (hàng > 100k). Các bước:
- Chọn bảng → Data → From Table/Range.
- Trong Power Query Editor, chọn cột cần đếm.
- Sử dụng Add Column → Custom Column và nhập công thức:
powerquery if Text.Trim(ColumnName) = "" then 1 else 0 - Sau khi thêm cột IsBlank, chọn Home → Group By, chọn Sum của IsBlank.
- Kết quả là số ô trống.
Power Query tự động loại bỏ các ô có công thức trả về “” vì dữ liệu đã được “đánh giá” thành giá trị.
10. Đếm Ô Trống Bằng VBA (Macro)
Khi cần thực hiện đếm ô trống trong nhiều sheet, hoặc tự động báo cáo, VBA là giải pháp.
10.1. Hàm đơn giản đếm ô trống thực sự
Function CountBlankCells(rng As Range) As Long Dim cell As Range, cnt As Long For Each cell In rng If IsEmpty(cell) Then cnt = cnt + 1 Next cell CountBlankCells = cnt
End Function
Cách dùng: Trong sheet, nhập =CountBlankCells(A1:C100).
10.2. Bao gồm ô có công thức trả về chuỗi rỗng
Function CountBlankOrEmptyFormula(rng As Range) As Long Dim cell As Range, cnt As Long For Each cell In rng If IsEmpty(cell) Or (Len(cell.Value) = 0 And Not IsError(cell.Value)) Then cnt = cnt + 1 End If Next cell CountBlankOrEmptyFormula = cnt
End Function
10.3. Đếm ô trống trong toàn bộ workbook
Sub CountBlankInWorkbook() Dim ws As Worksheet, totalBlank As Long For Each ws In ThisWorkbook.Worksheets totalBlank = totalBlank + Application.WorksheetFunction.CountBlank(ws.UsedRange) Next ws MsgBox "Tổng số ô trống trong workbook: " & totalBlank
End Sub
Lưu ý: UsedRange chỉ tính vùng đã sử dụng; nếu có ô trống bên ngoài vùng này sẽ không được tính.
11. Những Sai Lầm Thường Gặp Khi Đếm Ô Trống Và Cách Tránh
| Sai lầm | Hậu quả | Giải pháp |
|---|---|---|
| Dùng COUNTBLANK mà không nhận ra ô có công thức trả về “”. | Kết quả thấp hơn thực tế → báo cáo sai. | Dùng công thức LEN(TRIM(...))=0 hoặc VBA tùy chỉnh. |
| Không loại bỏ khoảng trắng (space) trong ô. | Ô được tính là không trống dù nội dung không có ý nghĩa. | Áp dụng TRIM hoặc SUBSTITUTE(cell," ",""). |
| Đếm ô trống trong dải đã lọc mà dùng COUNTBLANK. | Đếm cả ô ẩn → sai khi báo cáo chỉ cho dữ liệu hiển thị. | Dùng SUBTOTAL hoặc AGGREGATE. |
| Sử dụng hàm ISBLANK trong công thức mảng mà không bọc bằng IFERROR. | Khi ô chứa lỗi, công thức trả về lỗi toàn bộ. | Dùng IFERROR(ISBLANK(...),FALSE). |
| Quên cập nhật dải khi thêm dòng mới trong công thức cũ. | Dải không bao phủ toàn bộ dữ liệu mới → bỏ sót ô trống. | Chuyển dữ liệu thành Table hoặc dùng OFFSET/INDEX để tạo dải động. |
12. Tổng Kết – Khi Nào Nên Dùng Cách Nào?
| Nhu cầu | Phương pháp đề xuất |
|---|---|
| Đếm nhanh ô trống thực sự trong một dải nhỏ | COUNTBLANK |
| Bao gồm ô có công thức trả về “” | SUMPRODUCT(--(LEN(TRIM(range))=0)) |
| Đếm trong dữ liệu đã lọc | ROWS(range)-SUBTOTAL(103,range) |
| Đếm trong bảng (Table) và muốn tự động mở rộng | COUNTBLANK(TableColumn) hoặc SUMPRODUCT(--(LEN(TRIM(TableColumn))=0)) |
| Đếm nhiều dải không liên tục | COUNTBLANK(range1, range2, …) hoặc dùng CHOOSE + SUMPRODUCT |
| Kiểm tra nhanh mà không muốn viết công thức | Go To Special → Blanks hoặc Find (Ctrl+F) |
| Xử lý dữ liệu lớn (>100k hàng) | Power Query hoặc VBA |
| Tự động báo cáo số ô trống cho nhiều sheet | VBA macro (CountBlankInWorkbook) |
| Kết hợp với điều kiện (COUNTIFS, SUMPRODUCT) | COUNTIFS cho điều kiện đơn giản, SUMPRODUCT cho đa điều kiện và tính toán phức tạp |
13. Bài Tập Thực Hành
Bài 1: Trong bảng
SalesData(Table), cộtDiscountcó một số ô rỗng và một số ô chứa công thức=IF(Quantity>0, "", 0). Hãy tạo công thức trả về tổng số ô trống (bao gồm cả công thức trả về “”).
Giải pháp:excel
=SUMPRODUCT(--(LEN(TRIM(SalesDataDiscount))=0))Bài 2: Bạn có một danh sách 5000 khách hàng, trong đó 200 khách hàng chưa nhập email. Khi lọc danh sách để chỉ hiển thị những khách hàng có trạng thái “Active”, muốn biết còn bao nhiêu trong số họ chưa nhập email.
Giải pháp:excel
=COUNTIFS(StatusRange,"Active",EmailRange,"")Bài 3: Sử dụng VBA, viết macro để tự động gửi email báo cáo số ô trống trong cột
PhoneNumbercủa mỗi sheet trong workbook.
(Bạn có thể mở rộng phần VBA ở mục 10 để thực hiện chức năng gửi email.)
14. Kết Luận
Đếm ô trống trong Excel không chỉ là một thao tác đơn giản; nó là cầu nối quan trọng giữa việc nhập dữ liệu và việc phân tích dữ liệu có chất lượng. Bằng việc hiểu rõ các loại “ô trống”, lựa chọn hàm hoặc công cụ phù hợp, và tránh những lỗi phổ biến, bạn sẽ:
- Nắm bắt được mức độ đầy đủ của dữ liệu.
- Tự động hoá quy trình kiểm tra dữ liệu.
- Cải thiện độ tin cậy của báo cáo và quyết định dựa trên dữ liệu.
Hy vọng bài viết đã cung cấp cho bạn một bộ công cụ toàn diện – từ hàm COUNTBLANK cơ bản, tới các công thức mảng, Power Query, và VBA – để xử lý mọi tình huống đếm ô trống trong Excel. Hãy áp dụng ngay vào công việc của mình, và chia sẻ kinh nghiệm của bạn với đồng nghiệp!
Chúc bạn làm việc hiệu quả và luôn có dữ liệu sạch!
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ẻ.