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

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

waterfall1.png

Издали действительно похожа на водопад или навесной мост. Давайте с ней разберемся.

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

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

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

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

Способ 1. Невидимые столбцы

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

waterfall2.png

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

Если выделить всю таблицу и создать обычную гистограмму с накоплением через Вставка - Диаграмма (Insert - Chart):

waterfall3.png

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

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

waterfall4.png

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

Способ 2. Полосы повышения-понижения

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

waterfall5.png

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

waterfall6.png 

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

waterfall7.png 

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

  • В Excel 2003 для этого необходимо щелкнуть правой кнопкой мыши по любому из графиков и выбрать Формат ряда (Format Series) - Полосы повышения-понижения (Up-Down Bars)
  • В Excel 2007 и новее - перейти на вкладку Макет (Layout) - Полосы повышения-понижения (Up-Down Bars)

waterfall8.png

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

waterfall9.png 

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

  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 (гуглите). Там приращения реализованы через ... планки погрешностей. Мощная идея. И есть пара фич, которые я ни у кого больше не видел.