Как создать спидометр в Excel для дашборда с игровым дизайном

Диаграмма спидометр, также известная как Gauge Charts, часто используется в дашбордах для визуализации ключевых показателей производительности (KPIs). А также для итоговых показателей в целом – например, индекс страха и жадности рынка акций, облигаций или криптовалют. Спидометр чарт позволяет пользователям быстро понять, где находится текущий показатель по отношению к установленной цели. Помогает пользователю мгновенно определить, находится ли показатель в низком, нормальном, предупреждающем или критическом диапазоне.

Пример как сделать красивый Gauge Chart в Excel

Создаем чарт спидометр с игровым дизайном в Excel. В ячейке A2 введем исходное значение General Productivity для примера 35%. В ячейке B2 вводим формулу вычисления остатка от 100%, в результате получим 65%.

Нам понадобится еще одно значение 50% для оформления дизайна диаграммы в стиле «Спидометр». Эта доля для нижней части.

Создаем таблицу для новой серии данных, которую будем использовать в дизайне в качестве шкалы спидометра. Всего 15 одинаковых значений из них 10 на шкалу спидометра и 5 на нижнюю часть диаграммы. Выделаем диапазон ячеек и создаем диаграмму в Excel.

Убираем все лишнее и делаем прозрачный фон, оставляем только контуры для шкалы.

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

Поворачиваем чарт на 240 градусов по часовой стрелке. Первый сектор делаем прозрачным фоном, второй черным и последний белым градиентом. Обратите внимание на настройки параметров белого градиента с элементами прозрачности.

Чтобы продемонстрировать градиентную заливку сектора диаграммы, временно сделаем фон черным цветом. В двух секторах убираем контуры.

Украшение шкалы спидометра из комбинированного градиента цветов

Создаем фигуру овал для оформления шкалы спидометра из комбинации микса полупрозрачных фигур с разными цветами. Каждая фигура оформляется полупрозрачным радиальным градиентом с разными углами заливки.

Копируем фигуру удерживая зажатой клавишу «Control» на клавиатуре. Изменяем цвет градиента и угол его наклона. Накладываем полупрозрачные фигуры и выравниваем их по центру.

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

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

Последний овал, красный цвет, первый план.

Группируем все овалы. Делаем прозрачный фон чарта. Изменяем порядок положения фигур и чарта, таким образом, чтобы на первом плане был чарт, а под ним фигуры.

Создаем новый рабочий лист с черным фоном. Переносим на новый лист чарт и фигуры. Изменяем размеры и оптимизируем положение объектов на рабочем листе Excel. Добавляем новый овал с черным радиальным полупрозрачным градиентом цвета заливки. И не забудем убрать контур. Обратите внимание что стиль градиента по центру.

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

Добавляем интерактивные элементы для управления чартом

Создадим данные для будущего счетчика. Он будет управлять спидометром. Поэтому теперь в ячейке A5 будет находиться исходное значение, а в ячейке A2 формула преобразования исходных значений в проценты. Ведь элемент управления формой - счетчик работает только с целыми числами.

Из вкладки меню разработчика выберите вставку элемента управления счетчика и нарисуйте его квадратом на новом листе.

Правой кнопкой мышки вызовите контекстное меню для указания минимальных и максимальных значений и ссылки на исходные данные чарта в параметрах настроек счетчика. Теперь мы имеем возможность управлять диаграммой. Мы сделали чарт спидометра интерактивным.

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

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

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

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

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

Отмечаем флажком какие данные следует использовать для подписи кнопок среза. Проверяем работоспособность формулы.

Переходим на вкладку меню «Формула» чтобы создать именной диапазон. Указываем имя «WeekDay». В качестве ссылки на диапазон вставляем формулу выборки значений из исходной таблицы (все ссылки в аргументах формулы должны быть абсолютными).

В параметрах счетчика изменяем ссылку на имя именного диапазона «WeekDay». Также изменяем исходно значение чарта в ячейке A5 на первом рабочем листе «Processing». Теперь там ссылка на именной диапазон «WeekDay».

Переносим срез и оформляем его дизайн внешнего вида. Срезы оформляются с помощью настройки шаблонов на вкладке Slicer. Поэтому нам нужно сначала продублировать уже созданный шаблон, чтобы получить его копию и уже ее модифицировать, а также указать в качестве используемого шаблона для среза.

Скачать шаблон спидометр чарт в Excel

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