Диаграмма в виде спидометра в Excel — комбинация круговой и кольцевой диаграммы, отображающая уровень показателя согласно заданной шкале с зонами.

Диаграмма спидометр (в англоязычной среде называют speedometer chart или gauge chart) может использоваться как часть дашборда (это визуализированный компактный отчет с ключевыми показателями бизнеса), для вставки в презентацию или просто для улучшения визуализации данных.

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

В стандартном виде график спидометр выглядит следующим образом:

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

В первую очередь подготовим исходные данные для построения графика в виде спидометра: ряд для отображения шкалы (диапазон C4:C7) и ряд для отображения стрелки (диапазон F4:F7):

Обычно шкала делится на 3 основные зоны: красная, желтая и зеленая, при этом при попадании показателя в красную зону результат будет интерпретироваться как плохой, в желтую — хороший, в зеленую — отличный.
В зависимости от поставленной задачи можно варьировать количество зон оценки, их размер и т.д.
В данном примере зададим прозрачную часть равной 180 градусам (половине круга), остальные зоны рассчитаем из пропорции 70%/15%/15%, толщину стрелки примем равной 4 градусам.

Построение графика

Для построения графика в виде спидометра, выделяем диапазон данных C4:C7 и на панели вкладок нажимаем Вставка -> Диаграмма -> Круговая:

В итоге мы получаем круговую диаграмму со шкалой:

Далее добавим на диаграмму стрелку. Щелкаем правой кнопкой мыши по графику и нажимаем Выбрать данные, после чего добавляем диапазон F4:F7 в качестве нового ряда.
Внешний вид диаграммы не поменялся, за счет того, что шкала закрывает собой стрелку, поэтому перенесем ее на вспомогательную ось (щелкаем правой кнопкой по графику, далее выбираем Формат ряда данных -> Параметры ряда -> По вспомогательной оси), а затем меняем тип диаграммы для стрелки на кольцевую (щелкаем правой кнопкой по ряду со стрелкой и нажимаем Изменить тип диаграммы для ряда):

В результате получаем диаграмму, на которой на внешней части располагается шкала с зонами, на внутренней — стрелка:

Удаляем заливку с ненужных частей диаграммы, оставляем только шкалу и стрелку:

Для удобства чтения и восприятия графика повернем его на 90 градусов, в параметрах рядов (и для шкалы, и для стрелки) ставим угол 90 градусов в поле Угол поворота первого сектора:

Также выведем стрелку на диаграмме на передний план, для этого в параметрах ряда со стрелкой устанавливаем ряд на основную ось:

Далее настраиваем диаграмму на свой вкус — выбираем цвета для шкалы и стрелки, определяем оптимальную толщину стрелки, добавляем/удаляем легенду, подписи данных и прочее:

Подробно ознакомиться с вариантами построения графика спидометра — скачать пример.

Удачи вам и до скорых встреч на страницах блога Tutorexcel.ru!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Мастер диаграмм Excel предлагает множество различных типов диаграмм, кроме, к сожалению, диаграммы спидометра. Диаграмма спидометра — это весьма ловкий способ представления данных. При помощи трюков этого раздела можно создать диаграмму спидометра и добавить полосу прокрутки панели инструментов Элементы управления (Control Toolbox), которая будет изменять диаграмму и одновременно данные на рабочем листе.

Сначала нужно настроить некоторые данные (рис. 5.28) и создать кольцевую диаграмму (doughnut chart). Кольцевые диаграммы работают схожим образом с круговыми, но они могут содержать несколько рядов, в отличие от круговых.

Рис. 5.28. Данные для диаграммы спидометра

Нажмите сочетание клавиш Alt/Apple+~, чтобы отобразить формулы на рабочем листе. Можно воспользоваться командой Сервис → Параметры → Вид (Tools → Options → View) и установить флажок Формулы (Formulas), хотя это и более длинный путь.

Теперь выделите диапазон В2:В5 и запустите мастер диаграмм. На первом шаге мастера перейдите на вкладку Стандартные (Standard Types) (хотя она должна раскрываться по умолчанию). Затем в группе Тип (Chart Type) выберите вариант Кольцевая (Doughnut). Щелкните кнопку Далее (Next), чтобы перейти ко второму шагу мастера, и удостоверьтесь, что данные выводятся на диаграмму по строкам (в столбцах). Щелкните кнопку Далее (Next), чтобы перейти к шагу 3. Если необходимо, можно что-либо изменить на этом шаге, но для этого трюка настройка этого шага не обязательна. Щелкните кнопку Далее (Next), чтобы перейти к четвертому шагу, и удостоверьтесь, что диаграмма будет создана как объект на текущем рабочем листе (и снова это параметр по умолчанию). Создание диаграммы как объекта упростит работу с ней при настройке спидометра (рис. 5.29).

Рис. 5.29. Обычная кольцевая диаграмма

