понедельник, 24 января 2011 г.

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

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

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




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

Для начала нужно разбить фразы на слова.
  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. СУММ() суммирует все значения  массива.
Копируем формулу вниз.
Получаем в итоге такую таблицу, показывающую сколько выражений содержат определенное слово.



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

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



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

3 комментария:

  1. Не поверишь чувак, я уже три дня голову ломаю, как мне автоматизировать статистику по контекстной рекламе вмлинка, и твое предложение просто бомба, я имею ввиду: =СУММ(ЕСЛИ(ЕОШ(НАЙТИ(D2;$A$2:$A$2001));0;1))

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

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

    ОтветитьУдалить
  2. Как ни странно, я тоже, как и Юрий Диньков, ломаю голову 3 дня над этим)
    Спасибо, наконец-то нашел, что искал, жаль, что не в первый же день.

    ОтветитьУдалить
  3. Используйте условное форматирование! Гораздо проще и быстрей!

    ОтветитьУдалить