Посчитать слова в ячейках столбца

Посчитать количество слов в ячейке Excel можно формулой =ДЛСТР(СЖПРОБЕЛЫ(A2))-ДЛСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A2);" ";""))+1: слов всегда на одно больше, чем пробелов между ними, а СЖПРОБЕЛЫ убирает лишние пробелы. Чтобы пустая ячейка давала 0, используйте вариант =ДЛСТР(СЖПРОБЕЛЫ(A2&" ."))-ДЛСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A2&" .");" ";"")). Сколько раз встречается определённое слово, считает связка ДЛСТР и ПОДСТАВИТЬ, а в каком числе ячеек столбца оно есть — =СЧЁТЕСЛИ(A2:A100;"*слово*"). В !SEMTools для этого есть функция =слов(A1) и процедура подсчёта слов.

Как всем известно, 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 - само это слово. А так это выглядит на практике:

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

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

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

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

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

Количество ячеек, в которых встречается слово (фрагмент):

=СЧЁТЕСЛИ(A2:A100;"*кот*")

Общее количество вхождений слова во всех ячейках диапазона — та же формула, что для одной ячейки, внутри СУММПРОИЗВ (слово в ячейке B1):

=СУММПРОИЗВ((ДЛСТР(A2:A100)-ДЛСТР(ПОДСТАВИТЬ(СТРОЧН(A2:A100);СТРОЧН(B1);"")))/ДЛСТР(B1))

А общее число слов во всём столбце даёт =СУММПРОИЗВ(ДЛСТР(СЖПРОБЕЛЫ(A2:A100&" ."))-ДЛСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A2:A100&" .");" ";""))) — пустые ячейки в нём дают 0.

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

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

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

Посчитать слова в каждой ячейке с !SEMTools

Поскольку мне часто приходилось считать слова в ячейках, я реализовал процедуру в своей надстройке. Она доступна в полной версии надстройки (Lite, Pro) и находится в разделе ИЗВЛЕЧЬ:

Также в надстройке присутствуют функции slov и слов, возвращающие количество слов в ячейке:

Slov:

=slov(A1)

Слов:

=слов(A1)

Функции будут работать в любой книге. Достаточно скачать и установить бесплатную версию !SEMTools.

Почему количество слов считается неверно

• Пустая ячейка показывает 1 слово. Формула с «+1» не учитывает пустые ячейки. Используйте вариант с приписанной точкой или ЕСЛИ(ДЛСТР(СЖПРОБЕЛЫ(A1))=0;0;…).
• Слов больше, чем на самом деле. Отдельно стоящие тире и знаки препинания считаются словами. Сначала удалите пунктуацию.
• Слов меньше, чем на самом деле. Слова разделены неразрывными пробелами или переносами строк, которых нет в формуле. Замените их: ПОДСТАВИТЬ(A1;СИМВОЛ(160);" ").
• Слово найдено внутри другого слова. Подсчёт вхождений ищет фрагмент. Для целых слов используйте формулу с дополнительными пробелами из раздела выше.

Подсчёт слов в Excel — частые вопросы

Как посчитать количество слов в ячейке?

Формулой =ДЛСТР(СЖПРОБЕЛЫ(A1&" ."))-ДЛСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1&" .");" ";"")) — она корректно работает и с пустыми ячейками.

Как посчитать, сколько раз слово встречается в ячейке?

=(ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(СТРОЧН(A1);СТРОЧН(B1);"")))/ДЛСТР(B1), где в B1 — искомое слово.

Как посчитать количество слов в столбце?

Общее число слов — формулой с СУММПРОИЗВ из раздела про столбец. Число ячеек со словом — =СЧЁТЕСЛИ(A2:A100;"*слово*").

Есть ли в Excel функция подсчёта слов?

Встроенной нет. В надстройке !SEMTools есть функции =слов(A1) и =slov(A1), они работают даже в бесплатной версии.

Итоги: как посчитать слова в Excel

Надеюсь, на этой странице вы нашли то, что искали. Вас наверняка заинтересуют и похожие операции: посчитать символы в ячейке и N-gram анализ.