Диаграмма выполнения плана


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

Диаграмма выполнения плана - вариант 1

Надеюсь, тут всё очевидно и не требует подробных комментариев (как и должно быть у хорошей визуализации): 

  • план отмечается горизонтальной синей линией - здесь она сделана с помощью "усов" погрешностей (error bars);
  • перевыполнение плана свыше этой линии выделяется зелёным цветом, недовыполнение - красным;
  • аккуратные подписи данных рядов и отклонения от плана по оси Х добавляют информативности, но не мешают восприятию.

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

Другой вариант диаграммы план-факт

Построить такие диаграммы очень легко - давайте пошагово разберём весь процесс.

Подготовка данных

Предполагаем, что в качестве исходных данных мы отталкиваемся от следующей таблицы:

Исходные данные

Во-первых, имеет смысл сразу конвертировать эту таблицу в динамическую "умную", что позволит в дальнейшем не задумываться о её размерах при добавлении новых менеджеров-строк. Делаем это через команду Главная - Форматировать как таблицу (Home - Format as Table) или сочетанием клавиш Ctrl+T

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

Дополнительные расчетные столбцы для диаграммы

Здесь

  • Столбец Отклонение от плана - вычисляет отклонение от плана в процентах по классической формуле =[Факт]/[План]-1.
  • Столбец Подписи оси Х - склеивает имя менеджера с отклонением от плана. Для добавления между ними переноса строки, используем функцию СИМВОЛ (CHAR) с кодом 10, которая заменяет Alt+Enter и функцию ТЕКСТ (TEXT), чтобы показать отклонение от плана в виде округленных до целого процентов:

    Подписи к оси Х

  • Столбец Серое - берёт минимальное значение из плана и факта.
  • Столбец Красное - в случае невыполнения плана считает на сколько именно мы от него отстали, в противном случае выводит ошибку #Н/Д, эмулируя её с помощью функции НД (NA). Ошибка здесь нужна, чтобы в случае перевыполнения плана на диаграмму в красном ряду в принципе ничего не выводилось (даже нули):

    Красный ряд

  • Столбец Зеленое - противоположен по смыслу красному - в случае перевыполнения плана считает на сколько именно мы его перевыполнили, в противном случае опять же выводит ошибку #Н/Д.

Построение диаграммы

Дальше создаем и оформляем диаграмму по следующему алгоритму: 

  1. Выделяем в нашей умной таблице 4 последних столбца и строим Гистограмму с накоплением (Stacked Chart) на вкладке Вставка (Insert).
  2. Заливаем столбики рядов данных в соответствующие цвета: серый, зеленый и красный.
  3. Добавляем подписи данных на вкладке Конструктор - Добавить элемент диаграммы - Метки данных (Design - Add Chart Element - Data Labels).
  4. Добавляем на диаграмму ряд План (проще всего это сделать, скопировав исходные ячейки С3:С12 и вставив их затем из буфера прямо в диаграмму):

    Добавление ряда План

  5. Для добавленного ряда План меняем тип диаграммы на Точечную (XY Scatter Chart) по той же оси, щёлкнув по ряду правой кнопкой мыши и выбрав команду Изменить тип диаграммы для ряда (Change Series Chart Type). Жёлтые прямоугольники должны превратиться в желтые точки-маркеры.
  6. Сдвигаем подписи к желтым маркерам вправо, чтобы не мешались.
  7. Добавляем к этим жёлтым маркерам "усы" погрешностей через вкладку Конструктор - Добавить элемент диаграммы - Пределы погрешностей (Design - Add Chart Element - Error bars):

    Добавленные "усы" погрешностей

  8. Вертикальные "усы" выделяем и удаляем, а для горизонтальных настраиваем размеры и цвет-толщину, щёлкнув по ним правой кнопкой мыши и выбрав команду Формат пределов погрешностей.
Вот, собственно и всё. Добавляем внятный заголовок, убираем линии сетки - и наша диаграмма готова:

Диаграмма выполнения плана - вариант 1

Вместо красной заливки можно отформатировать прямоугольники невыполненного плана в виде "пустышек" с пунктирным контуром - по желанию.

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



Наверх