Подсчитать количество слов в ячейке Excel

Обновлено: 24.11.2024

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=(LEN(A2)-LEN(SUBSTITUTE(A2, "луна","")))/LEN("луна")

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

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

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

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

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

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

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

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

Если вам нужно подсчитать вхождения данного слова как в верхнем, так и в нижнем регистре, используйте функцию UPPER или LOWER внутри SUBSTITUTE, чтобы преобразовать исходный текст и текст, который вы хотите подсчитать, в один и тот же регистр.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Чтобы сделать формулу нечувствительной к регистру, используйте функцию ВЕРХНЯЯ или НИЖНЯЯ:

=СУММПРОИЗВ((ДЛСТР(A2:A4)-ДЛСТР(ПОДСТАВИТЬ((НИЖНИЙ(A2:A4)),НИЖНИЙ(C1),"")))/ДЛСТР(C1))

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

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

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

- формулы для подсчета всех или определенных символов в ячейке или диапазоне.

32 комментария к "Как считать слова в Excel"

Привет,
Если в ячейке "B" есть какое-то число от 1 до 30. И в ячейке "A", если я хочу дать оценки от 5 до 1 на основе чисел от 1 до 30. Пример:
если оценка от 25 до 30, то оценка «5»,
если оценка от 20 до 25, то 4 оценки,
если от 15 до 20, то 3 оценки,
если от 10 до 15 - 2 оценки и
если меньше 10 - 1 оценка.

Пожалуйста, помогите мне с формулой

Привет!
Этот вопрос задавался много раз в блоге. См. ответ в этой статье: Вложенный оператор IF в Excel.
Надеюсь, я ответил на ваш вопрос.

Office 365 профессиональный плюс переименовывается в Приложения Microsoft 365 для предприятий. Для получения дополнительной информации об этом изменении прочитайте эту запись в блоге.

Обзор

Эта статья содержит и описывает формулы, которые вычисляют следующее:

Количество вхождений текстовой строки в диапазоне ячеек.
Количество вхождений символа в одной ячейке.
Количество вхождений символа в диапазоне ячеек.
Количество слов (или текстовых строк), разделенных символом в ячейке.

Дополнительная информация

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

=СУММ(ДЛСТР(диапазон)-ДЛСТР(ПОДСТАВИТЬ(диапазон,"текст","")))/ДЛСТР("текст")

Где диапазон — это рассматриваемый диапазон ячеек, а «текст» заменяется конкретной текстовой строкой, которую вы хотите подсчитать.

Приведенная выше формула должна быть введена как формула массива. Чтобы ввести формулу в виде массива в Excel для Windows, нажмите клавиши CTRL+SHIFT+ВВОД. Чтобы ввести формулу в виде массива в Excel для Macintosh, нажмите COMMAND+RETURN.

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

Пример 1. Подсчет количества вхождений текстовой строки в диапазоне

Запустите Excel и откройте новую книгу.

Введите на листе 1 следующее:

Значение ячейки A8 равно 4, поскольку текст "яблоко" встречается в диапазоне четыре раза.

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

=LEN(cell_ref)-LEN(ЗАМЕНИТЬ(cell_ref,"a",""))

Где cell_ref — это ссылка на ячейку, а «a» заменяется символом, который вы хотите подсчитать.

Эту формулу не нужно вводить как формулу массива.

Пример 2. Подсчет количества вхождений символа в одной ячейке

Используйте те же данные из предыдущего примера; предполагая, что вы хотите подсчитать количество вхождений символа "p" в A7. Введите следующую формулу в ячейку A9:

Значение ячейки A9 равно 3, поскольку символ "p" встречается в ячейке A7 три раза.

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

=СУММ(ДЛСТР(диапазон)-ДЛСТР(ПОДСТАВИТЬ(диапазон,"а","")))

Где диапазон — это рассматриваемый диапазон ячеек, а «а» заменяется символом, который вы хотите подсчитать.

Приведенная выше формула должна быть введена как формула массива. Чтобы ввести формулу в виде формулы массива в Excel, нажмите клавиши CTRL+SHIFT+ВВОД.

Пример 3. Подсчет количества вхождений символа в диапазоне

Используйте те же данные из предыдущего примера; если вы хотите подсчитать количество вхождений или символ "p" в A2:A7. Введите следующую формулу в ячейку A10:

Значение ячейки A10 равно 11, так как символ "p" встречается 11 раз в A2:A7.

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

=IF(LEN(TRIM(cell_ref))=0,0,LEN(cell_ref)-LEN(SUBSTITUTE(cell_ref,char,""))+1)

Где cell_ref — ссылка на ячейку, а char — символ, разделяющий слова.

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

Пример 4. Подсчет количества слов, разделенных пробелом в ячейке

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

Запустите Excel и откройте новую книгу.

Введите на листе 1 следующее:

Формула в ячейке A2 возвращает значение 4, чтобы отразить, что строка содержит четыре слова, разделенные пробелами. Если слова разделены несколькими пробелами или слова начинаются или заканчиваются пробелом, это не имеет значения.Функция TRIM удаляет лишние пробелы, а также начальные и конечные пробелы в тексте ячейки.

В Excel вы также можете использовать макрос для подсчета вхождений определенного символа в ячейку или диапазон ячеек.

Ссылки

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

89794 Как использовать Visual Basic для приложений для подсчета вхождений символа в выделенном фрагменте в Excel

Хотите узнать количество слов в Excel? Хотите верьте, хотите нет, но в Excel нет встроенного счетчика слов.

Классный набор функций Excel (или немного VBA, если вам нравится) может легко сделать это за вас.

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

Это руководство охватывает:

Формула для подсчета слов в Excel

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

Предположим, у меня есть предложение, как показано ниже, для которого я хочу получить количество слов.

Пример 1. Когда между словами есть один пробел

Допустим, у меня есть следующий текст в ячейке A1: Выпустите кота из мешка

=ДЛСТР(A1)-ДЛСТР(ЗАМЕНИТЬ(A1," ",""))+1

В результате будет возвращено значение 7.

Вот как работает эта формула:

ДЛСТР(A1). Эта часть формулы возвращает 26 – общее количество символов в тексте в ячейке A1. Он включает в себя текстовые символы, а также символы пробела.
ПОДСТАВИТЬ(A1, ",") — эта часть формулы удаляет все пробелы из текста. Таким образом, результатом в этом случае будет Letthecat out of the bag.
LEN(SUBSTITUTE(A1", ","") – эта часть формулы подсчитывает общее количество символов в тексте без пробелов. Итак, результатом этого будет 20.
LEN(A1)-LEN(SUBSTITUTE(A1", ","")) — длина текста без пробелов будет вычтена из длины текста с пробелами. . В приведенном выше примере это будет 26-20, что равно 6.
=LEN(A1)-LEN(SUBSTITUTE(A1", ",""))+1 — мы добавляем 1 к общему результату как общее число пробелов на единицу меньше, чем общее количество слов. Например, в двух словах есть один пробел, а в трех словах - два пробела.

Теперь это хорошо работает, если у вас есть только один пробел между словами. Но это не сработает, если между словами будет больше одного пробела.

В этом случае используйте формулу из следующего примера.

Пример 2. Когда между словами несколько пробелов

Допустим, у вас есть следующий текст: Выпустите кота из мешка

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

Это аналогичная формула, используемая в приведенном выше примере, с небольшим изменением — здесь мы также использовали функцию TRIM.

Функция Excel TRIM удаляет все начальные, конечные и лишние пробелы (кроме одиночных пробелов между словами).

В остальном формула работает так же (как описано в примере 1).

Примечание. Если между словами нет пробелов, это считается одним словом.

Использование пользовательской функции VBA для подсчета слов в Excel

Приведенные выше формулы отлично работают, но если вам нужно часто подсчитывать количество слов, вы можете использовать VBA для создания пользовательской функции (также называемой пользовательской функцией).

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

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

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

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

Как работает эта формула:

Приведенный выше код VBA сначала использует функцию TRIM для удаления всех начальных, конечных и двойных пробелов из текстовой строки в указанной ячейке.

После очистки строки она использует функцию SPLIT в VBA для разделения текстовой строки на основе разделителя, который мы указали как символ пробела. Таким образом, каждое слово сохраняется как отдельный элемент в переменной Result.

Затем мы используем функцию UBOUND для подсчета общего количества элементов, сохраненных в переменных Result. Поскольку основание VBA равно 0, нам нужно добавить 1, чтобы получить общее количество слов.

Это означает, что Результат(0) сохраняет первое слово, Результат(1) сохраняет второе слово и так далее. Поскольку этот подсчет начинается с 0, нам нужно добавить 1, чтобы получить реальное количество слов.

Куда поместить этот код?

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

Ниже приведены шаги по размещению кода функции GetNumeric в рабочей книге.

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

Просто введите =Word, и формула появится в списке.

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

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

<р>1. Приведенная ниже функция TRIM возвращает строку, содержащую только обычные пробелы.

<р>2. Чтобы получить длину этой строки, добавьте функцию ДЛСТР.

<р>3. Приведенная ниже функция ПОДСТАВИТЬ возвращает строку без пробелов.

<р>4. Чтобы получить длину этой строки, добавьте функцию ДЛСТР.

<р>5. Чтобы подсчитать общее количество слов в ячейке A1, вычтите длину строки без пробелов (формула из шага 4) из длины строки, содержащей только обычные пробелы (формула из шага 2), и прибавьте 1.

Вывод: чтобы подсчитать общее количество слов в ячейке, просто посчитайте количество пробелов и добавьте к этому результату 1. 1 пробел означает 2 слова, 2 пробела означает 3 слова и т. д.

<р>6. Чтобы подсчитать общее количество слов в диапазоне A1:A2, добавьте функцию СУММПРОИЗВ и замените A1 на A1:A2.

Примечание: посетите нашу страницу о функции СУММПРОИЗВ, чтобы узнать больше об этой функции Excel.

Наконец, давайте подсчитаем, сколько раз конкретное слово встречается в ячейке или диапазоне ячеек. Этот трюк довольно крут.

<р>7. Приведенная ниже функция ДЛСТР возвращает длину исходного текста в ячейке A1.

<р>8. Приведенная ниже функция ПОДСТАВИТЬ возвращает строку без слова «собака».

<р>9. Приведенная ниже функция ДЛСТР возвращает длину этой строки.

<р>10. Вычтите длину строки без слова «собака» (формула из шага 9) из длины исходного текста в ячейке A1 (формула из шага 7).

Вывод: после удаления слова "собака" из исходного текста не хватает 12 символов.

<р>11. Мы знаем длину слова «собака» (3), поэтому слово «собака» встречается 12/3 = 4 раза в ячейке A1.

<р>12. Чтобы подсчитать, сколько раз слово «собака» встречается в диапазоне A1:A2, добавьте функцию СУММПРОИЗВ и замените A1 на A1:A2.

<р>13. Используйте функцию СЧЁТЕСЛИ в Excel, чтобы подсчитать количество ячеек, содержащих определённое слово.

Примечание: звездочка (*) соответствует последовательности из нуля или более символов. Посетите нашу страницу о функции СЧЁТЕСЛИ, чтобы узнать больше об этой замечательной функции Excel.

Читайте также: