Те из вас, кто хорошо разбирается в Excel, скорее всего, хорошо знакомы с функцией ВПР . Функция ВПР используется для поиска значения в другой ячейке на основе соответствующего текста в той же строке.
Если вы еще не знакомы с функцией VLOOKUP , вы можете прочитать мой предыдущий пост на как использовать ВПР в Excel.
Несмотря на всю свою мощь, VLOOKUP имеет ограничение на то, как должна быть структурирована справочная таблица соответствия, чтобы формула работала.
В этой статье будет показано ограничение, при котором нельзя использовать ВПР , а также представлена другая функция Excel под названием ИНДЕКС-ПОИСКПОЗ , которая может решить эту проблему.
Пример ИНДЕКС-СООТВЕТСТВИЯ в Excel
Используя следующий пример таблицы Excel, у нас есть список имен владельцев автомобилей и название автомобиля. В этом примере мы попытаемся получить Идентификатор автомобиля на основе Модели автомобиля , указанной у нескольких владельцев, как показано ниже:
На отдельном листе под названием CarType у нас есть простая база данных автомобилей с ID , Моделью автомобиля и Цветом . сильный>.
При такой настройке таблицы функция ВПР может работать только в том случае, если данные, которые мы хотим получить, расположены в столбце справа от того, что мы пытаемся сопоставить (Модель автомобиля поле).
Другими словами, с помощью этой структуры таблицы, поскольку мы пытаемся сопоставить ее на основе Модели автомобиля , единственная информация, которую мы можем получить, это Цвет (Не ID , поскольку столбец ID расположен слева от столбца Модель автомобиля .)
Это связано с тем, что при использовании ВПР искомое значение должно появляться в первом столбце, а столбцы поиска должны располагаться справа. В нашем примере ни одно из этих условий не соблюдено.
Хорошая новость заключается в том, что INDEX-MATCH сможет помочь нам в этом. На практике это фактически объединение двух функций Excel, которые могут работать по отдельности: функция ИНДЕКС и функция ПОИСКПОЗ .
Однако в рамках этой статьи мы будем говорить только о комбинации этих двух методов с целью воспроизведения функции VLOOKUP ..
На первый взгляд формула может показаться немного длинной и пугающей. Однако, воспользовавшись им несколько раз, вы выучите синтаксис наизусть.
В нашем примере это полная формула:
=INDEX(CarType!$A$2:$A$5,MATCH(B4,CarType!$B$2:$B$5,0))
Вот разбивка по каждому разделу
=ИНДЕКС( – “=” указывает на начало формулы в ячейке, а ИНДЕКС – это первая часть функции Excel. который мы используем.
CarType!$A$2:$A$5 – столбцы на листе CarType , в которых содержатся данные, которые мы хотим получить. В этом примере это ID каждой модели автомобиля .
MATCH( – вторая часть используемой нами функции Excel.
B4 – ячейка, содержащая используемый нами текст для поиска (Модель автомобиля ).
CarType!$B$2:$B$5 – столбцы на листе CarType с данными, которые мы будем использовать для сопоставления с текстом поиска.
0)) – чтобы указать, что текст поиска должен точно совпадать с текстом в соответствующем столбце (т. е. CarType!$B$2:$B$5 ). . Если точное совпадение не найдено, формула возвращает #N/A .
Примечание : помните о двойной закрывающей скобке в конце этой функции “))” и запятых между аргументами.
Лично я отказался от ВПР и теперь использую ИНДЕКС-ПОИСКПОЗ, поскольку он способен на большее, чем ВПР.
Функции ИНДЕКС-ПОИСКПОЗ имеют и другие преимущества по сравнению с ВПР :
Когда мы работаем с большими наборами данных, где сам расчет может занять много времени из-за множества функций ВПР, вы обнаружите, что как только вы замените все эти формулы на ИНДЕКС-ПОИСКПОЗ, общий расчет будет выполняться быстрее.
Если наша справочная таблица содержит ключевой текст, который мы хотим найти, в столбце C , а данные, которые нам нужно получить, находятся в столбце AQ , нам понадобится чтобы узнать/подсчитать, сколько столбцов находится между столбцом C и столбцом AQ при использовании VLOOKUP.
С помощью функций ИНДЕКС-ПОИСКПОЗ мы можем напрямую выбрать индексный столбец (т. е. столбец AQ), из которого нам нужно получить данные, и выбрать столбец для сопоставления (т. е. столбец C)..
<ол старт="3">ВПР в настоящее время довольно распространен, но немногие знают о совместном использовании функций ИНДЕКС-ПОИСКПОЗ.
Более длинная строка в функции ИНДЕКС-ПОИСКПОЗ поможет вам выглядеть экспертом в работе со сложными и расширенными функциями Excel. Наслаждайтесь!
.