Лайфхак для тех, кто строит отчеты по стажу и возрасту в динамике (используя 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 будет показывать максимальное количество.

Далее необходимо импортировать таблицу в Power Pivot. Для этого в Excel выделим созданную таблицу, перейдем на вкладку Power Pivot и выберем пункт "Добавить в модель данных". ВАЖНО! Связи этой таблицы с другими создавать не нужно.

После импорта сделаем несколько настроек нашей таблицы:

1. Скроем из пользовательского интерфейса столбцы №, Стаж MIN и MAX. Для этого откройте таблицу в Power Pivot, выделите необходимый столбец и нажмите "Скрыть из набора клиентских средств"
2. Настроим сортировку Групп стажа для корректного вывода в отчет. Нам необходимо, чтобы названия групп стажа выводились в отчете в том же порядке, что и в исходной таблице. Для этого у нас создан столбец №. Выделите столбец Группа стажа в Power Pivot, в ленте выберите пункт "Сортировка по столбцам". Далее в блоке "По" выберите наш столбец "№".

Теперь перейдем непосредственно к созданию формул для расчета.

1. Базовая формула расчета численности:

HC (базовый):=sum(Base[Headcount])

2. Формула расчета численности для каждой даты календаря (о том, как создать календарь я писал в предыдущей статье)

HC на каждую отдельную дату :=

CALCULATE (

    [HC (базовый)];

    FILTER (

        'Base';

        'Base'[Начало периода] <= MAX ( 'Calendar'[Дата] )

            && 'Base'[Окончание периода] >= MAX ( 'Calendar'[Дата] )

    )

)

В связи с тем, что в моем отчете из HR-системы отражаются все изменения за период, по одному работнику может быть несколько строк. У каждой из этих строк есть "Начало периода" и "Окончание периода". Между этими интервалами для одного работника пересечений быть не может. Таким образом для расчета численности на дату календаря необходимо определить только те строки по работникам, интервал которых пересекается с датой календаря. Эту операцию у нас выполняет фрагмент, выделенный желтым. Использование формулы MAX необходимо для того, чтобы из календаря получить единственное скалярное значение для сравнения с нашим интервалом. Вместо MAX можно воспользоваться любой другой формулой DAX (например, MIN), возвращающей скалярное значение из массива.

3. Формула для расчета средней списочной численности

ССЧ вспом :=

AVERAGEX (

    ADDCOLUMNS ( 'Calendar'; "ССЧ"; [HC на каждую отдельную дату] );

    [ССЧ]

)

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

4. Следущая вспомогательная формула будет рассчитывать скользящую среднюю списочную численность

ССЧ скользящяя за год :=

VAR posled_data =

    MAX ( 'Base'[Окончание периода] )

RETURN

    IF (

        MAX ( 'Calendar'[Дата] ) <= posled_data;

        CALCULATE (

            [ССЧ вспом];

            DATESBETWEEN (

                'Calendar'[Дата];

                NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Calendar'[Дата] ) ) );

                MIN ( LASTDATE ( 'Calendar'[Дата] ); posled_data )

            );

            ALL ( 'Base'[Отношение компании к увольнению] )

        )

    )

Эта формула более комплексная. Как вы можете заметить, в начале формулы использовано служебное слово VAR. Оно говорит движку DAX, что после VAR до знака "=" будет задано имя переменной posled_data, вычисляемой по формуле после знака "=". Далее эта переменная будет использована в формуле. Сама формула идет после служебного слова RETURN.

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

Более подробно об использовании переменных:

1. Переменные в DAX (англ., автор Альберто Феррари);
2. Использование переменных в DAX (англ., автор Мэтт Аллингтон)
3. Руководство по языку DAX (англ., Microsoft)
4. СОЗДАНИЕ ПЕРЕМЕННЫХ В DAX (рус., автор Будуев Антон)

Итак, что же делает эта формула?

• В связи с тем, что в моем наборе данных отчетный период завершается 30.06.2016, а календарь создан до конца 2016 года, я проверяю не превышает ли дата календаря окончание отчетного периода в блоке, выделенным розовым.
• Для расчета скользящей средней за год необходимо для каждой даты календаря определить все даты между ней и датой, отстоящей от нее на 1 год назад. Таким образом мы получим список дат за последний год до текущей даты в календаре формулой, выделенной зеленым.
• Для списка дат за скользящий год рассчитаем среднесписочную численность по формуле из пункта 3).

5. Создадим вспомогательную меру, которая будет рассчитывать стаж сотрудника на любую заданную дату. Ниже формула для ее расчета и по тексту формулы комментарии/пояснения после двойного тире "--".

Стаж_месяцев :=

VAR DataFinal =

    IF (

        MAX ( 'Calendar'[Дата] ) > EOMONTH ( MIN ( 'Base'[Дата увольнения] ); 0 );

        EOMONTH ( MIN ( 'Base'[Дата увольнения] ); 0 );

        MAX ( 'Calendar'[Дата] )

    )

RETURN

    IF (

        OR ( MAX ( 'Calendar'[Дата] ) < MAX ( 'Base'[Дата приема] );

 --если человек еще не принят на работу, то стаж=0

                 MAX ( 'Calendar'[Дата] ) > EOMONTH ( MIN ( 'Base'[Дата увольнения] ); 0 )

 --если человек уже уволен, то стаж=0

        );

        0;

        ( YEAR ( DataFinal ) - YEAR ( MAX ( 'Base'[Дата приема] ) ) ) * 12 

-- количество месяцев по разнице лет

            + MONTH ( DataFinal )  - MONTH ( MAX ( 'Base'[Дата приема] ) ) 

--разница месяцев

            + IF ( DAY ( DataFinal ) >= DAY ( MAX ( 'Base'[Дата приема] ) ); 0; -1 )

--если день в месяце приема больше даты в отчете, то отнимем 1 месяц

            + IF (

                AND ( DAY ( DataFinal ) = DAY ( MAX ( 'Calendar'[Начало месяца] ) );

                           DAY ( MAX ( 'Base'[Дата приема] ) ) = DAY ( MAX ( 'Calendar'[Конец месяца] ) )

                ); 1; 0 ) --если отчетная дата приходится на начало месяца и человека приняли в последний день месяца, то добавим 1 месяц к стажу

    )

Теперь нам остается создать итоговую меру.

Объясню, как работает эта мера (цифры рядом с формулой показывает последовательность вычислений):

1. проверим условие
2. используем ли мы в отчете группировку по стажу?
3. если используем, то рассчитаем вспомогательную меру [ССЧ вспом] для следующей таблицы табельных в разрезе групп стажа (вычисляется в пунктах 5-8)
5. Определим группу стажа, в которую попадает стаж конкретного работника на любую дату.

6-8) Пункты 6-8 работают совместно:

8 - получим список

7 - уникальных табельных номеров (т.к. в нашем примере есть повторения)

6 - для которых фильтр, вычисляющий куда относится стаж работника получает хотя-бы 1 строку

4. Если в отчете не используется группировка по стажу, то просто рассчитаем среднесписочную численность с помощью [ССЧ вспом].

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

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

В качестве дополнения покажу, как на базе ранее созданных мер с небольшой доработкой посчитать коэффициент постоянства (скользящая за год ССЧ принятых более 1 года назад/скользящая за год ССЧ) в Power pivot.

Вспомогательная мера подсчитает Headcount для работников, принятых более 365 дней назад (это условие желтым)

ССЧ скользящяя за год(отработали год) :=

VAR HC_na_datu =

    CALCULATE (

        [HC (базовый)];

        FILTER (

            'Base';

            'Base'[Начало периода] <= MAX ( 'Calendar'[Дата] )

                && 'Base'[Окончание периода] >= MAX ( 'Calendar'[Дата] )

                && 'Base'[Дата приема]

                    <= MAX ( 'Calendar'[Дата] ) - 365

        )

    )

VAR SSCH_vspom_otrab_god =

    AVERAGEX ( ADDCOLUMNS ( 'Calendar'; "ССЧ"; HC_na_datu ); [ССЧ] )

RETURN

    CALCULATE (

        SSCH_vspom_otrab_god;

        DATESBETWEEN (

            'Calendar'[Дата];

            NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Calendar'[Дата] ) ) );

            LASTDATE ( 'Calendar'[Дата] )

        );

        ALL ( 'Base'[Отношение компании к увольнению] )

    )

Итоговая формула будет следующей:

Коэффициент постоянства :=

VAR posled_data =

    MAX ( 'Base'[Окончание периода] )

RETURN

    IF (

        MAX ( 'Calendar'[Дата] ) <= posled_data;

        IF (

            [ССЧ скользящая за год по группам стажа и возрасту] = 0;

            0;

            [ССЧ скользящая за год по группам стажа и возрасту(отработали год)]

                / [ССЧ скользящая за год по группам стажа и возрасту]

        )

    )

С помощью описанных выше мер на языке DAX можно получить наглядный график изменения коэффициента постоянства.

Спасибо за ваше внимание. Буду признателен за обратную связь - что понятно/не понятно/интересно/. Также прошу поделиться вашим опытом использования Power Pivot для расчетов HR метрик и возможно предсказательной HR аналитики.

Ссылка на Итоговый файл.