Как построить диаграмму "водопад" (waterfall)

Все чаще и чаще встречаю в отчетности разных компаний и слышу просьбы от слушателей на тренингах объяснить как строится каскадная диаграмма отклонений - она же "водопад", она же "waterfall", она же "мост", она же "bridge" и т.д. Выглядит она примерно так:

Каскадная диаграмма водопад (waterfall)

Издали действительно похожа на каскад водопадов на горной реке или навесной мост - кто что видит :)

Особенность такой диаграммы том, что:

  • Мы наглядно видим начальное и конечное значение параметра (первый и последний столбцы).
  • Положительные изменения (рост) отображаются одним цветом (обычно зеленым), а отрицательные (спад) - другим (обычно красным).
  • Иногда в диаграмме могут присутствовать ещё и столбцы промежуточных итогов (серые, приземленные на ось Х столбцы).

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

  • Наглядное отображение динамики какого-либо процесса во времени: потока наличности (cash-flow), инвестиций (вкладываем деньги в проект и получаем от него прибыль).
  • Визуализация выполнения плана (крайний левый столбик в диаграмме - факт, крайний правый - план, вся диаграмма отображает наш процесс движения к желаемому результату)
  • Когда нужно наглядно показать факторы, влияющие на наш параметр (факторный анализ прибыли - из чего она складывается).

Есть несколько способов построения такой диаграммы - всё зависит от вашей версии Microsoft Excel.

Способ 1. Самый простой: встроенный тип в Excel 2016 и новее

Если у вас Excel 2016, 2019 или новее (или Office 365), то построение такой диаграммы не составит труда - в этих версиях Excel такой тип уже встроен по умолчанию. Нужно будет лишь выделить таблицу с данными и выбрать на вкладке Вставка (Insert) команду Каскадная (Waterfall):

Вставка диаграммы водопад в Excel 2016-2019

В результате мы получим практически готовую уже диаграмму:

Готовая диаграмма

Сразу же можно настроить желаемые цвета заливки для положительных и отрицательных столбцов. Удобнее всего это сделать, выделив соответствующие ряды Увеличение и Уменьшение прямо в легенде и, щёлкнув по ним правой кнопкой мыши, выбрать команду Заливка (Fill):

Выделение рядов через легенду

Если нужно добавить в диаграмму столбцы с промежуточными итогами или финальный столбец-итог, то удобнее всего это сделать с помощью функций ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS) или АГРЕГАТ (AGGREGATE). Они посчитает накопленную с начала таблицы сумму, исключив при этом из нее выше расположенные аналогичные итоги:

Функция АГРЕГАТ для промежуточных итогов

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

После добавления строк с итогами останется выделить на диаграмме появившиеся итоговые колонки (сделать два последовательных одиночных щелчка по столбцу) и, щёлкнув правой кнопкой мыши, выбрать команду Установить в качестве итога (Set as total):

Установка итоговых столбцов

Выбранный столбец "приземлится" на ось Х и автоматически поменяет цвет на серый.

Вот, собственно, и всё - диаграмма-водопад готова:

Готовая диаграмма с таблицей

Способ 2. Универсальный: невидимые столбцы

Если у вас Excel 2013 или более древние версии (2010, 2007 и т.д.), то описанный выше способ вам не подойдёт. Придется идти обходным путем и выпиливать недостающую каскадную диаграмму из обычной гистограммы с накоплением (суммированием столбиков друг на друга).

Хитрость тут заключается в использовании прозрачных столбцов-подпорок, приподнимающих наши красные и зеленые ряды данных на нужную высоту:

Диаграмма водопад с прозрачными столбцами

Для построения такой диаграммы нам потребуется добавить к исходным данным еще несколько вспомогательных колонок с формулами:

Дополнительные столбцы с формулами

  • Во-первых, нужно разделить наш исходный столбец, выделив положительные и отрицательные значения в разные колонки с помощью функции ЕСЛИ (IF).  
  • Во-вторых, нужно будет добавить перед сделанными столбцами колонку Пустышки, где первое значение будет 0, а начиная со второй ячейки формулой будет вычисляться высота тех самых прозрачных подпирающих столбцов.

После этого останется выделить всю таблицу кроме исходного столбца Поток и создать обычную гистограмму с накоплением через Вставка - Гистограмма (Insert - Column Chart):

Почти готовая диаграмма водопад

Если теперь выделить синие столбцы и сделать их невидимыми (по ним правой кнопкой мыши - Формат ряда - Заливка - Нет заливки), то мы как раз и получим то, что требуется. 

В плюсах подобного способа - простота. В минусах - необходимость считать вспомогательные колонки.

Способ 3. Если уходим в минус - всё сложнее

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

waterfall4.png

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

Способ 4. Экзотический: полосы повышения-понижения

Этот способ основан на использовании специального малоизвестного элемента плоских диаграмм (гистограмм и графиков) - Полос повышения-понижения (Up-Down Bars). Эти полосы попарно соединяют точки двух графиков, чтобы наглядно показать какая из двух точек выше-ниже, что активно используется при визуализации план-факта:

waterfall5.png

Легко сообразить, что если убрать линии графиков и оставить на диаграмме только полосы повышения-понижения, то мы получим все тот же "водопад".

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

waterfall6.png 

Для создания "водопада" нужно выделить столбец с месяцами (для подписей по оси Х) и два дополнительных столбца График 1 и График 2 и построить для начала обычный график через Вставка - График (Insert - Line Сhart):