Выделите кольцевую диаграмму, медленно дважды щелкните самый большой сектор, чтобы выделить его, а затем щелкните его правой кнопкой мыши, в контекстном меню выберите команду Формат точки данных (Format Data Point) и перейдите на вкладку Параметры (Options). Выберите угол поворота для этого сектора равным 90 градусам. Щелкните вкладку Вид (Patterns), выберите невидимую границу и прозрачную заливку, затем щелкните кнопку ОК. По очереди каждый из оставшихся секторов дважды медленно щелкните, затем дважды щелкните, чтобы открыть диалоговое окно Формат элемента данных (Format Data Series), и выберите нужный цвет. Кольцевая диаграмма должна выглядеть, как на рис. 5.30.

Рис. 5.30. Заготовка спидометра

Необходимо добавить еще один ряд (Series 2, Ряд 2) значений, чтобы создать циферблат. Снова выделите диаграмму, щелкните ее правой кнопкой мыши, в контекстном меню выберите команду Исходные данные (Source Data) и перейдите на вкладку Ряд (Series). Щелкните кнопку Добавить (Add), чтобы создать новый ряд, и в поле Значения (Values) выберите диапазон С2:С13. Еще раз щелкните кнопку Добавить (Add), чтобы добавить третий ряд (Series 3, Ряд 3), отвечающий за стрелку, и в поле Значения (Values) выберите диапазон Е2:Е5. Результат должен выглядеть, как на рис. 5.31.

Рис. 5.31. Кольцевая диаграмма с несколькими рядами

Теперь спидометр начинает обретать свой вид. Если вы хотите добавить подписи, стоит загрузить специальные утилиты (John Walkenbach’s Chart Tools) с нашей страницы загрузок. Часть этой надстройки, которая, к сожалению, работает только в Windows, предназначена специально для меток данных. Она позволяет указывать диапазон на рабочем листе, на основе которого будут создаваться подписи данных для рядов диаграммы. Надстройка Джона также поддерживает возможности, перечисленные в следующем списке.

• Chart Size (Размер диаграммы). Позволяет указывать точный размер диаграммы, а также приводить все диаграммы к одному размеру.
• Export (Экспортировать). Позволяет сохранять диаграммы в виде файлов в формате .gif, .jpg, .tif и .png.
• Picture (Рисунок). Преобразует диаграмму в рисунок (цветной или черно-белый).
• Text Size (Размер текста). Фиксирует размер всех текстовых элементов диаграммы. Когда размер диаграммы меняется, текстовые элементы сохраняют свой размер.
• Chart Report (Отчет диаграммы). Генерирует отчет для всех диаграмм или подробный отчет для одной диаграммы.

При помощи этой надстройки отформатируйте ряд Series 2 (Ряд 2), чтобы он отображал подписи данных, взятые из диапазона D2:D13. He сбрасывая выделение Series 2 (Ряд 2), дважды щелкните его, чтобы открыть диалоговое окно Формат ряда данных (Format Data Series). Перейдите на вкладку Вид (Patterns) и выберите невидимую границу и прозрачную заливку. Диаграмма должна выглядеть, как на рис. 5.32.

Рис. 5.32. Улучшенная диаграмма спидометра с добавленными подписями

Выделите ряд Series 3 (Ряд 3), затем щелкните его правой кнопкой мыши и в контекстном меню выберите команду Тип диаграммы (Chart Type). Выберите круговую диаграмму. Да, это выглядит немного странно (рис. 5.33). Но будьте уверены, если круговая диаграмма перекрывает кольцевую диаграмму, вы все сделали правильно.

Рис. 5.33. Круговая диаграмма перекрывает диаграмму спидометра

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

Рис. 5.34. Разобранная и уменьшенная круговая диаграмма

Теперь выделите всю круговую диаграмму, дважды щелкните ее, выберите команду Формат ряда данных (Format Data Series) и перейдите на вкладку Параметры (Options). Измените угол поворота первого сектора на 90 градусов. По очереди выделяйте все секторы круговой диаграммы, щелкайте их правой кнопкой мыши и в диалоговом окне Формат рядов данных (Format Data Series) переходите на вкладку Вид (Patterns). Для параметров Граница (Border) и Заливка (Area) выбирайте значение Невидимая (или Прозрачная) (None) для всех секторов, кроме третьего, который следует залить черным цветом. Вы получите диаграмму, показанную на рис. 5.35.

Рис. 5.35. Диаграмма спидометра, на которой окрашен только третий сектор круговой диаграммы

Чтобы добавить легенду, выделите диаграмму, щелкните ее правой кнопкой мыши, в контекстном меню выберите команду Параметры диаграммы (Chart Options) и перейдите на вкладку Подписи данных (Data Labels). Установите флажок Ключ легенды (Legend Key). Вы увидите спидометр (рис. 5.36). Теперь перемещайте диаграмму, изменяйте размер и редактируйте ее, как необходимо. Теперь, когда диаграмма спидометра создана, нужно создать полосу прокрутки с панели инструментов Элементы управления (Control Toolbox) и связать полосу прокрутки и диаграмму.

Рис. 5.36. Диаграмма спидометра с легендой

