Как сделать спидометр в экселе
Если Вы активно работаете с диаграммами в Excel, то наверняка знаете, что их в программе представлено достаточно много. Графики, гистограммы, линейчатые, круговые, лепестковые и т.д. Подходящий вариант для своих данных можно найти практически всегда. Однако, некоторые задачи визуализации решить стандартными средствами не получится.
Одна из самых распространенных проблем - отсутствие красивой и наглядной диаграммы для графического отображения единственного показателя (или, на языке диаграмм, единственной точки данных). Под это описание подходит, например, показатель KPI. Он обычно выражается в % в диапазоне от 0 до 100. Ни одна из встроенных диаграмм сходу не дает красивого и наглядного отображения такого показателя.
Но если проявить немного фантазии и навыков владения Excel, то можно создать собственные наглядные типы диаграмм на основе стандартных, которые красиво и легко решат поставленную задачу. Разберем построение трех типов таких диаграмм: Термометр , Круговая шкала и Спидометр . Если Вы не знаете как выполнить какое-то действие, указанное в статье, то можете посмотреть подробнейший пошаговый видеоурок по этой теме со всеми объяснениями . Видео найдете в конце статьи . Итоговый результат будет выглядеть вот так:
Термометр
Построение любой диаграммы начинается с подготовки исходных данных. В нашем случае у каждой диаграммы будет одна главная точка данных - показатель выполнения KPI в процентах от 0 до 100 . Для наглядности мы также создадим цветную шкалу, которая будет обозначать зоны выполнения KPI (Плохо - Нормально - Хорошо). Это будет второй набор данных.
Таким образом, для построения диаграммы Термометр нам нужны следующие небольшие таблички с данными:
Обратите внимание на таблицу для шкалы. Проценты в ней указаны по следующему принципу:
- Плохая зона - первые 30% (то есть от 0% до 30%)
- Нормальная зона - следующие 40% (то есть от 30% до 70%)
- Хорошая зона - последние 30% (то есть от 70% до 100%)
Общая сумма процентов, очевидно, должна быть равна 100%.
Построение диаграммы
Теперь по этим данным можно строить диаграмму. Выделяем таблицу со шкалой и строим " Нормированную гистограмму с накоплением ". Скорее всего, Excel построит три одинаковых столбца для каждого ряда: Плохо, Норма и Хорошо . Чтобы они приняли вид шкалы, надо выделить диаграмму и на вкладке " Конструктор " выбрать " Строка/столбец ". После выполнения этой команды шкала обретет нормальный вид.
Далее нужно убрать с нее все лишнее: название, подпись ряда данных. Также необходимо настроить цвет каждой зоны на шкале, и ширину диаграммы в целом и самой шкалы. Для изменения ширины самой шкалы, кликаем на ней правой кнопкой мыши, выбираем " Формат ряда данных " и меняем значение ползунка " Боковой зазор ". В итоге получится примерно следующее:
Теперь нужно на шкалу термометра добавить наш показатель KPI. Копируем его из нужной ячейки, выделяем диаграмму и нажимаем Ctrl+V . Скорее всего новые данные сольются с уже имеющейся шкалой и добавятся сверху зеленой зоны. Придется их донастроить вручную. Для начала меняем цвет самого верхнего сектора шкалы на черный. Затем кликаем правой кнопкой мыши на верхней части зеленой шкалы и выбираем " Изменить тип диаграммы для ряда ". В этом окне надо будет установить для нового добавленного ряда галочку " Вспомогательная ось " и тип диаграммы - " Гистограмма с группировкой ".
После нажатия кнопки OK нужно будет обязательно удалить вспомогательную ось с процентами и настроить ширину черного столбца ("ртутной полоски" нашего термометра). Останется только кликнуть на этом столбце правой кнопкой мыши и выбрать " Добавить подписи данных ". Диаграмма-термометр готова!
Круговая шкала
Для построение диаграммы с круговой шкалой нам, помимо самого показателя KPI, также понадобятся дополнительные данные для построения шкалы. В данном случае все просто. Нам нужен столбец из единичек. Количество единиц = количество делений на нашей круговой шкале. Для создания двадцати делений нам нужен будет столбец из двадцати единиц. Кроме того, понадобится не только показатель выполнения KPI, но и количество процентов, которых не хватило до 100%. Его вычислить просто: 1 - KPI . Исходные данные имеют такой вид:
Построение диаграммы
Построение начинается с создания шкалы. Выделяем столбец с единицами и строим кольцевую диаграмму. Удаляем всё лишнее: название и легенду. По умолчанию, диаграмма будет иметь очень пестрый вид. Каждый из 20 секторов будет окрашен в свой цвет.
Нужно будет вручную перекрасить шкалу в правильные цвета. Кликаете на кольцо правой кнопкой мыши и применяете нужную заливку сразу ко всем секторам. Затем придется задать вручную цвет для некоторых секторов. Итак, у нас 20 секторов. Мы знаем, что первые 30% - плохая зона. То есть первые 20 * 30% = 6 секторов будут красные. Следующие 8 секторов (40%) - будут желтые. Последние 6 секторов - зеленые.
Чтобы покрасить один сектор, кликните на кольцо, чтобы выделились все сектора. Затем кликните еще раз прямо по нужному сектору. Выделится только он. Измените его заливку. Затем, нажимая Tab , перемещайтесь на каждый следующий сектор и выбирайте нужные цвета. В итоге должно получиться вот так:
Теперь добавим на шкалу второе кольцо поверх первого. У него будет всего 2 зоны: выполненный KPI и невыполненный KPI. Копируем два числа из таблицы, выделяем диаграмму и жмем Ctrl+V . Появится второе (внешнее) кольцо на диаграмме. Кликаете на него правой кнопкой мыши и выбираете " Изменить тип диаграммы для ряда ". В окне изменения типа для Ряд2 включаете галочку " Вспомогательная ось ". Второе кольцо наложится сверху на первое.
Останется поменять заливку верхнего кольца. Для зоны выполненного KPI полностью убираете всю заливку, а для зоны невыполненного - ставите белую и задаете прозрачность в районе 20% (это делается на панели " Формат точки данных "). Также не лишним будет вставить внутрь кольца надпись, которая будет отображать процент выполнения KPI числом (для большей наглядности).
Стандартный список диаграмм Excel весьма ограничен и, чтобы построить что-то действительно интересное и необычное, приходится проявлять недюжинную фантазию. Одной из таких нетипичных диаграмм является так называемая "диаграмма-спидометр" (выглядит как полукруглая шкала прибора со стрелкой, показывающей текущее значение показателя).
Строится такая диаграмма на основе комбинированной диаграммы, составленной из кольцевой и круговой. Разберем пошагово процесс построения.
В качестве исходных данных нам нужна всего лишь одна цифра - процент выполнения плана. Это может быть стадия готовности проекта, показатель KPI, результаты тестирования и любые другие показатели, которые выражаются значением от 0% до 100% . Возьмем для примера данные готовности проекта.
В ячейке с выполнением плана (C4) находится формула: =C3/C2. Она вычисляет процент готовности путем деления количества завершенных этапов на общее число этапов проекта.
Далее нужно будет отдельно построить цветовую шкалу (шкала спидометра), и круговую диаграмму, которая будет рисовать стрелку спидометра.
Для создания шкалы понадобится кольцевая диаграмма. Она представляет собой круг (360 градусов). Чтобы шкала выглядела как полукруг, нижняя часть должна быть прозрачной (и равняться 180 градусам - половина круга). Оставшуюся половину можно разбить на любое количество зон, характеризующих тот или иной процент выполнения. Главное условие - сумма величин всех зон также должна равняться 180 градусам. В нашем примере будет 4 таких зоны. Таблица для построения шкалы выглядит так:
Чтобы нарисовать стрелку нам понадобится похожая таблица, но в ней всегда будет 4 части: прозрачный полукруг (180 градусов), размер стрелки (3-5 градусов будет достаточно), зона выполненного плана (180 умножить на % выполнения плана минус размер стрелки), зона остатка плана (180 минус зона выполненного плана минус размер стрелки).
Теперь, когда все данные подготовлены, переходим непосредственно к процессу построения. Выделяем в таблице "Шкала" столбец с числовыми значениями и выбираем "Вставка" - "Диаграммы" - "Кольцевая" .
После этого кликаем по-очереди по каждому сектору диаграммы (первый клик - выделяется кольцо, второй - выделяется конкретная точка данных) и окрашиваем его в нужный цвет (выбираем "Формат точки данных" - "Заливка" и задаем цвет). Там же желательно убрать границы, установив галочку "Нет линий". Для самого большого сектора нужно указать "Нет заливки", так как он должен быть прозрачным.
Теперь нужно добавить на диаграмму стрелку. Для этого кликаем на ней правой кнопкой мыши и нажимаем "Выбрать данные". В открывшемся окне нажимаем кнопку "Добавить".
Вводим имя ряда (например, "Стрелка") и указываем в качестве источника столбец с данными из таблицы для построения стрелки. На диаграмму добавится второй кольцевой ряд.
Для всех секторов этого ряда, кроме самого узкого - стрелки, нужно убрать заливку способом, описанным выше. Нелишним будет также убрать границы для всех точек, выбрав пункт "Нет линий". Саму стрелку можно перекрасить в удобный цвет.
Теперь нужно изменить тип диаграммы для стрелки, чтобы она перекрывала шкалу. Кликните правой кнопкой мыши на диаграмме и выберите "Изменить тип диаграммы. " Выберите "Комбинированная" и установите для ряда "Стрелка" тип "Круговая", а для второго ряда - "Кольцевая". Снимите все галочки, отвечающие за вспомогательную ось.
Если на предварительном просмотре стрелка не перекрывает шкалу, попробуйте поставить и убрать галочку напротив ряда "Стрелка".
Диаграмма выглядит почти так, как нужно. Осталось "повалить ее на бок". Для этого кликните правой кнопкой мыши ряду данных и выберите "Формат ряда данных". В появившейся панели переходим на вкладку "Параметры ряда" и указываем "Угол поворота первого сектора" равным 90 градусов.
Перевернется один из рядов (скорее всего, стрелка). Чтобы повернуть второй нажмите на стрелку рядом с надписью "Параметры ряда" на панели "Формат ряда данных" и выберите из списка второй ряд. После чего проделайте ту же операцию по повороту первого сектора.
Диаграмма в виде спидометра в Excel — комбинация круговой и кольцевой диаграммы, отображающая уровень показателя согласно заданной шкале с зонами.
Диаграмма спидометр (в англоязычной среде называют speedometer chart или gauge chart) может использоваться как часть дашборда (это визуализированный компактный отчет с ключевыми показателями бизнеса), для вставки в презентацию или просто для улучшения визуализации данных.
Однако почему диаграмму удобно представлять именно в виде спидометра?
Такой вид интуитивно удобен за счет схожести с автомобильным спидометром.
Представьте, ведь при управлении автомобилем (компанией) взгляд на спидометр (диаграмму) позволяет быстро оценить текущую ситуацию на дороге (в бизнесе) и в случае необходимости принять оперативные решения.
В стандартном виде график спидометр выглядит следующим образом:
Подготовка исходных данных
В первую очередь подготовим исходные данные для построения графика в виде спидометра: ряд для отображения шкалы (диапазон C4:C7) и ряд для отображения стрелки (диапазон F4:F7):
Обычно шкала делится на 3 основные зоны: красная, желтая и зеленая, при этом при попадании показателя в красную зону результат будет интерпретироваться как плохой, в желтую — хороший, в зеленую — отличный.
В зависимости от поставленной задачи можно варьировать количество зон оценки, их размер и т.д.
В данном примере зададим прозрачную часть равной 180 градусам (половине круга), остальные зоны рассчитаем из пропорции 70%/15%/15%, толщину стрелки примем равной 4 градусам.
Построение графика
Для построения графика в виде спидометра, выделяем диапазон данных C4:C7 и на панели вкладок нажимаем Вставка -> Диаграмма -> Круговая:
В итоге мы получаем круговую диаграмму со шкалой:
Далее добавим на диаграмму стрелку. Щелкаем правой кнопкой мыши по графику и нажимаем Выбрать данные, после чего добавляем диапазон F4:F7 в качестве нового ряда.
Внешний вид диаграммы не поменялся, за счет того, что шкала закрывает собой стрелку, поэтому перенесем ее на вспомогательную ось (щелкаем правой кнопкой по графику, далее выбираем Формат ряда данных -> Параметры ряда -> По вспомогательной оси), а затем меняем тип диаграммы для стрелки на кольцевую (щелкаем правой кнопкой по ряду со стрелкой и нажимаем Изменить тип диаграммы для ряда):
В результате получаем диаграмму, на которой на внешней части располагается шкала с зонами, на внутренней — стрелка:
Удаляем заливку с ненужных частей диаграммы, оставляем только шкалу и стрелку:
Для удобства чтения и восприятия графика повернем его на 90 градусов, в параметрах рядов (и для шкалы, и для стрелки) ставим угол 90 градусов в поле Угол поворота первого сектора:
Также выведем стрелку на диаграмме на передний план, для этого в параметрах ряда со стрелкой устанавливаем ряд на основную ось:
Далее настраиваем диаграмму на свой вкус — выбираем цвета для шкалы и стрелки, определяем оптимальную толщину стрелки, добавляем/удаляем легенду, подписи данных и прочее:
Подробно ознакомиться с вариантами построения графика спидометра — скачать пример.
Круговой индикатор выполнения (прогресс бар) в Excel
Подробно рассмотрим как построить и настроить внешний вид для кругового индикатора выполнения (прогресс-бар) в виде диаграммы в Excel.
Мы совсем недавно разбирали как можно создать линейный вариант прогресс-бара (перед прочтением данной статьи всячески рекомендую ознакомиться с разбором построения линейного графика), но полоска в виде горизонтальной линии далеко не единственный вариант отрисовки индикатора.
Достаточно популярным и внешне интересным является ещё круговой вид диаграммы, где как следует из названия, полоска представляет собой обычный круг.
Напомню, что прогресс бар — это графический элемент в виде полосы, показывающий статус процесса с помощью заполнения полосы по мере его выполнения.
Так же как и в прошлый раз предлагаю разобрать 2 варианта построения графика:
- Без делений на шкале; в этом случае круговая полоска нарисована как единое целое.
- С делениями. В этом случае на круге дополнительно отображается шкала с секциями, которая показывает уровни выполнения процесса.
Большая часть построений для кругового графика будет совпадать с построениями для линейного, но для новых читателей и для лучшего усвоения материала, основные моменты построения я по-прежнему буду подробно разбирать.
Теперь перейдем от теории к практике и приступим к построению.
Построение кругового индикатора в Excel
Вариант 1. Прогресс бар без шкалы
Для начала, как и всегда перед построением любого графика, создадим таблицу с данными, в которой будет всего 2 ряда: процент выполнения (к примеру, 78%) и недостающая остаточная часть до 100% (т.е. 22% = 100% — 78%):
Таблица получилась совсем небольшая и компактная, далее выделяем диапазон с данными таблицы A1:B2 и вставляем кольцевую диаграмму (в панели вкладок выбираем Вставка -> Диаграммы -> Круговая -> Кольцевая):
Следующим шагом зададим ширину кольца, которая отвечает за масштаб самого графика.
Щелкаем правой кнопкой мыши по ряду с данными и в контекстном меню выбираем Формат ряда данных -> Параметры ряда, и затем устанавливаем соответствующий параметр диаметра отверстия (рекомендую выбрать достаточно большое значение, но по желанию можно установить любое число от 0% до 90%, где 0% — полностью залитый круг, а 90% — кольцо):
Еще одной дополнительной возможностью настройки является изменение начала отсчета процентов на графике (т.е. с какого именно места начинает заполняться полоска) — мы можем с помощью задания значения для параметра Угол поворота первого сектора в настройке Параметры ряда повернуть наш график на любой угол.
В результате получаем следующий вид диаграммы:
Далее давайте настроим внешний вид кольца, в данном случае поменяем заливку отдельных полосок кольца на более нейтральные и контрастные цвета, чтобы внешне выделить данные:
Теперь выделим часть полоски кольца (которая как раз отвечает за процент выполнения) с помощью настройки толщины границы, тем самым еще более внешне выделив ряд с данными.
Для оставшейся части полоски границу вообще уберем, а для основной укажем достаточно большое значение (как раз чтобы она внешне стала заметно выделяться).
Щелкаем правой кнопкой мыши по соответствующему ряду с данными, в контекстном меню переходим Формат ряда данных -> Заливка и границы -> Граница, и указываем размеры границы:
Наша полоса существенно прибавила в весе, теперь поправим уже небольшие детали (уберем название и легенду с графика если они не нужны) и добавим подпись данных на диаграмму.
Однако тут нас ждет небольшая сложность, если добавить подпись стандартным способом (командой Добавить подпись данных в контекстном меню по щелчку мыши по ряду с данными), то полученный вид подписи будет слабо заметным:
Как мы видим, сейчас подпись добавилась для обеих частей круговой полоски, а также она расположена на самом кольце. Взамен этого можно добавить текстовое поле (в панели вкладок выбираем Вставка -> Текст -> Надпись), которое помещаем в центр диаграммы и прописываем в строке формул текстового поля ссылку на процент выполнения показателя:
Важный момент при добавлении текста с подписью.
Обратите внимание, что желательно добавить текстовое поле на график, когда диаграмма является выделенной и активной, т.е. идет работа непосредственно с диаграммой.
В этом случае текст как бы привязывается к объекту с диаграммой и при перемещении графика по листу или книге, текст будет перемещаться вместе с диаграммой. Если же этого не сделать, то график и текст будут жить по отдельности и друг от друга не зависеть.
Идем дальше, перейдем ко второму способу построения.
Вариант 2. Прогресс бар со шкалой
В данном варианте мы дополнительно пропишем подложку под шкалу в виде дополнительного ряда, поэтому таблица с данными претерпит незначительные изменения:
Шаг шкалы можно поставить абсолютно любой, при этом здесь в отличие от линейного индикатора даже не важно, чтобы сумма шагов давала 100% (в любом случае, так как это круговая диаграмма, то круг со шкалой заполнится полностью).
Но, чтобы снизить риск ошибки и не запутаться в размерах, все равно рекомендую стараться делать сумму шагов для шкалы равной 100%.
Выделяем диапазон с данными таблицы A1:B11 и, как и в предыдущем примере, строим кольцевую диаграмму:
Что у нас получилось: на внешнем кольце расположились данные с процентом выполнения, а на внутреннем — шкала с делениями.
Поменяем заливку каждого шага шкалы в подходящие цвета, поочередно выделяем каждое деление по отдельности и красим в соответствующий цвет (например, первые 4 деления делаем красным, следующие 3 — желтым, а замыкающие 3 — зеленым):
Как мы видим сама полоска с процентом выполнения и полоска со шкалой на диаграмме расположены рядом друг с другом, мы же хотим, чтобы они накладывались друг на друга. Поэтому щелкаем правой кнопкой мыши по любому из рядов с данными на диаграмме и в контекстном меню выбираем Изменить тип диаграммы для ряда:
И здесь для второго ряда (где находится данные с процентом выполнения) ставим галочку напротив поля Вспомогательная ось, тем самым мы этот ряд с данными сделаем находящимся над шкалой (т.е. условно говоря впереди его и ближе к нам):
Теперь мы на переднем плане видим саму полоску с процентом выполнения, а за ней на заднем плане находится шкала с делениями. Чтобы шкала стала видимой давайте настроим параметры прозрачности для основной полосы (щелкаем по ряду правой кнопкой мыши, в контекстном меню выбираем Формат ряда данных -> Заливка и границы -> Заливка):
В итоге после настройки прозрачности рядов получаем:
Уберем ненужные на текущий момент детали в виде названия и легенды с графика, а также добавим подпись данных на диаграмму таким же способом, как мы делали это в предыдущем примере с помощью текстового поля:
По желанию можно поэкспериментировать с цветами выделения основной полосы, к примеру, сделать ее более светлой, смело выбирайте подходящий для вас вариант:
Спасибо за внимание!
Если у вас есть вопросы или мысли по теме статьи — обязательно спрашивайте и пишите в комментариях, не стесняйтесь.
Читайте также: