Как построить водопадную диаграмму

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

Издалека это действительно похоже на каскад водопадов на горной реке или на висячий мост – кто что видит 🙂

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

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

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

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

Есть несколько способов построить такую ​​диаграмму – все зависит от вашей версии Microsoft Excel.

Способ 1. Самый простой: встроенный текст в Excel 2016 и более поздних версиях.

Если у вас Excel 2016, 2019 или новее (или Office 365), то построить такую ​​диаграмму не составит труда — в этих версиях Excel этот тип уже встроен по умолчанию. Останется только выделить таблицу с данными и выбрать на вкладке Вставить (Вставить) Command каскадный (Водопад):

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

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

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

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

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

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

Вот, собственно, и все — каскадная диаграмма готова:

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

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

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

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

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

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

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

Преимущество этого метода – простота. В минусах – необходимость считать вспомогательные столбцы.

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

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

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

Способ 4. Экзотика: полосы вверх-вниз

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

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

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

Для создания «водопада» необходимо выделить столбец с месяцами (для подписей по оси X) и два дополнительных столбца. Расписание 1 и Расписание 2 и построим для начала обычный график, используя Вставка – График (Вставка — Линейный график):

Теперь добавим на наш график полосы вверх-вниз:

• В Excel 2013 и более поздних версиях это необходимо выбрать на вкладке Конструктор Command Добавить элемент диаграммы — Полосы увеличения-уменьшения (Дизайн — Добавить элемент диаграммы — Полосы вверх-вниз)
• В Excel 2007-2010 — перейти на вкладку Макет – полосы продвижения-уменьшения (Макет — полосы вверх-вниз)

Тогда диаграмма будет выглядеть примерно так:

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

В последних версиях Microsoft Excel ширину столбцов можно изменить, щелкнув по одному из прозрачных графиков (не столбцов!) правой кнопкой мыши и выбрав команду Формат серии данных — боковой зазор (Серия формата — Ширина зазора).

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

1. Выделить построенную диаграмму
2. Нажмите сочетание клавиш другой+F11чтобы попасть в редактор Visual Basic
3. Нажмите сочетание клавиш Ctrl+Gоткрыть панель прямого ввода команд и отладки немедленная (обычно расположен внизу).

4. Скопируйте и вставьте туда следующую команду: ActiveChart.ChartGroups(1).GapWidth = 30 и нажмите Enter:

Конечно, вы можете поэкспериментировать со значением параметра, если хотите. Ширина зазорадля достижения желаемого зазора:

• Как построить маркированную диаграмму в Excel для визуализации KPI  
• Что нового в диаграммах в Excel 2013
• Как создать интерактивную «живую» диаграмму в Excel