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

Microsoft Excel предлагает множество встроенных типов диаграмм, в том числе гистограммы, линейчатые диаграммы, круговые и другие типы диаграмм. В этой статье мы подробно рассмотрим все детали создания простейших графиков, и кроме того, ближе познакомимся с особым типом диаграммы – каскадная диаграмма в Excel (аналог: диаграмма водопад). Вы узнаете, что представляет собой каскадная диаграмма и насколько полезной она может быть. Вы познаете секрет создания каскадной диаграммы в Excel 2010-2013, а также изучите различные инструменты, которые помогут сделать такую диаграмму буквально за минуту.

И так, давайте начнем совершенствовать свои навыки работы в Excel.

Примечание переводчика: Каскадная диаграмма имеет множество названий. Самые популярные из них: Водопад, Мост, Ступеньки и Летающие кирпичи, также распространены английские варианты – Waterfall и Bridge.

• Что такое диаграмма «Водопад»?
• Как построить каскадную диаграмму (Мост, Водопад) в Excel?

Что такое диаграмма «Водопад»?

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

Диаграмма «Водопад» – это особый тип диаграммы в Excel. Обычно используется для того, чтобы показать, как исходные данные увеличиваются или уменьшаются в результате ряда изменений.

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

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

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

Как построить каскадную диаграмму (Мост, Водопад) в Excel?

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

Примечание переводчика: В Excel 2016 Microsoft наконец-то добавила новые типы диаграмм, и среди них Вы найдете диаграмму «Водопад».

• Изменяем порядок данных в таблице
• Вставляем формулы
• Создаём стандартную гистограмму с накоплением
• Преобразуем гистограмму с накоплением в диаграмму «Водопад»
• Настраиваем каскадную диаграмму в Excel

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

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

Шаг 1. Изменяем порядок данных в таблице

Первым делом, добавим три дополнительных столбца к исходной таблице в Excel. Назовём их Base, Fall и Rise. Столбец Base будет содержать вычисленное исходное значение для отрезков спада (Fall) и роста (Rise) на диаграмме. Все отрицательные колебания объёма продаж из столбца Sales Flow будут помещены в столбец Fall, а положительные – в столбец Rise.

Также я добавил строку под названием End ниже списка месяцев, чтобы рассчитать итоговый объем продаж за год. На следующем шаге мы заполним эти столбцы нужными значениями.

Шаг 2. Вставляем формулы

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

1. Выбираем ячейку C4 в столбце Fall и вставляем туда следующую формулу:

   =IF(E40;E4;0)

   Это означает, что если значение в ячейке E4 больше нуля, то все положительные числа будут отображаться, как положительные, а отрицательные – как нули.

2. Используйте маркер автозаполнения, чтобы скопировать эту формулу вниз по столбцу.
3. Вставляем последнюю формулу в ячейку B5 и копируем ее вниз, включая строку End:

   =B4+D4-C5

   

   Эта формула рассчитывает исходные значения, которые поднимут отрезки роста и спада на соответствующие высоты на диаграмме.

Шаг 3. Создаём стандартную гистограмму с накоплением

Теперь все нужные данные рассчитаны, и мы готовы приступить к построению диаграммы:

1. Выделите данные, включая заголовки строк и столбцов, кроме столбца Sales Flow.
2. Перейдите на вкладку Вставка (Insert), найдите раздел Диаграммы (Charts).
3. Кликните Вставить гистограмму (Insert Column Chart) и в выпадающем меню выберите Гистограмма с накоплением (Stacked Column).

Появится диаграмма, пока ещё мало похожая на каскадную. Наша следующая задача – превратить гистограмму с накоплением в диаграмму «Водопад» в Excel.

Шаг 4. Преобразуем гистограмму с накоплением в диаграмму «Водопад»

Пришло время раскрыть секрет. Для того, чтобы преобразовать гистограмму с накоплением в диаграмму «Водопад», Вам просто нужно сделать значения ряда данных Base невидимыми на графике.

1. Выделяем на диаграмме ряд данных Base, щелкаем по нему правой кнопкой мыши и в контекстном меню выбираем Формат ряда данных (Format data series).В Excel 2013 в правой части рабочего листа появится панель Формат ряда данных (Format Data Series).
2. Нажимаем на иконку Заливка и границы (Fill & Line).
3. В разделе Заливка (Fill) выбираем Нет заливки (No fill), в разделе Граница (Border) – Нет линий (No line).

После того, как голубые столбцы стали невидимыми, остаётся только удалить Base из легенды, чтобы на диаграмме от этого ряда данных не осталось и следа.

Шаг 5. Настраиваем каскадную диаграмму в Excel

В завершение немного займёмся форматированием. Для начала я сделаю плавающие блоки ярче и выделю начальное (Start) и конечное (End) значения на диаграмме.

