Как построить график водопад в эксель

Диаграмма Водопад в EXCEL

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

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

Диаграмму Водопад построим в EXCEL 2010 с использованием стандартной диаграммой типа Гистограмма с накоплением .

Примечание : Начиная с версии 2016 года в EXCEL имеется стандартная каскадная диаграмма. Подробнее о ее построении можно прочитать в статье на сайте Microsoft .

Диаграмма водопад. Динамика показателя (+)

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

Сначала вычислим изменения за период. Увеличения и уменьшения разнесем по разным столбцам. Это нам позволит выделить цветом разнонаправленные изменения.

Также нам потребуется служебный столбец, который будет служить невидимой основой для столбцов-изменений (см. файл примера Лист Больше0 ).

Будем использовать Гистограмму с накоплением . В качестве рядов данных используем созданные выше столбцы (C, D, E).

Изменим по своему усмотрению цвета столбцов и зазор между ними. Добавим подписи данных. Чтобы не отражались 0 значения используйте пользовательский числовой формат .

Диаграмма водопад. Динамика показателя (+/-)

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

Вычислим изменение за период (столбец С). Для построения диаграммы также потребуются вспомогательные столбцы (см. файл примера Лист ВсеЗначения ).

В остальном построение диаграммы аналогично предыдущей задаче.

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

СОВЕТ : Для начинающих пользователей EXCEL советуем прочитать статью Основы построения диаграмм в MS EXCEL , в которой рассказывается о базовых настройках диаграмм, а также статью об основных типах диаграмм .

Анализ влияния факторов

В отличие от предыдущих 2-х задач, где анализировалось изменение значения показателя за несколько периодов, в этом разделе визуализируем влияние каждого фактора на полученное фактическое значение.

Пусть в начале года было задано плановое значение для прибыли = 140. В конце года было получено значение прибыли = 171. При этом известен вклад каждого из факторов (столбец С).

Используя подходы, указанные в этой статье выше, можно построить диаграмму Водопад для анализа влияния факторов.

Эта диаграмма состоит из 3-х рядов данных. Первый ряд данных включает начальное и конечное значение прибыли, а также невидимые служебные столбцы. Для построения такого ряда проще всего сначала установить для столбцов диаграммы значение Нет заливки , а затем для крайнего правого и левого значений вручную установить нужный цвет (например, синий). Для этого нужно выделить на диаграмме столбцы ряда, через 1 сек выделить левый столбик, изменить его заливку. Затем тоже сделать для последнего столбика. Подробнее см. статью Гистограмма в MS EXCEL с накоплением .

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

Создание каскадной диаграммы

Excel для Microsoft 365 Word для Microsoft 365 Outlook для Microsoft 365 PowerPoint для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Word 2021 Outlook 2021 PowerPoint 2021 Excel 2019 Word 2019 Outlook 2019 PowerPoint 2019 Excel 2016 Word 2016 Outlook 2016 PowerPoint 2016 Excel для iPad Excel для iPhone Еще. Меньше

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

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

Создание каскадной диаграммы

1. Выделите данные.
2. Щелкните Вставка >Вставить каскадную или биржевую диаграмму >Каскадная. Для создания каскадной диаграммы также можно использовать вкладку Все диаграммы в разделе Рекомендуемые диаграммы.

Совет: На вкладках Конструктор и Формат можно настроить внешний вид диаграммы. Если эти вкладки не отображаются, щелкните в любом месте каскадной диаграммы, и на ленте появится область Работа с диаграммами.

Итоги и промежуточные итоги с началом на горизонтальной оси

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

Дважды щелкните точку данных, чтобы открыть области задач Формат точки данных, и установите в поле Установить как итог.

Примечание: Если щелкнуть столбец один раз, будет выбран ряд данных, а не точка данных.
Чтобы снова сделать столбец плавающим, снимите флажок Задать как итог.

Совет: Итоги также можно задать, щелкнув правой кнопкой мыши точку данных и выбрав в контекстном меню пункт Задать как итог.

Отображение и скрытие соединительных линий

Соединительные линии соединяют конец предыдущего столбца c началом следующего, помогая отобразить поток данных на диаграмме.

• Чтобы скрыть соединитеальные линии, щелкните правой кнопкой мыши ряд данных, чтобы открыть области задач Формат ряда данных, и скроем поле Показать соединитеальные линии. Чтобы снова отобразить эти линии, установите флажок Отображать соединительные линии.

