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

Đây sẽ là một bài siêu đơn giản, ở đây mục đích của chúng ta là tìm số ô có chứa dữ liệu, các ô trống hoặc trống nên được loại trừ. Nhiệm vụ khá đơn giản nhưng câu hỏi trở thành 'làm thế nào để làm điều đó?'. Bạn biết rằng bạn muốn Excel đếm một loại ô nhất định cho bạn nhưng nếu bạn thấy mình đang băn khoăn không biết phải đặt gì trong trang tính của mình để có được số đếm đó, bạn sắp tìm ra

Hướng dẫn này sẽ hướng dẫn bạn cách sử dụng các hàm để đếm các ô không trống và cuối cùng, chúng ta cũng sẽ thấy một vài hàm để chỉ đếm các ô trống một cách có chọn lọc. Vì vậy, không có gì khó chịu, hãy bắt đầu ngay

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

Mục lục

Đếm các ô không trống

Ở đây chúng ta sẽ nói về ba hàm Excel có thể giúp chúng ta đếm các ô không trống từ một phạm vi

Sử dụng hàm COUNTIF

Hàm COUNTIF đếm số ô trong một phạm vi đáp ứng tiêu chí đã cho. COUNTIF yêu cầu phạm vi cần đếm và tiêu chí theo đó cần đếm. Để đếm tất cả các ô không trống bằng COUNTIF, chúng ta có thể sử dụng công thức sau

=COUNTIF(range,"<>")

Hãy cố gắng hiểu điều này với một ví dụ. Vì vậy, chúng tôi có một tập dữ liệu như hình dưới đây

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

Từ danh sách giảm giá sản phẩm này, chúng tôi sẽ nhắm đến việc tìm kiếm có bao nhiêu sản phẩm được giảm giá. Theo thuật ngữ của Excel, điều này có nghĩa là – chúng ta đang tìm xem có bao nhiêu ô không trống trong cột D (các ô không trống biểu thị giảm giá cho các sản phẩm và các ô trống biểu thị các sản phẩm không giảm giá)

Chúng ta có thể dễ dàng thực hiện điều này bằng cách sử dụng công thức COUNTIF sau

=COUNTIF(D3:D14,"<>")

Và kết quả cuối cùng của chúng tôi trông giống như thế này

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

Ở đây, công thức đã được nạp với phạm vi của cột D (D3. D14). Tiêu chí để tìm kiếm cột D là "<>" là chỉ báo cho các ô không trống ("" cho các ô trống)

Kết quả là hàm COUNTIF đã đếm được 8 ô không trống. Điều này cho chúng tôi biết rằng trong tập dữ liệu của chúng tôi, có 8 sản phẩm được giảm giá

Hàm COUNTIF chỉ có thể được sử dụng cho một điều kiện. Đối với nhiều điều kiện (e. g. , các ô không trống và giảm giá hơn 20%), chúng ta có thể sử dụng hàm COUNTIFS

Hàm tiếp theo chúng ta sẽ sử dụng để đếm nếu không có ô trống nào trong một phạm vi là hàm COUNTA

Sử dụng hàm COUNTA

Về bản chất, hàm COUNTA đếm các ô trong một phạm vi không trống. Đây là một hàm đối số duy nhất (ở dạng đơn giản nhất) chỉ yêu cầu phạm vi để đếm các ô không trống

Chúng tôi sẽ sử dụng nó trong ví dụ cuối cùng của chúng tôi và chỉ cần thêm phạm vi D3 của chúng tôi. D14 vào hàm COUNTA theo cách này

=COUNTA(D3:D14)

Ta có kết quả tương tự với hàm COUNTIF i. e. , 8 ô không trống, với ít đối số hơn

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

Với mục đích duy nhất là đếm các ô không trống, hàm COUNTA cung cấp giải pháp đơn giản, dễ dàng

Tuy nhiên, các hàm COUNTA và COUNTIF cũng sẽ đếm các ô có ký tự khoảng trắng và công thức trả về một chuỗi trống. Điều này có nghĩa là các hàm sẽ đếm các ô trông có vẻ trống nhưng về cơ bản không phải là ô trống

Tại sao đó là một vấn đề?

Về mặt kỹ thuật, một ký tự khoảng trắng hoặc một công thức trả về một chuỗi trống rất khác với một ô trống. Mặc dù chúng trông giống nhau nhưng về mặt kỹ thuật chúng là hai thứ khác nhau

Nhưng vì mục đích của chúng ta ở đây là đếm các ô không trống, nên chúng ta có thể muốn các ô trống được tính là các ô trống (về mặt kỹ thuật, mặc dù chúng không trống). Để thực hiện điều này thay vì sử dụng hàm COUNTIF hoặc COUNTA, tốt hơn là sử dụng hàm TỔNG KẾT. Và đây là những gì chúng ta sẽ thấy trong phần tiếp theo

Mẹo. Nếu bạn nhất quyết sử dụng hàm COUNTIF hoặc COUNTA để đếm các ô không trống, thì có một cách khác là lọc dữ liệu và loại bỏ các ô có giá trị ẩn. Điều này sẽ tinh chỉnh kết quả mà không bao gồm các ô trống giả. Bây giờ, chúng ta sẽ chuyển sang hàm TÓM TẮT

Sử dụng chức năng TÓM TẮT

TỔNG KẾT trả về tổng các tích của các phạm vi hoặc mảng được cung cấp. Để đếm các ô không trống bằng hàm SUMPRODUCT, chúng ta có thể sử dụng công thức dưới đây

=SUMPRODUCT(--(C2:C13<>""))

Hãy cố gắng hiểu công thức trước và sau đó chúng ta có thể so sánh nó với các hàm COUNTIF và COUNTA

Trong công thức trên, trước hết, chúng tôi đang kiểm tra xem các giá trị trong phạm vi C2. C13 bằng một chuỗi rỗng (không có gì). Điều này trả về một mảng các giá trị boolean như thế này –

=SUMPRODUCT(--({TRUE, TRUE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE, FALSE, TRUE, TRUE, TRUE}))

Bây giờ, vì hàm SUMPRODUCT không thể tính tổng các giá trị boolean nên chúng tôi đang chuyển đổi chúng thành 0 và 1 bằng cách sử dụng phủ định kép. Vì vậy, công thức được đơn giản hóa hơn nữa thành một cái gì đó như thế này -

=SUMPRODUCT({1, 1, 1, 0, 1, 1, 1, 1, 0, 1, 1, 1})

Hàm TÓM TẮT cộng tất cả các số 1 và 0 này rồi trả về kết quả cuối cùng

Một điều quan trọng cần lưu ý ở đây là công thức trên không coi ký tự khoảng trắng là một chuỗi rỗng (không có gì) nên nó vẫn được tính là một ô không trống. Để tính chúng là khoảng trống, chúng ta có thể sử dụng hàm TRIM và công thức cuối cùng sẽ như thế này –

=SUMPRODUCT(--(TRIM(C2:C13)<>""))

Mẹo chuyên nghiệp. Thay vì sử dụng các phủ định kép để chuyển đổi các giá trị boolean thành 1 và 0, chúng ta cũng có thể thêm 0 vào chúng hoặc nhân chúng với 1 và kết quả cuối cùng của chúng ta sẽ vẫn như vậy

Bây giờ, hãy đặt các hàm COUNTIF, COUNTA và SUMPRODUCT cạnh nhau và phân tích chúng

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

Những gì chúng ta có thể thấy từ việc đánh số sê-ri là có 12 hàng trong tập dữ liệu của chúng ta. C5 là ô trống được COUNTIF và COUNTA diễn giải chính xác là ô trống. Dường như có thêm 2 ô trống (C8 chứa ký tự khoảng trắng và C10 chứa công thức trả về chuỗi rỗng i. e. , = "") trong phạm vi có nghĩa là có 9 ô không trống

Mặc dù các hàm COUNTIF và COUNTA coi C5 là ô trống nhưng chúng coi hai hàm C8 và C10 còn lại là ô không trống và do đó trả về 11 làm số đếm

Mặt khác, công thức dựa trên TÓM TẮT xác định chính xác tất cả các ô như vậy và do đó trả về 9 dưới dạng số lượng ô không trống

đề xuất đọc. Đếm các giá trị duy nhất trong Excel

Đếm ô trống

Bây giờ đến lượt đếm các ô trống. Chúng ta có trước 2 cách rất đơn giản để đếm các ô trống với sự trợ giúp của COUNTBLANK và hàm COUNTIF

Sử dụng chức năng COUNTBLANK

Hàm COUNTBLANK, khá dễ hiểu, đếm số lượng ô trống/trống trong một phạm vi được chỉ định. Chúng tôi sẽ sử dụng chức năng rõ ràng để có được kết quả của chúng tôi

=COUNTBLANK(D3:D14)

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

Chúng tôi đã chèn phạm vi D3. D14 trong công thức để xem cột D có bao nhiêu ô trống sẽ cho ta biết số lượng sản phẩm không giảm giá. Kết quả của hàm COUNTBLANK là "4" ô trống

Sử dụng hàm COUNTIF

Như đã giải thích ở trên, hàm COUNTIF có thể được sử dụng để đếm cả ô trống và ô không trống. Bây giờ chúng ta sẽ xem cách sử dụng COUNTIF để đếm các ô trống

Để đếm các ô trống, hàm COUNTIF có thể được sử dụng như

________số 8_______

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

Trong công thức, được tạo thành từ phạm vi và tiêu chí, chúng tôi đã hoán đổi tiêu chí để đếm các ô không trống (i. e. , "<>") với tiêu chí đếm ô trống (i. e. , ""). Với phạm vi được chỉ định là D3. D14, kết quả hàm COUNTIF trả về "4" ô trống thể hiện 4 sản phẩm chưa chiết khấu

Ở phần cuối của hướng dẫn này, chúng tôi hy vọng bạn sẽ không cảm thấy trống rỗng khi cố gắng đếm nếu không trống và các ô trống. Chúng tôi đang theo dõi thêm cách thực hiện khi bạn đọc