Как найти значение по трем столбцам

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

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

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

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

Итак, необходимо выбрать значение выручки за определенный месяц, год и по нужному магазину. В итоге имеем 3 условия отбора.

Сразу по трем столбцам функция ВПР искать не может. Поэтому нам нужно объединить их в один. И, поскольку поиск производится всегда в крайнем левом (первом) столбце, то нужно добавить его в нашу таблицу тоже слева.

Вставляем перед таблицей с данными дополнительный столбец A. Затем при помощи оператора & объединяем в нем содержимое B,C и D. Записываем в А7

=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.

Выше мы уже рассматривали, как при помощи формулы массива можно организовать поиск ВПР с несколькими условиями. Предлагаем еще один способ.

Условия возьмем те же, что и в предыдущем примере.

Формулу в С4 введем такую:

=ВПР(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 – это значения, одно из которых нужно в итоге выбрать.

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

В первой колонке этой виртуальной таблицы мы будем искать, а из второй – извлекать результат.

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

Двойной ВПР при помощи ИНДЕКС + ПОИСКПОЗ

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

Существуют, пожалуй, даже более гибкие решения, нежели функция ВПР. Это комбинация функций ИНДЕКС + ПОИСКПОЗ.

Область их применения очень велика, о чем бы также будем рассказывать на сайте mister-office.ru.

А пока вернемся вновь к нашей задаче.

Формула в С4 теперь выглядит так:

=ИНДЕКС(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))

Итак, мы рассмотрели примеры использования функции ВПР с двумя и с несколькими условиями. А также обнаружили, что этой ценной функции есть замечательная альтернатива.

[the_ad_group id=”48″]

Примеры использования функции ВПР: