Cách đếm ô không trống trong Excel: 5 phương pháp chính

Tôi cũng có thể đếm theo hàng để xem mỗi người hoàn thành đến mức nào:

=COUNTA(B2:F2)Được hỗ trợ bởi

Với Sara Ibrahim, công thức trả về 5 vì cô ấy điền đủ mọi trường. Với Nour Mahmoud, =COUNTA(B6:F6) chỉ trả về 1 vì cô ấy chỉ cung cấp email và bỏ qua phần còn lại.

Khi nào COUNTA() là đủ

Hãy dùng COUNTA() khi bạn cần đếm đơn giản và dữ liệu sạch. Nếu mọi ô trống trong phạm vi của bạn thực sự rỗng (chưa từng chạm tới, không có công thức), COUNTA() hoạt động hoàn hảo.

Điều trở nên rắc rối khi công thức trả về "" (chuỗi rỗng). Tôi sẽ giải thích kỹ trường hợp ngoại lệ này trong phần "Excel xử lý ô trống so với ô rỗng như thế nào" phía sau.

Đếm ô không trống bằng COUNTIF()

COUNTIF() cho bạn một cách khác để đếm ô không trống. Thay vì đếm mọi thứ tồn tại, nó đếm các ô thỏa một điều kiện. Và điều kiện đó có thể là "không rỗng".

Tiêu chí "<>"

Công thức trông như sau:

=COUNTIF(B2:B13,"<>")Được hỗ trợ bởi

Phần "<>" nghĩa là "khác rỗng", tức là một cách khác để nói "không trống". Với cùng cột email như trước, công thức cho ra 10, giống COUNTA().

Vì sao không dùng COUNTIF(range,"*")?

Tôi thấy cách này trong các hướng dẫn cũ và đó là sai lầm. Ký tự đại diện "*" chỉ đếm văn bản. Nó hoàn toàn bỏ qua số, ngày và giá trị TRUE/FALSE. Nếu cột của bạn có kiểu dữ liệu hỗn hợp, bạn sẽ đếm sai.

Hãy dùng "<>" thay thế. Nó hoạt động với mọi kiểu dữ liệu.

Khi nào COUNTIF() tốt hơn COUNTA()

Ưu điểm thực sự của COUNTIF() là nó dẫn dắt tự nhiên tới COUNTIFS(). Khi bạn cần đếm các ô không trống đồng thời thỏa các điều kiện khác (như bộ phận cụ thể hay khoảng ngày), COUNTIFS() sẽ phát huy. Nếu bạn nghĩ có thể sẽ thêm điều kiện sau này, hãy bắt đầu với COUNTIF() để việc chuyển đổi được dễ dàng.

Với hầu hết dữ liệu, COUNTA() và COUNTIF(range,"<>") cho kết quả giống nhau. Cả hai đếm cùng những thứ, bao gồm lỗi và ô trống do công thức tạo ra. Khác biệt chính là tính linh hoạt. COUNTIF() là cầu nối của bạn tới đếm đa tiêu chí với COUNTIFS().

Đếm ô không trống kèm điều kiện

Đây là lúc COUNTIF() tỏa sáng. COUNTIFS() cho phép bạn đếm ô không trống đồng thời thỏa các điều kiện khác.

COUNTIFS() với logic không trống

Giả sử tôi đang theo dõi đào tạo nhân viên và muốn biết có bao nhiêu người ở bộ phận Sales đã nộp điểm. Công thức như sau:

=COUNTIFS(B2:B11,"Sales",E2:E11,"<>")Được hỗ trợ bởi

Nghĩa là: "Đếm các hàng mà Phòng ban là 'Sales' VÀ Điểm không trống." Trong bảng theo dõi của tôi, kết quả là 3 vì Mona Sayed thuộc Sales nhưng chưa nộp điểm.

Nhiều điều kiện

Bạn có thể xếp chồng tới 127 cặp tiêu chí nếu cần. Đây là công thức đếm nhân viên được đánh dấu "Complete" đồng thời có điểm:

=COUNTIFS(C2:C11,"Complete",E2:E11,"<>")Được hỗ trợ bởi

Kết quả là 6 vì Mona Sayed đã hoàn thành khóa học nhưng chưa từng nộp điểm.

Một ví dụ khác. Đếm những người thuộc Engineering có ngày hoàn thành:

=COUNTIFS(B2:B11,"Engineering",D2:D11,"<>")Được hỗ trợ bởi

Công thức trả về 3 vì đào tạo của Omar Mostafa vẫn đang chờ xử lý và chưa có ngày hoàn thành.

Mẫu số chung rất đơn giản: dùng "<>" cho cột bạn kiểm tra giá trị không trống và đặt các bộ lọc cụ thể ở các cột khác.

Excel xử lý ô "Blank" so với ô "Empty" như thế nào

Đây là nơi xảy ra hầu hết các sai sót khi đếm. Excel coi các loại "rỗng" khác nhau đều trông giống nhau trên màn hình.

Ô thực sự rỗng

Một ô thực sự rỗng là ô chưa bao giờ bị tác động hoặc đã được xóa sạch hoàn toàn. ISBLANK() trả về TRUE, COUNTA() bỏ qua và COUNTBLANK() đếm nó. Đây là kiểu ô duy nhất mà mọi hàm đều đồng thuận là trống.

Chuỗi rỗng do công thức tạo ra

Điều này khiến mọi người vấp phải suốt. Khi một công thức như =IF(A1>60,"Pass","") trả về "", ô trông rỗng nhưng thực ra đang chứa kết quả công thức. Các hàm nhìn nhận nó như sau:

• COUNTA() → Đếm (không trống)
• COUNTIF(range,"<>") → Đếm (không trống)
• COUNTBLANK() → Đếm (trống)
• ISBLANK() → Trả về FALSE

Thấy vấn đề chưa? COUNTBLANK() bảo rằng ô trống, nhưng ISBLANK() lại bảo không. Điều này làm khó cả những người dùng Excel lâu năm.

Cách lách bằng SUMPRODUCT() + LEN()

Để chỉ đếm các ô có nội dung hiển thị thực sự (không tính ô trống do công thức, không tính dấu cách), hãy dùng:

=SUMPRODUCT(--(LEN(B5:B10)>0))Được hỗ trợ bởi

Công thức này kiểm tra số ký tự trong mỗi ô. Nếu LEN() trả về 0, ô đó rỗng bất kể thế nào. Phần -- chuyển TRUE/FALSE thành 1/0, và SUMPRODUCT()` sẽ cộng chúng lại.

Để đếm chặt chẽ hơn, cũng bỏ qua các ô chỉ chứa dấu cách:

=SUMPRODUCT(--(LEN(TRIM(B5:B10))>0))Được hỗ trợ bởi

Ô có ký tự ẩn

Đôi khi một ô trông rỗng nhưng lại có khoảng trắng (CHAR(32)) hoặc khoảng trắng không ngắt (CHAR(160)) từ dữ liệu web hoặc PDF. COUNTA() đếm, LEN() trả về 1 trở lên, và ISBLANK() cho FALSE.

Để bắt những trường hợp này khi đếm, hãy dùng công thức chặt chẽ tôi vừa nêu: =SUMPRODUCT(--(LEN(TRIM(range))>0)). Phần TRIM() loại bỏ khoảng trắng thông thường trước khi đếm. Với khoảng trắng không ngắt, hãy làm sạch dữ liệu trước bằng =SUBSTITUTE(A1,CHAR(160),"") hoặc dùng hàm CLEAN().

Đếm ô không trống trong dữ liệu đã lọc

Một điều dễ đánh lừa: bạn lọc dữ liệu, viết công thức COUNTA(), và kết quả vẫn bao gồm các hàng bị ẩn. Đó là vì COUNTA() không quan tâm đến bộ lọc. Nó đếm mọi thứ trong phạm vi, dù bạn có nhìn thấy hay không.

SUBTOTAL(): Lựa chọn thay thế tôn trọng bộ lọc

SUBTOTAL() khắc phục điều này. Nó đếm có xét tới bộ lọc.

=SUBTOTAL(3,D2:D11)Được hỗ trợ bởi

Số 3 bảo SUBTOTAL() thực hiện đếm kiểu COUNTA(). Khác biệt: nó bỏ qua các hàng bị ẩn bởi bộ lọc.

Số hàm: 3 so với 103

SUBTOTAL() có hai chế độ:

• Số hàm 3: Bỏ qua các hàng bị ẩn bởi AutoFilter, nhưng vẫn đếm các hàng ẩn thủ công.
• Số hàm 103: Bỏ qua TẤT CẢ hàng ẩn (cả do lọc và ẩn thủ công).

=SUBTOTAL(3,D2:D11)    -- Skips AutoFilter rows
=SUBTOTAL(103,D2:D11)  -- Skips ALL hidden rowsĐược hỗ trợ bởi

Hầu hết thời gian, 3 là đủ. Chỉ dùng 103 nếu bạn đã ẩn hàng thủ công (chuột phải > Hide) và muốn loại trừ chúng.

AGGREGATE(): Phương án thay thế cho dữ liệu lộn xộn

Nếu dữ liệu đã lọc của bạn có giá trị lỗi, SUBTOTAL() sẽ phát sinh lỗi. AGGREGATE() xử lý tốt hơn:

=AGGREGATE(3,7,D2:D11)Được hỗ trợ bởi

Số 3 là số hàm (COUNTA()), và 7 bảo nó bỏ qua cả hàng ẩn và lỗi. Đây là lựa chọn an toàn nhất cho dữ liệu lộn xộn, nhưng đa số mọi người không cần tới.

Đếm ô không trống trong Bảng Excel

Nếu dữ liệu của bạn nằm trong một Excel Table (nhấn Insert > Table hoặc bấm Ctrl+T), bạn có thể dùng tham chiếu có cấu trúc thay cho địa chỉ ô. Điều này giúp công thức dễ hiểu hơn và tự động cập nhật khi bảng mở rộng.

Tham chiếu có cấu trúc với COUNTA()

Thay vì viết =COUNTA(C2:C9), bạn viết:

=COUNTA(TaskTracker[Assigned To])Được hỗ trợ bởi

Công thức này đếm các ô không trống trong cột Assigned To của bảng TaskTracker của tôi. Tham chiếu này tự động bỏ qua hàng tiêu đề và mở rộng khi tôi thêm hàng mới.

Kết hợp COUNTIFS() với tham chiếu có cấu trúc

COUNTIFS() hoạt động rất tốt với bảng:

=COUNTIFS(TaskTracker[Status],"Done",TaskTracker[Completion Date],"<>")Được hỗ trợ bởi

Công thức này đếm các nhiệm vụ được đánh dấu "Done" và có ngày hoàn thành. Trong bảng theo dõi của tôi, kết quả là 4 vì tất cả nhiệm vụ đã hoàn thành đều có ngày.

Vì sao bảng tốt hơn khi đếm

Có hai điều khiến bảng hữu ích. Thứ nhất, tham chiếu có cấu trúc tự động bỏ qua hàng tiêu đề, nên bạn không cần bận tâm bắt đầu từ hàng 2. Thứ hai, khi bạn thêm hàng mới vào bảng, mọi công thức dùng tham chiếu có cấu trúc đều tự động bao gồm dữ liệu mới. Không cần cập nhật phạm vi thủ công.

Điều này rất phù hợp với dữ liệu tăng dần theo thời gian, như nhật ký, bộ theo dõi, hoặc dữ liệu bạn nhập thường xuyên.

Các vấn đề thường gặp khi đếm ô không trống

Số đếm cao hơn kỳ vọng

Tình huống: COUNTA() cho 6, nhưng bạn chỉ thấy 4 ô có dữ liệu.

Nguyên nhân: Hai ô có công thức trả về "". Như đã đề cập, chúng trông rỗng nhưng về mặt kỹ thuật thì không.

Cách khắc phục: Dùng =SUMPRODUCT(--(LEN(range)>0)) thay cho COUNTA(). Công thức này chỉ đếm các ô có nội dung hiển thị, bỏ qua ô trống do công thức tạo ra.

Khoảng trắng ẩn làm sai lệch số đếm

Tình huống: Một ô trông rỗng, nhưng COUNTA() vẫn đếm và ISBLANK() trả về FALSE.

Nguyên nhân: Ô có chứa dấu cách, có thể do sao chép dữ liệu từ trang web hoặc tệp khác.

Cách khắc phục: Kiểm tra bằng =LEN(A1). Nếu trả về lớn hơn 0 khi ô trông rỗng, tức là có ký tự ẩn. Hãy làm sạch dữ liệu trước bằng =TRIM(A1), hoặc dùng công thức chặt chẽ tôi đã nêu: =SUMPRODUCT(--(LEN(TRIM(range))>0)).

Giá trị lỗi bị đếm trong khi bạn không muốn

Tình huống: Bạn muốn đếm ô không trống nhưng các lỗi như #DIV/0! hoặc #N/A lại được tính vào.

Nguyên nhân: Cả COUNTA() và COUNTIF(range,"<>") đều coi lỗi là không trống.

Cách khắc phục: Nếu dữ liệu của bạn cũng có bộ lọc, dùng =AGGREGATE(3,7,range) như tôi đã đề cập ở phần dữ liệu đã lọc. Tham số 7 bỏ qua cả hàng ẩn và lỗi. Với dữ liệu không lọc, dùng =SUMPRODUCT(--(LEN(IFERROR(range,""))>0)).

Số đếm không cập nhật sau khi lọc

Tình huống: Bạn lọc dữ liệu và COUNTA() vẫn hiển thị số đếm chưa lọc.

Nguyên nhân: COUNTA() hoàn toàn bỏ qua bộ lọc.

Cách khắc phục: Dùng =SUBTOTAL(3,range) như đã đề cập ở phần dữ liệu đã lọc. Nó tự động điều chỉnh theo phần đang hiển thị.

Thực hành tốt nhất khi đếm ô không trống

Sau khi xử lý những vấn đề này nhiều hơn mức tôi muốn thừa nhận, đây là cách tôi làm hiện nay:

• Bắt đầu với COUNTA() cho các tập dữ liệu sạch, đơn giản. Nhanh và dễ hiểu.

• Dùng COUNTIF(range,"<>") nếu có thể bạn sẽ thêm điều kiện sau này. Nó kết nối tự nhiên với COUNTIFS() để đếm đa tiêu chí.

• Kiểm tra ô trống do công thức trước khi tin vào số đếm. Kiểm tra ngẫu nhiên vài ô "rỗng" bằng =LEN(cell). Nếu khác 0, hãy chuyển sang cách SUMPRODUCT()+LEN().

• Dùng SUBTOTAL(3,range) cho dữ liệu đã lọc. Đây là hàm đếm duy nhất tôn trọng bộ lọc.

• Làm sạch dữ liệu nhập vào trước. Chạy TRIM() và kiểm tra CHAR(160) đối với dữ liệu từ web hoặc nguồn khác.

• Dùng Bảng Excel cho tập dữ liệu mở rộng. Tham chiếu có cấu trúc tự động cập nhật khi bạn thêm hàng.

• Ghi chú các công thức phức tạp. Nếu bạn dùng SUMPRODUCT(--(LEN(TRIM(range))>0)), hãy thêm chú thích giải thích lý do. Tương lai bạn sẽ cảm ơn bạn.

Kết luận

Excel cung cấp nhiều cách để đếm ô không trống, và cách phù hợp tùy tình huống của bạn. COUNTA() phù hợp cho hầu hết trường hợp. COUNTIF() và COUNTIFS() cho phép thêm điều kiện. SUBTOTAL() tôn trọng bộ lọc. Và SUMPRODUCT() kết hợp với LEN() là phương án dự phòng khi công thức tạo ra ô trống vô hình.

Mẹo không phải là học thuộc công thức. Mấu chốt là hiểu Excel coi "trống" là gì. Công thức trả về "" trông rỗng nhưng không rỗng. Ký tự khoảng trắng là vô hình nhưng vẫn bị đếm. Khi nắm được những khác biệt này, bạn sẽ không còn bị bất ngờ bởi các số đếm sai nữa.

Nếu bạn muốn tiếp tục nâng cao kỹ năng Excel, hãy xem khóa học Data Analysis in Excel của chúng tôi. Khóa học bao gồm công thức, pivot table và làm sạch dữ liệu, rất phù hợp với những gì chúng ta đã đề cập ở đây.