Создание График-пуля в Excel

График-пуля обычно используется на панелях мониторинга вместо традиционных датчиков и измерительных приборов и считается одним из самых эффективных инструментов визуализации данных. Он позволяет сравнивать одно ключевое значение с одним или несколькими связанными показателями. Например, вы можете сопоставить целевое значение с фактическим и отобразить их в контексте качественных диапазонов эффективности — таких как «удовлетворительно», «хорошо» и «отлично» — как вертикально, так и горизонтально, как показано на скриншоте ниже. Благодаря градиентной окраске фактическое значение сразу визуально оценивается по уровню эффективности.

В этом пошаговом руководстве описаны два метода создания вертикального и горизонтального графиков-пуль в Excel.

Создание вертикального График-пуля
Создание горизонтального График-пуля
Легко создайте горизонтальный График-пуля с помощью замечательного инструмента
Скачать образец файла

Создание вертикального График-пуля

В этом разделе вы подробно узнаете, как создать вертикальный график-пулю.

Сначала подготовьте данные

В приведённой ниже таблице указаны план продаж, фактические продажи каждого сотрудника и качественные диапазоны эффективности: «удовлетворительно», «хорошо» и «отлично».

Создайте График-пуля на основе данных таблицы

1. Выделите всю таблицу, перейдите на вкладку «Вставка» и нажмите «Вставить столбец или гистограмму → «Сгруппированный столбец».

Совет: Если в вашей таблице всего два столбца, после вставки сгруппированного столбца нажмите «Конструктор» → «Поменять строки и столбцы местами», чтобы поменять местами данные на диаграмме. В противном случае этот совет можно проигнорировать.

2. Теперь, когда сгруппированный столбец вставлен, выделите все линии сетки щелчком и нажмите клавишу «Delete», чтобы удалить их с диаграммы.

3. Щелкните правой кнопкой мыши по ряду данных цели (в данном случае — оранжевый столбец) и в контекстном меню выберите пункт «Изменить тип ряда Тип диаграммы».

4. Откроется диалоговое окно «Изменение типа диаграммы». Перейдите в раздел «Выбор типа диаграммы и оси для ряда данных», в поле «Цель» выберите имя серии, затем в раскрывающемся списке «Тип диаграммы» укажите значение «Сгруппированная линия с маркерами», установите флажок «Вспомогательная ось» и нажмите кнопку «ОК».

5. Диаграмма теперь выглядит так, как показано ниже. Чтобы удалить вспомогательную ось, выделите её и нажмите клавишу «Delete».

6. Снова щелкните правой кнопкой мыши по ряду данных «Цель» и в контекстном меню выберите пункт «Формат ряда данных».

7. В области «Формат ряда данных» перейдите на вкладку «Заливка и границы» и в разделе «Линия» выберите параметр «Нет линии» на вкладке «Линия».

8. Перейдите на вкладку «Маркеры» в разделе «Заливка и границы» и выполните следующие настройки:

9. Вернитесь к диаграмме, щёлкните правой кнопкой мыши по ряду данных «Факт» (в данном случае — синий столбец) и в контекстном меню выберите пункт «Изменить тип ряда Тип диаграммы».

10. В диалоговом окне «Изменение типа диаграммы» установите флажок «Вспомогательная ось» для ряда «Факт» и нажмите кнопку «ОК».

11. Затем снова щелкните правой кнопкой мыши по ряду данных «Факт» и выберите в контекстном меню пункт «Формат ряда данных».

12. В области «Формат ряда данных» на вкладке «Параметры ряда» укажите значение 250 % для параметра «Ширина зазора»; перейдите на вкладку «Заливка и границы» и задайте для неё нужный цвет заполнения.

13. Задайте разные градиенты для столбцов «Удовлетворительно», «Хорошо» и «Отлично». При необходимости удалите или переименуйте Заголовок диаграммы.

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

Создание горизонтального График-пуля

Если вы хотите создать горизонтальный график-пулю, метод, описанный в этом разделе, поможет вам легко справиться с задачей.

Сначала подготовьте данные

Возьмём за основу данные из приведённой выше таблицы и создадим вспомогательную строку для дальнейшей работы.

Введите число 0,5 в последнюю ячейку столбца «Последний столбец»; в предыдущую ячейку — 1,5 (0,5 + 1). Продолжайте прибавлять по 1, пока все ячейки не заполнятся нужными числами. См. скриншот:

Создайте График-пуля на основе данных таблицы

1. Выделите диапазон таблицы A4:F9, исключая вспомогательную строку. Перейдите на вкладку «Вставка» и выберите «Вставить столбец или гистограмму» → «Сгруппированная линейчатая диаграмма».

Совет: Если в вашей таблице всего два столбца, после вставки сгруппированной линейчатой диаграммы нажмите «Конструктор» → «Поменять строки и столбцы местами», чтобы поменять данные на диаграмме местами. В противном случае этот совет можно проигнорировать.