Для этого правой кнопкой мыши щелкните область панелей инструментов на экране — это верхняя область экрана, где расположены панели инструментов Стандартная (Standard) и Форматирование (Formatting), — и выберите команду Элементы управления (Control Toolbox). Выберите инструмент полосы прокрутки и перетащите его в нужное место на рабочем листе. Выделите полосу прокрутки, щелкните ее правой кнопкой мыши и в контекстном меню выберите команду Свойства (Properties). Откроется диалоговое окно Свойства (Properties). В поле LinkedCell выберите ячейку F3, укажите максимальное значение 100 и минимальное значение 0. Закрыв это диалоговое окно и переместив полосу прокрутки на диаграмму, вы увидите приблизительно то же, что и на рис. 5.37.

Рис. 5.37. Законченная диаграмма спидометра

Щелкая стрелки или перетаскивая ползунок на полосе прокрутки, вы будете изменять показания спидометра, но помните, что при этом также изменяются данные на связанном со спидометром рабочем листе.

Друзья, наступил учебный год – а значит пришло время новых знаний и новых открытий. Сегодня хочется поделиться с вами секретами построения в MS Excel одной нестандартной диаграммы, а именно – диаграммы-спидометра.

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

Итак, в силу отсутствия этой диаграммы среди стандартных средств Excel, раскрываем секрет ее самостоятельного создания. «Спидометр» состоит из двух круговых диаграмм, одна из которых служит основой для секторов – индикаторов выполнения, а другая – для указателя спидометра.

Имеем: исходные данные: факт выполнения плана продаж на 89%

А также критическое нижнее значение плана – 25% и «средний» диапазон 25%-75%.

Уровни	% от 100
< min
> N

Для первой диаграммы с индикаторами получаем таблицу:

В сумме – 360 градусов, то есть весь круг. Высчитывание секторов происходит по принципу расчета % от 180 градусов – верхнего полукруга диаграммы. Соответственно, три сектора будут иметь значения для нашего примера: 25%*180 градусов, (75%-25%)*180 градусов, (100%-75%)*180 градусов.

Определим значения для второй диаграммы – указателя. Чтобы он был достаточно узким, зададим угол 3 градуса. Соответственно, он будет разбивать верхнюю половину круга (и 180 градусов) на 2 части: 89%*180 градусов и 11%*180 градусов. Вычтем из первого значения единицу, чтобы компенсировать место, занимаемое стрелкой. Получим (180 – 89% — 1 ) для первого блока, что равно 159.2. Для второго блока значение фиксируем на 3, для третьего вычисляем 180-3-(180-89%-1). Везде вместо 89% указываем ячейку, в которой это значение хранится.

1. По двум столбцам строим круговую диаграмму. Так как Excel не умеет строить 2 разные диаграммы одновременно, он их помещает в одну область, накладывая друг на друга.

2. Выделив круговую диаграмму (автоматически выделяется верхняя), изменяем ее тип с «круговой» на «кольцевую». Она автоматически уйдет на задний план. Таким образом, из 2 диаграмм круговая (в будущем – указатель) будет на переднем плане, кольцевая (в будущем – индикаторы низкий-средний-высокий) будет на заднем плане.

3. Для круговой диаграммы дважды кликаем на одном из секторов, изменяя его заливку на прозрачную. Аналогичное действие повторяем для остальных секторов за исключением самого узкого (в таблице его значение равно 3 градусам, в будущем — указатель).

4. Изменяем заливку единственного оставшегося непрозрачного сектора круговой диаграммы на черный. Получаем указатель.

Поворачиваем ОБЕ диаграммы на 90 градусов (кликаем по ним правой кнопкой, вносим изменения в меню «Формат ряда данных – Параметры – Угол первого сектора — 90»).
Таким образом, отсчет секторов (в нашем примере 180-45-90-45 и 180-159.2-3-17.8) будет начинаться не с крайней верхней точки, а с крайней правой. Тогда именно на верхнюю половину диаграмм будут приходиться сектора 45-90-45 и сектор с указателем, что и будет напоминать спидометр.

5. При вызове правой кнопкой мыши меню выбираем «Формат ряда данных – Параметры – Диаметр отверстия – 90%». Таким образом, мы сужаем сектора-индикаторы высокий-средний-низкий.

6. Меняем цвет заливки нижней области с синего на прозрачный, верхних на красный-желтый-зеленый. Меняем цвет указателя на черный, удаляем легенду для диаграммы.

7. Добавляем подпись для указателя (2 клика правой кнопкой по указателю – «Добавить подпись данных»). Автоматически выставляется значение, по которому строилась диаграмма, то есть 3 (градуса).

8. Изменяем источник данных для подписи на формулу ячейки, в которой содержится значение указателя (то есть 89). Для этого дважды кликаем правой кнопкой мыши на подписи и в строке формул вводим номер ячейки.

9. Еще раз убедимся, что спидометр «исправен». Заменим исходное значение 89% на 15%. Вуаля. Диаграмма–спидометр к вашим услугам. Значение ниже среднего – а значит, пора действовать!

Сен 12, 2012Ирина Доброва