Общая формула
=SUMPRODUCT(--ISNUMBER(SEARCH(keywords,A1)))
Резюме
Чтобы подсчитать количество определенных слов или ключевых слов, которые появляются в данной ячейке, можно использовать формулу, основанную на функциях ПОИСК, ЕЧИСЛО и СУММПРОИЗВ. В показанном примере формула C5 имеет следующий вид:
=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)
И двойной минус (-), чтобы заменить их на единицы и нули:
(0;0;1;0;1)
Затем функция СУММПРОИЗВ просто возвращает сумму массива, в данном случае 2.
Обработка пустых ключевых слов
Если диапазон ключевых слов содержит пустые ячейки, формула не будет работать правильно, потому что функция ПОИСК возвращает ноль при поиске пустой строки (""). Чтобы отфильтровать любые пустые ячейки в диапазоне ключевых слов, вы можете использовать следующий вариант:
(=SUMPRODUCT(--ISNUMBER(SEARCH(IF(keywords"",keywords),B5))))
Примечание: эта версия представляет собой формулу массива и должна вводиться с помощью Ctrl + Shift + Enter.
