Как построить диаграмму водопад в экселе
Диаграмма Водопад в EXCEL
history 29 марта 2015 г.
Построим диаграмму Водопад, которая используется для визуализации динамики показателя (за одинаковые периоды), а также для демонстрации влияния различных факторов на показатель . Диаграмма Водопад также часто называют «каскадной диаграммой».
Сначала научимся создавать диаграмму Водопад только для положительных значений (проще), затем рассмотрим более общий случай (сложнее).
Диаграмма водопад. Динамика показателя (+)
Пусть дана таблица со значениями показателя в каждый период (остатки на складе на конец месяца, столбец В). Предполагается, что остатки на складе не могут быть отрицательными.
Сначала вычислим изменения за период. Увеличения и уменьшения разнесем по разным столбцам. Это нам позволит выделить цветом разнонаправленные изменения.
Также нам потребуется служебный столбец, который будет служить невидимой основой для столбцов-изменений (см. файл примера Лист Больше0 ).
Диаграмма водопад. Динамика показателя (+/-)
Используем исходную таблицу, в которой приведено значение прибыли в конце каждого периода (столбец В). Обратите внимание, что в отличие от предыдущего случая, значения прибыли может быть отрицательным. Изменения, которые соответствуют отрицательным значениям, будем выделять на диаграмме более светлым цветом, чтобы подчеркнуть, что эти изменения привели к убыткам.
Вычислим изменение за период (столбец С). Для построения диаграммы также потребуются вспомогательные столбцы (см. файл примера Лист ВсеЗначения ).
В остальном построение диаграммы аналогично предыдущей задаче.
Чтобы вывести подписи данных использован специальный столбец, значения которого выводятся на диаграмме с помощью вспомогательной оси (скрыта). В качестве подписей данных используются не сами значения, а имена категорий (в качестве категорий (ось х) указаны изменения за период).
Анализ влияния факторов
В отличие от предыдущих 2-х задач, где анализировалось изменение значения показателя за несколько периодов, в этом разделе визуализируем влияние каждого фактора на полученное фактическое значение.
Пусть в начале года было задано плановое значение для прибыли = 140. В конце года было получено значение прибыли = 171. При этом известен вклад каждого из факторов (столбец С).
Используя подходы, указанные в этой статье выше, можно построить диаграмму Водопад для анализа влияния факторов.
Как создать каскадную диаграмму в Excel
Если вы часто используете Excel, то наверняка знаете о преимуществах графиков не понаслышке. Графическое представление данных оказывается очень полезным, когда нужно наглядно провести сравнение данных или обозначить вектор развития.
Microsoft Excel предлагает множество встроенных типов диаграмм, в том числе гистограммы, линейчатые диаграммы, круговые и другие типы диаграмм. В этой статье мы подробно рассмотрим все детали создания простейших графиков, и кроме того, ближе познакомимся с особым типом диаграммы – каскадная диаграмма в Excel (аналог: диаграмма водопад). Вы узнаете, что представляет собой каскадная диаграмма и насколько полезной она может быть. Вы познаете секрет создания каскадной диаграммы в Excel 2010-2013, а также изучите различные инструменты, которые помогут сделать такую диаграмму буквально за минуту.
И так, давайте начнем совершенствовать свои навыки работы в Excel.
Примечание переводчика: Каскадная диаграмма имеет множество названий. Самые популярные из них: Водопад, Мост, Ступеньки и Летающие кирпичи, также распространены английские варианты – Waterfall и Bridge.
Что такое диаграмма «Водопад»?
Для начала, давайте посмотрим, как же выглядит самая простая диаграмма «Водопад» и чем она может быть полезна.
Диаграмма «Водопад» – это особый тип диаграммы в Excel. Обычно используется для того, чтобы показать, как исходные данные увеличиваются или уменьшаются в результате ряда изменений.
Первый и последний столбцы в обычной каскадной диаграмме показывают суммарные значения. Промежуточные колонки являются плавающими и обычно показывают положительные или отрицательные изменения, происходящие от одного периода к другому, а в конечном итоге общий результат, т.е. суммарное значение. Как правило, колонки окрашены в разные цвета, чтобы наглядно выделить положительные и отрицательные значения. Далее в этой статье мы расскажем о том, как сделать промежуточные столбцы «плавающими».
Диаграмма «Водопад» также называется «Мост«, поскольку плавающие столбцы создают подобие моста, соединяющего крайние значения.
Эти диаграммы очень удобны для аналитических целей. Если вы хотите оценить прибыль компании или доходы от производства продукции, сделать анализ продаж или просто увидеть, как изменилось количество ваших друзей в Facebook за год, каскадная диаграмма в Excel — это то, что вам необходимо.
Как построить каскадную диаграмму (Мост, Водопад) в Excel?
Не тратьте время на поиск каскадной диаграммы в Excel – её там нет. Проблема в том, что в Excel просто нет готового шаблона такой диаграммы. Однако, не сложно создать собственную диаграмму, упорядочив свои данные и используя встроенную в Excel гистограмму с накоплением.
Примечание переводчика: В Excel 2016 Microsoft наконец-то добавила новые типы диаграмм, и среди них Вы найдете диаграмму «Водопад».
Для лучшего понимания, давайте создадим простую таблицу с положительными и отрицательными значениями. В качестве примера возьмем объем продаж. Из таблицы, представленной ниже, видно, что на протяжении нескольких месяцев продажи то росли, то падали относительно начального уровня.
Диаграмма «Мост» в Excel отлично покажет колебания продаж за взятые двенадцать месяцев. Если сейчас применить гистограмму с накоплением к конкретно этим значениям, то ничего похожего на каскадную диаграмму не получится. Поэтому, первое, что нужно сделать, это внимательно переупорядочить имеющиеся данные.
Шаг 1. Изменяем порядок данных в таблице
Первым делом, добавим три дополнительных столбца к исходной таблице в Excel. Назовём их Base, Fall и Rise. Столбец Base будет содержать вычисленное исходное значение для отрезков спада (Fall) и роста (Rise) на диаграмме. Все отрицательные колебания объёма продаж из столбца Sales Flow будут помещены в столбец Fall, а положительные – в столбец Rise.
Также я добавил строку под названием End ниже списка месяцев, чтобы рассчитать итоговый объем продаж за год. На следующем шаге мы заполним эти столбцы нужными значениями.
Шаг 2. Вставляем формулы
Лучший способ заполнить таблицу – вставить нужные формулы в первые ячейки соответствующих столбцов, а затем скопировать их вниз в смежные ячейки, используя маркер автозаполнения.
Смысл этой формулы в том, что если значение в ячейке E4 меньше либо равно нулю, то отрицательное значение будет показано как положительное, а вместо положительного значения будет показан ноль.
Замечание: Если Вы хотите, чтобы все значения в каскадной диаграмме лежали выше нуля, то необходимо ввести знак минус (-) перед второй ссылкой на ячейку Е4 в формуле. Минус на минус даст плюс.
Эта формула рассчитывает исходные значения, которые поднимут отрезки роста и спада на соответствующие высоты на диаграмме.
Шаг 3. Создаём стандартную гистограмму с накоплением
Теперь все нужные данные рассчитаны, и мы готовы приступить к построению диаграммы:
Появится диаграмма, пока ещё мало похожая на каскадную. Наша следующая задача – превратить гистограмму с накоплением в диаграмму «Водопад» в Excel.
Шаг 4. Преобразуем гистограмму с накоплением в диаграмму «Водопад»
Пришло время раскрыть секрет. Для того, чтобы преобразовать гистограмму с накоплением в диаграмму «Водопад», Вам просто нужно сделать значения ряда данных Base невидимыми на графике.
После того, как голубые столбцы стали невидимыми, остаётся только удалить Base из легенды, чтобы на диаграмме от этого ряда данных не осталось и следа.
Шаг 5. Настраиваем каскадную диаграмму в Excel
В завершение немного займёмся форматированием. Для начала я сделаю плавающие блоки ярче и выделю начальное (Start) и конечное (End) значения на диаграмме.
Замечание: Другой способ изменить заливку и контур столбцов на диаграмме – открыть панель Формат ряда данных (Format Data Series) или кликнуть по столбцу правой кнопкой мыши и в появившемся меню выбрать параметры Заливка (Fill) или Контур (Outline).
Замечание: Если разница между размерами столбцов очевидна, а точные значения точек данных не так важны, то подписи можно убрать, но тогда следует показать ось Y, чтобы сделать диаграмму понятнее.
Когда закончите с подписями столбцов, можно избавиться от лишних элементов, таких как нулевые значения и легенда диаграммы. Кроме того, можно придумать для диаграммы более содержательное название. О том, как в Excel добавить название к диаграмме, подробно рассказано в одной из предыдущих статей.
Каскадная диаграмма готова! Она значительно отличается от обычных типов диаграмм и предельно понятна, не так ли?
Теперь Вы можете создать целую коллекцию каскадных диаграмм в Excel. Надеюсь, для вас это будет совсем не сложно. Спасибо за внимание!
Создание каскадной диаграммы
Каскадная диаграмма показывает нарастающий итог по мере добавления или вычитания значений. Это помогает понять, как серия положительных и отрицательных значений влияет на исходную величину (например, чистую прибыль).
Столбцы обозначены цветом, чтобы можно было быстро отличить положительные значения от отрицательных. Столбцы начального и конечного значений часто начинаются с горизонтальной оси,в то время как промежуточные значения являются плавающими столбцами. Из-за такого вида каскадные диаграммы также часто называют диаграммами моста.
Создание каскадной диаграммы
Щелкните Вставка > Вставить каскадную или биржевую диаграмму > Каскадная.
Для создания каскадной диаграммы также можно использовать вкладку Все диаграммы в разделе Рекомендуемые диаграммы.
Совет: На вкладках Конструктор и Формат можно настроить внешний вид диаграммы. Если эти вкладки не отображаются, щелкните в любом месте каскадной диаграммы, и на ленте появится область Работа с диаграммами.
Итоги и промежуточные итоги с началом на горизонтальной оси
Если данные содержат значения, которые считаются итогами или итогами, например «Чистый доход», их можно настроить так, чтобы они начинались с горизонтальной оси с нуля и не «плавали».
Дважды щелкните точку данных, чтобы открыть области задач Формат точки данных, и установите в поле Установить как итог.
Примечание: Если щелкнуть столбец один раз, будет выбран ряд данных, а не точка данных.
Чтобы снова сделать столбец плавающим, снимите флажок Задать как итог.
Совет: Итоги также можно задать, щелкнув правой кнопкой мыши точку данных и выбрав в контекстном меню пункт Задать как итог.
Отображение и скрытие соединительных линий
Соединительные линии соединяют конец предыдущего столбца c началом следующего, помогая отобразить поток данных на диаграмме.
Чтобы скрыть соединитеальные линии, щелкните правой кнопкой мыши ряд данных, чтобы открыть области задач Формат ряда данных, и скроем поле Показать соединитеальные линии.
Чтобы снова отобразить эти линии, установите флажок Отображать соединительные линии.
Совет: В легенде диаграммы точки данных сгруппированы по типам: Увеличение, Уменьшение и Итог. Если щелкнуть легенду диаграммы, на диаграмме будут выделены все столбцы, соответствующие выбранной группе.
Вот как можно создать каскадную диаграмму в Excel для Mac:
На вкладке Вставка нажмите кнопку Каскадная 
(значок каскадной) и выберите каскадная.
Примечание: На вкладках Конструктор и Формат можно настроить внешний вид диаграммы. Если эти вкладки не отображаются, щелкните в любом месте каскадной диаграммы, чтобы отобразить их на ленте.
Как построить диаграмму «водопад» (waterfall)
Особенность такой диаграммы том, что:
В повседневной жизни такие диаграммы используются обычно в следующих случаях:
Способ 1. Самый простой: встроенный тип в Excel 2016 и новее
В результате мы получим практически готовую уже диаграмму:
Сразу же можно настроить желаемые цвета заливки для положительных и отрицательных столбцов. Удобнее всего это сделать, выделив соответствующие ряды Увеличение и Уменьшение прямо в легенде и, щёлкнув по ним правой кнопкой мыши, выбрать команду Заливка (Fill) :
После добавления строк с итогами останется выделить на диаграмме появившиеся итоговые колонки (сделать два последовательных одиночных щелчка по столбцу) и, щёлкнув правой кнопкой мыши, выбрать команду Установить в качестве итога (Set as total) :
Выбранный столбец «приземлится» на ось Х и автоматически поменяет цвет на серый.
Способ 2. Универсальный: невидимые столбцы
Если у вас Excel 2013 или более древние версии (2010, 2007 и т.д.), то описанный выше способ вам не подойдёт. Придется идти обходным путем и выпиливать недостающую каскадную диаграмму из обычной гистограммы с накоплением (суммированием столбиков друг на друга).
Хитрость тут заключается в использовании прозрачных столбцов-подпорок, приподнимающих наши красные и зеленые ряды данных на нужную высоту:
Для построения такой диаграммы нам потребуется добавить к исходным данным еще несколько вспомогательных колонок с формулами:
К сожалению, предыдущий способ адекватно работает только для положительных значений. Если хотя бы на каком-то участке наш водопад уходит в отрицательную область, то сложность задачи возрастает в разы. В этом случае необходимо будет формулами просчитать каждый ряд (пустышки, зеленые и красные) отдельно для отрицательной и положительной частей:
Чтобы не сильно мучиться и не изобретать велосипед, готовый шаблон для такого случая можно скачать в заголовке этой статьи.
Способ 4. Экзотический: полосы повышения-понижения
Легко сообразить, что если убрать линии графиков и оставить на диаграмме только полосы повышения-понижения, то мы получим все тот же «водопад».
Для такого построения нам потребуется добавить к нашей таблице еще два дополнительных столбца с простыми формулами, которые расчитают положение двух требуемых невидимых графиков:
Теперь добавим к нашей диаграмме полосы повышения-понижения:
Диаграмма после этого начнёт выглядеть примерно так:
В старых версиях Excel для такого исправления приходилось использовать команду на Visual Basic:
При желании можно, конечно, поиграться со значением параметра GapWidth, чтобы добиться нужной величины зазора:
Как построить диаграмму водопад в экселе
Создать диаграмму водопада или моста в Excel
Каскадная диаграмма, также называемая мостовой диаграммой, представляет собой особый тип столбчатой диаграммы, она помогает вам определить, как на начальное значение влияет увеличение и уменьшение промежуточных данных, что приводит к окончательному значению.
На каскадной диаграмме столбцы выделены разными цветами, поэтому вы можете быстро просмотреть положительные и отрицательные числа. Столбцы первого и последнего значений начинаются на горизонтальной оси, а промежуточные значения представляют собой плавающие столбцы, как показано ниже.
Создать диаграмму водопада в Excel 2016 и более поздних версиях
В Excel 2016 и более поздних версиях была представлена новая встроенная диаграмма водопада. Итак, вы можете быстро и легко создать эту диаграмму, выполнив следующие шаги:
1. Подготовьте свои данные и рассчитайте окончательный чистый доход, как показано на скриншоте ниже:
2. Выберите диапазон данных, на основе которого вы хотите создать диаграмму водопада, а затем щелкните Вставить > Вставить водопад, раструб, Сток, Поверхность, или радарная карта > Водопад, см. снимок экрана:
3. А теперь на лист вставлен график, см. Снимок экрана:
4. Затем вы должны установить значение чистого общего дохода, чтобы начать на горизонтальной оси с нуля, дважды щелкните последнюю точку данных, чтобы выбрать только столбец, а затем щелкните правой кнопкой мыши, выберите Форматировать точку данных вариант, см. снимок экрана:
5. Затем в открытом Форматировать точку данных панель, проверьте Установить как общее вариант под Варианты серий кнопка, теперь столбец итоговых значений настроен так, чтобы он начинался с горизонтальной оси, а не с плавающей точки, см. снимок экрана:
Чаевые: Чтобы получить этот результат, вы также можете просто выбрать Установить как Итого вариант из меню правой кнопки мыши. Смотрите скриншот: 
6. Наконец, чтобы сделать график более профессиональным:
Чтобы раскрасить другие столбцы точек данных по очереди с помощью этой операции. И вы получите диаграмму водопада, как показано на скриншоте ниже:
Создание диаграммы водопада в Excel 2013 и более ранних версиях
Если у вас есть Excel 2013 и более ранние версии, Excel не поддерживает эту функцию диаграммы водопада, чтобы вы могли использовать ее напрямую, в этом случае вам следует применить приведенный ниже метод шаг за шагом.
Создайте вспомогательные столбцы для исходных данных:
1. Во-первых, вы должны переставить диапазон данных, вставить три столбца между двумя исходными столбцами, дать им имена заголовков как Base, Down и Up. Смотрите скриншот:
2. Затем введите формулу: = ЕСЛИ (E2 в ячейку C2 и перетащите дескриптор заполнения вниз в ячейку C11, вы получите все отрицательные числа из столбца E, которые будут отображаться как положительные числа, а все положительные числа будут отображаться как 0. Смотрите снимок экрана:
3. Продолжайте вводить эту формулу: = ЕСЛИ (E2> 0, E2,0) в ячейку D2, затем перетащите дескриптор заполнения вниз в ячейку D11, теперь вы получите результат, что если ячейка E2 больше 0, все положительные числа будут отображаться как положительные, а все отрицательные числа отображаются как 0. См. Скриншот:
4. Затем введите эту формулу: = B2 + D2-C3 в ячейку B3 базового столбца и перетащите маркер заполнения в ячейку B12, см. снимок экрана:
Вставьте диаграмму с накоплением и отформатируйте ее как диаграмму водопада
5. После создания вспомогательных столбцов выберите диапазон данных, но исключите столбец Amount, см. Снимок экрана:
6, Затем нажмите Вставить > Вставить столбец или гистограмму > Столбец с накоплением, и диаграмма вставлена, как показано на следующих снимках экрана:
 |  |  |
7. Затем вам нужно отформатировать столбчатую диаграмму с накоплением как диаграмму водопада, пожалуйста, нажмите на базовую серию, чтобы выбрать их, а затем щелкните правой кнопкой мыши и выберите Форматировать ряд данных вариант, см. снимок экрана:
8. В открытом Форматировать ряд данных панель, под Заливка и линия значок, выберите Без заливки и Нет линии из Заполнять и Граница разделы отдельно, см. снимок экрана:
9. Теперь столбцы серии Base станут невидимыми, просто удалите Base из легенды диаграммы, и вы получите диаграмму, как показано на скриншоте ниже:
10. Затем вы можете отформатировать столбец серий вниз и вверх с помощью определенных цветов заливки, которые вам нужны, вам просто нужно нажать на серию вниз или вверх, а затем нажать Формат > Заливка формыи выберите нужный вам цвет, см. снимок экрана:
11. На этом этапе вы должны отобразить последнюю точку данных о чистом доходе, дважды щелкнуть последнюю точку данных, а затем щелкнуть Формат> Заливка формы, выбрать один цвет, который вам нужен, чтобы отобразить его, см. Снимок экрана:
12. Затем дважды щелкните любой из столбцов диаграммы, чтобы открыть Форматировать точку данных панель, под Варианты серий значок, измените Ширина зазора меньше, например 10%, см. снимок экрана:
13. Наконец, дайте имя диаграмме, и теперь вы успешно получите диаграмму водопада, см. Снимок экрана:
Внимание: Иногда вам может понадобиться добавить метки данных к столбцам. Пожалуйста, сделайте следующее:
1. Выберите серию, к которой вы хотите добавить метку, затем щелкните правой кнопкой мыши и выберите Добавить метки данных вариант, см. снимок экрана:
2. Повторите операцию для другой серии и удалите ненужные нулевые значения из столбцов, и вы получите следующий результат:
3. Теперь вы должны изменить положительные значения на отрицательные для столбца «Вниз», щелкните «Вниз», щелкните правой кнопкой мыши и выберите Форматирование меток данных вариант, см. снимок экрана:
4. в Форматирование меток данных панель, под Число раздел, тип -Генеральная в Код формата текстовое поле, а затем щелкните Добавить кнопку см. скриншоты:
 | |  |
5. Теперь вы получите диаграмму с метками данных, как показано на скриншоте ниже:
