Как подсчитать количество упоминаний слова в Excel

Обновлено: 21.11.2024

Одним из распространенных требований при работе с большими наборами данных является подсчет того, сколько раз встречается определенное слово или значение.

Возможно, вам потребуется узнать, сколько раз слово встречается в ячейке, столбце или на всем листе.

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

В этом руководстве мы рассмотрим оба типа проблем и различные способы их решения, включая использование VBA.

Оглавление

Подсчитайте определенное слово в диапазоне, используя СЧЁТЕСЛИ

Основная задача функции СЧЁТЕСЛИ — подсчитать, сколько раз выполняется условие.

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

Синтаксис функции СЧЁТЕСЛИ:

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

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

Давайте применим эту функцию к простому примеру. Допустим, мы хотим подсчитать, сколько раз имя Питер появляется в диапазоне A2:A10, как показано ниже:

Чтобы применить функцию СЧЁТЕСЛИ для вышеуказанной проблемы, выполните следующие действия:

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

Это даст количество раз, когда слово "Питер" появляется в диапазоне ячеек A2:A10.

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

Подсчитайте определенное слово в ячейке, используя ДЛСТР и ПОДСТАВИТЬ

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

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

Чтобы решить эту проблему, вы можете использовать несколько методов.

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

Функция ДЛСТР

Функция ДЛСТР — это очень часто используемая функция Excel. Он используется для нахождения длины строки (количества символов в строке). Синтаксис функции:

Где строка может быть текстом или ссылкой на ячейку, содержащую текст, для которого вы хотите найти длину.

Функция ПОДСТАВИТЬ

Функция ПОДСТАВИТЬ используется для удаления определенного слова из строки. Общий синтаксис этой функции:

original_string – это текст или ссылка на ячейку, с которой вы хотите работать.
old_text – это слово или подстрока, которую вы хотите заменить
новый_текст – это слово или подстрока, которыми вы хотите заменить старый_текст.

Объединение обеих функций

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

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

Здесь word — это слово, которое вы хотите подсчитать, а cell_reference — это ссылка на ячейку, от которой вы хотите подсчитать.

Вот как вы можете применить приведенную выше формулу к проблеме:

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

Это даст количество раз, когда слово "счастливый" появляется в ячейке A2.

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

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

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

Чтобы понять, как работает эта формула, нам нужно разбить ее на части:

Во-первых, мы использовали функцию ПОДСТАВИТЬ, чтобы удалить слово "счастливый" из исходной строки (заменив его пробелом): ПОДСТАВИТЬ(A2, "счастливый"",")
Затем мы использовали функцию ДЛСТР, чтобы найти длину исходной строки без какого-либо вхождения в нее слова «счастливый»: ДЛСТР(ПОДСТАВИТЬ(A2, «счастливый»,»))
После этого мы вычли эту длину из длины исходной строки (со словами «счастливый» в ней): ДЛСТР(A2)- ДЛСТР(ПОДСТАВИТЬ(А2, «счастливый»», «»)))
Это дает общее количество символов во всех вхождениях слова "счастливый". Это слово из 5 букв, поэтому, если оно встречается 4 раза, описанная выше операция вернет 20.
Наконец, это значение делится на длину слова "счастливый": =(LEN($A$2)-LEN(SUBSTITUTE($A$2, A5,""))) /ДЛСТР(A5).

Это должно дать вам количество раз, когда слово «счастливый» встречается в исходном тексте! Получаем 20 / 5 = 4. Слово «счастливый» встречается 4 раза в ячейке A2.

Примечание. Функция ПОДСТАВИТЬ чувствительна к регистру. В результате эта формула будет подсчитывать только количество раз, когда слово «счастливый» встречается во всех строчных буквах. Если бы вы искали слово, написанное заглавными буквами, вы бы не получили правильный ответ.

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

Здесь функция LOWER используется для преобразования исходного и нового текста в нижний регистр.

Подсчитать, сколько раз слово появляется в диапазоне

Описанный выше метод работает, только если вы хотите узнать, сколько раз слово встречается в одной ячейке. Но что, если вам нужно узнать, сколько раз одно и то же слово встречается в строках в нескольких ячейках?

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

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

Допустим, у вас есть следующий набор текстовых строк (A2:A3), и вы хотите узнать, сколько раз слово "счастливый" встречается в всех из них. :

Просто выполните следующие действия:

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

Это даст количество раз, когда слово "счастливый" появляется в диапазоне A2:A3.

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

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

Использование VBA для подсчета количества вхождений слова в любом диапазоне

Вы можете выполнить ту же задачу, что и выше, с помощью VB Script.

Вот код VBA, который мы будем использовать для подсчета количества раз, когда слово "счастливый" появляется в коде. Не стесняйтесь выбирать и копировать его.

Выполните следующие действия:

