Công thức chung
=SUMPRODUCT(--ISNUMBER(SEARCH(keywords,A1)))
Tóm lược
Để đếm số từ hoặc từ khóa cụ thể xuất hiện trong một ô nhất định, bạn có thể sử dụng công thức dựa trên các hàm SEARCH, ISNUMBER và SUMPRODUCT. Trong ví dụ được hiển thị, công thức trong C5 là:
=SUMPRODUCT(--ISNUMBER(SEARCH(keywords,B5)))
trong đó "từ khóa" là dải ô được đặt tên E5: E9.
Giải trình
Lưu ý: nếu một từ khóa xuất hiện nhiều hơn một lần trong một ô nhất định, nó sẽ chỉ được tính một lần. Nói cách khác, công thức chỉ tính các trường hợp của các từ khóa khác nhau.
Cốt lõi của công thức này là phương pháp ISNUMBER + SEARCH để tìm kiếm văn bản trong một ô, được giải thích chi tiết hơn ở đây. Trong trường hợp này, chúng tôi đang tìm kiếm tất cả các từ trong phạm vi được đặt tên là "từ khóa" (E5: E9) trong mỗi ô. Chúng tôi thực hiện điều này bằng cách chuyển phạm vi vào SEARCH dưới dạng đối số find_text. Bởi vì chúng tôi chuyển vào một mảng gồm 5 mục:
("green";"orange";"white";"blue";"pink")
kết quả là chúng ta nhận được một mảng gồm 5 mục:
(#VALUE!;#VALUE!;1;#VALUE!;14)
Các số tương ứng với các kết quả phù hợp và lỗi #VALUE! lỗi có nghĩa là không tìm thấy kết quả phù hợp. Trong trường hợp này, vì chúng tôi không quan tâm vị trí văn bản được tìm thấy trong ô, chúng tôi sử dụng ISNUMBER để chuyển đổi mảng thành các giá trị TRUE và FALSE:
(FALSE;FALSE;TRUE;FALSE;TRUE)
Và âm kép (-) để thay đổi chúng thành số 1 và số không:
(0;0;1;0;1)
Sau đó, hàm SUMPRODUCT chỉ cần trả về tổng của mảng, trong trường hợp này là 2.
Xử lý các từ khóa trống
Nếu phạm vi từ khóa chứa các ô trống, công thức sẽ không hoạt động chính xác, vì hàm TÌM KIẾM trả về giá trị 0 khi tìm kiếm một chuỗi trống (""). Để lọc bất kỳ ô trống nào trong phạm vi từ khóa, bạn có thể sử dụng biến thể bên dưới:
(=SUMPRODUCT(--ISNUMBER(SEARCH(IF(keywords"",keywords),B5))))
Lưu ý: phiên bản này là một công thức mảng và phải được nhập bằng control + shift + enter.
