Для наглядного сравнения фактических данных с плановыми используются разные виды диаграмм. В этой статье описывается построение диаграммы, похожей на автомобильный спидометр. В основе такой диаграммы лежит круговая диаграмма Excel:
На диаграмме есть несколько зон и стрелка, показывающая фактическое значение контролируемого показателя:
Данный пример построен на расчёте точки безубыточности в Excel. Красная зона символизирует зону убытка для компании, это критическая ситуация. Желтая зона – компания в прибыли, но план продаж не достигнут. Зелёная зона – план выполнен или перевыполнен, всё хорошо. Положение стрелки на диаграмме наглядно показывает фактическое значение и запас прочности.
В разделе Расчёт точки безубыточности и запаса прочности (ячейка В21 и рядом) уже рассчитана плановая точка безубыточности. Для построения диаграммы «план-факт» нужны также данные максимально возможного объёма продаж (это определяет ширину зелёной зоны) и фактические данные за исследуемый период.
Диаграмма-спидометр будет состоять из двух диаграмм Excel, наложенных друг на друга: кольцевая диаграмма для отображения зон и круговая диаграмма Excel для отрисовки стрелки. Для построения этих диаграмм нужны дополнительные промежуточные данные в ячейках F16:F25.
Рассчитайте ширину зон. Красная зона – от 0 до точки безубыточности, поэтому в ячейке F16 формула «=С24». Жёлтая зона – от точки безубыточности до планового значения: в ячейке F17 ширина зоны рассчитана как «=C23-F16». Зелёная зона – от планового значения до максимально возможного: в ячейке F18 формула «=C29-C23». В ячейке F19 суммируются все эти значения (можно просто подставить максимальное значение показателя), эта ячейка нужна для построения кольцевой диаграммы, но отображаться это значение не будет.
Для построения круговой диаграммы, отображающей стрелку, необходимо три значения. В ячейку F23 скопировано ссылкой значение фактического показателя: «=C30». В ячейке F24 задаётся толщина стрелки, пока поставьте сюда 1. Плюсом необходимо задать пустую область формулой: «=C29*2-F23-F24» (удвоенное максимальное значение показателя минус два предыдущих значения.
На полученной диаграмме на графической области нажмите правой кнопкой мыши, выберите Формат ряда данных…
В открывшемся окне в разделе Параметры ряда, Угол поворота первого сектора введите 270. Диаграмма повернётся, теперь все нужные области составляют верхний полукруг диаграммы:
Теперь нужно раскрасить зоны в нужные цвета, а нижний полукруг спрятать. Выделяйте один за другим все зоны, вызывайте правой клавишей контекстное меню Формат точки данных, теперь в разделе Заливка справа выбирайте Сплошная заливка, задавайте нужный цвет области.
Для нижнего полукруга цвет задавать не надо, выберите в этом меню Нет заливки. Первая диаграмма готова, очередь за стрелкой.
Нажмите правой кнопкой на всей диаграмме, выберите в контекстном меню Выбрать данные, затем нажмите кнопку Добавить, введите в поле Значения диапазон F23:F25:
Теперь выберите только новое кольцо на диаграмме, нажмите правую кнопку мыши, выберите �?зменить тип диаграммы для ряда, выберите круговую диаграмму.
Получится круговая диаграмма, которая наложится на построенную кольцевую. Нажмите на этой диаграмме правой кнопкой, выберите Формат ряда данных, в открывшемся окне также задайте угол поворота первого сектора 270 градусов и отметьте Построить ряд – По вспомогательной оси.
Теперь спрячьте все части новой диаграммы, кроме стрелки (правой клавишей контекстное меню Формат точки данных, разделе Заливка – Нет заливки). Теперь видно зоны и стрелку в виде сектора.
Осталось придать стрелке наглядный вид. Выберите стрелку, снова контекстное меню Формат точки данных, раздел Цвет границы – выберите Сплошная линия, задайте чёрный цвет. В разделе Стили границ установите ширину границы – 1,5 или 2 пт. Теперь хитрость: в ячейку F24 введите ноль. Диаграмма приобретёт более наглядный вид:
Осталось подписать стрелку. Снова правой кнопкой мыши на диаграмме, выбрать Добавить подписи данных. Появятся подписи, из них можно удалить всё, кроме 0, выбирая по одной подписи. На оставшейся подписи двойной щелчок мышью – откроется режим редактирования. Мышью щёлкните на области формул, затем на ячейке F23, теперь рядом со стрелкой отображается фактическое значение показателя.
Друзья, наступил учебный год – а значит пришло время новых знаний и новых открытий. Сегодня хочется поделиться с вами секретами построения в MS Excel одной нестандартной диаграммы, а именно – диаграммы-спидометра.
Почему она удобна? Очень часто хочется избежать траты времени на разглядывание разных графиков и таблиц и в то же время иметь возможность понять текущий статус (проекта, бюджета,итд). Не случайно ее очень часто используют в дэшбордах – ведь по аналогии с автомобильным спидометром, водитель (аналитик) лишь по одному взгляду на прибор (диаграмму) определяет скорость (статус) и оперативно принимает решения.
Итак, в силу отсутствия этой диаграммы среди стандартных средств Excel, раскрываем секрет ее самостоятельного создания. «Спидометр» состоит из двух круговых диаграмм, одна из которых служит основой для секторов – индикаторов выполнения, а другая – для указателя спидометра.
Имеем: исходные данные: факт выполнения плана продаж на 89%
Факт
А также критическое нижнее значение плана – 25% и «средний» диапазон 25%-75%.
Уровни
Для первой диаграммы с индикаторами получаем таблицу:
В сумме – 360 градусов, то есть весь круг. Высчитывание секторов происходит по принципу расчета % от 180 градусов – верхнего полукруга диаграммы. Соответственно, три сектора будут иметь значения для нашего примера: 25%*180 градусов, (75%-25%)*180 градусов, (100%-75%)*180 градусов.
Определим значения для второй диаграммы – указателя. Чтобы он был достаточно узким, зададим угол 3 градуса. Соответственно, он будет разбивать верхнюю половину круга (и 180 градусов) на 2 части: 89%*180 градусов и 11%*180 градусов. Вычтем из первого значения единицу, чтобы компенсировать место, занимаемое стрелкой. Получим (180 – 89% — 1 ) для первого блока, что равно 159.2. Для второго блока значение фиксируем на 3, для третьего вычисляем 180-3-(180-89%-1). Везде вместо 89% указываем ячейку, в которой это значение хранится.
1. По двум столбцам строим круговую диаграмму. Так как Excel не умеет строить 2 разные диаграммы одновременно, он их помещает в одну область, накладывая друг на друга.
2. Выделив круговую диаграмму (автоматически выделяется верхняя), изменяем ее тип с «круговой» на «кольцевую». Она автоматически уйдет на задний план. Таким образом, из 2 диаграмм круговая (в будущем – указатель) будет на переднем плане, кольцевая (в будущем – индикаторы низкий-средний-высокий) будет на заднем плане.
3. Для круговой диаграммы дважды кликаем на одном из секторов, изменяя его заливку на прозрачную. Аналогичное действие повторяем для остальных секторов за исключением самого узкого (в таблице его значение равно 3 градусам, в будущем — указатель).
Поворачиваем ОБЕ диаграммы на 90 градусов (кликаем по ним правой кнопкой, вносим изменения в меню «Формат ряда данных – Параметры – Угол первого сектора — 90»). Таким образом, отсчет секторов (в нашем примере 180-45-90-45 и 180-159.2-3-17.8) будет начинаться не с крайней верхней точки, а с крайней правой. Тогда именно на верхнюю половину диаграмм будут приходиться сектора 45-90-45 и сектор с указателем, что и будет напоминать спидометр.
5. При вызове правой кнопкой мыши меню выбираем «Формат ряда данных – Параметры – Диаметр отверстия – 90%». Таким образом, мы сужаем сектора-индикаторы высокий-средний-низкий.
6. Меняем цвет заливки нижней области с синего на прозрачный, верхних на красный-желтый-зеленый. Меняем цвет указателя на черный, удаляем легенду для диаграммы.
7. Добавляем подпись для указателя (2 клика правой кнопкой по указателю – «Добавить подпись данных»). Автоматически выставляется значение, по которому строилась диаграмма, то есть 3 (градуса).
8. Изменяем источник данных для подписи на формулу ячейки, в которой содержится значение указателя (то есть 89). Для этого дважды кликаем правой кнопкой мыши на подписи и в строке формул вводим номер ячейки.
9. Еще раз убедимся, что спидометр «исправен». Заменим исходное значение 89% на 15%. Вуаля. Диаграмма–спидометр к вашим услугам. Значение ниже среднего – а значит, пора действовать!
Диаграмма в виде спидометра показывает шкалу с зонами (уровни достижения цели) и стрелкой, указывающей текущее положение.
Такой вид визуализации интуитивно понятен, так как мы сталкиваемся с ним в своей повседневной жизни: приборная панель автомобиля, кухонные приборы и т.д.
К тому же они гармоничны и не занимают много места, и поэтому их можно устанавливать до нескольких десятков в одном отчете.
В стандартном наборе Excel диаграмма спидометра отсутствует. Чтобы построить такую диаграмму, нам потребуется прибегнуть к хитрости.
Для этого нам понадобятся две диаграммы: круговая и кольцевая, одна из которых будет изображать шкалу спидометра, другая – стрелку.
Разберем пошагово как такой построить. Знаю несколько вариантов строительства, в конце статьи поделюсь со всеми желающими ещё одним:)
Шкала
Шаг 1. Таблица
Составляем таблицу шкалы нашего спидометра, так как эта диаграмма представляет собой половинку круга, то нам понадобится дополнительная строка «Пусто» в таблице, равная сумме всех значений зон шкалы.
Тих шкал может быть 3, 4, 5. При желании можете указывать свое количество.
Шаг 2. Кольцевая диаграмма
Строим кольцевую диаграмму на основе таблицы из п.1 Вставка => Просмотр всех диаграмм => Все диаграммы => Круговая => Кольцевая
Удаляем название и легенду (выделяем и нажимаем Delete на клавиатуре)
Шаг 3. Скрываем лишние шкалы
Шаг 4. Меняем цвета заливки шкал
В остальных зонах также меняем цвет заливки на красный/желтый/зеленый. И убираем контуры у секторов.
Шаг 5. Разворачиваем
Чтобы развернуть график, щелкаем в любом месте графика правой кнопкой мыши, в диалоговом окне Формат ряда данных меняем угол поворота первого сектора на 270 градусов.
Тут ключевой момент в том, что в следующих шагах мы скопируем этот график, чуток изменим и положим друг на друга.
Есть ещё один способ, без наложений, но о нем расскажу позже тем, кто заберет подарок в конце статьи.
Почти готово, осталось нарисовать стрелку.
Стрелка
Шаг 6. Таблица для стрелки
Шаг 7. Копируем диаграмму для стрелки
Копируем (Ctrl+C), выделяем ячейку на листе и вставляем (Ctl+V) график шкалы. Щелкнув по графику, перетаскиваем рамочки на таблицу с данными для стрелки.
Шаг 8. Меняем цвета секторов
Меняем цвет заливки для секторов Пусто и Факт на Нет заливки, а сектор стрелки меняем на черный. Саму диаграмму также выделяем и убираем заливку (по умолчанию заливка сплошным белым цветом), чтобы можно было совместить с графиком шкалы.
Шаг 9. Меняем тип на круговую
Шаг 10. Радуемся результату
Теперь можно играть с фактическим значением и смотреть как стрелка меняет свое положение.
По-хорошему можно ещё добавить подписи секторам (красный, желтый, зеленый) и стрелке, но я бы хотел поделиться с вами ещё одним вариантом построения спидометра. Ключевое отличие — не нужно заниматься наложением одной диаграммы на другую. Всё построим на одной 🙂
Как построить диаграмму типа спидометр в MS Excel?
Правильно подобранное изображение может донести больше полезной информации, чем самая элегантная таблица. Очень эффектной является диаграмма типа спидометр, которая показывает текущее значение на некоторой ограниченной шкале. На этой диаграмме можно отображать, например, уровень KPI, выполнение плана, уровень обслуживания клиентов и другие показатели с ограниченным диапазоном изменения. Циферблат спидометра делится на зоны, стрелка показывает текущее состояние дел. Снизу можно добавить точное значение измеряемого показателя.
Такая диаграмма отлично встраивается в отчеты и дашборды, т.к. весьма информативна, не занимает много места и на вид красива. Однако этой диаграммы нет в стандартном наборе Excel (во всяком случае пока). Для ее построения, придется использовать доступные средства, скомбинировав кольцевую и круговую диаграммы, для шкалы и стрелки, соответственно.
Вначале об общем принципе. Шкала – это верхняя половина кольцевой диаграммы. Нижняя половина также есть, но она прозрачная. Стрелка – это контур видимого сектора круговой диаграммы. Там же есть еще два сектора, но они прозрачны. Местоположение стрелки определяет измеряемый показатель.
Теперь изучим, как сделать диаграмму-спидометр в Excel. Вначале подготовим данные для шкалы, для чего нужно задать 4 значения: величина нижней прозрачной части, красной, желтой и зеленой зоны (цвета и их количество, разумеется, можно выбирать самостоятельно). Т.к. прозрачная часть занимает половину диаграммы, то она должна быть равна сумме трех цветов. Для простоты пусть весь циферблат занимает 100 делений. Тогда красная зона (плохо) – 50, желтая (нормально) – 30 и зеленая (хорошо) – 20 (50+30+20=100). Чтобы получился полукруг, невидимая часть также должна быть равна 100.
Выделяем весь диапазон и создаем кольцевую диаграмму.
По умолчанию получится следующее.
В параметрах ряда делаем поворот на 90⁰.
Удаляем название и легенду.
Почти готово. Двойным нажатием заходим в каждый сектор и меняем цвет: нижний – прозрачный, остальные – красный, желтый и зеленый. Контур также убираем.
Получаем циферблат спидометра.
Теперь сделаем стрелку. Подготовим данные, по которым будут строится три сектора круговой диаграммы. Первый сектор – от нуля до стрелки (прозрачный), второй – стрелка, третий – оставшаяся часть круга (прозрачный).
На этот раз секторы должны быть подвижными и зависеть от измеряемого показателя. Результатом будет «отклонение стрелки» на соответствующую величину. Пусть показатель измеряется в процентах и его первоначальное значение равно 60%.
Как и с циферблатом, диапазон от 0 до 100% должен приходиться на верхний полукруг. Тогда весь круг – это 200%. Чтобы стрелка меняла свое положение, первый сектор (от которого строятся остальные) привяжем к значению измеряемого показателя. Стрелка имеет фиксированный размер, установим пока 2% (потом вообще уберем). Последний сектор – это разница между 200% и суммой первых двух секторов.
Теперь нужно добавить на имеющийся циферблат спидометра новый ряд данных, чтобы отобразить стрелку. Для этого нажимаем правой кнопкой мыши по диаграмме и в контекстном меню «Выбрать данные…».
Указываем источник данных (диапазон из трех значений) и ОК. Должно получиться примерно следующее.
Не нужно пугаться, вон тот оранжевый зародыш внизу – это будущая стрелка. Дело в том, что вместо нужной круговой диаграммы пока получилось второе кольцо. Поэтому в контекстном меню (через правую кнопку мыши) выбираем новый тип для этого ряда.
Меняем диаграмму на круговую.
Обязательно нужно выбрать вспомогательную ось, как на рисунке, иначе стрелка окажется под циферблатом. Осталось повернуть диаграмму на 270⁰ и сделать прозрачными ненужные секторы.
Не забываем убрать контуры секторов.
Чтобы стрелка была больше похожа на стрелку, а не на узкий кусочек пирога, зададим для нее вначале черный (или другой) контур, а затем ширину сектора вместо 2% сделаем 0%.
Сектор исчезнет, а контур превратится в черную линию.
Для более точного и отображения снизу можно добавить цифровое значение показателя. Это сделать очень просто. Вставим прямоугольник с закругленными углами.
Сделаем прозрачный фон, красный контур. Затем выделим полученную фигуру, поставим курсор в строку формул и сделаем ссылку на отображаемое значение.
Отформатируем, как нужно и получим окончательный вид спидометра.
Остался один нюанс. Дело в том, что, если значение выйдет за пределы от 0 до 100%, то стрелка окажется не известно где.
Чтобы исправить возможную ошибку, с помощью функции ЕСЛИ в формуле, определяющей отклонение стрелки, зададим минимальное значение 0 и максимальное 100%.
Примерно так рисуется «классический» спидометр.
Иногда диапазон возможных значений нельзя разделить четкими границам типа «плохо», «нормально», «хорошо». Четких границ может не быть, тогда потребуется плавный переход от одного цвета к другому. Например, когда в качестве результата получается некоторая вероятность (p-value, бинарная классификация и др.) или измеряется уровень дефицита запасов, где также нет четких границ и хотелось бы подчеркнуть их размытость. В этом случае для шкалы спидометра следует использовать градиентную заливку. В целом диаграмма строится также, но ее циферблат состоит из одного цвета, плавно переходящего в другой.
Сделать такую диаграмму также несложно. Отличие только в циферблате. В ролике ниже показана пошаговая инструкция, как в Excel сделать оба варианта спидометров.
Используйте диаграмму спидометр в Excel, чтобы удивить своих коллег, а также руководство. По кнопке ниже можно скачать файл с примерами.
Друзья, наступил учебный год – а значит пришло время новых знаний и новых открытий. Сегодня хочется поделиться с вами секретами построения в MS Excel одной нестандартной диаграммы, а именно – диаграммы-спидометра.
