Диаграмма Спидометр в excel привлечет к себе внимание в любом отчете или дашборде. На нем отлично визуализируется выполнение плана продаж, уровень различных показателей и т.д.
Однако в стандартный набор диаграмм «Спидометр» не включен.
Но для знатоков Excel существует обходной путь — мы сделаем Спидометр, скомбинировав кольцевую и круговую диаграмму.
Диаграмма Спидометр в Excel для визуализации выполнения плана по выручке
Чтобы построить диаграмму Спидометр, нужно подготовить данные. Диапазон данных нужно разделить на три зоны — красную, желтую и зеленую (в вашем случае могут быть другие цвета или другое количество зон).
Сначала разделим на три зоны целевые показатели выручки (верхняя таблица). Т.е. если выручка будет ниже 2 млн, то стрелка спидометра будет попадать в красную зону, если выше 2 млн, но ниже 7 млн — в желтую зону, и т.д.
Затем подготовим вспомогательную таблицу (нижняя таблица). Формулы, которые использовались для вспомогательной таблицы, приведены рядом.
Суть вспомогательной таблицы: разделить круг ровно на две половины. Первая половина (Невидимая часть) всегда равна 100%. Вторая половина (красная, желтая, зеленая зоны) делится в зависимости от соотношения частей в верхней таблице, но сумма частей должна быть равна также 100%.
Затем выделяем вспомогательную таблицу и переходим в меню Вставка — блок Диаграммы — Круговая диаграмма — Кольцевая.
Получившую диаграмму нужно развернуть. Для этого щелкнем на ней правой кнопкой мыши и выберем Формат ряда данных — Параметры ряда — Угол поворота правого сектора 90 градусов.
Получилась заготовка диаграммы Спидометр, где нижняя часть — это Невидимая часть. Сделаем ее невидимой. Для этого дважды щелкнем левой кнопкой мыши на нижнем секторе диаграммы, перейдем в Заливка и границы — Нет заливки.
Далее таким же способом изменим цвета у оставшихся секторов диаграммы. Только вместо Нет заливки будем выбирать Сплошная заливка и соответствующий цвет.
Рисуем стрелку
Добавим данные по фактической величине выручке (которую будет показывать стрелка на спидометре).
Затем создадим еще одну вспомогательную таблицу — для отображения стрелки.
В этой таблице также есть Невидимая часть, которая равна 100%.
Левая часть до стрелки показывает величину отклонения стрелки (насколько стрелка поднимется от нуля). Формула расчета приведена рядом с таблицей:
Фактическое значение / Максимальное значение
Величина стрелки фиксированная, зададим ее 2%.
Правая часть равно 100% минус сумма Левой части и Стрелки.
Теперь нужно добавить данные из второй вспомогательной таблицы на диаграмму. Для этого щелкнем правой кнопкой мыши на области диаграммы — Выбрать данные.
Нажмем Добавить.
Имя ряда можно не заполнять, активируем поле Значения и выделим значения из вспомогательной таблицы (второй).
Теперь изменим тип диаграммы для получившегося второго ряда на Круговую.
Для этого щелкнем на внешнем круге правой кнопкой мыши — Изменить тип диаграммы для ряда.
Меняем тип диаграммы для Ряда 2 на Круговая и обязательно ставим галочку Вспомогательная ось.
Не пугаемся, что наш первый ряд(сама шкала) куда-то исчез ))) Мы его скоро увидим снова.
Если присмотритесь к получившейся диаграмме, то маленький серый сектор — это и есть будущая стрелка. Только она почему-то внизу. Исправим это.
Правая кнопка мыши на круговой диаграмме — Формат ряда данных — Параметры ряда — Угол поворота правого сектора 90 градусов.
Теперь стрелка встала в нужное положение.
Осталось убрать заливку со всех секторов круга, кроме маленького серого сектора (стрелка). Сделаем это точно так же, как мы делали с невидимой часть шкалы (правая кнопка — Формат ряда данных — Заливка и границы — Нет заливки).
И вот уже стрелка почти готова.
Настроим внешний вид стрелки. Для этого выделим ее двойным щелчком и в Параметрах ряда в блоке Граница выберем Сплошная линия и черный цвет.
Теперь изменим значение для сектора стрелки. Было 2% — сделаем 0%.
Диаграмма практически готова.
Для наглядности можно добавить отображение фактического значения рядом со стрелкой.
Для этого выберем подходящую геометрическую фигуру, например, прямоугольник со скругленными углами. Нарисуем его рядом со стрелкой и уберем заливку.
Чтобы фактическое значение попадало в прямоугольник автоматически, выделим его и сразу установим курсор в строку формул. В строке формул напишем знак равно — и сошлемся (выделим) ячейку с фактическим значением. Выровняем текст в прямоугольнике посередине и по центру. Готово!
Диаграмма интерактивна, если меняется значение фактической выручки — стрелка перемещается в нужную позицию.
Статья взята с моего сайта https://excel-analytics.ru/