Трюк №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.
Рис. 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. Законченная диаграмма спидометра
Щелкая стрелки или перетаскивая ползунок на полосе прокрутки, вы будете изменять показания спидометра, но помните, что при этом также изменяются данные на связанном со спидометром рабочем листе.