Share |

понедельник, 11 июня 2018 г.

Расчет показателей текучести персонала в 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'[Дата] ) )11 )
            && 'Calendar'[Дата] <= MAX ( 'Calendar'[Дата] )
    )
)

Эта считает ССЧ за аналогичный период прошлого года. При ее использовании удобно на одних временных отрезках показывать данные текущего года и прошлого в сравнении.
0211.ССП с начала года(Прошлый год) :=
CALCULATE (
    [01.Среднесписочная численность(ССП)];
    FILTER (
        ALL ( 'Calendar' );
        'Calendar'[Дата]
            > DATE ( YEAR ( MAX ( 'Calendar'[Дата] ) ) - 111 )
            && 'Calendar'[Дата]
                <= DATE ( YEAR ( MAX ( 'Calendar'[Дата] ) ) - 1MONTH ( 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'[Дата] ) )11 )
            && 'Calendar'[Дата] <= MAX ( 'Calendar'[Дата] )
    )
)

Аналогичный показатель за прошлый год.
064.Уволенных с начала года ВСЕГО (Прошлый год) :=
CALCULATE (
    [03.Количество уволенных ВСЕГО];
    FILTER (
        ALL ( 'Calendar' );
        'Calendar'[Дата]
            > DATE ( YEAR ( MAX ( 'Calendar'[Дата] ) ) - 111 )
            && 'Calendar'[Дата]
                <= DATE ( YEAR ( MAX ( 'Calendar'[Дата] ) ) - 1MONTH ( MAX ( 'Calendar'[Дата] ) )DAY ( MAX ( 'Calendar'[Дата] ) ) )
    )
)

Так рассчитываем количество принятых с начала года.
065.Принятых с начала года (Прошлый год) :=
CALCULATE (
    [032.Количество принятых];
    FILTER (
        ALL ( 'Calendar' );
        'Calendar'[Дата]
            > DATE ( YEAR ( MAX ( 'Calendar'[Дата] ) ) - 111 )
            && 'Calendar'[Дата]
                <= DATE ( YEAR ( MAX ( 'Calendar'[Дата] ) ) - 1MONTH ( 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'[Дата] ) )11 )
            && 'Calendar'[Дата] <= MAX ( 'Calendar'[Дата] )
    )
)

Вариант предыдущей формулы для расчета аналогичного показателя за аналогичный период прошлого года.
0711.Уволенных с начала года (СОЖАЛЕЕМ Прошлый год) :=
CALCULATE (
    [04.Количество уволенных (СОЖАЛЕЕМ)];
    FILTER (
        ALL ( 'Calendar' );
        'Calendar'[Дата]
            > DATE ( YEAR ( MAX ( 'Calendar'[Дата] ) ) - 111 )
            && 'Calendar'[Дата]
                <= DATE ( YEAR ( MAX ( 'Calendar'[Дата] ) ) - 1MONTH ( 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'[Дата] ) )11 )
            && '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.

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

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





17 комментариев:

  1. Круто. А почему сразу Time Intelligence не использовать? Хотя дело вкуса)

    ОтветитьУдалить
    Ответы
    1. Люда, здравствуйте.
      Использование функций Time Intelligence в DAX бесспорно более эффективно с точки зрения производительности, но для этого необходимо сделать связь между таблицей с данными и таблицей дат. В моем примере каждая запись в таблице данных представляет собой не одну дату, а диапазон. Какие изменения вы бы внесли в модель и формулу среднесписочной численности и скользящей средней, для использования Time Intelligence ?

      Удалить
    2. Связью можно управлять и судя по вашим формулам вы знаете как, раз умело управляете контекстом фильтров. Но и без связи они прекрасно работают, проверено. Я могу прислать файл с toy-example. Реальные данные, как вы понимаете, останутся у меня)))

      Удалить
    3. Буду признателен за ваш файл-пример.

      Удалить
    4. Я возьму ваши же данные.

      Удалить
    5. Денис, на какой адрес направить пример?

      Удалить
    6. а что, отдельным постом никак?

      Удалить
    7. это твое решение, и тебе решать

      Удалить
  2. Люда, если не затруднит, прошу выложить на файлообменник. Например этот http://rgho.st

    ОтветитьУдалить
  3. Эдуард, как Вы можете помешать? :-)
    Жаль только одно - подавляющее большинство читателей блога скорее всего не имеют доступ к модели данных, где видны формулы, так как не многие готовы доплачивать за "расширенную" версию Microsoft Office.

    ОтветитьУдалить
    Ответы
    1. Люда, отличный пример, спасибо. Понравилась формула с использованием VAR, существенно сокращает код. Разница со скользящим годом думаю +-1 день не сильно влияет на среднюю для целей сравнения.

      Удалить
    2. Тем читателям, которые все же захотят разобраться с моделями Power Pivot и возможностью сэкономить свое время, можно попробовать бесплатный Power BI Desktop (https://powerbi.microsoft.com/ru-ru/desktop/). Не столь привычно в части отражения таблиц, но прекрасно для визуализации и есть возможность импортировать настроенную в excel модель данных.

      Удалить
    3. Сравнил скользящую среднюю численность по вашей формуле и моей. Минус 12 месяцев все ж дает погрешность, которую можно избежать немного иной конструкцией формулы.
      ССЧ скользящее за год 2:=CALCULATE (
      [ССЧ вспом];
      DATESBETWEEN (
      'Calendar'[Дата];
      NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Calendar'[Дата] ) ) );
      LASTDATE ( 'Calendar'[Дата] )
      );
      ALL ( 'Base'[Отношение компании к увольнению] )
      )

      Удалить

Популярные сообщения

п