Как настроить и применять тепловые карты в Excel для быстрого анализа HR-данных

Тепловая карта (Heat Map) в Excel — это способ визуализации числовых данных, при котором фон ячеек окрашивается в соответствии с цветовой легендой. Обычно, чем больше число, тем интенсивнее или темнее цвет ячейки; чем меньше число, тем бледнее заливка.

Использование цветовой шкалы сокращает время на визуальное сравнение числовых значений в таблице, так как позволяет быстро выявить и оценить:

• минимальное и максимальное значения в массиве данных,
• характер распределения данных (например, сосредоточены ли значения в одной области или распределены равномерно),
• закономерности (наличие или отсутствие взаимосвязей).

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

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

В статье покажу вариант нетипового оформления тепловой карты в Excel. Он предусматривает комбинацию условного форматирования таблицы (с пользовательскими настройками), гистограммы и линейчатой диаграммы. Для примера использую данные о фактической выработке сотрудников по дням рабочей недели (с понедельника по пятницу).

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

2. Сформировать базовую (типовую) визуализацию данных, чтобы быстрее и легче проанализировать массив. Для этого к числовым данным нужно применить условное форматирование (вкладка «Главная» → «Условное форматирование» → «Цветовые шкалы») и выбрать любой готовый шаблон. Это временный вариант.

3. Настроить пользовательские параметры. Снова выделить диапазон и изменить цветовую шкалу («Главная» → «Условное форматирование» → «Управление правилами» → Выбрать созданное правило → «Изменить правило»).

Основные параметры Excel, которые можно настроить:

1. Выбрать стиль формата — двухцветную шкалу (две "крайности") или трехцветную шкалу (по системе "светофор").
2. Задать цвета для максимального, минимального и среднего значений (если шкала трехцветная). Например, использовать RGB коды цветов из корпоративной палитры.
3. Изменить пороговые значения, чаще всего — формат среднего значения. По умолчанию используется — 50 процентиль (т.е. 50% значений будут меньше этого уровня, другие 50% — выше этого уровня). Величину процентиля можно как изменить, так и заменить на фиксированное значение (абсолютное или относительное) или формулу.

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

1. Цветовая легенда — это шкала, которая расшифровывает, какое числовое значение соответствует цвету или оттенку на карте. Обычно значение цвета интуитивно понятно. Например, зеленый цвет означает "хорошо", красный — "плохо"; светлый оттенок — минимальное значение, темный оттенок — максимальное.

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

То есть, если максимальное значение производительности труда маркируется на карте зеленым цветом, то максимальное значение дебиторской задолженности — красным цветом (при использовании системы "светофор"). Цветовая легенда необходима для однозначной интерпретации палитры, используемой на тепловой карте.

2. Диаграммы — это средства наглядного представления числовых значений. Гистограмма иллюстрирует величину значения через высоту столбцов. Линейчатая диаграмма делает то же самое, но с помощью горизонтальных полос.

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

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

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

Так например, из анализа приведенной тепловой карты следует:

• выработка работников в течение недели распределяется неравномерно: ее минимальное значение зафиксировано в среду, максимальное — в пятницу,
• установлена взаимосвязь между днем рабочей недели и размером выработки (общая тенденция для всех сотрудников),
• 47% сотрудников (7 из 15) имеют значение выработки ниже среднего значения.

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

• организация труда — корректировка графиков и нагрузки;
• расчёт численности — на основе фактической производительности труда;
• оценка результативности и эффективности персонала — формирование рейтингов и KPI.