1. Выделяем ряд данных Fall на диаграмме и открываем вкладку Формат (Format) в группе вкладок Работа с диаграммами (Chart Tools).
2. В разделе Стили фигур (Shape Styles) нажимаем Заливка фигуры (Shape Fill).
3. В выпадающем меню выбираем нужный цвет.Здесь же можете поэкспериментировать с контуром столбцов или добавить какие-либо особенные эффекты. Для этого используйте меню параметров Контур фигуры (Shape Outline) и Эффекты фигуры (Shape Effects) на вкладке Формат (Format).Далее проделаем то же самое с рядом данных Rise. Что касается столбцов Start и End, то для них нужно выбрать особый цвет, причём эти два столбца должны быть окрашены одинаково.В результате диаграмма будет выглядеть примерно так:

   

Замечание: Другой способ изменить заливку и контур столбцов на диаграмме – открыть панель Формат ряда данных (Format Data Series) или кликнуть по столбцу правой кнопкой мыши и в появившемся меню выбрать параметры Заливка (Fill) или Контур (Outline).

3. Затем можно удалить лишнее расстояние между столбцами, чтобы сблизить их.
4. Дважды кликните по одному из столбцов диаграммы, чтобы появилась панель Формат ряда данных (Format Data Series)
5. Установите для параметра Боковой зазор (Gap Width) небольшое значение, например, 15%. Закройте панель.Теперь на диаграмме «Водопад» нет ненужных пробелов.

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

6. Выделите ряд данных, к которому нужно добавить подписи.
7. Щёлкните правой кнопкой мыши и в появившемся контекстном меню выберите Добавить подписи данных (Add Data Labels).Проделайте то же самое с другими столбцами. Можно настроить шрифт, цвет текста и положение подписи так, чтобы читать их стало удобнее.

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

Когда закончите с подписями столбцов, можно избавиться от лишних элементов, таких как нулевые значения и легенда диаграммы. Кроме того, можно придумать для диаграммы более содержательное название. О том, как в Excel добавить название к диаграмме, подробно рассказано в одной из предыдущих статей.

Каскадная диаграмма готова! Она значительно отличается от обычных типов диаграмм и предельно понятна, не так ли?

Теперь Вы можете создать целую коллекцию каскадных диаграмм в Excel. Надеюсь, для вас это будет совсем не сложно. Спасибо за внимание!

Урок подготовлен для Вас командой сайта office-guru.ru
Источник: /> Перевел: Антон Андронов

Правила перепечаткиЕще больше уроков по Microsoft Excel

Оцените качество статьи. Нам важно ваше мнение:

Все чаще и чаще встречаю в отчетности разных компаний и слышу просьбы от слушателей на тренингах объяснить как строится каскадная диаграмма отклонений — она же «водопад», она же «waterfall», она же «мост», она же «bridge» и т.д. Выглядит она примерно так:

Издали действительно похожа на водопад или навесной мост. Давайте с ней разберемся.

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

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

• потока наличности (приход-расход, cash flow)
• инвестиций (вкладываем деньги в проект и получаем от него прибыль)
• выполнения плана (крайний левый столбик в диаграмме — факт, крайний правый — план, вся диаграмма отображает наш процесс движения к желаемому результату)
• и т.д.

Есть несколько способов построения подобной нестандартной диаграммы «водопада» в Microsoft Excel.

Способ 1. Невидимые столбцы

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

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

Если выделить всю таблицу и создать обычную гистограмму с накоплением через Вставка — Диаграмма (Insert — Chart):

А потом выделить синие столбцы и сделать их невидимыми (по ним правой кнопкой мыши — Формат ряда — Заливка — Нет заливки), то мы как раз и получим то, что хотелось. 

В плюсах подобного способа — простота. В минусах — необходимость считать вспомогательный невидимый столбец. Причем, если диаграмма уходит в отрицательную область, то сложность формул возрастает, т.к. необходимо будет отдельно просчитать отрицательную и положительную части диаграммы:

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

Способ 2. Полосы повышения-понижения

Этот способ основан на использовании специального малоизвестного элемента плоских диаграмм (гистограмм и графиков) — Полос повышения-понижения (Up-Down Bars). Эти полосы попарно соединяют точки двух графиков, чтобы наглядно показать какая из двух точек выше-ниже:

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

Для создания «водопада» нужно выделить столбец с месяцами (для подписей по оси Х) и два дополнительных столбца График 1 и 2 и посроить для начала обычный график через Вставка — Диаграмма — График (Insert — Chart — Line):

Теперь добавим к нашей диаграмме полосы повышения-понижения:

• В Excel 2003 для этого необходимо щелкнуть правой кнопкой мыши по любому из графиков и выбрать Формат ряда (Format Series) — Полосы повышения-понижения (Up-Down Bars)
• В Excel 2007 и новее — перейти на вкладку Макет (Layout) — Полосы повышения-понижения (Up-Down Bars)

Осталось выделить графики и сделать их прозрачными, щелкнув по ним по очереди правой кнопкой мыши и выбрав команду Формат ряда (Format series). Аналогичным образом можно изменить и стандартные черно-белые цвета полос повышения-понижения, чтобы получить в итоге более приятную картинку:

