Содержание:
Редкий менеджер в своей практике не сталкивается с необходимостью визуализировать достигнутые результаты в сравнении с изначально запланированными. В разных компаниях я видел много подобных графиков под названием «План-Факт», «Факт vs Бюджет» и т. д. Иногда они строятся так:
Неудобство такой диаграммы в том, что зрителю приходится попарно сравнивать столбцы плана и факта, стараясь удержать в голове всю картину, и гистограмма здесь, на мой взгляд, не лучший вариант. Если уж строить такую визуализацию, то однозначно нагляднее использовать графики плана и факта. Но тогда перед нами стоит задача визуального попарного сравнения точек за одинаковые периоды и выделения разницы между ними. Давайте попробуем для этого несколько удобных приемов.
Метод 1. Полосы вверх-вниз
Это визуальные прямоугольники, соединяющие попарно точки плана и графиков фактов на нашей диаграмме. Причем их цвет зависит от того, выполнили мы план или нет, а размер показывает на сколько:
Такие полосы включены во вкладку Конструктор – Добавить элемент диаграммы – Полосы вверх/вниз (Дизайн — Добавить элемент диаграммы — Полосы вверх/вниз) в Excel 2013 или на вкладке Макет – полосы продвижения-уменьшения (Макет — полосы вверх-вниз) в Экселе 2007-2010. По умолчанию они будут черно-белыми, но вы можете легко изменить их цвет, щелкнув по ним правой кнопкой мыши и выбрав команду Формат полос вверх/вниз (Формат полос вверх/вниз). Очень рекомендую использовать полупрозрачную заливку, т.к. сплошная линия замыкает сами исходные графики.
К сожалению, простого встроенного способа регулировки ширины полос не существует – для этого придется воспользоваться небольшой хитростью.
- Выделить построенную диаграмму
- Нажмите сочетание клавиш Alt + F11чтобы попасть в редактор Visual Basic
- Нажмите сочетание клавиш Ctrl + Gоткрыть панель прямого ввода команд и отладки немедленная
- Скопируйте и вставьте туда следующую команду: ActiveChart.ChartGroups(1).GapWidth = 30 и нажмите Enter:
Конечно, параметром (30) можно поиграться, чтобы получить нужную вам ширину экспериментальным путем.
Способ 2. Диаграмма с заливкой зон между линиями плана и факта.
Этот метод предполагает визуальное заполнение (можно штриховкой, например) области между графиками плана и фактов:
Весьма впечатляюще, не так ли? Давайте попробуем это реализовать.
Сначала добавим в нашу таблицу еще один столбец (назовем его, скажем, разница), где вычисляем разницу между фактом и планом по формуле:
Теперь выделим одновременно столбцы с датами, планом и разницей (удерживая Ctrl) и построим диаграмму с участками накопленияиспользуя вкладку Вставить (Вставить):
Результат должен выглядеть примерно так:
Следующий шаг — выбор строк План и Факт, скопируйте их (Ctrl + C) и добавим к нашей диаграмме, вставив (Ctrl + V) — в нашем «сэндвиче в разрезе» сверху должны появиться два новых «слоя»:
Теперь давайте переключим тип диаграммы для этих двух добавленных слоев на график. Для этого выделите поочередно каждую строку, щелкните по ней правой кнопкой мыши и выберите команду Изменение типа диаграммы для серии (Изменить тип диаграммы серий). В более старых версиях Excel 2007–2010 вы можете выбрать нужный тип диаграммы (График с маркерами), а в новом Excel 2013 появится диалоговое окно со всеми строками, где для каждой строки из выпадающих списков выбирается нужный тип:
После нажатия на OK мы увидим картинку уже похожую на ту, что нам нужна:
Нетрудно понять, что осталось лишь выделить синюю область и изменить цвет ее заливки на прозрачный. Без заливки (без заполнения). Ну и наведите общий блеск: добавьте подписи, заголовок, уберите лишние элементы в легенде и т.д.
По-моему, это намного лучше, чем колонки, нет?
- Как быстро добавить новые данные на диаграмму путем копирования
- Маркированная диаграмма для отображения KPI
- Видеоурок по созданию диаграммы Ганта проекта в Excel