Пулевая диаграмма (bullet chart) в Excel

Разберем построение пулевой диаграммы (bullet chart) в Excel, представляющую собой совокупность обычной гистограммы с накоплением и графика с маркерами (или точечной диаграммы).


Приветствую всех, дорогие читатели блога TutorExcel.Ru.

Пулевая диаграмма, диаграмма-шкала или диаграмма-термометр (в англоязычной среде ее называют bullet chart или bullet graph) очень часто используется в качестве визуализации выполнения какого-либо KPI, например, для план-факт анализа показателей.
Выглядит она следующим образом:

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

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

Разберем внешний вид графика по составляющим. График включает в себя 3 основных элемента:

  • Шкала. Состоит из нескольких уровней (обычно трех, условные зоны «плохо», «хорошо», «отлично», но на усмотрение можно сделать и любое другое количество);
  • Линия с целевым значением. Горизонтальная (или вертикальная) линия, показывающая цель, к которой стремимся (другими словами, плановый показатель);
  • Линия с текущим значением. Индикатор текущего значения (фактический показатель).

Из преимуществ подобного способа визуализации данных можно назвать достаточно компактный вид (график не занимает много места) и простоту чтения (интуитивно понятный).
К недостаткам можно отнести малое количество анализируемых данных (берется только 1 показатель) и отсутствие возможности показать динамику, тренд.

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

Теперь от теории перейдем к практике — к построению.

Как создать bullet chart в Excel?

Построение пулевой диаграммы. Вариант 1.

Прежде всего создадим таблицу с исходными данными, в которой отметим 3 зоны («плохо», «хорошо» и «отлично»), а также целевое и фактическое значения:

Таблица с исходными данными
Выделим диапазон с данными (A1:B6) и создадим диаграмму (на панели вкладок выберем Вставка -> Диаграммы -> Гистограмма -> Гистограмма с накоплением):

Создание гистограммы с накоплением. Шаг 1
Если по умолчанию у вас диаграмма приобрела несколько иной вид, к примеру, столбцы расположились не по вертикали, а по горизонтали (рядом друг с другом), то поменяйте местами столбцы и строки. Это можно сделать либо на вкладке Конструктор, либо в окне Выбор источника данных.

Теперь на диаграмме выберем ряд данных Факт и отобразим его по вспомогательной оси.
Щелкаем правой кнопкой мыши по ряду и выбираем Формат ряда данных -> Параметры ряда и отмечаем галочку напротив По вспомогательной оси:

Настройка параметров ряда
Также изменим величину бокового зазора (сделаем его больше, чем у других рядов), чтобы ряд не заслонял собой другие данные на графике:

Перенос ряда на вспомогательную ось. Шаг 2
Далее для ряда данных Цель поменяем тип диаграммы.
Щелкаем по нему правой кнопкой мыши и выбираем Изменить тип диаграммы для ряда, и в диалоговом окне меняем текущий тип на График с маркерами:

Изменение типа ряда. Шаг 3
Затем превратим появившуюся точку с целевым значением в линию, чтобы она на графике была более заметной.
Опять щелкаем правой кнопкой мыши по точке, переносим ряд на вспомогательную ось и в настройках параметров устанавливаем вид маркера в виде линии:

Настройка параметров маркера
В результате получаем следующий вид диаграммы:

Отображение линии с целевым значением. Шаг 4
Как мы видим у нас на графике есть 2 оси (на основной отображаются зоны, на второстепенной — плановое и фактическое значение), причем с разными координатными границами.
Воспользуемся небольшой хитростью: выделим второстепенную ось и удалим ее.
Этим шагом мы добьемся единого масштаба для всех рядов данных (как на основной, так и на второстепенной оси):

Единый масштаб данных. Шаг 5
Диаграмма построена, остались последние штрихи в виде оформления.
Можно выбрать различные цвета для уровней, настроить ширину столбцов и т.д.:

Пулевая диаграмма (вариант 1)

Построение пулевой диаграммы. Вариант 2.

Внешний вид линии с целевым значением можно представить и иным способом.
Для ряда Цель поменяем тип диаграммы с График с маркерами на Точечная.
Затем с помощью вкладки Макет или Конструктор (с Excel 2013) добавим на график Планки погрешностей:

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

Настройка параметров планки погрешностей
Все, приготовления закончены и диаграмма готова:

Пулевая диаграмма (вариант 2)

Скачать файл с примером.

Спасибо за внимание!
Если у вас есть мысли или вопросы по теме статьи — делитесь ими в комментариях.

Удачи вам и до скорых встреч на страницах блога TutorExcel.Ru!

Поделиться с друзьями:
Поиск по сайту:

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

Ваш адрес email не будет опубликован.