Лайфхак для тех, кто строит отчеты по стажу и возрасту в динамике (используя Power Pivot для Excel)

Всем привет, дорогие читатели.

С вами снова Денис Костин и сегодня я покажу полезный лайфхак, который пригодится всем тем, кто строит отчеты по персоналу в разрезе возраста или стажа в динамике за период. Несколько раз наблюдал, как коллега испытывает мучения, пытаясь собрать подобную аналитику из нашей не очень дружелюбной HR-системы. Для этого ему приходилось осуществлять следующие шаги:

1. сформировать настроенный отчет по персоналу на конец каждого месяца необходимого периода;
2. В каждом отчете за месяц добавить столбцы с расчетом стажа и возраста;
3. На отдельном листе рассчитать необходимый показатель (например, списочную численность) для каждой группы стажа и возраста за каждый период.

Как думаете, какие сложности приносит такой подход?

На мой взгляд как минимум следующие:

1. Необходимость формировать большое количество отдельных отчетов из HR системы;
2. Большое количество формул СУММЕСЛИ и СУММЕСЛИМН делает файл очень медленным;
3. При необходимости построения отчета в новом разрезе (например, пола или грейда) дополнительные затраты времени на создание новых формул.
4. Ошибки при расчете, т.к. не учитывается динамика в течение месяца.

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

В результате вы сможете на основе единственного отчета из HR-системы сформировать с помощью Power Pivot подобный расчет:

Для создания примера будем использовать файл из предыдущей статьи 

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

Стаж является изменяющейся во времени величиной, поэтому на каждую дату в отчете он должен быть рассчитан отдельно.

Решение будет состоять всего из 6 шагов:

1. Создание в Excel таблицы с группировками стажа
2. Импорт таблицы в Power Pivot
3. Создание вспомогательных мер для расчета среднесписочной численности
4. Создание вспомогательной меры для расчета стажа
5. Создание итоговой меры
6. Создание сводной таблицы с использованием нашей итоговой меры

Для начала создадим таблицу в Excel следующего содержания:

№	Группа стажа	Стаж MIN	Стаж MAX
1	до 3 мес	0	3
2	03 мес-1 год	3	12
3	1-2 лет	12	24
4	2-3 лет	24	36
5	3-5 лет	36	60
6	5-10 лет	60	120
7	более 10 лет	120	999

В столбце Стаж MIN указано минимальное количество полных месяцев, при которых стаж будет относиться к конкретной группе. Аналогично Стаж MAX будет показывать максимальное количество.

Воронка подбора. Три способа построения

ПРИГЛАШАЮ ОТСЛЕЖИВАТЬ НАС В ТЕЛЕГРАМ

Мало кто в HR не знает, что такое воронка подбора. Это хороший визуальный инструмент для первичного определения проблем в процессе подбора. Она позволяет понять, как протекает процесс, состоящий из нескольких стадий. Если ваш вопрос в определении самой «узкой» части в этом процессе, то воронка как раз то, что надо. В сочетании с метриками, позволяющими еще и время оценить на каждом этапе, у вас появляется реально рабочий инструмент для контроля.

Мы сейчас настраиваем на Power BI такой дашборд. Он может выглядеть так (данные фейковые 😊 )

Но и без Power BI вы можете построить воронку подбора, используя обычный excel, и excel «усиленный» надстройкой Power Pivot.

Этот пост как раз про эти три способа построения воронки подбора, которые может применить каждый в своих рабочих или учебных целях.

В качестве примера использован датасет «данные» из 4840 наблюдений за 2018 год, содержащий следующие переменные:

1. Дата открытия вакансии;
2. Дата закрытия вакансии;
3. Количество кандидатов;
4. Количество телефонных интервью;
5. Количество рассмотренных кандидатов;
6. Количество проверок СБ;
7. Количество предложений кандидатам;
8. Количество приёмов;
9. Структурное подразделение.

Мы предлагаем пройти решение вместе с нами, для этого скачать файл с учебными данными «Пример построения воронки подбора.xlsx» (в файле доступны решения для Способов 1 и 2 с расчётом всех формул.) Формат файла оказывает влияние на последующий расчет, и где-то такой формат удобен, а где-то придется писать много формул. Формат можно конечно поменять, но оставим все как есть, чтобы почувствовать разницу 😊. 

Реализация

ПРИГЛАШАЮ ОТСЛЕЖИВАТЬ НАС В ТЕЛЕГРАМ

Способ 1. MS Excel

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

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

В начале логика: мы хотим вывести на экран некий текстовый символ определенное количество раз пропорционально значению в строке, для этого количество событий на каждом этапе воронки надо умножить на некий множитель. Нам понадобится создать этот множитель - вспомогательную величину, которая потребуется для визуализации результатов. Максимальное количество символов (в нашем случае черточек), которое помещается в ячейку excel в этом примере выбрали равным 60, но оно может быть любым числом. Далее считаем по следующей формуле:

=60/ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Сумма по полю Количество кандидатов";$A$2)

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

=ПОВТОР("|";ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Сумма по полю Количество кандидатов";$A$2)*$B$11)

Чтобы результат в строке казался непрерывным выберите шрифт «Britanic Bold» и необходимый размер. Ваш итоговый результат представлен на рисунке далее.

ПРИГЛАШАЮ ОТСЛЕЖИВАТЬ НАС В ТЕЛЕГРАМ

Способ 2. Power Pivot

Excel позволил нам быстро построить воронку подбора кандидатов, не прибегая к сложным расчётам, но у этого подхода есть существенный недостаток – вашу картинку может «сломать» другой пользователь или вы сами захотите расширить исходную сводную таблицу, добавив новый столбец. Вариантов много – исход один – надо всегда проверять, не затерлись ли формулы, не уехали ли ссылки. Можно ли сразу все реализовать в единой сводной таблице и больше не переживать об этом? Как «запихнуть» текст именно как текст в область значений сводной таблицы и реализовать ровно тот же способ, но забыть навсегда о прописывании отдельных формул где-то в стороне от сводной таблицы? Чтобы наша черточка осталась черточкой, а не пересчиталась в число?

Это можно, но использовать придется Power Pivot – надстройку для Excel. Power Pivot подключается отдельно в параметрах Excel, в надстройках COM и доступен  пользователем профессиональных версий Microsoft Office (2010, 2013, 2016). У надстройки много преимуществ, и одно из них (не главное, но очень приятное) – вы можете написать формулы, результатом которых будет именно текст (не числовое значение), и выводя эту формулу (меру языком power pivot) в область значений вы увидите именно текст. Попробуйте кто не знал и создайте меру:

Test for text:="Hellow word"

А теперь выведете ее в сводную таблицу, например, как тут:

Вы видите куда ее «определили» в сводной таблице и что мы получили на выходе. Мы не увидели ни одного числа, мы увидели именно текст. Именно эта особенность работы с power pivot и даст возможность построить более «устойчивую» визуалку для воронки подбора. Логика визуализации та же самая, что и в excel, просто теперь это становится элементом сводной таблицы.

Что делаем:

1.Загружаем исходные данные.

2. Создаем таблицу дат. Очень сильно пригодится, если вы захотите гибко фильтровать отчет по временным периодам. Он может быть создан рядом способов, в нашем примере был использован код в Power Query (ещё одна надстройка Excel, позволяющая осуществлять наиболее эффективную подготовку данных для последующей работы).

Пример создания календаря в Power Query (на английском) можно посмотреть здесь

1. Пишем формулы для расчета мер на языке DAX.

Усложним условия – если вакансия закрылась в январе или осталась активна на 31.01, то выбирая в фильтре январь (или любой другой период), мы должны увидеть в воронке все события по этой вакансии, даже если они случились в прошлом году. То есть дата самого события (интервью, собеседование) здесь уже уходит на задний план. Но тут у кого как поставлена задача, вариантов много.

Задачу позволяют решить следующие формулы для каждого этапа воронки (приведена только первая, но они все есть в прикрепленном файле). Формулы можно переписать гораздо более оптимально, но в таком виде более понятна логика самого расчета. 

Общее количество кандидатов (кол-во):=

--для закрытых вакансий

CALCULATE(

SUM('данные'[Количество кандидатов]);

FILTER('данные';'данные'[close_date]>=MIN('calendare'[RepDate])&&'данные'[close_date]<=MAX('calendare'[RepDate])))

+

--для открытых вакансий

CALCULATE(

SUM('данные'[Количество кандидатов]);

FILTER('данные';ISBLANK('данные'[close_date])&&'данные'[start_date]<=MAX('calendare'[RepDate])))

….

Нам вновь понадобится множитель, для этого создадим меру

Множитель:=60/[Общее количество кандидатов (кол-во)]

Как и в Способе 1 визуализация будет осуществлена с помощью символа «|», приведём первые две формулы:

1. Общее количество кандидатов:

=REPT("|";[Множитель]*[Общее количество кандидатов (кол-во)])

2. Телефонные интервью:

=REPT("|";[Телефонные интервью (кол-во)]*[Множитель])

Нам также хотелось бы знать конверсию кандидатов от общего числа кандидатов на каждом этапе, пример формулы:

Конверсия в рассмотрено кандидатов:=[Рассмотрено кандидатов (кол-во)]/[Общее количество кандидатов (кол-во)])

