В этой статье объясняется, как подсчитывать слова в Excel с помощью функции ДЛСТР в сочетании с другими функциями Excel, а также приводятся формулы для подсчета общего или определенного количества слов в ячейке или диапазоне, чувствительных к регистру и нечувствительных к верхнему и нижнему регистру букв.
В Microsoft Excel есть несколько полезных функций, которые могут подсчитывать практически все: функция COUNT для подсчета ячеек с числами, COUNT для подсчета непустых ячеек, COUNTIF и COUNTIFS для условных счетчиков ячеек и DLSTR для вычисления длины текстовой строки. Мы рассмотрим различные способы подсчета слов:
- Как посчитать общее количество слов в ячейке
- Как посчитать определенные слова в ячейке
- Прописные и строчные буквы
- Подсчет отдельных слов без прописных и строчных букв
- Как подсчитать общее количество слов в диапазоне
- Как посчитать определенные слова в диапазоне
- Как считать слова без использования формул
К сожалению, в Excel нет встроенного инструмента подсчета слов. Но комбинируя функции, вы можете создавать более сложные выражения для решения практически любой задачи. И мы будем использовать этот подход для подсчета слов в Excel.
Как посчитать общее количество слов в ячейке
Чтобы подсчитать количество слов в ячейке, используйте следующую комбинацию функций LONG, REPLACE и TRIM:
=LANG(TRIMSPACES(ячейка))-LONG(SUBSTITUTE(ячейка ;" ";""))+1
Здесь вы заменяете адрес ячейки, в которой хотите посчитать слова.
Например, чтобы подсчитать количество слов в ячейке A2, используйте это выражение:
=ДЛИННЫЙ(ОТРЕЗАТЬ ПРОСТРАНСТВА(A2))-ДЛИН(ЗАМЕНИТЬ(A2," ",""))+1
Затем вы можете скопировать это вниз по столбцу, чтобы найти количество слов в других ячейках в столбце A:
Как работает эта формула подсчета слов?
Во-первых, используйте функцию REPLACE, чтобы удалить все пробелы в тексте и заменить их пустой строкой (""), чтобы функция DLSTR вернула количество символов без пробелов:
ДЛСТР(ЗАМЕНИТЬ(A2;" ";""))
После этого из общей длины строки вычтите длину строки без пробелов, а к конечному количеству слов прибавьте 1, так как количество слов всегда равно количеству пробелов плюс 1.
Кроме того, используйте функцию TRIM, чтобы удалить лишние пробелы в тексте. Иногда электронная таблица может содержать множество невидимых на первый взгляд пробелов, например, два или более пробелов между словами или случайным образом написанных в начале или в конце текста (то есть начальные и конечные пробелы). И все они могут испортить ваши результаты подсчета слов. Поэтому убираем все лишние пробелы, кроме обычных между словами.
Приведенную выше формулу подсчета слов в Excel можно было бы назвать идеальной, если бы не ошибка: она возвращает число 1 для пустых ячеек. Чтобы исправить это, вы можете добавить оператор IF для проверки наличия пустых ячеек:
=ЕСЛИ(A2="", 0, ДЛИННОЕ(ОБРЕЗАТЬ ПРОСТРАНСТВА(A2))-ДЛИННОЕ(ПОДСТАВИТЬ(A2," ",""))+1)
Как вы можете видеть на скриншоте выше, вычисление возвращает ноль для пустых ячеек и правильное количество слов для непустых.
Как посчитать конкретные слова в ячейке
Чтобы подсчитать, сколько раз появляется определенное слово, текст или подстрока, используйте следующий шаблон:
=(DLSTR(ячейка)-DLSTR(REPLACE(ячейка ; слово ;")))/DLSTR(слово)
Например, подсчитаем количество вхождений слова «напрасно» в A2:
=(LON($A2)-LONG(SUBSTITUTE($A2,B$1,"")))/LON(B$1)
Совет. Если вы планируете копировать формулу в несколько ячеек, обязательно используйте абсолютные и относительные ссылки, как это сделано в примере выше.
Рассмотрим пошагово, как подсчитывается количество вхождений того или иного текста в ячейку
- Функция REPLACE удаляет указанное слово из исходного текста.
В этом примере мы удаляем слово, введенное в ячейку B1, из исходного текста, находящегося в ячейке A2:
ЗАМЕНИТЬ($A2;B$1;"") - Затем функция DLSTR вычисляет длину текстовой строки без указанного слова.
В этом примере DLSTR(SUBSTITUTE($A2,B$1,"")) возвращает длину текста в ячейке A2 после удаления всех символов, найденных во всех вхождениях слова "waste». - Затем вычтите число, полученное в разделе 2, из общей длины исходного текста:
DLSTR($A2)-DLSTR(ЗАМЕНИТЬ($A2;B$1;"")) - Результатом этой операции является количество символов, найденных во всех вхождениях целевого слова, которое в данном примере равно 16 (2 вхождения слова «напрасно», по 8 символов в каждом).
- Наконец, вычисленное выше число делится на длину слова. Другими словами, вы делите количество символов во всех вхождениях целевого слова на количество символов в одном вхождении этого слова. В этом примере 16 делится на 8, и в результате получается 2.
Помимо подсчета количества определенных слов в ячейке, вы можете использовать эту формулу для подсчета вхождений любого текста (подстроки). Например, вы можете подсчитать, сколько раз появляется текст «Воспроизведено»:
Как видите, часть слова здесь тоже была включена в расчет.
Формула с учетом регистра для подсчета определенных слов в ячейке
Как вы, наверное, знаете, функция REPLACE в Excel чувствительна к регистру. Поэтому стандартная формула подсчета слов, которую мы используем, состоит из верхнего и нижнего регистра:
Вы можете увидеть это на скриншоте выше.
Формула без учета регистра для подсчета определенных слов в ячейке
Если вам нужно подсчитать количество вхождений данного слова как в верхнем, так и в нижнем регистре, используйте функцию LOWER или UPPER в REPLACE, чтобы преобразовать исходный текст и текст, который вы хотите посчитать, в одну и ту же букву.
=(LON(ячейка)-LONG(ПОДСТАВИТЬ(НИЖНИЙ(ячейка),НИЖНИЙ(текст),"")))/НИЗКИЙ(текст)
или
=(LNSTR(ячейка)-LNSTR(ЗАМЕНИТЬ(ПРОПИСН(ячейка),ПРОПИСН(текст),"")))/LLSTR(текст)
Чтобы подсчитать количество вхождений слова из B2 в ячейку A3 без учета регистра, используйте, например:
=(LONG($A3)-LONG(REPLACE(LOWER($A3),LOWER(B$2),"")))/LOW(B$2)
Как показано на снимке экрана ниже, выражение возвращает одинаковое количество слов независимо от того, как слово написано:
Как сосчитать общее количество слов в диапазоне
Чтобы узнать, сколько слов содержит строка, столбец или диапазон, возьмите формулу, подсчитывающую общее количество слов в ячейке, и соедините ее с функцией СУММПРОИЗВ или СУММ:
=СУММПРОИЗВ(ДСТР(ОТРЕЗАТЬПРОБЕЛ(диапазон))-ДСТР(ЗАМЕНИТЬ(диапазон ;" ";""))+1)
или
=СУММ(ДЛИННОЕ(ОТРЕЗАТЬ ПРОБЕЛ(диапазон))-ДЛИННОЕ(ЗАМЕНИТЬ(диапазон;" ";""))+1)
СУММПРОИЗВ — одна из немногих функций Excel, которые могут обрабатывать массивы. Поэтому вы завершаете ввод обычным способом, нажимая клавишу Enter.
Чтобы функция СУММ вычисляла массивы, ее необходимо использовать в формуле массива, заканчивающейся на Ctrl+Shift+Enter вместо обычного Enter.
Например, чтобы подсчитать все слова в столбце A2:A5, используйте один из следующих вариантов:
=СУММПРОИЗВ(DLSTR(FLAT(A2: A5))-DLSTR(ЗАМЕНИТЬ(A2: A5," ",""))+1)
или
{=SUM(LANG(TRIMSPACES(A2: A5))-LONG(REPLACE(A2: A5," ",""))+1)}
Как подсчитать конкретные слова в диапазоне
Если вы хотите подсчитать, сколько раз конкретное слово или текст встречается в строке, столбце или диапазоне ячеек, используйте аналогичный подход — возьмите формулу для подсчета конкретных слов в ячейке и объедините ее с функцией СУММ или СУММПРОИЗВ:
=СУММПРОИЗВ((DLSTR(диапазон)-DLSTR(ЗАМЕНИТЬ(диапазон, слово,"")))/DLSTR(слово))
или
=СУММ((LIN(диапазон)-LIN(REPLACE(диапазон, слово ;")))/DLSTR(слово))
Не забудьте нажать Ctrl + Shift + Enter, чтобы использовать функцию СУММ в качестве формулы массива.
Например, чтобы подсчитать все вхождения слова в C1 в столбцах A2:A5, используйте это выражение:
=СУММПРОИЗВ((LIN(A2:A5)-LIN(ЗАМЕНИТЬ(A2:A5, C1,"")))/LIN(C1))
Если вам не нужно быть чувствительным к регистру, добавьте функцию НИЖНИЙ, как вы делали ранее при подсчете в отдельной ячейке:
=СУММПРОИЗВ((ДЛСТР(A2:A5)-ДЛИН(ЗАМЕНИТЬ(НИЖНИЙ(A2:A5),НИЗКИЙ(C1),"")))/ЛЕВЫЙ(C1))
Как сосчитать слова без использования формул.
Если вам нужно быстро посчитать слова в ячейке или диапазоне, вы можете сделать это без формул. Для этого используйте инструмент «Подсчет слов», который входит в надстройку Ultimate Suite for Excel.
Я уже много рассказывал об этом замечательном инструменте, и здесь он тоже может пригодиться.
Подробный обзор функций инструмента для подсчета слов и отдельных символов в ячейках можно найти здесь, на нашем сайте.
А теперь на скриншоте ниже вы можете увидеть результаты применения. Вам нужно выбрать диапазон ячеек (или только одну из них), активировать опцию Подсчет слов, выбрать, как вы хотите получить окончательный результат: в виде числа или в виде формулы. Затем нажмите кнопку «Вставить результаты». Столбец с результатами будет вставлен справа от выделенной области.
На скриншоте выше видно, что результаты подсчета слов по формулам, рассмотренным в этой статье, и с помощью инструмента Word Count совпадают. Только во втором случае это займет у нас гораздо меньше времени.
