Расчет показателей текучести персонала в 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 в связи с расчетом скользящей средней и ряда других формул.

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

01.Среднесписочная численность(ССП) :=

CALCULATE (

    AVERAGEX (

        ADDCOLUMNS (

            VALUES ( 'Calendar'[Дата] );

            "HC"; SUMX (

                FILTER (

                    'Base';

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

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

                );

                'Base'[Headcount]

            )

        );

        [HC]

    );

    ALL ( Base[Причина увольнения]; Base[Отношение компании к увольнению] )

)

Объясню, что же она делает. На каждый день рассчитывается количество занятых ставок работающими и агрегируется на уровне периода большего чем день - месяца, квартала, года и др. В конце формулы я добавил ALL ( Base[Причина увольнения]; Base[Отношение компании к увольнению] ). В сводной таблице это условие не будет учитывать выбранные в фильтры и разрезы причины увольнения и отношение компании к увольнению.

Если вы начинаете писать формулы сами, они чаще выглядят как непрерывный неформатированный текст. Для того, чтобы выделить логические части на разных уровнях можно воспользоваться ресурсом http://www.daxformatter.com/ В верхней части необходимо выбрать используемые в вашей системе разделители в формулах Power Pivot. Для русских систем это точка с запятой. После этого можно вставлять свою формулу и нажимать Format.

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

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

02.ССП за скользящий год :=

CALCULATE (

    [01.Среднесписочная численность(ССП)];

    FILTER (

        ALL ( 'Calendar' );

        'Calendar'[УникальныйНомерДня]

            > MAX ( 'Calendar'[УникальныйНомерДня] ) - 365

            && 'Calendar'[УникальныйНомерДня] <= MAX ( 'Calendar'[УникальныйНомерДня] )

    )

)

Дополнительно использую несколько вариаций этой формулы.

Эта считает накопительную среднюю от начала года

021.ССП с начала года :=

CALCULATE (

    [01.Среднесписочная численность(ССП)];

    FILTER (

        ALL ( 'Calendar' );

        'Calendar'[Дата] > DATE ( YEAR ( MAX ( 'Calendar'[Дата] ) ); 1; 1 )

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

    )

)

Эта считает ССЧ за аналогичный период прошлого года. При ее использовании удобно на одних временных отрезках показывать данные текущего года и прошлого в сравнении.

0211.ССП с начала года(Прошлый год) :=

CALCULATE (

    [01.Среднесписочная численность(ССП)];

    FILTER (

        ALL ( 'Calendar' );

        'Calendar'[Дата]

            > DATE ( YEAR ( MAX ( 'Calendar'[Дата] ) ) - 1; 1; 1 )

            && 'Calendar'[Дата]

                <= DATE ( YEAR ( MAX ( 'Calendar'[Дата] ) ) - 1; MONTH ( MAX ( 'Calendar'[Дата] ) ); DAY ( MAX ( 'Calendar'[Дата] ) ) )

    )

)

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

03.Количество уволенных ВСЕГО :=

SUMX (

    ADDCOLUMNS (

        VALUES ( 'Calendar'[Дата] );

        "Уволенных"; SUMX (

            FILTER (

                'Base';

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

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

                    && 'Base'[Дата увольнения] = 'Calendar'[Дата]

            );

            'Base'[Headcount]

        )

    );

    [Уволенных]

)

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

032.Количество принятых:=

SUMX (

    ADDCOLUMNS (

        VALUES ( 'Calendar'[Дата] );

        "Принятых"; SUMX (

            FILTER (

                'Base';

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

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

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

            );

            'Base'[Headcount]

        )

    );

    [Принятых]

)

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

04.Количество уволенных (СОЖАЛЕЕМ) :=

SUMX (

    ADDCOLUMNS (

        VALUES ( 'Calendar'[Дата] );

        "Уволенных"; SUMX (

            FILTER (

                'Base';

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

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

                    && 'Base'[Дата увольнения] = 'Calendar'[Дата]

                    && 'Base'[Отношение компании к увольнению] = "сожалеем"

            );

            'Base'[Headcount]

        )

    );

    [Уволенных]

)

Так считаем уволенных, потеря которых для компании не важна.

05.Количество уволенных (НЕ СОЖАЛЕЕМ) :=

