Chúng ta có thể sử dụng các hàm COUNT, COUNTA, COUNTIF, COUNTIFS, QUERY, SUMPRODUCT, v.v., để đếm số lượng ô không trống trong Google Sheets.
Ví dụ, tôi có tên nhân viên trong phạm vi ô A2:A và ô B2:B để nhập số ngày họ đã có mặt trong tháng 1.
Tôi sẽ điền cột này khi tôi nhận được báo cáo từ thủ quỹ của mình.
Tôi muốn kiểm tra có bao nhiêu ô không trống trong B2:B tương ứng với A2:A. Như vậy tôi có thể yêu cầu anh ta nộp báo cáo chi tiết chuyên cần còn lại.
Trong tình huống cụ thể này, tôi có thể sử dụng hàm COUNT. Nếu các giá trị có định dạng văn bản, sau đó sử dụng COUNTA.
Hãy kiểm tra: Cách sử dụng Tất cả các hàm đếm của Google Sheets [Tất cả 8 Hàm đếm]
Nhưng trong một số tình huống, chúng ta có thể điểm danh điều kiện không trống trong một phạm vi trong Google Sheets.
Ví dụ, chúng ta có tên mục trong cột A và khối lượng bán hàng trong cột B.
Giả sử “táo” là một mục trong danh sách đó trong cột A.
Chúng ta có thể sử dụng COUNTIF để tìm số lần xuất hiện của mục “táo” trong cột A vì nó chấp nhận tiêu chí.
Nhưng làm thế nào để chúng ta đếm số lần xuất hiện của “táo” với khối lượng bán hàng> 0?
Chúng ta sẽ thấy tất cả trong hướng dẫn COUNT NẾU KHÔNG RỖNG này. Nhưng trước khi đi vào chi tiết, còn một điều nữa.
Khi bạn áp dụng đếm hoặc đếm nếu không trống trong Google Sheets, kết quả công thức có thể không khớp với số lượng ô không trống thực tế của bạn.
Có hai lý do chính mà tôi có thể chỉ ra.
Một là khả năng có khoảng trắng, dấu nháy đơn hoặc các ký tự ẩn trong mảng hoặc phạm vi.
Hoặc có thể bạn đang sử dụng một công thức trả về giá trị rỗng hoặc chuỗi rỗng trong phạm vi đếm / đếm nếu, ví dụ: NẾU(A1=”táo”, 100, “”) trong ô B1. Nếu giá trị A1 không phải là văn bản “táo”, công thức sẽ trả về chuỗi rỗng. Nó trả về GIẢ FAL khi bạn kiểm tra B1 với ISBLANK.
Làm thế nào để Trả về ô trống Đúng trong Công thức?
Phần này sẽ giúp bạn có kết quả chính xác khi bạn đếm các ô không trống trong Google Sheets. Áp dụng cho cả đếm nếu không trống.
Cách trả về ô trống đúng phụ thuộc vào công thức đang sử dụng.
Trong các hàm logic như IF, IFS, IFNA và IFERROR, bạn có thể để không chỉ định giá trị nếu sai, tức là phần đối số thứ hai.
Trong IF(A1=”táo”,100,””), hãy sử dụng công thức dưới đây.
=IF(A1=”táo”,100,)
Khi sử dụng IFERROR như =IFERROR(ông_formula,””) sử dụng nó như =IFERROR(your_formula,)
Mẹo
- Xác định ô trống ĐÚNG trong các hàm CƠ SỞ DỮ LIỆU:
Khi bạn muốn chỉ định tiêu chí trống (hai ô trống dọc) trong các hàm cơ sở dữ liệu, bạn có thể sử dụng {if(,,);if(,,)} hoặc VSTACK(if(,,),if(,,)).
Trong if(,,) trả về một ô thực sự trống. Một ví dụ về việc sử dụng này là Phép nhân theo hàng của Mảng 2D trong Google Sheets.
- Khi Sử Dụng Các Hàm Lambda:
Trong các hàm trợ giúp LAMBDA (LHF) SCAN và REDUCE, bạn có thể muốn chỉ định một ô trống trong phần đối số initial_value.
Ở đó bạn có thể tuân theo phương pháp hàm logic hoặc phương pháp hàm cơ sở dữ liệu. Tôi có nghĩa là, hoặc bỏ qua sử dụng initial_value hoặc chỉ định if(,,).
Ví dụ: =scan(if(,,),A2:A,Lambda(a,v,a+v)) (Công thức Tổng số trên toàn diện)
Hãy đi đếm một phạm vi nếu không trống trong Google Sheets.
Đếm các ô không trống trong một phạm vi trong Google Sheets
Chúng ta sẽ bắt đầu với cái đơn giản nhất.
Đếm, COUNTA hoặc COUNTIF Function: Đếm Tất cả Các Ô Không Trống
Trong ví dụ sau, bạn có thể tìm thấy tên nhân viên trong phạm vi ô A2:A10 và chi tiết chuyên cần của họ trong phạm vi ô B2:B10.
Hàm COUNT sau đây trong ô D2 trả về số lượng ô không trống trong phạm vi B2:B10.
=COUNT(B2:B10)
Ở đây là một cách thay thế sử dụng COUNTIF.
=COUNTIF(B2:B10,”<>”)
Làm thế nào để tôi chọn hàm chính xác cho việc đếm như vậy?
Nói một cách đơn giản, sử dụng COUNTA trong một cột văn bản và sử dụng COUNT trong một cột ngày, thời gian hoặc số.
Để đếm các ô không trống trong một cột với loại dữ liệu kết hợp, hãy sử dụng COUNTA. Nhưng COUNTIF hoạt động trong tất cả các tình huống.
Làm thế nào để loại trừ một giá trị cụ thể khỏi số lượng?
Đây là sự liên quan của đếm nếu không trống trong Google Sheets. Trước khi đến với điều đó, chúng ta phải biết làm thế nào để xử lý kết quả đếm không hợp lệ do khoảng trắng, giá trị rỗng, v.v.
QUERY, COUNTIF hoặc SUMPRODUCT Function: Đếm Các Ô Không Trống Thực Sự
QUERY không nghi ngờ là một trong những hàm ma thuật trong Google Sheets.
Người dùng Excel đã sử dụng rộng rãi SUMPRODUCT để biến các công thức thông thường thành mảng.
Ngoài hai chức năng này, chúng ta có thể sử dụng COUNTIF để đếm số lượng ô không trống “thực sự” trong Google Sheets.
Nếu một ô có giá trị hiển thị khi chúng ta áp dụng hai màu sắc khác nhau cho văn bản và nền ô, đó là ô không trống “thực sự”.
Nhưng cả ba đều yêu cầu sự trợ giúp của hàm TRIM để trả về số lượng chỉ thực sự các ô không trống trong Google Sheets. Ngoài ra, chúng có thể hữu ích trong việc đếm nếu không trống trong Google Sheets.
Trong các ví dụ sau đây, tôi đã đặt dấu nháy đơn trong ô A5 và một khoảng trống trong ô A8. Vì vậy, công thức sau sẽ trả về 9 trong khi phải là 7.
=COUNTA(A2:A10)
Ngay cả khi bạn sử dụng TRIM với nó, kết quả vẫn giống nhau. Vậy giải pháp có sẵn là gì?
SUMPRODUCT: =SUMPRODUCT(LEN(TRIM(A2:A10))>0)
COUNTIF: =ARRAYFORMULA(COUNTIF(LEN(TRIM(A2:A10))>0,TRUE))
Cả hai công thức đều yêu cầu hàm LEN.
QUERY: =ARRAYFORMULA(QUERY(TRIM(A2:A10),”Select Count(Col1) label Count(Col1)””))
TRIM loại bỏ khoảng trắng và LEN trả về độ dài của văn bản đã được cắt gọn.
Khi sử dụng cả hai chức năng này trong một mảng/phạm vi, chúng ta phải sử dụng một chức năng mảng như SUMPRODUCT, INDEX, ARRAYFORMULA hoặc SORT. Tôi đã chọn ARRAYFORMULA.
Đếm NẾU không trống với Tiêu chí trong Google Sheets
Chúng ta đã thấy một số ví dụ về cách đếm các ô trong Google Sheets. Tôi có nghĩa là các ô không trống. Là một ghi chú phụ, bạn có thể sử dụng hàm COUNTBLANK để đếm các ô trống trong Google Sheets.
Tôi ưa thích hàm COUNTA trong hầu hết các trường hợp ngoài việc đếm thực sự các ô không trống. Nó có khả năng đếm giá trị văn bản và giá trị số. COUNTIF là lựa chọn tiếp theo của tôi.
Công thức đếm nếu không trống thường dùng để đếm một cột hoặc nhiều cột theo điều kiện. Đây là một trong những tình huống mà bạn có thể thử nghiệm điều này.
COUNTIFS không trống
Tôi muốn đếm một mục cụ thể trong một cột nếu cột tương ứng không trống. Chúng ta có thể sử dụng hàm COUNTIFS cho điều này.
=COUNTIFS(A2:A,”cam”,B2:B,”<>”)
Nó đếm “cam” trong A2:A nếu các ô tương ứng trong B2:B không trống.
LAMBDA và các tùy chọn khác cho COUNT NẾU không trống trong Google Sheets
LAMBDA và các hàm trợ giúp của nó làm cho những điều không thể thành có thể trong Google Sheets.
Chúng ta có thể sử dụng hàm trợ giúp MAP Lambda để đếm nếu không trống trong Google Sheets. Đây là một phương án MAP thay thế cho công thức COUNTIFS không trống trên Google Sheets.
=SUM(MAP(A2:A,B2:B,LAMBDA(a,b,-AND(a=”cam”,b<>””))))
Còn SUMPRODUCT và QUERY thì sao?
Tất nhiên, chúng ta cũng có thể sử dụng chúng.
QUERY: =QUERY(A2:B,”Select Count(A) where A=’cam’ and B is not null label Count(A)””)
SUMPRODUCT: =SUMPRODUCT(A2:A=”cam”,B2:B<>””)
Đó là tất cả. Có thắc mắc? Hãy hỏi tôi trong phần bình luận.
