Діаграма водоспад (waterfall chart) в excel, exceltip

Водоспад діаграма (waterfall chart) є однією з форм візуалізації даних, яка показує сукупний ефект послідовно введених позитивних і негативних значень. Також іноді можна зустріти назву bridge chart, або «міст».

Зазвичай водоспади використовують для розуміння того, як початкове значення залежить від ряду проміжних позитивних і негативних значень. Найчастіше початковий і кінцевий значення відображається у вигляді цілого стовпця, а проміжний - у вигляді плаваючих. Залежно від позитивного або негативного впливу стовпчика, колір його різниться.

Виглядає вона наступним чином:

Діаграма водоспад (waterfall chart) в excel, exceltip

Отже, подивимося, як же можна побудувати діаграму, схожу на водоспад.

Підготовка данних

Спочатку у вас є дані, приблизно схожі на таблицю нижче. Щоб побудувати водоспад, вам доведеться привести їх у належний вигляд.

Діаграма водоспад (waterfall chart) в excel, exceltip

Водоспад - це звичайна діаграма, відформатована певним чином. «Порожнечі» під проміжними значеннями - це теж ряди даних, але тільки без заливки. Таким чином, нам потрібно розбити дані вихідної таблиці на чотири колонки: крайні і прозорі колонки, позитивні значення і негативні значення. Наша таблиця набуде вигляду:

Діаграма водоспад (waterfall chart) в excel, exceltip

Де є такі формули

Найбільш складною для розуміння є логіка формул, що відповідають за прозорі колонки - в залежності від підвищення або зниження проміжної колонки, Excel вибирає на яку висоту підняти або опустити порожню колонку.

Для більшої наочності, додамо коннектори між проміжними значеннями, вони поліпшать читаність діаграми за рахунок установки точки відліку колонки.

Тут все просто, осередок G3 підсумовує значення осередків C3: E3, відповідно формула в ній буде = СУММ (C3: E3). Осередок G4 копіює значення осередку G3.

Створення діаграми Водоспад

Залишилося найпростіше - побудувати діаграму. Виділяємо комірки A1: A6 (так, вільну позицію теж включаємо), тиснемо клавішу Ctrl і виділяємо комірки C1: J6, таким чином у вас буде виділено дві області.

Переходимо на вкладку Вставка в групу Діаграми, вибираємо Вставити гистограмму -> Гистограмма з накопиченням. У вас повинен вийти ось такий графік:

Діаграма водоспад (waterfall chart) в excel, exceltip

Міняємо значення стовпців і рядків місцями. Для цього переходимо по вкладці Робота з діаграмами -> Конструктор в групу Дані і клацаємо по іконці Строка / стовпець. Наша діаграма набуде вигляду:

Діаграма водоспад (waterfall chart) в excel, exceltip

Клацаємо правою кнопкою по будь-якому ряду даних, з спливаючого меню вибираємо Змінити тип діаграми для ряду. У діалоговому вікні, міняємо ряди даних відповідні коннекторам на Графік.

Діаграма водоспад (waterfall chart) в excel, exceltip

На діаграмі клацаємо правою кнопкою по ряду даних Конектор1, з спливаючого меню вибираємо Формат ряду даних, в правій панелі у вкладці Лінія встановлюємо формат, щоб вийшла пунктирна лінія чорного кольору. Повторюємо ці дії для всіх рядів даних відповідають за коннектори.

Діаграма водоспад (waterfall chart) в excel, exceltip

Далі скористаємося принципом умовного форматування для діаграм. який був описаний в одному з попередніх статей. Крайні ряди даних заливаємо в сірий, Зелений і червоний заливаємо відповідними кольорами, у Прозорого прибираємо заливку зовсім. Клацаємо правою кнопкою миші по ряду даних, вибираємо Формат ряду даних. У правій панелі у вкладці Заливка і кордон встановлюємо необхідні опції заливки для конкретного ряду даних.

Діаграма водоспад (waterfall chart) в excel, exceltip

В принципі, наша діаграма водоспад готова.

Діаграма водоспад (waterfall chart) в excel, exceltip

Залишилося додати деякі штрихи в форматування. Прибрати зайві елементи: легенду, лінії сітки. Дати діаграмі виразне ім'я. Додати підписи даних. В добавок, я зменшив бічний зазор між колонками. Всі наші пригоди повинні були привести нас до наступного результату:

Діаграма водоспад (waterfall chart) в excel, exceltip

Вам також можуть бути цікаві такі статті

Схожі статті