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

Hướng dẫn thảo luận về cú pháp và cách sử dụng cơ bản của hàm COUNTBLANK để đếm số lượng ô 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ốngkhoả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 trong Excel

Tiền boa. Để đếm không trống các ô trong Excel, sử dụng QUỐC GIA chức năng.

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.

Sử dụng công thức COUNTBLANK trong Excel

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.
  • QUỐC GIA 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 tài liệu tham khảo tương đối đố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:
Công thức đếm ô ngân hàng trong Excel

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 QUẬN hoặc là QUẬN 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 trong Excel bằng cách sử dụng COUNTIFS

Đế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, QUẬN 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ó “Táo” 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, "")

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

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 NẾU 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ị:
NẾU công thức COUNTBLANK trong Excel

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")

Chạy một chức năng khác tùy thuộc vào kết quả COUNTBLANK.

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))

Đếm các hàng trống trong Excel

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))

Một công thức khác để đếm các hàng trống trong Excel

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ả:
Một công thức để đếm các ô thực sự trống, không phải các chuỗi trống

Đó 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!

Tải xuống có sẵn

Bạn cũng có thể quan tâm:

Source link

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *