Каскадная диаграмма «Водопад»: как построить график в Excel

Чтобы делать правильные выводы на основе данных, не всегда достаточно просто сравнить показатели «было → стало». В некоторых бизнес-процессах важно понимать, что именно повлияло на изменение начального значения. Для этого существует каскадная диаграмма. Она показывает, из-за чего показатель рос, а из-за чего снижался. И какие факторы сильнее всего сказались на конечном результате.
Допустим, в марте ваша компания заработала 25 миллионов. Что будет по итогам апреля? Что принесет ей больше прибыли, а что станет самой серьезной статьей расходов? Такая визуализация дает наглядные ответы на все эти вопросы. Одного взгляда достаточно, чтобы не только увидеть результат работы в апреле, но и понять, почему он именно такой.
Показатели

Источник фото: Tableau Public

Как читать и использовать график

Эту визуализацию называют по-разному: каскадная диаграмма, «Водопад», Waterfall chart, бридж, «мост». Она состоит из вертикальных столбцов: первый и последний показывают начальное и конечное значения, а промежуточные — факторы, которые повлияли на результат. Для большей наглядности их принято окрашивать разными цветами.
Closing Amount

Источник: Qlik Community

Такую визуализацию чаще всего используют для нескольких целей:

● показать динамику процесса: например, движение денежных средств, поток инвестиций и так далее;
● сравнить целевое значение с реальным (план-факт анализ) и увидеть точки, в которых что-то пошло не так;
● показать, как и из-за чего исходный показатель приходит к итоговому (факторный анализ).

Менять начальное значение могут, например, продажи отдельных товаров, приход и расход средств, движение показателей в рамках определенного периода и так далее. Например, компания Apple с помощью водопадной диаграммы показывает, за счет чего изменился объем продаж, какие продуктовые линейки повлияли на снижение выручки, а какие, наоборот, были более востребованными в конкретный период времени. В результате анализа становится понятно, над чем стоит более усердно поработать, чтобы сменить минус на плюс.
Значения
По статистике доход от продаж за второй квартал 2020 года в сравнении с тем же периодом 2019-го практически не изменился — 58,015 млрд долларов против 58,313 млрд соответственно. Рост составил всего лишь 0,5%.

Если взять данные конкретно по каждой продуктовой линейке, можно увидеть, что уровень продаж снизился на 6,7 % у iPhone, на 10,3 % — у iPad, на 2,9% — у Mac. Зато прочие устройства, такие как Apple TV, Apple Watch, Beats, iPod и фирменные аксессуары продавались лучше на 22,5%, а сервисы — на 16,6%. За счет этого итоговая сумма в плюсе, хоть и незначительном.
Решение задач бизнеса с помощью аналитики
Кейсы и лайфхаки от практика
Excel, Power BI, PowerPoint

Обучение на реальных бизнес-задачах

Курсы по визуализации и аналитике данных

Что такое факторный анализ и каковы его задачи

Его цель — выявить причины (факторы), которые влияют на фактические результаты промышленной, хозяйственной и финансовой деятельности. Для визуализации этого вида анализа и применяют водопадный график.
Взаимосвязь показателей обычно представляют в форме математического расчета. Численно определяют, какие значения и на сколько провоцируют рост, а какие — падение.
Чаще всего этот способ используют для анализа выполнения производственных планов, контроля инвестиций и финансовых потоков.

Рассмотрим еще один простой пример факторного анализа. Допустим, есть некое значение плановой прибыли — 140 000 рублей. На конец года фактический доход составил 171 000.
Факторы
Первый и последний столбцы — это план и факт. Промежуточные — факторы, влияющие на итог. Зеленым цветом отмечены показатели роста, красным — снижения.

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

Простые «каскады» можно создать в обычном Excel или использовать надстройку Think-cell. Для продвинутых отчётов и больших объемов данных гораздо удобнее работать с платформой Power BI.

Как построить каскадную диаграмму «Водопад» в Excel 2013 и более ранних версиях Эксель

