Nội dung chính
Làm cách nào để tôi đếm các ô có văn bản trong Excel? Có một vài công thức khác nhau để đếm các ô có chứa bất kỳ văn bản, ký tự cụ thể hoặc chỉ các ô được lọc. Tất cả các công thức hoạt động trong Excel 2019, 2016, 2013 và 2010.
Ban đầu, bảng tính Excel được thiết kế để hoạt động với các con số. Nhưng ngày nay chúng ta thường sử dụng chúng để lưu trữ và thao tác văn bản. Bạn muốn biết có bao nhiêu ô có văn bản trong bảng tính của bạn? Microsoft Excel có một số chức năng cho việc này. Bạn nên sử dụng cái nào? Vâng, nó phụ thuộc vào tình hình. Trong hướng dẫn này, bạn sẽ tìm thấy một loạt các công thức và khi mỗi công thức là tốt nhất để sử dụng.
Cách đếm số ô có văn bản trong Excel
Có hai công thức cơ bản để tìm xem có bao nhiêu ô trong một phạm vi nhất định chứa bất kỳ chuỗi văn bản hoặc ký tự nào.
Công thức COUNTIF để đếm tất cả các ô có văn bản
Khi bạn muốn tìm số lượng ô có văn bản trong Excel, QUẬN chức năng với một dấu sao trong tiêu chí đối số là giải pháp tốt nhất và dễ nhất:
QUẬN (phạm vi, “*”)
Vì dấu hoa thị
là ký tự đại diện khớp với bất kỳ chuỗi ký tự nào, công thức tính tất cả các ô có chứa bất kỳ văn bản nào.
Công thức SUMPRODVEL để đếm các ô với bất kỳ văn bản nào Một cách khác để có được số lượng ô chứa văn bản là kết hợp TÓM TẮT
và các chức năng ISTEXT:TÓM TẮT (- ISTEXT (phạm vi
))
Hoặc làTÓM TẮT (ISTEXT (phạm vi
) * 1)
Hàm ISTEXT kiểm tra xem mỗi ô trong phạm vi đã chỉ định có chứa bất kỳ ký tự văn bản nào không và trả về một mảng các giá trị TRUE (các ô có văn bản) và FALSE (các ô khác). Phép nhân đôi (-) hoặc phép nhân nhân ép TRUE và FALSE thành 1 và 0, tương ứng, tạo ra một mảng gồm các số và số không. Hàm SUMPRODVEL tổng hợp tất cả các phần tử của mảng và trả về số 1, là số ô chứa văn bản.
| Để hiểu rõ hơn về cách thức các công thức này hoạt động, vui lòng xem giá trị nào được tính và giá trị nào không: | Những gì được tính |
|
|
Các ô trống
=COUNTIF(A2:A10, "*")
=SUMPRODUCT(--ISTEXT(A2:A10))
=SUMPRODUCT(ISTEXT(A2:A10)*1)
Ví dụ: để đếm các ô có văn bản trong phạm vi A2: A10, ngoại trừ số, ngày, giá trị logic, lỗi và ô trống, hãy sử dụng một trong các công thức sau:
Công thức Excel để đếm các ô có văn bản
Đếm các ô có văn bản không bao gồm khoảng trắng và chuỗi trống
Các công thức được thảo luận ở trên đếm tất cả các ô có bất kỳ ký tự văn bản nào trong đó. Tuy nhiên, trong một số trường hợp, điều đó có thể gây nhầm lẫn vì một số ô nhất định có thể trông trống rỗng, nhưng trên thực tế, có chứa các ký tự không nhìn thấy được bằng mắt người như chuỗi rỗng, dấu nháy đơn, dấu cách, ngắt dòng, v.v. tế bào được tính theo công thức khiến người dùng nhổ tóc cố gắng tìm hiểu tại sao 🙂 Để loại trừ các ô trống “dương tính giả” khỏi số đếm, hãy sử dụng QUẬN
chức năng với ký tự “loại trừ” trong tiêu chí thứ hai. Ví dụ: để đếm các ô có văn bản trong phạm vi A2: A7 bỏ qua các ô có chứa mộtnhân vật không gian
=COUNTIFS(A2:A7,"*", A2:A7, "<> ")
Công thức đếm các ô có văn bản không bao gồm các ô có chứa khoảng trắng Nếu phạm vi mục tiêu của bạn chứa bất kỳ dữ liệu nào theo công thức, một số công thức có thể dẫn đến chuỗi rỗng (“”). Để bỏ qua các tế bào với chuỗi rỗng cũng vậy, thay thế “*” bằng “*? *” trong tiêu chí1
=COUNTIFS(A2:A9,"*?*", A2:A9, "<> ")
tranh luận:
Một dấu hỏi được bao quanh bởi các dấu sao cho biết rằng phải có ít nhất một ký tự văn bản trong ô. Vì một chuỗi rỗng không có ký tự trong đó, nó không đáp ứng các tiêu chí và không được tính. Các ô trống bắt đầu bằng dấu nháy đơn (‘) cũng không được tính.
Đếm các ô có văn bản không bao gồm khoảng trắng và chuỗi trống
Cách đếm ô với văn bản nhất định trong Excel Để có được số lượng ô chứa văn bản hoặc ký tự nhất định, bạn chỉ cần cung cấp văn bản đó trong tiêu chí
đối số của hàm COUNTIF. Các ví dụ dưới đây giải thích các sắc thái. Để phù hợp với văn bản mẫuchính xác
, nhập toàn văn kèm theo dấu ngoặc kép:QUẬN (phạm vi, “bản văn
“) Để đếm các ô với một phầntrận đấu
, đặt văn bản giữa hai dấu sao, đại diện cho bất kỳ số lượng ký tự nào trước và sau văn bản:QUẬN (phạm vi, “*bản văn
* “)
=COUNTIF(A2:A7, "bananas")
Ví dụ: để tìm có bao nhiêu ô trong phạm vi A2: A7 chứa chính xác từ “chuối”, hãy sử dụng công thức này:
=COUNTIF(A2:A7, "*bananas*")
Để đếm tất cả các ô có chứa “chuối” như một phần nội dung của chúng ở bất kỳ vị trí nào, hãy sử dụng ô này:
=COUNTIF(A2:A7, D2)
Để làm cho công thức trở nên thân thiện hơn với người dùng, bạn có thể đặt tiêu chí vào một ô được xác định trước, giả sử là D2 và đặt tham chiếu ô trong đối số thứ hai:
- Tùy thuộc vào đầu vào trong D2, công thức có thể khớp với văn bản mẫu đầy đủ hoặc một phần: Để khớp hoàn toàn, hãy nhập toàn bộ từ hoặc cụm từ khi nó xuất hiện trong bảng nguồn, vdChuối
- . Để khớp một phần, hãy nhập văn bản mẫu được bao quanh bởi các ký tự đại diện, như* Chuối *
. Như công thứctrường hợp không nhạy cảm , bạn có thể không bận tâm về trường hợp thư, có nghĩa là * chuối *
Công thức để đếm các ô có chứa văn bản nhất định – khớp chính xác và một phần Ngoài ra, để đếm các ô vớitrận đấu một phần
=COUNTIF(A2:A7, "*"&D2&"*")
Công thức đếm các ô có văn bản nhất định trong Excel Để biết thêm thông tin, xin vui lòng xemCách đếm ô với văn bản cụ thể trong Excel
.
Cách đếm các ô được lọc bằng văn bản trong Excel Khi đang sử dụng Bộ lọc Excel để chỉ hiển thị dữ liệu có liên quan tại một thời điểm nhất định, đôi khi bạn có thể cần phải đếmcác ô hiển thị với văn bản
. Đáng tiếc, không có giải pháp một cú nhấp chuột cho nhiệm vụ này, nhưng ví dụ dưới đây sẽ thoải mái đưa bạn qua các bước. Giả sử, bạn có một bảng như thể hiện trong hình dưới đây. Một số mục được lấy từ cơ sở dữ liệu lớn hơn bằng các công thức và các lỗi khác nhau đã xảy ra trên đường đi. Bạn đang tìm kiếm tổng số mục trong cột A. Với tất cả các hàng có thể nhìn thấy, công thức COUNTIF mà chúng tôi đã sử dụng cho đếm ô có văn bản
=COUNTIF(A2:A10, "*")
điều trị
Các ô được lọc với văn bản cần được tính Để đếmcác ô được lọc với văn bản
- , đây là những gì bạn cần làm: Trong bảng nguồn của bạn, làm cho tất cả các hàng hiển thị. Đối với điều này, xóa tất cả các bộ lọc vàbỏ ẩn hàng
- . Thêm một cột trợ giúp với ĐĂNG KÝ
công thức cho biết nếu một hàng được lọc hay không. Giải quyếtcác tế bào được lọc , sử dụng 3 cho hàm_num=SUBTOTAL(3, A2)tranh luận: Để xác địnhtất cả các ô ẩn , được lọc và ẩn thủ công, đặt 103 vàohàm_num=SUBTOTAL(103, A2): Trong ví dụ này, chúng tôi chỉ muốn đếm các ô hiển thị với văn bản
bất kể các ô khác bị ẩn như thế nào, vì vậy chúng tôi nhập công thức thứ hai vào A2 và sao chép nó xuống A10.
- Xác định các ô có thể nhìn thấy Sử dụng hàm COUNTIFS với hai khác nhautiêu chí/ tiêu chí
- các cặp để đếm các ô hiển thị với văn bản:
- Tiêu chí1 – tìm kiếm các ô có bất kỳ văn bản nào (“*”) trong phạm vi A2: A10.
=COUNTIFS(A2:A10, "*", D2:D10, 1)
Criteria2 – tìm kiếm 1 trong phạm vi D2: D10 để phát hiện các ô hiển thị.
Công thức Excel để đếm các ô được lọc bằng văn bản
=SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A10))), --(ISTEXT(A2:A10)))
=SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A10, ROW(A2:A10) - MIN(ROW(A2:A10)),,1)), -- (ISTEXT(A2:A10)))
Nếu bạn không muốn chèn một cột bổ sung trong bảng tính của mình, thì bạn sẽ cần một công thức dài hơn để hoàn thành nhiệm vụ. Chỉ cần chọn một trong những bạn thích tốt hơn:
=SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A10))) * (ISTEXT(A2:A10)))
=SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A10, ROW(A2:A10)-MIN(ROW(A2:A10)),,1)) * (ISTEXT(A2:A10)))
Toán tử nhân cũng sẽ hoạt động như sau:
Công thức đếm các ô hiển thị bằng văn bản
Những công thức này hoạt động như thế nào Công thức đầu tiên sử dụng GIÁN TIẾP có chức năng “cung cấp” các tham chiếu riêng lẻ của tất cả các ô trong phạm vi được chỉ định tới SUBTOTAL. Công thức thứ hai sử dụng kết hợpBÙ LẠI
, Hàm ROW và MIN cho cùng một mục đích.
Hàm SUBTOTAL trả về một mảng 1 và 0 trong đó các ô đại diện cho các ô hiển thị và các số không khớp với các ô ẩn (như cột của trình trợ giúp ở trên).
=SUMPRODUCT({0;1;1;1;0;1;1;0;0}, {1;1;1;0;1;1;0;1;1})
Hàm ISTEXT kiểm tra từng ô trong A2: A10 và trả về TRUE nếu một ô chứa văn bản, FALSE nếu không. Toán tử đơn nguyên kép (-) ép các giá trị TRUE và FALSE thành 1 và 0. Tại thời điểm này, công thức trông như sau:
Hàm SUMPRODVEL trước tiên nhân các phần tử của cả hai mảng trong cùng một vị trí và sau đó tính tổng mảng kết quả.
=SUMPRODUCT({0;1;1;0;0;1;0;0;0})
Khi nhân với 0 sẽ cho 0, chỉ các ô được biểu thị bằng 1 trong cả hai mảng có 1 trong mảng cuối cùng.
Và số 1 trong mảng trên là số ô hiển thị có chứa văn bản.
Đó là cách tính các ô có văn bản 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!
Công thức Excel để đếm các ô có văn bản
Bạn cũng có thể quan tâm:
[ad_2]
Chat với tư vấn viên
Gọi ngay