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

Опубликовано 03 Мар 2015
Рубрика: Справочник Excel | Один комментарий



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

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

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

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

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

— диаграммы «Bullet Chart» (дословно: «график-пуля») в MS Excel.

Так как программу Excel считают «своей» и инженеры, и финансисты, и управленцы, то примеров интересного применения графиков-шкал может быть бесчисленное множество.

В этой статье рассмотрен пример создания в MS Excel графика-шкалы модифицированного специалистом по дашбордам Стивеном Фью.

График-шкала выполнения плана производства.

Построим «график-пулю» для ежедневного информирования работников производственного участка о ходе выполнения месячного плана.

Результатом работы станут отформатированные таблица и график-шкала в 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. На первом шаге создания диаграммы в выпавшем окне на вкладке «Стандартные» выбираем тип диаграммы «Гистограмма», вид – «Гистограмма с накоплением…»

Мастер диаграмм шаг 1-21s

3. Нажав на кнопку «Далее», на вкладке «Диапазон данных» включаем чек-бокс «Ряды в: строках».

Мастер диаграмм шаг 2-21s



Нажимаем на кнопку «Готово» (пропускаем третий и четвертый шаги «Мастера диаграмм») - заготовка для  диаграммы «Bullet Chart» сделана.

4. Убирать ненужные элементы с поля диаграммы Excel удобно так: выделяем мышью элемент и нажимаем на клавишу «Delete» на клавиатуре. Удаляем таким способом «Легенду» и горизонтальную «Ось категорий», очищая поле будущего графика-шкалы.

5. Наводим курсор мыши на сине-фиолетовый нижний столбец, отображающий ряд «Факт:» и щелчком правой кнопки мыши вызываем контекстное меню, в котором выбираем «Формат рядов данных…». В появившемся окне «Формат ряда данных» на вкладке «Вид» меняем цвет заливки на черный.

format-ryada-dannykh-vid-1-21s

6. Переходим на вкладку «Ось» этого же окна и включаем чек-бокс «Построить ряд по вспомогательной оси».

format-ryada-dannykh-os-21s

7. Здесь же на вкладке «Параметры» изменяем «Ширину зазора».

format-ryada-dannykh-parametry-21s

Нажимаем «ОК» — главный информационный черный столбик, отражающий ряд «Факт:», готов.

8. Займемся рядом «Цель:» и превратим его изображение на графике-шкале в красную узкую полоску. Наводим курсор мыши на вишневый нижний столбец, отображающий этот ряд, вновь вызываем контекстное меню правой кнопки мыши и выбираем «Тип диаграммы…». В выпавшем окне Excel «Тип диаграммы» на вкладке «Стандартные» меняем тип диаграммы на «Точечная».

tip-diagrammy-standartnye-21s

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

9. Щелчком правой кнопки мыши по лиловой точке, показывающей теперь ряд «Цель:», вызываем контекстное меню и делаем выбор – «Формат рядов данных». В окне Excel «Формат ряда данных» на вкладке «Вид» можно выключить маркер.

format-ryada-dannykh-vid-2-21s

10. Переходим в этом же окне на вкладку «X-погрешности», настраиваем так, как показано на следующем скриншоте и нажимаем «ОК».

format-ryada-dannykh-x-pogreshnosti-21s

11. Наводим курсор на появившуюся вместо маркера тонкую черную полоску – планку погрешности, в очередной раз правой кнопкой мышки вызываем контекстное меню и выбираем «Формат полос погрешностей…». В выпавшем окне «Формат планок погрешностей» настраиваем вкладку «Вид» в соответствии со следующим рисунком.

format-planok-pogreshnostey-vid-21s

Красная горизонтальная полоска, показывающая «Цель:», создана.

12. Приведем все столбцы к одной вертикальной шкале. Для удаления правой вертикальной оси — «Дополнительной оси значений» — выделим ее мышью и нажмем на клавиатуре «Delete».

13. Так как столбики «Очень плохо», «Плохо» и «Хорошо» стоят сейчас в неправильной последовательности, то необходимо выстроить их как положено, переместив ряд «Очень плохо» вниз, а ряд «Хорошо», подняв на самый верх. Для этого наводим курсор на любой из вышеназванных столбцов, вызываем контекстное меню и выбираем «Формат рядов данных». В появившемся окне Excel «Формат ряда данных» настраиваем вкладку «Порядок рядов» так, как на скриншоте внизу.

format-ryada-dannykh-poryadok-ryadov-21s

График-шкала построен. Осталось завершить форматирование.

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).

Вопросы, замечания, пожелания пишите в комментариях, уважаемые читатели.

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

На главную


Введите Ваш e-mail:

Статьи с близкой тематикой

Отзывы

Один комментарий на «График-шкала «Bullet Chart» в Excel»

  1. Николай 04 Мар 2015 11:39

    Александр прекрасный график контролирует , стимулирует работу большое Вам спасибо... С уважением Николай...

Ваш отзыв







  • Посетители: 657 649

  • Подписчики: 3 416