Примечание. Если вы не видите ленту Разработчик, в меню "Файл" выберите Параметры. Выберите Настроить ленту и установите флажок Разработчик на основных вкладках. Наконец, нажмите OK.

Самое замечательное в этом методе то, что не имеет значения, выбираете ли вы одну ячейку или диапазон ячеек. Это работает в обе стороны.

Вот несколько способов изменить приведенный выше код в соответствии с вашими требованиями:

Вы можете настроить этот код, изменив значение search_word в строке 3 на слово, которое вы хотите подсчитать. Поэтому, если вы хотите подсчитать, сколько раз появляется слово «потому что», вы можете заменить эту строку на: search_word = «потому что»
Вы можете использовать ссылку на ячейку вместо указания фактического слова для поиска в самом коде. Итак, если вы хотите использовать ссылку на ячейку, скажем, A6, вместо того, чтобы указывать слово для поиска «счастливый», вы можете заменить строку 3 на: search_word = Cells(6,A”). Значение
Аналогично вы можете отобразить результат в ячейке вместо окна сообщения. Итак, если вы хотите отобразить количество в ячейке, скажем, B6, вместо того, чтобы отображать его в окне сообщения, вы можете заменить строку 10 на: Cells(6,B”).Value = Count
Если вы хотите, чтобы при поиске не учитывался регистр, вы можете использовать функцию LCase. Итак, вам нужно будет изменить строку 8 на: Count = Count + ((Len(cell) – Len(Replace$(LCase(cell), LCase(search_word), “”))) / Len(search_word))

В этом руководстве мы продемонстрировали, как можно подсчитать, сколько раз слово появляется в Excel. Мы показали, как подсчитать точное количество вхождений слова в диапазоне ячеек с помощью СЧЁТЕСЛИ.

Затем мы показали, как можно подсчитать, сколько раз слово появляется в строке одной ячейки, используя функции ПОДСТАВИТЬ и ДЛСТР.

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

Наконец, мы предоставили фрагмент кода VBA, чтобы вы могли быстро выполнить эту задачу.

Мы создали это руководство с учетом различных проблем, с которыми вы можете столкнуться при подсчете вхождений слова на листе. Мы надеемся, что это было полезно.

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.

В этом разделе я представлю формулу для подсчета количества экземпляров числа или слова в столбце.

Выберите пустую ячейку и введите эту формулу =СЧЁТЕСЛИ(A1:A10,"Звезды" ), нажмите клавишу Ввод, затем будет подсчитано количество вхождений слова "Звезды" в диапазоне столбца. Смотрите скриншот:

В формуле A1:A10 — это диапазон столбцов, из которого вы хотите подсчитать слово или число, а «Звезды» — это слово, которое вы хотите подсчитать. Вы можете изменить его на число, символ или другие слова по своему усмотрению.

Если вы хотите подсчитать количество символов или слов в одной ячейке, вы можете попробовать следующие методы.

Подсчет определенного символа в одной ячейке с формулой

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

Совет: в этой формуле F1 — это ячейка, из которой вы хотите подсчитать символы, а G1 — это символ, который вы хотите подсчитать, вы можете изменить их по своему усмотрению.

Подсчитайте определенное слово в одной ячейке с помощью формулы

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

Совет: в этой формуле A14 — это ячейка, которую вы используете, B14 — это слово, которое вы хотите подсчитать, вы можете изменить их по своему усмотрению.

Подсчитайте определенное слово в ячейке с помощью Kutools for Excel

Если вы не хотите запоминать формулы, вы можете применить Kutools for Excel's Count the number of the word утилита для быстрого подсчета определенного слова в диапазоне.

В некоторых случаях вам может понадобиться подсчитать количество всех слов в диапазоне или ячейке так же, как общее количество в документе Word. Очевидно, вы можете скопировать содержимое ячеек в документ Word, чтобы подсчитать общее количество слов, но здесь я представляю вам удобный инструмент добавления – Kutools for Excel с его утилитой подсчета слов в диапазоне для быстрого подсчета слов в диапазон или ячейка.

После установки Kutools for Excel сделайте следующее: (Бесплатно загрузите Kutools for Excel сейчас!)

<р>1. Выберите пустую ячейку, в которую вы поместите подсчитанный результат, нажмите Kutools > Помощник по формулам > Помощник по формулам. Смотрите скриншот:

<р>2. В диалоговом окне «Помощник по формулам» установите флажок «Фильтр», затем введите «количество» в текстовое поле, чтобы отфильтровать относительный подсчет формулы, затем нажмите «Подсчитать общее количество слов» в разделе «Выбрать формулу», а затем выберите ячейки, в которых вы хотите подсчитать количество слов. Текстовое поле "Диапазон".

<р>3. Нажмите «ОК». И подсчитано общее количество слов в выбранном диапазоне.

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

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

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

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

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

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

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

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

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

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

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