COUNTBLANK và các hàm khác để đếm các ô trống trong Excel

Trong một vài bài đăng gần đây, chúng tôi đã thảo luận nhiều cách khác nhau để xác định các ô trống và khoảng trống nổi bật trong Excel. Tuy nhiên, trong một số tình huống, bạn có thể muốn biết có bao nhiêu ô không có gì trong đó. Microsoft Excel cũng có một chức năng đặc biệt cho việc này. Hướng dẫn này sẽ chỉ cho bạn các phương pháp nhanh nhất và thuận tiện nhất để có được số lượng ô trống trong một phạm vi cũng như các hàng hoàn toàn trống.

Hàm COUNTBLANK của Excel

Hàm COUNTBLANK trong Excel được thiết kế để đếm các ô trống trong một phạm vi được chỉ định. Nó thuộc danh mục các chức năng Thống kê và có sẵn trong tất cả các phiên bản Excel cho Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 và Excel 2007.

Cú pháp của hàm này rất đơn giản và chỉ cần một đối số:

COUNTBLANK (phạm vi)

Ở đâu phạm vi là phạm vi của các ô trong đó khoảng trống sẽ được tính.

Dưới đây là một ví dụ về công thức COUNTBLANK trong Excel ở dạng đơn giản nhất:

=COUNTBLANK(A2:D2)

Công thức, được nhập vào E2 và được sao chép xuống E7, xác định số lượng ô trống trong các cột từ A đến D trong mỗi hàng và trả về các kết quả này:

Hàm COUNTBLANK – 3 điều cần nhớ

Để sử dụng hiệu quả một công thức Excel để đếm các ô trống, điều quan trọng là phải hiểu những ô nào mà hàm COUNTBLANK coi là “khoảng trống”.

1. Các ô có chứa bất kỳ văn bản, số, ngày, giá trị logic hoặc lỗi không được tính.
2. Các tế bào chứa số không được coi là không trống và không được tính.
3. Các tế bào chứa công thức trả về chuỗi rỗng (“”) Được coi là trống và được tính.

Nhìn vào ảnh chụp màn hình ở trên, xin lưu ý rằng ô A7 chứa công thức trả về một chuỗi trống được tính hai lần:

• COUNTBLANK coi một chuỗi có độ dài bằng không là một ô trống vì nó xuất hiện trống.
• COUNT coi một chuỗi có độ dài bằng không là một ô không trống bởi vì nó thực sự chứa một công thức.

Điều đó có vẻ hơi phi logic, nhưng Excel hoạt động theo cách này 🙂

Cách đếm ô trống trong Excel – ví dụ về công thức

COUNTBLANK là cách thuận tiện nhất nhưng không phải là cách duy nhất để đếm các ô trống trong Excel. Các ví dụ sau đây cho thấy một vài phương pháp khác và giải thích công thức nào là tốt nhất được sử dụng trong kịch bản nào.

Đếm các ô trống trong phạm vi với COUNTBLANK

Bất cứ khi nào bạn cần đếm khoảng trống trong Excel, COUNTBLANK là chức năng đầu tiên để thử.

Ví dụ: để lấy số lượng ô trống trong mỗi hàng trong bảng bên dưới, chúng tôi nhập công thức sau vào F2:

=COUNTBLANK(A2:E2)

Khi chúng ta sử dụng chức năng ABS đối với phạm vi, chúng ta chỉ cần kéo công thức xuống và các tham chiếu sẽ tự động điều chỉnh cho từng hàng, tạo ra kết quả sau:

Xem thêm:

Cách tạo số ngẫu nhiên trong Excel mà không bị lặp

Cách hiển thị, ẩn và khôi phục ruy băng bị thiếu trong Excel

Cách tìm và loại bỏ các ô trùng lặp trong Excel

Cách lấy mẫu ngẫu nhiên trong Excel mà không có bản sao

Cách đếm các ô trống trong Excel bằng cách sử dụng COUNTIFS hoặc COUNTIF

Một cách khác để đếm các ô trống trong Excel là sử dụng Countif hoặc Countifs hàm hoặc với một chuỗi rỗng (“”) làm tiêu chí.

Trong trường hợp của chúng tôi, các công thức sẽ như sau:

=COUNTIF(B2:E2, "")

Hoặc là

=COUNTIFS(B2:E2, "")

Như bạn có thể thấy trong ảnh chụp màn hình bên dưới, kết quả của COUNTIFS hoàn toàn giống với kết quả của COUNTBLANK, vì vậy sử dụng công thức nào trong kịch bản này là vấn đề sở thích cá nhân của bạn.

Đếm các ô trống có điều kiện

Trong một tình huống, khi bạn muốn đếm các ô trống dựa trên một số điều kiện, Countif là chức năng phù hợp để sử dụng như cú pháp của nó cung cấp cho nhiều tiêu chí.

Ví dụ: để xác định số lượng ô có “Apples” trong cột A và khoảng trống trong cột C, hãy sử dụng công thức này:

=COUNTIFS(A2:A9, "apples", C2:C9, "")

Hoặc nhập điều kiện vào một ô được xác định trước, giả sử F1 và gọi ô đó là tiêu chí:

=COUNTIFS(A2:A9, F1, C2:C9, "")

NẾU QUỐC GIA trong Excel

Trong một số trường hợp, bạn có thể không chỉ cần đếm các ô trống trong một phạm vi, mà thực hiện một số hành động tùy thuộc vào việc có bất kỳ ô trống nào hay không.

Mặc dù không có hàm IF COUNTBLANK tích hợp trong Excel, bạn có thể dễ dàng tạo công thức của riêng mình bằng cách sử dụng IF và hàm COUNTBLANK cùng nhau. Đây là cách thực hiện:

• Kiểm tra xem số lượng khoảng trống có bằng không và đưa biểu thức này vào kiểm tra logic của IF:
  COUNTBLANK(B2:D2)=0
• Nếu kiểm tra logic đánh giá thành TRUE, hãy xuất “Không khoảng trống”.
• Nếu kiểm tra logic đánh giá thành FALSE, hãy xuất “Khoảng trống”.

Công thức hoàn chỉnh có hình dạng này:

=IF(COUNTBLANK(B2:D2)=0, "No blanks", "Blanks")

Kết quả là, công thức xác định tất cả các hàng thiếu một hoặc nhiều giá trị:

Hoặc bạn có thể chạy một chức năng khác tùy thuộc vào số lượng khoảng trống. Chẳng hạn, nếu không có ô trống nào trong phạm vi B2: D2 (nghĩa là nếu COUNTBLANK trả về 0), thì tổng các giá trị, nếu không thì trả về “Khoảng trống”:

=IF(COUNTBLANK(B2:D2)=0, SUM(B2:D2), "Blanks")

Cách đếm hàng trống trong Excel

Giả sử bạn có một bảng trong đó một số hàng chứa thông tin trong khi các hàng khác hoàn toàn trống. Câu hỏi là – làm thế nào để bạn có được số lượng hàng không chứa bất cứ thứ gì trong đó?

Giải pháp đơn giản nhất mà bạn nghĩ đến là thêm một cột trợ giúp và điền nó với công thức COUNTBLANK của Excel để tìm số lượng ô trống trong mỗi hàng:

=COUNTBLANK(A2:E2)

Và sau đó, sử dụng hàm COUNTIF để tìm xem có bao nhiêu hàng tất cả các ô đều trống. Vì bảng nguồn của chúng tôi chứa 5 cột (từ A đến E), chúng tôi đếm các hàng có 5 ô trống:

=COUNTIF(F2:F8, 5))

Thay vì “mã hóa” số lượng cột, bạn có thể sử dụng hàm COLUMNS để tính toán tự động:

=COUNTIF(F2:F8, COLUMNS(A2:E2))

