Вспомним, как работает функция ВПР: она просматривает крайний левый столбец таблицы-источника и, как только находит первое совпадение с заданным условием, возвращает (подтягивает) значение из указанного столбца в той же строке. В стандартном варианте функция ВПР ищет совпадение по одному критерию. Но что делать, если требования к поиску не ограничиваются одним условием? В этой статье рассмотрим, как работает функция ВПР в excel с несколькими условиями.
В стандартном наборе функций Excel функции ВПР с несколькими условиями не существует. Однако, есть несколько способов решить задачу поиска ВПР по двум или более условиям.
-
- Способ 1. Функция ВПР в Excel с несколькими условиями при помощи вспомогательного столбца
- Способ 2. ВПР с несколькими условиями в Excel при помощи сочетания функций ИНДЕКС / ПОИСКПОЗ
- Способ 3. ВПР по двум условиям при помощи формулы массива
- Способ 4. ВПР с несколькими условиями при помощи функции СУММЕСЛИМН
Способ 1. Функция ВПР в Excel с несколькими условиями при помощи вспомогательного столбца
Это самый распространенный и самый простой способ в excel сделать ВПР двух или нескольких значений.
Рассмотрим на примере. Есть две таблицы — таблица-источник (зеленая “шапка”) и рабочая таблица (синяя “шапка”), в которую нужно подтянуть количество автомобилей из источника по трем условиям: марка, модель и цвет автомобиля.
В таблице-источнике создадим вспомогательный столбец, в котором объединим все имеющиеся значения в столбцах при помощи оператора конкатенации & или функцией СЦЕП. Вспомогательный столбец должен быть крайним слева (помним, что ВПР ищет совпадения в крайнем левом столбце).
Вспомним синтаксис функции ВПР:
=ВПР(искомое_значение; таблица; номер столбца; [интервальный просмотр])
В качестве искомого значения нам нужно объединить все критерии поиска в том же порядке, как во вспомогательном столбце таблицы-источника.
Если будете копировать формулу в другие ячейки, то ссылки на ячейки и диапазон таблицы в формуле необходимо закрепить знаками $.
Как видите, функция ВПР в excel с несколькими условиями (а данном случае три условия) подтянула значение из выделенной строки.
Сообщество Excel Analytics | обучение Excel
Способ 2. ВПР с несколькими условиями в Excel при помощи сочетания функций ИНДЕКС / ПОИСКПОЗ
В этом способе, на самом деле, совсем не используется функция ВПР. Однако, он решает ту же самую задачу — подтянуть значения из таблицы-источника по нескольким условиям.
Этот способ удобен тем, что не нужно создавать дополнительных столбцов в таблице-источнике (на практике не всегда возможно добавить столбец — источник может быть защищен от изменений).
В данном примере мы не будем разбирать, как работают функции ИНДЕКС и ПОИСКПОЗ по отдельности, а рассмотрим только, как решить нашу задачу — сделать ВПР с несколькими условиями в excel на примере.
Скопируем в нашем примере строку с условиями поиска и напишем следующую формулу:
После написания формулы необходимо нажать сочетание клавиш Ctrl + Shift + Enter. Это необходимо сделать, т.к. это формула массива — в противном случае выйдет ошибка #ЗНАЧ.
Давайте разберем формулу:
=ИНДЕКС(L4:L13;ПОИСКПОЗ(B5&C5&D5;I4:I13&J4:J13&K4:K13;0))
-
-
- L4:L13 — массив, из которого будет подтягиваться информация. Тот столбец, данные из которого нам нужны.
- B5&C5&D5 — критерии для поиска, которые мы объединили между собой оператором конкатенации &.
- I4:I13&J4:J13&K4:K13 — столбцы, в которых будут происходить поиск по заданным критериям.
-
I4:I13 — столбец с марками автомобилей (соответствует критерию в ячейке В5)
J4:J13 — столбец с моделями автомобилей (соответствует критерию в ячейке С5)
K4:K13 — столбец с цветом автомобилей (соответствует критерию в ячейке D5)
Обратите внимание, что столбцы, в которых будет происходить поиск, должны располагаться в формуле в таком же порядке, как и критерии поиска.
-
-
- 0 — аргумент, обозначающий, что нужен поиск точного совпадения.
-
Способ 3. ВПР по двум условиям при помощи формулы массива
Рассмотрим работу функции ВПР по двум условиям на то же примере, только исключим один из критериев поиска — будем искать количество автомобилей по Модели автомобиля и Цвету. Так будет проще понять эту формулу.
Запишем следующую формулу для поиска:
=ВПР(C6;ЕСЛИ(K4:K13=D6;J4:L13;0);3;0)
В конце обязательно нужно нажать сочетание клавиш Ctrl + Shift + Enter, т.к. это формула массива, иначе будет ошибка #Н/Д.
Разберем, как работает эта формула.
С6 — это первый критерий для поиска. Но поскольку у нас есть еще один критерий (D6), то искать C6 формула будет но во всем столбце J, а только в той строке, где будет совпадение с столбце К со значением второго критерия (D6).
Таким образом, при помощи конструкции внутри формулу ВПР
ЕСЛИ(K4:K13=D6;J4:L13;0)
Создается виртуальная таблица для поиска значения первого критерия.
А дальше формула ВПР по двум условиям работает как обычная ВПР — указывается номер столбца 3 и интервальный просмотр 0 (точный поиск).
Способ 4. ВПР с несколькими условиями при помощи функции СУММЕСЛИМН
Функция СУММЕСЛИМН является полноценной альтернативой функции ВПР, если нужно подтянуть числовой результат по нескольким условиям.
Отличие функции СУММЕСЛИМН от ВПР в следующем: ВПР ищет самое первое совпадение и возвращает данные по строке с этом первом совпадении. А СУММЕСЛИМН просуммирует все значения, соответствующие критериям поиска. Нужно учитывать эту особенность.
Синтаксис функции СУММЕСЛИМН:
=СУММЕСЛИМН(Диапазон_суммирования; Диапазон_условия1; Условие1;…; Диапазон_условияN; УсловиеN)
Подтянем данные в нашу рабочую таблицу из таблицы-источника по тем же критериям, но уже при помощи функции СУММЕСЛИМН.
Функция СУММЕСЛИМН просуммировала все значения в столбце L, у которых значение в столбце I равно значению В7, значения в столбце J — C7, а значения в столбце K — D7. Как видите, результат такой же, как и при других способах.
Но, если мы добавим еще одну такую же строчку, то результат вычисления функции изменится. Именно эту особенность и нужно учитывать, если вы используете СУММЕСЛИМН вместо ВПР по нескольким условиям.
В этой статье мы рассмотрели, как работает функция ВПР в excel с несколькими условиями различными способами.
