Очень часто наши требования к восстановлению данных не ограничиваются одним условием. Например, нам нужна выручка магазина за данный месяц, количество конкретного товара, проданного конкретному покупателю и т д. Обычными средствами функции ВПР эту задачу решить сложно и даже не всегда возможно. Ведь он предусматривает использование только одного критерия поиска.
Мы предложим вам несколько вариантов решения задачи поиска различных условий.
ВПР по нескольким условиям с использованием дополнительного столбца.
Задачу, рассмотренную в примере выше, можно решить другим способом, не используя формулу массива. Ведь работа с массивами многим кажется сложной и недоступной. Дополнительный столбец для множественного поиска был бы в некотором смысле проще.
Поэтому нужно выбрать значение выручки за определенный месяц, год и для нужного магазина. В итоге имеем 3 условия отбора.
Функция ВПР не может выполнять поиск в трех столбцах одновременно. Поэтому нам нужно объединить их в один. А поскольку поиск всегда выполняется в крайнем левом (первом) столбце, нам также нужно добавить его в нашу левую таблицу.
Вставляем дополнительный столбец A перед таблицей данных. Затем с помощью оператора & объединяем содержимое B, C и D. Записываем в A7
=B7 и C7 и D7
и скопируйте в ячейки ниже.
Формула поиска в D4 будет выглядеть так:
=ВПР(D1&D2&D3;A7:E20;5;0)
Мы также включаем наш дополнительный столбец в диапазон поиска. Поисковый термин также является объединением 3 значений. И извлекаем результат 5-го столбца.
Все работает, но вид несколько портит лишний столбец. В крайнем случае его можно скрыть с помощью контекстного меню правой кнопки мыши.
Вид станет более приятным, но на результаты это никак не повлияет.
ВПР по двум условиям при помощи формулы массива.
У нас есть таблица, в которой записан ежедневный доход для каждого магазина. Мы хотим быстро найти объем продаж для конкретного магазина в конкретный день.
Для этого вверху нашего листа пропишем критерии поиска: дата и магазин. В ячейке B3 мы будем отображать сумму дохода.
Формула в B3 выглядит так:
{=ВПР(B1,ЕСЛИ(B6:B19=B2,A6:C19,»»)),3,0)}
Обратите внимание на фигурные скобки, указывающие на то, что это формула массива. То есть наша функция ВПР работает не с одиночными значениями, а один раз с массивами данных.
Разберем процесс подробно.
Ищем дату, написанную в ячейке B1. Но мы просто будем искать его не в нашем исходном диапазоне данных, а в немного измененном. Для этого воспользуемся условием
ЕСЛИ(B6:B19=B2,A6:C19;»»)
То есть в том случае, если название магазина соответствует критериям в ячейке B2, мы оставляем исходные значения нашего диапазона. А если нет, то заменяем их пробелами. И так для каждой строки.
В результате мы получаем виртуальный массив данных на основе нашей исходной таблицы:
Как видите, строки, которые ранее имели «Магазин 1», были заменены пустыми. И теперь мы будем искать нужную дату только среди информации «Магазина 2». И извлеките значения дохода из третьего столбца.
С такой работой функция ВПР будет работать просто отлично.
Это движение стало возможным благодаря применению матричной формулы. Поэтому обратите особое внимание: скобки в формуле не нужно писать от руки! В ячейке B3 вы записываете формулу
=ВПР(B1,ЕСЛИ(B6:B19=B2,A6:C19,»»),3,0)
А затем нажмите комбинацию клавиш CTRL+Shift+Enter. В этом случае Excel поймет, что вы хотите ввести формулу массива, и подставит скобки.
Таким образом, функция ВПР выполняет поиск по двум столбцам в 2 этапа: во-первых, мы очищаем диапазон данных от строк, которые не соответствуют одному из условий, используя функцию ЕСЛИ и формулу массива. А затем по этим скорректированным данным мы выполняем обычный поиск, используя только второй критерий с помощью ВПР.
Чтобы упростить вашу работу в будущем и застраховаться от возможных ошибок при добавлении новой информации о продажах, рекомендуем использовать «умную» таблицу. Он автоматически изменит свой размер, чтобы разместить добавленные строки, и вам не нужно будет изменять какие-либо ссылки на формулы.
Вот как это будет выглядеть.
ВПР по нескольким критериям с применением массивов — способ 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} просто означает, что вы должны сначала взять первый элемент, затем второй и объединить их в виртуальную таблицу: массив.
Мы будем искать в первом столбце этой виртуальной таблицы и извлекать результат из второго.
Поэтому для того, чтобы функция ВПР работала с несколькими условиями, снова используем дополнительный столбец. Просто мы создаем его не реально, а виртуально.
Двойной ВПР при помощи ИНДЕКС + ПОИСКПОЗ
Также мы больше не будем говорить о функции ВПР, а решим ту же задачу. В качестве критериев поиска нам снова нужно использовать различные условия.
Возможно, есть еще более гибкие решения, чем функция ВПР. Это комбинация функций ИНДЕКС + ПОИСКПОЗ.
Сфера его применения очень широка, о чем мы также расскажем на сайте мистер-офис.ру.
А пока вернемся к нашей проблеме.
Формула в 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) создаст виртуальную таблицу, подобную этой:
Как видите, сначала мы последовательно сравниваем каждое значение с нашими критериями выбора. В столбце А у нас есть месяцы; мы сравниваем их с критерием месяца ячейки C1. Если оно совпадает, получаем ИСТИНА, в противном случае — ЛОЖЬ. Аналогично последовательно проверяем год и название магазина. А потом просто умножить значения. Поскольку логические переменные Excel равны либо 0, либо 1, то их произведение может равняться 1, только если у нас есть ИСТИНА для каждого столбца (т.е. 1). Во всех остальных случаях получаем 0.
Мы следим за тем, чтобы число 1 встречалось только один раз.
С помощью ПОИСКПОЗ мы определяем, в какой позиции он находится. В какой позиции стоит 1, желаемая сумма дохода находится в той же позиции в массиве. В нашем случае это 10.
Также с помощью ИНДЕКС мы извлекаем 10-й подряд доход.
Поэтому выбираем значение по различным условиям без использования функции ВПР.
Достойная замена – функция СУММПРОИЗВ.
У нас есть данные о продажах от разных менеджеров в разных регионах. Необходимо сделать выбор по дате, менеджеру и региону.
Поясним расчеты.
Выражение
=СУММПРОИЗВ((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))
Итак, мы увидели примеры использования функции ВПР с двумя и несколькими условиями. Мы также обнаружили, что есть отличная альтернатива этой ценной функции.
Примеры использования функции ВПР:
Как объединить две или более таблиц в Excel. В этом руководстве вы найдете несколько приемов для объединения таблиц Excel путем сопоставления данных в одном или нескольких столбцах. Как часто при анализе в Excel вся необходимая информация собирается в один…
2 способа извлечения данных из разных таблиц с помощью ВПР. — Задача: данные для поиска и извлечения с помощью функции ВПР находятся в нескольких таблицах. Эти таблицы имеют одинаковую структуру (то есть одни и те же столбцы, расположенные в одном и том же…
Вычисление номера столбца для извлечения данных в ВПР — Домашнее задание: Самый простой способ узнать, как указать столбец, из которого функция ВПР будет извлекать данные. При этом мы не будем менять саму формулу, так как это может привести к случайным ошибкам.…
4 способа сделать левую ВПР в Excel. — Функция ВПР — одна из самых популярных, когда нужно искать и извлекать данные из таблицы. Однако у него есть большой недостаток. Она смотрит в дальний левый угол…
Формула ВПР в Excel для сравнения двух таблиц — 4 способа — Сравнение таблиц — это задача, которую Excel приходится решать довольно часто. Например, у нас есть старый прайс-лист и его новая версия. Вам нужно посмотреть, какие цены изменились и…
Почему ВПР не работает в Excel? — Функция ВПР — очень мощный инструмент поиска. Но если по какой-то причине это не удастся, вы получите сообщение об ошибке #Н/Д. Давайте попробуем вместе…
Функция ВПР в Excel: пошаговая инструкция с 5 примерами. ВПР — это функция Excel для поиска и извлечения данных из определенного столбца в таблице. Поддерживает приблизительные и точные совпадения, а также подстановочные знаки (* и ?) для поиска значений…
Формула ВПР в Excel: 22 факта, которые вам нужно знать. — В процессе работы в Excel часто возникает задача извлечения необходимых данных из рабочих листов. Для этой цели в Excel предусмотрена формула ВПР. И хотя ВПР относительно… Александр Трифунтов2020-05-22T17:14:21+03:0024 ноября 20194 коммент