и конверсию от предыдущего этапа к следующему, пример формулы:

 Конверсия с этапа тел. интервью в рассмотрен. кандидатов:=[Рассмотрено кандидатов (кол-во)]/[Телефонные интервью (кол-во)])

Визуализируем, подключим срезы, временные шкалы на вкладке «Вставка» и получим результат, представленный на рисунке ниже.

ПРИГЛАШАЮ ОТСЛЕЖИВАТЬ НАС В ТЕЛЕГРАМ

Способ 3. Power BI

Шаги идентичны способу 2. Нам потребуется загрузить данные в Power BI и создать календарь. В том случае, если вы выполнили Способ 2, то вы можете открыть ваш «xlsx» файл через Power BI и получить готовую модель со всеми таблицами и мерами, останется только сверстать дашборд. Если нет, то понадобится написать формулы для расчёта метрик по каждому этапу и это будут единственные меры, что нам потребуются. Power BI и Power Pivot использует один и тот же язык DAX, соответственно формулы будут аналогичными.

В Power BI есть готовая диаграмма в виде воронки, в которую мы передаём наши меры, и никаких дополнительных упражнений не требуется.

Ещё раз посмотрим на полученный результат:

Заключение

Несмотря на то, что задачу построения воронки подбора персонала нельзя отнести к глубокой аналитической работе, тем не менее это повседневная необходимость для HR-руководителей, рекрутеров и специалистов ответственных за подготовку отчётов в компании. Возможность самостоятельного построения такой воронки существенно облегчает работу для них, а также может стать первым шагом на пути решения уже более серьёзных аналитических задач, к примеру, поиску ответа на вопрос: почему на данном этапе воронка приобретает такой вид, а на следующем этапе иной?

Нами были представлены три способа построения воронки подбора персонала, которые может использовать каждый HR, в зависимости от удобства и предпочтений. Может кому-то это будет полезно.

ПРИГЛАШАЮ ОТСЛЕЖИВАТЬ НАС В ТЕЛЕГРАМ

Расчет показателей текучести персонала в Power Pivot (Excel)

Всем, привет, дорогие друзья аналитики и не только.

Часто ли вам приходилось тратить уйму времени на переделку отчета, когда руководитель просил показать его еще "вот в таком разрезе" ? Используя стандартные средства Excel на переделку может уйти много времени, но это совершенно не обязательно.

Хочу поделиться с вами тем, как можно использовать инструменты бизнес-аналитики в Excel - Power pivot и Power Query для анализа текучести персонала. Использование этих мощных инструментов позволит вам существенно сэкономить время, затрачиваемое на подготовку аналитики в любых разрезах, имеющихся в исходных данных.

Ознакомиться в деталях с тем, что такое Power Pivot, функциональный язык DAX, Power Query и скриптовый язык M вы можете самостоятельно. Тут важно то, что, если вы хотите повысить свою продуктивность как аналитика, освоение этих инструментов поможет вам продвинуться в этом направлении. Ниже несколько ссылок на полезные источники:

1. http://baguzin.ru/wp/obrabotka-dannyh-s-pomoshhyu-modulya-powerpivot/ - введение в Power pivot
2. https://support.office.com/ru-ru/article/Справка-по-power-pivot-241aac41-92e3-4e46-ae58-2f2cd7dbcf4f - справка Power Pivot от Microsoft
3. https://msdn.microsoft.com/ru-ru/query-bi/dax/data-analysis-expressions-dax-reference - руководство по языку DAX.
4. https://habr.com/post/271019/ - введение в Power Query, краткий обзор возможностей
5. https://msdn.microsoft.com/query-bi/m/power-query-m-reference - полное описание языка M, используемого в Power Query (англ.)
6. https://needfordata.ru/blog/category/power-query - адаптированные переводы статей гуру Power Query Криса Вебба (Chris Webb).

