График-шкала «Bullet Chart» в Excel

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

Красивая, эффектная и понятная с первого беглого взгляда визуализация уровня или процесса при помощи вертикального или горизонтального индикатора давно находит применение в самых разных областях нашей жизни:

— ртутные, спиртовые и шкальные электронные  термометры;

— столбчатые индикаторы уровней топлива и масла на панелях приборов автомобилей;

— полоски-индикаторы выполнения процессов в операционной системе компьютера;

— диаграммы «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.

Ссылка на скачивание файла программы: grafik-shkala (xls 22.5KB).

Другие статьи автора блога

На главную