Диаграмма водопад (waterfall chart) в Excel

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

Диаграмма водопад (также называют waterfall chart) выглядит следующим образом:

Диаграмма водопад (waterfall chart)

Диаграмма водопад (waterfall chart)


Подобная визуализация часто используется в финансовом анализе (например, план-факт анализ, free cash flow и т.п.)

Построение водопадной диаграммы

Предположим, что у нас есть таблица с данными для факторного анализа:

Таблица для факторного анализа
Чтобы построить диаграмму добавим в таблицу вспомогательные элементы:

Построение вспомогательной таблицы

Построение вспомогательной таблицы

  • Прозрачный — вспомогательный столбец поднимающий факторы на необходимую высоту;
  • Красный 1 — начальное значение;
  • Оранжевый — отрицательный фактор;
  • Зеленый — положительный фактор;
  • Красный 2 — конечное значение.

Расшифровка формул вспомогательной таблицы

Расшифровка формул вспомогательной таблицы


Выделяем данные вспомогательной таблицы и вставляем гистограмму. Для этого переходим в панели вкладок на Вставка -> Диаграмма -> Гистограмма -> Гистограмма с группировкой:

Построение накопительной гистограммы

Построение накопительной гистограммы


Приводим столбцы диаграммы в удобный вид, для этого скроем столбец «Прозрачный» и разукрасим остальные столбцы в соответствующие цвета (нажимаем правой кнопкой мыши на необходимый ряд, выбираем Формат ряда данных -> Заливка):

Меняем цвет рядов диаграммы

Меняем цвет рядов диаграммы


Задаем боковой зазор для всех рядов равным 0 — нажимаем правой кнопкой мыши на ряд, выбираем Формат ряда данных -> Параметры ряда -> Боковой зазор:

Выставляем боковой зазор для рядов равным 0

Выставляем боковой зазор для рядов равным 0


Добавляем подпись данных на ряды (нажимаем правой кнопкой мыши на ряд, выбираем Формат ряда данных -> Добавить подпись данных):

Добавляем подпись данных на ряды

Добавляем подпись данных на ряды

Далее форматируем диаграмму на свое усмотрение и получаем окончательный вид (убираем легенду, подпись вертикальной оси и т.д.):

Диаграмма водопад (waterfall chart)

Диаграмма водопад (waterfall chart)

Особенности диаграммы водопад

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

Ошибка при переходе через 0

Ошибка при переходе через 0


Чтобы избежать подобных проблем приподнимем всю диаграмму на определенную высоту, которая заведомо позволит уйти от перехода через ноль.
Для этого добавим в первый и последний элемент вспомогательного столбца «Прозрачный» достаточно больше положительное число (в данном примере в ячейки C11 и C17 поставили значение 1 000), остальные элементы таблицы автоматически пересчитаются:

Изменение вспомогательной таблицы

Изменение вспомогательной таблицы


После всех манипуляций с таблицей, итоговый вид диаграммы водопад приобретает следующий вид:

Диаграмма водопад (waterfall chart)

Диаграмма водопад (waterfall chart)

Удачи вам и до скорых встреч на страницах блога Tutorexcel.ru!

Поделиться с друзьями:
Поиск по сайту:

Одна идея о “Диаграмма водопад (waterfall chart) в Excel

  1. Василий

    Толковый сайт, спасибо! В Excel давно работаю, но всё равно что-то новое нахожу.

Добавить комментарий

Ваш адрес email не будет опубликован.