Эта визуализация появилась лишь в Microsoft Office 2016 года и, конечно, есть во всех более новых версиях. Тем не менее ее можно сделать и в старом Excel: потребуется чуть больше времени, но визуально результат будет неотличим.
С помощью некоторых манипуляций в нужный нам график можно превратить обычную гистограмму. В первую очередь для этого придется вручную подкорректировать таблицу с данными: нужно быть очень внимательным, чтобы не допустить ошибок.
Рассмотрим на примере, из каких этапов состоит построение каскадной диаграммы в Excel 2013 или более старых версиях Эксель.

Корректируем данные в таблице

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

Для этого создаем дополнительные столбцы и вручную переносим туда данные из «Суммы»: отрицательные значения — в «Минус» (но без знака!), положительные — в «Плюс». Из «Суммы» данные по изменениям удаляем, сохранив показатель исходной валовой прибыли. Получаем таблицу, на основе которой мы и будем строить график.
Финансы2

Добавляем формулы

Столбец «Сумма» не должен оставаться пустым. В ячейке под показателем валовой прибыли вводим формулу: =C3-D4+E3. Чтобы в вашем случае все получилось верно, проследите, чтобы порядок ячеек в формуле соответствовал примеру.
Финансы3
То есть у нас это выглядит так. Ставим курсор во вторую ячейку столбца «Сумма», под показателем валовой прибыли. Вводим формулу: отмечаем предыдущую ячейку этого столбца → отнимаем значение из «Минус» в активной строке → прибавляем предыдущую ячейку столбца «Плюс».
Протягиваем формулу на весь столбец и получаем следующее:
Финансы4

Строим гистограмму с накоплением

Именно из нее мы потом сделаем водопадную диаграмму. Выделяем таблицу и выбираем «Вставка» → «Гистограмма с накоплением». Получаем график, как на картинке 2:
Данные из столбца «Сумма» Excel автоматически закрасил синим цветом, отрицательные значения («Минус») — оранжевым, а положительные («Плюс») — серым.

Преобразуем в диаграмму «Водопад»

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

● Кликнем по любому синему столбику.
● Убедимся, что все они выделились.
● В меню «Формат» → «Заливка фигуры» выберем «Нет заливки».
График2
Затем, вручную выделив только первый и последний столбцы (кликнув на каждый из них дважды), тем же путем вернем им первоначальную заливку.
График3
Воспользуемся правилом светофора и сделаем положительные значения зелеными, а отрицательные – красными. Для этого кликнем по любому оранжевому столбцу и в меню «Формат» → «Заливка фигуры» выберем красный. Затем таким же образом перекрасим серые столбцы в зеленый.
График4
«Водопад» готов. Теперь нужно отредактировать его и навести красоту.

Для большей наглядности сделаем столбцы пошире, чтобы визуально визуализация напоминала мост, связывающий начальное и конечное значения. Одно из ее названий — бридж-диаграмма (от англ. bridge — мост), так как ее смысл и внешний вид соответствуют идее соединения двух крайних опор.
Для этого воспользуемся меню «Формат ряда данных» и уменьшим боковой зазор до 20%.
График5
Теперь поработаем с текстовыми элементами. Напишем заголовок и сделаем его крупным, удалим легенду. Увеличим шрифт подписей на оси X: важно, чтобы они оставались горизонтальными, так как читать под углом неудобно. Добавим метки данных и вручную удалим те, которые относятся к незалитым частям. Увеличим шрифт меток.
В итоге у нас получилась классическая водопадная диаграмма, наглядно показывающая все этапы движения денег. Можно не просто сравнить начальное и конечное значения, но и увидеть, какие факторы привели к результату: что сыграло в плюс, а что в минус.
Вы можете скачать файл для старых версий Excel и повторить все действия самостоятельно. В нем есть таблица с данными (уже с дополнительными столбцами), изначально построенная гистограмма, а также готовый элемент в нужном виде.
Важный момент: при переходе по ссылке вы увидите некорректную визуализацию (это проделки Гугл диска). В меню «Файл» нажмите «Скачать» и выберите Microsoft Excel (XLSX): работайте именно со скачанным документом, в нем все будет отображаться корректно.
интерактив на базе сводных таблиц в Excel

грамотная визуализация любых данных

наглядные отчеты для вашего руководства

Интерактивные отчеты, которые понравятся директору

Дашборд в Excel с нуля
ОНЛАЙН-КУРС

