Диаграмма Водопад в EXCEL
Построим диаграмму Водопад, которая используется для визуализации динамики показателя (за одинаковые периоды), а также для демонстрации влияния различных факторов на показатель . Диаграмма Водопад также часто называют «каскадной диаграммой».
Сначала научимся создавать диаграмму Водопад только для положительных значений (проще), затем рассмотрим более общий случай (сложнее).
Диаграмму Водопад построим в EXCEL 2010 с использованием стандартной диаграммой типа Гистограмма с накоплением .
Примечание : Начиная с версии 2016 года в EXCEL имеется стандартная каскадная диаграмма. Подробнее о ее построении можно прочитать в статье на сайте Microsoft .
Диаграмма водопад. Динамика показателя (+)
Пусть дана таблица со значениями показателя в каждый период (остатки на складе на конец месяца, столбец В). Предполагается, что остатки на складе не могут быть отрицательными.
Сначала вычислим изменения за период. Увеличения и уменьшения разнесем по разным столбцам. Это нам позволит выделить цветом разнонаправленные изменения.
Также нам потребуется служебный столбец, который будет служить невидимой основой для столбцов-изменений (см. файл примера Лист Больше0 ).
Будем использовать Гистограмму с накоплением . В качестве рядов данных используем созданные выше столбцы (C, D, E).
Изменим по своему усмотрению цвета столбцов и зазор между ними. Добавим подписи данных. Чтобы не отражались 0 значения используйте пользовательский числовой формат .
Диаграмма водопад. Динамика показателя (+/-)
Используем исходную таблицу, в которой приведено значение прибыли в конце каждого периода (столбец В). Обратите внимание, что в отличие от предыдущего случая, значения прибыли может быть отрицательным. Изменения, которые соответствуют отрицательным значениям, будем выделять на диаграмме более светлым цветом, чтобы подчеркнуть, что эти изменения привели к убыткам.
Вычислим изменение за период (столбец С). Для построения диаграммы также потребуются вспомогательные столбцы (см. файл примера Лист ВсеЗначения ).
В остальном построение диаграммы аналогично предыдущей задаче.
Чтобы вывести подписи данных использован специальный столбец, значения которого выводятся на диаграмме с помощью вспомогательной оси (скрыта). В качестве подписей данных используются не сами значения, а имена категорий (в качестве категорий (ось х) указаны изменения за период).
СОВЕТ : Для начинающих пользователей EXCEL советуем прочитать статью Основы построения диаграмм в MS EXCEL , в которой рассказывается о базовых настройках диаграмм, а также статью об основных типах диаграмм .
Анализ влияния факторов
В отличие от предыдущих 2-х задач, где анализировалось изменение значения показателя за несколько периодов, в этом разделе визуализируем влияние каждого фактора на полученное фактическое значение.
Пусть в начале года было задано плановое значение для прибыли = 140. В конце года было получено значение прибыли = 171. При этом известен вклад каждого из факторов (столбец С).
Используя подходы, указанные в этой статье выше, можно построить диаграмму Водопад для анализа влияния факторов.
Эта диаграмма состоит из 3-х рядов данных. Первый ряд данных включает начальное и конечное значение прибыли, а также невидимые служебные столбцы. Для построения такого ряда проще всего сначала установить для столбцов диаграммы значение Нет заливки , а затем для крайнего правого и левого значений вручную установить нужный цвет (например, синий). Для этого нужно выделить на диаграмме столбцы ряда, через 1 сек выделить левый столбик, изменить его заливку. Затем тоже сделать для последнего столбика. Подробнее см. статью Гистограмма в MS EXCEL с накоплением .
Второй ряд содержит значения положительных отклонений (зеленый цвет), третий ряд — отрицательные (красные столбики). Диаграмма построена в файле примера на листе Факторы .
Как создать и настроить каскадную диаграмму в Microsoft Excel
Если вы хотите создать визуальное представление, показывающее, как положительные и отрицательные значения влияют на итоги, вы можете использовать каскадную диаграмму, также называемую мостовой или каскадной диаграммой. Вы можете легко создать и настроить каскадную диаграмму в Microsoft Excel.
Когда использовать диаграмму водопада
Вам может быть интересно, подходят ли данные, которые есть в вашей электронной таблице, для каскадной диаграммы. Если у вас есть начальное значение с положительным и отрицательным рядом, которое влияет на конечный результат, то каскадная диаграмма для вас.
Программы для Windows, мобильные приложения, игры — ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале — Подписывайтесь:)
Вот лишь несколько распространенных применений:
- Проверка счетов: используйте начальный баланс, добавьте кредиты, вычтите дебеты и покажите конечный баланс.
- Запасы: введите начальную сумму, добавьте полученные поставки, вычтите проданные единицы и покажите конечную сумму.
- Продукты: Отображение начальной суммы, вычитание поврежденных единиц, добавление отремонтированных единиц и отображение суммы, пригодной для продажи.
- Доход: используйте начальную сумму, добавьте доход, вычтите расходы и покажите оставшуюся сумму.
Создайте диаграмму водопада в Excel
Если у вас есть данные, которые идеально вписываются в каскадную диаграмму для полезного визуального представления, давайте приступим прямо к делу! В этом уроке мы будем использовать расчетный счет в качестве примера.
Начните с выбора ваших данных. Ниже вы можете видеть, что наши данные начинаются с начального баланса, включают входящие и исходящие средства и заканчиваются конечным балансом. Вы должны упорядочить свои данные аналогичным образом.
Перейдите на вкладку «Вставка» и раздел «Диаграммы» на ленте. Щелкните стрелку раскрывающегося списка «Водопад» и выберите «Водопад» в качестве типа диаграммы.
Водопадная диаграмма появится в вашей электронной таблице.
Теперь вы можете заметить, что начальный и конечный итоги не совпадают с числами на вертикальной оси и не окрашены как Итого согласно легенде. Не волнуйтесь — это простое решение!
Excel распознает эти начальные и конечные суммы как части ряда (положительные и отрицательные), а не итоги.
Чтобы исправить это, дважды щелкните диаграмму, чтобы отобразить боковую панель «Формат». Выберите столбец для итогового значения, щелкнув его дважды. Перейдите на вкладку «Параметры серии» на боковой панели и при необходимости разверните «Параметры серии».
Установите флажок «Установить как общее». Затем сделайте то же самое для другой суммы.
Теперь вы увидите, что эти столбцы совпадают с вертикальной осью и окрашены как Total согласно легенде.
Настройте каскадную диаграмму
Как и другие типы диаграмм в Excel, вы можете настроить дизайн, цвета и внешний вид диаграммы. Если вы еще не делали этого в Excel, вот основные принципы настройки диаграммы.
Если вы хотите начать с изменения заголовка, щелкните текстовое поле «Заголовок диаграммы».
Дважды щелкните диаграмму, чтобы открыть боковую панель «Формат области диаграммы». Затем используйте вкладки «Заливка и линия», «Эффекты» и «Размер и свойства», чтобы добавить границу, применить тень или масштабировать диаграмму.
Выберите диаграмму и используйте кнопки справа (Excel в Windows), чтобы настроить элементы диаграммы, такие как метки и легенда, или стили диаграммы, чтобы выбрать тему или цветовую схему.
Выберите диаграмму и перейдите на вкладку «Дизайн диаграммы». Затем используйте инструменты на ленте, чтобы выбрать другой макет, изменить цвета, выбрать новый стиль или настроить выбор данных.
Вы также можете переместить диаграмму в новое место на листе, просто перетащив ее. Чтобы изменить размер диаграммы, перетащите ее внутрь или наружу из угла или края.
Чтобы получить справку по другим типам диаграмм, посмотрите, как создать линейчатую диаграмму в Excel или создать комбинированную диаграмму.
Программы для Windows, мобильные приложения, игры — ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале — Подписывайтесь:)
покупка
Легко создать горизонтальную диаграмму водопада в Excel
Kutools for Excel
Повышает Excel с помощью 300+
Мощные функции
Как правило, вы можете быстро создать вертикальную диаграмму водопада в Excel. Здесь рекомендую Горизонтальная диаграмма водопада полезности Kutools for Excel. С помощью этой функции вы можете легко создать горизонтальную диаграмму водопада или мини-горизонтальную диаграмму водопада в Excel.
Создайте горизонтальную диаграмму водопада
Создайте мини-карту с горизонтальным водопадом
Создайте горизонтальную диаграмму водопада
Вы можете сделать следующее, чтобы создать горизонтальную диаграмму водопада в Excel.
1. Нажмите Кутулс > Графики > Сравнение различий > Горизонтальная диаграмма водопада.
2. в Горизонтальная диаграмма водопада диалоговом окне настройте следующим образом.
2.1). График напишите раздел, выберите График вариант;
2.2). Выберите данные раздел:
Ярлыки осей: Выберите диапазон данных меток оси;
Диапазон данных: Выберите диапазон данных серии.
2.3) Нажмите OK.
3. Kutools for Excel появится диалоговое окно, щелкните Да.
Затем создается горизонтальная диаграмма водопада, как показано на скриншоте ниже.
Создайте мини-карту с горизонтальным водопадом
Эта функция также может помочь в создании мини-горизонтальной водопадной диаграммы в ячейках Excel. Для этого вы можете сделать следующее.
1. Нажмите Кутулс > Графики > Сравнение различий > Горизонтальная диаграмма водопада.
2. в Горизонтальная диаграмма водопада диалоговом окне выполните следующие настройки.
2.1). Тип диаграммы раздел, выберите Мини-диаграмма вариант;
2.2). Параметры мини-диаграммы раздел, есть два варианта «Добавьте метку названия категории(Основной ключ) и Добавить метку значения серии», Необязательно выбирать один из них, оба или ни одного, как вам нужно;
2.3). Выберите данные раздел:
Ярлыки осей: Выберите диапазон данных меток оси;
Диапазон данных: Выберите диапазон данных серии.
2.4) Нажмите OK.
3. В дебюте Kutools for Excel диалоговое окно, нажмите Да.
4. Затем Выберите данные появится диалоговое окно, выберите ячейку для вывода мини-диаграммы и нажмите OK.
Теперь создана мини-диаграмма горизонтального водопада.
При выборе как «Добавьте метку названия категории(Основной ключ) и Добавить метку значения серии»В диалоговом окне« Горизонтальная диаграмма водопада », вы получите следующую мини-диаграмму.
Если ни один из них не выбран, вы получите мини-диаграмму, как показано на скриншоте ниже.
При выборе одного из них, например «Добавить метку значения серии”, Вы получите следующий результат.
v
Заметки:
1. Нажмите Пример в Горизонтальная диаграмма водопада диалоговое окно откроет пример книги.
2. Чтобы изменить размер или цвет мини-диаграммы, просмотрите подробную информацию в примере книги.
3. Для данных серии последнее значение должно быть итоговым значением списка.
Если вы хотите получить бесплатную пробную версию ( 30 -день) этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Рекомендуемые инструменты для повышения производительности
Следующие ниже инструменты могут значительно сэкономить ваше время и деньги. Какой из них вам подходит?
Office Tab: Использование удобных вкладок в вашем офисе, как и в случае Chrome, Firefox и New Internet Explorer.
Kutools for Excel: Более 300 дополнительных функций для Excel 2021, 2019, 2016, 2013, 2010, 2007 и Office 365.
Kutools for Excel
Описанный выше функционал — лишь одна из 300 мощных функций Kutools for Excel.
Предназначен для Excel(Office) 2021, 2019, 2016, 2013, 2010, 2007 и Office 365. Бесплатно скачать и использовать в течение 30 дней.
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 с помощью именованных диапазонов