Получить первое совпадающее значение в ячейке по списку
Предположим, у вас есть список ключевых слов, чтобы получить первое ключевое слово, появившееся в определенной ячейке, в то время как ячейка содержит несколько других значений, вам нужно будет использовать ИНДЕКС и МАТЧ формула с помощью ОБЩИЙ и ПОИСК функции.
Как получить первое совпадающее значение в ячейке по списку?
Чтобы получить первое совпавшее ключевое слово в ячейке из списка ключевых слов как показано в таблице выше, это означает, что нам нужно выполнить поисковое совпадение, а не точное совпадение. Для этого вы можете использовать функцию ПОИСК, чтобы передать позиции ключевых слов в ячейке числовыми числами в функцию АГРЕГАТ. Тогда AGGREGATE получит наименьшее число, установив его function_num равным 15, а аргумент ref2 - 1. Затем используйте ПОИСКПОЗ, чтобы найти первое наименьшее значение, и подайте номер позиции в ИНДЕКС, чтобы получить значение в этой позиции.
Общий синтаксис
=INDEX(keyword_rng,MATCH(AGGREGATE(15,6,SEARCH(keyword_rng,lookup_cell),1),SEARCH(keyword_rng,lookup_cell),0))
√ Примечание. Это формула массива, требующая ввода с помощью Ctrl + Shift + Enter.
- ключевое слово_rng: Диапазон ячеек с ключевыми словами.
- lookup_cell: Ячейка для поиска, если она содержит ключевые слова.
Чтобы получить первое совпавшее ключевое слово появилось в ячейке B5 напротив столбца "Ключевые слова"., скопируйте или введите формулу ниже в ячейку C5 и нажмите Ctrl + Shift + Enter чтобы получить результат:
= ИНДЕКС (5 доллара E $: 7 E $, МАТЧ (АГРЕГАТ (15,6; ПОИСК (5 доллара E $: 7 E $,B5), 1), ПОИСК (5 доллара E $: 7 E $,B5), 0))
√ Примечание. Знаки доллара ($) выше указывают на абсолютные ссылки, что означает ключевое слово_rng в формуле не изменится при перемещении или копировании формулы в другие ячейки. Однако знаки доллара не добавлены к lookup_cell поскольку вы хотите, чтобы он был динамичным. После ввода формулы перетащите маркер заполнения вниз, чтобы применить формулу к ячейкам ниже.
Пояснение формулы
=INDEX($E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5),0))
- ПОИСК ($ E $ 5: $ E $ 7, B5): Функция ПОИСК возвращает позицию каждого ключевого слова из диапазона 5 доллара E $: 7 E $ при нахождении в числовых числах и #СТОИМОСТЬ! ошибка, если не обнаружена. Результатом будет такой массив: {15; 11; # ЗНАЧЕНИЕ!}.
- АГРЕГАТ (15,6,ПОИСК ($ E $ 5: $ E $ 7, B5), 1) = АГРЕГАТ (15,6,{15; 11; # ЗНАЧЕНИЕ!}, 1): Функция АГРЕГАТ с function_num из 15 и вариант из 6 вернет наименьшее значение в массиве в соответствии с ref2 аргумент 1, игнорируя значения ошибок. Итак, фрагмент вернет 11.
- СООТВЕТСТВИЕ(АГРЕГАТ (15,6,ПОИСК ($ E $ 5: $ E $ 7, B5), 1),ПОИСК ($ E $ 5: $ E $ 7, B5), 0) = СООТВЕТСТВИЕ(11,{15; 11; # ЗНАЧЕНИЕ!}, 0): Компания match_type 0 заставляет функцию MATCH выполнять точное совпадение и возвращать позицию 11 в массиве {15; 11; # ЗНАЧЕНИЕ!}. Итак, функция вернет 2.
- ИНДЕКС (5 E $: 7 E $,СООТВЕТСТВИЕ(АГРЕГАТ (15,6,ПОИСК ($ E $ 5: $ E $ 7, B5), 1),ПОИСК ($ E $ 5: $ E $ 7, B5), 0)) = ИНДЕКС ($ E $ 5: $ E $ 7,2): Затем функция ИНДЕКС возвращает 2ое значение в диапазоне 5 доллара E $: 7 E $, Которая является BBB.
замечание
- Если в ячейке нет ключевых слов, #NUM! ошибка будет возвращена.
- В формуле не учитывается регистр. Чтобы выполнить поиск с учетом регистра, вы можете просто заменить функцию ПОИСК на НАЙТИ.
Связанные функции
Функция ИНДЕКС Excel возвращает отображаемое значение на основе заданной позиции из диапазона или массива.
Функция ПОИСКПОЗ в Excel ищет определенное значение в диапазоне ячеек и возвращает относительное положение значения.
В Excel функция ПОИСК может помочь вам найти положение определенного символа или подстроки в заданной текстовой строке, как показано на следующем снимке экрана. В этом руководстве я расскажу, как использовать эту функцию ПОИСК в Excel.
Функция Excel AGGREGATE возвращает совокупность вычислений, таких как SUM, COUNT, SMALLL и т. Д., С возможностью игнорировать ошибки и скрытые строки.
Связанные формулы
Получить первое значение списка из ячейки
Чтобы получить первое ключевое слово, перечисленное в диапазоне из определенной ячейки, в то время как ячейка содержит одно из нескольких значений, вам придется использовать довольно сложную формулу массива с функциями ИНДЕКС, ПОИСКПОЗ, ЕЧИСЛО и ПОИСК.
Точное совпадение с ИНДЕКСОМ и ПОИСКПОЗ
Если вам нужно найти информацию, указанную в Excel, о конкретном продукте, фильме или человеке и т. Д., Вы должны хорошо использовать комбинацию функций ИНДЕКС и ПОИСКПОЗ.
Проверьте, содержит ли ячейка определенный текст
Здесь в руководстве представлены формулы, позволяющие проверить, содержит ли ячейка определенный текст и вернуть значение ИСТИНА и ЛОЖЬ, как показано на скриншоте ниже, а также объясняются аргументы и принцип работы формул.
Проверьте, содержит ли ячейка все много вещей
Предположим, что в Excel есть список значений в столбце E, вы хотите проверить, содержат ли ячейки в столбце B все значения в столбце E, и вернуть TRUE или FALSE, как показано ниже. Здесь, в этом руководстве, предлагается формула для решения этой задачи.
Проверьте, содержит ли ячейка одно из множества значений
В этом руководстве представлена формула, позволяющая проверить, содержит ли ячейка одно из нескольких значений в Excel, а также объясняются аргументы в формуле и принцип работы формулы.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.
- Рассчитан на 1500 сценариев работы, помогает решить 80% задач Excel.
- Уменьшите количество нажатий на клавиатуру и мышь каждый день, избавьтесь от усталости глаз и рук.
- Станьте экспертом по Excel за 3 минуты. Больше не нужно запоминать какие-либо болезненные формулы и коды VBA.
- 30-дневная неограниченная бесплатная пробная версия. 60-дневная гарантия возврата денег. Бесплатное обновление и поддержка 2 года.
Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), точно так же, как Chrome, Firefox и новый Internet Explorer.