Как сделать каскадную диаграмму «Водопад» в Excel 2016 и более поздних версиях

Здесь уже всё гораздо проще. Выделяем нужный диапазон данных в таблице, на основе которых необходимо построить график. На главной панели выбираем значок «Каскадная». Получим вполне наглядный результат (рис.2).
Правда, как и любая автоматически созданная визуализация, она требует редактирования и чистки.

Настраиваем и оформляем

Повторяем те же шаги, которые мы делали в старых версиях Excel. Настраиваем заголовок, убираем легенду, меняем цвет столбцов.

Важно: здесь нам придется менять цвет вручную у каждого столбика, так как иначе автоматически перекрасятся все. Откорректируйте подписи на осях, убедитесь, что они расположены горизонтально, а не под углом.
Анализ
Вы можете скачать таблицу с данными и образцом диаграммы в Excel 2016 или более новых версиях.
Важный момент: при переходе по ссылке вы увидите некорректную визуализацию (это проделки Гугл диска). В меню «Файл» нажмите «Скачать» и выберите Microsoft Excel (XLSX): работайте именно со скачанным документом.

В видео — наглядная пошаговая инструкция для построения каскада. Это урок из моего старого курса. В новой обучающей программе он тоже есть, только в лучшем качестве и с учетом современных возможностей программы.
К сожалению, Microsoft все равно не добился того, чтобы вопрос «Как сделать график «Водопад» в Эксель?» решался мгновенно и без усилий.

Для построения сложных сводных графиков всё-таки гораздо удобнее использовать дополнительные инструменты.
интерактив на базе сводных таблиц в Excel

грамотная визуализация любых данных

наглядные отчеты для вашего руководства

Интерактивные отчеты, которые понравятся директору

Дашборд в Excel с нуля
ОНЛАЙН-КУРС

Что такое надстройка Think-cell и чем она помогает

Многие компании для создания каскадных диаграмм используют Think-cell. Это инструмент, который позволяет без особых усилий строить двухмерные графики в PowerPoint прямо на странице с презентацией из данных Excel. Профессионально, максимально точно и в соответствии со стандартами компании. При этом делать это можно в 3-5 раз быстрее, чем в Эксель.

Think-cell имеет простую настройку параметров. Визуализации синхронизируются с данными из Excel. Подписи и суммарные значения распознаются автоматически. Фигуры и текстовые блоки легко связываются между собой.
План

Как построить каскадную диаграмму в Power BI

Но как же быть с обширными массивами данных, сводными таблицами и необходимостью быстро и безболезненно формировать отчеты? Для продвинутых специалистов и компаний, которые ценят свое время, в Microsoft создали аналитическую платформу Power BI.

Это комплекс программ и сервисов, которые позволяют быстро выгружать информацию из любых источников, приводить её к единой модели данных и на их основе производить необходимые расчеты, строить графики и создавать отчеты. Подробно об этом рассказано в статье «Как работает интерактивный дашборд».
Фильтры
Построить диаграмму Waterfall в Power BI быстрее, чем в Excel. Данные из таблицы необходимо загрузить на платформу. Подробно о том, как это делать, рассказано в отдельной статье. Далее выбираем элемент на панели «Визуализации» и определяем некоторые значения. Добавляем в область «Распределение» нужный фактор — и детализация готова.

У новичков могут возникать сложности при подготовке и хранении данных. На курсе мы подробно разбираем все нюансы. Я рассказываю, как уменьшить трудозатраты на подготовку и как лучше поступать в конкретной ситуации — строить диаграмму на платформе Power BI или использовать дополнительные интерфейсы.
Профессиональное владение Power BI

Интерактивные отчеты за 3 часа

Быстрый и удобный анализ данных

Красивые и понятные аналитические отчеты за 1 месяц

Дашборд в Power BI с нуля до профи
ОНЛАЙН-КУРС

Simple Waterfall в Power BI – простой водопад

