Как подсчитать, сколько раз встречается определённое слово или значение в Excel?
При повседневной работе в Excel часто возникают ситуации, когда необходимо определить, сколько раз конкретное слово или значение встречается в одной ячейке или в диапазоне ячеек. Например, вы можете захотеть проверить, насколько часто используется определённое ключевое слово в текстовых данных, подсчитать количество появлений конкретного элемента в наборе данных или проанализировать согласованность содержимого. Точный подсчёт вхождений помогает при проверке данных, анализе текста, инвентаризации, обработке результатов опросов и многом другом. В этой статье представлены несколько практических методов подсчёта вхождений слов или значений в Excel и даны рекомендации по выбору наиболее подходящего подхода для ваших рабочих задач.
Подсчёт частоты вхождения слова/значения в одной ячейке или диапазоне с помощью формул
Простой подсчёт частоты вхождения слова/значения в одной ячейке или диапазоне с помощью удобного инструмента
Подсчёт вхождений с использованием макроса VBA (пользовательский метод)
Подсчёт вхождений с помощью формул СЧЁТЕСЛИ или СУММПРОИЗВ (без учёта регистра/частичные совпадения)
Подсчёт вхождений с помощью Сводная таблица Excel (сводка по диапазонам)
Подсчёт частоты вхождения слова/значения в одной ячейке или диапазоне с помощью формул
Вы можете использовать следующую формулу для подсчёта частоты вхождения слова или конкретного значения — как в одной ячейке, так и в заданном диапазоне. Этот подход идеально подходит, когда нужен быстрый и прямой результат без привлечения дополнительных инструментов или макросов.
Для подсчёта частоты вхождения слова в одной ячейке, выберите пустую ячейку для отображения результата, введите приведённую ниже формулу и нажмите Enter:
=SUMPRODUCT((LEN(A2)-LEN(SUBSTITUTE(A2,D1,«»)))/LEN(D1))
Чтобы подсчитать частоту вхождения слова в диапазоне ячеек, измените формулу так, чтобы она охватывала весь диапазон. Введите её в пустую ячейку следующим образом:
=SUMPRODUCT((LEN(A2:A15)-LEN(SUBSTITUTE(A2:A15,D1,«»)))/LEN(D1))
Примечания:
Подсчёт частоты вхождения слова/значения в одной ячейке или диапазоне с помощью Kutools для Excel
Функция Количество слов в тексте в Kutools для Excel позволяет мгновенно определить, сколько раз любое слово или значение встречается в ячейке или заданном диапазоне. Этот метод значительно упрощает подсчёт для пользователей, предпочитающих не работать с формулами или кодом VBA, повышая эффективность и точность при выполнении повторяющихся задач и обработке больших объёмов данных.
1. Выберите пустую ячейку для вывода результата, затем перейдите в меню Kutools > Помощник формул > Помощник формул.
2. В диалоговом окне Помощник формул выполните следующие шаги настройки:
Советы: Чтобы быстро найти эту формулу, если вы знаете её название, воспользуйтесь полем Фильтр.
Вы сразу увидите общее количество вхождений искомого слова в выбранной ячейке или диапазоне.
Ограничение: Требуется установка Kutools для Excel.
Если вы хотите воспользоваться бесплатной пробной версией (30 дней) этой утилиты, нажмите, чтобы скачать её, а затем выполните операцию в соответствии с приведёнными выше шагами.
Подсчёт вхождений с использованием макроса VBA (пользовательский метод)
Для пользователей, которым нужны более гибкие или расширенные возможности подсчёта — например, подсчёт только целых слов, переключение между режимами с учётом и без учёта регистра или автоматизация процесса для больших наборов данных — решение на основе макроса VBA станет практичным выбором. Такой подход позволяет настраивать режим сопоставления, что экономит время и повышает согласованность при выполнении повторяющихся операций.
Применимый сценарий: когда нужен настраиваемый подсчёт (например, игнорирование регистра или учёт только целых слов) либо автоматизация подсчёта в рамках рабочего процесса.
Меры предосторожности: Всегда создавайте резервные копии файлов перед запуском макросов и включайте макросы только из надёжных источников.
1. Откройте редактор VBA, выбрав Средства разработчика > Visual Basic. В окне VBA выберите Вставка > Модуль и вставьте следующий код в модуль:
Sub CountOccurrencesOfWord()
Dim rng As Range
Dim wordToCount As String
Dim caseSensitive As Integer
Dim wholeWordOnly As Integer
Dim totalCount As Long
Dim cell As Range
Dim cellText As String
Dim i As Integer
On Error Resume Next
Set rng = Application.Selection
Set rng = Application.InputBox("Select the range to count:", "KutoolsforExcel", rng.Address, Type:=8)
wordToCount = Application.InputBox("Enter the word or value to count:", "KutoolsforExcel", "", Type:=2)
caseSensitive = MsgBox("Enable case sensitive count? (OK = Yes, Cancel = No)", vbOKCancel, "KutoolsforExcel")
wholeWordOnly = MsgBox("Count whole words only? (OK = Yes, Cancel = No)", vbOKCancel, "KutoolsforExcel")
totalCount = 0
For Each cell In rng
cellText = cell.Value
If Not IsError(cellText) And Len(cellText) > 0 Then
If wholeWordOnly = vbOK Then
Dim regEx As Object
Set regEx = CreateObject("VBScript.RegExp")
regEx.Pattern = "\b" & wordToCount & "\b"
regEx.Global = True
regEx.IgnoreCase = (caseSensitive <> vbOK)
If regEx.Test(cellText) Then
totalCount = totalCount + regEx.Execute(cellText).Count
End If
Else
If caseSensitive = vbOK Then
i = (Len(cellText) - Len(Replace(cellText, wordToCount, ""))) / Len(wordToCount)
Else
i = (Len(LCase(cellText)) - Len(Replace(LCase(cellText), LCase(wordToCount), ""))) / Len(wordToCount)
End If
totalCount = totalCount + i
End If
End If
Next cell
MsgBox "Total occurrences: " & totalCount, vbInformation, "KutoolsforExcel"
End Sub2. После ввода кода нажмите кнопку 
«Выполнить». Появится диалоговое окно, в котором вам предложат выбрать диапазон для подсчёта, указать искомое слово или значение, а также определить, нужно ли учитывать регистр и/или считать только целые слова.
Советы:
- Если включена опция «Учитывать только целые слова», частичные совпадения (например, «he» внутри «the») игнорируются.
- Включите режим без учёта регистра, чтобы проводить более общие обзоры или анализировать данные со смешанным регистром.
- Макрос игнорирует пустые ячейки и ячейки, содержащие ошибки, при вычислении.
Устранение неполадок: Если при запуске возникают ошибки, убедитесь, что ваши данные не содержат ошибок в формулах или пустых значений. Если ваша версия Excel блокирует макросы, включите их в настройках Центра управления безопасностью.
Рекомендация по использованию: Этот метод на основе VBA идеально подходит, когда стандартные формулы не справляются с вашими задачами, и легко масштабируется для реализации более сложных правил сопоставления или пакетной обработки.
Подсчёт вхождений с помощью формул СЧЁТЕСЛИ или СУММПРОИЗВ (без учёта регистра/частичные совпадения)
Excel предлагает несколько гибких формул для подсчёта как точных, так и частичных совпадений. Эти методы отлично подходят, когда нужно быстрое и легко настраиваемое автоматизированное решение. Они идеальны для работы со списками значений, данными опросов или фильтрации конкретных вхождений в пределах всей таблицы или отдельного столбца.
Формула СЧЁТЕСЛИ: подсчёт точных совпадений без учёта регистра
В ячейке If you want to count the number of cells in a range that match a word or value (regardless of case) введите следующую формулу в пустую ячейку (например, E1):
=COUNTIF(A2:A15, D1)Эта формула подсчитывает количество ячеек в диапазоне A2:A15, содержащих значение, полностью совпадающее со значением в ячейке D1 (регистр не учитывается: «Apple» и «apple» считаются одинаковыми). После ввода формулы нажмите Enter, а затем при необходимости перетащите маркер заполнения, чтобы скопировать её в другие строки.
COUNTIF with partial match (using wildcards)
Чтобы подсчитать количество ячеек, содержащих определённое слово в любом месте ячейки (а не только точные совпадения), измените формулу СЧЁТЕСЛИ следующим образом и введите её в пустую ячейку:
=COUNTIF(A2:A15, "*" &, D1 &, "*")Эта версия подсчитывает ячейки, в которых содержимое ячейки D1 встречается в любом месте, по-прежнему без учёта регистра. Идеально подходит для обработки ответов на опросы, товарных кодов или фрагментов текста.
- A2:A15: Диапазон данных, в котором выполняется поиск.
- D1: Искомое слово или ячейка, содержащая ссылку.
Напоминание: Если требуются чувствительные к регистру частичные совпадения, рассмотрите возможность использования VBA, как указано выше.
Совет: Все эти формулы автоматически адаптируются к новым данным при расширении диапазонов.
Подсчёт вхождений с помощью Сводная таблица Excel (сводка по диапазонам)
Встроенная функция Excel «Сводная таблица» особенно эффективна для суммирования и подсчёта количества появлений определённых значений или слов в списке или таблице — особенно при работе со структурированными данными, такими как списки, записи транзакций или ответы на опросы. Сводные таблицы обеспечивают динамическую группировку и подсчёт по нескольким критериям, что делает их практичным выбором для анализа данных без написания формул или кода.
Пояснение значений: Идеально подходит для обобщения больших таблиц, определения частоты ответов в опросах, подсчёта повторяющихся имён и анализа количества товаров на складе.
Преимущества: Не требует формул, отличается наглядностью и интерактивностью, легко обновляется при добавлении новых данных.
Недостатки: Менее подходит для подсчёта внутри отдельных ячеек или неструктурированного текста; лучше всего справляется с подсчётом значений по столбцам.
Шаги по использованию Сводная таблица для подсчёта количества появлений значений:
- Выберите свой диапазон данных (например, A1:A100 или всю таблицу).
- Перейдите на вкладку Вставка и нажмите Сводная таблица.
- Выберите место размещения сводной таблицы — новый лист или существующий лист — и нажмите «ОК».
- Перетащите соответствующий столбец (тот, в котором находятся текст или значения для подсчёта) одновременно в область Строки и в область Значения в списке полей. Сводная таблица автоматически подсчитает, сколько раз встречается каждое уникальное значение.
Таблицу результатов можно обновлять в любое время при изменении исходных данных — это гарантирует точный расчёт с минимальными усилиями.
Советы:
- Вы можете фильтровать значения прямо в сводной таблице, чтобы получать более точные сводки.
- Чтобы детализировать данные по нескольким полям (например, подсчитать количество вхождений по словам и категориям), просто добавьте нужные поля в область «Строки» или «Столбцы».
Устранение неполадок: Если ваша сводная таблица не обновляется после изменения исходных данных, щёлкните правой кнопкой мыши в любом её месте и выберите «Обновить». Если список полей не отображается, перейдите на вкладку «Анализ сводной таблицы» и нажмите «Список полей».
Связанные статьи
Подсчёт количества вхождений дублирующихся значений в столбце Excel
В этой статье описаны 4 метода подсчёта количества вхождений дублирующихся значений в столбце Excel.
Подсчёт ячеек, содержащих текст или его часть, в Excel
Допустим, у вас есть данные, подобные приведённым ниже, и вы хотите отдельно посчитать, сколько ячеек содержат текст «Apple», сколько — «Orange» и сколько — «Peach». Как это сделать? В этом руководстве подробно разбирается функция СЧЁТЕСЛИ, которая позволяет быстро подсчитать количество ячеек с определённым текстом или его частью в Excel. А ещё здесь представлена удобная функция, с помощью которой вы решите эту задачу всего за несколько кликов!
Функция СЧЁТЕСЛИ с несколькими критериями в Excel
В Excel функция СЧЁТЕСЛИ помогает подсчитать, сколько раз определённое значение встречается в списке. Но что делать, если нужно учитывать сразу несколько критериев? В этой статье мы рассмотрим различные способы подсчёта с использованием нескольких условий.
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек