Как подсчитать количество слов в столбце в Excel

Обновлено: 23.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 для подсчета слов. Сначала подсчитаем общее количество слов в ячейке или диапазоне ячеек.

<р>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.

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

Примечание. Формула на этой странице подсчитываетвхождения слова в диапазоне. Например, если ячейка содержит два экземпляра слова, она будет равна 2 в общем счете. Если вы просто хотите подсчитать ячейки, содержащие определенное слово, воспользуйтесь этой простой формулой, основанной на функции СЧЁТЕСЛИ.

В общей версии формулы rng представляет собой диапазон для проверки, а txt — это слово или подстрока для подсчета.

В показанном примере диапазон B5:B8 – это диапазон для проверки, а C2 содержит текст (слово или подстроку), который необходимо подсчитать.

Для каждой ячейки в диапазоне ПОДСТАВИТЬ удаляет подстроку из исходного текста, а ДЛСТР вычисляет длину текста без подстроки. Затем это число вычитается из длины исходного текста. Результатом является количество символов, которые были удалены с помощью ЗАМЕНЫ.

Затем количество удаляемых символов делится на длину подстроки. Таким образом, если подстрока или слово состоит из 5 символов, а 10 символов отсутствуют после того, как они были удалены из исходного текста, мы знаем, что подстрока/слово дважды появлялись в исходном тексте.

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

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

Игнорирование регистра

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

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