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

Если у вас Excel 2013 или все еще 2010, вы тоже можете построить такой же красивый водопад с помощью «подушки». Скажу даже больше – у вас будет больше свободы действий. Например, можно сделать горизонтальный водопад на основе линейчатой диаграммы и вместить длинные названия категорий.

Шаг 1

В таблице создаем столбцы «Минус» и «Плюс». В первый перенесем из столбца «Сумма» показатели с отрицательными значениями (без знака минус!), а во второй – с положительными. В «Сумме» оставляем только наш исходный показатель – размер валовой прибыли. На основе получившейся таблицы и будем строить диаграмму.

Шаг 2

Теперь будем наполнять опустевший столбец «Сумма» – для этого понадобятся формулы. Сначала заполним вторую ячейку столбца, под показателем валовой прибыли.

Вводим формулу: отмечаем предыдущую ячейку этого столбца → отнимаем значение из столбца «Минус» в активной строке → прибавляем предыдущую ячейку столбца «Плюс».

В моем случае формула будет выглядеть так: =C3-D4+E3. После этого останется только протянуть формулу на весь столбец «Сумма». Если вы все правильно посчитали, то два последних значения (налог на прибыль и чистая прибыль) у вас будут одинаковыми. В результате получаем вот такую таблицу.

Шаг 3

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

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

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

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

В Excel появляются новые диаграммы: тримап, воронка, водопад, но все они оказываются сырыми, не доведенными до ума, плюс не работают на сводных таблицах. Я думаю, что это тактика Microsoft, чтобы для продвинутых задач пользовались уже полноценным инструментом для дашбордов – Power BI.

Я и сам разделяю такой подход, поэтому остановился на двух популярных диаграммах. Хотя есть умельцы, которые строят и более сложные потоковые диаграммы, графы связей. Но это требует более серьезной подготовки данных, формул расчета и даже макросов. Из новых необычных в Excel есть еще диаграмма «Солнечные лучи», но она лишена всякого смысла, так что я не стал ее рассматривать.

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

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

Резюме

В новых версиях Excel появляются продвинутые диаграммы, которые отображают комбинации видов анализа для конкретных бизнес-кейсов. Они не работают на сводных таблицах, но это легко решается проставлением ссылок на соседние ячейки.

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

Воронка = динамика + структура

1. Добавьте в таблицу новый столбец и рассчитайте в нем значения «подушки» по заданной формуле.

2. Постройте линейчатую диаграмму с накоплением и поставьте ряд «Подушка» первым.

3. Уберите цветную заливку с этого ряда, чтобы он стал прозрачным.

4. Оформите диаграмму по чек-листу.

Водопад = динамика + рейтинг

1. Добавьте в таблицу дополнительные столбцы с отрицательными и положительными значениями.

2. Заполните опустевший основной столбец новыми значениями «подушки» по заданной формуле.

3. Постройте гистограмму с накоплением. Ряд с «подушкой» сделайте прозрачным, только верните цвет первому и последнему столбцам – это «опоры моста».

4. Для факторов отклонений задайте цвета по правилу светофора.

5. Оформите внешний вид диаграммы по чек-листу.

Как повысить корпоративную культуру работы с данными