Подсчёт количества ключевых слов, содержащихся в ячейке, на основе списка
Если вы хотите подсчитать, сколько ключевых слов из заданного списка содержится в ячейке, воспользуйтесь комбинацией функций СУММПРОИЗВ, ЕЧИСЛО и ПОИСК — она легко справится с этой задачей в Excel.
Подсчёт количества ключевых слов, содержащихся в ячейке, на основе списка
Допустим, у вас в столбце A находится список текстовых строк, а в столбце D — несколько ключевых слов, как показано на скриншоте ниже. Чтобы подсчитать, сколько ключевых слов из столбца D содержится в каждой ячейке столбца A, используйте следующую формулу.
1. Введите или скопируйте приведённую ниже формулу в пустую ячейку:
2. Затем перетащите маркер заполнения, чтобы скопировать формулу в другие ячейки — и количество ключевых слов в каждой из них будет автоматически рассчитано. См. скриншот:
Пояснение к формуле:
- SEARCH($D$2:$D$6, A2): используется для возврата позиции ключевых слов в текстовой строке, если они найдены; если не найдены, отображается ошибка #ЗНАЧ!;
- --ISNUMBER(SEARCH($D$2:$D$6, A2)): если ключевое слово найдено в текстовой строке, функция ПОИСК возвращает номер позиции, а функция ЕЧИСЛО — значение ИСТИНА; если ключевое слово не найдено, функция ПОИСК выдаёт ошибку #ЗНАЧ!, а функция ЕЧИСЛО — ЛОЖЬ; двойной унарный минус (--) преобразует значения ИСТИНА и ЛОЖЬ в 1 и 0 соответственно.
- СУММПРОИЗВ (SUMPRODUCT): используется для суммирования массива, состоящего из единиц и нулей.
Примечания:
1. Если одно и то же ключевое слово встречается в ячейке несколько раз, оно учитывается лишь один раз.
2. Если в списке ключевых слов есть пустые ячейки, приведённая выше формула не даст корректного результата. В этом случае используйте следующую формулу массива:
Не забудьте нажать одновременно клавиши Ctrl + Shift + Enter, чтобы получить корректный результат — см. скриншот:
Используемые связанные функции:
- SUMPRODUCT:
- Функция СУММПРОИЗВ (SUMPRODUCT) позволяет умножать два или более столбцов или массивов, а затем суммировать полученные произведения.
- SEARCH:
- Функция ПОИСК (SEARCH) возвращает позицию заданного символа или фрагмента текста внутри указанной текстовой строки.
- ISNUMBER:
- Функция ЕЧИСЛО (ISNUMBER) возвращает ИСТИНА (TRUE), если ячейка содержит число, и ЛОЖЬ (FALSE) — во всех остальных случаях.
Другие статьи:
- Подсчёт строк, разделённых символами перевода строки в ячейке или диапазоне
- В этой статье объясняется, как с помощью формулы в Excel подсчитать количество строк, разделённых символами перевода строки, в ячейке или диапазоне ячеек.
- Извлечение нескольких строк из ячейки
- Если у вас есть список текстовых строк, разделённых символами перевода строки (вставляемыми при нажатии Alt + Enter во время ввода), и вы хотите разбить их на отдельные ячейки, как показано на скриншоте ниже, как это можно сделать с помощью формулы Excel?
- Отображение содержимого ячейки, если она содержит текст, в Excel
- Если у вас в столбце A есть список текстовых строк и отдельная строка с ключевыми словами, необходимо проверить, содержатся ли эти ключевые слова в каждой из текстовых строк. Если ключевое слово найдено в ячейке — оно отображается; если нет — ячейка остаётся пустой, как показано на следующем скриншоте.
Лучшие инструменты для повышения продуктивности в Office
Kutools для Excel — Помогает вам выделиться из толпы
Kutools для Excel предлагает более 300 функций,гарантируя, что всё необходимое всегда под рукой…
Office Tab — Включает вкладки для чтения и редактирования в Microsoft Office (включая Excel)
- Переключайтесь между десятками открытых документов всего за секунду!
- Сократите сотни кликов мышью каждый день и забудьте о «мышечной руке».
- Повышает продуктивность на 50 % при одновременном просмотре и редактировании нескольких документов.
- Добавляет удобство работы с вкладками в Office (включая Excel) — как в Chrome, Edge и Firefox.