Công thức chung
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))
Tóm lược
Để đếm số lần một từ cụ thể (hoặc bất kỳ chuỗi con nào) xuất hiện bên trong một dải ô, bạn có thể sử dụng công thức dựa trên các hàm SUBSTITUTE, LEN và SUMPRODUCT. Trong ví dụ được hiển thị, công thức trong C11 là:
=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,C2,"")))/LEN(C2))
Lưu ý: Công thức trên trang này đếm thứ của một từ trong một phạm vi. Ví dụ: nếu một ô chứa hai trường hợp của một từ, nó sẽ đóng góp 2 vào tổng số. Nếu bạn chỉ muốn đếm các ô có chứa một từ cụ thể, hãy xem công thức đơn giản này dựa trên hàm COUNTIF.
Giải trình
Trong phiên bản chung của công thức, rng đại diện cho phạm vi cần kiểm tra và txt là từ hoặc chuỗi con cần đếm.
Trong ví dụ được hiển thị, B5: B8 là phạm vi cần kiểm tra và C2 chứa văn bản (từ hoặc chuỗi con) để đếm.
Đối với mỗi ô trong phạm vi, SUBSTITUTE xóa chuỗi con khỏi văn bản gốc và LEN tính toán độ dài của văn bản không có chuỗi con. Con số này sau đó được trừ vào độ dài của văn bản gốc. Kết quả là số ký tự đã bị xóa bởi SUBSTITUTE.
Sau đó, số ký tự bị loại bỏ được chia cho độ dài của chuỗi con. Vì vậy, nếu một chuỗi con hoặc từ dài 5 ký tự và thiếu 10 ký tự sau khi nó bị xóa khỏi văn bản gốc, chúng ta biết chuỗi con / từ xuất hiện hai lần trong văn bản gốc.
Bởi vì phép tính ở trên được bao bọc trong hàm SUMPRODUCT, kết quả là một mảng chứa một số cho mỗi ô trong phạm vi. Những con số này đại diện cho số lần xuất hiện của chuỗi con trong mỗi ô. Đối với ví dụ này, mảng có dạng như sau: (1; 1; 0; 1)
Cuối cùng, SUMPRODUCT tổng hợp tất cả các mục trong mảng để lấy tổng số lần xuất hiện của chuỗi con trong phạm vi ô.
Bỏ qua trường hợp
SUBSTITUTE là một hàm phân biệt chữ hoa chữ thường, vì vậy nó sẽ so khớp chữ hoa chữ thường khi chạy thay thế. Nếu bạn cần đếm số lần xuất hiện cả chữ hoa và chữ thường của một từ hoặc chuỗi con, hãy sử dụng hàm UPPER bên trong SUBSTITUTE để chuyển văn bản thành chữ hoa trước khi chạy thay thế:
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE((UPPER(rng)),UPPER(txt),"")))/LEN(txt))
