Диаграмма «Водопад» представляет собой один из вариантов обычной гистограммы, наглядно отображающий динамику любого процесса. Особенность диаграммы-водопада в том, что на ней отображается не собственно значения какого-либо параметра, а только его изменения. Причем положительные изменения (рост) - одним цветом (зеленым), а отрицательные (спад) - другим (красным).
В повседневной жизни такие диаграммы используются, например, для наглядного отображения:
· потока наличности (приход-расход, cash flow)
· инвестиций (вкладываем деньги в проект и получаем от него прибыль)
· выполнения плана (крайний левый столбик в диаграмме - факт, крайний правый - план, вся диаграмма отображает наш процесс движения к желаемому результату)
· и т.д.
Есть несколько способов построения подобной нестандартной диаграммы "водопада" в Microsoft Excel.
Способ 1. Невидимые столбцы
Суть этого способа заключается в использовании невидимых столбцов-подпорок, приподнимающих наши красные и зеленые ряды данных на нужную высоту:
Для построения потребуется в таблице не только столбцы с исходными данными, но и вспомогательная колонка "невидимый столбец". Обратите внимание на формулу в синей ячейке. Она подсчитывает остаток суммы на конец месяца, т.е. как раз высоту невидимой подпирающей колонки, которая нам нужна.
Если выделить всю таблицу и создать обычную гистограмму с накоплением через Вставка - Диаграмма (Insert - Chart):
А потом выделить синие столбцы и сделать их невидимыми (по ним правой кнопкой мыши - Формат ряда - Заливка - Нет заливки), то мы как раз и получим то, что хотелось.
В плюсах подобного способа - простота. В минусах - необходимость считать вспомогательный невидимый столбец. Причем, если диаграмма уходит в отрицательную область, то сложность формул возрастает, т.к. необходимо будет отдельно просчитать отрицательную и положительную части диаграммы:
|
|
Способ 2. Полосы повышения-понижения
Этот способ основан на использовании специального малоизвестного элемента плоских диаграмм (гистограмм и графиков) - Полос повышения-понижения (Up-Down Bars). Эти полосы попарно соединяют точки двух графиков, чтобы наглядно показать какая из двух точек выше-ниже:
Легко заметить, что если убрать линии графиков и оставить на диаграмме только полосы повышения-понижения, то мы получим все тот же "водопад". В этом и суть данного способа. Для построения нам потребуется добавить к нашей таблице еще два дополнительных столбца с простыми формулами, которые рассчитают положение двух требуемых невидимых графиков:
Для создания "водопада" нужно выделить столбец с месяцами (для подписей по оси Х) и два дополнительных столбца График 1 и 2 и построить для начала обычный график через Вставка - Диаграмма - График (Insert - Chart - Line):
Теперь добавим к нашей диаграмме полосы повышения-понижения:
В Excel 2003 для этого необходимо щелкнуть правой кнопкой мыши по любому из графиков и выбрать Формат ряда (Format Series) - Полосы повышения-понижения (Up-Down Bars)
В Excel 2007 и новее - перейти на вкладку Макет (Layout) - Полосы повышения-понижения (Up-Down Bars)
Осталось выделить графики и сделать их прозрачными, щелкнув по ним по очереди правой кнопкой мыши и выбрав команду Формат ряда (Format series). Аналогичным образом можно изменить и стандартные черно-белые цвета полос повышения-понижения, чтобы получить в итоге более приятную картинку:
|
|
К сожалению, нет встроенной возможности быстро изменить ширину полос, а по умолчанию они довольно "худенькие", как можно заметить. Для исправления этого нюанса воспользуемся VBA:
· Выделите построенную диаграмму
· Нажмите сочетание клавиш Alt+F11, чтобы попасть в редактор Visual Basic
· Нажмите сочетание клавиш Ctrl+G, чтобы открыть панель прямого ввода команд и отладки Immediate
· Скопируйте и вставьте туда вот такую команду: ActiveChart.ChartGroups(1).GapWidth = 30 и нажмите Enter:
При желании можно, конечно, поиграться со значением параметра GapWidth, чтобы добиться нужной величины зазора:
