Диаграмма-шкала (bullet chart) для отображения KPI
Если вы часто строите в 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
Очень жаль, что такой книги нет на русском. У нас как-то интересное с длительной задержкой печатают или совсем не печатают.
А еще 26 июля у меня на Бауманской будет однодневный открытый тренинг по визуализации в Excel - приходите, там таких примеров будут десятки
При построении данной диаграммы в момент когда необходимо было в формате ряда данных выбрать "по вспомогательной оси",в моем экселе 2010 года такого пункта нет.Подскажите как быть в этом случае?
Спасибо!
у меня сразу же перескакивает на другое значение
Я столкнулась с такой проблемой. У меня 4 группы товаров. Мне надо наложить столбик с валовой прибылью на столбик уровня продаж каждой группы товаров. Как это можно сделать? Когда начинаю двигать один ряд значений, например валовая прибыль товаров 1, двигаются все категории и получается каша...
Зараннее спасибо за помощь