Это интерфейс без лишних заморочек. Чтобы установить этот (или другой) не входящий в базовый функционал тип диаграммы, нужно в меню «Визуализации» кликнуть по иконке с тремя точками и выбрать «Еще визуальные элементы».
Иконки
Откроется страница с множеством дополнительных визуализаций от партнеров. Через поиск в верхнем правом углу находите график Simple Waterfall и устанавливаете его. В перечне визуализаций появится новая иконка (рис. 2).
Теперь нужно добавить этот визуальный элемент «Простой водопад» на холст. Укажите категории для анализа, определите цвет положительных и отрицательных факторов, обозначьте итоговые и промежуточные значения, добавьте всплывающие подсказки и контекстное меню. Как обычно, не забудьте уделить внимание шрифтам, подписям на осях и меткам данных, убрать все лишнее.
Детализация

Zebra BI для Power BI

Zebra BI — самая мощная технология визуализации данных для построения каскадной диаграммы. Интерфейс, который расширяет функциональные возможности и позволяет создавать идеальные финансовые отчеты.

Например, в Power BI не очень удобно сравнивать данные из разных категорий. С Zebra BI это можно сделать за считанные минуты. К существующему элементу просто добавить еще один параметр.

Автоматически строится серия визуальных элементов с одинаковым масштабом и осями, которые легко сравнивать между собой. Одним кликом можно переключаться между разбивкой по дням, месяцам, кварталам.
Итоги
Часто несколько значений заметно превышают остальные. При построении каскада эти столбцы будут выбиваться из ряда и искажать разницу. В Zebra ВI доступна функция «разрыв столбца» — всё выравнивается по одному нажатию.

Чтобы понять, как построить график «Водопад» в Excel (Эксель) и в Power BI, и сравнить, где процесс удобнее, попробуйте сделать это самостоятельно в обеих программах.

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

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

В совершенстве овладеть Power BI я помогаю на моем онлайн-курсе. Всего за 2 месяца (а может, даже за 1) вы освоите навыки создания интерактивных дашбордов, научитесь делать понятные и красивые аналитические презентации. Узнаете нюансы каждой из диаграмм и сможете сформировать отчет любой сложности за 15 минут до начала совещания.
Профессиональное владение Power BI

Интерактивные отчеты за 3 часа

Быстрый и удобный анализ данных

Красивые и понятные аналитические отчеты за 1 месяц

Дашборд в Power BI с нуля до профи
ОНЛАЙН-КУРС

Плюсы диаграммы

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

● Простота исполнения. Построение диаграммы «Водопад» в Excel старых версий требует определенных усилий, но в более новых или в специализированных программах график создается за несколько кликов, как мы убедились выше.

● Наглядность и понятность. Благодаря цветовой кодировке сразу ясно, какие факторы оказывают положительное, а какие отрицательное влияние на конечный результат.

● Возможность сравнивать целевые и фактические показатели (план/факт) и одновременно видеть факторы, повлиявшие на их разницу.

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

Минусы диаграммы

● Возможность анализировать только определенный формат данных: нам нужно иметь исходное и конечное значение (или целевое/фактическое), а также промежуточные, приводящие к итоговому.

● Не подходит для более детализированного анализа, так как нельзя фильтровать или выводить дополнительные данные.

● Необходимость доп. интерфейсов или надстроек. Возможности большинства программ по умолчанию позволяют создавать только самые простые варианты. Я показал, как строить каскадную диаграмму в Excel (Эксель) как в старых, так и новых версиях, и рассказал, какие дополнительные интерфейсы Power BI нужны для создания более сложных и разнообразных графиков.

Подведем итоги

● Мы убедились, что «Каскад» (он же «Водопад», Waterfall, bridge или «мост») — это удобный и наглядный способ отобразить процесс изменения какого-то показателя с учетом влияющих на него моментов.

● Выяснили, как сделать акцент на причинах изменения показателя.

● Разобрались, что такое факторный анализ и как его отображать с помощью этого типа визуализации.

● Рассмотрели, как сделать диаграмму «Водопад» в Excel (в разных версиях Эксель) и в Power BI, а также как создать план-факт анализ и использовать дополнительные интерфейсы.

Я думаю, что пик популярности «Водопада» еще впереди: сейчас его применяют не все, но удобство и понятность, а также простота реализации помогут ему занять достойное место среди диаграмм-лидеров.
Вам понравилась статья?
Читайте также
Подпишись на рассылку и получи в подарок «Каталог лучших отраслевых дашбордов»!

Хочешь получать актуальные статьи о визуализации данных?