2. Теперь, когда сгруппированная линейчатая диаграмма вставлена, щелкните, чтобы выделить все линии сетки, и нажмите клавишу «Delete», чтобы удалить их с диаграммы.

Как видите, имена на диаграмме отображаются в обратном порядке. Их необходимо изменить, чтобы соответствовать порядку исходных данных.

3. Выделите подписи оси имён, щелкните правой кнопкой мыши и выберите в контекстном меню пункт «Формат оси».

4. В области «Формат оси» на вкладке «Параметры оси» в разделе «Параметры оси» установите флажок «Категории в обратном порядке».

5. Выделите ряд данных «Факт» (в данном случае — синюю полосу), щелкните правой кнопкой мыши и в контекстном меню выберите пункт «Изменить тип ряда Тип диаграммы».

6. В диалоговом окне «Изменение типа диаграммы» найдите ряд «Факт» в поле «Выбор типа диаграммы и оси для ряда данных», выберите значение «Точечная» в раскрывающемся списке «Тип диаграммы» и нажмите кнопку «ОК».

7. Удалите вспомогательную ось с диаграммы: просто выделите её и нажмите клавишу «Delete».

8. Щелкните правой кнопкой мыши по диаграмме и в контекстном меню выберите пункт Выбрать данные.

9. В диалоговом окне Выбрать данные Source выберите Actual в поле Диапазон имени ряда и нажмите кнопку Edit.

10. В диалоговом окне Edit Series выберите фактическое значение (в данном случае — B5:F5) в поле Значение серии по оси X, выберите данные вспомогательной строки (в данном случае — B10:F10) в поле Значение серии по оси Y и нажмите OK.

11. Нажмите OK, когда вернётесь в диалоговое окно Выбрать данные Source.

12. Выберите целевой ряд данных (оранжевый столбец), щёлкните правой кнопкой мыши и в контекстном меню выберите команду Change Series Тип диаграммы.

13. В диалоговом окне Change Тип диаграммы найдите поле Target Имя серии в разделе Choose the Тип диаграммы and axis for your data series, выберите тип Scatter из раскрывающегося списка Тип диаграммы и нажмите кнопку OK.

14. Щёлкните правой кнопкой мыши по диаграмме и в контекстном меню выберите команду Выбрать данные.

15. В диалоговом окне Выбрать данные Source выберите Target в поле Диапазон имени ряда и нажмите кнопку Edit.

16. В диалоговом окне Edit Series выберите значения Target (в данном случае — B6:F6) в поле Значение серии по оси X, выберите данные вспомогательной строки (в данном случае — B10:F10) в поле Значение серии по оси Y и нажмите OK.

17. Нажмите OK, когда вернётесь в диалоговое окно Выбрать источник данных.

Теперь диаграмма отображается так, как показано на приведённом ниже снимке экрана.

18. Выделите ряд данных Actual (синие точки), перейдите на вкладку «Конструктор» и нажмите Add Chart Element > Error Bars > Standard Error.

19. Выделите горизонтальные линии погрешностей, щёлкните правой кнопкой мыши и выберите Format Error Bars.

20. В области Format Error Bars выполните следующие настройки.

21. Затем в появившемся диалоговом окне Custom Error Bars выберите фактические значения (B5:F5) в поле Negative Error Values и нажмите OK.

22. Удалите вертикальные линии погрешностей с диаграммы. После этого вы получите График-пуля , как показано ниже.

23. Снова выделите горизонтальные линии погрешностей. Перейдите на вкладку Fill & Line в области Format Error Bars и задайте цвет заливки color и ширину width для линий.

24. Выделите ряд данных Actual (синие точки), перейдите на вкладку Fill & Line в области Format Data Series, нажмите Marker и выберите значение None в разделе Marker Options.

23. Задайте разные градиенты для столбцов Fair, Good и Excellent, скорректируйте ширину столбцов, удалите или переименуйте Заголовок диаграммы по мере необходимости. Теперь диаграмма выглядит так, как показано ниже:

Затем замените оранжевые точки (целевой ряд данных) на указанную вертикальную линию в графике-пуле.

26. Перейдите на вкладку «Вставка», нажмите Shapes > Line, как показано на снимке экрана ниже.

27. Нарисуйте вертикальную линию на диаграмме. Выделите линию, перейдите на вкладку Format и задайте её Theme цвет и Weight в раскрывающемся списке Shape Outline. После этого вы получите вертикальную линию, как показано на приведённом ниже снимке экрана.

28. Выделите вертикальную линию и нажмите клавиши Ctrl+C, чтобы скопировать её.

29. Теперь выделите целевой ряд данных (оранжевые точки) и нажмите клавиши Ctrl+V, чтобы заменить все точки на вертикальную линию. Затем удалите вертикальную линию, которую вы нарисовали на шаге 27.

Вот итоговый файл Excel График-пуля .

Легко создавайте вертикальную или горизонтальную График-пуля в Excel

Скачать образец файла