Как всем известно, 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-граммы (двусловники, трехсловники и т.д.), и помимо подсчета слов, может строить сводные таблицы сопоставленных с текстом метрик. Подробно можно почитать в статье и посмотреть на видео ниже.