Сравнение плана и факта достаточно частая задача в бизнес-среде.
Рассмотрим различные виды план-факт анализа в Excel и способы их создания.
Наиболее популярными видами сравнения являются таблицы и диаграммы.
Таблицы удобно использовать, если сравнение между планом и фактом происходит по различным показателям, например, данные по продажам различных товаров компании, P&L, CF и т.д.
Диаграммы удобнее для визуализации план-факта конкретного показателя, например, месячная динамика продаж конкретного товара.
Таблица план-факт
Стандартная план-факт таблица состоит из нескольких блоков: в левой части — название показателя, в центре — данные с планом и фактом, в правой — отклонение (в абсолютных величинах, в процентах).
Для лучшей визуализации дополнительно можно окрашивать ячейку или шрифт текста с отклонением, например, в красный и зеленый цвета.
Диаграмма план-факт
Предположим, что для план-факт графика у нас имеются помесячные данные по продажам (фактические и планируемые), а также отклонение между фактом и планом:
Разберем 2 различных варианта построения план-факт диаграмм.
Вариант 1. Добавление дополнительного ряда в виде гистограммы
Выделяем диапазон ячеек A1:M4 и добавляем стандартный график с маркерами (на панели вкладок выбираем Вставка -> График -> График с маркерами):
На графике отобразились 3 ряда — Факт, План и Отклонение, при этом ряд с отклонением получился существенно ниже первых двух за счет эффекта масштаба.
Поэтому сделаем ряд «Отклонение» гистограммой (нажимаем правой кнопкой мыши на ряд, выбираем Изменить тип диаграммы -> Гистограмма -> Гистограмма с группировкой) и перенесем его на вспомогательную ось (снова выбираем ряд, нажимаем правую кнопку и выбираем Формат ряда данных -> Параметры ряда -> По вспомогательной оси)
Теперь приведем в порядок внешний вид план-факт диаграммы — скроем вспомогательную ось, установим минимальные и максимальные значение для основной и вспомогательной оси:
Добавляя подпись данных к рядам получаем окончательный вид план-факт графика:
Вариант 2. Полосы повышения-понижения
Еще одним вариантом план-факт анализа является диаграмма с использованием свойств полосы повышения-понижения.
Повторяем действия из первого примера, выделяем диапазон ячеек A1:M3 (без отклонения) и строим график с маркерами:
Во вкладке Конструктор (Excel 2013 и старше) или Макет (Excel 2007-2010) добавляем Полосы повышения-понижения:
Между линией факта и плана появились прямоугольные полосы (полосы повышения-понижения), которые показывают изменение между линиями.
При этом в зависимости от знака отклонения они окрашиваются в разные цвета (в данном примере, если факт больше плана, то в черный и наоборот, если факт меньше плана, то в белый).
Изменим цвет полосы повышения на зеленый, а полосы понижения на красный, а также сделаем их полупрозрачными, чтобы полосы не наезжали на линии графика (нажимаем правой кнопкой мыши на полосу и выбираем Формат полос повышения/понижения):
Подробно ознакомиться с шаблонами диаграмм из разобранных примеров — скачать пример.
Удачи вам и до скорой встречи на страницах блога Tutorexcel.ru!
Наглядность красива на небольшом количестве данных, у меня по дням за два годак Что делать?
Если сравнивать по дням за 2 года, это более 700 сравнений, мне кажется в любом случае не получится красивое сравнение сделать.
На мой взгляд в этом случае лучше агрегировать данные.