Получить первое совпадающее значение в ячейке по списку
Предположим, у вас есть список ключевых слов, и вам нужно получить первое ключевое слово, которое появилось в определенной ячейке, при этом ячейка содержит несколько других значений. Для этого вам придется использовать формулу INDEX и MATCH с помощью функций AGGREGATE и SEARCH.
Как получить первое совпадающее значение в ячейке по списку?
Чтобы получить первое совпадающее ключевое слово в ячейке по списку Ключевых слов, как показано в таблице выше, это означает, что нам нужно выполнить частичное совпадение, а не точное. Для этого вы можете использовать функцию 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, поскольку вы хотите, чтобы она была динамической. После ввода формулы перетащите маркер заполнения вниз, чтобы применить формулу к нижележащим ячейкам.
Объяснение формулы
=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 MATCH ищет определенное значение в диапазоне ячеек и возвращает относительную позицию этого значения.
В Excel функция SEARCH может помочь вам найти позицию определенного символа или подстроки из заданной текстовой строки, как показано на следующем скриншоте. В этом руководстве я покажу, как использовать функцию SEARCH в Excel.
Функция 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 для Excel имеет более 300 функций, гарантируя, что нужный вам инструмент находится всего в одном клике...
Office Tab - Включите работу с вкладками в Microsoft Office (включая Excel)
- Один щелчок мыши, чтобы переключаться между десятками открытых документов!
- Сократите сотни кликов мышью ежедневно, попрощайтесь с болью в руке.
- Увеличивает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.