ВПР с несколькими условиями

ВПР с несколькими условиями
На чтение
26 мин.
Просмотров
26
Дата обновления
06.11.2024

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

Мы предложим вам несколько вариантов решения задачи поиска различных условий.

 

ВПР по нескольким условиям с использованием дополнительного столбца.

Задачу, рассмотренную в примере выше, можно решить другим способом, не используя формулу массива. Ведь работа с массивами многим кажется сложной и недоступной. Дополнительный столбец для множественного поиска был бы в некотором смысле проще.

 

 

Поэтому нужно выбрать значение выручки за определенный месяц, год и для нужного магазина. В итоге имеем 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 коммент

 

0 Комментариев
Комментариев на модерации: 0
Оставьте комментарий