Диаграмма выполнения плана
Для визуализации классического сценария "план-факт" существует огромное количество всевозможных вариантов диаграмм, некоторые из которых я уже разбирал. Сегодня хочу добавить к этому списку и в ваш инструментарий ещё один весьма прикольный, на мой взгляд, вариант:
Надеюсь, тут всё очевидно и не требует подробных комментариев (как и должно быть у хорошей визуализации):
- план отмечается горизонтальной синей линией - здесь она сделана с помощью "усов" погрешностей (error bars);
- перевыполнение плана свыше этой линии выделяется зелёным цветом, недовыполнение - красным;
- аккуратные подписи данных рядов и отклонения от плана по оси Х добавляют информативности, но не мешают восприятию.
Как вариант, можно заменить красные прямоугольники невыполненного плана на пустышки с пунктиром по контуру - тоже будет наглядно:
Построить такие диаграммы очень легко - давайте пошагово разберём весь процесс.
Подготовка данных
Предполагаем, что в качестве исходных данных мы отталкиваемся от следующей таблицы:
Во-первых, имеет смысл сразу конвертировать эту таблицу в динамическую "умную", что позволит в дальнейшем не задумываться о её размерах при добавлении новых менеджеров-строк. Делаем это через команду Главная - Форматировать как таблицу (Home - Format as Table) или сочетанием клавиш Ctrl+T.
Во-вторых, следующая хитрость в том, что строить диаграмму мы будем не по имеющимся столбцам, а по дополнительным, в которых с помощью несложных формул рассчитаем числовые ряды для будущей диаграммы:
Здесь
- Столбец Отклонение от плана - вычисляет отклонение от плана в процентах по классической формуле =[Факт]/[План]-1.
- Столбец Подписи оси Х - склеивает имя менеджера с отклонением от плана. Для добавления между ними переноса строки, используем функцию СИМВОЛ (CHAR) с кодом 10, которая заменяет Alt+Enter и функцию ТЕКСТ (TEXT), чтобы показать отклонение от плана в виде округленных до целого процентов:
- Столбец Серое - берёт минимальное значение из плана и факта.
- Столбец Красное - в случае невыполнения плана считает на сколько именно мы от него отстали, в противном случае выводит ошибку #Н/Д, эмулируя её с помощью функции НД (NA). Ошибка здесь нужна, чтобы в случае перевыполнения плана на диаграмму в красном ряду в принципе ничего не выводилось (даже нули):
- Столбец Зеленое - противоположен по смыслу красному - в случае перевыполнения плана считает на сколько именно мы его перевыполнили, в противном случае опять же выводит ошибку #Н/Д.
Построение диаграммы
Дальше создаем и оформляем диаграмму по следующему алгоритму:
- Выделяем в нашей умной таблице 4 последних столбца и строим Гистограмму с накоплением (Stacked Chart) на вкладке Вставка (Insert).
- Заливаем столбики рядов данных в соответствующие цвета: серый, зеленый и красный.
- Добавляем подписи данных на вкладке Конструктор - Добавить элемент диаграммы - Метки данных (Design - Add Chart Element - Data Labels).
- Добавляем на диаграмму ряд План (проще всего это сделать, скопировав исходные ячейки С3:С12 и вставив их затем из буфера прямо в диаграмму):
- Для добавленного ряда План меняем тип диаграммы на Точечную (XY Scatter Chart) по той же оси, щёлкнув по ряду правой кнопкой мыши и выбрав команду Изменить тип диаграммы для ряда (Change Series Chart Type). Жёлтые прямоугольники должны превратиться в желтые точки-маркеры.
- Сдвигаем подписи к желтым маркерам вправо, чтобы не мешались.
- Добавляем к этим жёлтым маркерам "усы" погрешностей через вкладку Конструктор - Добавить элемент диаграммы - Пределы погрешностей (Design - Add Chart Element - Error bars):
- Вертикальные "усы" выделяем и удаляем, а для горизонтальных настраиваем размеры и цвет-толщину, щёлкнув по ним правой кнопкой мыши и выбрав команду Формат пределов погрешностей.
Вместо красной заливки можно отформатировать прямоугольники невыполненного плана в виде "пустышек" с пунктирным контуром - по желанию.
Ссылки по теме
- Диаграмма "план-факт" в Excel
- Подсветка заданных столбцов на диаграмме (условное форматирование диаграммы)
- Диаграмма-термометр в Excel
Спасибо, что поделились, Николай!