Совет: В легенде диаграммы точки данных сгруппированы по типам: Увеличение, Уменьшение и Итог. Если щелкнуть легенду диаграммы, на диаграмме будут выделены все столбцы, соответствующие выбранной группе.

Вот как можно создать каскадную диаграмму в Excel для Mac:

1. Выделите данные.
2. На вкладке Вставка нажмите кнопку Каскадная (значок каскадной) и выберите каскадная.

Примечание: На вкладках Конструктор и Формат можно настроить внешний вид диаграммы. Если эти вкладки не отображаются, щелкните в любом месте каскадной диаграммы, чтобы отобразить их на ленте.

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

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

Опубликовано 18.05.2013 Автор Ренат Лотфуллин

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

Обычно водопады используют для понимания того, как начальное значение зависит от ряда промежуточных положительных и отрицательных значений. Чаще всего начальное и конечное значение отображается в виде целого столбца, а промежуточный – в виде плавающих. В зависимости от положительного или негативного влияния столбца, цвет его различается.

Выглядит она следующим образом:

Итак, посмотрим, как же можно построить диаграмму, похожую на водопад.

Подготовка данных

Изначально у вас имеются данные, примерно похожие на таблицу ниже. Чтобы построить водопад, вам предстоит привести их в должный вид.

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

Где имеются такие формулы

Наиболее сложной для понимания является логика формул, отвечающих за прозрачные колонки – в зависимости от повышения или понижения промежуточной колонки, Excel выбирает на какую высоту поднять или опустить пустую колонку.

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

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

Создание диаграммы Водопад

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

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

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

Щелкаем правой кнопкой по любому ряду данных, из всплывающего меню выбираем Изменить тип диаграммы для ряда. В появившемся диалоговом окне, меняем ряды данных соответствующие коннекторам на График.

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

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

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

Осталось добавить кое-какие штрихи в форматирование. Убрать лишние элементы: легенду, линии сетки. Дать диаграмме внятное имя. Добавить подписи данных. В добавок, я уменьшил боковой зазор между колонками. Все наши злоключения должны были привести нас к следующему результату:

Вам также могут быть интересны следующие статьи

• Что такое Treemap и как его сделать в Excel
• Планки погрешностей в Excel — нестандартное использование
• Создание пулевой диаграммы (bullet graph)
• Создание диаграммы в виде спидометра в Excel
• Диаграмма Ганта в Excel
• Воронка продаж в Excel
• Создание графика в Excel с отрицательными и положительными значениями
• Создание простейшего дашборда в Visio — импорт данных из Excel в Visio
• Бесплатный шаблон дашборда в Excel — потребленная энергетическая ценность продуктов
• Создание динамической диаграммы в Excel с помощью именованных диапазонов

MS Excel

Диаграммы в Excel можно построить по разным данным. Можно в диаграмме показать текущие данные, а можно показать только разницу данных по периодам. Диаграмма водопад в Excel поможет отобразить только изменение данных по периодам, как положительных, так и отрицательных. Поможет провести анализ в Excel любой деятельности.
Для того, чтобы увидеть и сравнить рост и снижение данных на графике, строим диаграмму «Водопад».
Есть несколько вариантов, как построить диаграмму Водопад.

Первый способ.
Как сделать диаграмму водопад в Excel , когда все данные в таблице положительные.
Например, нам нужно посмотреть, как движется прибыль по месяцам. Построим таблицу.
В столбце A – наименование (товар, прибыль, период, т.д.), любые данные, изменение по которым хотим увидеть на графике.
В столбце C пишем цифры роста (прибыли, товара, т.д.) В столбце D пишем цифры снижения.
В столбце B пишем в верхней ячейке число, от которого будем отталкиваться. Например, остаток на начало месяца, если хотим увидеть разницу по месяцам, года – если хотим посмотреть разницу по годам, т.д.
В ячейке B3 пишем формулу, которая будет считать остаток на начало следующего периода (месяца, года). =В2+С3-D3 Пояснение формулы – остаток на январь плюс приход за февраль, минус расход за февраль, получается остаток на февраль и на начало марта. Копируем формулу по столбцу. У нас получилась такая таблица.

