Чаще всего наши требования к восстановлению данных не ограничиваются одним условием. Например, нам нужна выручка магазина за определенный месяц, количество проданного конкретного товара конкретному покупателю и т.д. При использовании обычных средств функции ВПР эта задача сложна и даже не всегда возможна. В конце концов, предполагается, что будет использоваться только один критерий поиска.
Мы предложим вам несколько вариантов решения проблемы поиска разных условий.
ВПР по нескольким условиям с использованием дополнительного столбца.
Проблема, рассмотренная в предыдущем примере, может быть решена другим способом, без использования формулы массива. Ведь многим работа с массивами кажется сложной и недоступной. Дополнительный столбец для поиска нескольких условий в некоторых отношениях был бы более простым вариантом.
Затем вам нужно выбрать значение выручки за месяц, год и для желаемого магазина. Следовательно, у нас есть 3 условия выбора.
Функция ВПР не может одновременно просматривать три столбца. Следовательно, нам нужно объединить их в одно целое. И поскольку поиск всегда выполняется в крайнем левом (первом) столбце, нам также необходимо добавить его в нашу таблицу слева.
Вставляем дополнительный столбец A перед таблицей данных. Затем с помощью оператора & мы объединяем содержимое B, C и D. Мы пишем в A7
= B7, C7 и D7
и скопируйте в ячейки ниже.
Формула поиска в D4 будет выглядеть так:
= ВПР (RE1 & RE2 & RE3; LA7: MI20; 5; 0)
Мы также включаем наш дополнительный столбец в диапазон поиска. Критерий поиска — это также объединение 3-х значений. И извлечем результат из пятого столбца.
Все работает, но вид несколько портит дополнительная колонка. В крайнем случае, его можно скрыть с помощью контекстного меню, нажав правую кнопку мыши.
Вид будет более приятным, но это не повлияет на результат.
ВПР по двум условиям при помощи формулы массива.
У нас есть таблица, в которой записывается дневная выручка для каждого магазина. Мы хотим быстро найти сумму продаж в конкретном магазине за конкретный день.
Для этого в верхней части нашего списка обратите внимание на критерии поиска: дата и магазин. В ячейке B3 мы покажем сумму дохода.
Формула в B3 выглядит следующим образом:
{= ВПР (B1, SE (B6: B19 = B2, A6: C19, «»), 3,0)}
Обратите внимание на фигурные скобки, обозначающие, что это формула массива. То есть наша функция ВПР работает не с одиночными значениями, а единожды с массивами данных.
Разберем процесс подробно.
Ищем дату, записанную в ячейку B1. Но мы будем искать его только не в нашем исходном диапазоне данных, а в немного измененном. Для этого воспользуемся условием
SE (A6: B19 = B2; A6: C19;»»)
То есть, если имя магазина соответствует критерию в ячейке B2, мы сохраняем исходные значения из нашего диапазона. А если нет — заменяем их пробелами. И так по каждой строчке.
В результате мы получаем следующий виртуальный массив данных на основе нашей исходной таблицы:
Как видите, строки, в которых раньше было «Магазин 1», были заменены пустыми. А теперь будем искать нужную дату только в информации «Магазин 2». И извлеките значения дохода из третьего столбца.
Функция ВПР может справиться с этим типом работы.
Этот ход стал возможным благодаря применению формулы массива. Поэтому обратите особое внимание: вовсе не обязательно писать скобки в формуле от руки! В ячейке B3 напишите формулу
= ВПР (B1; SE (B6: B19 = B2; A6: C19; «»); 3,0)
А затем нажмите комбинацию клавиш CTRL + SHIFT + ENTER. В то же время Excel поймет, что вы хотите вставить формулу массива и заменить сами скобки.
Таким образом, функция ВПР выполняет поиск по двум столбцам в два этапа. Во-первых, мы удаляем диапазон данных из строк, которые не соответствуют одному из условий, используя функцию ЕСЛИ и формулу массива. И затем, в этой правильной информации, мы выполняем обычный поиск, используя только второй критерий, используя ВПР.
Чтобы упростить вашу работу в будущем и застраховаться от возможных ошибок при добавлении новой информации о продажах, мы рекомендуем вам использовать умную таблицу. Он автоматически изменит размер, чтобы соответствовать добавленным строкам, поэтому вам не нужно изменять ссылки в ваших формулах.
Вот как это будет выглядеть.
ВПР по нескольким критериям с применением массивов — способ 2.
Выше мы уже рассмотрели, как с помощью формулы массива можно организовать поиск ВПР с несколькими условиями. Предлагаем другой способ.
Возьмем те же условия, что и в предыдущем примере.
Формула в C4 следующая:
= ВПР (C1 & C2 & C3; ВЫБОР ({1; 2}; A7: A20 & B7: B20 & C7: C20; D7: D20); 2; 0)
Конечно, не забудьте нажать CTRL + Shift + Enter.
Теперь посмотрим, как это работает, по шагам.
Наша задача здесь также создать дополнительный столбец для работы функции ВПР. Только сейчас мы создаем его не на листе книги Excel, а виртуально.
Как и в предыдущем примере, мы ищем текст из объединенных поисковых запросов.
Затем мы определяем данные, среди которых будем искать.
ВЫБРАТЬ ({1; 2}; A7: A20 & B7: B20 & C7: C20; D7: D20)
Конструкция модуля A7: A20 и B7: B20 и C7: C20; D7: D20 создает 2 элемента. Первый — это объединение столбцов A, B и C из исходных данных. Если вы помните, мы сделали то же самое в нашей дополнительной колонке. Вторые D7: D20 — это значения, одно из которых необходимо выбрать в конце.
Функция SELECT позволяет создать массив из этих элементов. {1,2} просто означает, что вы должны сначала взять первый элемент, затем второй и объединить их в виртуальную таблицу — массив.
В первом столбце этой виртуальной таблицы мы будем искать, а во втором — получить результат.
Поэтому, чтобы функция ВПР работала с несколькими условиями, мы снова используем дополнительный столбец. Только создаем не совсем, а виртуально.
Двойной ВПР при помощи ИНДЕКС + ПОИСКПОЗ
Также мы не будем говорить о функции ВПР, но решим ту же проблему. Опять же, нам нужно использовать несколько условий в качестве критерия поиска.
Есть, пожалуй, даже более гибкие решения, чем функция ВПР. Это комбинация функций ИНДЕКС + ПОИСК.
Сфера их применения очень широка, о чем мы тоже поговорим на сайте mister-office.ru.
А пока вернемся к нашей задаче.
Формула в C4 теперь выглядит так:
= ИНДЕКС (D7: D20; ПОИСК (1; (A7: A20 = C1) * (B7: B20 = C2) * (C7: C20 = C3); 0))
И не забывайте нажимать CTRL + Shift + Enter при вводе! Это формула массива.
Теперь давайте узнаем, как это работает.
Функция ИНДЕКС в нашем случае позволяет извлечь элемент из списка по его порядковому номеру. Список представляет собой диапазон D7: D20, в котором разносятся суммы выручки. Но серийный номер, который нужно извлечь, мы определяем с помощью ПОИСКА.
Синтаксис следующий:
ПОИСК (какой_поиск; где_поиск; тип_поиск)
Установите для типа поиска значение 0, что является точным соответствием. В нашем случае мы будем искать 1. Далее мы определим массив, с которым будем работать.
Выражение (A7: A20 = C1) * (B7: B20 = C2) * (C7: C20 = C3) создаст виртуальную таблицу, подобную следующей:
Как видите, изначально мы постоянно сравниваем каждое значение с нашими критериями выбора. В столбце A записаны месяцы — мы сравниваем их с критерием месяца из ячейки C1. В случае совпадения получаем ИСТИНА, в противном случае — ЛОЖЬ. Так же мы постоянно проверяем год и название магазина. А затем умножаем значения. Поскольку логические переменные для Excel равны 0 или 1, их произведение может быть равно 1 только в том случае, если у нас есть ИСТИНА для каждого столбца (т.е. 1). Во всех остальных случаях получаем 0.
Убедитесь, что цифра 1 появляется только один раз.
С помощью ПОИСКПОЗ мы определяем, в какой позиции он находится. В какой позиции находится 1, в той же позиции в матрице находится требуемая сумма выручки. В нашем случае это десятый.
Затем с помощью INDEX извлекаем десятый доход.
Поэтому мы выбрали значение для нескольких условий без использования функции ВПР.
Достойная замена – функция СУММПРОИЗВ.
У нас есть данные о продажах разных менеджеров в разных регионах. Сделать выбор нужно по дате, менеджеру и региону.
Объясняем расчеты.
Выражение
= СУММПРОИЗВ ((A2: A27 = $ G $ 2) * (B2: B27 = $ G $ 3) * ($ C $ 2: $ C $ 27 = G4) * (D2: D27))
Он работает как формула массива, хотя на самом деле это не так. Это замечательное свойство функции СУММПРОИЗВ, о котором мы много поговорим в других статьях.
Последовательно для каждой строки в диапазоне от 2 до 27 проверяют совпадение каждого соответствующего значения с критериями поиска. Эти результаты умножаются друг на друга и, в конечном итоге, также умножаются на сумму дохода. Если есть хотя бы одно несоответствие между тремя условиями, общая сумма будет равна 0. Если есть совпадение, сумма дохода будет трижды умножена на 1.
Затем все эти 27 рабочих мест складываются и в результате получается желаемый доход менеджера в определенном регионе на определенную дату.
В качестве бонуса вы можете продолжить с этого примера и рассчитать общий объем продаж менеджера в конкретном регионе.
Для этого просто удалите сравнение дат из формулы.
= СУММПРОИЗВ ((A2: A27 = $ G $ 2) * (B2: B27 = $ G $ 3) * (D2: D27))
Кстати, возможен и другой вариант расчета с той же функцией:
= ПОДПРОДУКТ (- (A2: A27 = $ G $ 2), — (B2: B27 = $ G $ 3), (D2: D27))
Итак, мы рассмотрели примеры использования функции ВПР с двумя и с разными условиями. Мы также обнаружили, что есть отличная альтернатива этой ценной функции.