spidometr11

Диаграмма-спидометр в Excel — анализ план-факт

Для наглядного сравнения фактических данных с плановыми используются разные виды диаграмм. В этой статье описывается построение диаграммы, похожей на автомобильный спидометр. В основе такой диаграммы лежит круговая диаграмма Excel:

spidometr1

На диаграмме есть несколько зон и стрелка, показывающая фактическое значение контролируемого показателя:

  • Зелёная зона – всё хорошо.
  • Жёлтая зона – наверняка нужно принимать меры по исправлению ситуации.
  • Красная зона – нужны немедленные действия.

Данный пример построен на расчёте точки безубыточности в Excel. Красная зона символизирует зону убытка для компании, это критическая ситуация. Желтая зона – компания в прибыли, но план продаж не достигнут. Зелёная зона – план выполнен или перевыполнен, всё хорошо. Положение стрелки на диаграмме наглядно показывает фактическое значение и запас прочности.

Скачайте Plan-fakt-spidometr-shablon с исходными данными.

В разделе Расчёт точки безубыточности и запаса прочности (ячейка В21 и рядом) уже рассчитана плановая точка безубыточности. Для построения диаграммы «план-факт» нужны также данные максимально возможного объёма продаж (это определяет ширину зелёной зоны) и фактические данные за исследуемый период.

Диаграмма-спидометр будет состоять из двух диаграмм Excel, наложенных друг на друга: кольцевая диаграмма для отображения зон и круговая диаграмма Excel для отрисовки стрелки. Для построения этих диаграмм нужны дополнительные промежуточные данные в ячейках F16:F25.

Рассчитайте ширину зон. Красная зона – от 0 до точки безубыточности, поэтому в ячейке F16 формула «=С24». Жёлтая зона – от точки безубыточности до планового значения: в ячейке F17 ширина зоны рассчитана как «=C23-F16». Зелёная зона – от планового значения до максимально возможного: в ячейке F18 формула «=C29-C23». В ячейке F19 суммируются все эти значения (можно просто подставить максимальное значение показателя), эта ячейка нужна для построения кольцевой диаграммы, но отображаться это значение не будет.

Для построения круговой диаграммы, отображающей стрелку, необходимо три значения. В ячейку F23 скопировано ссылкой значение фактического показателя: «=C30». В ячейке F24 задаётся толщина стрелки, пока поставьте сюда 1. Плюсом необходимо задать пустую область формулой: «=C29*2-F23-F24» (удвоенное максимальное значение показателя минус два предыдущих значения.

Постройте кольцевую диаграмму: выделите ячейки F16:F19, меню Вставка – Диаграммы — Другие диаграммы — Кольцевая.

spidometr2

На полученной диаграмме на графической области нажмите правой кнопкой мыши, выберите Формат ряда данных…

spidometr3

В открывшемся окне в разделе Параметры ряда, Угол поворота первого сектора введите 270. Диаграмма повернётся, теперь все нужные области составляют верхний полукруг диаграммы:

spidometr4

Теперь нужно раскрасить зоны в нужные цвета, а нижний полукруг спрятать. Выделяйте один за другим все зоны, вызывайте правой клавишей контекстное меню Формат точки данных, теперь в разделе Заливка справа выбирайте Сплошная заливка, задавайте нужный цвет области.

spidometr5

Для нижнего полукруга цвет задавать не надо, выберите в этом меню Нет заливки. Первая диаграмма готова, очередь за стрелкой.

Нажмите правой кнопкой на всей диаграмме, выберите в контекстном меню Выбрать данные, затем нажмите кнопку Добавить, введите в поле Значения диапазон F23:F25:

spidometr6

Теперь выберите только новое кольцо на диаграмме,  нажмите правую кнопку мыши, выберите ?зменить тип диаграммы для ряда, выберите круговую диаграмму.

Получится круговая диаграмма, которая наложится на построенную кольцевую. Нажмите на этой диаграмме правой кнопкой, выберите Формат ряда данных, в открывшемся окне также задайте угол поворота первого сектора 270 градусов и отметьте Построить рядПо вспомогательной оси.

spidometr9

Теперь спрячьте все части новой диаграммы, кроме стрелки (правой клавишей контекстное меню Формат точки данных,  разделе ЗаливкаНет заливки). Теперь видно зоны и стрелку в виде сектора.

spidometr10

Осталось придать стрелке наглядный вид. Выберите стрелку, снова контекстное меню Формат точки данных, раздел Цвет границы – выберите Сплошная линия, задайте чёрный цвет. В разделе Стили границ установите ширину границы – 1,5 или 2 пт. Теперь хитрость: в ячейку F24 введите ноль. Диаграмма приобретёт более наглядный вид:

spidometr11

Осталось подписать стрелку. Снова правой кнопкой мыши на диаграмме, выбрать Добавить подписи данных. Появятся подписи, из них можно удалить всё, кроме 0, выбирая по одной подписи. На оставшейся подписи двойной щелчок мышью – откроется режим редактирования. Мышью щёлкните на области формул, затем на ячейке F23, теперь рядом со стрелкой отображается фактическое значение показателя.

spidometr1

Наглядная диаграмма-спидометр в Excel готова!

Смотрите также статьи:

Простой анализ точки безубыточности в Excel с построением наглядного графика

 

Leave a Reply

Ваш e-mail не будет опубликован.