Теперь выделяем всю таблицу с шапкой. На закладке «Вставка» в разделе «Диаграммы» нажимаем на кнопку «Гистограмма». Выбираем — «Гистограмма с накоплением».

Теперь нужно изменить цвета столбцов диаграммы. Синий цвет убрать, сделать эти столбцы прозрачными. Нажимаем на синий столбец на диаграмме. Столбцы выделились. Нажимаем на синий столбец правой мышкой, выбираем — «Формат ряда данных» -> «Параметры ряда» -> «Заливка» -> «Нет заливки ».

Теперь установим зеленый цвет для данных прихода (повышения)и красный цвет для данных расхода (снижения).

Нажимаем правой мышкой на столбец диаграммы и выбираем из контекстного меню функцию «Формат ряда данных». В разделе «Параметры ряда» уменьшаем «Зазор». Мы поставили 20%.

Второй способ .
У нас такая таблица по движению финансовых средств по месяцам.

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

В столбце C посчитали данные нарастающим итогом – к данным предыдущего месяца прибавили данные текущего месяца. Например, к значению за январь прибавили значение за февраль.
В ячейке С4 напишем такую формулу. =C3+B4 Получилось накопление нарастающим итогом за февраль. (3 + (-2,5)= 0,5). В феврале произошло снижение, но итоговое значение осталось положительным. Эти данные отложатся на диаграмме над нулевой осью. А в апреле итоговое значение отрицательное. Оно отложится под нулевой линией.
Для этих целей разобьем все данные по отдельным колонкам, чтобы диаграмма построилась правильно.
Все формулы пишем в строке 4, затем копируем вниз по столбцу.

В ячейке E4 формула будет такая. =ЕСЛИ(И(C4 <0;C3<0);МАКС(C3:C4);0)
В ячейке F4. =ЕСЛИ(И(B4>0;C4>0);МИН(B4;C4);0)
В ячейке G4. =ЕСЛИ(И(B4>0;C3 <0);МАКС(-B4;C3);0)
В ячейке H4. =ЕСЛИ(И(C4>0;C3>0);МИН(C3:C4);0)
В ячейке I4. =ЕСЛИ(И(B4 <0;C4<0);МАКС(B4;C4);0)
В ячейке J4. =ЕСЛИ(И(B40);МИН(-B4;C3);0)
В таблице название столбцов окрашено тем же цветом, что и будем раскрашивать столбцы в диаграмме. Не окрашенные столбцы в диаграмме сделаем прозрачными.
Выделяем столбец A с названием столбца. Нажимаем клавишу « Ctrl », удерживая её нажатой, выделяем столбцы с D до J. Получается, мы не выделили только два столбца – В и С.
На закладке «Вставка» в разделе «Диаграммы» нажимаем на кнопку «Гистограмма». Выбираем — «Гистограмма с накоплением».

На закладке «Конструктор» в разделе «Данные» нажимаем на кнопку функции «Строка/Столбец». Меняем местами в диаграмме строки и столбцы. Легенду на диаграмме расположили справа.

Теперь осталось разобраться с цветами столбцов. Нажимаем на столбец в диаграмме правой мышкой. Из контекстного меню выбираем функцию «Формат ряда данных». Устанавливаем нужный цвет.
Столбец января окрасили в серый цвет – это точка отсчета.
Дополнительные столбцы – ставим – «Нет заливки», они станут прозрачными.
Столбцы прихода окрашиваем в зеленый цвет. Столбцы расхода окрашиваем в красный цвет.

Третий способ.
Еще один способ построения диаграммы «Водопад» в Excel с помощью графика .
В ячейке C3 стоит такая формула. =D2 В ячейке D3 стоит такая формула. =D2+B3
Формулы копируем вниз по столбцам. Выделяем столбцы A,C, D (кроме столбца В)с помощью клавиши « Ctrl » (смотрите второй способ).На закладке «Вставка» нажимаем кнопку функции «График ».

Теперь нужно добавить полосы повышения и понижения. В Excel 2013 нажимаем на плюсик справа от диаграммы. Или на закладке «Конструктор» нажимаем на кнопку функции «Добавить элемент диаграммы», Выбираем функцию «Полосы повышения и понижения». В Excel 2007 на закладке «Макет» в разделе «Анализ» нажимаем на кнопку «Полосы повышения/понижения».