Как сделать таблицу отклонений

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

waterfall1.png

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

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

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

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

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

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

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

waterfall2.png

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

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

waterfall3.png

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

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

waterfall4.png

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

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

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

waterfall5.png

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

waterfall6.png 

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

waterfall7.png 

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

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

waterfall8.png

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

waterfall9.png 

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

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

waterfall10.png

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

waterfall11.png 

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


Источник: http://www.planetaexcel.ru/techniques/4/163/

Закрыть ... [X]

Нормальное давление и пульс у взрослого человека Что делать если на лице следы прыщей

Как сделать таблицу отклонений Налоговый учет и требования ПБУ 18/02 по расчету
Как сделать таблицу отклонений РАЗВИТИЕ РЕБЁНКА В 8 МЕСЯЦЕВ Рост, вес,рацион
Как сделать таблицу отклонений Опросник для родителей Анализ семейных
Как сделать таблицу отклонений Как построить диаграмму водопад (waterfall)
Как сделать таблицу отклонений ГЛАВА 13 Непараметрическая статистика
Как сделать таблицу отклонений Глава 3 Средняя геометрическая
Как сделать таблицу отклонений M Ольга Георгиевна Крысина
Анекдоты про мультфильмы Болит зуб под пломбой - причины и методы лечения Грибок ногтей Лечение грибка на ногах и ногтях Как снять повышенную чувствительность зубов Картинки с изображением Одежды и Обуви. Обсуждение на LiveInternet