Як побудувати водоспадну діаграму

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

Здалеку це дійсно схоже на каскад водоспадів на гірській річці чи навісний міст – хто що побачить 🙂

Особливістю такої діаграми є те, що:

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

У повсякденному житті такі діаграми зазвичай використовуються в наступних випадках:

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

Існує кілька способів побудови такої діаграми – все залежить від вашої версії Microsoft Excel.

Спосіб 1. Найпростіший: вбудований текст у Excel 2016 і новіших версіях

Якщо у вас Excel 2016, 2019 або новішої версії (або Office 365), то побудувати таку діаграму нескладно – у цих версіях Excel цей тип вже вбудований за замовчуванням. Потрібно буде тільки вибрати таблицю з даними і вибрати на вкладці Insert (Вставити) Command каскадні (Водоспад):

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

Ви можете відразу встановити бажані кольори заливки для позитивних і негативних стовпців. Найпростіший спосіб зробити це, вибравши відповідні рядки Augmenter и зменшити безпосередньо в легенді та, клацнувши на них правою кнопкою миші, виберіть команду Заповнювати (Заповнити):

Якщо вам потрібно додати на діаграму стовпці з проміжними підсумками або кінцевий стовпець-підсумок, то найзручніше це зробити за допомогою функцій ПРОМІЖНІ ПІДСУМКИ (ПІДСУМКИ) 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 і натисніть Що натомість? Створіть віртуальну версію себе у :

Ви, звичайно, можете пограти зі значенням параметра, якщо хочете. GapWidthщоб досягти бажаного кліренсу:

• Як побудувати маркировану діаграму в Excel для візуалізації KPI  
• Що нового в діаграмах в Excel 2013
• Як створити інтерактивну «живу» діаграму в Excel