Công thức chung
=(LEN(text)-LEN(SUBSTITUTE(text,word,"")))/LEN(word)
Tóm lược
Nếu bạn cần đế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 ô, bạn có thể sử dụng công thức sử dụng SUBSTITUTE và LEN. Ở dạng chung của công thức ở trên, "văn bản" đại diện cho một ô chứa văn bản và "từ" đại diện cho từ hoặc chuỗi con đang được đếm. Trong ví dụ, chúng tôi đang sử dụng công thức này:
=(LEN(B4)-LEN(SUBSTITUTE(B4,C4,"")))/LEN(C4)
Giải trình
B4 là ô mà chúng tôi đang đếm các từ trong và C4 chứa chuỗi con (từ hoặc bất kỳ chuỗi con nào) mà bạn đang đếm.
SUBSTITUTE xóa chuỗi con khỏi văn bản gốc và LEN tính độ 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.
Cuối cùng, 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 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 xuất hiện hai lần trong văn bản gốc.
Xử lý 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ế:
=(LEN(B4)-LEN(SUBSTITUTE(UPPER(B4),UPPER(C4),"")))/LEN(C4)
Bởi vì công thức này chuyển đổi chuỗi con và văn bản thành chữ hoa trước khi thực hiện thay thế, nó sẽ hoạt động tốt như nhau với văn bản trong mọi trường hợp.
Xử lý sự cố
Đếm từ trong Excel rất khó vì Excel không hỗ trợ biểu thức chính quy. Do đó, rất khó để nhắm mục tiêu chính xác các từ bạn muốn đếm, trong khi bỏ qua các chuỗi con và các kết quả phù hợp từng phần khác (tức là tìm "cáo" nhưng không tìm "cáo"). Các biến thể về dấu câu và chữ hoa khiến cho vấn đề này trở nên khá khó khăn.
Một cách giải quyết là sử dụng một công thức khác trong cột trợ giúp để "chuẩn hóa văn bản" ở bước đầu tiên. Sau đó, sử dụng công thức trên trang này để đếm các từ được bao bọc trong các ký tự khoảng trắng để có được số lượng chính xác (tức là bạn có thể tìm "con cáo" trong văn bản chuẩn hóa.
Lưu ý: cách tiếp cận này chỉ tốt khi văn bản chuẩn hóa mà bạn có thể tạo và bạn có thể cần phải điều chỉnh công thức chuẩn hóa nhiều lần để có được kết quả bạn cần.
