Пулевая диаграмма встроена в excel

Если вы часто строите в Excel отчеты с финансовыми показателями (KPI), то вам должен понравится этот экзотический тип диаграммы — диаграмма-шкала или диаграмма-термометр (Bullet Chart):

• Горизонтальная красная линия показывает целевое значение, к которому мы стремимся.
• Трехцветная фоновая заливка шкалы наглядно отображает зоны «плохо-средне-хорошо», куда мы попадаем.
• Черный центральный прямоугольник отображает текущее значение параметра.

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

Видео

Этап 1. Гистограмма с накоплением

Начать придется с построения на основе наших данных стандартной гистограммы, которую мы потом за несколько шагов приведем к нужному нам виду. Выделяем исходные данные, открываем вкладку Вставка (Insert) и выбираем гистограмму с накоплением (Stacked Histogram):

Теперь допиливаем:

• Чтобы столбцы выстроились не в ряд, а друг на друга — меняем местами строки и столбцы с помощью кнопки Строка/столбец (Row/Column) на вкладке Конструктор (Design).
• Легенду и название (если были) убираем — у нас тут  минимализм.
• Настраиваем цветовую заливку столбиков по их смыслу (выделить их по очереди, щелкнуть по выделенному правой кнопкой мыши и выбрать Формат точки данных).
• Сужаем диаграмму по ширине

На выходе должно получиться что-то похожее:

Этап 2. Вторая ось

Выделяем ряд Значение (черный прямоугольник), открываем его свойства сочетанием Ctrl+1 или правой кнопкой мыши по нему — Формат ряда (Format Data Point) и в окне параметров переключаем ряд на Вспомогательную ось (Secondary Axis).

Черный столбец уйдет по второй оси и станет закрывать все остальные цветные прямоугольники — не пугайтесь, все по плану Чтобы видеть шкалу увеличиваем для него Боковой зазор (Gap) до максимума, чтобы получить похожую картину:

Уже теплее, не так ли?

Этап 3. Ставим цель

Выделяем ряд Цель (красный прямоугольник), щелкаем по нему правой кнопкой мыши, выбираем команду Изменить тип диаграммы для ряда (Change chart type) и меняем тип на Точечную (Scatter). Красный прямоугольник должен превратиться в одиночный маркер (круглый или Ж-образный), т.е. в точку:

Не снимая выделения с этой точки, включаем для нее Планки погрешностей (Error Bars) на вкладке Макет (Layout). или на вкладке Конструктор (в Excel 2013). Последние версии Excel предлагают несколько вариантов таких планок — поэкспериментируйте с ними, при желании:

От нашей точки должны во все четыре стороны разойтись «усы» — обычно их используют для наглядного отображения допусков по точности или разброса (дисперсии) значений, например в статистике, но сейчас мы их используем с более прозаической целью. Вертикальные планки удаляем (выделить и нажать клавишу Delete), а горизонтальные настраиваем щелкнув по ним правой кнопкой мыши и выбрав команду Формат предела погрешностей (Format Error Bars):

В окне свойств горизонтальных планок погрешностей в разделе Величина погрешности выбираем Фиксированное значение или Пользовательская (Custom) и задаем положительное и отрицательное значение ошибки с клавиатуры равное 0,2 — 0,5 (подбирается на глаз). Здесь же можно увеличить толщину планки и поменять ее цвет на красный. Маркер можно отключить. В итоге должно получиться так:

Этап 4. Последние штрихи

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

Вот и все, диаграмма готова. Красиво, правда?

Скорее всего у вас будет несколько параметров, которые вы хотите отобразить с помощью таких диаграмм. Чтобы не повторять заново всю эпопею с построением, можно просто скопировать диаграмму, а затем (выделив ее) перетащить синий прямоугольник зоны исходных данных на новые значения:

Ссылки по теме

• Как построить диаграмму Парето в Excel
• Как построить каскадную диаграмму отклонений («водопад» или «мост») в Excel
• Новые возможности диаграмм в Excel 2013

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

В сегодняшней статье мы рассмотрим интересный способ визуализации достижения целевых показателей. Bullet graph (пулевая диаграмма) –вид гистограммы, разработанная экспертом по визуализации Stephen Few, которая служит в качестве замены датчиков и счетчиков на дашборде. Целью пулевой диаграммы является отображение нескольких слоев информации на небольшом участке дашборда. На рисунке ниже отображена пулевая диаграмма с одним показателем эффективности в сравнении с целевым значением и качественной оценкой достижения KPI: плохо, средне, хорошо, отлично.

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

Подготовка данных

В таблице указаны все данные, необходимые для построения bullet graph. Первые четыре значения (Плохо, Средне, Хорошо, Отлично) необходимы для построения столбца качественной оценки. Для удобства мы взяли процентные значения, которые в сумме дают 100%. Пятое значение необходимо для создания колонки с текущим значением, шестое – для создания маркера с целевым значением.

Создание пулевой диаграммы

Выделяем ячейки таблицы со значениями A1:B7, переходим по вкладке Вставка в группу Диаграммы, щелкаем Гистограмма –> Гистограмма с накоплением. Excel построил диаграмму с одним рядом данных. Чтобы превратить ее в пулевую диаграмму, необходимо поменять столбы и строки данных местами, при этом, каждое значение превратится в отдельный ряд данных.

Щелкаем правой кнопкой мыши по целевому ряду данных, во всплывающем окне выбираем Изменить тип диаграммы для ряда. В появившемся диалоговом окне Изменение типа диаграммы, меняем гистограмму на график с маркерами.

Еще раз щелкаем правой кнопкой мыши по целевому ряду данных, выбираем  Формат ряда данных. В панели справа во вкладке Параметры ряда устанавливаем маркер напротив поля По вспомогательной оси.

В этой же панели переходим на вкладку Параметры маркера, устанавливаем тип маркера в виде линии размером 15, меняем заливку маркера на соответствующий целевому цвет (у меня целевой цвет ассоциируется с красным) и убираем границы маркера.

Удаляем вспомогательную ось на диаграмме, для этого щелкаем по ней правой кнопкой мыши и выбираем Удалить. Это необходимо сделать, чтобы масштаб обоих осей совпал для всех рядов данных.

Щелкаем правой кнопкой мыши по ряду данных с текущим показателем эффективности, во всплывающем меню выбираем Формат ряда данных. В правой панели во вкладке Параметры ряда устанавливаем маркер По вспомогательной оси и меняем боковой зазор на 315%.

Отформатируйте цвета рядов данных под ваши корпоративные цвета для большего эффекта и ваша пулевая диаграмма готова.

Создание горизонтальной bullet graph

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

Разворачиваем направление текста осей на 270 градусов, для этого щелкаем правой кнопкой мыши по оси, выбираем Формат оси, переходим по вкладке Размер и свойства и меняем значение параметра Направление текста на Повернуть весь текст на 270.

Далее нам потребуется воспользоваться инструментом Камера, который не доступен в стандартном наборе инструментов ленты Excel2013. Нам нужно добавить его в панель быстрого доступа. Переходим по вкладке Файл -> Параметры. В появившемся диалоговом окне Параметры Excel, переходим по вкладке Панель быстрого доступа. Находим инструмент Камера в левом поле, щелкаем Добавить, чтобы переместить его панель быстрого доступа.

Выделяем на листе диапазон ячеек, на переднем плане которых находится наш bullet graph, щелкаем по иконке Камера в панели быстрого доступа, при этом изображение указателя мышки сменится на небольшой крест. Щелкаем на то место на листе, куда мы хотим поместить нашу горизонтальную диаграмму. Excel вставит снимок диапазона ячеек, который выделили. Теперь нашей диаграммой можно пользоваться, как изображением. Щелкаем по нему, по краям появятся маркеры редактирования картинок. Наводим указатель мыши на иконку в виде закручивающейся стрелки и поворачиваем диаграмму на 270 градусов.

Наша горизонтальная пулевая диаграмма готова. Обратите внимание, что при изменении значений в таблице с данными, изображение с диаграммой тоже будет менять свой внешний вид.

Скачать файл с примером пулевой диаграммы (bullet graph).

Bullet charts came into existence to overcome the drawbacks of Gauge charts. We can refer to them as Liner Gauge charts. Bullet charts were introduced by Stephen Few. A Bullet chart is used to compare categories easily and saves on space. The format of the Bullet chart is flexible.

