Создание диаграммы в виде спидометра в Excel

Диаграмма в виде спидометра отображает шкалу с зонами (достижение/ не достижение целей) и стрелкой, указывающей текущее значение. Такой вид визуализации интуитивно понятен, так как мы сталкиваемся с ним в своей повседневной жизни: в кухонных плитах, кондиционерах, приборных панелях автомобиля и т.д. К тому же они гармонично встраиваются в дашборды – (полу)круглые шкалы таких диаграмм не занимают много места, и поэтому их можно устанавливать до нескольких десятков в одном отчете.

В стандартном наборе Excel диаграмма спидометра отсутствует. Чтобы построить такую диаграмму, нам потребуется прибегнуть к хитрости. Для этого нам понадобятся две круговые диаграммы, одна из которых будет изображать шкалу спидометра, другая – стрелку. Ниже описано пошаговое руководство.

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

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

Таблица с KPI

Следующая таблица определяет размеры областей шкалы диаграммы. Так как мы будем строить круговую диаграмму, удобно, чтобы сумма значений равнялась 360. Первая область – прозрачная – равная 180 – нижняя часть спидометра, ее мы видеть не будем. Далее, красная и зеленая зона равняется 25% от 180 градусов, соответственно равны 45. Желтая зона – оставшиеся 90 градусов.

Таблица для шкалы

И последняя таблица с данными для диаграммы, которая отвечает за положение стрелки. Прозрачная часть равна 180, тут все понятно, аналогично предыдущей таблице. Далее, значения Меньше KPI и Больше KPI – это области, находящиеся слева и справа от указателя. Левая область занимает 73% от 180 градусов, правой досталось все остальное. Размер стрелки приняли равным 3-м градусам.

Таблица для указателя

Строим круговые диаграммы

Выделяем значения таблицы для шкалы B5:B8, переходим по вкладке Вставка в группу Диаграммы, выбираем Круговая -> Круговая

Круговая диаграмма

Excel нам построит круговую диаграмму с одним рядом данных (шкала спидометра), в которую необходимо добавить еще один ряд (стрелка спидометра). Для этого щелкаем правой кнопкой мыши по диаграмме, в выпадающем меню выбираем Выбрать данные.

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

В появившемся диалоговом окне Выбор источника данных, щелкаем по кнопке Добавить, находящегося в поле Элементы легенды (ряды). Далее указываем значения таблицы для стрелки. Жмем ОК.

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

Внешне наша диаграмма не изменилась, это произошло, потому что первый ряд данных наложился на второй. Еще раз щелкаем правой кнопкой по диаграмме и выпавшем меню выбираем Изменить тип диаграммы для ряда… В диалоговом окне Изменение типа диаграммы для ряда данных Шкала меняем круговую диаграмму на кольцевую.

Изменить тип даграммы

Теперь на передний план выступил ряд данных Указатель. Убираем заливку с не нужных областей, для этого дважды по ней щелкаем, во вкладке Работа с диаграммами –> Формат в группе Стили фигур устанавливаем значение Заливка фигуры в Нет заливки.

нет заливки

Точно также убираем заливку для всех остальных элементов ряда данных Указатель, кроме области отвечающей за стрелку.

стрелка спидометра

Осталось убрать заливку с ряда данных, отвечающего за шкалу. Для этого нам необходимо будет вернуть положение ряда данных Шкала на передний план. Щелкаем правой кнопкой мыши по диаграмме, в выпадающем меню выбираем Изменить тип диаграммы для ряда. В появившемся диалоговом окне ставим галочку в колонке Вспомогательная ось напротив ряда данных Шкала. Жмем ОК.

Вспомогательная ось

И точно также, как и для предыдущего ряда убираем не нужную заливку

убираем заливку

Осталось перевернуть диаграмму и отформатировать наш спидометр. Выделяем диаграмму, переходим по вкладке Работа с диаграммами –> Формат в группу Текущий фрагмент. В выпадающем меню выбираем Ряд Шкала -> Формат выделенного. В появившейся справа панели во вкладке Параметры ряда устанавливаем Угол поворота первого сектора равным 90 градусов. Такие же шаги проделываем для ряда данных Указатель.

Угол поворота

Отформатируйте диаграмму в виде спидометра по вашему усмотрению, измените цвета шкал, уберите легенду, добавьте подпись для указателя (чтобы вместо значения ряда данных в подписи было значение KPI, выделите подпись и строке формул укажите адрес ячейки, в котором находиться KPI). И ваша диаграмма готова.

Диаграмма спидометр

Скачать файл с примером диаграммы в виде спидометра.


11 комментариев

  1. Pingback: Диаграмма — MS Office Excel - synonym

      • Вопрос поставлен корректно. Подпись указателя (стрелки) ссылается на значение указателя (Е10), а у Вас каким-то образом на значение KPI (А5). Вот и у меня вопрос как так сделать?

        • Есть контакт. Читаем внимательно инструкцию — «чтобы вместо значения ряда данных в подписи было значение KPI, выделите подпись и в строке формул укажите адрес ячейки, в котором находиться KPI» — не все так просто, но получилось, спасибо.

  2. Как вам удалось обрезать прозрачный полукруг? У меня получается полкартинки пустого места( А на примере четенько размещается только отображаемая часть.

  3. Несколько лет назад тоже был озадачен созданием спидометра.
    Реализовал на MS Visio, через задание формул для параметров фигур (длина, ширина, угол наклона, цвет и пр.).
    Ренат, не пробовали Visio использовать для для дашбордов? Тоже интересный инструмент для бизнес графики.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *