Cách Đếm Số Ô Không Trống (count Non‑empty Cells) Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Có thể bạn quan tâm: Cách Đếm Số Ô Không Rỗng Trong Excel: Hướng Dẫn Toàn Diện Và Thực Hành

Lời Mở Đầu

Trong công việc văn phòng, kế toán, phân tích dữ liệu hay nghiên cứu khoa học, Excel luôn là công cụ không thể thiếu. Một trong những thao tác thường gặp nhưng lại vô cùng quan trọng là đếm số ô không trống (count non‑empty cells). Việc biết được bao nhiêu ô chứa dữ liệu thực tế giúp bạn:

• Kiểm tra tính đầy đủ của dữ liệu nhập.
• Xác định số lượng mẫu, bản ghi, giao dịch đã được ghi lại.
• Tính toán tỷ lệ phần trăm hoàn thành, tỷ lệ lỗi, hoặc các chỉ số KPI.
• Tự động hoá báo cáo và biểu đồ mà không cần kiểm tra thủ công.

Bài viết này sẽ cung cấp cho bạn một hướng dẫn toàn diện về cách đếm ô không trống trong Excel, từ các hàm cơ bản như COUNTA, COUNTIF, COUNTIFS cho tới các kỹ thuật nâng cao sử dụng Power Query, PivotTable, định dạng có điều kiện, VBA và Dynamic Arrays. Bạn sẽ hiểu được nguyên lý hoạt động, cách áp dụng trong các tình huống thực tế và tránh các lỗi phổ biến.

Mục tiêu của bài viết:
– Giải thích chi tiết các hàm và công cụ liên quan.
– Đưa ra ví dụ thực tế, kèm công thức và hình ảnh minh hoạ.
– So sánh các phương pháp, chỉ ra ưu, nhược điểm.
– Hướng dẫn tối ưu hoá hiệu suất khi làm việc với dữ liệu lớn.

Hãy cùng khám phá ngay sau đây!

Có thể bạn quan tâm: Excel Copy Value Not Formula: Hướng Dẫn Chi Tiết Để Sao Chép Giá Trị Mà Không Sao Chép Công Thức

1. Kiến Thức Cơ Bản: Hàm COUNTA

1.1 COUNTA là gì?

COUNTA là hàm đếm số ô không trống trong một phạm vi (range) hoặc mảng (array). Nó tính cả:

• Các giá trị số.
• Văn bản.
• Công thức trả về kết quả chuỗi hoặc số.
• Lỗi (#N/A, #DIV/0!, …) – lưu ý: lỗi cũng được tính như một ô không trống.

Cú pháp:

COUNTA(value1, value2, …)

• value1, value2, …: các đối số có thể là ô, phạm vi ô hoặc mảng.

1.2 Ví dụ cơ bản

• =COUNTA(A1:C3) trả về 6 (các ô chứa 10, abc, 20, công thức, lỗi, 0).
• =COUNTA(A1:A3) trả về 2 (10 và công thức).

1.3 Khi nào COUNTA không phù hợp?

• Khi bạn muốn bỏ qua các lỗi (đếm chỉ các ô chứa giá trị hợp lệ).
• Khi bạn cần đếm theo điều kiện (ví dụ: chỉ đếm ô không trống và lớn hơn 100).
• Khi dữ liệu được lưu trữ trong bảng (Table) và bạn muốn tính toán động trên các cột mới thêm.

Có thể bạn quan tâm: Cách Chuyển Công Thức Thành Giá Trị Trong Excel: Hướng Dẫn Chi Tiết Và Thực Hành

2. Đếm Ô Không Trống Có Điều Kiện: COUNTIF & COUNTIFS

2.1 COUNTIF – Đếm một điều kiện

COUNTIF cho phép bạn đếm số ô không trống và thỏa mãn một tiêu chí nhất định (tiêu chí có thể là “không rỗng” hoặc một biểu thức).

Cú pháp:

COUNTIF(range, criteria)

• range: phạm vi cần đếm.
• criteria: tiêu chí (có thể là "" để đại diện cho bất kỳ chuỗi nào, hoặc "<>" để đại diện cho “không trống”).

Ví dụ:
– =COUNTIF(A1:A10, "<>") – đếm số ô không rỗng trong A1:A10.
– =COUNTIF(B1:B20, "") – đếm số ô chứa văn bản (không tính số và lỗi).

Lưu ý: "<>" và "" có ý nghĩa khác nhau: "<>" đếm mọi giá trị không rỗng (số, văn bản, lỗi), còn "" chỉ đếm các ô chứa văn bản.

2.2 COUNTIFS – Đếm nhiều điều kiện

Khi cần đếm ô không trống đồng thời thỏa mãn nhiều tiêu chí (ví dụ: cột A không trống và cột B > 100), bạn dùng COUNTIFS.

Cú pháp:

COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, …)

Ví dụ thực tế:
Giả sử bảng bán hàng có cột Mã KH (A), Số lượng (B) và Ngày giao (C). Bạn muốn đếm số giao dịch đã nhập mã khách hàng và số lượng > 0:

=COUNTIFS(A2:A1000, "<>", B2:B1000, ">0")

Kết quả sẽ trả về số dòng thỏa mãn cả hai điều kiện.

2.3 Khi sử dụng COUNTIF/COUNTIFS với ô rỗng

Nếu muốn loại trừ các ô rỗng trong một tiêu chí, bạn luôn dùng "<>". Đối với các tiêu chí phức tạp hơn (ví dụ: “không trống và không phải lỗi”), bạn có thể kết hợp hàm ISERROR trong công thức mảng (Array Formula) hoặc dùng SUMPRODUCT.

3. Sử Dụng SUMPRODUCT Để Đếm Ô Không Trống Có Điều Kiện Phức Tạp

SUMPRODUCT là một hàm mạnh mẽ cho phép thực hiện đếm có điều kiện mà không cần nhập công thức mảng (Ctrl+Shift+Enter). Nó xử lý các mảng logic và trả về tổng của chúng.

3.1 Cú pháp cơ bản

SUMPRODUCT((range1<>"") (range2>0) (range3="Yes"))

Mỗi biểu thức trong dấu ngoặc trả về mảng TRUE/FALSE, được ép sang 1/0 khi nhân.

3.2 Ví dụ thực tế

Bạn có ba cột: Tên (A), Điểm (B), Trạng thái (C). Bạn muốn đếm số học sinh có tên, điểm >= 5 và đã hoàn thành (C = “Hoàn thành”).

=SUMPRODUCT((A2:A500<>"") (B2:B500>=5) (C2:C500="Hoàn thành"))

Kết quả là số học sinh thỏa mãn cả ba tiêu chí.

3.3 Ưu nhược điểm

• Ưu điểm: Không cần công thức mảng, dễ đọc, mạnh mẽ.
• Nhược điểm: Khi phạm vi quá lớn (hàng triệu), hiệu suất có thể giảm. Đối với dữ liệu lớn, nên dùng Power Query hoặc PivotTable.

4. Đếm Ô Không Trống Trong Bảng (Excel Table)

Khi dữ liệu được định dạng dưới dạng Table (Insert → Table), các công thức có thể tự động mở rộng khi bạn thêm hàng mới. Để đếm ô không trống trong một cột Table, bạn sử dụng structured references.

4.1 Cú pháp

Giả sử Table có tên SalesData và cột ProductID:

=COUNTA(SalesDataProductID)

Nếu muốn đếm chỉ các ô không trống và không phải lỗi:

=COUNTIFS(SalesDataProductID, "<>")

4.2 Lợi ích

• Tự động mở rộng: Khi bạn thêm dòng mới, công thức cập nhật tự động.
• Đọc dễ hiểu: Cột SalesDataProductID rõ ràng hơn B2:B1000.
• Tương thích với Power Query và Power Pivot.

5. Đếm Ô Không Trống Bằng Power Query

5.1 Khi nào dùng Power Query?

• Khi dữ liệu được nhập từ nhiều nguồn (CSV, SQL, Web).
• Khi cần xử lý dữ liệu lớn (> 100.000 hàng).
• Khi muốn tự động hoá quá trình làm sạch và tính toán.

5.2 Các bước thực hiện

1. Load dữ liệu vào Power Query: Data → Get Data → From Table/Range.
2. Thêm cột tùy chỉnh: Add Column → Custom Column.
3. Công thức: if Column1 = null then 0 else 1 → trả về 1 nếu ô không trống.
4. Tổng hợp: Home → Group By → chọn cột mới và dùng Sum để tính tổng số ô không trống.
5. Load lại: Close & Load về Excel.

5.3 Ví dụ thực tế

Bạn có bảng Orders với cột CustomerID. Để đếm số đơn hàng có CustomerID không trống:

• Thêm cột IsNotNull = if CustomerID = null then 0 else 1.
• Group By All Rows → Sum IsNotNull.

Kết quả trả về số đơn hàng hợp lệ.

5.4 Ưu điểm so với công thức

Có thể bạn quan tâm: Cách Đếm Số Ký Tự Trong Ô Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

• Xử lý nhanh cho dữ liệu lớn.
• Không phụ thuộc vào công thức tính toán trong sheet, giảm lỗi người dùng.
• Tự động cập nhật khi dữ liệu nguồn thay đổi.

6. PivotTable – Đếm Ô Không Trống Nhanh Gọn

PivotTable là công cụ mạnh mẽ để tổng hợp và đếm dữ liệu một cách trực quan.

6.1 Tạo PivotTable

1. Chọn bảng dữ liệu → Insert → PivotTable.
2. Kéo cột muốn đếm (ví dụ ProductID) vào Values.
3. Trong Value Field Settings, chọn Count (đếm số ô không trống).

6.2 Đếm theo nhóm

Bạn có thể group theo ngày, tháng, hoặc phân khúc khác để xem số ô không trống trong mỗi nhóm.

6.3 Khi PivotTable không đếm đúng?

• Nếu cột chứa công thức trả về rỗng (""), PivotTable sẽ đếm chúng vì chúng không phải là ô trống.
• Giải pháp: Thêm cột phụ trong nguồn dữ liệu, dùng công thức =IF(A2="",0,1) và đếm cột phụ.

7. Định Dạng Có Điều Kiện (Conditional Formatting) Để Đánh Dấu Ô Không Trống

Mặc dù không phải là cách đếm trực tiếp, Conditional Formatting (CF) giúp bạn nhìn thấy ngay những ô không trống và hỗ trợ kiểm tra nhanh.

7.1 Cách thiết lập

1. Chọn phạm vi cần kiểm tra.
2. Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.
3. Nhập công thức: =LEN(TRIM(A1))>0 (đánh dấu ô có nội dung).
4. Chọn màu nền, nhấn OK.

7.2 Kết hợp với COUNTA

Sau khi CF hiển thị, bạn có thể đếm bằng COUNTA để xác nhận số lượng ô đã được đánh dấu.

8. Sử Dụng Dynamic Array Functions (Excel 365/2026)

Excel 365 giới thiệu các hàm Dynamic Array như FILTER, UNIQUE, SEQUENCE, giúp đếm ô không trống một cách ngắn gọn.

8.1 Đếm ô không trống với FILTER

=ROWS(FILTER(A2:A1000, A2:A1000<>""))

• FILTER trả về mảng các ô không rỗng.
• ROWS đếm số hàng trong mảng.

8.2 Đếm ô không trống và loại bỏ lỗi

=ROWS(FILTER(A2:A1000, (A2:A1000<>"") (ISERROR(A2:A1000)=FALSE)))

8.3 Khi nào dùng Dynamic Array?

• Khi công thức ngắn gọn, dễ đọc.
• Khi muốn lấy danh sách các giá trị không trống (kết hợp với UNIQUE).
• Khi làm việc trên Excel 365 hoặc Excel 2026 trở lên.

9. Đếm Ô Không Trống Bằng VBA (Macro)

Đối với các tác vụ lặp lại, tự động hoá hoặc khi cần đếm trong nhiều sheet cùng lúc, VBA là lựa chọn phù hợp.

9.1 Mã VBA đơn giản

Function CountNonEmpty(rng As Range) As Long Dim cell As Range Dim cnt As Long cnt = 0 For Each cell In rng If Not IsEmpty(cell) Then cnt = cnt + 1 Next cell CountNonEmpty = cnt
End Function

• Cách dùng: =CountNonEmpty(A1:C10) trong sheet.
• Hàm này bỏ qua lỗi (lỗi được tính là không trống). Nếu muốn bỏ qua lỗi, dùng If Not IsError(cell) And Not IsEmpty(cell) Then.

9.2 Đếm nhanh trong toàn bộ workbook

Sub CountAllSheets() Dim ws As Worksheet Dim total As Long total = 0 For Each ws In ThisWorkbook.Worksheets total = total + Application.WorksheetFunction.CountA(ws.UsedRange) Next ws MsgBox "Tổng số ô không trống trong workbook: " & total
End Sub

9.3 Khi nào nên dùng VBA?

• Khi cần đếm trên nhiều sheet hoặc tự động hoá báo cáo.
• Khi công thức truyền thống không đáp ứng (ví dụ: đếm trong các vùng không liên tục).
• Khi muốn tối ưu tốc độ cho dữ liệu cực lớn (hàng triệu).

10. Các Lỗi Thường Gặp Khi Đếm Ô Không Trống và Cách Khắc Phục

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

Lựa chọn:
– Dữ liệu nhỏ (<10k) → COUNTA, COUNTIF, COUNTIFS là đủ.
– Nhiều điều kiện phức tạp → SUMPRODUCT hoặc COUNTIFS.
– Dữ liệu lớn (>100k) → Power Query hoặc VBA.
– Báo cáo động, người không chuyên → PivotTable hoặc Dynamic Array.

12. Thực Hành: 5 Bài Tập Thực Tế

Bài 1: Đếm số nhân viên đã nhập email trong cột Email (A2:A500)

=COUNTIF(A2:A500, "<>")

Bài 2: Đếm số đơn hàng có giá trị > 0 và không trống trong cột Amount (B2:B2000)

=COUNTIFS(B2:B2000, "<>", B2:B2000, ">0")

Bài 3: Đếm số học sinh có điểm trung bình (C) không rỗng và lớn hơn hoặc bằng 6.5, đồng thời đã nộp bài tập (D = “Yes”).

=COUNTIFS(C2:C1000, "<>", C2:C1000, ">=6.5", D2:D1000, "Yes")

Bài 4: Đếm số dòng trong bảng SalesData có cột CustomerID không trống và cột OrderDate là ngày trong tháng 3/2026.

=COUNTIFS(SalesDataCustomerID, "<>", SalesDataOrderDate, ">=03/01/2026", SalesDataOrderDate, "<=03/31/2026")

Bài 5: Sử dụng Power Query để đếm số bản ghi không trống trong cột ProductCode của một tệp CSV lớn (500.000 dòng).

1. Data → Get Data → From Text/CSV → Load.
2. Add Column → Custom Column: if ProductCode = null then 0 else 1.
3. Home → Group By → Sum Custom.
4. Kết quả hiển thị số bản ghi hợp lệ.

13. Mẹo Tối Ưu Hóa Khi Đếm Ô Không Trống

1. Tránh sử dụng toàn cột (A:A) nếu không cần thiết; dùng phạm vi thực tế để giảm tải tính toán.
2. Sử dụng Table để công thức tự mở rộng, tránh lỗi khi thêm dữ liệu.
3. Tắt tính năng Automatic Calculation (Formulas → Calculation Options → Manual) khi làm việc với công thức phức tạp trên dữ liệu lớn, sau đó nhấn F9 để tính lại.
4. Kiểm tra dữ liệu rỗng ẩn: Đôi khi ô chứa dấu cách " " hoặc ký tự không hiển thị; dùng TRIM hoặc LEN để phát hiện.
5. Sử dụng Named Ranges để quản lý phạm vi và giảm lỗi khi di chuyển dữ liệu.

14. Kết Luận

Việc đếm số ô không trống trong Excel không chỉ là một thao tác đơn giản mà còn là nền tảng cho nhiều phân tích, báo cáo và tự động hoá. Từ hàm cơ bản COUNTA, COUNTIF, COUNTIFS cho tới các công cụ mạnh mẽ như Power Query, PivotTable, Dynamic Arrays và VBA, mỗi phương pháp có ưu, nhược điểm riêng và phù hợp với các tình huống khác nhau.

Bạn nên:

• Xác định quy mô và tính chất dữ liệu (kích thước, nguồn, yêu cầu tính toán).
• Lựa chọn công cụ phù hợp: công thức nhanh cho dữ liệu nhỏ, Power Query/VBA cho dữ liệu lớn.
• Kiểm tra và xử lý dữ liệu rỗng ẩn (dấu cách, lỗi).
• Tận dụng Table và Structured References để công thức luôn cập nhật.

Hy vọng bài viết đã cung cấp cho bạn đủ kiến thức để đếm ô không trống một cách chính xác, nhanh chóng và hiệu quả trong mọi môi trường làm việc với Excel. Hãy áp dụng ngay các ví dụ và mẹo trên để nâng cao năng suất và độ tin cậy của các báo cáo của bạn!

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