Содержание:
Если вы часто строите отчеты с финансовыми показателями (KPI) в Excel, то вам должен понравиться такой экзотический тип диаграммы – масштабная диаграмма или диаграмма-термометр (Bullet Chart):
- Горизонтальная красная линия показывает целевое значение, к которому мы стремимся.
- Трехцветная фоновая заливка шкалы четко отображает зоны «плохо-средне-хорошо», куда мы попадаем.
- Черный центральный прямоугольник отображает текущее значение параметра.
Конечно, на такой диаграмме нет предыдущих значений параметра, т.е. никакой динамики или тенденций мы не увидим, но для точечного отображения достигнутых результатов по сравнению с целями на данный момент она вполне подходит.
Видео
Этап 1. Составная гистограмма
Начать нам придется с построения стандартной гистограммы на основе наших данных, которую затем за несколько шагов приведем к нужному нам виду. Выберите исходные данные, откройте вкладку Вставить , а затем выбрать гистограмма с накоплением:
Теперь добавляем:
- Чтобы столбцы располагались не в ряд, а друг над другом, поменяйте местами строки и столбцы с помощью кнопки Строка/Столбец (Строка/Столбец) таб Конструктор (Дизайн).
- Убираем легенду и название (если есть) — тут минимализм.
- Настройте цветовую заливку столбцов в соответствии с их значением (выделяйте их по одному, щелкните правой кнопкой мыши по выбранному и выберите Формат точки данных).
- Сужение диаграммы по ширине
Результат должен выглядеть примерно так:
Этап 2. Вторая ось
Выберите строку Значение (черный прямоугольник), откройте его свойства комбинацией Ctrl + 1 или щелкните по нему правой кнопкой мыши - Формат строки (формат точки данных) и в окне параметров переключите строку на Вспомогательная ось (Вторичная ось).
Черный столбик пройдет по второй оси и начнет перекрывать все остальные цветные прямоугольники – не пугайтесь, все по плану 😉 Чтобы увидеть масштаб, увеличьте его. Боковой зазор (зазор) по максимуму, чтобы получить похожую картинку:
Уже теплее, не так ли?
Этап 3. Поставьте цель
Выберите строку Цель (красный прямоугольник), кликаем по нему правой кнопкой мыши, выбираем команду Изменение типа диаграммы для серии и измените тип на Пунктирный (разброс). Красный прямоугольник должен превратиться в одиночный маркер (круглый или Г-образный), т.е. ровно:
Не снимая выделения с этого пункта, включаем для него Полосы ошибок таб Планировка. или на вкладке Конструктор (в Excel 2013). Последние версии Excel предлагают несколько вариантов этих полосок — поэкспериментируйте с ними, если хотите:
С нашей точки зрения, «усы» должны расходиться во все четыре стороны — обычно они используются для визуального отображения допусков точности или разброса (разброса) значений, например, в статистике, но сейчас мы используем их для более прозаической цели. Удалите вертикальные полосы (выделите и нажмите клавишу Удалить), а горизонтальные отрегулируйте, щелкнув по ним правой кнопкой мыши и выбрав команду Полосы ошибок формата:
В окне свойств горизонтальных полос ошибок в разрезе Значение ошибки Выберите фиксированное значение or Пользовательский (Пользовательский) и устанавливаем с клавиатуры положительное и отрицательное значение ошибки равным 0,2 – 0,5 (подбирается на глаз). Здесь вы также можете увеличить толщину полосы и изменить ее цвет на красный. Маркер можно отключить. В результате должно получиться вот так:
Этап 4. Последние штрихи
Сейчас будет волшебство. Следите за руками: выберите нужную дополнительную ось и нажмите Удалить на клавиатуре. Все построенные нами столбцы шкалы, полоса целевой ошибки и основной черный прямоугольник текущего значения параметра приводятся к одной системе координат и начинают откладываться по одной оси:
Все, схема готова. Красиво, не так ли? 🙂
Скорее всего у вас будет несколько параметров, которые вы хотите отобразить с помощью таких графиков. Чтобы не повторять всю эпопею с построением, можно просто скопировать диаграмму, а затем (выделив ее) перетащить синий прямоугольник зоны исходных данных на новые значения:
- Как построить диаграмму Парето в Excel
- Как построить водопадную диаграмму отклонений («водопад» или «мост») в Excel
- Что нового в диаграммах в Excel 2013