Всем привет! Сегодня мы будем рассматривать одну из самых сложных тем в 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.
