Note: The other languages of the website are Google-translated. Back to English
English English

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

Предположим, у вас есть список ключевых слов, чтобы получить первое ключевое слово, появившееся в определенной ячейке, в то время как ячейка содержит несколько других значений, вам нужно будет использовать ИНДЕКС и МАТЧ формула с помощью ОБЩИЙ и ПОИСК функции.

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

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

Чтобы получить первое совпавшее ключевое слово в ячейке из списка ключевых слов как показано в таблице выше, это означает, что нам нужно выполнить поисковое совпадение, а не точное совпадение. Для этого вы можете использовать функцию ПОИСК, чтобы передать позиции ключевых слов в ячейке числовыми числами в функцию АГРЕГАТ. Тогда 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 поскольку вы хотите, чтобы он был динамичным. После ввода формулы перетащите маркер заполнения вниз, чтобы применить формулу к ячейкам ниже.

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

Пояснение формулы

=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

В 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 года.
Лента Excel (с установленным Kutools for Excel)

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

  • Одна секунда для переключения между десятками открытых документов!
  • Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), точно так же, как Chrome, Firefox и новый Internet Explorer.
Снимок экрана Excel (с установленной вкладкой Office)
Сортировать комментарии по
Комментарии (0)
Оценок пока нет. Оцените первым!
Здесь еще нет комментариев
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места