Диаграмма-шкала (bullet chart) для отображения KPI

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

Диаграмма-шкала или диаграмма-термометр для отображения KPI

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

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

Видео

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

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

bullet-chart2.pngСтроим гистограмму с накоплением

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

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

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

Диаграмма-шкала

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

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

Настройка диаграммы-шкалы

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

Диаграмма-шкала

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

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

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

bullet-chart7.png

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

bullet-chart10.png bullet-chart9.png

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

Настройка горизонтальных планок погрешностей

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

Почти готовая диаграмма-шкала

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

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

готовая диаграмма-шкала

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

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

Копирование диаграммы на новые данные

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



09.07.2014 17:01:19
Удивительное рядом, как раз читаю "Dashboards and Reports" - Michael Alexander, John Walkenbach главу про "Using a Bullet Graph". :D
09.07.2014 17:08:31
Ну, там-то покруче написано, думаю :)
25.07.2014 11:17:03
Спасибо за ссылку.
Спасибо за интересный урок.

Очень жаль, что такой книги нет на русском. У нас как-то интересное с длительной задержкой печатают или совсем не печатают.
10.07.2014 07:30:42
Видел аналог - "Диаграммы в Excel" Джона Уокенбаха. Тоже на тему визуализации и диаграмм очень здорово написано.
А еще 26 июля у меня на Бауманской будет однодневный открытый тренинг по визуализации в Excel - приходите, там таких примеров будут десятки ;)
11.07.2014 10:15:39
Приветствую Николай, а где на Бауманской, есть ссылка на тренинг?
Спасибо за приглашение, но живу я во Владивостоке. Вот, если бы Вы вели вебинары, то обязательно прошёл бы.
20.07.2014 01:32:17
Добрый день,Николай!
При построении данной диаграммы в момент когда необходимо было в формате ряда данных выбрать "по вспомогательной оси",в моем экселе 2010 года такого пункта нет.Подскажите как быть в этом случае?
06.08.2014 20:50:00
Где-то промахнулись просто. Не может этот пункт отсутствовать. Попробуйте еще раз медленно и внимательно по шагам.
22.07.2014 18:30:06
Спасибо, очень интересное решение
25.07.2014 15:03:20
боковой зазор нужно не уменьшать, а увеличивать. Если его уменьшить, он перекроет остальные столбцы.:)
30.07.2014 18:54:35
Вариант со вспомагательной осью опробовал на сводной диаграмме и всплыла проблема: с изменением параметров фильтра диаграмма перестраиваться, но параметр отдельных рядов "по вспомагательной оси" исчезает. Могут быть какие -нибудь решения этой проблемы??
Спасибо!
31.07.2014 16:16:57
Диаграмма прямо в кассу! Спасибо!
05.08.2014 14:05:58
Николай, добрый день пытаюсь сделать на основании этой таблицы калькулятор для оценки медицинских анализов, не могу разобраться. Почему не совпадают параметры в таблице и диаграмме. Например параметр "средне" соответствует "50". Я так понимаю, это верхняя граница, то есть желтое поле на диаграмме соответствующее параметру "средне" должно находиться между 20 и 50. То есть логика такая, если "плохо" это 20, то на диаграмме поле от 0 до 20, далее идет параметр"средне"-50, он соответствует полю от 20 до 50, и "хорошо" соответствует полю от 50 и выше. Однако на диаграмме это не так, зеленое поле , соответствующее значению "хорошо" начинается примерно от 70, хотя его значение 40 в таблице. Поясните если можно. Простите если путано
06.08.2014 20:48:33
Юрий, значения для "средне" - это размер этой зоны, а не ее граница. Т.е. начинается она там, где кончается "плохо" (на 20), а заканчивается на 20+50 = 70.
07.08.2014 13:01:07
спасибо, теперь понял
Николай, а точно такую же диаграмму, но в горизонтальном виде как сделать?
10.08.2014 08:42:25
С нуля, используя линейную диаграмму - никак. Но можно построить вертикальную, выделить ячейки под диаграммой, скопировать и вставить с помощью инструмента Камера как связанную картинку и повернуть ее на 90 градусов. Чтобы добавить кнопку Камера, щелкните по панели быстрого доступа правой кнопкой мыши и выберите Настройка панели быстрого доступа.
24.03.2015 16:10:32
возможно ли сделать движение черной полосы таким же плавным, как на гифке в начале?
у меня сразу же перескакивает на другое значение
18.12.2018 09:58:20
Поставьте новый Excel 2016 - там при изменении исходных данных диаграмма перерисовывается плавно и с анимацией, а не рывком, как в старых версиях
26.04.2015 14:05:48
Здравствуйте,
Я столкнулась с такой проблемой. У меня 4 группы товаров. Мне надо наложить столбик с валовой прибылью на столбик уровня продаж каждой группы товаров. Как это можно сделать? Когда начинаю двигать один ряд значений, например валовая прибыль товаров 1, двигаются все категории и получается каша...
Зараннее спасибо за помощь
20.01.2016 21:53:59
Николай откройте пожалуйста секрет замедленного движения полосы "значение"
18.12.2018 09:57:36
Просто в Excel 2016 при изменении исходных данных диаграмма перерисовывается плавно и с анимацией, а не рывком, как в старых версиях :)
05.02.2016 00:54:26
Я даже не поленюсь зарегистрироваться, что бы вас поблагодарить) Продолжайте радовать нас интересными приемами, а мы будем совершенствовать свои умения;)
24.11.2016 14:54:56
Сделал объёмную для своих целей:)

bullet
10.08.2017 10:27:18
Николай, а как вывести на эту диаграмму автоматически процент изменения величины по сравнению с целью?
17.12.2018 16:41:09
Можно ли построить такую диаграмму по данным сводной таблицы? У меня выходит сообщение, что использовать точечную диаграмму в сводных табл нельзя
18.12.2018 09:56:46
Да, по сводным таблицам можно строить не все типы диаграмм. Но можно вытащить данные из сводной в отдельную промежуточную мини-таблицу с помощью прямых ссылок или функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ и потом уже по ней строить любую диаграмму.
11.02.2020 18:49:11
как мне сделать горизонтальную диаграмму на подобие этой? Пробовал по аналогии, не получается.
14.05.2021 12:44:51
Николай, спасибо большое за данный урок. Подскажите, а как можно цель задать не фиксированным значением, а например - коридором "от и до"?
Наверх