Как сделать в excel спидометр?

Excel для финансиста

Поиск на сайте

Диаграмма-спидометр в Excel — анализ план-факт

Для наглядного сравнения фактических данных с плановыми используются разные виды диаграмм. В этой статье описывается построение диаграммы, похожей на автомобильный спидометр. В основе такой диаграммы лежит круговая диаграмма 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» (удвоенное максимальное значение показателя минус два предыдущих значения.

Постройте кольцевую диаграмму: выделите ячейки F16:F19, меню Вставка – Диаграммы — Другие диаграммы — Кольцевая.

На полученной диаграмме на графической области нажмите правой кнопкой мыши, выберите Формат ряда данных…

В открывшемся окне в разделе Параметры ряда, Угол поворота первого сектора введите 270. Диаграмма повернётся, теперь все нужные области составляют верхний полукруг диаграммы:

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

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

Нажмите правой кнопкой на всей диаграмме, выберите в контекстном меню Выбрать данные, затем нажмите кнопку Добавить, введите в поле Значения диапазон F23:F25:

Теперь выберите только новое кольцо на диаграмме, нажмите правую кнопку мыши, выберите �?зменить тип диаграммы для ряда, выберите круговую диаграмму.

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

Теперь спрячьте все части новой диаграммы, кроме стрелки (правой клавишей контекстное меню Формат точки данных, разделе ЗаливкаНет заливки). Теперь видно зоны и стрелку в виде сектора.

Осталось придать стрелке наглядный вид. Выберите стрелку, снова контекстное меню Формат точки данных, раздел Цвет границы – выберите Сплошная линия, задайте чёрный цвет. В разделе Стили границ установите ширину границы – 1,5 или 2 пт. Теперь хитрость: в ячейку F24 введите ноль. Диаграмма приобретёт более наглядный вид:

Осталось подписать стрелку. Снова правой кнопкой мыши на диаграмме, выбрать Добавить подписи данных. Появятся подписи, из них можно удалить всё, кроме 0, выбирая по одной подписи. На оставшейся подписи двойной щелчок мышью – откроется режим редактирования. Мышью щёлкните на области формул, затем на ячейке F23, теперь рядом со стрелкой отображается фактическое значение показателя.

Наглядная диаграмма-спидометр в Excel готова!

Как построить диаграмму типа спидометр в MS Excel?

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

Такая диаграмма отлично встраивается в отчеты и дашборды, т.к. весьма информативна, не занимает много места и на вид красива. Однако этой диаграммы нет в стандартном наборе Excel (во всяком случае пока). Для ее построения, придется использовать доступные средства, скомбинировав кольцевую и круговую диаграммы, для шкалы и стрелки, соответственно.

Вначале об общем принципе. Шкала – это верхняя половина кольцевой диаграммы. Нижняя половина также есть, но она прозрачная. Стрелка – это контур видимого сектора круговой диаграммы. Там же есть еще два сектора, но они прозрачны. Местоположение стрелки определяет измеряемый показатель.

Теперь изучим, как сделать диаграмму-спидометр в Excel. Вначале подготовим данные для шкалы, для чего нужно задать 4 значения: величина нижней прозрачной части, красной, желтой и зеленой зоны (цвета и их количество, разумеется, можно выбирать самостоятельно). Т.к. прозрачная часть занимает половину диаграммы, то она должна быть равна сумме трех цветов. Для простоты пусть весь циферблат занимает 100 делений. Тогда красная зона (плохо) – 50, желтая (нормально) – 30 и зеленая (хорошо) – 20 (50+30+20=100). Чтобы получился полукруг, невидимая часть также должна быть равна 100.

Выделяем весь диапазон и создаем кольцевую диаграмму.

По умолчанию получится следующее.

В параметрах ряда делаем поворот на 90⁰.

Удаляем название и легенду.

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

Читать еще:  Как сделать постоянную шапку в excel?

Получаем циферблат спидометра.

Теперь сделаем стрелку. Подготовим данные, по которым будут строится три сектора круговой диаграммы. Первый сектор – от нуля до стрелки (прозрачный), второй – стрелка, третий – оставшаяся часть круга (прозрачный).

На этот раз секторы должны быть подвижными и зависеть от измеряемого показателя. Результатом будет «отклонение стрелки» на соответствующую величину. Пусть показатель измеряется в процентах и его первоначальное значение равно 60%.

Как и с циферблатом, диапазон от 0 до 100% должен приходиться на верхний полукруг. Тогда весь круг – это 200%. Чтобы стрелка меняла свое положение, первый сектор (от которого строятся остальные) привяжем к значению измеряемого показателя. Стрелка имеет фиксированный размер, установим пока 2% (потом вообще уберем). Последний сектор – это разница между 200% и суммой первых двух секторов.

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

Указываем источник данных (диапазон из трех значений) и ОК. Должно получиться примерно следующее.

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

Меняем диаграмму на круговую.

Обязательно нужно выбрать вспомогательную ось, как на рисунке, иначе стрелка окажется под циферблатом. Осталось повернуть диаграмму на 270⁰ и сделать прозрачными ненужные секторы.

Не забываем убрать контуры секторов.

Чтобы стрелка была больше похожа на стрелку, а не на узкий кусочек пирога, зададим для нее вначале черный (или другой) контур, а затем ширину сектора вместо 2% сделаем 0%.

Сектор исчезнет, а контур превратится в черную линию.

Для более точного и отображения снизу можно добавить цифровое значение показателя. Это сделать очень просто. Вставим прямоугольник с закругленными углами.

Сделаем прозрачный фон, красный контур. Затем выделим полученную фигуру, поставим курсор в строку формул и сделаем ссылку на отображаемое значение.

Отформатируем, как нужно и получим окончательный вид спидометра.

Остался один нюанс. Дело в том, что, если значение выйдет за пределы от 0 до 100%, то стрелка окажется не известно где.

Чтобы исправить возможную ошибку, с помощью функции ЕСЛИ в формуле, определяющей отклонение стрелки, зададим минимальное значение 0 и максимальное 100%.

Примерно так рисуется «классический» спидометр.

Иногда диапазон возможных значений нельзя разделить четкими границам типа «плохо», «нормально», «хорошо». Четких границ может не быть, тогда потребуется плавный переход от одного цвета к другому. Например, когда в качестве результата получается некоторая вероятность (p-value, бинарная классификация и др.) или измеряется уровень дефицита запасов, где также нет четких границ и хотелось бы подчеркнуть их размытость. В этом случае для шкалы спидометра следует использовать градиентную заливку. В целом диаграмма строится также, но ее циферблат состоит из одного цвета, плавно переходящего в другой.

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

Используйте диаграмму спидометр в Excel, чтобы удивить своих коллег, а также руководство. По кнопке ниже можно скачать файл с примерами.

Строим спидометр в Excel 2

Друзья, наступил учебный год – а значит пришло время новых знаний и новых открытий. Сегодня хочется поделиться с вами секретами построения в 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 градусам, в будущем — указатель).

