Заставьте данные говорить. Как сделать бизнес-дашборд в Excel. Руководство по визуализации данных - Алексей Сергеевич Колоколов

Давайте разберемся, с помощью каких трюков можно построить эти визуализации, а также какие у них есть плюсы и минусы.

Комбинированный график для динамики

У всех обновленных визуализаций есть общая идея: «План» представляет собой отметку, которую пересекает «Факт». Чтобы настроить это на графике, выделяем линию с «Фактом» и в контекстном меню выбираем «Изменить тип диаграммы для ряда». Если у вас нет такого пункта, значит, вы нажали на область диаграммы, а не на сам ряд (линию или столбец).

В открывшемся окне для каждого ряда данных мы можем выбрать отдельный тип диаграммы. В нашем случае для ряда «Факт» выбираем гистограмму с группировкой.

Вот и весь трюк. Преимущество столбца в том, что подписи данных можно расположить в центре и они не будут накладываться на «План», как это было раньше.

Остается адаптировать диаграмму по чек-листу из главы 3.4. Сначала сокращаем боковой зазор со 150 до 50 %. Потом увеличиваем толщину линии «План» до 3 пунктов, чтобы она не потерялась на фоне мощных столбцов.

В этом примере можно было оставить и линии, все достаточно наглядно. Но часто нужно показать 3 ряда: план, факт и факт прошлого года. И здесь такой прием хорошо выручает. Только следите за акцентами: прошлый год не должен отвлекать внимание от текущего.

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

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

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

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

Настраиваем внешний вид по чек-листу – добавляем метки, убираем оси. Но тут возникает проблема: линия среднего чека снова прилипает к оси X, хотя ряд построен по вспомогательной оси. Это очередная особенность Excel, из-за которой просто удалить шкалу с диаграммы мы не можем. Зато можем ее спрятать: сделаем оси невидимыми, перекрасив их в цвет фона – у нас это белый. Они по-прежнему есть, но уже не отвлекают внимания от данных.

Все эти сложности и лайфхаки нам нужны только для построения таких диаграмм в Excel. В специализированных программных продуктах, таких как Power BI, такие изощрения не потребуются.

Буллет-чарт для рейтинга план-факта

«Диаграмма-пуля» используется для отображения план-фактных значений. Визуальная идея заключается в том, что полоса с фактом как бы выстреливает, вылетает из ствола с планом. Пример слева больше похож на такое ружье, а справа – уже минималистичная версия, где план представляет собой черту, планку, которой достигает факт. Плюс тут использовано условное форматирование: если цель не достигнута, то красный; если выполнена, то зеленый.

Оба варианта корректны, но я рекомендую второй. На нем для столбца «Факт» больше места, а «План» компактно отображен чертой. Но это примеры готовой диаграммы Bullet Chart, построенной в Power BI. В Excel нам придется поколдовать, и результат не будет на 100 % соответствовать эталону.

Горизонтальный буллет-чарт с совмещением рядов

Для построения горизонтального буллет-чарта нам необходимо для начала изменить параметр для ряда данных «Факт», установить перекрытие ряда 100 %. Все подписи данных смешались, и поэтому удаляем их со столбца «План».

Затем переводим «Факт» на вспомогательную ось и выставляем для перекрытия ряда 0 %, а для бокового зазора – 125 %. После настройки разрядности оси для вспомогательного ряда удаляем ее с диаграммы.

Затем выбираем ряд данных «План». Меняем ему цвет на нейтральный серый, чтобы акцент остался только на «Факте», и выставляем следующие настройки: перекрытие ряда – 0 %, а боковой зазор – 25 %.

Все настройки буллет-чарта завершены, и он выглядит почти как эталонный из Power BI. Аналогично можете настроить и вертикальный буллет-чарт, если он вам понадобится для работы.

Вертикальный буллет-чарт с чертой

Второй вид эталонного буллет-чарта также можно построить в Excel.

Шаг 1

Изменить тип диаграммы, выбрав комбинированную, а затем для «Плана» выбрать тип «График с маркерами».

Шаг 2

Настроить ряд данных «План»: в разделе «Линия» выставляем значение «Нет линий», а в разделе «Маркер» в подразделе «Параметры маркера» выбираем вид «Встроенный», тип – самая большая черта и выставляем размер 45.

Шаг 3

Из-за перекрытия маркером значений факта необходимо перенести подписи данных для этого ряда и выбрать положение «В центре».

Но все получилось не идеально: для малых значений у «Логистики и сервиса» места меткам данных все равно не хватает.

К сожалению, такой тип буллет-чарта можно построить в Excel только для вертикальных столбцов, но не для горизонтальных. Потому что маркер – это атрибут графика, а график может отображать только динамику. А динамика может быть только на горизонтальной временной шкале. Здесь, как ни странно, создатели Excel поступили правильно и даже не дали возможности строить вертикальные графики.

Диаграммы для сложной структуры

В корпоративной отчетности структура редко принимает какие-то сложные формы. Но когда это случается, примерно 8 из 10 отчетов доносят эту информацию и непонятно и неправильно. В частности, это касается двух случаев:

● когда нужно показать структуру сразу по двум параметрам;

● когда структуру нужно представить в комбинации с другим видом анализа.

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

Ошибка: кольцевая для двух рядов данных

Традиционные диаграммы для структуры – круговая и тримап – могут отобразить только один ряд данных. Хотя на кольцевую