Загальна формула
=SUMPRODUCT(--ISNUMBER(SEARCH(keywords,A1)))
Резюме
Щоб підрахувати кількість конкретних слів або ключових слів, які з’являються в певній комірці, ви можете використовувати формулу, засновану на функціях ПОШУК, ЧИСЛО та ПІДГОТОВКА. У наведеному прикладі формула в С5 має вигляд:
=SUMPRODUCT(--ISNUMBER(SEARCH(keywords,B5)))
де "ключові слова" - названий діапазон E5: E9.
Пояснення
Примітка: якщо ключове слово з’являється кілька разів у даній комірці, воно буде враховане лише один раз. Іншими словами, формула враховує лише екземпляри різних ключових слів.
Ядром цієї формули є підхід ISNUMBER + SEARCH до пошуку тексту в комірці, який більш детально пояснюється тут. У цьому випадку ми шукаємо в кожній клітинці всі слова з названого діапазону "ключові слова" (E5: E9). Ми робимо це, передаючи діапазон в SEARCH як аргумент find_text. Оскільки ми передаємо масив із 5 елементів:
("green";"orange";"white";"blue";"pink")
в результаті ми отримуємо масив з 5 елементів:
(#VALUE!;#VALUE!;1;#VALUE!;14)
Номери відповідають збігам, а #VALUE! помилка означає, що збіг не знайдено. У цьому випадку, оскільки нам все одно, де текст був знайдений у комірці, ми використовуємо ISNUMBER для перетворення масиву у значення TRUE та FALSE:
(FALSE;FALSE;TRUE;FALSE;TRUE)
І подвійний мінус (-), щоб змінити їх на 1s та нулі:
(0;0;1;0;1)
Потім функція SUMPRODUCT просто повертає суму масиву, 2 у цьому випадку.
Обробка порожніх ключових слів
Якщо діапазон ключових слів містить порожні клітинки, формула не працюватиме належним чином, оскільки функція SEARCH повертає нуль при пошуку порожнього рядка (""). Щоб відфільтрувати будь-які порожні клітинки в діапазоні ключових слів, ви можете використовувати варіант нижче:
(=SUMPRODUCT(--ISNUMBER(SEARCH(IF(keywords"",keywords),B5))))
Примітка: ця версія формула масиву і повинна бути введена за допомогою control + shift + enter.