К сожалению, нет встроенной возможности быстро изменить ширину полос, а по умолчанию они довольно «худенькие», как можно заметить. Для исправления этого нюанса воспользуемся VBA:

1. Выделите построенную диаграмму
2. Нажмите сочетание клавиш Alt+F11, чтобы попасть в редактор Visual Basic
3. Нажмите сочтетание клавиш Ctrl+G, чтобы открыть панель прямого ввода команд и отладки Immediate
4. Скопируйте и вставьте туда вот такую команду: ActiveChart.ChartGroups(1).GapWidth = 30 и нажмите Enter:

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

Ссылки по теме

• Как в Excel построить диаграмму-шкалу (bullet chart) для визуализации KPI  
• Новые возможности диаграмм в Excel 2013
• Как в Excel создать интерактивную «живую» диаграмму

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Скачать файл с примером диаграммы водопад.

Диаграмма «Водопад» (Waterfall, Мост, Bridge, Каскадная диаграмма) применяется тогда, когда нужно показать влияние на какое-то значение ряда промежуточных факторов с указанием величины и направленности влияния (на сколько выросла/уменьшались величина за счет определенного фактора). Этот вариант визуализации часто используется в факторном и план-факт анализе, а значит умение строить такую диаграмму пригодится многим, кто имеет дело с анализом и графическим представлением данных в Excel. 

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

Шаг 1. Определение исходных данных для построения

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

1) Исходная величина анализируемого показателя (для примера возьмем остаток на расчетном счете на начало месяца);

2) Итоговая величина анализируемого показателя (остаток на расчетном счете на конец месяца);

3) Изменения анализируемого показателя под влиянием различных факторов (движения средств на расчетном счете в разрезе назначений платежей) 

Шаг 2. Преобразование исходных данных и доп. расчеты

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

1) Ряд исходных данных

2) Ряд итоговых данных

3) Невидимый столбец

4) Положительные изменения (приросты)

5) Отрицательные изменения (снижения) 

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

Для удобства в исходных данных все поступления оставим положительными числами, а все расходования сделаем отрицательными. Чтобы быстро превратить число в отрицательное нужно умножить его на «-1». Самый быстрый способ — специальная вставка. Как пользоваться — смотри здесь. 

В столбцах «Ряд исходных данных» и «Ряд итоговых данных» указываем в нужной строке соответствующую величину. 

В столбце «Приросты» во всех строках кроме исходной и итоговой пишем формулу «=ЕСЛИ(C5>0;C5;0)», где C5 — величина изменения в соответствующей строке.  Эта формула позволит записать в столбце только величины приростов. 

 В столбце «Снижения» во всех строках кроме исходной и итоговой пишем формулу «=ЕСЛИ(C50;СУММ($C$4:C4);СУММ($C$4:C4)+C5)», где C5 — величина изменения в соответствующей строке, а C4 — ячейка с исходной величиной.  

Эта формула проверяет, прирост или снижение дала нам величина в текущей строке. Если прирост — то размер невидимого столбца равен нарастающему итогу всех изменений до текущей строки (обращаем внимание на закрепление ячейки в функции СУММ($C$4:C4) ,  если снижение — то размер невидимого столбца равен нарастающему итогу всех изменений до текущей строки за вычетом величины снижения.

Шаг 3. Построение диаграммы

После того, как все столбцы заполнены, можно строить диаграмму. Для этого выделяем вместе с шапкой столбец с названиями категорий (в его шапке должно быть пусто, иначе диаграмма не поймет, что это столбец подписей оси Х), а также все расчетные столбцы и выбираем для построения «Гистограмму с накоплением».  

Шаг 4. Форматирование и настройка диаграммы

Чтобы диаграмма приняла нужный вид мы должны ее настроить. 

1) Скрыть невидимый столбец. Для этого выбираем ряд данных «Невидимый столбец», кликаем на нем правой кнопкой мыши, выбираем «Формат ряда данных» и в появившейся панели в блоке «Заливка и границы» отмечаем пункт «Нет заливки» 

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

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

 Остальные настройки можете применять по своему вкусу и в соответствии со своим видением построения хорошей диаграммы.  

Каскадные диаграммы в Excel 2016

Счастливые обладатели Excel 2016 будут рады узнать, что в этой версии разработчики наконец-то добавили такой тип диаграммы, как «Водопад». Правда, называется она «Каскадная диаграмма», но сути дела это не меняет.  

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

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

В появившейся панели отмечаем пункт «Установить в качестве итога».  

Еще одна причина установить себе последнюю версию Excel.

Поддержать наш проект и его дальнейшее развитие можно вот здесь.

Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot

С уважением, команда tDots.ru 

Похожие записи:

Как сделать график распределения в excel? Как сделать график выхода на работу в excel? Как сделать генерацию случайных чисел в excel? Как сделать вычисления в excel?