Получение первого значения из списка, содержащегося в ячейке

Чтобы найти первое ключевое слово из диапазона, содержащееся в определённой ячейке (когда ячейка может включать одно из нескольких возможных значений), нужно использовать довольно сложную формулу массива с функциями ИНДЕКС, ПОИСКПОЗ, ЕЧИСЛО и ПОИСК.

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

Чтобы получить первое подходящее ключевое слово из столбца «Ключевые слова», содержащееся в ячейках, как показано выше, нужно выполнить частичное сопоставление, а не точное. Для этого объедините функции ЕЧИСЛО и ПОИСК — они создадут массив значений ИСТИНА и ЛОЖЬ. Затем с помощью функции ПОИСКПОЗ найдите позицию первого значения ИСТИНА и передайте этот номер функции ИНДЕКС, чтобы получить соответствующее значение.

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

=INDEX(keyword_rng,MATCH(TRUE,ISNUMBER(SEARCH(keyword_rng,lookup_cell)),0))

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

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

=ИНДЕКС()($E$5:$E$7);ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(ПОИСК()))($E$5:$E$7);B5));0))

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

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

=INDEX(($E$5:$E$7,MATCH(TRUE,ISNUMBER(SEARCH($E$5:$E$7,B5)),0))

• SEARCH($E$5:$E$7,B5):Функция ПОИСК возвращает позиции ключевых слов из диапазона $E$5:$E$7, если они найдены в тексте ячейки B5, и ошибку #ЗНАЧ!, если совпадений нет. Результат выводится в виде массива:{15,11,#ЗНАЧ!}.
• ЕЧИСЛО()SEARCH($E$5:$E$7,B5))=ЕЧИСЛО(){15;11;#ЗНАЧ!}):Функция ЕЧИСЛО преобразует числа в массиве в значения ИСТИНА, а ошибки #ЗНАЧ! — в ЛОЖЬ, как показано ниже:{ИСТИНА;ИСТИНА;ЛОЖЬ}.
• ПОИСКПОЗ(ИСТИНА;)ЕЧИСЛО()SEARCH($E$5:$E$7,B5));0)=ПОИСКПОЗ(ИСТИНА;){ИСТИНА;ИСТИНА;ЛОЖЬ};0):Аргумент тип_сопоставления 0 заставляет функцию ПОИСКПОЗ выполнять точное сопоставление и возвращать позицию первого значения ИСТИНА в массиве {ИСТИНА;ИСТИНА;ЛОЖЬ}. Таким образом, функция вернёт значение 1.
• ИНДЕКС(($E$5:$E$7:ПОИСКПОЗ(ИСТИНА;)ЕЧИСЛО()SEARCH($E$5:$E$7,B5));0)) = ИНДЕКС(($E$5:$E$7:1):Затем функция ИНДЕКС возвращает 1-е значение из диапазона $E$5:$E$7, то есть AAA.

Примечание

• Если ячейка содержит два или более ключевых слов, формула вернёт первое из них согласно порядку в списке ключевых слов.
• Формула не учитывает регистр. Чтобы выполнить сопоставление с учётом регистра, просто замените функцию ПОИСК на функцию НАЙТИ.
• Вы также можете заменить список ключевых слов жёстко заданной строкой в виде константы массива (например, {«AAA»}), если требуется проверять только наличие этой строки в ячейках.

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

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