4. Изменяем заливку единственного оставшегося непрозрачного сектора круговой диаграммы на черный. Получаем указатель.

Поворачиваем ОБЕ диаграммы на 90 градусов (кликаем по ним правой кнопкой, вносим изменения в меню «Формат ряда данных – Параметры – Угол первого сектора — 90»).
Таким образом, отсчет секторов (в нашем примере 180-45-90-45 и 180-159.2-3-17.8) будет начинаться не с крайней верхней точки, а с крайней правой. Тогда именно на верхнюю половину диаграмм будут приходиться сектора 45-90-45 и сектор с указателем, что и будет напоминать спидометр.

Читать еще:  Как в excel сделать автоматическую нумерацию строк при удалении?

5. При вызове правой кнопкой мыши меню выбираем «Формат ряда данных – Параметры – Диаметр отверстия – 90%». Таким образом, мы сужаем сектора-индикаторы высокий-средний-низкий.

6. Меняем цвет заливки нижней области с синего на прозрачный, верхних на красный-желтый-зеленый. Меняем цвет указателя на черный, удаляем легенду для диаграммы.

7. Добавляем подпись для указателя (2 клика правой кнопкой по указателю – «Добавить подпись данных»). Автоматически выставляется значение, по которому строилась диаграмма, то есть 3 (градуса).

8. Изменяем источник данных для подписи на формулу ячейки, в которой содержится значение указателя (то есть 89). Для этого дважды кликаем правой кнопкой мыши на подписи и в строке формул вводим номер ячейки.

9. Еще раз убедимся, что спидометр «исправен». Заменим исходное значение 89% на 15%. Вуаля. Диаграмма–спидометр к вашим услугам. Значение ниже среднего – а значит, пора действовать!

