• En
  • АГЕНТСТВО
    • Agency
    • Агентство
    • Презентация
    • Портфолио
    • Behance
  • ← Блог

infographer

  • Теория
  • Примеры
    • Примеры
    • Критический взгляд
  • События
    • Текущие события
    • Прошлые события
  • Инструменты
  • Книги
    • Книги
    • Книжный магазин
  • Новости
Инструменты

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

10
Comments
Linkedin
2
ВКонтакте
0
Pocket

Друзья, наступил учебный год – а значит пришло время новых знаний и новых открытий. Сегодня хочется поделиться с вами секретами построения в MS Excel одной нестандартной диаграммы, а именно – диаграммы-спидометра.

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

Итак, в силу отсутствия этой диаграммы среди стандартных средств Excel, раскрываем секрет ее самостоятельного создания. «Спидометр» состоит из двух круговых диаграмм, одна из которых служит основой для секторов – индикаторов выполнения, а другая – для указателя спидометра.

Имеем: исходные данные: факт выполнения плана продаж на 89%

Факт

%

89

А также критическое нижнее значение плана – 25% и «средний» диапазон 25%-75%.

Уровни

% от 100

< min

25

> N

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

Сен 12, 2012Ирина Доброва
Статья была интересной?
  • Поделюсь (2)
  • Полезно (22)
  • Бесполезно (1)
  • Банально (0)
45 способ показать разницу двух величинКофе: где и сколько?
Главная Книги Строим спидометр в Excel
Статья10 г. назад 10 Инструментыdashboard, excel, графики и диаграммы, спидометр
facebook-profile-picture
Ирина Доброва

Основатель и идеолог агентства Infographer

Сайт Фейсбук Instagram
  • etymologist

    Спасибо большое за инструкцию!
    Как Вам удаётся переключаться между круговой и кольцевой диаграммами? У меня последовательность операций в Excel немного не совпадает с той, что Вы описали.

    • Макс Горбачевский

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

  • Mantis

    Добрый день!
    Подскажите, пожалуйста, каким образом можно изменить источник данных для подписи на формулу ячейки, в которой содержится значение указателя? Ничего не получается при двойном щелчке правой кнопкой на подписи. Спасибо!

    • Макс Горбачевский

      Необходимо не двойным щелчком, а двумя однократными (так, чтобы компьютер не подумал что вы делаете двойной щелчок). Обычно секунда-полторы — достаточное время между щелчками.

  • Mantis

    Просто в этом примере мало конкретики, дело даже не в двойном клике, а в том, что надо было написать формулу вида =Лист1!$B$2, чтобы отображались изменяемые значения или можно было просто выложить excel-евский пример, а то голову можно сломать чайнику с такой инструкции ))
    После таки удачного повтора данного примера возникло еще пару вопросов:
    1) как быть если например нужно, чтобы шкала была не до 100 а например до 150%
    2) какие данные необходимо внести в столбец отвечающий за диапазон цветов, чтобы например красная граница была до 50%, желтая до 80%, ну и зеленая до 100%.
    Спасибо

    • Макс Горбачевский

      1) Чтобы шкала была до бОльшего чем 100% значения — всего лишь внесите соответствующие изменения в формулу в ячейках колонки 3, там по умолчанию забита цифра 100 (т.е. 100%)

      2) необходимо внести именно эти значения, 50 и 80… Не вижу проблемы ?

  • Mantis

    Подскажите, а каким образом нужно вводить данные, если необходимо, чтобы шкала была больше 100% в случае перевыполнения плана? Спасибо!

  • den

    а можно пример в Excel посмотреть? выложите ссылку пожалуйста.

  • Els

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

  • Els

    Возможно важное дополнение к вопросу выше — офис 2010

  См. также  
Примеры визуализации

Круг эмоций Плутчика, эмоджи и атлас эмоций Далай-ламы

Роберт Плутчик создал колесо эмоций, RJ Andrews (автор Info we trust) развеселил колесо, добавив эмоджи, ну а мы всю эту красоту перевели. 1

Инструменты

Приборная панель, помогающая принимать решения. Или от хорошего к простому

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

Инструменты

iWork — удобен ли для инфографики?

Многим нравятся красоты стилей и переходов, которые получаются в Keynote. 0

Инструменты

Нано-чарты и Микро-графики

На конференции DD4D Mark Palmer (VizMetrics Inc.) рассказывал, как ловко они перерабатывают стопки бизнес отчетов В концентрированный... 0

Теория

Гантт в Экселе — 4 варианта

Конда нужно быстро набросать план, и визуализировать сроки/этапы проекта - тратить на создание диаграммы Гантта целых полчаса нет... 0

я Beispiele von Visualisierungen, я Business-Infografik, я Werkzeuge

Es wurde neuer Diagrammtyp entwickelt, der die planmäßigen Kennziffern mit den faktischen vergleichen lässt!

Außerdem sind wir noch darauf gekommen, wie dieses Diagramm im Excel erstellt werden könnte. 0

Примеры визуализации

Когда много графиков

Публикую несколько примеров, которые помогали нам работать над бизнес отчетами и дэшбордами. 0

Инструменты

Подборки: про повседневное — Excel

Продолжаем наши подборки. Поговорим про самый ходовой инструмент визуализации в бизнес контексте — Excel. 0

Мы в Facebook, ежедневно

Unable to display Facebook posts.
Show error

Error: Server configuration issue
Please refer to our Error Message Reference.
Для авторов
Приглашаем авторов по теме инфографики к сотрудничеству. Политика обработки персональных данных и пользовательское соглашение.
Контакты

Infographer – это агентство инфографики и образовательный ресурс об инфографике. Офисы в Москве и Гааге, работаем для клиентов со всего мира (контакты).

Адрес: Москва, Ленинский просп. 30А

Телефон: +7 916 628 47 64

Имейл: mg@infographer.ru

Пишем для вас с 2009 года © Infographer, при перепечатке ссылка обязательна. 16+