Как посчитать строки в которых содержится нужное слово

Рассматривать будем на примере статистики ключевых запросов.

У меня есть таблица значений в первом столбце - поисковый запрос, во втором - число показов в месяц.

Теперь я хочу выбрать все слова, встречающиеся в запросах и посчитать сколько раз каждое слово встречается в запросах.

Для начала нужно разбить фразы на слова.

1. Выделяем диапазон и копируем, например, в столбец "F".
2. Дальше уменьшаем ширину этого столбца до одного символа.
3. Выделяем  столбец "F" и выбираем Меню - Правка - Заполнить - Выровнять.

Таким образом получаем в одной строке одно слово.

Теперь надо выбрать уникальные слова.

Используем расширенный фильтр.

1. Выделяем нужный диапазон вместе с заголовком. 
2. Заходим Меню - Данные - Фильтр - Расширенный фильтр 
3. Выбираем "скопировать результат в другое место". Указываем куда "поместить диапазон" и отмечаем галочкой "Только уникальные записи" и щелкаем ОК..

Теперь у нас есть выборка из всех слов. Чтобы определить количество запросов в которых встречается слово составим такую формулу:

=СУММ(ЕСЛИ(ЕОШ(НАЙТИ(D2;$A$2:$A$2001));0;1))

Это формула содержит массив. Поэтому завершаем ввод через CTRL + SHIFT + ENTER.

Работает формула так

1. Функция НАЙТИ() ищет вхождение слова из ячейки "D2" в каждой из ячеек фиксированного диапазона "$A$2:$A$2001". Появляется массив значений по количеству значений равный длине диапазона "$A$2:$A$2001".
2. Если находит, то функция ЕОШ() возвращает значение "ложь", а функция ЕСЛИ() возвращает 1, в противном случае ноль. Массив состоит из нулей и единиц.
3. СУММ() суммирует все значения  массива.

Копируем формулу вниз.
Получаем в итоге такую таблицу, показывающую сколько выражений содержат определенное слово.

Правда есть недостатки. Например, предлог "в" засчитывается как буква.

Посчитать ячейки, содержащие нужное слово, можно используя инструмент "Найти и заменить". Как это сделать описано в статье "Как найти ячейки, содержащие нужное слово".

Похожие по тематике посты - еще почитать: