Создание диаграммы пули в Excel

Обычно диаграмма пули служит заменой датчиков и измерителей на информационных панелях, являясь одним из лучших инструментов визуализации данных. Она помогает сравнить одну меру с одной или несколькими связанными мерами. Например, мы сравниваем целевое значение с фактическим значением и отображаем их в контексте качественных диапазонов производительности (например, удовлетворительно, хорошо и отлично и т.д.) вертикально или горизонтально, как показано на скриншоте ниже. Мы можем оценить производительность фактического значения с первого взгляда по градиентному цвету, где оно находится.

Это руководство представляет собой пошаговое руководство, демонстрирующее два метода создания вертикальной и горизонтальной диаграмм пули в Excel.

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

Создание вертикальной диаграммы пули

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

Во-первых, подготовьте ваши данные

Нижняя таблица содержит целевые продажи, фактические продажи каждого человека и качественные диапазоны производительности (удовлетворительно, хорошо и отлично).

Создайте диаграмму пули на основе данных таблицы

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

Совет: Если ваша таблица содержит только две колонки, после вставки стековой колонки вам нужно нажать Дизайн > Переключить строку/колонку для обмена данными в диаграмме. В противном случае игнорируйте этот совет.

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

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

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

5. И тогда диаграмма будет отображаться, как показано ниже, пожалуйста, выберите вторичную ось, нажмите клавишу Удалить, чтобы удалить её из диаграммы.

6. Щелкните правой кнопкой мыши серию данных Цель снова и выберите Формат ряда данных из контекстного меню.

7. В панели Формат ряда данных перейдите на вкладку Заливка и линия, а затем выберите Без линии в разделе Линия под вкладкой Линия.

8. Нажмите вкладку Маркер под вкладкой Заливка и линия, и затем продолжайте настройку следующим образом:

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

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

11. А затем щелкните правой кнопкой мыши серию данных Факт снова и выберите Формат ряда данных из контекстного меню.

12. В панели Формат ряда данных укажите Ширину зазора (здесь я указываю 250%) в разделе Параметры ряда; Перейдите на вкладку Заливка и линия, укажите цвет заливки для него.

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

Вот финальная вертикальная диаграмма пули, созданная, как показано на скриншоте ниже.

Создание горизонтальной диаграммы пули

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

Во-первых, подготовьте ваши данные

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

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

Создайте диаграмму пули на основе данных таблицы

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

Совет: Если ваша таблица содержит только две колонки, после вставки стековой гистограммы вам нужно нажать Дизайн > Переключить строку/колонку для обмена данными в диаграмме. В противном случае игнорируйте этот совет.

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

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

3. Выберите метку оси Имя, щелкните правой кнопкой мыши и выберите Формат оси из контекстного меню.

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

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

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

7. Удалите вторичную ось из диаграммы. Просто выберите её и нажмите клавишу Удалить.

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

9. В диалоговом окне Выбор источника данных выберите Факт в поле Элементы легенды (Ряды) и затем нажмите кнопку Редактировать.

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

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

12. Выберите серию данных Цель (оранжевая полоса), щелкните правой кнопкой мыши и выберите Изменить тип диаграммы ряда из контекстного меню.

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

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

15. В диалоговом окне Выбор источника данных выберите Цель в поле Элементы легенды (Ряды) и затем нажмите кнопку Редактировать.

16. В диалоговом окне Редактирование ряда выберите значения Цель (здесь я выбираю B6:F6) в поле Значения X ряда, выберите данные вспомогательной строки (B10:F10 в данном случае) в поле Значения Y ряда, и затем нажмите ОК.

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

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

18. Выберите серию данных Факт (синие точки), перейдите на вкладку Дизайн, и затем нажмите Добавить элемент диаграммы > Линии ошибок > Стандартная ошибка.

19. Выберите горизонтальные линии ошибок, щелкните правой кнопкой мыши и выберите Формат линий ошибок.

20. В панели Формат линий ошибок вам нужно выполнить следующие настройки.

21. Затем, в появившемся диалоговом окне Пользовательские линии ошибок, выберите фактические значения (B5:F5) в поле Отрицательные значения ошибки, и затем нажмите ОК.

22. Удалите вертикальные линии ошибок из диаграммы. И тогда вы получите диаграмму пули, как показано ниже.

23. Выберите горизонтальные линии ошибок снова. Перейдите на вкладку Заливка и линия в панели Формат линий ошибок, и затем укажите цвет заливки и ширину для линий.

24. Выберите серию данных Факт (синие точки), перейдите на вкладку Заливка и линия в панели Формат ряда данных, нажмите Маркер, и затем выберите Нет в разделе Параметры маркера.

25. Укажите разные градиенты для столбцов Удовлетворительно, Хорошо и Отлично, отрегулируйте ширину столбцов, удалите или переименуйте заголовок диаграммы по необходимости. Теперь диаграмма показана ниже:

Затем вам нужно заменить оранжевые точки (серия данных Цель) в диаграмме пули указанными вертикальными линиями.

26. Перейдите на вкладку Вставка, нажмите Фигуры > Линия, как показано на скриншоте ниже.

27. Нарисуйте вертикальную линию на диаграмме. Выберите линию, перейдите на вкладку Формат, и затем укажите её Цвет темы и Вес в выпадающем списке Обводка фигуры. И вы получите вертикальную линию, как показано на скриншоте ниже.

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

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

Вот финальная диаграмма пули в Excel.

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

Скачать пример файла