Стандартный список диаграмм Excel весьма ограничен и, чтобы построить что-то действительно интересное и необычное, приходится проявлять недюжинную фантазию. Одной из таких нетипичных диаграмм является так называемая "диаграмма-спидометр" (выглядит как полукруглая шкала прибора со стрелкой, показывающей текущее значение показателя).
Строится такая диаграмма на основе комбинированной диаграммы, составленной из кольцевой и круговой. Разберем пошагово процесс построения.
В качестве исходных данных нам нужна всего лишь одна цифра - процент выполнения плана. Это может быть стадия готовности проекта, показатель KPI, результаты тестирования и любые другие показатели, которые выражаются значением от 0% до 100%. Возьмем для примера данные готовности проекта.
В ячейке с выполнением плана (C4) находится формула: =C3/C2. Она вычисляет процент готовности путем деления количества завершенных этапов на общее число этапов проекта.
Далее нужно будет отдельно построить цветовую шкалу (шкала спидометра), и круговую диаграмму, которая будет рисовать стрелку спидометра.
Для создания шкалы понадобится кольцевая диаграмма. Она представляет собой круг (360 градусов). Чтобы шкала выглядела как полукруг, нижняя часть должна быть прозрачной (и равняться 180 градусам - половина круга). Оставшуюся половину можно разбить на любое количество зон, характеризующих тот или иной процент выполнения. Главное условие - сумма величин всех зон также должна равняться 180 градусам. В нашем примере будет 4 таких зоны. Таблица для построения шкалы выглядит так:
Чтобы нарисовать стрелку нам понадобится похожая таблица, но в ней всегда будет 4 части: прозрачный полукруг (180 градусов), размер стрелки (3-5 градусов будет достаточно), зона выполненного плана (180 умножить на % выполнения плана минус размер стрелки), зона остатка плана (180 минус зона выполненного плана минус размер стрелки).
Теперь, когда все данные подготовлены, переходим непосредственно к процессу построения. Выделяем в таблице "Шкала" столбец с числовыми значениями и выбираем "Вставка" - "Диаграммы" - "Кольцевая".
После этого кликаем по-очереди по каждому сектору диаграммы (первый клик - выделяется кольцо, второй - выделяется конкретная точка данных) и окрашиваем его в нужный цвет (выбираем "Формат точки данных" - "Заливка" и задаем цвет). Там же желательно убрать границы, установив галочку "Нет линий". Для самого большого сектора нужно указать "Нет заливки", так как он должен быть прозрачным.
Теперь нужно добавить на диаграмму стрелку. Для этого кликаем на ней правой кнопкой мыши и нажимаем "Выбрать данные". В открывшемся окне нажимаем кнопку "Добавить".
Вводим имя ряда (например, "Стрелка") и указываем в качестве источника столбец с данными из таблицы для построения стрелки. На диаграмму добавится второй кольцевой ряд.
Для всех секторов этого ряда, кроме самого узкого - стрелки, нужно убрать заливку способом, описанным выше. Нелишним будет также убрать границы для всех точек, выбрав пункт "Нет линий". Саму стрелку можно перекрасить в удобный цвет.
Теперь нужно изменить тип диаграммы для стрелки, чтобы она перекрывала шкалу. Кликните правой кнопкой мыши на диаграмме и выберите "Изменить тип диаграммы..." Выберите "Комбинированная" и установите для ряда "Стрелка" тип "Круговая", а для второго ряда - "Кольцевая". Снимите все галочки, отвечающие за вспомогательную ось.
Если на предварительном просмотре стрелка не перекрывает шкалу, попробуйте поставить и убрать галочку напротив ряда "Стрелка".
Диаграмма выглядит почти так, как нужно. Осталось "повалить ее на бок". Для этого кликните правой кнопкой мыши ряду данных и выберите "Формат ряда данных". В появившейся панели переходим на вкладку "Параметры ряда" и указываем "Угол поворота первого сектора" равным 90 градусов.
Перевернется один из рядов (скорее всего, стрелка). Чтобы повернуть второй нажмите на стрелку рядом с надписью "Параметры ряда" на панели "Формат ряда данных" и выберите из списка второй ряд. После чего проделайте ту же операцию по повороту первого сектора.
Получим следующий результат:
Осталось убрать легенду, поменять название, сделать заливку фона и добавить под шкалу фигуру "Прямоугольник". Когда вставите ее, кликните по ней один раз и в строке формул введите ссылку на ячейку с % выполнения плана. Каждый раз при изменении процента число в прямоугольнике также будет меняться.
Файл пример с данными и построенной диаграммой можно найти вот в этом посте на нашем канале.
Поддержать наш проект и его дальнейшее развитие можно вот здесь.
Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot
С уважением, команда tDots.ru