Итак, в моем распоряжении имеется следующий набор данных https://cloud.mail.ru/public/Ffce/Gev6hBqWE. В файле отражены кадровые изменения (должность, подразделение, оклад, руководитель, город, филиал, центр затрат, количество занимаемых ставок и др.) по работникам за период с 2013 по 2016 годы. По одному работнику в таблице может быть несколько строк, каждая из которых имеет свой период начала и окончания действия. Периоды для одного работника не пересекаются. Также в крайних правых столбцах таблицы указаны причины увольнения и отношение компании к увольнению (сожалеем или не сожалеем). Отношение компании указано в связи с тем, что не все увольнения по собственному желанию являются нежелательной текучестью.

На базе этих данных я покажу как рассчитать среднесписочную численность, количество принятых и уволенных, Коэффициент оборота по выбытию, Коэффициент текучести, Скользящую среднюю текучесть за год. Итоговый файл со всеми формулами вы можете найти по ссылке https://cloud.mail.ru/public/7zD9/ysPgHSYcg.

Работу над моделью данных, в которой будут рассчитываться эти коэффициенты я построил в 2 шага:

1. Подготовительный - создание календаря для Power pivot с помощью Power Query.
2. Основной - создание формул, для расчета показателей текучести в Power Pivot.

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

Первым делом создадим новый лист Параметры, добавим на него таблицу Параметры, состоящую из наименования параметра и значения.

Начало и окончание периода я взял как начало первого года самого раннего события имеющегося набора данных и окончание последнего года. Во избежание проблем рекомендую при использовании календарей всегда захватывать год целиком. Последний параметр "Язык" указывает для какой локализации создавать календарь. Все вы знаете, что в разных странах могут использоваться различные форматы записи дат. Более подробно можете ознакомиться здесь (https://ru.wikipedia.org/wiki/Календарная_дата)

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

(ParameterName as text) =>

 let

     ParamSource = Excel.CurrentWorkbook(){[Name="Параметры"]}[Content],

     ParamRow = Table.SelectRows(ParamSource, each ([Параметр]=ParameterName)),

     Value=

if Table.IsEmpty(ParamRow)=true

then null

else Record.Field(ParamRow{0},"Значение")

in

    Value

После этого наживаете Готово. Добавленная функция сохранится с именем Запрос1. Переименуем во что-то более понятное. В моем случае я заменил название на fnGetParameter

Эта функция необходима нам для того, чтобы получать из таблицы в Excel требуемых параметры.

Следующим шагом создадим наш календарь. Для этого, не выходя из окна Power Query создадим еще один пустой запрос. 
Нажмите правой кнопкой мыши в левой области интерфейса и выберите пункт как на скрине.

В открывшемся окне снова удалим содержимое и далее вставим текст, скопированный из файла Календарь PQ с параметрами задаваемыми из Excel.txt (https://cloud.mail.ru/public/vz7f/5Hygn66Q7). Созданный запрос переименуем в Calendar.

Наш календарь готов, и мы можем переходить к его загрузке в Power Pivot. Для этого в интерфейсе Power Query нажмите "Закрыть и загрузить-Закрыть и загрузить в". В следующем окне выберите "Создать только подключение" и в нижней части "Добавить эти данные в модель данных"

Не буду вдаваться в подробности описания кода Power Query. Если возникнут вопросы, в части PQ  прошу задавать в комментариях.

На следующем шаге мы создадим непосредственно расчетные формулы в Power Pivot. Сначала перейдите на лист База, в меню Excel перейдите на вкладку Power Pivot и нажмите добавить в модель данных, после этого нажмите кнопку управление - откроется интерфейс Power pivot

На листе Calendar добавьте столбец УникальныйНомерДня. Выберите первую из ячеек столбца и введите формулу:

= COUNTROWS (

    FILTER (

        ALL ( 'Calendar' );

        'Calendar'[Дата] <= EARLIER ( 'Calendar'[Дата] )

            && NOT (

                MONTH ( 'Calendar'[Дата] ) = 2

                    && DAY ( 'Calendar'[Дата] ) = 29

            )

    )

)

Этот столбец нам впоследствии пригодится для расчета скользящей средней за год.

Далее нам необходимо указать PP где у нас указаны даты (непрерывный список дней). Для этого перейдите на вкладку Конструктор -Пометить как таблицу дат. В открывшемся окне выберите наименование столбца, в котором наш список дат. В нашем случае столбец так и называется Дата.

Для тех, кто знаком с Power Pivot скажу, что мы не будем задавать связь между таблицами Calendar и Base в связи с расчетом скользящей средней и ряда других формул.