Общая формула
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))
Резюме
Чтобы подсчитать, сколько раз конкретное слово (или любая подстрока) появляется внутри диапазона ячеек, вы можете использовать формулу, основанную на функциях ПОДСТАВИТЬ, LEN и СУММПРОИЗВ. В показанном примере формула в C11:
=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,C2,"")))/LEN(C2))
Примечание. Формула на этой странице подсчитывает количество слов в диапазоне. Например, если ячейка содержит два экземпляра слова, она добавит 2 к общему количеству. Если вы просто хотите подсчитать ячейки, содержащие определенное слово, ознакомьтесь с этой простой формулой, основанной на функции СЧЁТЕСЛИ.
Объяснение
В универсальной версии формулы rng представляет диапазон для проверки, а txt - это слово или подстрока для подсчета.
В показанном примере B5: B8 - это диапазон для проверки, а C2 содержит текст (слово или подстроку) для подсчета.
Для каждой ячейки в диапазоне SUBSTITUTE удаляет подстроку из исходного текста, а LEN вычисляет длину текста без подстроки. Затем это число вычитается из длины исходного текста. Результат - количество символов, которые были удалены с помощью SUBSTITUTE.
Затем количество удаленных символов делится на длину подстроки. Итак, если подстрока или слово имеет длину 5 символов, а после удаления из исходного текста отсутствуют 10 символов, мы знаем, что подстрока / слово дважды появлялось в исходном тексте.
Поскольку приведенный выше расчет заключен в функцию СУММПРОИЗВ, результатом является массив, содержащий число для каждой ячейки в диапазоне. Эти числа представляют количество появлений подстроки в каждой ячейке. В этом примере массив выглядит так: (1; 1; 0; 1)
Наконец, SUMPRODUCT суммирует все элементы в массиве, чтобы получить общее количество вхождений подстроки в диапазоне ячеек.
Игнорирование дела
SUBSTITUTE - это функция с учетом регистра, поэтому она будет соответствовать регистру при выполнении подстановки. Если вам нужно подсчитать вхождения слова или подстроки как в верхнем, так и в нижнем регистре, используйте функцию UPPER внутри SUBSTITUTE, чтобы преобразовать текст в верхний регистр перед запуском подстановки:
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE((UPPER(rng)),UPPER(txt),"")))/LEN(txt))