Трюк №57. Как в Excel создать диаграмму спидометра?

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

Мастер диаграмм Excel предлагает множество различных типов диаграмм, кроме, к сожалению, диаграммы спидометра. Диаграмма спидометра — это весьма ловкий способ представления данных. При помощи трюков этого раздела можно создать диаграмму спидометра и добавить полосу прокрутки панели инструментов Элементы управления (Control Toolbox), которая будет изменять диаграмму и одновременно данные на рабочем листе.

Сначала нужно настроить некоторые данные (рис. 5.28) и создать кольцевую диаграмму (doughnut chart). Кольцевые диаграммы работают схожим образом с круговыми, но они могут содержать несколько рядов, в отличие от круговых.

Рис. 5.28. Данные для диаграммы спидометра

Нажмите сочетание клавиш Alt/Apple+

, чтобы отобразить формулы на рабочем листе. Можно воспользоваться командой Сервис → Параметры → Вид (Tools → Options → View) и установить флажок Формулы (Formulas), хотя это и более длинный путь.

Теперь выделите диапазон В2:В5 и запустите мастер диаграмм. На первом шаге мастера перейдите на вкладку Стандартные (Standard Types) (хотя она должна раскрываться по умолчанию). Затем в группе Тип (Chart Type) выберите вариант Кольцевая (Doughnut). Щелкните кнопку Далее (Next), чтобы перейти ко второму шагу мастера, и удостоверьтесь, что данные выводятся на диаграмму по строкам (в столбцах). Щелкните кнопку Далее (Next), чтобы перейти к шагу 3. Если необходимо, можно что-либо изменить на этом шаге, но для этого трюка настройка этого шага не обязательна. Щелкните кнопку Далее (Next), чтобы перейти к четвертому шагу, и удостоверьтесь, что диаграмма будет создана как объект на текущем рабочем листе (и снова это параметр по умолчанию). Создание диаграммы как объекта упростит работу с ней при настройке спидометра (рис. 5.29).

Рис. 5.29. Обычная кольцевая диаграмма

Выделите кольцевую диаграмму, медленно дважды щелкните самый большой сектор, чтобы выделить его, а затем щелкните его правой кнопкой мыши, в контекстном меню выберите команду Формат точки данных (Format Data Point) и перейдите на вкладку Параметры (Options). Выберите угол поворота для этого сектора равным 90 градусам. Щелкните вкладку Вид (Patterns), выберите невидимую границу и прозрачную заливку, затем щелкните кнопку ОК. По очереди каждый из оставшихся секторов дважды медленно щелкните, затем дважды щелкните, чтобы открыть диалоговое окно Формат элемента данных (Format Data Series), и выберите нужный цвет. Кольцевая диаграмма должна выглядеть, как на рис. 5.30.

Рис. 5.30. Заготовка спидометра

Необходимо добавить еще один ряд (Series 2, Ряд 2) значений, чтобы создать циферблат. Снова выделите диаграмму, щелкните ее правой кнопкой мыши, в контекстном меню выберите команду Исходные данные (Source Data) и перейдите на вкладку Ряд (Series). Щелкните кнопку Добавить (Add), чтобы создать новый ряд, и в поле Значения (Values) выберите диапазон С2:С13. Еще раз щелкните кнопку Добавить (Add), чтобы добавить третий ряд (Series 3, Ряд 3), отвечающий за стрелку, и в поле Значения (Values) выберите диапазон Е2:Е5. Результат должен выглядеть, как на рис. 5.31.

Рис. 5.31. Кольцевая диаграмма с несколькими рядами

Теперь спидометр начинает обретать свой вид. Если вы хотите добавить подписи, стоит загрузить специальные утилиты (John Walkenbach’s Chart Tools) с нашей страницы загрузок. Часть этой надстройки, которая, к сожалению, работает только в Windows, предназначена специально для меток данных. Она позволяет указывать диапазон на рабочем листе, на основе которого будут создаваться подписи данных для рядов диаграммы. Надстройка Джона также поддерживает возможности, перечисленные в следующем списке.

  • Chart Size (Размер диаграммы). Позволяет указывать точный размер диаграммы, а также приводить все диаграммы к одному размеру.
  • Export (Экспортировать). Позволяет сохранять диаграммы в виде файлов в формате .gif, .jpg, .tif и .png.
  • Picture (Рисунок). Преобразует диаграмму в рисунок (цветной или черно-белый).
  • Text Size (Размер текста). Фиксирует размер всех текстовых элементов диаграммы. Когда размер диаграммы меняется, текстовые элементы сохраняют свой размер.
  • Chart Report (Отчет диаграммы). Генерирует отчет для всех диаграмм или подробный отчет для одной диаграммы.

