Функция ВПР в Excel. Приемы работы.
Функция ВПР для чайников. Простой пример использования.
На текущем занятии рассмотрим, как для связывания таблиц в Excel используется функция ВПР. Урок будет полезен как для чайников, так и для продвинутых пользователей. Такая связь может понадобиться в самых разных ситуациях. Может потребоваться получить по известному артикулу розничную цену товара. Возможно, потребуется, зная отдел и должность сотрудника, узнать его оклад. Возможно, будет необходимо отобразить по известному коду наименование номенклатуры.
В простейшем случае можно применить функцию ВПР без дополнительных комбинаций. Если требуется найти нужное значение по конкретному критерию без дополнительных, то достаточно ввести функцию ВПР, используя стандартные параметры поочередно. Напомним эти параметры.
- Критерий. Функция выполнит поиск значения, соответствующего указанному данным аргументом, в крайней левой колонке таблицы, из которой требуется извлечь данные. Программа либо найдет значение, совпадающее с указанным, либо найдет ближайшее подходящее значение, не превышающее критерий. Какой вариант будет использован – зависит от четвёртого параметра, о котором речь ниже.
- Таблица поиска. Это таблица, из которой извлекаются необходимые данные. Поиск критерия выполняется по первому столбцу данной таблицы. Если предполагается копирование функции ВПР по столбцу, то адрес данной таблицы надо закрепить. Так же можно присвоить диапазону с таблицей определенное имя или превратить ее в умную таблицу.
- Номер столбца таблицы поиска, из которого надо получить результат. Можно указать его явно, либо применить для поиска по заголов5ку функцию ПОИСКПОЗ
- Интервальный просмотр, а если проще – способ проверки ячеек первого столбца таблицы поиска на соответствие критерию. Тут есть два варианта
Вариант «А». Четвертый параметр задан как 1, ИСТИНА или вообще не задан. В этом случае Excel выполнит поиск в первой колонке таблицы поиска значения, которое максимально близко к критерию, но не превышает его. Первый столбец таблицы поиска в этом случае предварительно должен быть отсортирован по возрастанию. Этот вариант применяется при необходимости найти значение в некотором диапазоне, например, при обработке ступенчатых скидок.
Вариант «Б». Четвертый параметр задан как 0 или ЛОЖЬ. В таком случае запустится поиск среди значений первого столбца на точное соответствие критерию. Сортировка при этом не требуется. Способ применяется при получении значения из таблицы по явно заданному критерию.
Приведем простой пример использования ВПР. Имеется таблица по реализации различного вида товаров в ТД «Лакомка». Необходимо определить сумму реализации для котлет «Нежность».
Наша таблица находится в диапазоне A7:L37 на листе «Реализация». В результате получаем такую запись для работы функции, с учетом того, что нужный нам товар указан в ячейке А2 текущего листа с формулой.
=ВПР(A2;реализация!A6:L38;5;0)
Более подробно о том, как используется функция ВПР для чайников, о ее особенностях и связанных с ней возможных проблемах рассказано в наших предыдущем материале.
Сегодня же мы поговорим о более сложной ситуации. Приведем такой пример. Имеется табель рабочего времени. Необходимо извлечь для каждого сотрудника его тарифную ставку из таблицы штатного расписания. Все было бы просто, если бы должности сотрудников были уникальными. Но, как видно по таблице «Штатное расписание», у нас имеются как одинаковые наименования должностей в разных отделах, так и несколько должностей в одном и том же отделе. Другими словами, ни наименование должности, ни наименование отдела в штатном расписании вовсе не являются уникальными.
Наша же задача – получить тарифную ставку, соответствующую конкретно указанному сочетанию отдела, к которому принадлежит сотрудник, и занимаемой им должности. Как это сделать – рассмотрим ниже.
Функция ВПР с несколькими условиями.
Рассмотрим на нескольких примерах, как применяется функция ВПР с несколькими условиями поиска критерия.
Способ 1. Создаем дополнительный столбец, в котором объединяем колонки таблицы поиска, по которым необходимо определить соответствие условиям. В данном случае в штатном расписании мы объединим колонки «Структурное подразделение» и «Должность…», добавив между ними пробел.
Теперь в качестве критерия достаточно аналогично соединить наименование отдела, пробел и название должности в табеле рабочего времени. В качестве таблицы поиска задаем наше штатное расписание, начиная с столбца, в котором выполнено объединение наименований подразделения и должности.
Несмотря на кажущуюся простоту, недостаток способа очевиден. Мало того, что требуется создать дополнительный столбец, а затем чаще всего еще и прятать его от коллег. В большинстве случаев такое добавление невозможно в принципе, так как в нормальных организациях листы с критичными данными – а тарифная сетка безусловно к таким относится – защищены от изменений. И это как минимум. Поэтому данный способ был приведен только по причине того, что он вообще имеется.
Способ 2 . Используем формулу массива и комбинацию функции ЕСЛИ с функцией ВПР. Вначале функция ЕСЛИ формирует из всей таблицы штатного расписания только те строки, начиная с колонки «Наименование подразделения», у которых значение должности совпадает с названием должности сотрудника, для которого извлекается тарифная ставка. Это достигается формулой
ЕСЛИ(штатное_расписание_2020!$D$15:$D$92=Табель2020!D8;штатное_расписание_2020!$C$15:$F$92).
В этой формуле:
штатное_расписание_2020!$D$15:$D$92 – столбец таблицы «Штатное расписание» , содержащий название должностей.
Табель2020!D8 – наименование должности сотрудника из листа «Табель»
штатное_расписание_2020!$C$15:$F$92 – исходная таблица штатного расписания, по которой формируется новый диапазон.
После того, как новый диапазон получен, уже не составит труда получить по нему значение тарифной ставки, используя название отдела, к которому принадлежит сотрудник. Итоговая формула выглядит следующим образом.
{=ВПР(C8;ЕСЛИ(штатное_расписание_2020!$D$15:$D$92=Табель2020!D8;штатное_расписание_2020!$C$15:$F$92);4;0)}
Заметим, что это – формула массива, поэтому ее необходимо вводить сочетанием Ctrl + Shift + Enter. Заметим так же, что если заранее присвоить на листе «Штатное расписание» имя «расписание» для всей таблицы, начиная с графы «Наименование, для графы «Наименование подразделения» присвоить имя «Отдел», а для графы «Должность» – имя «должность», то формула получается более простой, а главное – более доступной для восприятия.
В этом случае формула будет выглядеть уже так.
{=ВПР(C8;ЕСЛИ(должность=Табель2020!D8;расписание);4;0)}
Напомним, что для присвоения имени диапазону достаточно его выделить, в контекстном меню выбрать команду «присвоить имя» и ввести желаемое значение, используя только буквы, цифры, подчеркивание и точки.
Способ 3. Так же используется формула массива. Однако вместо функции если используем функцию «Выбор». Данная функция позволяет сформировать новый диапазон из указанных в ней, если в качестве индекса выбора применить массив, содержащий номера всех указанных одномерных диапазонов в ней. Причем эти диапазоны могут быть как явными, так и сформированными формулой. Сразу приведем результат.
{=ВПР(C8&D8;ВЫБОР({1;2};отдел&должность;штатное_расписание_2020!F15:F92);2;0)}
Что же здесь происходит? Ключевое значение имеет выражение
ВЫБОР({1;2};отдел&должность;штатное_расписание_2020!F15:F92)
Именно здесь формируется новая таблица из двух столбцов. В первом мы соединяем название отдела и должности, во второй записываем значения тарифных ставок. Отдел и должность – это имена диапазонов, присвоенные нами на предыдущем примере, штатное_расписание_2020!F15:F92 – это диапазон таблицы «штатное расписание» с тарифными ставками. Далее все происходит так же, как и в первом способ. Однако поисковый диапазон формируется виртуально, поэтому никаких дополнительных столбцов не требуется. Кроме этого, не требуется вычислять номер столбца, из которого извлекается значение, так как в этом случае он всегда второй.
Подведем итоги.
Подведем итоги нашего занятия. На самом деле таких вариантов использования ВПР с несколькими условиями конечно же больше. Однако и тех, которые приведены в данной статье, вполне достаточно для работы с ВПР при наличии нескольких критериев, по которым необходимо выполнить поиск для получения нужного значения из связанной таблицы. Кому-то нравится вариант с дополнительным столбцом. Кто-то оценит вариант с комбинированием с ВПР функции ЕСЛИ. Кому-то более удобным покажется вариант с функцией ВЫБОР. Кроме этого, не стоит забывать и о функциях ИНДЕКС и СМЕЩ в комбинации с функцией ПОИСК.
Наше же занятие подошло к концу. Если же Вы хотите узнать еще больше полезных трюков работы в Excel, если хотите не просто переписывать формулы, но и научиться в них разбираться на профессиональном уровне, если хотите сделать свою работу в Excel более удобной, быстрой и качественной – добро пожаловать на наши курсы, доступные как в очном формате, так и в виде вебинаров и тренингов онлайн. Вы можете связаться с нами, используя контактные данные на листе «контакты» или послать сообщение на электронную почту.
Также вы можете скачать учебный файл для отработки изученных на нашем занятии формул. Мы же всем желаем хорошего дня и спешной работы.
