Диаграмма "План-Факт"
Редкий менеджер в своей практике не сталкивается с необходимостью наглядного представления достигнутых результатов по сравнению с запланированными изначально. В разных компаниях я встречал много подобных диаграмм, называющихся "План-Факт", "Actual vs Budget" и т.д. Иногда их строят примерно так:
Неудобство такой диаграммы в том, что зритель должен попарно сравнивать столбцы плана и факта друг с другом, пытаясь удержать всю картину в голове, и гистограмма здесь, по-моему, не лучший вариант. Если уж и строить такую визуализацию, то однозначно нагляднее использовать графики для плана и факта. Но тогда перед нами встает задача наглядного попарного сравнения точек за одинаковые периоды и выделения разницы между ними. Давайте попробуем применить несколько удобных техник для этого.
Способ 1. Полосы повышения-понижения
Это наглядные прямоугольники, соединяющие попарно точки графиков плана и факта на нашей диаграмме. Причем их цвет зависит от того, выполнили мы план или нет, а размер показывает - на сколько:
Включаются такие полосы на вкладке Конструктор - Добавить элемент диаграммы - Полосы повышения/понижения (Design - Add Chart Element - Up/Down Bars) в Excel 2013 или на вкладке Макет - Полосы повышения-понижения (Layout - Up-Down Bars) в Excel 2007-2010. По умолчанию они будут черно-белые, но можно легко изменить их цвет, щелкнув по ним правой кнопкой мыши и выбрав команду Формат полос повышения/понижения (Format Up/Down Bars). Очень рекомендую использовать полупрозрачную заливку, т.к. сплошная закрывает сами исходные графики.
К сожалению нет легкого встроенного способа регулировать ширину полос - для этого придется воспользоваться небольшим трюком.
- Выделите построенную диаграмму
- Нажмите сочетание клавиш Alt+F11, чтобы попасть в редактор Visual Basic
- Нажмите сочтетание клавиш Ctrl+G, чтобы открыть панель прямого ввода команд и отладки Immediate
- Скопируйте и вставьте туда вот такую команду: ActiveChart.ChartGroups(1).GapWidth = 30 и нажмите Enter:
Само собой, параметром (30) можно поиграться, чтобы получить нужную вам ширину экспериментальным путем.
Способ 2. Диаграмма с заливкой зоны между линиями плана и факта
Этот способ предполагает наглядную заливку (можно со шриховкой, например) области между графиками плана и факта:
Весьма эффектно, не так ли? Давайте попробуем это реализовать.
Для начала добавьте к нашей таблице еще один столбец (назовем его, допустим, Разница), где посчитаем формулой разность между фактом и планом:
Теперь выделим одновременно столбцы с датами, планом и разницей (удерживая Ctrl) и построим диаграмму с областями с накоплением, используя вкладку Вставка (Insert):
На выходе должно получиться примерно так:
Следующим шагом выделим ряды План и Факт, скопируем их (Ctrl+C) и добавим в нашу диаграмму вставкой (Ctrl+V) - в нашем "бутерброде в разрезе" сверху должны появиться два новых "слоя":
Теперь переключим тип диаграммы для этих двух добавленных слоев в график. Для этого выделите по очереди каждый ряд, щелкните по нему правой кнопкой мыши и выберите команду Изменить тип диаграммы для ряда (Change Series Chart Type). В старых версиях Excel 2007-2010 дальше можно выбрать нужный тип диаграммы (График с маркерами), а в новом Excel 2013 появится диалоговое окно со всеми рядами, где нужный тип выбирается для каждого ряда из выпадающих списков:
После нажатия на ОК увидим уже похожую на то, что нам нужно картину:
Легко сообразить, что осталось только выделить синюю область и поменять у нее цвет заливки на прозрачный Нет заливки (No Fill). Ну, и навести общий блеск: добавить подписи, заголовок, удалить лишние элементы в легенде и т.д.
По-моему, это сильно лучше чем столбики, нет?
Ссылки по теме
- Как быстро добавить новые данные в диаграмму копированием
- Диаграмма-шкала (bullet chart) для отображения KPI
- Видеоурок по созданию проектной диаграммы Ганта в Excel
Как всегда всё изящно
Но у меня никак не получается воспроизвести подписи данных в том виде, как у Вас. Раскройте секрет.
Как сделать, чтобы значения были снаружи ряда данных?
Очень хорошая статья, спасибо большое. В своей работе я ежемесячно использую диаграмму "Динамика ...(план-факт).
У Вас на первой "наглядной" диаграмме (разница) отклонение показано разными цветами столбцов.
Для того чтобы реализовать второй тип "наглядной" диаграммы с разной расцветкой заштрихованной области, в зависимости от характера отклонения: (+ зелёный) (- красный), я использую вставку объекта и изменение его узлов.
Подскажите пожалуйста, есть ли возможность изменять цвет заштрихованной области, используя "механизм" из первой диаграммы.
Заранее спасибо за ответ.
Тогда в минимум: =МИН(План; Факт)
Позитивное отклонение: =МАКС(0; Факт-План)
Негативно отклонение: =МАКС(0; План-Факт)
И в накопительной диаграмме области позитивного и негативного отклонения можно будет раскрасить как надо.
Что-то возникли проблемы со вставкой картинки.
Проблема возникает на переходных областях (пересечениях) от негатива к позитиву.
Но чисто логически в любом случае графики будут мешать друг другу, поэтому моя идея не прокатит)
Прошу прощения, что ввел Вас в заблуждение) Если у меня что нибудь получится то выложу.
В более старых версиях для этого нужно использовать макросы или бесплатную надстройку XYChartLabeler.
На вашем месте, я бы вытащил данные из сводной в обычную таблицу с помощью функции , а потом уже строил по обычной любые графики.
Спасибо за диаграмму и описание! Уже несколько раз использовала, очень наглядно.
Сейчас появилась ошибка - делаю все тоже самое, но отрицательная разница на графике с накоплением уходит в минуса, как исправить?
Заранее спасибо.
ПОДСКАЖИТЕ как создать разрыв графика / диаграммы по оси Игрек?
иногда приходиться сопоставлять на графике совершенно несоизмеримые значения, поэтому возникла мысль разбить ось Y как-минимум на две области с разными масштабами, например от 0 до 100, цена деления 5, от 101 до 1000, цена деления 50,
вот такая задача, возможно ли ее решить с помощью PLEX'а
C уважением,
Сергей
Хотелось бы узнать можно ли в Excel находить точки пересечения хотя бы двух диапазонов данных.
Графически (линейные графики) видно что они пересекаются, но точное значение определить не могу.
Рылся в интернете. Нашел этот макрос, но он не работает. Ссылка внизу.
Не могли бы Вы подсказать в каком направлении идти, для решения подобной задачи. Может в программе есть встроенные функции или еще что-то.
Спасибо.
http:/tutorexcel.ru/makrosy-vba/tochka-peresecheniya-grafikov-v-excel/
Динамический План Факт анализ в Microsoft Excel
Не могу понять почему, но не получается воспользоваться 1-м способом - полосы понижения-повышения в меню неактивны.
Где можно скачать примеры план/факта по приходу денег?
ps. книга по PQ просто теперь шикарна! Распечатана и читается. Спасибо!