Диаграмма "Водопад" в Excel. Пошаговая инструкция

Диаграмма "Водопад" (Waterfall, Мост, Bridge, Каскадная диаграмма) применяется тогда, когда нужно показать влияние на какое-то значение ряда промежуточных факторов с указанием величины и направленности влияния (на сколько выросла/уменьшались величина за счет определенного фактора). Этот вариант визуализации часто используется в факторном и план-факт анализе, а значит умение строить такую диаграмму пригодится многим, кто имеет дело с анализом и графическим представлением данных в Excel. 

Во всех версиях Excel (за исключением Excel 2016) стандартного средства построения подобной диаграммы не предусмотрено. Наиболее частым и популярным методом, используемым для реализации такой визуализации, является создание гистограммы с прозрачными столбцами. Рассмотрим пошагово, как построить диаграмму "Водопад" с помощью обычной гистограммы. 

Шаг 1. Определение исходных данных для построения

Обычно диаграмма строится на основе следующего набора исходных данных:

1) Исходная величина анализируемого показателя (для примера возьмем остаток на расчетном счете на начало месяца);

2) Итоговая величина анализируемого показателя (остаток на расчетном счете на конец месяца);

3) Изменения анализируемого показателя под влиянием различных факторов (движения средств на расчетном счете в разрезе назначений платежей) 

Шаг 2. Преобразование исходных данных и доп. расчеты

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

1) Ряд исходных данных

2) Ряд итоговых данных

3) Невидимый столбец

4) Положительные изменения (приросты)

5) Отрицательные изменения (снижения) 

 

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

Для удобства в исходных данных все поступления оставим положительными числами, а все расходования сделаем отрицательными. Чтобы быстро превратить число в отрицательное нужно умножить его на "-1". Самый быстрый способ - специальная вставка. Как пользоваться - смотри здесь. 

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

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

 В столбце "Снижения" во всех строках кроме исходной и итоговой пишем формулу "=ЕСЛИ(C5<0;ABS(C5);0)", где C5 - величина изменения в соответствующей строке.  Эта формула позволит записать в столбце только величины снижений, при этом они будут переведены в положительные числа (взяты по модулю), что нужно для построения гистограммы.  

В столбце "Невидимый столбец" во всех строках кроме исходной и итоговой пропишем формулу "=ЕСЛИ(C5>0;СУММ($C$4:C4);СУММ($C$4:C4)+C5)", где C5 - величина изменения в соответствующей строке, а C4 - ячейка с исходной величиной.  

Эта формула проверяет, прирост или снижение дала нам величина в текущей строке. Если прирост - то размер невидимого столбца равен нарастающему итогу всех изменений до текущей строки (обращаем внимание на закрепление ячейки в функции СУММ($C$4:C4) ,  если снижение - то размер невидимого столбца равен нарастающему итогу всех изменений до текущей строки за вычетом величины снижения.

  

Шаг 3. Построение диаграммы

После того, как все столбцы заполнены, можно строить диаграмму. Для этого выделяем вместе с шапкой столбец с названиями категорий (в его шапке должно быть пусто, иначе диаграмма не поймет, что это столбец подписей оси Х), а также все расчетные столбцы и выбираем для построения "Гистограмму с накоплением".  

Шаг 4. Форматирование и настройка диаграммы

Чтобы диаграмма приняла нужный вид мы должны ее настроить. 

1) Скрыть невидимый столбец. Для этого выбираем ряд данных "Невидимый столбец", кликаем на нем правой кнопкой мыши, выбираем "Формат ряда данных" и в появившейся панели в блоке "Заливка и границы" отмечаем пункт "Нет заливки" 

  2) Применить нужные цвета к другим столбцам. Для этого выбираем нужный ряд данных, кликаем на нем правой кнопкой мыши, выбираем "Формат ряда данных" и в появившейся панели в блоке "Заливка и границы" отмечаем пункт "Сплошная заливка", после чего выставляем необходимый цвет.  

  3) Изменяем ширину столбцов на диаграмме. Для этого выбираем любой ряд данных, кликаем на нем правой кнопкой мыши, выбираем "Формат ряда данных" и в появившейся панели в блоке "Параметры ряда" меняем значение величины "Бокового зазора" на нужное нам (чем меньше % бокового зазора, тем шире столбцы диаграммы).   

 Остальные настройки можете применять по своему вкусу и в соответствии со своим видением построения хорошей диаграммы.  

 

Каскадные диаграммы в Excel 2016

Счастливые обладатели Excel 2016 будут рады узнать, что в этой версии разработчики наконец-то добавили такой тип диаграммы, как "Водопад". Правда, называется она "Каскадная диаграмма", но сути дела это не меняет.  

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

Выбираем исходные данные, вставляем диаграмму, выбираем тип "Каскадная". Кликаем дважды на рядах с итоговыми данными, чтобы открылась панель "Формат точки данных". Если вместо нее открывается "Формат ряда данных", кликните на ряде еще пару раз. 

В появившейся панели отмечаем пункт "Установить в качестве итога".  

Еще одна причина установить себе последнюю версию Excel.

Поддержать наш проект и его дальнейшее развитие можно вот здесь.

Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot

С уважением, команда tDots.ru