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

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

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

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

Менять начальное значение могут, например, продажи отдельных товаров, приход и расход средств, движение показателей в рамках определенного периода и так далее. Например, компания 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%. За счет этого итоговая сумма в плюсе, хоть и незначительном.

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

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

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

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

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

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

Эта визуализация появилась лишь в Microsoft Office 2016 года и, конечно, есть во всех более новых версиях. Тем не менее ее можно сделать и в старом Excel: потребуется чуть больше времени, но визуально результат будет неотличим.

Рассмотрим на примере, из каких этапов состоит построение каскадной диаграммы в Excel 2013 или более старых версиях Эксель.

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

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

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

Столбец «Сумма» не должен оставаться пустым. В ячейке под показателем валовой прибыли вводим формулу: =C3-D4+E3. Чтобы в вашем случае все получилось верно, проследите, чтобы порядок ячеек в формуле соответствовал примеру.

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

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

Данные из столбца «Сумма» Excel автоматически закрасил синим цветом, отрицательные значения («Минус») — оранжевым, а положительные («Плюс») — серым.

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

● Кликнем по любому синему столбику.
● Убедимся, что все они выделились.
● В меню «Формат» → «Заливка фигуры» выберем «Нет заливки».

Затем, вручную выделив только первый и последний столбцы (кликнув на каждый из них дважды), тем же путем вернем им первоначальную заливку.

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

«Водопад» готов. Теперь нужно отредактировать его и навести красоту.

Для большей наглядности сделаем столбцы пошире, чтобы визуально визуализация напоминала мост, связывающий начальное и конечное значения. Одно из ее названий — бридж-диаграмма (от англ. bridge — мост), так как ее смысл и внешний вид соответствуют идее соединения двух крайних опор.
Для этого воспользуемся меню «Формат ряда данных» и уменьшим боковой зазор до 20%.

Теперь поработаем с текстовыми элементами. Напишем заголовок и сделаем его крупным, удалим легенду. Увеличим шрифт подписей на оси X: важно, чтобы они оставались горизонтальными, так как читать под углом неудобно. Добавим метки данных и вручную удалим те, которые относятся к незалитым частям. Увеличим шрифт меток.

В итоге у нас получилась классическая водопадная диаграмма, наглядно показывающая все этапы движения денег. Можно не просто сравнить начальное и конечное значения, но и увидеть, какие факторы привели к результату: что сыграло в плюс, а что в минус.

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

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

Правда, как и любая автоматически созданная визуализация, она требует редактирования и чистки.

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

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

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

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

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

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

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

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

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

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

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

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

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

Откроется страница с множеством дополнительных визуализаций от партнеров. Через поиск в верхнем правом углу находите график Simple Waterfall и устанавливаете его. В перечне визуализаций появится новая иконка (рис. 2).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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