waterfall7.png 

Теперь добавим к нашей диаграмме полосы повышения-понижения:

  • В Excel 2013 и новее для этого необходимо выбрать на вкладке Конструктор команду Добавить элемент диаграммы - Полосы повышения-понижения (Design - Add Chart Element - Up-Down Bars)
  • В Excel 2007-2010 - перейти на вкладку Макет - Полосы повышения-понижения (Layout - Up-Down Bars)

Диаграмма после этого начнёт выглядеть примерно так:

waterfall8.png

Осталось выделить графики и сделать их прозрачными, щелкнув по ним по очереди правой кнопкой мыши и выбрав команду Формат ряда данных (Format series). Аналогичным образом можно изменить и стандартные, весьма убого выглядящие, чёрно-белые цвета полос на зелёные и красные, чтобы получить в итоге более приятную картинку:

waterfall9.png 

В последних версиях Microsoft Excel ширину полос можно изменить, щёлкнув по одному из прозрачных графиков (не по полосам!) правой кнопкой мыши и выбрав команду Формат ряда данных - Боковой зазор (Format series - Gap width).

В старых версиях Excel для такого исправления приходилось использовать команду на Visual Basic:

  1. Выделите построенную диаграмму
  2. Нажмите сочетание клавиш Alt+F11, чтобы попасть в редактор Visual Basic
  3. Нажмите сочтетание клавиш Ctrl+G, чтобы открыть панель прямого ввода команд и отладки Immediate (обычно она расположена внизу).
  4. Скопируйте и вставьте туда вот такую команду: ActiveChart.ChartGroups(1).GapWidth = 30 и нажмите Enter:

waterfall10.png

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

waterfall11.png 

Ссылки по теме


23.01.2013 12:46:31
Ссылка Скачать пример наверху есть, но не работает (
24.01.2013 13:08:58
Спасибо, починил :)
06.02.2013 10:33:21
Файл не открывается! Говорит, что поврежден!
07.02.2013 00:38:47
Не гоните волну :) - только что скачал-проверил - все открывается и работает.
20.03.2013 14:09:09
спасибо:)
28.05.2013 14:45:50
классно, спасибо!
09.07.2013 18:42:34
Спасибо, Николай! Искал и нашел, и даже с автоматической раскраской!
31.07.2013 12:34:44
Просто и удобно, но можно ли изменить цвет отдельно взятого столбца во втором способе?
31.07.2013 14:59:25
Почему нет? Сначала выделите его (два одиночных последовательных щелчка левой кнопкой мыши) и перекрашивайте на здоровье.
01.08.2013 22:24:12
Пардон, думал вы про первый способ... Полосу повышения отдельно выделить никак, да.
08.08.2013 12:03:41
Большое спасибо. Сделал вторым способом, вот только как добавить подписи на полосы Повышения/понижения?
08.01.2014 11:54:36
Привет! А как изменить масштаб в диаграмме-водопаде?
Если отклонения незначительны, то они показываются тонкой полоской, вместо ожидаемого длинного столбца...
05.01.2015 00:07:36
Николай, есть возможность изменить ширину полос без VBA
Формат ряда данных - Параметры ряда - Боковой зазор

Уменьшаем до нуля и пожалуйста, достаточно приличной ширины становятся полосы понижения/повышения.:)

По крайней мере так в Excel 2010
14.08.2015 11:46:05
По-моему, тут в первой диаграмме какая-то ошибка. Если в первом (невидимом столбце) приводятся данные на конец периода, то с конца мая по конец июня (т.е. за июнь) не было ни прироста, ни убыли, а в диаграмме в июне прирост на 15. Похожее и в ноябре...
14.08.2015 12:37:54
Я тут прикинул... Должно получиться как-то так:[img]C:\Личное\9\1.jpg[/img]
17.01.2016 15:58:18
Подскажите, как сделать (скорее всего аналог) данного вида диаграммы??
Суть: построение гистограммы по месяцам, НО, отталкивающейся от нулевой оси?
Например, Январь +100 - зеленая колонка поднимается вверх от нулевой оси, а если по полученным данным в феврале -20 - красный столбец опускается ниже нулевой оси.
Заранее благодарю за вашу помощь и подсказку!!
28.03.2016 12:05:55
Евгений, я полагаю, Вам нечто подобное нужно:
Photo
28.03.2016 12:11:37
Хм... А как приаттачить изображение?:oops:

Пришлось только ссылку на файлообменник делать.
28.03.2016 12:26:11
Итак, идея такая:
янв5400100%0%
фев4500-17%0%
мар540020%0%
апр3900-28%0%
май3000-23%0%
июн450050%0%
июл3600-20%0%
авг540050%0%
сен600011%0%
окт5700-5%0%
ноя3600-37%0%
дек450025%0%

Есть данные по месяцам. Допустим, январь = 100%. Каждый следующий месяц сравниваем с предыдущим и добавляем нулевой столбец. На базе 1, 3 и 4 колонки строим график как в способе 2. Вуаля)))
05.10.2016 11:50:29
Самую крутую и удобную водопадку, которую я когда-либо видел можно взять у vertex42 (гуглите). Там приращения реализованы через ... планки погрешностей. Мощная идея. И есть пара фич, которые я ни у кого больше не видел.
Наверх