Верно ли утверждение что пулевая диаграмма встроена в excel
Обновлено: 07.07.2024
Простейший график-шкала есть у большинства из нас на кухне. Прозрачное окошко со шкалой на стенке электрического чайника, через которое мы видим и оцениваем количество воды, находящейся внутри – это и есть самый суперпонятный простейший график-шкала.
Красивая, эффектная и понятная с первого беглого взгляда визуализация уровня или процесса при помощи вертикального или горизонтального индикатора давно находит применение в самых разных областях нашей жизни:
— ртутные, спиртовые и шкальные электронные термометры;
— столбчатые индикаторы уровней топлива и масла на панелях приборов автомобилей;
— полоски-индикаторы выполнения процессов в операционной системе компьютера;
— диаграммы «Bullet Chart» (дословно: «график-пуля») в MS Excel.
Так как программу Excel считают «своей» и инженеры, и финансисты, и управленцы, то примеров интересного применения графиков-шкал может быть бесчисленное множество.
В этой статье рассмотрен пример создания в MS Excel графика-шкалы модифицированного специалистом по дашбордам Стивеном Фью.
График-шкала выполнения плана производства.
Построим «график-пулю» для ежедневного информирования работников производственного участка о ходе выполнения месячного плана.
Результатом работы станут отформатированные таблица и график-шкала в Excel, которые вы видите ниже на рисунке.
1. Таблица данных:
1. Функцию, которая выводит в ячейку текущую дату, вписываем
в B2: =СЕГОДНЯ() = 28.02.15
/Числовой формат ячейки – «Дата», тип – «14.03.01»/
Эта информация не относится к исходным данным для формирования диаграммы и является чисто уведомительной.
Все последующие значения записываем в процентах!
2. Фактическое текущее значение выполнения месячного плана производства работниками участка заносим
в ячейку B3: 92%
3. Целевое значение, утвержденное в начале месяца и определяющее повышенную премию, вводим
в B4: 104%
4. Размер зеленой зоны «Хорошо» заносим
в B5: 20%
5. Размер желтой зоны «Плохо» записываем
в B6: 25%
6. Размер светло-коричневой области будущей диаграммы «Очень плохо» пишем
в B7: 65%
2. Создание и форматирование графика-шкалы:
1. Выделяем часть сформированной таблицы — область A3-B7, и включаем «Мастера диаграмм» нажатием на соответствующую пиктограмму на панели инструментов Excel «Стандартная».
2. На первом шаге создания диаграммы в выпавшем окне на вкладке «Стандартные» выбираем тип диаграммы «Гистограмма», вид – «Гистограмма с накоплением…»
3. Нажав на кнопку «Далее», на вкладке «Диапазон данных» включаем чек-бокс «Ряды в: строках».
Нажимаем на кнопку «Готово» (пропускаем третий и четвертый шаги «Мастера диаграмм») - з аготовка для диаграммы «Bullet Chart» сделана.
4. Убирать ненужные элементы с поля диаграммы Excel удобно так: выделяем мышью элемент и нажимаем на клавишу «Delete» на клавиатуре. Удаляем таким способом «Легенду» и горизонтальную «Ось категорий», очищая поле будущего графика-шкалы.
5. Наводим курсор мыши на сине-фиолетовый нижний столбец, отображающий ряд «Факт:» и щелчком правой кнопки мыши вызываем контекстное меню, в котором выбираем «Формат рядов данных…». В появившемся окне «Формат ряда данных» на вкладке «Вид» меняем цвет заливки на черный.
6. Переходим на вкладку «Ось» этого же окна и включаем чек-бокс «Построить ряд по вспомогательной оси».
7. Здесь же на вкладке «Параметры» изменяем «Ширину зазора».
Нажимаем «ОК» — главный информационный черный столбик, отражающий ряд «Факт:», готов.
8. Займемся рядом «Цель:» и превратим его изображение на графике-шкале в красную узкую полоску. Наводим курсор мыши на вишневый нижний столбец, отображающий этот ряд, вновь вызываем контекстное меню правой кнопки мыши и выбираем «Тип диаграммы…». В выпавшем окне Excel «Тип диаграммы» на вкладке «Стандартные» меняем тип диаграммы на «Точечная».
В стопке из четырех столбцов самый нижний столбец исчез, и вместо него появилась точка в виде лилового квадратного маркера.
9. Щелчком правой кнопки мыши по лиловой точке, показывающей теперь ряд «Цель:», вызываем контекстное меню и делаем выбор – «Формат рядов данных». В окне Excel «Формат ряда данных» на вкладке «Вид» можно выключить маркер.
10. Переходим в этом же окне на вкладку «X-погрешности», настраиваем так, как показано на следующем скриншоте и нажимаем «ОК».
11. Наводим курсор на появившуюся вместо маркера тонкую черную полоску – планку погрешности, в очередной раз правой кнопкой мышки вызываем контекстное меню и выбираем «Формат полос погрешностей…». В выпавшем окне «Формат планок погрешностей» настраиваем вкладку «Вид» в соответствии со следующим рисунком.
Красная горизонтальная полоска, показывающая «Цель:», создана.
12. Приведем все столбцы к одной вертикальной шкале. Для удаления правой вертикальной оси — «Дополнительной оси значений» — выделим ее мышью и нажмем на клавиатуре «Delete».
13. Так как столбики «Очень плохо», «Плохо» и «Хорошо» стоят сейчас в неправильной последовательности, то необходимо выстроить их как положено, переместив ряд «Очень плохо» вниз, а ряд «Хорошо», подняв на самый верх. Для этого наводим курсор на любой из вышеназванных столбцов, вызываем контекстное меню и выбираем «Формат рядов данных». В появившемся окне Excel «Формат ряда данных» настраиваем вкладку «Порядок рядов» так, как на скриншоте внизу.
График-шкала построен. Осталось завершить форматирование.
14. Для того чтобы изменить цвет любого элемента диаграммы нужно через контекстное меню вызвать соответствующее окно Excel «Формат…», перейти на вкладку «Вид» и раскрасить все как душе угодно! Мы это уже делали в п.6 и п.12, поэтому детально третий раз эту процедуру описывать, думаю, не имеет смысла.
Отмечу лишь как это сделано в рассматриваемом примере:
— ряд «Хорошо» заливаем ярко-зеленым цветом;
— ряд «Плохо» — желтым цветом;
— ряд «Очень плохо» — светло-коричневым цветом;
— область построения диаграммы — текстурой «Упаковочная бумага»;
— область диаграммы закрашиваем текстурой «Пергамент».
Доступ к текстурам через кнопку «Способы заливки» на вкладке «Вид».
15. Изменить размер шрифта левой вертикальной оси — «Оси значений» — можно в окне «Формат оси» на вкладке «Шрифт».
16. Для изменения размеров диаграммы следует щелкнуть левой кнопкой мышки на области диаграммы и затем потянуть мышью за маркеры, которые появятся по периметру.
Результат работы — на картинке в самом начале статьи.
Попробуйте изменить значение ряда «Факт:» — черный столбик диаграммы «Bullet Chart» тут же отреагирует изменением своей высоты.
Заключение.
Пример, рассмотренный в статье, выполнен в MS Excel 2003. В других версиях программы все делается аналогично.
Горизонтальный график-шкала строится точно по такому же алгоритму, лишь на начальном этапе следует выбрать «Линейчатую диаграмму с накоплением».
После того, как вами будет построено 5…7 графиков-шкал, время, затрачиваемое на построение новой диаграммы, не превысит и трех минут. Главное осмысленно, а не бездумно выполнять все действия.
Диаграммы «Bullet Chart» в Excel могут показывать степень укомплектованности материалами, инструментом, персоналом. Можно построить графики-шкалы для разнообразных экономических показателей. Ограничениями в этой теме являются лишь границы вашей фантазии.
Размеры, названия и количество областей типа «Хорошо», «Плохо» могут быть любыми – на ваше усмотрение! Единицами измерения могут служить натуральные показатели – штуки, килограммы. Не обязательно это должны быть проценты!
Беглого взгляда на диаграмму «Bullet Chart» достаточно для понимания в каком состоянии находится процесс, который она отражает! График-шкала, обладающий фантастической информативностью, завоевал сегодня очень большую популярность у пользователей MS 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 на клавиатуре. Все наши построенные столбики шкалы, целевая планка погрешности и основной черный прямоугольник текущего значения параметра приводятся к одной системе координат и начинают откладываться по одной оси:
Вот и все, диаграмма готова. Красиво, правда? :)
Скорее всего у вас будет несколько параметров, которые вы хотите отобразить с помощью таких диаграмм. Чтобы не повторять заново всю эпопею с построением, можно просто скопировать диаграмму, а затем (выделив ее) перетащить синий прямоугольник зоны исходных данных на новые значения:
Пулевые диаграммы появились, чтобы преодолеть недостатки калибровочных карт. Мы можем ссылаться на них как на линейные калибровочные диаграммы. Графики пули были представлены Стивеном Фью. Bullet chart используется для простого сравнения категорий и экономии места. Формат Bullet chart является гибким.
Что такое пуля?
По словам Стивена Фью, диаграммы Bullet поддерживают сравнение показателя с одним или несколькими связанными показателями (например, целью или тем же показателем в какой-то момент в прошлом, например, год назад) и связывают показатель с определенными количественными диапазонами. которые декларируют свое качественное состояние (например, хорошее, удовлетворительное и плохое). Его линейный дизайн не только дает ему небольшую площадь, но и поддерживает более эффективное считывание, чем калибровочные диаграммы.
С указанными выше значениями Bullet chart выглядит так, как показано ниже.
Хотя мы использовали цвета в приведенной выше таблице, Стивен Фью предлагает использовать только оттенки серого в интересах дальтоников.
Преимущества Bullet Charts
Bullet Charts широко используются аналитиками данных и поставщиками информационных панелей.
Для сравнения показателей метрики можно использовать пулевые диаграммы. Например, если вы хотите сравнить продажи за два года или сравнить общие продажи с целью, вы можете использовать диаграммы с маркерами.
Вы можете использовать Bullet chart для отслеживания количества дефектов в категориях Low, Medium и High.
Вы можете визуализировать поток доходов за финансовый год.
Вы можете визуализировать расходы за финансовый год.
Вы можете отслеживать прибыль%.
Вы можете визуализировать удовлетворение клиента и использовать его для отображения ключевых показателей эффективности.
Bullet Charts широко используются аналитиками данных и поставщиками информационных панелей.
Для сравнения показателей метрики можно использовать пулевые диаграммы. Например, если вы хотите сравнить продажи за два года или сравнить общие продажи с целью, вы можете использовать диаграммы с маркерами.
Вы можете использовать Bullet chart для отслеживания количества дефектов в категориях Low, Medium и High.
Вы можете визуализировать поток доходов за финансовый год.
Вы можете визуализировать расходы за финансовый год.
Вы можете отслеживать прибыль%.
Вы можете визуализировать удовлетворение клиента и использовать его для отображения ключевых показателей эффективности.
Подготовка данных
Расположите данные, как указано ниже.
Создание пули
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
Независимо от отрасли или бизнеса, в которой вы работаете, всегда можно определить целевой показатель эффективности (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 градусов.
Наша горизонтальная пулевая диаграмма готова. Обратите внимание, что при изменении значений в таблице с данными, изображение с диаграммой тоже будет менять свой внешний вид.
Вам также могут быть интересны следующие статьи
6 комментариев
А как можно доработать данную диаграмму под такие требования:
всего2 показателя качества Удовлетварительно (>99%) и неудовлетварительно (<99%)
цель находится в промежутке 99-100%, т.е. закрашен должен быть целый отрезок.
т.к. показатели могут отличатся на сотые, то в диаграмме необходимо отражать только отрезок 97%-100%.
я попробовал переделать вашу диаграмму, но успехом это увенчалось только частично.
Сергей, для этого нужно изменить минимальное значение оси Y. Щелкаем правой кнопкой по оси, из выпадающего меню выбираем Формат оси, в появившемся окне, в поле минимальное значение ставим 0,97. Вот скриншот настроек
Читайте также: