Как построить водопад в excel
График водопада в Excel
Что вы имеете в виду под диаграммой водопада в Excel?
Каскадная диаграмма в Excel также известна как Мостовая диаграмма в Excel, которая представляет собой особый тип столбчатой диаграммы, которая используется для отображения того, как начальная позиция определенного ряда данных изменяется со временем, будь то рост или уменьшение, первый столбец в каскадная диаграмма — это первое значение, а последний столбец в каскадной диаграмме — это окончательное значение, и в сумме они представляют общее значение.
Объяснил
Диаграмма Waterfall excel была представлена в 2016 году. На диаграмме показано, как значение увеличивается или уменьшается в результате серии изменений. По умолчанию положительные и отрицательные значения имеют цветовую кодировку. В 2013 или более ранних версиях Excel нет типа диаграммы «Водопад». Однако в этой статье вы также узнаете, как создать диаграмму водопада, если вы используете старую версию Excel.
Использование диаграммы водопада Excel
Каскадная диаграмма широко используется для визуализации финансовых отчетов, сравнения доходов и анализа продаж или стоимости продукта за определенный период времени. Он также используется для анализа прибылей и убытков и визуализации запасов. Они стали популярными в конце 20-х гг. th столетие, когда McKinsey and Company впервые представили их на презентации своим клиентам.
Как создать диаграмму водопада в Excel 2016?
Теперь, например, вы хотите визуализировать данные на диаграмме водопада.
Вы можете выбрать ячейки A3: B14, щелкнуть тип диаграммы и выбрать диаграмму водопада.
Диаграмма водопада для этих данных будет выглядеть так:
На диаграмме будет отображаться доход, полученный за каждый месяц года.
Вы также можете построить аналогичную диаграмму, используя более старые версии Excel. Посмотрим, как это сделать.
Сначала создайте пять столбцов A: E — (1) Месяцы (2) Уменьшение (3) Увеличение (4) Доход.
Начальное значение будет в 3 rd ряд и конец в 16 th ряд, как показано ниже.
Для столбца «Уменьшить» используйте следующий синтаксис —
= ЕСЛИ (E4 = 0, E4, 0) для ячейки D4 и так далее.
Для столбца Base используйте следующий синтаксис —
= B3 + D3 — C4 для ячейки B4 и так далее.
Данные будут выглядеть так —
Теперь выберите ячейки A2: E16 и щелкните диаграммы.
Нажмите «Столбец», а затем постройте диаграмму с накоплением столбцов в Excel.
График будет выглядеть так.
Теперь вы можете изменить цвет базовых столбцов на прозрачный или без заливки, и диаграмма превратится в диаграмму водопада, как показано ниже.
Создание диаграммы водопада в Excel более ранних версий 2007, 2010, 2013
Предположим, у вас есть чистый денежный поток вашей компании за каждый месяц года. Теперь вы хотите увидеть этот денежный поток на графике водопада, чтобы лучше визуализировать денежный поток в течение года и увидеть, какой период столкнулся с наибольшим кризисом. Данные о чистом денежном потоке показаны ниже.
Чтобы построить эту диаграмму, просто выберите ячейки B3: C16 и щелкните водопадную диаграмму на графике.
Если у вас более ранняя версия Excel, вы можете использовать альтернативный метод построения графика.
Во-первых, сделайте пять столбцов: «Время», «База», «Уменьшение», «Увеличение» и «Чистый денежный поток», как показано ниже.
Теперь заполните данные для Start. Базовое значение будет аналогично чистому денежному потоку, уменьшение и увеличение будет равно 0.
Для января увеличение будет выражено как —
= ЕСЛИ (F23> = 0, F23, 0) для ячейки E23.
Для января снижение будет выражено как —
График будет выглядеть так.
После того, как диаграмма будет подготовлена, вы можете установить для базовой заливки значение «Нет заливки», и вы получите эту диаграмму, как показано ниже. Также вы можете изменить цвет полос начала и конца.
Вы можете настроить толщину полос и промежутков между ними для лучшей визуализации, используя следующие шаги:
Теперь, наконец, ваша диаграмма будет выглядеть так.
На диаграмме вы можете заметить, что чистый денежный поток увеличился в течение года, а чистый денежный поток был максимальным в июле. Однако в период с августа по сентябрь снижение было огромным.
Как построить водопад в excel
Создать диаграмму водопада или моста в 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. Теперь вы получите диаграмму с метками данных, как показано на скриншоте ниже:
Скачать образец файла Waterfall Chart
Видео: Создание диаграммы водопада в Excel
Лучшие инструменты для работы в офисе
Exceltip
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
Диаграмма водопад (waterfall chart) в Excel
Водопад диаграмма (waterfall chart) является одной из форм визуализации данных, которая показывает совокупный эффект последовательно введенных положительных и отрицательных значений. Также иногда можно встретить название bridge chart, или «мост».
Обычно водопады используют для понимания того, как начальное значение зависит от ряда промежуточных положительных и отрицательных значений. Чаще всего начальное и конечное значение отображается в виде целого столбца, а промежуточный – в виде плавающих. В зависимости от положительного или негативного влияния столбца, цвет его различается.
Выглядит она следующим образом:
Итак, посмотрим, как же можно построить диаграмму, похожую на водопад.
Подготовка данных
Изначально у вас имеются данные, примерно похожие на таблицу ниже. Чтобы построить водопад, вам предстоит привести их в должный вид.
Водопад – это обычная диаграмма, отформатированная определенным образом. «Пустоты» под промежуточными значениями – это тоже ряды данных, но только без заливки. Таким образом, нам нужно разбить данные исходной таблицы на четыре колонки: крайние и прозрачные колонки, положительные значения и отрицательные значения. Наша таблица примет вид:
Где имеются такие формулы
Наиболее сложной для понимания является логика формул, отвечающих за прозрачные колонки – в зависимости от повышения или понижения промежуточной колонки, Excel выбирает на какую высоту поднять или опустить пустую колонку.
Для большей наглядности, добавим коннекторы между промежуточными значениями, они улучшат читаемость диаграммы за счет установки точки отсчета колонки.
Тут все просто, ячейка G3 суммирует значения ячеек C3:E3, соответственно формула в ней будет =СУММ(C3:E3). Ячейка G4 копирует значение ячейки G3.
Создание диаграммы Водопад
Осталось самое простое – построить диаграмму. Выделяем ячейки A1:A6 (да, пустую ячейку тоже включаем), жмем клавишу Ctrl и выделяем ячейки C1:J6, таким образом у вас будет выделено две области.
Щелкаем правой кнопкой по любому ряду данных, из всплывающего меню выбираем Изменить тип диаграммы для ряда. В появившемся диалоговом окне, меняем ряды данных соответствующие коннекторам на График.
На диаграмме щелкаем правой кнопкой по ряду данных Конектор1, из всплывающего меню выбираем Формат ряда данных, в правой панели во вкладке Линия устанавливаем формат, чтобы получилась пунктирная линия черного цвета. Повторяем эти действия для всех рядов данных отвечающих за коннекторы.
Далее воспользуемся принципом условного форматирования для диаграмм, который был описан в одном из предыдущих статей. Крайние ряды данных заливаем в серый, Зеленый и красный заливаем соответствующими цветами, у Прозрачного убираем заливку вовсе. Щелкаем правой кнопкой мыши по ряду данных, выбираем Формат ряда данных. В правой панели во вкладке Заливка и граница устанавливаем необходимые опции заливки для конкретного ряда данных.
В принципе, наша диаграмма водопад готова.
Осталось добавить кое-какие штрихи в форматирование. Убрать лишние элементы: легенду, линии сетки. Дать диаграмме внятное имя. Добавить подписи данных. В добавок, я уменьшил боковой зазор между колонками. Все наши злоключения должны были привести нас к следующему результату:
Создание каскадной диаграммы
Каскадная диаграмма показывает нарастающий итог по мере добавления или вычитания значений. Это помогает понять, как серия положительных и отрицательных значений влияет на исходную величину (например, чистую прибыль).
Столбцы обозначены цветом, чтобы можно было быстро отличить положительные значения от отрицательных. Столбцы начального и конечного значений часто начинаются с горизонтальной оси,в то время как промежуточные значения являются плавающими столбцами. Из-за такого вида каскадные диаграммы также часто называют диаграммами моста.
Создание каскадной диаграммы
Щелкните Вставка > Вставить каскадную или биржевую диаграмму > Каскадная.
Для создания каскадной диаграммы также можно использовать вкладку Все диаграммы в разделе Рекомендуемые диаграммы.
Совет: На вкладках Конструктор и Формат можно настроить внешний вид диаграммы. Если эти вкладки не отображаются, щелкните в любом месте каскадной диаграммы, и на ленте появится область Работа с диаграммами.
Итоги и промежуточные итоги с началом на горизонтальной оси
Если данные содержат значения, которые считаются итогами или итогами, например «Чистый доход», их можно настроить так, чтобы они начинались с горизонтальной оси с нуля и не «плавали».
Дважды щелкните точку данных, чтобы открыть области задач Формат точки данных, и установите в поле Установить как итог.
Примечание: Если щелкнуть столбец один раз, будет выбран ряд данных, а не точка данных.
Чтобы снова сделать столбец плавающим, снимите флажок Задать как итог.
Совет: Итоги также можно задать, щелкнув правой кнопкой мыши точку данных и выбрав в контекстном меню пункт Задать как итог.
Отображение и скрытие соединительных линий
Соединительные линии соединяют конец предыдущего столбца c началом следующего, помогая отобразить поток данных на диаграмме.
Чтобы скрыть соединитеальные линии, щелкните правой кнопкой мыши ряд данных, чтобы открыть области задач Формат ряда данных, и скроем поле Показать соединитеальные линии.
Чтобы снова отобразить эти линии, установите флажок Отображать соединительные линии.
Совет: В легенде диаграммы точки данных сгруппированы по типам: Увеличение, Уменьшение и Итог. Если щелкнуть легенду диаграммы, на диаграмме будут выделены все столбцы, соответствующие выбранной группе.
Вот как можно создать каскадную диаграмму в Excel для Mac:
На вкладке Вставка нажмите кнопку Каскадная 
(значок каскадной) и выберите каскадная.
Примечание: На вкладках Конструктор и Формат можно настроить внешний вид диаграммы. Если эти вкладки не отображаются, щелкните в любом месте каскадной диаграммы, чтобы отобразить их на ленте.