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

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

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 на закладке «Макет» в разделе «Анализ» нажимаем на кнопку «Полосы повышения/понижения».

9. Каскадная диаграмма

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

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

Вы даже можете начать вычисление с помощью e в первом столбце. В этом случае think-cell начинает с крайнего правого столбца и проводит вычисление в обратном порядке для поиска значения столбца e . Таким образом, следующая таблица дает такую же диаграмму, которая показана выше:

Вы можете ввести в одном столбце два или больше значений. Если столбец состоит из нескольких сегментов, вы можете ввести e только в одном из них.

Если в одном столбце есть и положительные, и отрицательные числа, математическая сумма всех значений будет использоваться для продолжения вычислений, то есть для двух сегментов со значениями 5 и -2 промежуток между соединителями по бокам от столбца будет иметь значение 3. В то же время все отдельные сегменты всегда отображаются с правильным экстентом. Для визуализации математической суммы знаковых сегментов соединитель будет использовать привязку, которая может не соответствовать верхней или нижней части какого-либо отдельного сегмента:

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

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

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

На следующей диаграмме, которая основана на первоначальном примере, соединитель между 1 и 2 столбцами был удален:

При перемещении соединителя вся диаграмма изменяется соответствующим образом, а столбцы, содержащие знак «равно» обновляются.

На следующей диаграмме, которая также основана на первоначальном примере, добавлен столбец со знаком «равно» и оба конца выделенного соединителя перемещены:

Если соединители конфликтуют друг с другом, они будут отклонены. Удалите некоторые из таких соединителей, чтобы устранить проблему.

Например, чтобы создать каскадную диаграмму по возрастанию с итоговыми значениями слева, введите следующие данные на листе:

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

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

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

на панели инструментов. При этом таблица по умолчанию заполняется значениями, необходимыми для диаграммы по убыванию. Кроме этого, каскадные диаграммы по возрастанию и убыванию в think-cell ничем не отличаются.

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

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

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

9.2 “Процент от 100 % таблицы=» как содержимое метки

Метки для стрелок разницы уровней (см. раздел Стрелка разницы уровней) на каскадных диаграммах также поддерживают отображение значений в процентах от значения 100 %= в таблице ( % от 100 % таблицы= ).

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

На диаграммах выше показаны два параметра содержимого метки. На диаграмме слева разница, равна 2, сравнивается с начальным значением 2, что приводит к отображению «+100 %». Если значение 100 %= в таблице оставить пустым, оно будет равно сумме значений столбца. Поэтому на диаграмме справа разница, равная 2, сравнивается с суммой столбца, равной 3, что приводит к отображению «+67 %».

Другое применение показано на следующей диаграмме. Для центрального столбца каскадной диаграммы итоговое значение 5 задано как значение 100 %= в таблице. Используя параметр % от 100 % таблицы= , можно отобразить, что верхние два сегмента соответствуют 40 % от этого итогового значения.