Диаграмма в виде спидометра отображает шкалу с зонами (достижение/ не достижение целей) и стрелкой, указывающей текущее значение. Такой вид визуализации интуитивно понятен, так как мы сталкиваемся с ним в своей повседневной жизни: в кухонных плитах, кондиционерах, приборных панелях автомобиля и т.д. К тому же они гармонично встраиваются в дашборды – (полу)круглые шкалы таких диаграмм не занимают много места, и поэтому их можно устанавливать до нескольких десятков в одном отчете.
В стандартном наборе Excel диаграмма спидометра отсутствует. Чтобы построить такую диаграмму, нам потребуется прибегнуть к хитрости. Для этого нам понадобятся две круговые диаграммы, одна из которых будет изображать шкалу спидометра, другая – стрелку. Ниже описано пошаговое руководство.
Подготовка данных
Нам необходимо подготовить три таблицы с данными. Первая, самая простая, содержит значение KPI в процентах. Этот показатель будет определять положение указателя на спидометре.
Следующая таблица определяет размеры областей шкалы диаграммы. Так как мы будем строить круговую диаграмму, удобно, чтобы сумма значений равнялась 360. Первая область – прозрачная – равная 180 – нижняя часть спидометра, ее мы видеть не будем. Далее, красная и зеленая зона равняется 25% от 180 градусов, соответственно равны 45. Желтая зона – оставшиеся 90 градусов.
И последняя таблица с данными для диаграммы, которая отвечает за положение стрелки. Прозрачная часть равна 180, тут все понятно, аналогично предыдущей таблице. Далее, значения Меньше KPI и Больше KPI – это области, находящиеся слева и справа от указателя. Левая область занимает 73% от 180 градусов, правой досталось все остальное. Размер стрелки приняли равным 3-м градусам.
Строим круговые диаграммы
Выделяем значения таблицы для шкалы B5:B8, переходим по вкладке Вставка в группу Диаграммы, выбираем Круговая -> Круговая
Excel нам построит круговую диаграмму с одним рядом данных (шкала спидометра), в которую необходимо добавить еще один ряд (стрелка спидометра). Для этого щелкаем правой кнопкой мыши по диаграмме, в выпадающем меню выбираем Выбрать данные.
В появившемся диалоговом окне Выбор источника данных, щелкаем по кнопке Добавить, находящегося в поле Элементы легенды (ряды). Далее указываем значения таблицы для стрелки. Жмем ОК.
Внешне наша диаграмма не изменилась, это произошло, потому что первый ряд данных наложился на второй. Еще раз щелкаем правой кнопкой по диаграмме и выпавшем меню выбираем Изменить тип диаграммы для ряда… В диалоговом окне Изменение типа диаграммы для ряда данных Шкала меняем круговую диаграмму на кольцевую.
Теперь на передний план выступил ряд данных Указатель. Убираем заливку с не нужных областей, для этого дважды по ней щелкаем, во вкладке Работа с диаграммами –> Формат в группе Стили фигур устанавливаем значение Заливка фигуры в Нет заливки.
Точно также убираем заливку для всех остальных элементов ряда данных Указатель, кроме области отвечающей за стрелку.
Осталось убрать заливку с ряда данных, отвечающего за шкалу. Для этого нам необходимо будет вернуть положение ряда данных Шкала на передний план. Щелкаем правой кнопкой мыши по диаграмме, в выпадающем меню выбираем Изменить тип диаграммы для ряда. В появившемся диалоговом окне ставим галочку в колонке Вспомогательная ось напротив ряда данных Шкала. Жмем ОК.
И точно также, как и для предыдущего ряда убираем не нужную заливку
Осталось перевернуть диаграмму и отформатировать наш спидометр. Выделяем диаграмму, переходим по вкладке Работа с диаграммами –> Формат в группу Текущий фрагмент. В выпадающем меню выбираем Ряд Шкала -> Формат выделенного. В появившейся справа панели во вкладке Параметры ряда устанавливаем Угол поворота первого сектора равным 90 градусов. Такие же шаги проделываем для ряда данных Указатель.
Отформатируйте диаграмму в виде спидометра по вашему усмотрению, измените цвета шкал, уберите легенду, добавьте подпись для указателя (чтобы вместо значения ряда данных в подписи было значение KPI, выделите подпись и строке формул укажите адрес ячейки, в котором находиться KPI). И ваша диаграмма готова.
Скачать файл с примером диаграммы в виде спидометра.
Pingback: Диаграмма — MS Office Excel - synonym
Добрый день. а как сделать чтобы в подписи было значение 73 а не 3?
Наверное, вы имели ввиду наоборот, изменить подпись с 73 на 3. Для этого необходимо в ячейке A5 поменять значение на 3
Вопрос поставлен корректно. Подпись указателя (стрелки) ссылается на значение указателя (Е10), а у Вас каким-то образом на значение KPI (А5). Вот и у меня вопрос как так сделать?
Есть контакт. Читаем внимательно инструкцию — «чтобы вместо значения ряда данных в подписи было значение KPI, выделите подпись и в строке формул укажите адрес ячейки, в котором находиться KPI» — не все так просто, но получилось, спасибо.
Как вам удалось обрезать прозрачный полукруг? У меня получается полкартинки пустого места( А на примере четенько размещается только отображаемая часть.
С этим разобрался.
Каким же образом убрали прозрачный полукруг?
На самом деле он там есть, просто видимая часть помещена в рамку)
Несколько лет назад тоже был озадачен созданием спидометра.
Реализовал на MS Visio, через задание формул для параметров фигур (длина, ширина, угол наклона, цвет и пр.).
Ренат, не пробовали Visio использовать для для дашбордов? Тоже интересный инструмент для бизнес графики.
А если выполнение будет 110%? Как-то график строится тогда не правильно
Здравствуйте!
А как сделать на одной диаграмме несколько указателей? Например, если на одном «спидометре» хочу расположить результаты нескольких офисов?