Nếu bạn không muốn xáo trộn cấu trúc của bảng tính được thiết kế đẹp mắt của mình, bạn có thể đạt được kết quả tương tự với công thức phức tạp hơn nhiều, tuy nhiên không yêu cầu bất kỳ cột trợ giúp nào hoặc thậm chí không nhập mảng:

=SUM(--(MMULT(--(A2:E8<>""), ROW(INDIRECT("A1:A"&COLUMNS(A2:E8))))=0))

Làm việc từ trong ra ngoài, đây là những gì công thức làm:

• Trước tiên, bạn kiểm tra toàn bộ phạm vi cho các ô không trống bằng cách sử dụng biểu thức như A2: E8 <> “”, sau đó ép các giá trị logic được trả về của TRUE và FALSE thành 1 và 0 bằng cách sử dụng toán tử đơn nguyên (-). Kết quả của hoạt động này là một mảng hai chiều của những người (không khoảng trống) và số không (khoảng trống).
• Mục đích của phần ROW là tạo ra một mảng dọc số khác không các giá trị, trong đó số phần tử bằng với số cột của phạm vi. Trong trường hợp của chúng tôi, phạm vi bao gồm 5 cột (A2: E8), vì vậy chúng tôi nhận được mảng này: {1; 2; 3; 4; 5}
• Hàm MMULT tính toán sản phẩm ma trận của các mảng trên và tạo ra kết quả như: {11; 0; 15; 8; 0; 8; 10}. Trong mảng này, điều duy nhất quan trọng đối với chúng tôi là 0 giá trị đại diện cho các hàng trong đó tất cả các ô đều trống.
• Cuối cùng, bạn so sánh từng phần tử của mảng trên với 0, ép TRUE và FALSE thành 1 và 0, sau đó tính tổng các phần tử của mảng cuối cùng này: {0; 1; 0; 0; 1; 0; 0}. Hãy nhớ rằng 1 là tương ứng với các hàng trống, bạn sẽ nhận được kết quả mong muốn.

Nếu công thức trên có vẻ quá khó để bạn hiểu, bạn có thể thích công thức này hơn:

=SUM(--(COUNTIF(INDIRECT("A"&ROW(A2:A8) & ":E"&ROW(A2:A8)), "<>"&"")=0))

Tại đây, bạn sử dụng hàm COUNTIF để tìm xem có bao nhiêu ô không trống trong mỗi hàng và INDIRECT “cung cấp” các hàng cho COUNTIF từng hàng một. Kết quả của hoạt động này là một mảng như {4; 0; 5; 3; 0; 3; 4}. Kiểm tra 0, biến đổi mảng trên thành {0; 1; 0; 0; 1; 0; 0} trong đó 1 là các hàng trống, vì vậy bạn chỉ cần thêm chúng vào.

Đếm các ô trống thực sự, trừ các chuỗi trống

Trong tất cả các ví dụ trước, chúng tôi đã đếm các ô trống bao gồm cả các ô chỉ xuất hiện trống nhưng thực tế, có chứa các chuỗi trống (“”) được trả về bởi một số công thức. Trong trường hợp bạn muốn loại trừ các chuỗi có độ dài bằng không khỏi kết quả, bạn có thể sử dụng công thức chung này:

ROWS (phạm vi) * CỘT(phạm vi) – QUỐC GIA (phạm vi)

Công thức làm là nhân số lượng hàng với số lượng cột để có được tổng số ô trong phạm vi, từ đó bạn trừ đi số lượng khoảng trống được trả về bởi COUNTA. Như bạn có thể nhớ, hàm COUNTA của Excel coi các chuỗi trống là các ô không trống, vì vậy chúng sẽ không được đưa vào kết quả cuối cùng.

Ví dụ: để xác định có bao nhiêu ô trống hoàn toàn trong phạm vi A2: A8, đây là công thức nên sử dụng:

=ROWS(A2:A8) * COLUMNS(A2:A8) - COUNTA(A2:A8)

Ảnh chụp màn hình bên dưới hiển thị kết quả:

Đó là cách đếm các ô trống trong Excel. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới!