Разберем построение пулевой диаграммы (bullet chart) в Excel, представляющую собой совокупность обычной гистограммы с накоплением и графика с маркерами (или точечной диаграммы).
Приветствую всех, дорогие читатели блога TutorExcel.Ru.
Пулевая диаграмма, диаграмма-шкала или диаграмма-термометр (в англоязычной среде ее называют bullet chart или bullet graph) очень часто используется в качестве визуализации выполнения какого-либо KPI, например, для план-факт анализа показателей.
Выглядит она следующим образом:
Такое название диаграммы появилось за некоторое внешнее сходство с термометром или пулей.
Вдобавок ко всему, термометр в жизни является прибором для измерения температуры, т.е. производит оценку данных.
Такого же эффекта мы хотим добиться и при прочтении графика.
Разберем внешний вид графика по составляющим. График включает в себя 3 основных элемента:
- Шкала. Состоит из нескольких уровней (обычно трех, условные зоны «плохо», «хорошо», «отлично», но на усмотрение можно сделать и любое другое количество);
- Линия с целевым значением. Горизонтальная (или вертикальная) линия, показывающая цель, к которой стремимся (другими словами, плановый показатель);
- Линия с текущим значением. Индикатор текущего значения (фактический показатель).
Из преимуществ подобного способа визуализации данных можно назвать достаточно компактный вид (график не занимает много места) и простоту чтения (интуитивно понятный).
К недостаткам можно отнести малое количество анализируемых данных (берется только 1 показатель) и отсутствие возможности показать динамику, тренд.
В любом случае, пулевая диаграмма может стать полезным дополнением для практически любого дашборда.
Теперь от теории перейдем к практике — к построению.
Как создать bullet chart в Excel?
Построение пулевой диаграммы. Вариант 1.
Прежде всего создадим таблицу с исходными данными, в которой отметим 3 зоны («плохо», «хорошо» и «отлично»), а также целевое и фактическое значения:
Выделим диапазон с данными (A1:B6) и создадим диаграмму (на панели вкладок выберем Вставка -> Диаграммы -> Гистограмма -> Гистограмма с накоплением):
Если по умолчанию у вас диаграмма приобрела несколько иной вид, к примеру, столбцы расположились не по вертикали, а по горизонтали (рядом друг с другом), то поменяйте местами столбцы и строки. Это можно сделать либо на вкладке Конструктор, либо в окне Выбор источника данных.
Теперь на диаграмме выберем ряд данных Факт и отобразим его по вспомогательной оси.
Щелкаем правой кнопкой мыши по ряду и выбираем Формат ряда данных -> Параметры ряда и отмечаем галочку напротив По вспомогательной оси:
Также изменим величину бокового зазора (сделаем его больше, чем у других рядов), чтобы ряд не заслонял собой другие данные на графике:
Далее для ряда данных Цель поменяем тип диаграммы.
Щелкаем по нему правой кнопкой мыши и выбираем Изменить тип диаграммы для ряда, и в диалоговом окне меняем текущий тип на График с маркерами:
Затем превратим появившуюся точку с целевым значением в линию, чтобы она на графике была более заметной.
Опять щелкаем правой кнопкой мыши по точке, переносим ряд на вспомогательную ось и в настройках параметров устанавливаем вид маркера в виде линии:
В результате получаем следующий вид диаграммы:
Как мы видим у нас на графике есть 2 оси (на основной отображаются зоны, на второстепенной — плановое и фактическое значение), причем с разными координатными границами.
Воспользуемся небольшой хитростью: выделим второстепенную ось и удалим ее.
Этим шагом мы добьемся единого масштаба для всех рядов данных (как на основной, так и на второстепенной оси):
Диаграмма построена, остались последние штрихи в виде оформления.
Можно выбрать различные цвета для уровней, настроить ширину столбцов и т.д.:
Построение пулевой диаграммы. Вариант 2.
Внешний вид линии с целевым значением можно представить и иным способом.
Для ряда Цель поменяем тип диаграммы с График с маркерами на Точечная.
Затем с помощью вкладки Макет или Конструктор (с Excel 2013) добавим на график Планки погрешностей:
Теперь на графике вместо линии должна появиться точка с расходящимися в 4 разные стороны линиями.
Вертикальную линию мы удаляем, так как для этого графика она нам не нужна, оставляем только горизонтальную, и в ее настройках указываем параметры (ширину и цвет):
Все, приготовления закончены и диаграмма готова:
Спасибо за внимание!
Если у вас есть мысли или вопросы по теме статьи — делитесь ими в комментариях.
Удачи вам и до скорых встреч на страницах блога TutorExcel.Ru!