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

вызываем контекстное меню и выбираем «Установить в качестве итога». Аналогично делаем для начального столбца.

Шаг 3

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

Для этого нажимаем на легенду один раз, затем еще раз – на элемент «Увеличение» и выбираем ему зеленый цвет. Такая функция стабильно работает только в версиях 2019+, в более старых это окрашивает фон легенды в зеленый.

Шаг 4

Оформляем диаграмму по чек-листу из главы 3.4: убираем лишние ось Y, линии сетки, легенду. Чтобы подписи категорий уместились по горизонтали, придется уменьшить шрифт и растянуть диаграмму по ширине. Если для текста недостаточно места, Excel будет принудительно поворачивать его и размещать под углом. Так что если у вас длинные названия факторов отклонений, то они все равно не уместятся, и с этим ничего не поделать.

Шаг 5

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

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

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

Как сделать водопад в старых версиях 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. Постройте гистограмму с накоплением. Ряд с «подушкой» сделайте прозрачным, только верните цвет первому и последнему столбцам – это «опоры моста».