Дашборды и визуализация данных

В этом уроке ты узнаешь

Считать основные показатели KPI по правильным формулам
Выбирать диаграмму по типу вопроса
Добавлять спарклайны и условное форматирование со значками
Организовывать дашборд по схеме «данные → расчёты → панель»

Каждый понедельник утром у директора есть пять минут: какой филиал выполняет план, какой отстаёт, растут продажи или падают? Ему нужен не отчёт на 40 листов, а дашборд на один экран. Хороший дашборд отвечает на три вопроса: «Что происходит?», «Как мы идём относительно плана?», «Где нужно вмешаться?»

Математика KPI

Achievement = A / T; Variance = A − T; Growth = (A − P) / PAchievement = A / T; Variance = A − T; Growth = (A − P) / P

где:

Aфактический результат (например, продажи, ₼)
Tплан (цель), ₼
Pрезультат предыдущего периода, ₼

Выполнение плана и рост — отношения (процентный формат), отклонение — в манатах. Знак важен: для затрат отрицательное отклонение — хорошо, для продаж — плохо.

Интерактив

Загрузка симуляции…

План, факт и прошлый год по филиалам (в манатах): выполнение, отклонение, рост год к году и статус.

CAGR = (Vₙ / V₀)^(1/n) − 1CAGR = (Vₙ / V₀)^(1/n) − 1

где:

V₀, Vₙначальное и конечное значение, ₼
nчисло лет (шагов между периодами)

Среднегодовой сложный темп роста получаем, решив Vₙ = V₀ · (1 + g)ⁿ относительно g. Excel: =(B6/B2)^(1/4)-1 или =RRI(4,B2,B6).

CAGR и ловушка «среднего процента»

1) Выручка компании в 2021 году — 80 000 ₼, в 2025-м — 117 128 ₼. Каков среднегодовой рост? 2) Продажи другой компании за год выросли на 50%, а на следующий год упали на 50%. Верно ли говорить «средний рост 0%»?

Показать решение

1) С 2021 по 2025 год n = 4 шага.
CAGR = (117 128 / 80 000)^(1/4) − 1 = 1,4641^0,25 − 1 = 10%. Проверка: 80 000 · 1,1⁴ = 117 128.
2) Нет! 100 → 150 → 75: за два года −25%.
CAGR = (75 / 100)^(1/2) − 1 = √0,75 − 1 ≈ −13,4% в год.
Складывать проценты и делить (арифметическое среднее) при сложном росте нельзя — нужно геометрическое среднее.

Диаграмма под вопрос

Вопрос	Диаграмма	Путь в Excel
Сравнить категории	линейчатая, отсортированная	`Insert › Charts › Bar`
Изменение во времени	график	`Insert › Charts › Line`
Факт и план	комбинированная: столбцы + линия	`Insert › Charts › Combo`
Пошагово объяснить отклонение	каскадная (waterfall)	`Insert › Charts › Waterfall`
Распределение	гистограмма, «ящик с усами»	`Insert › Charts › Insert Statistic Chart`
Связь двух показателей	точечная	`Insert › Charts › Scatter`
Доли целого (≤ 5)	нормированная линейчатая или круговая	`Insert › Charts › Pie`

1
Добавь спарклайны
Выдели продажи по месяцам (например, C2:N5) › Insert › Sparklines › Line › Location Range: O2:O5. В каждой ячейке появится маленькая линия тренда.
2
Выдели точки
Sparkline › Show: отметь High Point и Low Point — лучший и худший месяцы будут выделены цветом.
3
Уравняй оси
Sparkline › Group › Axis › Same for All Sparklines (для минимума и максимума). Иначе у каждого спарклайна свой масштаб, и маленький филиал выглядит как крупный.
4
Значки для статуса
Выдели столбец выполнения (E2:E5) › Home › Styles › Conditional Formatting › Icon Sets › 3 Traffic Lights › Manage Rules › Edit Rule: Type = Number, зелёный >= 100, жёлтый >= 90. Всё, что ниже, получит красный сигнал, и директор увидит проблему с первого взгляда.

Компоновка дашборда

Три слоя: Data (сырые данные в таблицах Excel), Calc (сводные таблицы, формулы KPI), Dashboard (только диаграммы, карточки, срезы). Никогда не вбивай числа на панель вручную.
Главное — слева вверху. Глаз читает экран по линии «Z»: сверху 3–5 карточек KPI, под ними тренд, затем детали.
Мало цветов, и все со смыслом: один основной цвет, красный — только для проблем. Скрой сетку: View › Show › Gridlines.
Один экран, пять секунд: если главное не читается за 5 секунд, упрощай. Убери 3D-эффекты, тени и лишние легенды.

Посчитай карточки KPI

По таблице вверху дашборда будут четыре карточки: общие продажи, выполнение плана, рост год к году и «отстающие филиалы» (выполнение < 90%). Найди значения и формулы.

Показать решение

1) Общие продажи: =C6 → 111 200 ₼.
2) Выполнение: =C6/B6 → 111 200 / 112 000 ≈ 99,3% (среднее процентов филиалов дало бы 97,75% — неверная мера).
3) Рост год к году: =C6/D6-1 → 111 200 / 103 900 − 1 ≈ +7,0%.
4) Отстающие: =COUNTIF(E2:E5,"<90") → 1 (Сумгайыт, 86%).
На карточках — крупный шрифт и под ним короткий комментарий: «99,3% плана, не хватает 800 ₼».

Диаграмма по выделенным данным на этом листеAlt+F1

Открыть панель формата выделенного элемента диаграммыCtrl+1

Повторить последнее действие форматирования (например, тот же цвет следующей карточке)F4

Главное

Выполнение = факт / план, отклонение = факт − план, рост = (факт − прошлый) / прошлый.
Общий процент — отношение итогов, а не среднее процентов; для многолетнего роста используй CAGR.
Выбирай диаграмму по вопросу: сравнение — линейчатая, время — график, план/факт — комбинированная, объяснение отклонения — каскадная.
В наборах значков выбирай Type = Number; у спарклайнов уравнивай оси.
Дашборд: Data → Calc → Dashboard, главное слева вверху, мало цветов, один экран.

Проверь себя

Вопросов: 10. Каждый правильный ответ приносит XP.

1 / 10

План 30 000 ₼, факт 27 900 ₼. Каковы выполнение и отклонение?