SUMX (

    ADDCOLUMNS (

        VALUES ( 'Calendar'[Дата] );

        "Уволенных"; SUMX (

            FILTER (

                'Base';

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

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

                    && 'Base'[Дата увольнения] = 'Calendar'[Дата]

                    && 'Base'[Отношение компании к увольнению] = "не сожалеем"

            );

            'Base'[Headcount]

        )

    );

    [Уволенных]

)

Так считаем общее количество уволенных за скользящий год.

06.Уволенных за скользящий год ВСЕГО :=

CALCULATE (

    [03.Количество уволенных ВСЕГО];

    FILTER (

        ALL ( 'Calendar' );

        'Calendar'[УникальныйНомерДня]

            > MAX ( 'Calendar'[УникальныйНомерДня] ) - 365

            && 'Calendar'[УникальныйНомерДня] <= MAX ( 'Calendar'[УникальныйНомерДня] )

    )

)

Далее вариация предыдущей формулы для расчета уволенных с начала года.

061.Уволенных с начала года ВСЕГО :=

CALCULATE (

    [03.Количество уволенных ВСЕГО];

    FILTER (

        ALL ( 'Calendar' );

        'Calendar'[Дата] > DATE ( YEAR ( MAX ( 'Calendar'[Дата] ) ); 1; 1 )

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

    )

)

Аналогичный показатель за прошлый год.

064.Уволенных с начала года ВСЕГО (Прошлый год) :=

CALCULATE (

    [03.Количество уволенных ВСЕГО];

    FILTER (

        ALL ( 'Calendar' );

        'Calendar'[Дата]

            > DATE ( YEAR ( MAX ( 'Calendar'[Дата] ) ) - 1; 1; 1 )

            && 'Calendar'[Дата]

                <= DATE ( YEAR ( MAX ( 'Calendar'[Дата] ) ) - 1; MONTH ( MAX ( 'Calendar'[Дата] ) ); DAY ( MAX ( 'Calendar'[Дата] ) ) )

    )

)

Так рассчитываем количество принятых с начала года.

065.Принятых с начала года (Прошлый год) :=

CALCULATE (

    [032.Количество принятых];

    FILTER (

        ALL ( 'Calendar' );

        'Calendar'[Дата]

            > DATE ( YEAR ( MAX ( 'Calendar'[Дата] ) ) - 1; 1; 1 )

            && 'Calendar'[Дата]

                <= DATE ( YEAR ( MAX ( 'Calendar'[Дата] ) ) - 1; MONTH ( MAX ( 'Calendar'[Дата] ) ); DAY ( MAX ( 'Calendar'[Дата] ) ) )

    )

)

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

07.Уволенных за скользящий год (СОЖАЛЕЕМ) :=

CALCULATE (

    [04.Количество уволенных (СОЖАЛЕЕМ)];

    FILTER (

        ALL ( 'Calendar' );

        'Calendar'[УникальныйНомерДня]

            > MAX ( 'Calendar'[УникальныйНомерДня] ) - 365

            && 'Calendar'[УникальныйНомерДня] <= MAX ( 'Calendar'[УникальныйНомерДня] )

    )

)

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

071.Уволенных с начала года (СОЖАЛЕЕМ) :=

CALCULATE (

    [04.Количество уволенных (СОЖАЛЕЕМ)];

    FILTER (

        ALL ( 'Calendar' );

        'Calendar'[Дата] > DATE ( YEAR ( MAX ( 'Calendar'[Дата] ) ); 1; 1 )

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

    )

)

Вариант предыдущей формулы для расчета аналогичного показателя за аналогичный период прошлого года.

0711.Уволенных с начала года (СОЖАЛЕЕМ Прошлый год) :=

CALCULATE (

    [04.Количество уволенных (СОЖАЛЕЕМ)];

    FILTER (

        ALL ( 'Calendar' );

        'Calendar'[Дата]

            > DATE ( YEAR ( MAX ( 'Calendar'[Дата] ) ) - 1; 1; 1 )

            && 'Calendar'[Дата]

                <= DATE ( YEAR ( MAX ( 'Calendar'[Дата] ) ) - 1; MONTH ( MAX ( 'Calendar'[Дата] ) ); DAY ( MAX ( 'Calendar'[Дата] ) ) )

    )

)

