Задача: Данные, которые нужно найти и извлечь при помощи функции ВПР, находятся в нескольких таблицах. Эти таблицы имеют одинаковую структуру (то есть, одни и те же столбцы, расположенные в одном и том же порядке). Необходимо помочь функции ВПР определить, из какой именно таблицы ей извлечь результаты поиска?

1. Использование обработки условия при помощи ЕСЛИ.

Рассмотрим на примере. Необходимо определить комиссионные для каждого из менеджеров с учетом объема их продаж и стажа работы. В зависимости от стажа существуют различные ставки комиссионных выплат, как это показано на рисунке ниже.

Для того, чтобы легче было работать с таблицами ставок, используем именованные диапазоны и обозначим их tabl1, tabl2 и tabl3.

В качестве условия для использования каждой из таблиц служит стаж работника. Таким образом, внутри функции ВПР (VLOOKUP) используем обработку условий при помощи ИЛИ (IF). В ячейке D2 запишем:

=ВПР(C2;ЕСЛИ(B2>3;tabl3;ЕСЛИ(B2<1;tabl1;tabl2));2;1)

Если стаж более 3 лет, то используем tabl3. Если нет, то проверяем условие "стаж менее года". В этом случае данные будем извлекать из tabl1. Если и это не выполняется, тогда остается только второй вариант и диапазон tabl2.

Таким образом, наш источник данных будет меняться в зависимости от величины стажа.

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

Сумма комиссионных находится простым произведением ставки комиссионных на объем продаж. Далее копируем эти формулы для всех менеджеров.

Как приятную особенность использования именованных диапазонов следует отметить, что не имеет значения, где они расположены на листе рабочей книги Excel.

Как видите, все отлично работает, хотя диапазон поиска находится левее столбца с условиями. Своего рода "обратный" или "левый" ВПР, о котором мы уже рассказывали. (см. ссылки в конце статьи).

2. Использование функции ДВССЫЛ и именованных диапазонов.

Еще один хороший способ создать ссылку на именованный диапазон - это функция ДВССЫЛ (INDIRECT). Она позволяет преобразовать текст в ссылку. Разберем на примере.

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

Чтобы не вводить критерии поиска руками, создадим два выпадающих списка: один - с именами, второй - с названиями месяцев.

Как создать выпадающий список в Excel - читайте пошаговую инструкцию.

Также создадим 3 именованных диапазона с именами такими же, как названия месяцев - январь, февраль, март.

выбор таблицы с данными для ВПР

Теперь вспомним, что формула

=ДВССЫЛ("март")

означает ссылку на именованный диапазон "март". А если подставить сюда ссылку на ячейку, то ссылка будет формироваться исходя из содержимого этой ячейки.

= ДВССЫЛ($B$15)

при условии, что в D15 выбрано значение "март", также означает ссылку на диапазон "март". Но ссылка эта уже будет динамической, поскольку будет меняться в зависимости от того, какой месяц был выбран.

Таким образом, формулу в ячейке С14 запишем:

=ВПР($B$14;ДВССЫЛ($B$15);2;ЛОЖЬ)

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

ссылка на именованный диапазон в ВПР

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

Надеемся эта информация была вам полезна.

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

Вычисление номера столбца для извлечения данных в ВПР - Задача: Наиболее простым способом научиться указывать тот столбец, из которого функция ВПР будет извлекать данные. При этом мы не будем изменять саму формулу, поскольку это может привести в случайным ошибкам.…
4 способа, как сделать левый ВПР в Excel. - Функция ВПР – одна из самых популярных, когда нужно найти и извлечь из таблицы какие-либо данные. Но при этом она имеет один существенный недостаток. Поиск она производит в крайнем левом…
Формула ВПР в Excel для сравнения двух таблиц - 4 способа - Сравнение таблиц – это задача, которую в Excel приходится довольно часто решать. Например, у нас есть старый прайс-лист и его новая версия. Нужно просмотреть, цены на какие товары изменились и…
Почему не работает ВПР в Excel? - Функция ВПР – это очень мощный инструмент поиска. Но если он по каким-то причинам завершился неудачно, то вы получите сообщение об ошибке #Н/Д (#N/A в английском варианте). Давайте постараемся вместе…
ВПР с несколькими условиями: 5 примеров. - Очень часто наши требования к поиску данных не ограничиваются одним условием. К примеру, нам нужна выручка по магазину за определенный месяц, количество конкретного товара, проданного определенному покупателю и т.д. Обычными…
Функция ВПР в Excel: пошаговая инструкция с 5 примерами - ВПР - это функция Excel для поиска и извлечения данных из определенного столбца в таблице. Она поддерживает приблизительное и точное сопоставление, а также подстановочные знаки (* и ?). Значения поиска…
22 факта, которые нужно знать о формуле ВПР в Excel. - В процессе работы в Excel часто возникает задача извлечения нужных данных из рабочих таблиц. Для этой цели в Excel предусмотрена функция ВПР (VLOOKUP в английском варианте). И все же, хотя…