В этой статье объясняется, как подсчитывать слова в Excel с помощью функции DLSTR вместе с другими функциями Excel, и приводятся формулы для подсчета общего количества или конкретных слов в ячейке или диапазоне с учетом регистра и без учета регистра.
В Microsoft Excel есть несколько полезных функций, которые могут подсчитывать практически все: функция COUNT для подсчета ячеек с числами, COUNT для подсчета непустых ячеек, COUNTIF и COUNTIFS для подсчета условных ячеек и DLSTR для подсчета длины текстовой строки. Мы увидим разные способы подсчета слов:
- Как посчитать общее количество слов в ячейке
- Как посчитать определенные слова в ячейке
- Подсчет с учетом регистра
- Подсчитайте определенные слова, не различая верхний и нижний регистр
- Как подсчитать общее количество слов в диапазоне
- Как посчитать определенные слова в диапазоне
- Как считать слова без использования формул
К сожалению, в Excel нет встроенного инструмента подсчета слов. Но комбинируя функции, вы можете создавать более сложные выражения для решения практически любой задачи. И мы будем использовать этот подход для подсчета слов в Excel.
Как посчитать общее количество слов в ячейке
Чтобы подсчитать количество слов в ячейке, используйте следующую комбинацию функций LENGTH, SUBSTITUTE и TRIM:
=ДЛИНА(ОТРЕЗАТЬ ПРОБЕЛ(ячейка))-ДЛИНА(ЗАМЕНИТЬ(ячейка;» «;»»))+1
Здесь вы подставляете адрес ячейки, в которой хотите посчитать слова.
Например, чтобы подсчитать количество слов в ячейке A2, используйте это выражение:
=ДЛИНА(ОТРЕЗАТЬ ПРОБЕЛЫ(A2))-ДЛИНА(ПОДСТАВИТЬ(A2,» «,»»))+1
Затем вы можете скопировать его вниз по столбцу, чтобы найти количество слов в других ячейках столбца A:
Как работает эта формула подсчета слов?
Во-первых, используйте функцию ПОДСТАВИТЬ, чтобы удалить все пробелы в тексте и заменить их пустой строкой («»), чтобы функция ДЛСТР вернула количество символов без пробелов:
ДЛСТР(ПОДСТАВИТЬ(A2;» «;»»))
После этого из общей длины строки вычитается длина строки без пробелов и к конечному количеству слов прибавляется 1, так как количество слов всегда равно количеству пробелов плюс 1.
Кроме того, он использует функцию TRIM для удаления лишних пробелов в тексте, если таковые имеются. Иногда рабочий лист может содержать много невидимых на первый взгляд пробелов, например, два или более пробелов между словами или случайным образом написанных в начале или в конце текста (т.е начальные и конечные пробелы). И все они могут испортить результаты подсчета слов. Поэтому убираем все лишние пробелы, кроме обычных между словами.
Приведенную выше формулу подсчета слов в Excel можно было бы назвать идеальной, если бы не один недостаток: она возвращает число 1 для пустых ячеек. Чтобы исправить это, вы можете добавить оператор IF для проверки пустых ячеек:
=ЕСЛИ(A2=»», 0, ДЛИНА(ОТРЕЗАТЬ ПРОБЕЛЫ(A2))-ДЛИНА(ПОДСТАВИТЬ(A2,» «,»»))+1)
Как вы можете видеть на скриншоте выше, вычисление возвращает ноль для пустых ячеек и правильное количество слов для непустых ячеек.
Как посчитать конкретные слова в ячейке
Чтобы подсчитать, сколько раз встречается определенное слово, текст или подстрока, используйте следующий шаблон:
=(DLSTR(ячейка)-DLSTR(ЗАМЕНИТЬ(ячейка ; слово ;»»)))/DLSTR(слово)
Например, подсчитаем количество вхождений слова «напрасно» в A2:
=(LON($A2)-ДЛИНА(ПОДСТАВИТЬ($A2,B$1,»»)))/LON(B$1)
Наконечник. Если вы планируете копировать формулу в несколько ячеек, обязательно используйте абсолютные и относительные ссылки, как это было сделано в примере выше.
Рассмотрим пошагово, как подсчитывается количество вхождений определенного текста в ячейку
- Функция ПОДСТАВИТЬ удаляет указанное слово из исходного текста.
В этом примере мы удаляем слово, введенное в ячейку 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.
Помимо подсчета количества определенных слов в ячейке, вы можете использовать эту формулу для подсчета вхождений любого текста (подстроки). Например, вы можете посчитать, сколько раз появляется текст «Rendered»:
Как видите, часть слова здесь тоже учитывалась при расчете.
Формула с учетом регистра для подсчета определенных слов в ячейке
Как вы, наверное, знаете, функция ПОДСТАВИТЬ в Excel чувствительна к регистру. Поэтому используемая нами формула подсчета слов по умолчанию чувствительна к регистру:
Вы можете увидеть это на скриншоте выше.
Формула без учета регистра для подсчета определенных слов в ячейке
Если вам нужно подсчитать количество вхождений данного слова как в верхнем, так и в нижнем регистре, используйте функцию ВЕРХНИЙ или НИЖНИЙ РЕГИСТР в ЗАМЕНИТЬ, чтобы преобразовать исходный текст и текст, который вы хотите подсчитать, в один и тот же регистр.
=(LON(ячейка)-ДЛИНА(ЗАМЕНИТЬ(НИЖНИЙ(ячейка),НИЖНИЙ(текст),»»)))/НИЗКИЙ(текст)
или
=(LNSTR(ячейка)-LNSTR(ЗАМЕНИТЬ(ПРОПИСН(ячейка),ПРОПИСН(текст),»»)))/LLSTR(текст)
Например, чтобы подсчитать количество вхождений слова 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)}
Как подсчитать конкретные слова в диапазоне
Если вы хотите подсчитать, сколько раз определенное слово или текст появляется в строке, столбце или диапазоне ячеек, используйте аналогичный подход: возьмите формулу для подсчета определенных слов в ячейке и объедините ее с функцией СУММ или СУММПРОИЗВ:
=СУММПРОИЗВ((DLSTR(диапазон)-DLSTR(ПОДСТАВИТЬ(диапазон, слово, «»)))/DLSTR(слово))
или
=СУММ((LIN(диапазон)-LIN(ПОДСТАВИТЬ(диапазон, слово ;»»)))/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 совпадают. Только во втором случае это займет гораздо меньше времени.
Вот как вы можете считать слова в Excel.
Если ни одно из решений, обсуждаемых в этом руководстве, не помогло вам, напишите в комментариях. Я постараюсь помочь.
Вас также могут заинтересовать