Диаграмма-спидометр в Excel
Диаграмма-спидометр – это комбинация кольцевой и круговой диаграммы. Выглядит такая диаграмма вот так:
Чтобы создать диаграмму-спидометр:
- Выделите диапазон H2:I6.
- На вкладке Вставка (Insert) в разделе Диаграммы (Charts) нажмите Все диаграммы (Other Charts) и выберите Кольцевая (Doughnut).
- Далее необходимо выделить каждую точку данных и при помощи команды Формат выделенного (Format selection) настроить заливку каждого элемента.Обратите внимание на пример, показанный ниже:
- Для ряда данных «Donut» заливка настроена следующим образом: первые три сектора имеют цвет заливки разного цвета (красный, желтый и салатовый), а четвертая точка без заливки.
- Для ряда данных «Pie» – первая и третья точки без заливки, а вторая (наименьший сектор) черный цвет заливки.
Ряды данных «Donut» или «Pie» можно выбрать на вкладке Формат (Format). При помощи клавиш со стрелками можно перемещаться от одной точки данных к другой.
- невидимого бесцветного сектора, соответствующего значению 75,
- чёрного сектора-стрелки, соответствующего значению 1
- и еще одного бесцветного сектора, соответствующего значению 124.
Как построить диаграмму типа спидометр в MS Excel?
Правильно подобранное изображение может донести больше полезной информации, чем самая элегантная таблица. Очень эффектной является диаграмма типа спидометр, которая показывает текущее значение на некоторой ограниченной шкале. На этой диаграмме можно отображать, например, уровень KPI, выполнение плана, уровень обслуживания клиентов и другие показатели с ограниченным диапазоном изменения. Циферблат спидометра делится на зоны, стрелка показывает текущее состояние дел. Снизу можно добавить точное значение измеряемого показателя.
Такая диаграмма отлично встраивается в отчеты и дашборды, т.к. весьма информативна, не занимает много места и на вид красива. Однако этой диаграммы нет в стандартном наборе Excel (во всяком случае пока). Для ее построения, придется использовать доступные средства, скомбинировав кольцевую и круговую диаграммы, для шкалы и стрелки, соответственно.
Вначале об общем принципе. Шкала – это верхняя половина кольцевой диаграммы. Нижняя половина также есть, но она прозрачная. Стрелка – это контур видимого сектора круговой диаграммы. Там же есть еще два сектора, но они прозрачны. Местоположение стрелки определяет измеряемый показатель.
Теперь изучим, как сделать диаграмму-спидометр в Excel. Вначале подготовим данные для шкалы, для чего нужно задать 4 значения: величина нижней прозрачной части, красной, желтой и зеленой зоны (цвета и их количество, разумеется, можно выбирать самостоятельно). Т.к. прозрачная часть занимает половину диаграммы, то она должна быть равна сумме трех цветов. Для простоты пусть весь циферблат занимает 100 делений. Тогда красная зона (плохо) – 50, желтая (нормально) – 30 и зеленая (хорошо) – 20 (50+30+20=100). Чтобы получился полукруг, невидимая часть также должна быть равна 100.
Выделяем весь диапазон и создаем кольцевую диаграмму.
По умолчанию получится следующее.
В параметрах ряда делаем поворот на 90⁰.
Удаляем название и легенду.
Почти готово. Двойным нажатием заходим в каждый сектор и меняем цвет: нижний – прозрачный, остальные – красный, желтый и зеленый. Контур также убираем.
Получаем циферблат спидометра.
Теперь сделаем стрелку. Подготовим данные, по которым будут строится три сектора круговой диаграммы. Первый сектор – от нуля до стрелки (прозрачный), второй – стрелка, третий – оставшаяся часть круга (прозрачный).
На этот раз секторы должны быть подвижными и зависеть от измеряемого показателя. Результатом будет «отклонение стрелки» на соответствующую величину. Пусть показатель измеряется в процентах и его первоначальное значение равно 60%.
Как и с циферблатом, диапазон от 0 до 100% должен приходиться на верхний полукруг. Тогда весь круг – это 200%. Чтобы стрелка меняла свое положение, первый сектор (от которого строятся остальные) привяжем к значению измеряемого показателя. Стрелка имеет фиксированный размер, установим пока 2% (потом вообще уберем). Последний сектор – это разница между 200% и суммой первых двух секторов.
Теперь нужно добавить на имеющийся циферблат спидометра новый ряд данных, чтобы отобразить стрелку. Для этого нажимаем правой кнопкой мыши по диаграмме и в контекстном меню «Выбрать данные…».
Указываем источник данных (диапазон из трех значений) и ОК. Должно получиться примерно следующее.
Не нужно пугаться, вон тот оранжевый зародыш внизу – это будущая стрелка. Дело в том, что вместо нужной круговой диаграммы пока получилось второе кольцо. Поэтому в контекстном меню (через правую кнопку мыши) выбираем новый тип для этого ряда.
Меняем диаграмму на круговую.
Обязательно нужно выбрать вспомогательную ось, как на рисунке, иначе стрелка окажется под циферблатом. Осталось повернуть диаграмму на 270⁰ и сделать прозрачными ненужные секторы.
Не забываем убрать контуры секторов.
Чтобы стрелка была больше похожа на стрелку, а не на узкий кусочек пирога, зададим для нее вначале черный (или другой) контур, а затем ширину сектора вместо 2% сделаем 0%.
Сектор исчезнет, а контур превратится в черную линию.
Для более точного и отображения снизу можно добавить цифровое значение показателя. Это сделать очень просто. Вставим прямоугольник с закругленными углами.
Сделаем прозрачный фон, красный контур. Затем выделим полученную фигуру, поставим курсор в строку формул и сделаем ссылку на отображаемое значение.
Отформатируем, как нужно и получим окончательный вид спидометра.
Остался один нюанс. Дело в том, что, если значение выйдет за пределы от 0 до 100%, то стрелка окажется не известно где.
Чтобы исправить возможную ошибку, с помощью функции ЕСЛИ в формуле, определяющей отклонение стрелки, зададим минимальное значение 0 и максимальное 100%.
Примерно так рисуется «классический» спидометр.
Иногда диапазон возможных значений нельзя разделить четкими границам типа «плохо», «нормально», «хорошо». Четких границ может не быть, тогда потребуется плавный переход от одного цвета к другому. Например, когда в качестве результата получается некоторая вероятность (p-value, бинарная классификация и др.) или измеряется уровень дефицита запасов, где также нет четких границ и хотелось бы подчеркнуть их размытость. В этом случае для шкалы спидометра следует использовать градиентную заливку. В целом диаграмма строится также, но ее циферблат состоит из одного цвета, плавно переходящего в другой.
Сделать такую диаграмму также несложно. Отличие только в циферблате. В ролике ниже показана пошаговая инструкция, как в Excel сделать оба варианта спидометров.
Используйте диаграмму спидометр в Excel, чтобы удивить своих коллег, а также руководство. По кнопке ниже можно скачать файл с примерами.
Диаграмма в виде спидометра в Excel — комбинация круговой и кольцевой диаграммы, отображающая уровень показателя согласно заданной шкале с зонами.
Диаграмма спидометр (в англоязычной среде называют speedometer chart или gauge chart) может использоваться как часть дашборда (это визуализированный компактный отчет с ключевыми показателями бизнеса), для вставки в презентацию или просто для улучшения визуализации данных.
Однако почему диаграмму удобно представлять именно в виде спидометра?
Такой вид интуитивно удобен за счет схожести с автомобильным спидометром.
Представьте, ведь при управлении автомобилем (компанией) взгляд на спидометр (диаграмму) позволяет быстро оценить текущую ситуацию на дороге (в бизнесе) и в случае необходимости принять оперативные решения.
В стандартном виде график спидометр выглядит следующим образом:
Подготовка исходных данных
В первую очередь подготовим исходные данные для построения графика в виде спидометра: ряд для отображения шкалы (диапазон C4:C7) и ряд для отображения стрелки (диапазон F4:F7):
Обычно шкала делится на 3 основные зоны: красная, желтая и зеленая, при этом при попадании показателя в красную зону результат будет интерпретироваться как плохой, в желтую — хороший, в зеленую — отличный.
В зависимости от поставленной задачи можно варьировать количество зон оценки, их размер и т.д.
В данном примере зададим прозрачную часть равной 180 градусам (половине круга), остальные зоны рассчитаем из пропорции 70%/15%/15%, толщину стрелки примем равной 4 градусам.
Построение графика
Для построения графика в виде спидометра, выделяем диапазон данных C4:C7 и на панели вкладок нажимаем Вставка -> Диаграмма -> Круговая:
В итоге мы получаем круговую диаграмму со шкалой:
Далее добавим на диаграмму стрелку. Щелкаем правой кнопкой мыши по графику и нажимаем Выбрать данные, после чего добавляем диапазон F4:F7 в качестве нового ряда.
Внешний вид диаграммы не поменялся, за счет того, что шкала закрывает собой стрелку, поэтому перенесем ее на вспомогательную ось (щелкаем правой кнопкой по графику, далее выбираем Формат ряда данных -> Параметры ряда -> По вспомогательной оси), а затем меняем тип диаграммы для стрелки на кольцевую (щелкаем правой кнопкой по ряду со стрелкой и нажимаем Изменить тип диаграммы для ряда):
В результате получаем диаграмму, на которой на внешней части располагается шкала с зонами, на внутренней — стрелка:
Удаляем заливку с ненужных частей диаграммы, оставляем только шкалу и стрелку:
Для удобства чтения и восприятия графика повернем его на 90 градусов, в параметрах рядов (и для шкалы, и для стрелки) ставим угол 90 градусов в поле Угол поворота первого сектора:
Также выведем стрелку на диаграмме на передний план, для этого в параметрах ряда со стрелкой устанавливаем ряд на основную ось:
Далее настраиваем диаграмму на свой вкус — выбираем цвета для шкалы и стрелки, определяем оптимальную толщину стрелки, добавляем/удаляем легенду, подписи данных и прочее:
Подробно ознакомиться с вариантами построения графика спидометра — скачать пример.
Удачи вам и до скорых встреч на страницах блога Tutorexcel.ru!
Диаграмма-спидометр в Excel
Диаграмма-спидометр – это комбинация кольцевой и круговой диаграммы.
Чтобы создать диаграмму-спидометр:
- Выделите диапазон H2:I6.
- На вкладке Вставка (Insert) в разделе Диаграммы (Charts) нажмите Все диаграммы (Other Charts) и выберите Кольцевая (Doughnut).
- Далее необходимо выделить каждую точку данных и при помощи команды Формат выделенного (Format selection) настроить заливку каждого элемента.
- Для ряда данных «Donut» заливка настроена следующим образом: первые три сектора имеют цвет заливки разного цвета (красный, желтый и салатовый), а четвертая точка без заливки.
- Для ряда данных «Pie» – первая и третья точки без заливки, а вторая (наименьший сектор) черный цвет заливки.
- Ряды данных «Donut» или «Pie» можно выбрать на вкладке Формат (Format). При помощи клавиш со стрелками можно перемещаться от одной точки данных к другой.
Выделите ряд данных «Donut», нажмите кнопку Формат выделенного (Format Selection) и введите для параметра Угол поворота первого сектора (Angle) значение 270 градусов.
Выделите диаграмму, кликните по ней правой кнопкой мыши, в контекстном меню нажмите Формат области диаграммы (Format Chart Area) и для параметров заливки и границы выберите соответственно Нет заливки (No fill) и Нет линий (No line).
Выделите ряд данных «Pie» и измените тип диаграммы для этого ряда на Круговая (Pie).
Выделите ряд данных «Pie», нажмите кнопку Формат выделенного (Format selection), для параметра Угол поворота первого сектора (Angle) введите значение 270 градусов и выберите режим построения ряда по вспомогательной оси (secondary axis).
График ряда данных «Pie» состоит из:
невидимого бесцветного сектора, соответствующего значению 75,
чёрного сектора-стрелки, соответствующего значению 1
и еще одного бесцветного сектора, соответствующего значению 124.
При помощи элемента управления Счётчик (Spin button) измените значение ячейки I3 с 75 на 76. На графике ряда данных «Pie» произойдут изменения: первый бесцветный сектор отразит значение 76; второй чёрный останется равным 1; третий бесцветный сектор покажет значение 200-1-76=123. Благодаря формуле в ячейке I3 сумма этих трёх секторов всегда будет равна 200.