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 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 một 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 ô có chứa văn bản cụ thể, nghĩa là văn bản có thể ở bất kỳ đâu trong ô. Để thực hiện việc này, chúng ta cần sử dụng ký tự dấu hoa thị (*) làm ký tự đại diện. Ví dụ: để đếm các ô có chứa ký tự "a" ở bất kỳ đâu chúng tôi sử dụng:
=COUNTIF(range,"*a*")
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:
=COUNTIF(B5:B15,"*a*") // contains "a" =COUNTIF(B5:B15,"*2*") // contains "2" =COUNTIF(B5:B15,"*-S*") // contains "-s" =COUNTIF(B5:B15,"*x*") // contains "x"
Lưu ý: COUNTIF không phân biệt chữ hoa chữ thường.
Với tham chiếu ô
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:
=COUNTIF(range,"*"&A1&"*")
Đế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ề số không.
Để đếm một số cụ thể trong nội dung số, bạn có thể sử dụng một công thức khác dựa trên hàm TÌM KIẾM và hàm ISNUMBER như sau:
=SUMPRODUCT(--(ISNUMBER(SEARCH(text,range))))
Trong công thức này, văn bả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 ISNUMBER sẽ 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ột mảng hoặc các giá trị TRUE và FALSE. Â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ên hàm FIND:
=SUMPRODUCT(--(ISNUMBER(FIND(text,range))))
Ở đây, hàm FIND được sử dụng thay vì SEARCH, vì FIND có 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.
