В статье объясняется, как подсчитывать слова в Excel с помощью функции ДЛСТР в сочетании с другими функциями Excel, а также приводятся формулы для подсчета общего количества или конкретных слов в ячейке или диапазоне с учетом и без учета регистра букв.
В Microsoft Excel есть несколько полезных функций, которые могут подсчитывать почти все: функция СЧЁТ для подсчета ячеек с числами, СЧЁТЗ для подсчета непустых ячеек, СЧЁТЕСЛИ и СЧЁТЕСЛИМН для условного подсчета ячеек и ДЛСТР для вычисления длины текстовой строки. Мы рассмотрим различные способы подсчета слов:
К сожалению, в Excel нет встроенного инструмента для подсчета количества слов. Но, комбинируя функции, вы можете создавать более сложные выражения для решения практически любой задачи. И мы будем использовать этот подход для подсчета слов в Excel.
Как посчитать общее количество слов в ячейке
Для подсчета слов в ячейке используйте следующую комбинацию функций ДЛСТР, ПОДСТАВИТЬ и СЖПРОБЕЛЫ:
=ДЛСТР(СЖПРОБЕЛЫ( ячейка ))-ДЛСТР(ПОДСТАВИТЬ( ячейка ;" ";""))+1
Сюда вы подставляете адрес ячейки, в которой вы хотите посчитать слова.
Например, чтобы пересчитать слова в ячейке A2, используйте такое выражение:
=ДЛСТР(СЖПРОБЕЛЫ(A2))-ДЛСТР(ПОДСТАВИТЬ(A2;" ";""))+1
Затем вы можете скопировать его вниз по столбцу, чтобы найти количество слов в других ячейках столбца A:
Как работает эта формула подсчета слов?
Во-первых, вы используете функцию ПОДСТАВИТЬ, удаляя этим все пробелы в тексте и заменив их пустой строкой (""), чтобы функция ДЛСТР возвратила количество символов без пробелов:
ДЛСТР(ПОДСТАВИТЬ(A2;" ";""))
После этого вы вычитаете длину строки без пробелов из общей длины строки и добавляете 1 к окончательному количеству слов, поскольку количество слов всегда равно количеству пробелов плюс 1.
Кроме того, вы используете функцию СЖПРОБЕЛЫ, чтобы удалить лишние пробелы в тексте, если они есть. Иногда рабочий лист может содержать много невидимых на первый взгляд пробелов, например, два или более между словами или случайно набранные в начале или в конце текста (то есть начальные и конечные пробелы). И все они могут испортить результаты вашего подсчета слов. Поэтому удаляем все лишние пробелы, кроме обычных между словами.
Приведенную выше формулу для подсчета слов в Excel можно было бы назвать идеальной, если бы не один недостаток: она возвращает число 1 для пустых ячеек. Чтобы исправить это, вы можете добавить оператор ЕСЛИ для проверки незаполненных ячеек:
=ЕСЛИ(A2=""; 0; ДЛСТР(СЖПРОБЕЛЫ(A2))-ДЛСТР(ПОДСТАВИТЬ(A2;" ";""))+1)
Как видно на скриншоте выше, расчёт возвращает ноль для пустых ячеек и правильное количество слов для непустых.
Как посчитать конкретные слова в ячейке
Чтобы подсчитать, сколько раз появляется определенное слово, текст или подстрока, используйте следующий шаблон:
=(ДЛСТР( ячейка )-ДЛСТР(ПОДСТАВИТЬ( ячейка ; слово ;"")))/ДЛСТР( слово )
Например, давайте посчитаем количество появлений слова "напрасно" в A2:
=(ДЛСТР($A2)-ДЛСТР(ПОДСТАВИТЬ($A2;B$1;"")))/ДЛСТР(B$1)
Совет. Если вы планируете копировать формулу в несколько ячеек, обязательно используйте абсолютные и относительные ссылки, как это сделано в примере выше.
Рассмотрим пошагово, как подсчитывается количество вхождений определенного текста в ячейку
- Функция ПОДСТАВИТЬ удаляет указанное слово из исходного текста.
В этом примере мы удаляем слово, введенное в B1, из исходного текста, расположенного в A2:
ПОДСТАВИТЬ($A2;B$1;"") - Затем функция ДЛСТР вычисляет длину текстовой строки без указанного слова.
В этом примере ДЛСТР(ПОДСТАВИТЬ($A2;B$1;"")) возвращает длину текста в ячейке A2 после удаления всех символов, содержащихся во всех вхождениях слова «напрасно». - После этого полученное в п.2 число вычитается из общей длины исходного текста:
ДЛСТР($A2)-ДЛСТР(ПОДСТАВИТЬ($A2;B$1;"")) - Результатом этой операции является количество символов, содержащихся во всех вхождениях целевого слова, которое в этом примере равно 16 (2 вхождения слова «напрасно», по 8 символов в каждом).
- Наконец, посчитанное выше число делится на длину слова. Другими словами, вы делите количество символов, содержащихся во всех вхождениях целевого слова, на количество символов, содержащихся в одном вхождении этого слова. В этом примере 16 делится на 8, и в результате мы получаем 2.
Помимо подсчета количества определенных слов в ячейке, вы можете использовать эту формулу для подсчета вхождений любого текста (подстроки). Например, вы можете подсчитать, сколько раз появляется текст «Вынес»:
Как видите, часть слова здесь тоже была учтена при подсчёте.
Формула с учетом регистра для подсчета определенных слов в ячейке
Как вы, наверное, знаете, в функции Excel ПОДСТАВИТЬ учитывается регистр букв. Поэтому используемая нами формула подсчета слов по умолчанию чувствительна к регистру:
Вы можете в этом убедиться на скриншоте выше.
Формула без учета регистра для подсчета определенных слов в ячейке
Если вам нужно подсчитать вхождения данного слова как в верхнем, так и в нижнем регистре, используйте функцию СТРОЧН или ПРОПИСН внутри ПОДСТАВИТЬ, чтобы преобразовать исходный текст и тот текст, который вы хотите подсчитать, в один и тот же регистр.
=(ДЛСТР( ячейка )-ДЛСТР(ПОДСТАВИТЬ(СТРОЧН( ячейка );СТРОЧН( текст );"")))/ДЛСТР( текст )
или
=(ДЛСТР( ячейка )-ДЛСТР(ПОДСТАВИТЬ(ПРОПИСН( ячейка );ПРОПИСН( текст );"")))/ДЛСТР( текст )
Например, чтобы подсчитать количество вхождений слова из B2 в ячейке A3 без учета регистра, используйте:
=(ДЛСТР($A3)-ДЛСТР(ПОДСТАВИТЬ(СТРОЧН($A3);СТРОЧН(B$2);"")))/ДЛСТР(B$2)
Как показано на скриншоте ниже, выражение возвращает одно и то же количество слов независимо от того, как набрано слово:
Как сосчитать общее количество слов в диапазоне
Чтобы узнать, сколько слов содержит строка, столбец или диапазон, возьмите формулу, которая подсчитывает общее количество слов в ячейке, и вставьте ее в функцию СУММПРОИЗВ или СУММ:
=СУММПРОИЗВ(ДЛСТР(СЖПРОБЕЛЫ( диапазон ))-ДЛСТР(ПОДСТАВИТЬ( диапазон ;" ";""))+1)
или
=СУММ(ДЛСТР(СЖПРОБЕЛЫ( диапазон ))-ДЛСТР(ПОДСТАВИТЬ( диапазон ;" ";""))+1)
СУММПРОИЗВ - одна из немногих функций Excel, которые умеют обрабатывать массивы. Поэтому вы завершаете ввод обычным способом, нажимая клавишу Enter.
Чтобы функция СУММ могла вычислять массивы, ее следует использовать в формуле массива, которая завершается нажатием Ctrl + Shift + Enter вместо обычного ввода Enter.
Например, чтобы подсчитать все слова в столбце A2:A5, используйте один из следующих вариантов:
=СУММПРОИЗВ(ДЛСТР(СЖПРОБЕЛЫ(A2: A5))-ДЛСТР(ПОДСТАВИТЬ(A2: A5;" ";""))+1)
или
{=СУММ(ДЛСТР(СЖПРОБЕЛЫ(A2: A5))-ДЛСТР(ПОДСТАВИТЬ(A2: A5;" ";""))+1)}
Как подсчитать конкретные слова в диапазоне
Если вы хотите подсчитать, сколько раз конкретное слово или текст появляется в строке, столбце или же в определённом диапазоне ячеек, используйте аналогичный подход — возьмите формулу для подсчета определенных слов в ячейке и объедините ее с функцией СУММ или СУММПРОИЗВ:
=СУММПРОИЗВ((ДЛСТР( диапазон )-ДЛСТР(ПОДСТАВИТЬ( диапазон ; слово ;"")))/ДЛСТР( слово))
или
=СУММ((ДЛСТР( диапазон )-ДЛСТР(ПОДСТАВИТЬ( диапазон ; слово ;"")))/ДЛСТР( слово))
Пожалуйста, не забудьте нажать Ctrl + Shift + Enter, чтобы правильно использовать функцию СУММ как формулу массива.
Например, чтобы подсчитать все вхождения слова, находящегося в C1, в столбце A2:A5, используйте это выражение:
=СУММПРОИЗВ((ДЛСТР(A2:A5)-ДЛСТР(ПОДСТАВИТЬ(A2:A5; C1;"")))/ДЛСТР(C1))
Если не нужно учитывать регистр букв, добавьте функцию СТРОЧН, как делали ранее при подсчёте в отдельной ячейке:
=СУММПРОИЗВ((ДЛСТР(A2:A5)-ДЛСТР(ПОДСТАВИТЬ(СТРОЧН(A2:A5); СТРОЧН(C1);"")))/ДЛСТР(C1))
Как сосчитать слова без использования формул.
Если нужно быстро пересчитать слова в ячейке или в диапазоне, то можно сделать это и без формул. Для этого служит инструмент «Count Words», который входит в надстройку Ultimate Suite for Excel.
Об этом замечательном инструменте я уже много рассказывал, и вот здесь он тоже может пригодиться.
Подробный обзор возможностей инструмента подсчёта слов и отдельных символов в ячейках вы можете посмотреть здесь на нашем сайте.
А сейчас на скриншоте ниже вы видите результаты его применения. Нужно выделить диапазон ячеек (или только одну из них), активировать опцию Count Words, выбрать – как вы хотите получить итоговый результат: в виде числа или формулой. После этого нажимаем кнопку Insert Results. Справа от выделенной области будет вставлен столбец с результатами.
На скриншоте выше вы видите, что результаты подсчета слов при помощи рассмотренных в этой статье формул и с использованием инструмента «Count Words» — одинаковы. Только времени во втором случае у нас уйдёт гораздо меньше.
Вот как можно сосчитать слова в Excel.
Если ни одно из решений, обсуждаемых в этом руководстве, для вас не подошло, пишите в комментариях. Постараюсь помочь.
