На службе эйчара Excel занимает свое, пускай и не главное, но определенно заметное место. Однако для HR-менеджеров, которые пока далеки от сложных excel-инструментов, работа с ним часто похожа на пытку. Давайте это поправим. И начнем вот с чего…

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


График тренингов

Если вы сталкивались в своей работе с планированием и организацией тренингов для сотрудников, то согласитесь, что грамотный выбор времени для них — очень важен. Дорогой и нужный тренинг, но проведенный в неудачное время, когда сотрудники завалены сезонной работой или, наоборот, разъехались по отпускам — выкинутые деньги компании и зря потраченные ресурсы.

Для правильного выбора дат нужна наглядность. Предположим, что на каждого сотрудника запланировано по два тренинга в год. Сведем предварительные даты начала и завершения тренингов по каждому сотруднику в следующую таблицу:

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

Теперь выделим все пустые квадратные ячейки, начиная с F5 и до конца таблицы вправо-вниз и выберем на вкладке Главная — Условное форматирование — Создать правило (Home — Conditional formatting — Create Rule). В открывшемся окне уточним тип создаваемого правила — Использовать формулу для определения форматируемых ячеек и введем следующую формулу:

Это комбинация из двух логических функций И, связанных друг с другом логическим ИЛИ. Каждая из функций И (AND) проверяет попадание даты соответствующей данной ячейке в диапазон между датами старта и окончания тренинга. Поскольку мы хотим залить цветом оба тренинга, то две функции И связаны одной ИЛИ поверх.

В результате получаем:

Причем, если изменить шаг временной шкалы в желтой ячейке E1 до, например, недели, то получим более общую картину:

 

Расчет бонусов или доплаты за выслугу лет

Предположим, что мудрое руководство нагрузило нас приятной обязанностью — рассчитать доплаты к окладам наших сотрудников за выслугу лет (такое иногда бывает в госкомпаниях) или добавочные бонусы к зарплате. И то и другое предполагает сложную многоступенчатую систему доплат в зависимости от стажа наших коллег. Представим это в виде таблицы:

То есть, если сотрудник проработал у нас меньше 12 месяцев — он не получает ничего. Если проработал от года до двух — получает 10% доплаты (или бонуса). Если от двух до трех — 15%. Если от трех до пяти — 25% и т. д. Максимальный бонус в 100% полагается только старожилам — тем, кто работает в компании больше 10 лет.

Можно пойти классическим путем и использовать функцию проверки ЕСЛИ (IF). Причем, нам придется вкладывать одну ЕСЛИ в другую несколько раз, т. к. надо проверить попадание в несколько диапазонов:

Бррр… Ужас, правда? Задачу можно решить гораздо изящнее, если использовать известную в узких кругах финансистов и аналитиков, функцию ВПР (VLOOKUP):

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

• Искомое значение — значение стажа сотрудника, для которого мы определяем бонус.
  
  
• Таблица — наша таблица бонусов. Если вы планируете копировать формулу вниз на других сотрудников, то ссылку на таблицу нужно будет сделать абсолютной, т. е. добавить значки доллара, чтобы при копировании ссылка на смещалась. Это можно сделать с помощью клавиши F4, предварительно выделив адрес в строке Таблица.
  
  
• Номер столбца — порядковый номер столбца в нашей таблице бонусов, откуда мы берем размер доплаты (у нас всего два столбца и номер, очевидно, 2).
  
  
• Интервальный просмотр — этот аргумент нужно задать равным 1, чтобы Excel производил поиск ближайшего наименьшего числа в первой колонке таблицы. Для точного поиска используется значение 0.

 

Лепестковая диаграмма компетенций

Любой HR занимавшийся когда-либо подбором персонала, не понаслышке знает, как сложно порой бывает подобрать правильных людей на вакантные должности. Думаю, все могут припомнить последствия неудачного выбора, когда сотрудники потом или «не тянут» или быстро «перерастают» занимаемую должность и процесс приходится повторять заново, тратя время, ресурсы и деньги компании. Как же наглядно и качественно оценить — насколько данный кандидат подходит на определенную должность?

В такой задаче имеет смысл использовать хоть и не очень распространенный, но весьма удобный в данном случае тип диаграммы в Microsoft Excel — Лепестковая (Radial). В английской терминологии этот тип диаграмм иногда называют еще Spider Chart — за ее внешнее сходство с паутиной.

Составим для нашей вакантной должности список из 5–10 ключевых компетенций (навыков, требований). Под 0 в данном случае понимается отсутствие требований, под 10 — максимальная потребность. Например, на должность директора по продажам этот список может выглядеть так:

• Навыки устного и письменного общения — 8
• Навыки проведения презентаций — 7
• Знание/понимание английского — 5
• Знание технологии производства товаров — 2
• Знание финансов и бухгалтерии — 7
• Знание компьютера и ПО — 4

… и т. д.

По результатам общения с кандидатами (рассмотрения их резюме, собеседований, тестирования) мы можем создать похожий список их качеств и навыков с аналогичными оценками, нормированными по шкале от 0 до 10.

Теперь можно свести все наши данные в одну таблицу и, выделив ее, построить по ней лепестковую диаграмму, выбрав на вкладке Вставка в группе Диаграмма команду Лепестковая:

Дополнительно, для наглядного отображения набранных баллов в диапазоне B2:D10 я использовал условное форматирование гистограммами (Главная — Условное форматирование — Гистограммы), а в диапазоне C12:D12 — цветовыми шкалами (Главная — Условное форматирование — Цветовые шкалы).

Какие же выводы можно сделать по диаграмме?

Хорошо видно, что Кандидат2 хотя и имеет больший общий суммарный балл по сравнению с Кандидатом1 (61 против 52), но к данной должности подходит меньше, т. к. имеет высокие знания и навыки не там, где нужно (знания технологии или финансов), а по нужным параметрам (навыки ведения переговоров и презентаций) как раз сильно отстает. Кандидат1 напротив, по всем необходимым к данной должности компетенциям укладывается в требования очень неплохо. Если немного «подтянуть» его по презентациям и переговорам, что легко можно сделать отправив его на соответствующие тренинги, то он идеально впишется в эту вакансию.

Для вычисления итогового численного значения «попадания в должность» можно использовать следующую формулу (для ячейки C12):

=СУММ(ЕСЛИ(C2:C10<$B$2:$B$10;C2:C10-$B$2:$B$10;0))/СУММ($B$2:$B$10)+1

Обратите внимание на то, что это формула массива, т. е. она должна вводиться с использованием не клавиши Enter в конце, как обычно, а с помощью сочетания клавиш Ctrl+Shift+Enter. Формулы массива отличаются от обычных формул Excel и позволяют работать сразу с целыми массивами данных. В строке формул они отображаются в фигурных скобках (но ставить их с клавиатуры нельзя). Данная формула массива вычисляет отклонение качеств кандидата от требований вакансии и представляет это в виде доли, подразумевая за 100% идеальное совпадение по всем требованиям. Причем перебор навыков, т. е. ситуация, когда кандидат превосходит требования — не учитывается и не дает ему преимуществ.

Все описанные примеры можно скачать в виде файла samples.xlsx

Николай Павлов