Далее приведу формулы без пояснений, из наименований понятно, что они считают.

08.Коэффициент оборота по выбытию за скользящий год :=

IF (

    [02.ССП за скользящий год] = 0;

    0;

    [06.Уволенных за скользящий год ВСЕГО] / [02.ССП за скользящий год]

)

081.Коэффициент оборота по выбытию с начала года :=

IF (

    [021.ССП с начала года] = 0;

    0;

    [061.Уволенных с начала года ВСЕГО] / [021.ССП с начала года]

)

09.Текучесть за скользящий год :=

IF (

    [02.ССП за скользящий год] = 0;

    0;

    [07.Уволенных за скользящий год (СОЖАЛЕЕМ)] / [02.ССП за скользящий год]

)

10.Текучесть с начала года :=

IF (

    [021.ССП с начала года] = 0;

    0;

    [071.Уволенных с начала года (СОЖАЛЕЕМ)] / [021.ССП с начала года]

)

101.Текучесть с начала года(Прошлый год) :=

IF (

    [0211.ССП с начала года(Прошлый год)] = 0;

    0;

    [0711.Уволенных с начала года (СОЖАЛЕЕМ Прошлый год)]

        / [0211.ССП с начала года(Прошлый год)]

)

062.Принятых за скользящий год :=

CALCULATE (

    [032.Количество принятых];

    FILTER (

        ALL ( 'Calendar' );

        'Calendar'[УникальныйНомерДня]

            > MAX ( 'Calendar'[УникальныйНомерДня] ) - 365

            && 'Calendar'[УникальныйНомерДня] <= MAX ( 'Calendar'[УникальныйНомерДня] )

    )

)

063.Принятых с начала года:=

CALCULATE (

    [032.Количество принятых];

    FILTER (

        ALL ( 'Calendar' );

        'Calendar'[Дата] > DATE ( YEAR ( MAX ( 'Calendar'[Дата] ) ); 1; 1 )

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

    )

)

082.Коэффициент оборота по приему за скользящий год :=

IF (

    [02.ССП за скользящий год] = 0;

    0;

    [062.Принятых за скользящий год] / [02.ССП за скользящий год]

)

083.Коэффициент оборота по приему с начала года :=

IF (

    [021.ССП с начала года] = 0;

    0;

    [063.Принятых с начала года] / [021.ССП с начала года]

)

11.Коэффициент полного оборота  за скользящий год :=

IF (

    [02.ССП за скользящий год] = 0;

    0;

    ( [06.Уволенных за скользящий год ВСЕГО] + [062.Принятых за скользящий год] )

        / [02.ССП за скользящий год]

)

111.Коэффициент полного оборота с начала года :=

IF (

    [021.ССП с начала года] = 0;

    0;

    ( [061.Уволенных с начала года ВСЕГО] + [063.Принятых с начала года] )

        / [021.ССП с начала года]

)

112.Коэффициент полного оборота с начала года (Прошлый год) :=

IF (

    [0211.ССП с начала года(Прошлый год)] = 0;

    0;

    ( [064.Уволенных с начала года ВСЕГО (Прошлый год)]

        + [065.Принятых с начала года (Прошлый год)] )

        / [0211.ССП с начала года(Прошлый год)]

)

12.Коэффициент замещения за скользящий год :=

IF (

    [06.Уволенных за скользящий год ВСЕГО] = 0;

    0;

    ( [062.Принятых за скользящий год] / [06.Уволенных за скользящий год ВСЕГО] )

)

121.Коэффициент замещения с начала года :=

IF (

    [061.Уволенных с начала года ВСЕГО] = 0;

    0;

    [063.Принятых с начала года] / [061.Уволенных с начала года ВСЕГО]

)

122.Коэффициент замещения с начала года (Прошлый год) :=

IF (

    [064.Уволенных с начала года ВСЕГО (Прошлый год)] = 0;

    0;

    [065.Принятых с начала года (Прошлый год)]

        / [064.Уволенных с начала года ВСЕГО (Прошлый год)]

)

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

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

Расчет метрик покажу с возможностью разбивки на группы.

Спасибо всем за внимание. Буду рад обратной связи и предложениям по улучшению быстродействия формул и добавлению новых показателей.