What is a Bullet Chart?

According to Stephen Few, Bullet charts support the comparison of a measure to one or more related measures (for example, a target or the same measure at some point in the past, such as a year ago) and relate the measure to defined quantitative ranges that declare its qualitative state (for example, good, satisfactory and poor). Its linear design not only gives it a small footprint, but also supports more efficient reading than the Gauge charts.

Consider an example given below −

In a Bullet chart, you will have the following components −

• The qualitative bands representing the qualitative states −

• Target Value, say 80%.
• Actual Value, say 70%.

With the above values, the Bullet chart looks as shown below.

Though we used colors in the above chart, Stephen Few suggests the usage of only Gray shades in the interest of color-blind people.

Advantages of Bullet Charts

Bullet charts have the following uses and advantages −

• Bullet Charts are widely used by data analysts and dashboard vendors.

• Bullet charts can be used to compare the performance of a metric. For example, if you want to compare the sales of two years or to compare the total sales to a target, you can use bullet charts.

• You can use Bullet chart to track the number of defects in Low, Medium and High categories.

• You can visualize the Revenue flow across the Fiscal year.

• You can visualize the expenses across the Fiscal year.

• You can track Profit%.

• You can visualize customer satisfaction and can be used to display KPIs also.

Preparation of Data

Arrange the data as given below.

As you can observe, the qualitative values are given in the column – Performance. The Bands are represented by the column – Value.

Creating a Bullet Chart

Following are the steps to create a Bullet chart −

Step 1 − Select the data and insert a Stacked Column chart.

Step 2 − Click on the chart.

Step 3 − Click the DESIGN tab on the Ribbon.

Step 4 − Click Switch Row/ Column button in the Data group.

Step 5 − Change the chart type.

• Click Change Chart Type.
• Click the Combo icon.
• Change the chart type for Target to Stacked Line with Markers.
• Check the box – Secondary Axis for Target and click OK.

Step 6 − As you can see, the Primary and the Secondary Vertical Axis have different ranges. Make them equal as follows.

• Right click on Primary Vertical Axis and select Format Axis.
• Click on the AXIS OPTIONS tab in the Format Axis pane.
• In AXIS OPIONS, under Bounds, type the following −
  • 0.0 for Minimum
  • 1.0 for Maximum
• Repeat the above steps for Secondary Vertical Axis.

Step 7 − Deselect Secondary Vertical Axis in the Chart Elements.

Step 8 − Design the chart

• Click on the chart.
• Click the DESIGN tab on the Ribbon.
• Click Change Chart Type.
• Check the Secondary Axis box for the Value series.

Step 9 − Right click on the column for Value (blue color in the above chart).

Step 10 − Select Format Data Series.

Step 11 − Change Gap Width to 500% under SERIES OPTIONS in Format Data Series pane.

Step 12 − Deselect Secondary Vertical Axis in the Chart Elements.

The chart will look as follows −

Step 13 − Design the chart as follows −

• Click on the chart.
• Click Chart Styles at the right corner of the chart.
• Click the COLOR tab.
• Select Color 17.

Step 14 − Fine tune the chart as follows.

• Right click on the Value column.
• Change the Fill color to dark green.
• Click on the Target.
• Change the Fill and Line color of Marker to orange.
• Increase the size of the Marker.

Step 15 − Fine-tune the chart design.

• Resize the chart.
• Select Legend in Chart Elements.
• Deselect Primary Horizontal Axis in Chart Elements.
• Deselect Gridlines in Chart Elements.
• Give a Chart Title.

Your Bullet chart is ready.

You can change the color of the chart to gray gradient scale to make it colorblind friendly.

Bullet Chart in Reverse Contexts

Suppose you want to display the number of defects found in a Bullet chart. In this case, lesser defects mean greater quality. You can define defect categories as follows −

Step 1 − You can then define a Limit for number of defects and represent the number of defects found by a Value. Add Value and Limit to the above table.

Step 2 − Select the data.

Step 3 − Create a Bullet chart as you have learnt in the previous section.

As you can see, the ranges are changed to correctly interpret the context.