Рассмотрим несколько примеров практического применения функции ВПР. Обычное ее применение — это поиск и извлечение данных из таблицы в соответствии с каким-то условием. Сейчас мы рассмотрим, как при помощи формулы ВПР можно извлечь данные из разных таблиц.
Задача: Данные, которые нужно найти и извлечь при помощи функции ВПР, находятся в нескольких таблицах. Эти таблицы имеют одинаковую структуру (то есть, одни и те же столбцы, расположенные в одном и том же порядке). Необходимо помочь функции ВПР определить, из какой именно таблицы ей извлечь результаты поиска?
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). Она позволяет преобразовать текст в ссылку, которую можно использовать в формуле ВПР. Разберем на примере.
Предположим, у нас есть данные о суммах выплат по отдельным сотрудникам по месяцам. Необходимо организовать быстрый поиск суммы по имени и по месяцу.
Чтобы не вводить критерии поиска руками, создадим два выпадающих списка: один - с именами, второй - с названиями месяцев.
Также создадим 3 именованных диапазона с именами такими же, как названия месяцев - январь, февраль, март.
Теперь вспомним, что формула
=ДВССЫЛ("март")
означает ссылку на именованный диапазон "март". А если подставить сюда ссылку на ячейку, то ссылка будет формироваться исходя из содержимого этой ячейки.
= ДВССЫЛ($B$15)
при условии, что в D15 выбрано значение "март", также означает ссылку на диапазон "март". Но ссылка эта уже будет динамической, поскольку будет меняться в зависимости от того, какой месяц был выбран.
Таким образом, формулу в ячейке С14 запишем:
=ВПР($B$14;ДВССЫЛ($B$15);2;ЛОЖЬ)
Полученная таким хитрым образом ссылка на диапазон будет представлять таблицу поиска данных для функции ВПР. Искать мы будем, как обычно, в первом столбце, а извлекать данные — из второго. А в ячейке В14 мы указали критерий поиска для ВПР, который также можем быстро менять при помощи выпадающего списка.
Таким образом мы решили проблему, как ВПР заставить использовать разные таблицы с данными. И, кроме того, мы даже использовали здесь два критерия поиска - имя и месяц.
Надеемся эта информация была вам полезна.
