16 августа 2012 г.

Как подсчитать количество слов в одной ячейке в Excel

Глазами. Скажете вы. Да я не об этом.

Допустим, вы занимаетесь поисковой оптимизацией (SEO). У вас имеется список ключевых фраз в длинющем столбике в таблице Excel. (Вы загрузили их из Google Keyword Tool.)

В каждой ячейке - разное количество слов, разумеется. А вам нужно, чтобы в ячейке рядом было указано число слов в фразе. (Вы хотите отобрать "long tail" фразы, то есть ключевые фразы с большим количеством слов, например, "отдам сто пятьсот долларов в хорошие руки кацапетовка на углу".)

Я уже смотрел: нет в Excel функции "подсчитать количество слов в тексте в такой-то ячейке".

Но сделать это все-таки возможно. Как?


  1. Вставьте столбик рядом, назвав его, например, "Количество слов".
  2. В первой ячейке (скажем, B2) введите такую магическую функцию:

    =IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1)

    Обратите внимание, что я использую номер ячейки A2, которая находится слева от ячейки B2 и содержит фразу для подсчета слов. В вашем случае это может быть другая ячейка.
  3. Скопируйте содержимое ячейки вниз. (Самый простой способ, "ухватится" за правый нижний угол ячейки B2 (курсор станет черным крестиком) и протянуть это вниз по столбику, не отпуская левую кнопку "мыши".)
Для самых любопытных объясню формулу:
  1. Берем исходную фразу из ячейки, например, A2 и удаляем из нее все пробелы, за исключением одиночных пробелов между словами: TRIM(A2)
  2. В получившейся фразе, подсчитываем количество символов в ней: LEN(TRIM(A2))
  3. Опять берем исходную фразу из этой же ячейки и удаляем из нее все пробелы: (SUBSTITUTE(A2," ","")
  4. Подсчитываем количество символов в получившейся фразе: LEN(SUBSTITUTE(A2," ",""))
  5. А теперь узнаем количество удаленных пробелов (шаг 3), отняв от количества символов фразы с пробелами между словами (шаг 2) количество символов фразы с удаленными пробелами между словами (шаг 4).
  6. А так как пробелы находятся между словами, то слов всегда будет на одно больше, поэтому добавляем 1 к полученному числу: LEN(TRIM(A2)-LEN(SUBSTITUTE(A2," ",""))+1
  7. Дополнительно проверяем, чтобы в исходная ячейка не была пустой (после удаления лишних пробелов): LEN(TRIM(A2))=0.
  8. Если это условие выполняется (т.е. ячейка пуста), то возвращаем 0, иначе подсчитываем количество слов и возвращаем его: =IF(условие: ячейка пуста,0,количество подсчитанных слов)
В принципе, если вы точно знаете, что в ячейке всегда будет минимум одно слово и точно не будет лишних пробелов, то функцию можно упростить:

=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1

Теперь она даже не кажется такой страшной.

Кстати, функцию придумал не я, а великий интернет.

Комментариев нет:

Отправить комментарий