Диаграмма Спидометр в Excel

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

Однако в стандартный набор диаграмм «Спидометр» не включен.
Но для знатоков Excel существует обходной путь — мы сделаем Спидометр, скомбинировав кольцевую и круговую диаграмму.

Диаграмма Спидометр в Excel для визуализации выполнения плана по выручке

Чтобы построить диаграмму Спидометр, нужно подготовить данные. Диапазон данных нужно разделить на три зоны — красную, желтую и зеленую (в вашем случае могут быть другие цвета или другое количество зон).

Сначала разделим на три зоны целевые показатели выручки (верхняя таблица). Т.е. если выручка будет ниже 2 млн, то стрелка спидометра будет попадать в красную зону, если выше 2 млн, но ниже 7 млн — в желтую зону, и т.д.

Затем подготовим вспомогательную таблицу (нижняя таблица). Формулы, которые использовались для вспомогательной таблицы, приведены рядом.

Суть вспомогательной таблицы: разделить круг ровно на две половины. Первая половина (Невидимая часть) всегда равна 100%. Вторая половина (красная, желтая, зеленая зоны) делится в зависимости от соотношения частей в верхней таблице, но сумма частей должна быть равна также 100%.

Затем выделяем вспомогательную таблицу и переходим в меню Вставка — блок Диаграммы — Круговая диаграмма — Кольцевая.

Получившую диаграмму нужно развернуть. Для этого щелкнем на ней правой кнопкой мыши и выберем Формат ряда данных — Параметры ряда — Угол поворота правого сектора 90 градусов.

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

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

Рисуем стрелку

Добавим данные по фактической величине выручке (которую будет показывать стрелка на спидометре).

Затем создадим еще одну вспомогательную таблицу — для отображения стрелки.

В этой таблице также есть Невидимая часть, которая равна 100%.

Левая часть до стрелки показывает величину отклонения стрелки (насколько стрелка поднимется от нуля). Формула расчета приведена рядом с таблицей:

Фактическое значение / Максимальное значение

Величина стрелки фиксированная, зададим ее 2%.

Правая часть равно 100% минус сумма Левой части и Стрелки.

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

Нажмем Добавить.

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

Теперь изменим тип диаграммы для получившегося второго ряда на Круговую.

Для этого щелкнем на внешнем круге правой кнопкой мыши — Изменить тип диаграммы для ряда.

Меняем тип диаграммы для Ряда 2 на Круговая и обязательно ставим галочку Вспомогательная ось.

Не пугаемся, что наш первый ряд(сама шкала) куда-то исчез ))) Мы его скоро увидим снова.

Если присмотритесь к получившейся диаграмме, то маленький серый сектор — это и есть будущая стрелка. Только она почему-то внизу. Исправим это.

Правая кнопка мыши на круговой диаграмме — Формат ряда данных — Параметры ряда — Угол поворота правого сектора 90 градусов.

Теперь стрелка встала в нужное положение.

Осталось убрать заливку со всех секторов круга, кроме маленького серого сектора (стрелка). Сделаем это точно так же, как мы делали с невидимой часть шкалы (правая кнопка — Формат ряда данных — Заливка и границы — Нет заливки).

И вот уже стрелка почти готова.

Настроим внешний вид стрелки. Для этого выделим ее двойным щелчком и в Параметрах ряда в блоке Граница выберем Сплошная линия и черный цвет.

Теперь изменим значение для сектора стрелки. Было 2% — сделаем 0%.

Диаграмма практически готова.

Для наглядности можно добавить отображение фактического значения рядом со стрелкой.

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

Чтобы фактическое значение попадало в прямоугольник автоматически, выделим его и сразу установим курсор в строку формул. В строке формул напишем знак равно — и сошлемся (выделим) ячейку с фактическим значением. Выровняем текст в прямоугольнике посередине и по центру. Готово!