Полосы повышения и понижения 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).GapW >

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

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

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

Новый формат диаграммы в Excel ? Щелкните здесь для 5 минут бесплатные учебные о том, как форматирование диаграммы.

Определенных типов линии и линейчатые диаграммы доступны в плоскости линейчатые диаграммы с накоплением и гистограммы, графики, круговая диаграмма и линейчатые диаграммы круговые диаграммы, диаграммы с областями и биржевые диаграммы.

Предварительно определенных типов линии и линейчатые диаграммы, которые можно добавить в диаграмму

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

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

Линии проекции Доступно в области плоской и объемной диаграммы и графики, эти строки расширение из точки данных ось горизонтальной оси (категорий), чтобы было ясно, где заканчивается один маркер данных и следующего запуска маркер данных.

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

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

Добавление линий или полос

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

Откроется панель Работа с диаграммами с дополнительными вкладками Конструктор, Макет и Формат.

На вкладке Макет в группе Анализ выполните одно из следующих действий:

Нажмите кнопку линии и выберите нужный тип линии.

Примечание: Типы другую строку доступны для различных типов диаграмм.

Нажмите кнопку Понижения и нажмите кнопку Понижения.

Совет: Вы можете изменить формат ряда линии, линии проекции, коридор колебания и полосы повышения и понижения вверх, которые отображаются на диаграмме, щелкните правой кнопкой мыши строку или строку и выбрав Формат .

Удаление линий или полос

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

Откроется панель Работа с диаграммами с дополнительными вкладками Конструктор, Макет и Формат.

На вкладке Макет в группе Анализ нажмите кнопку линии или Понижения и нажмите кнопку Нет для удаления строки или погрешностей на диаграмме.

Совет: Вы также можете удалить линии или полосы сразу же после их добавления, нажав кнопку Отменить на панели быстрого доступа или клавиши CTRL+Z.

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

Добавление других линий

Это действие применяется в Microsoft Word для Mac только: В меню Вид выберите пункт Разметка страницы.

На диаграмме выберите ряд данных, который требуется добавить линию и откройте вкладку Конструктор диаграмм.

Например, щелкните одну из линий графика. Будут выделены все маркеры данных этого ряда.

Нажмите кнопку Добавить элемент диаграммы и нажмите кнопку Сетка.

Выберите параметр строки, которые вы и нажмите кнопку Дополнительные параметры сетки.

В зависимости от типа диаграммы некоторые параметры могут быть недоступны.

Удаление других линий

Это действие применяется в Microsoft Word для Mac только: В меню Вид выберите пункт Разметка страницы.

Щелкните диаграмму с линиями, а затем откройте вкладку Конструктор диаграмм.

Нажмите кнопку Добавить элемент диаграммы, выберите команду линии сетки и нажмите кнопку Дополнительные параметры сетки.

Выберите Нет линии.

Также можно щелкнуть линию и нажать кнопку DELETE.

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

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

Диаграмму Водопад построим в EXCEL 2010 с использованием стандартной диаграммой типа Гистограмма с накоплением.

Примечание: Начиная с версии 2016 года в EXCEL имеется стандартная каскадная диаграмма. Подробнее о ее построении можно прочитать ]]> в статье на сайте Microsoft ]]> .

Диаграмма водопад. Динамика показателя (+)

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

Сначала вычислим изменения за период. Увеличения и уменьшения разнесем по разным столбцам. Это нам позволит выделить цветом разнонаправленные изменения.

Также нам потребуется служебный столбец, который будет служить невидимой основой для столбцов-изменений (см. файл примера Лист Больше0 ).

Будем использовать Гистограмму с накоплением. В качестве рядов данных используем созданные выше столбцы (C, D, E).

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

Диаграмма водопад. Динамика показателя (+/-)

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

Вычислим изменение за период (столбец С). Для построения диаграммы также потребуются вспомогательные столбцы (см. файл примера Лист ВсеЗначения ).

В остальном построение диаграммы аналогично предыдущей задаче.

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

СОВЕТ: Для начинающих пользователей EXCEL советуем прочитать статью Основы построения диаграмм в MS EXCEL, в которой рассказывается о базовых настройках диаграмм, а также статью об основных типах диаграмм.

Анализ влияния факторов

В отличие от предыдущих 2-х задач, где анализировалось изменение значения показателя за несколько периодов, в этом разделе визуализируем влияние каждого фактора на полученное фактическое значение.

Пусть в начале года было задано плановое значение для прибыли = 140. В конце года было получено значение прибыли = 171. При этом известен вклад каждого из факторов (столбец С).

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

Эта диаграмма состоит из 3-х рядов данных. Первый ряд данных включает начальное и конечное значение прибыли, а также невидимые служебные столбцы. Для построения такого ряда проще всего сначала установить для столбцов диаграммы значение Нет заливки, а затем для крайнего правого и левого значений вручную установить нужный цвет (например, синий). Для этого нужно выделить на диаграмме столбцы ряда, через 1 сек выделить левый столбик, изменить его заливку. Затем тоже сделать для последнего столбика. Подробнее см. статью Гистограмма в MS EXCEL с накоплением.

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