ВПР по 2 и более условиям в Excel: урок

Всем привет! Сегодня мы будем рассматривать одну из самых сложных тем в Excel, а именно использование ВПР по двум или более условиям в Excel. Не переживайте, если вам что-то будет непонятно с первого раза. Я в свое время раз 5 перечитывал разные статьи и курсы на данную тему. Скорее всего, вам придется возвращаться к этому урока и перечитывать его не один раз. Я предупреждаю вас, потому что сам с этим сталкивался. Не стоит винить себя, если сначала вообще ничего не будет понятно – это нормально, с этим сталкиваются все. Но мы попробуем преодолеть эту трудность вместе.

С несколькими условиями

Давайте посмотрим, как использовать формулу ВПР по 2 условиям в Excel. Сразу хочу сказать, что по умолчанию она так не может. Но есть небольшой лайфхак, который может нам помочь. Для начала давайте посмотрим, какой пример я для вас приготовил (скриншот ниже).

Если вы еще не знакомы с ВПР, то советую ознакомиться с этим уроком (переходим по ссылке). Дальше мы не будем заострять внимание именно на принципе работы ВПР, вы уже должны это знать.

Наша задача вывести прибыль по условию месяца, года и подразделения. Для работы нам понадобится пустой столбец слева, где мы будем объединять свойства, по которым будет идти фильтрация. Выделяем А7 и вводим формулу объединения значений по месяцу, году и подразделению:

=B7&C7&D7

С помощью маркера автозаполнения заполняем остальные ячейки ниже.

Читаем – что такое маркер автозаполнения.

Вы уже можете догадаться, что мы будем делать. Мы будем использовать эти объединенные данные в ВПР, так как оно может без проблем работать с одним условием. Теперь устанавливаем курсор в D4 и используем формулу ВПР:

=ВПР(D1&D2&D3;A7:E20;5;0)

Обратите внимание, что мы используем условия объединения ячеек выше (D1, D2 и D3). Далее вводим диапазон нашей таблицы, в которой мы ищем.

Напомню, что первый столбец мы можем просто скрыть, так как он является вспомогательным в нашей табличке.

По двум условиям

Давайте теперь рассмотрим пример с ВПР по двум условиям в Excel. Представим себе, что нам нужно вывести прибыль по подразделению за определенную дату. Для этого мы будем использовать формулу:

=ВПР(B1;ЕСЛИ(B6:B19=B2;A6:C19;””);3;0)

Мы будем искать по массиву, поэтому после ввода формулы, нам нужно одновременно зажать клавиши:

Ctrl + Shift + Enter

Программа сразу же подставит фигурные скобки так, чтобы поиск шел по массиву. Давайте коротко рассмотрим саму формулу:

• Используем поиск ВПР по дате (B1).
• Далее с помощью функции ЕСЛИ() мы используем остальной диапазон таблицы и полную таблицу. Мы создаем как бы виртуальную таблицу, где хранятся только строки с «Вторым» подразделениями. И по ним идет поиск уже по дате. Если вам здесь непонятно, то советую сначала ознакомиться с уроком по функции ЕСЛИ.
• В конце все оборачивается в фигурные скобки массива.

Применение массива и нескольких условий в ВПР

Еще один пример с ВПР и несколькими условиями в Excel. Но в отличие от первой главы, здесь мы будем использовать массив. Сразу рассмотрим формулу:

=ВПР(C1&C2&C3;ВЫБОР({1;2};A7:A20&B7:B20&C7:C20;D7:D20);2;0)

Не забываем, что мы тут используем массив, а значит жмем:

Ctrl + Shift + Enter

Если в первом примере мы создавали дополнительный столбец отдельно в А, то здесь мы будем создавать его виртуально с помощью формулы. Рассмотрим её подробно:

ВЫБОР({1;2};A7:A20&B7:B20&C7:C20;D7:D20)

Вот, как это выглядит в виртуальном пространстве (смотрим на картинку ниже).

Первый столбец объединяет столбцы по году, месяцу и подразделению. А второй столбец мы в итоге и используем для выбора прибыли. ВЫБОР из двух этих элементов {1,2} создает массив и объединяет в виртуальную таблицу. Плюс данного метода в том, что теперь нам не нужно создавать отдельный столбец и скрывать его.

ИНДЕКС и ПОИСКПОЗ

Здесь мы не будем использовать функцию ВПР, но сама задача будет такая же. Для этих целей мы прибегнем к функциям ИНДЕКС и ПОИСКПОЗ, которые позволяют более гибко настроить выборку. Рассмотрим формулу, которую мы применяем:

=ИНДЕКС(D7:D20;ПОИСКПОЗ(1;(A7:A20=C1)*(B7:B20=C2)*(C7:C20=C3);0))

Не забываем применить массив к записи формулы. ИНДЕКС извлекает элемент по порядковому номеру из диапазона D7:D20. Там записана вся прибыль по месяцам и подразделениям. Чтобы найти порядковый номер мы уже используем формулу поиск позиции (ПОИСКПОЗ).

ПОИСКПОЗ(какой элемент ищем; где именно производим поиск; тип поиска)

(A7:A20=C1)*(B7:B20=C2)*(C7:C20=C3)

В итоге в конце идут перемножения с булевыми значения ЛОЖЬ и ИСТИНА, где мы находим только одну позицию, где и будет расположено необходимая прибыль по условиям. Если говорить грубо, мы создаем виртуальную таблицу, где сначала ищем совпадения по месяцу, потом во втором столбце по году и в конце по подразделению. Обратите внимание, что позиция, где находится единичка – находится там же, где и искомая величина прибыли в основной таблице.

Я понимаю, что тема достаточно сложная. Я советую создать аналогичную табличку у себя и производить расчеты там же. Если что-то было непонятно, попробуйте внимательно перечитать статью еще раз. Я в свое время перечитывал подобные темы по 5-7 раз. До новых встреч на портале WiFiGiD.RU.

Видео