Công thức Excel: Đếm các ô có chứa văn bản cụ thể
Công thức Excel: Đếm các ô có chứa văn bản cụ thể

Công thức chung
=COUNTIF(range,"*txt*")
Tóm lược

Để đếm các ô có chứa văn bản nhất định, bạn có thể sử dụng Hàm COUNTIF với một ký tự đại diện. Trong ví dụ được hiển thị, công thức trong E5 là:

=COUNTIF(B5:B15,"*a*")

Giải trình

Hàm COUNTIF đếm các ô trong một phạm vi đáp ứng tiêu chí. Ví dụ: để đếm số ô trong phạm vi có chứa “a”, bạn có thể sử dụng:

=COUNTIF(range,"a") // exact match

Tuy nhiên, lưu ý rằng đây là một trận đấu chính xác. Để được đếm, một ô phải chứa chính xác “a”. Nếu ô chứa bất kỳ ký tự nào khác, nó sẽ không được tính.

Đối với vấn đề được hiển thị, chúng tôi muốn đếm các ô Lưu trữ văn bản cụ thể, nghĩa là văn bản có thể ở bất kỳ đâu trong ô. Để làm điều này, chúng ta cần sử dụng dấu hoa thị nhân vật như mộtký tự đại diện

=COUNTIF(range,"*a*")

. Ví dụ: để đếm các ô có chứa ký tự “a” ở bất kỳ đâu chúng tôi sử dụng:

=COUNTIF(B5:B15,"*a*") // contains "a"
=COUNTIF(B5:B15,"*2*") // contains "2"
=COUNTIF(B5:B15,"*-S*") // contains "-s"
=COUNTIF(B5:B15,"*x*") // contains "x"

Dấu hoa thị trong công thức có nghĩa là “khớp với bất kỳ số ký tự nào, kể cả số 0”, do đó, mẫu này sẽ đếm bất kỳ ô nào có chứa “a” ở bất kỳ vị trí nào, có hoặc không có các ký tự khác ở bên trái hoặc bên phải. Tất cả các công thức được sử dụng trong ví dụ đều tuân theo cùng một mẫu:

Lưu ý: COUNTIF không phân biệt chữ hoa chữ thường.

Với tham chiếu ô

=COUNTIF(range,"*"&A1&"*")

Bạn có thể dễ dàng điều chỉnh công thức này để sử dụng nội dung của ô khác cho tiêu chí. Ví dụ: nếu A1 chứa văn bản bạn muốn đối sánh, bạn có thể sử dụng:

Đếm số lượng cụ thể

Công thức COUNTIF ở trên sẽ không hoạt động nếu bạn đang tìm kiếm một số cụ thể và các ô chứa dữ liệu số. Điều này là do ký tự đại diện tự động khiến COUNTIF chỉ tìm kiếm văn bản (nghĩa là tìm kiếm “2” thay vì chỉ 2). Bởi vì giá trị văn bản sẽ không bao giờ được tìm thấy trong một số thực, COUNTIF sẽ trả về 0. Để đếm một số cụ thể trongnội dung số , bạn có thể sử dụng một công thức khác dựa trên Chức năng tìm kiếm Hàm ISNUMBER

=SUMPRODUCT(--(ISNUMBER(SEARCH(text,range))))

như thế này: Trong công thức này, bản văn là văn bản bạn đang tìm kiếm, và phạm vi là phạm vi ô bạn muốn đếm. Khi TÌM KIẾM tìm một kết quả phù hợp, nó sẽ trả về một số và hàm ISNUMBERsẽ chuyển đổi số thành giá trị TRUE hoặc FALSE . Bởi vì chúng tôi đang kiểm tra nhiều ô, kết quả sẽ là mảng hoặc các giá trị TRUE và FALSE. Các Âm kép được sử dụng để chuyển đổi các giá trị TRUE và FALSE thành 1s và 0s, và Hàm SUMPRODUCT

sẽ trả về tổng của mảng dưới dạng kết quả cuối cùng.

TÌM KIẾM tự động coi tất cả các số là văn bản, vì vậy, nó sẽ tìm, chẳng hạn như số 1 trong số 201. Ngoài ra, không cần sử dụng các ký tự đại diện để biểu thị vị trí, vì TÌM KIẾM sẽ tự động xem qua tất cả văn bản trong một ô.

Phiên bản phân biệt chữ hoa chữ thường Đối với số lượng phân biệt chữ hoa chữ thường, bạn có thể sử dụng công thức dựa trênHàm TÌM

=SUMPRODUCT(--(ISNUMBER(FIND(text,range))))

:

Ở đây, hàm FIND được sử dụng thay vì SEARCH, vì FIND phân biệt chữ hoa chữ thường. Ở các khía cạnh khác, công thức hoạt động giống như công thức ISNUMBER + FIND được mô tả ở trên.

https://exceljet.net/formula/count-cells-that-contain-specific-text