Возможности !SEMTools

Как всем известно, MS Excel не умеет оперировать такой сущностью, как слова, в его мире есть только ячейки и символы в них, среди которых пробелы не являются значимым разделителем. Поэтому подсчёт слов как штатная операция в Excel, в отличие от Word, отсутствует. Но при желании можно решить задачу различными способами. Рассмотрим их!

Формула для подсчета количества слов в ячейке Excel

Этот лайфхак стар как мир — если:

то слов в ячейке всегда на единицу больше, чем количество пробелов в ней.

Простейшая формула, которая решит задачу, подробно описана в статье про функцию ДЛСТР, а я лишь приведу её оттуда:

=ДЛСТР(A1) - ДЛСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);" ";"")) + 1

Эта формула не учитывает случаи, когда ячейка совсем пуста или содержит только пробелы. Чтобы учесть этот вариант, есть как минимум два варианта.

Первый вариант — проверить длину ячейки после удаления лишних пробелов и вернуть 0 слов, если 0 символов, а если нет, применять формулу выше

=ЕСЛИ(ДЛСТР(СЖПРОБЕЛЫ(A1))=0;0;ДЛСТР(СЖПРОБЕЛЫ(A1))-ДЛСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);" ";""))+1)

Второй вариант мне нравится больше. Можно добавить к ячейке любое слово заранее и из результата подсчета слов вычесть единицу. А поскольку она изначально прибавлялась к количеству пробелов, то из изначальной формулы можно удалить «+ 1» :)

Формула максимально лаконична, если использовать в качестве этого мнимого слова короткий символ, например, точку:

=ДЛСТР(СЖПРОБЕЛЫ(A1&" ."))-ДЛСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1&" .");" ";""))

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

Если заранее известно, какое слово вы хотите проверить на количество вхождений в строку, решить задачу не так уж сложно. Здесь также поможет связка функций ДЛСТР и ПОДСТАВИТЬ. И поскольку функция ПОДСТАВИТЬ регистрозависимая, для приведения символов в один регистр нужна будет еще функция СТРОЧН или ПРОПИСН (какая вам больше нравится).

Нужно:

  • измерить длину исходной строки
  • функцией ПОДСТАВИТЬ удалить из нее искомое слово, оба значения преобразовав в нижний или верхний регистр перед заменой на пустоту
  • измерить длину результата
  • вычесть из первого значения второе
  • остаток поделить на длину слова

А вот формула:

=(ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(СТРОЧН(A1);СТРОЧН(B1);"")))/ДЛСТР(B1)
подсчет встречаемости слова в ячейке по вхождению (может входить в другие слова)

Но, если вы хотите посчитать слова целиком, а не по вхождению, есть пара важных моментов:

Если произвести все эти процедуры прямо в формуле (кроме регистра), она будет выглядеть так:

=(ДЛСТР(" "&ПОДСТАВИТЬ(A1;" ";"  ")&" ")-ДЛСТР(ПОДСТАВИТЬ(" "&ПОДСТАВИТЬ(A1;" ";"  ")&" ";" "&B1&" ";"")))/ДЛСТР(" "&B1&" ")

Здесь в ячейке A1 — текст, в котором ищем слово, а в ячейке B1 — само это слово. А так это выглядит на практике:

Подсчет встречаемости точного слова в ячейке

Как посчитать определенные слова в столбце/диапазоне

Этот вопрос не самый однозначный, т.к. ищущий не всегда сам до конца осознаёт, что ищет. Объясню подробнее :)

Если вы хотите узнать, в каком количестве ячеек столбца или диапазона присутствует искомое слово, формула одна, а если допускается, что в ячейках одно и то же слово может присутствовать не единожды, формула уже другая.

Для первой достаточно будет функции СЧЁТЕСЛИ, для второго варианта потребуется формула массива, аналогичная по своей механике той, что описана выше (подсчёт слов в одной ячейке).

И опять же, если слово длинное и не может входить в другие слова, формулы будут проще, т.к. не придется использовать дополнительные пробелы, а если может входить в них — формулы нужно усложнять.

Подсчет всех уникальных слов в столбце с выводом количества повторений

В Excel отсутствует всякая возможность провернуть подобное ни штатными функциями, ни процедурами. Однако, поскольку у меня и моих коллег-маркетологов часто возникает такая потребность, я разработал и добавил такую процедуру в свою надстройку для Excel !SEMTools.

Важная особенность в том, что она умеет не только считать слова, но любые n-граммы (двусловники, трехсловники и т.д.), и помимо подсчета слов, может строить сводные таблицы сопоставленных с текстом метрик. Подробно можно почитать в статье и посмотреть на видео ниже.