Функция ВПР в Excel. Пример использования

12909
Ивлев Дмитрий
специалист управления по финансам, рискам и контролю ООО «Страховая компания КАРДИФ»
Некоторые инструменты Excel способны значительно облегчить работу финансового директора. Например, c помощью функции ВПР в Excel можно изменить исходный отчет, полученный из ИТ-системы, который затем в преобразованном виде снова загрузить в программу. Разберем на примере.

Функция ВПР в Excel наиболее понятна, если разобрать ее на примере.

Менеджмент условной компании «Полет»* решил изменить схему начисления премий работникам. Причин тому было несколько: во-первых, по абсолютной величине средней премии сотрудника отдела продаж компания занимала второе место в своем рыночном сегменте. Во-вторых, в соотношении фиксированной части оклада и доплаты за объем продаж постоянная часть была максимальной для рынка. Эти факторы существенно влияли на фонд оплаты труда и соответствующие налоговые отчисления. При этом достичь ключевых показателей эффективности (КПЭ) на 100% сотрудникам удавалось довольно редко.

Новый способ расчета премий формировался с учетом факторов, которые предложила служба по управлению персоналом. Решение о переходе к новой системе определения бонусов приняли в конце I квартала, поэтому корректировки касались данных уже II квартала. Изменения проводились в Excel по нескольким причинам:

  • исходные данные службы по управлению персоналом в части правил расчета премий хранились в Excel;
  • данные необходимо было свести в единую таблицу по вариантам премий;
  • значения премий во всем списке сотрудников удобнее заменить сначала в Excel и затем загрузить в ИТ-систему («1С: Зарплата и Управление персоналом 8»).

Функция ВПР в Excel и ее применение. Исходные данные

Сотрудникам финансового отдела предоставили набор таблиц Excel, в одной из которых – список данных с набором фактических параметров для каждого элемента (строка), полученных из ИТ-системы, в остальных – исходные данные по взаимосвязям групп этих параметров между собой (см. рис. 1 и 2). Также существует одна или несколько искомых величин, значение которых определяется как связь между параметрами, упомянутыми выше. Наша задача – добавить столбцы с искомыми величинами (в зависимости от их количества) в таблицу с фактическими данными (отчет из ИТ-системы), подобрав их на основе известных взаимосвязей параметров. В этом поможет функция ВПР.

Рисунок 1. Лист «Исходные данные»

Функция ВПР в Excel - нетрадиционный подход

Рисунок 2. Лист «Список вариантов премий»

Функция ВПР в Excel - нетрадиционный подход

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

  • должность (консультант, специалист первого или второго уровня, специалист по распределению консультантов и др.);
  • уровень специалиста (консультант-продавец, старший консультант, консультант-эксперт и др.);
  • расположение точки продаж (Москва, Санкт-Петербург, другие регионы);
  • подразделение, обслуживающее куплю-продажу товаров торговой марки (в нашем условном примере – «Марка 1», «Марка 2» и т.д.);
  • уровень заработной платы;
  • набор КПЭ для расчета размера премии к выплате.

В I квартале премии сотрудникам начислили исходя из определенной доли их оклада. Однако со II квартала работников планировалось премировать ежемесячно фиксированными суммами, исходя из параметров, приведенных выше. Сумма для каждого сотрудника определялась (в зависимости от результатов) по условной шкале: минимальная (min), средняя (100%) и максимальная (max), детали расчета премий опустим. Финансистам нужно было подобрать для каждого сотрудника в зависимости от занимаемой им должности набор исходных размеров премий (min, 100%, max), а также добавить эти значения в таблицу с фактическими данными.

В таблице «Список вариантов премий» многие значения повторяются, поскольку отвечают одним и тем же комбинациям параметров (нескольким группам) работников. Это несколько упрощает задачу. Однако была возможность менять (например, индексировать) размеры премий сразу по нескольким группам сотрудников в ИТ-системе и защитить полученные массивы данных от неосознанных ошибок ручного ввода. Поэтому мы ввели новый параметр «Уровень премии» – соответствующий столбец включили в листы «Список вариантов премий» и «Готовое решение».

Используя данный параметр в ИТ-системе, достаточно изменить размеры премий, отвечающие данному уровню, в справочнике, и данные обновятся у всех групп сотрудников. Если же возникнет необходимость пересмотреть размер премии только одному работнику, достаточно заменить значение в поле «Уровень премии» в карточке сотрудника. При этом таблица с фактическими данными не будет перегружена избыточным количеством новых столбцов, что обеспечит удобство для пользователей и защитит базу данных от ошибок при вводе.

Функция ВПР в Excel в итоговом варианте

Теперь нужно присвоить каждому сотруднику уровень премии на основе его параметров, то есть задать соответствие между данными о каждом сотруднике на листе «Исходные данные» и необходимым вариантом из таблицы на листе «Список вариантов премий». Традиционно это выполняют с помощью функции ВПР в Excel (VLOOKUP), которая позволяет по одному аргументу подобрать искомое значение в массиве данных.

Однако в нашем случае функция ВПР в Excel не используется напрямую, поскольку нужно подобрать значение не по одному, а сразу нескольким критериям, а программа не предусматривает других функций для решения подобных задач. Чтобы обойти это ограничение, преобразуем параметры «Департамент», «Должность», «Регион», «Зарплата» в один. Сделать это можно либо с помощью функции «Сцепить» (=СЦЕПИТЬ), либо использовать символ амперсанд (&). Специалисты компании «Полет» использовали второй вариант, который оказался более простым и гибким в работе.

Рисунок 3. Технический столбец с объединенным аргументом на листе «Исходные данные»

Функция ВПР в Excel - нетрадиционный подход

В результате преобразований (см. рис. 3) из нескольких аргументов получился один технический столбец, который можно использовать с функцией ВПР. Теперь нужно добавить такой же столбец на лист «Список вариантов премий». После этого можно использовать ВПР и решить поставленную задачу. Добавим на лист «Исходные данные» столбец «Уровень премии», в котором как один из вариантов будет использоваться формула =ЕСЛИ(ИЛИ (L11=$L$9;L11=$L$33);L11;ВПР(H11;'Список вариантов премий'!$B$13:$K$699;2;0))».

Рисунок 4. Лист «Готовое решение»

Функция ВПР в Excel - нетрадиционный подход

В формулу намеренно был добавлен оператор «ЕСЛИ», чтобы не присваивать уровень премии уволенным, а также находящимся в длительном отпуске по уходу за ребенком или по беременности и родам сотрудникам и не закладывать суммы бонусов на них в бюджет. Заполнив столбец «Уровень премии», мы получили соответствие между фактическими данными и уровнями премий.

После использования функции ВПР в Excel финансистам компании «Полет» оставалось лишь подготовить техническое задание штатному программисту, который должен был создать новый справочник в базе данных, загрузить туда варианты размеров премий и вставить столбцы с этими данными (min, «100%», max) для расчетов выплачиваемых сумм. Затем в карточки работников добавили поле, содержащее уровень премии каждого из них со ссылкой на новый справочник.

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

Полезная статья? Добавьте страницу в закладки, сохраните, распечатайте или переадресуйте. 



Подписка на статьи

Чтобы не пропустить ни одной важной или интересной статьи, подпишитесь на рассылку. Это бесплатно.

Школа

Школа

Проверь свои знания и приобрети новые

Записаться

Самое выгодное предложение

Самое выгодное предложение

Воспользуйтесь самым выгодным предложением на подписку и станьте читателем уже сейчас

Живое общение с редакцией

А еще...


Рассылка




© 2007–2016 ООО «Актион управление и финансы»

«Финансовый директор» — практический журнал по управлению финансами компании

Зарегистрировано Федеральной службой по надзору в сфере связи,
информационных технологий и массовых коммуникаций (Роскомнадзор)
Свидетельство о регистрации Эл №ФС77-43625 от 18.01.2011
Все права защищены. email: fd@fd.ru


  • Мы в соцсетях
×
Зарегистрируйтесь на сайте,
чтобы скачать образец документа

В подарок, на адрес электронной почты, которую Вы укажете при регистрации, мы отправим форму «Порядок управления дебиторской задолженностью компании»

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Я тут впервые
И получить доступ на сайт
Займет минуту!
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Зарегистрируйтесь на сайте,
чтобы продолжить чтение статьи

Еще Вы сможете бесплатно:
Скачать надстройку для Excel. Узнайте риск налоговой проверки в вашей компании Прочитать книгу «Запасной финансовый выход» (раздел «Книги»)

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Я тут впервые
И получить доступ на сайт
Займет минуту!
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль