Диаграмма "План-Факт"

Редкий менеджер в своей практике не сталкивается с необходимостью наглядного представления достигнутых результатов по сравнению с запланированными изначально. В разных компаниях я встречал много подобных диаграмм, называющихся "План-Факт", "Actual vs Budget" и т.д. Иногда их строят примерно так:

plan-fact-chart1.png

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

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

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

Диаграмма план-факт с полосами повышения-понижения

Включаются такие полосы на вкладке Конструктор - Добавить элемент диаграммы - Полосы повышения/понижения (Design - Add Chart Element - Up/Down Bars) в Excel 2013 или на вкладке Макет - Полосы повышения-понижения (Layout - Up-Down Bars) в Excel 2007-2010. По умолчанию они будут черно-белые, но можно легко изменить их цвет, щелкнув по ним правой кнопкой мыши и выбрав команду Формат полос повышения/понижения (Format Up/Down Bars). Очень рекомендую использовать полупрозрачную заливку, т.к. сплошная закрывает сами исходные графики.

К сожалению нет легкого встроенного способа регулировать ширину полос - для этого придется воспользоваться небольшим трюком.

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

waterfall10.png

Само собой, параметром (30) можно поиграться, чтобы получить нужную вам ширину экспериментальным путем.

Способ 2. Диаграмма с заливкой зоны между линиями плана и факта

Этот способ предполагает наглядную заливку (можно со шриховкой, например) области между графиками плана и факта:

Диаграмма план-факт с заливкой областей между графиками

Весьма эффектно, не так ли? Давайте попробуем это реализовать.

Для начала добавьте к нашей таблице еще один столбец (назовем его, допустим, Разница), где посчитаем формулой разность между фактом и планом:

plan-fact-chart4.png

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

plan-fact-chart5.png

На выходе должно получиться примерно так:

plan-fact-chart6.png

Следующим шагом выделим ряды План и Факт, скопируем их (Ctrl+C) и добавим в нашу диаграмму вставкой (Ctrl+V) - в нашем "бутерброде в разрезе" сверху должны появиться два новых "слоя":

plan-fact-chart7.png

Теперь переключим тип диаграммы для этих двух добавленных слоев в график. Для этого выделите по очереди каждый ряд, щелкните по нему правой кнопкой мыши и выберите команду Изменить тип диаграммы для ряда (Change Series Chart Type). В старых версиях Excel 2007-2010 дальше можно выбрать нужный тип диаграммы (График с маркерами), а в новом Excel 2013 появится диалоговое окно со всеми рядами, где нужный тип выбирается для каждого ряда из выпадающих списков:

plan-fact-chart8.png

После нажатия на ОК увидим уже похожую на то, что нам нужно картину:

plan-fact-chart9.png

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

plan-fact-chart10.png

По-моему, это сильно лучше чем столбики, нет?

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

 


15.12.2014 07:16:20
Николай, не устаю удивляться вашей изобретательности! Конечно это лучше чем столбики и даже лучше чем просто графики:). Спасибо вам большое за данный сайт!
15.12.2014 08:13:50
Не за что, Василий! Рад, что понравилось :)
01.01.2015 20:46:32
Однозначно лучше чем столбики:)  Визуально легче сравнивать области чем полосы понижения повышения. Спасибо за идею!
13.01.2015 14:37:53
Николай, спасибо!
Как всегда всё изящно :)
Но у меня никак не получается воспроизвести подписи данных в том виде, как у Вас. Раскройте секрет.
Как сделать, чтобы значения были снаружи ряда данных?
Жир. Спасибо огромное
19.03.2015 09:57:28
Спасибо, теперь подача данных более информативна и читабельна:)
27.03.2015 22:06:22
Доброго времени суток, Николай.
Очень хорошая статья, спасибо большое. В своей работе я ежемесячно использую диаграмму "Динамика ...(план-факт).
У Вас на первой "наглядной" диаграмме (разница) отклонение показано разными цветами столбцов.
Для того чтобы реализовать второй тип "наглядной" диаграммы с разной расцветкой заштрихованной области, в зависимости от характера  отклонения: (+ зелёный) (- красный), я использую вставку объекта и изменение его узлов.
Подскажите пожалуйста, есть ли возможность изменять цвет заштрихованной области, используя "механизм" из первой диаграммы.
Заранее спасибо за ответ.
Думаю что этот вариант легко можно решить добавлением не одного столбца с разницей, а трех: минимум, позитивное отклонение, негативное отклонение.
Тогда в минимум: =МИН(План; Факт)
Позитивное отклонение: =МАКС(0; Факт-План)
Негативно отклонение: =МАКС(0; План-Факт)

И в накопительной диаграмме области позитивного и негативного отклонения можно будет раскрасить как надо.
Картинки не видно, как заливаются? Чисто по расчетам они взаимоисключаемы.
11.10.2015 12:27:26
Извиняюсь, Анатолий.
Что-то возникли проблемы со вставкой картинки.
Проблема возникает на переходных областях (пересечениях) от негатива к позитиву.
Никита, я Вас понял, думаю надо пробовать с компенсационными столбцами и вспомогательными осями.
Но чисто логически в любом случае графики будут мешать друг другу, поэтому моя идея не прокатит)
Прошу прощения, что ввел Вас в заблуждение) Если у меня что нибудь получится то выложу.
30.07.2015 19:04:21
Николай, добрый день! Может быть вы расскажите как создавать диаграммы в виде спидометра? Спасибо.
14.10.2015 11:29:36
О, отличная идея - запишу видео в ближайшее время :)
30.08.2015 10:56:47
Николай, добрый день! Подскажите, как можно добавить примечания к данным на диаграмме. Диаграмма из двух кривых. Первая-поступление денег, вторая-платежи по договорам. Так вот к суммам платежей нужно примечания-номер договора+аванс, либо окончательный расчет.
14.10.2015 11:29:09
Алексей, в Excel 2013-2016 при добавлении подписей можно выбрать опцию "Из ячеек" и выделить ячейки с любыми данными (не обязательно теми, по которым она построена).
В более старых версиях для этого нужно использовать макросы или бесплатную надстройку XYChartLabeler.
15.02.2016 11:26:18
Всем привет, ширину полос можно также отрегулировать при помощи бокового зазора ряда.:)
31.03.2016 18:48:52
Если я делаю такую диаграмму на основе сводной таблицы то автоматически подтагивается "Разница" и ни чего не получается, а без сводной ни как нельзя, данные должны обновлятся.
10.04.2016 08:10:48
Николай, у меня таже проблема что и у Игоря, очень хочется сводную диаграмму такого плана следать для интерактивного отчета, но не получается..
10.04.2016 21:00:51
Игорь, Юлия, диаграммы по сводным таблицам имеют очень много ограничений.
На вашем месте, я бы вытащил данные из сводной в обычную таблицу с помощью функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ , а потом уже строил по обычной любые графики.
15.06.2016 12:37:41
Здравствуйте! У меня не активна кнопка Линии повышения и понижения в excel, как в 2007 так и в 2013. В чем проблема и как ее устранить? Очень надо! Спасибо!
25.06.2016 23:51:38
Здравствуйте! Подскажите, пожалуйста, как построить на одном графике несколько областей?
27.06.2016 09:50:32
Татьяна, каких областей? Подробнее, плиз.
07.02.2017 12:20:01
Добрый день!

Спасибо за диаграмму и описание! Уже несколько раз использовала, очень наглядно.
Сейчас появилась ошибка - делаю все тоже самое, но отрицательная разница на графике с накоплением уходит в минуса, как исправить?

Заранее спасибо.