Нестандартное применение функции ВПР в MS Excel

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

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

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

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

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

Исходные данные

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

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

Нестандартное применение функции ВПР в MS Excel

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

Нестандартное применение функции ВПР в MS Excel

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

должность (консультант, специалист первого или второго уровня, специалист по распределению консультантов и др.);

уровень специалиста (консультант-продавец, старший консультант, консультант-эксперт и др.);

расположение точки продаж (Москва, Санкт-Петербург, другие регионы);

подразделение, обслуживающее куплю-продажу товаров торговой марки (в нашем условном примере – «Марка 1», «Марка 2» и т.д.);

уровень заработной платы;

набор КПЭ для расчета размера премии к выплате.

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

Готовое решение

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

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

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

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

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

Нестандартное применение функции ВПР в MS Excel

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

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

Нестандартное применение функции ВПР в MS Excel

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

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

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

Вложенные файлы

Доступно только подписчикам
  • Файл Excel, на который ссылается данная статья.xlsx


Ваша персональная подборка

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

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

    Рекомендации по теме

    Школа

    Школа

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

    Записаться

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

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

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

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

    А еще...




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

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

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

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

    Это бесплатно и займет всего 1 минуту.

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

    Внимание!
    Вы читаете профессиональную статью для финансиста.
    Зарегистрируйтесь на сайте и продолжите чтение!

    Это бесплатно и займет всего 1 минут.

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