Перейти к содержимому

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

Author: Amanda Li Last Modified: 2025-06-05

Предположим, у вас есть список ключевых слов, и вам нужно получить первое ключевое слово, которое появилось в определенной ячейке, при этом ячейка содержит несколько других значений. Для этого вам придется использовать формулу INDEX и MATCH с помощью функций AGGREGATE и SEARCH.

retrieve first matching value in cell against a list 1

Как получить первое совпадающее значение в ячейке по списку?

Чтобы получить первое совпадающее ключевое слово в ячейке по списку Ключевых слов, как показано в таблице выше, это означает, что нам нужно выполнить частичное совпадение, а не точное. Для этого вы можете использовать функцию SEARCH, чтобы передать позиции ключевых слов, найденных в ячейке, в числовом формате, в функцию AGGREGATE. Затем AGGREGATE получит наименьшее число, установив function_num равным 15, а аргумент ref2 равным 1. После этого используйте MATCH для определения первого наименьшего значения и передайте номер позиции в INDEX для получения значения в этой позиции.

Общий синтаксис

=INDEX(keyword_rng,MATCH(AGGREGATE(15,6,SEARCH(keyword_rng,lookup_cell),1),SEARCH(keyword_rng,lookup_cell),0))

√ Примечание: Это формула массива, которая требует ввода с помощью Ctrl + Shift + Enter.

  • keyword_rng: Диапазон ячеек с ключевыми словами.
  • lookup_cell: Ячейка, в которой проверяется наличие ключевых слов.

Чтобы получить первое совпадающее ключевое слово, появившееся в ячейке B5 по столбцу Ключевые слова, скопируйте или введите формулу ниже в ячейку C5 и нажмите Ctrl + Shift + Enter, чтобы получить результат:

=INDEX($E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5),0))

√ Примечание: Знаки доллара ($) выше указывают на абсолютные ссылки, что означает, что keyword_rng в формуле не изменится, если вы переместите или скопируете формулу в другие ячейки. Однако знаки доллара не добавлены к lookup_cell, поскольку вы хотите, чтобы она была динамической. После ввода формулы перетащите маркер заполнения вниз, чтобы применить формулу к нижележащим ячейкам.

retrieve first matching value in cell against a list 2

Объяснение формулы

=INDEX($E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),0))

  • SEARCH($E$5:$E$7,B5): Функция SEARCH возвращает позицию каждого ключевого слова из диапазона $E$5:$E$7, когда оно найдено в числовом формате, и ошибку #VALUE!, если не найдено. Результат будет в виде массива, например: {15;11;#VALUE!}.
  • AGGREGATE(15,6,AGGREGATE(15,6,SEARCH($E$5:$E$7,B5),1),1) = AGGREGATE(15,6,AGGREGATE(15,6,{15;11;#VALUE!},1),1): Функция AGGREGATE с function_num равным 15 и option равным 6 вернет наименьшее значение в массиве согласно аргументу ref2 равному 1, игнорируя ошибочные значения. Таким образом, фрагмент вернет 11.
  • MATCH(MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),1),,SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),0),0) = MATCH(MATCH(11,,{15;11;#VALUE!},0),0): Тип соответствия 0 заставляет функцию MATCH выполнять точное соответствие и возвращать позицию 11 в массиве {15;11;#VALUE!}. Таким образом, функция вернет 2.
  • INDEX($E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),0)) = INDEX($E$5:$E$7,2): Функция INDEX затем возвращает второе значение в диапазоне $E$5:$E$7, которое является bbb.

Примечание

  • Если в ячейке нет ключевых слов, будет возвращена ошибка #NUM!.
  • Формула не чувствительна к регистру. Чтобы выполнить поиск с учетом регистра, вы можете просто заменить функцию SEARCH на FIND.

Связанные функции

Функция Excel INDEX

Функция Excel INDEX возвращает отображаемое значение на основе заданной позиции из диапазона или массива.

Функция Excel MATCH

Функция Excel MATCH ищет определенное значение в диапазоне ячеек и возвращает относительную позицию этого значения.

Функция Excel SEARCH

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

Функция Excel AGGREGATE

Функция Excel AGGREGATE возвращает агрегированные вычисления, такие как SUM, COUNT, SMALL и т.д., с возможностью игнорировать ошибки и скрытые строки.


Связанные формулы

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

Чтобы получить первое ключевое слово, указанное в диапазоне из определенной ячейки, при этом ячейка содержит одно из нескольких значений, вам придется использовать довольно сложную формулу массива с функциями INDEX, MATCH, ISNUMBER и SEARCH.

Точное совпадение с INDEX и MATCH

Если вам нужно найти информацию, указанную в Excel, о конкретном продукте, фильме или человеке и т.д., вам следует хорошо использовать комбинацию функций INDEX и MATCH.

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

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

Проверить, содержит ли ячейка все из множества элементов

Предположим, в Excel есть список значений в столбце E, и вы хотите проверить, содержат ли ячейки в столбце B все значения из столбца E, и вернуть TRUE или FALSE, как показано на скриншоте ниже. В этом руководстве представлена формула для решения этой задачи.

Проверить, содержит ли ячейка один из множества элементов

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


Лучшие инструменты для повышения производительности Office

Kutools для Excel - Помогает вам выделиться из толпы

🤖 KUTOOLS AI Помощник: Революционизируйте анализ данных на основе: Интеллектуального выполнения   |  Генерация кода  |  Создание пользовательских формул  |  Анализ данных и создание диаграмм  |  Вызов расширенных функций Kutools
Популярные функции: Найти, Выделить или Отметить дубликаты  |  Удалить пустые строки  |  Объединить столбцы или ячейки без потери данных  |  Округлить без формулы ...
Супер VLookup: Множественные критерии  |  Множественные значения  |  По нескольким листам  |  Распознавание нечетких соответствий...
Расширенный выпадающий список: Простой выпадающий список  |  Зависимый выпадающий список  |  Выпадающий список с множественным выбором...
Управление столбцами: Добавить определенное количество столбцов  |  Переместить столбцы  |  Переключить видимость скрытых столбцов  Сравнить столбцы для выбора одинаковых и разных ячеек ...
Основные функции: Сетка фокусировки  |  Дизайн листа  |  Улучшенная строка формул  |  Управление книгами и листами | Библиотека автотекста (Auto Text)  |  Выбор даты  |  Объединить данные  |  Шифровать/Расшифровать ячейки  |  Отправить письма по списку  |  Супер фильтр  |  Специальный фильтр (фильтр жирного/курсивного/зачеркнутого текста...) ...
Топ-15 наборов инструментов12 текстовых инструментов (Добавить текст, Удалить определенные символы ...)  |  50+ типов диаграмм (Диаграмма Ганта ...)  |  40+ практических формул (Расчет возраста на основе даты рождения ...)  |  19 инструментов вставки (Вставить QR-код, Вставить изображение по пути ...)  |  12 инструментов преобразования (Преобразовать в слова, Конвертация валюты ...)  |  7 инструментов объединения и разделения (Расширенное объединение строк, Разделить ячейки Excel ...)  |  ... и многое другое

Kutools для Excel имеет более 300 функций, гарантируя, что нужный вам инструмент находится всего в одном клике...


Office Tab - Включите работу с вкладками в Microsoft Office (включая Excel)

  • Один щелчок мыши, чтобы переключаться между десятками открытых документов!
  • Сократите сотни кликов мышью ежедневно, попрощайтесь с болью в руке.
  • Увеличивает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.