Как посчитать слова в Excel

В учебнике объясняется, как подсчитывать слова в Excel с помощью функции ДЛСТР в сочетании с другими функциями Excel, и представлены формулы с учетом и без учета регистра для подсчета общего количества или конкретных слов/текста в ячейке или диапазоне.

В Microsoft Excel есть несколько полезных функций, которые могут подсчитывать практически все: функция СЧЁТ для подсчета ячеек с числами, СЧЁТЕСЛИ для подсчёта непустых ячеек, СЧЁТЕСЛИ и СЧЁТЕСЛИМН для условного подсчёта ячеек и ДЛСТР для подсчёта длины текстовой строки.

К сожалению, в Excel нет встроенного инструмента для подсчета количества слов. К счастью, комбинируя функции serval, вы можете создавать более сложные формулы для решения практически любой задачи. И мы будем использовать этот подход для подсчета слов в Excel.

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

Чтобы подсчитать количество слов в ячейке, используйте следующую комбинацию функций ДЛСТР, ПОДСТАВИТЬ и ОБРЕЗАТЬ:

ДЛИНА(ОТРЕЗАТЬ(клетка))-ЛЕН(ЗАМЕНИТЬ(клетка,” “,””))+1

Где клетка адрес ячейки, в которой вы хотите подсчитать слова.

Например, чтобы подсчитать количество слов в ячейке A2, используйте следующую формулу:

=ДЛСТР(ОТРЕЗАТЬ(A2))-ДЛСТР(ПОДСТАВИТЬ(A2,” “,””))+1

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

Как работает эта формула подсчета слов

Во-первых, вы используете функцию ПОДСТАВИТЬ, чтобы удалить все пробелы в ячейке, заменив их пустой текстовой строкой (“”), чтобы функция ДЛСТР вернула длину строки без пробелов:

ДЛСТР(ПОДСТАВИТЬ(A2,” “,””))

После этого вы вычитаете длину строки без пробелов из общей длины строки и добавляете 1 к итоговому количеству слов, так как количество слов в ячейке равно количеству пробелов плюс 1.

Кроме того, вы используете функцию TRIM, чтобы удалить лишние пробелы в ячейке, если таковые имеются. Иногда рабочий лист может содержать много невидимых пробелов, например, два или более пробела между словами или символы пробела, случайно введенные в начале или в конце текста (т. е. начальные и конечные пробелы). И все эти лишние пробелы могут сбить ваш счет слов. Чтобы избежать этого, перед вычислением общей длины строки мы используем функцию TRIM для удаления всех лишних пробелов, кроме одиночных пробелов между словами.

Улучшенная формула, которая правильно обрабатывает пустые ячейки

Приведенную выше формулу для подсчета слов в Excel можно было бы назвать идеальной, если бы не один недостаток — она возвращает 1 для пустых ячеек. Чтобы исправить это, вы можете добавить оператор IF для проверки пустых ячеек:

=ЕСЛИ(A2=””, 0, ДЛСТР(ОТРЕЗАТЬ(A2))-ДЛСТР(ПОДСТАВИТЬ(A2,” “,””))+1)
Улучшенная формула для подсчета общего количества слов в ячейке, которая правильно обрабатывает пустые ячейки.

Как вы можете видеть на снимке экрана выше, формула возвращает ноль для пустых ячеек и правильное количество слов для непустых ячеек.

Как посчитать определенные слова в ячейке

Чтобы подсчитать, сколько раз определенное слово, текст или подстрока появляются в ячейке, используйте следующую формулу:

=(ДЛСТР(клетка)-LEN(ПОДСТАВИТЬ(клетка, слово,””)))/ЛЕН(слово)

Например, посчитаем количество “луна” вхождения в ячейку A2:

=(ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(А2, “луна”,””)))/ДЛСТР(“луна”)
Формула для подсчета конкретных слов в ячейке

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

Кончик. Если вы планируете копировать формулу в несколько ячеек, не забудьте исправить ссылку на ячейку, содержащую слово для подсчета, со знаком $. Например:
=(ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(А2, $B$1,””)))/ДЛСТР($B$1)
Вместо того, чтобы вводить слово для подсчета в формулу, введите его в какую-нибудь ячейку и укажите ссылку на эту ячейку в своей формуле.

Как эта формула подсчитывает количество вхождений определенного текста в ячейку

  1. Функция ПОДСТАВИТЬ удаляет указанное слово из исходного текста.
    В этом примере мы удаляем ввод слова в ячейке B1 из исходного текста, расположенного в ячейке A2:
    ПОДСТАВИТЬ(A2, $B$1,””)
  2. Затем функция ДЛСТР вычисляет длину текстовой строки без указанного слова.
    В этом примере ДЛСТР(ПОДСТАВИТЬ(A2, $B$1,””)) возвращает длину текста в ячейке A2 после удаления всех символов, содержащихся во всех вхождениях слова “луна“.
  3. После этого указанное выше число вычитается из общей длины исходной текстовой строки:
    (ДЛСТР(A2)-ДЛСТР(ЗАМЕНИТЬ(A2, $B$1,””)))

    Результатом этой операции является количество символов, содержащихся во всех вхождениях целевого слова, которое в данном примере равно 12 (3 вхождения слова “луна“, по 4 символа).

  4. Наконец, приведенное выше число делится на длину слова. Другими словами, вы делите количество символов, содержащихся во всех вхождениях целевого слова, на количество символов, содержащихся в одном вхождении этого слова. В этом примере 12 делится на 4 и в результате получается 3.

Помимо подсчета количества определенных слов в ячейке, вы можете использовать эту формулу для подсчета вхождений любого текста (подстроки). Например, вы можете посчитать, сколько раз текст “выбирать” появляется в ячейке A2:
Подсчет вхождений определенного текста в ячейку

Формула с учетом регистра для подсчета определенных слов в ячейке

Как вы, наверное, знаете, функция ПОДСТАВИТЬ в Excel чувствительна к регистру, поэтому формула подсчета слов, основанная на ЗАМЕНЕ, по умолчанию чувствительна к регистру:
Формула с учетом регистра для подсчета определенных слов в ячейке

Формула без учета регистра для подсчета определенных слов в ячейке

Если вам нужно подсчитать как прописные, так и строчные буквы данного слова, используйте функцию ПРОПИСНАЯ или НИЖНЯЯ внутри ПОДСТАВИТЬ, чтобы преобразовать исходный текст и текст, который вы хотите подсчитать, в один и тот же регистр.

=(ДЛСТР(клетка)-LEN(ЗАМЕНИТЬ(ВЕРХНИЙ(клетка),ВЕРХНИЙ(текст),””)))/ЛЕН(текст)

Или же

=(ДЛСТР(клетка)-ДЛСТР(ПОДСТАВИТЬ(НИЖЕ(клетка),НИЖЕ(текст),””)))/ЛЕН(текст)

Например, чтобы подсчитать количество вхождений слова в B1 в ячейке A2 без учета регистра, используйте следующую формулу:

=(ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(НИЖНИЙ(A2),НИЖНИЙ($B$1),””)))/ДЛСТР($B$1)

Как показано на снимке экрана ниже, формула возвращает одно и то же количество слов независимо от того, написано ли слово ЗАГЛАВНЫМИ (ячейка B1), строчными (ячейка D1) или в предложении (ячейка C1):
Формула без учета регистра для подсчета определенных слов в ячейке

Подсчитать общее количество слов в диапазоне

Чтобы узнать, сколько слов содержит определенный диапазон, возьмите формулу, которая подсчитывает общее количество слов в ячейке и вставьте его в функцию СУММПРОИЗВ или СУММ:

=СУММПРОИЗВ(ДЛСТР(ОТРЕЗАТЬ(диапазон))-ЛЕН(ЗАМЕНИТЬ(диапазон,” “,””))+1)

Или же

=СУММ(ДЛСТР(ОТРЕЗАТЬ(диапазон))-ЛЕН(ЗАМЕНИТЬ(диапазон,” “,””))+1)

СУММПРОИЗВ — одна из немногих функций Excel, которые могут обрабатывать массивы, и вы завершаете формулу обычным способом, нажимая клавишу Enter.

Чтобы функция SUM вычисляла массивы, ее следует использовать в формуле массива, которая завершается нажатием Ctrl+Shift+Enter вместо обычного нажатия Enter.

Например, чтобы подсчитать все слова в диапазоне A2:A4, используйте одну из следующих формул:

=СУММПРОИЗВ(ДЛСТР(ОТРЕЗАТЬ(A2:A4))-ДЛСТР(ПОДСТАВИТЬ(A2:A4,” “,””))+1)

=СУММ(ДЛСТР(ОТРЕЗАТЬ(A2:A4))-ДЛСТР(ПОДСТАВИТЬ(A2:A4,” “,””))+1)
Подсчет общего количества слов в диапазоне

Подсчет определенных слов в диапазоне

Если вы хотите подсчитать, сколько раз конкретное слово или текст появляется в диапазоне ячеек, используйте аналогичный подход — используйте формулу для посчитать определенные слова в ячейкеи объедините его с функцией СУММ или СУММПРОИЗВ:

=СУММПРОИЗВ((ДЛСТР(диапазон)-LEN(ПОДСТАВИТЬ(диапазон, слово,””)))/ЛЕН(слово))

Или же

=СУММ((ДЛСТР(диапазон)-LEN(ПОДСТАВИТЬ(диапазон, слово,””)))/ЛЕН(слово))

Не забудьте нажать Ctrl+Shift+Enter, чтобы правильно заполнить формулу СУММ массива.

Например, чтобы подсчитать все вхождения слова, введенного в ячейку C1, в диапазоне A2:A4, используйте следующую формулу:

=СУММПРОИЗВ((ДЛСТР(A2:A4)-ДЛСТР(ПОДСТАВИТЬ(A2:A4, C1,””)))/ДЛСТР(C1))
Подсчет определенных слов в диапазоне

Как вы помните, ПОДСТАВИТЬ — это функция, чувствительная к регистру, поэтому приведенная выше формула различает текст в верхнем и нижнем регистре:
Формула с учетом регистра для подсчета определенных слов в диапазоне

Чтобы сделать формулу нечувствительной к регистру, используйте функцию UPPER или LOWER:

=СУММПРОИЗВ((ДЛСТР(A2:A4)-ДЛСТР(ЗАМЕНИТЬ((ПРОГЛАВНЫЕ(A2:A4)),ПРОГЛАВНЫЕ(C1),””)))/ДЛСТР(C1))

Или же

=СУММПРОИЗВ((ДЛСТР(A2:A4)-ДЛСТР(ПОДСТАВИТЬ((НИЖНИЙ(A2:A4)),НИЖНИЙ(C1),””)))/ДЛСТР(C1))
Формула без учета регистра для подсчета определенных слов в диапазоне

Вот как вы считаете слова в Excel. Чтобы лучше понять и, возможно, перепроектировать формулы, вы можете загрузить образец книги Excel Count Words.

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

Вас также могут заинтересовать

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *