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

Шаг 5

Последние штрихи. Меня радует, что боковой зазор столбцов по умолчанию стоит 50 %. Но именно для водопадной диаграммы нужно усилить визуальную метафору и сделать так, чтобы «ступеньки» почти примыкали друг другу. Для этого сокращаем зазор до 10 %.

Расположение подписей данных не случайно: для плюсовых категорий они над цветными столбцами, а для минусовых – под ним. Это я менять не буду, только настрою разрядность, уберу знаки после запятой и немного увеличу размер шрифта. Теперь чистовик готов!

Так же как и воронка, каскадная диаграмма не строится на сводных таблицах в Excel. Чтобы его обмануть, опять делаем ссылки на соседние ячейки.

Как сделать водопад в старых версиях Excel

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

Шаг 1

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

Шаг 2

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

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

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

Шаг 3

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

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

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

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

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

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

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