При помощи этой надстройки отформатируйте ряд Series 2 (Ряд 2), чтобы он отображал подписи данных, взятые из диапазона D2:D13. He сбрасывая выделение Series 2 (Ряд 2), дважды щелкните его, чтобы открыть диалоговое окно Формат ряда данных (Format Data Series). Перейдите на вкладку Вид (Patterns) и выберите невидимую границу и прозрачную заливку. Диаграмма должна выглядеть, как на рис. 5.32.

Рис. 5.32. Улучшенная диаграмма спидометра с добавленными подписями

Выделите ряд Series 3 (Ряд 3), затем щелкните его правой кнопкой мыши и в контекстном меню выберите команду Тип диаграммы (Chart Type). Выберите круговую диаграмму. Да, это выглядит немного странно (рис. 5.33). Но будьте уверены, если круговая диаграмма перекрывает кольцевую диаграмму, вы все сделали правильно.

Рис. 5.33. Круговая диаграмма перекрывает диаграмму спидометра

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

Читать еще:  Как сделать раскрывающийся список с календарем в excel?

Рис. 5.34. Разобранная и уменьшенная круговая диаграмма

Теперь выделите всю круговую диаграмму, дважды щелкните ее, выберите команду Формат ряда данных (Format Data Series) и перейдите на вкладку Параметры (Options). Измените угол поворота первого сектора на 90 градусов. По очереди выделяйте все секторы круговой диаграммы, щелкайте их правой кнопкой мыши и в диалоговом окне Формат рядов данных (Format Data Series) переходите на вкладку Вид (Patterns). Для параметров Граница (Border) и Заливка (Area) выбирайте значение Невидимая (или Прозрачная) (None) для всех секторов, кроме третьего, который следует залить черным цветом. Вы получите диаграмму, показанную на рис. 5.35.

Рис. 5.35. Диаграмма спидометра, на которой окрашен только третий сектор круговой диаграммы

Чтобы добавить легенду, выделите диаграмму, щелкните ее правой кнопкой мыши, в контекстном меню выберите команду Параметры диаграммы (Chart Options) и перейдите на вкладку Подписи данных (Data Labels). Установите флажок Ключ легенды (Legend Key). Вы увидите спидометр (рис. 5.36). Теперь перемещайте диаграмму, изменяйте размер и редактируйте ее, как необходимо. Теперь, когда диаграмма спидометра создана, нужно создать полосу прокрутки с панели инструментов Элементы управления (Control Toolbox) и связать полосу прокрутки и диаграмму.

Рис. 5.36. Диаграмма спидометра с легендой

Для этого правой кнопкой мыши щелкните область панелей инструментов на экране — это верхняя область экрана, где расположены панели инструментов Стандартная (Standard) и Форматирование (Formatting), — и выберите команду Элементы управления (Control Toolbox). Выберите инструмент полосы прокрутки и перетащите его в нужное место на рабочем листе. Выделите полосу прокрутки, щелкните ее правой кнопкой мыши и в контекстном меню выберите команду Свойства (Properties). Откроется диалоговое окно Свойства (Properties). В поле LinkedCell выберите ячейку F3, укажите максимальное значение 100 и минимальное значение 0. Закрыв это диалоговое окно и переместив полосу прокрутки на диаграмму, вы увидите приблизительно то же, что и на рис. 5.37.

Рис. 5.37. Законченная диаграмма спидометра

Щелкая стрелки или перетаскивая ползунок на полосе прокрутки, вы будете изменять показания спидометра, но помните, что при этом также изменяются данные на связанном со спидометром рабочем листе.

Строим спидометр в Excel 2

Друзья, наступил учебный год – а значит пришло время новых знаний и новых открытий. Сегодня хочется поделиться с вами секретами построения в 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 градусам, в будущем — указатель).

4. Изменяем заливку единственного оставшегося непрозрачного сектора круговой диаграммы на черный. Получаем указатель.

Поворачиваем ОБЕ диаграммы на 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%. Вуаля. Диаграмма–спидометр к вашим услугам. Значение ниже среднего – а значит, пора действовать!

Ссылка на основную публикацию
Adblock
detector