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

Трюк №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, предназначена специально для меток данных. Она позволяет указывать диапазон на рабочем листе, на основе которого будут создаваться подписи данных для рядов диаграммы. Надстройка Джона также поддерживает возможности, перечисленные в следующем списке.

При помощи этой надстройки отформатируйте ряд 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.

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

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

Источник

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Уровни