Создание диаграммы Парето в Excel

В Excel диаграмма Парето состоит из столбчатой и линейной диаграмм, где столбцы представляют значения частоты в порядке убывания, а линия показывает накопленные итоги на вторичной оси. Она используется для анализа значимых областей дефектов в продукте и определения улучшений, которые увеличивают ценность компании. Чтобы создать диаграмму Парето для отображения наиболее распространенных причин отказов или дефектов продукта на рабочем листе Excel, эта статья вам поможет.

• Создание простой диаграммы Парето в Excel 2016 и более поздних версиях
• Создание простой диаграммы Парето в Excel 2013 и более ранних версиях
• Создание динамической диаграммы Парето в Excel
• Скачать пример файла с диаграммой Парето
• Видео: Создание диаграммы Парето в Excel

Создание простой диаграммы Парето в Excel 2016 и более поздних версиях

Если у вас есть Excel 2016 и более поздние версии, существует встроенная функция — Диаграмма Парето, которая поможет быстро и легко вставить диаграмму Парето. Пожалуйста, выполните следующие действия:

1. Выберите диапазон данных, на основе которого вы хотите создать диаграмму Парето.

2. Затем нажмите Вставка > Вставить статистическую диаграмму > Парето, см. скриншот:

3. После этого диаграмма Парето будет создана сразу, как показано на скриншоте ниже:

4. Затем вы можете изменить заголовок диаграммы и добавить метки данных по мере необходимости, см. скриншот:

Создание простой диаграммы Парето в Excel 2013 и более ранних версиях

Если вы используете Excel 2013 и более ранние версии, вам следует применить следующие шаги один за другим:

Во-первых, подготовьте данные для создания диаграммы Парето:

1. Вам нужно отсортировать данные в порядке убывания, выберите ячейку B2, затем нажмите Данные > Сортировка Z-A, см. скриншот:

2. И теперь диапазон данных отсортирован в порядке убывания, см. скриншот:

3. Затем рассчитайте Накопленный Счет, введя эту формулу =B2 в ячейку C2 в данном случае, и нажмите клавишу Enter. См. скриншот:

4. Затем введите эту формулу =C2+B3 в ячейку C3 и перетащите маркер заполнения вниз по ячейкам, см. скриншоты:

5. После получения накопленного счета продолжите расчет накопленного процента, введите эту формулу: =C2/$C$11 в ячейку D2 и перетащите маркер заполнения вниз по нужным ячейкам, см. скриншот:

6. Затем вам нужно преобразовать десятичные значения в процентные значения, выберите ячейки с формулами, затем нажмите Главная > Процентный формат, см. скриншот:

Во-вторых, создайте диаграмму на основе данных

7. После подготовки данных выберите данные в столбце A, столбце B и столбце D, удерживая клавишу Ctrl, см. скриншот:

8. Затем нажмите Вставка > Вставить столбчатую или гистограммную диаграмму > Группированная гистограмма, см. скриншот:

9. И вы получите диаграмму, как показано на скриншоте ниже:

10. Затем щелкните правой кнопкой мыши по красному столбцу (Накопленный Процент) и выберите Изменить тип диаграммы ряда данных из контекстного меню, см. скриншот:

11. В диалоговом окне Изменение типа диаграммы, на вкладке Все диаграммы, выберите Комбинированный вариант слева, в списке Выберите тип диаграммы и ось для ваших рядов данных, нажмите раскрывающийся список в поле Накопленный и выберите Линейчатый график с маркерами, и установите флажок, см. скриншот:

12. Затем нажмите кнопку OK, и вы получите диаграмму, как показано на скриншоте ниже:

13. Затем щелкните правой кнопкой мыши по оси процентов и выберите Формат оси из контекстного меню, см. скриншот:

14. В панели Формат оси, на вкладке Параметры оси, рядом с Максимум, установите значение 1.0 в текстовое поле, и диаграмма Парето будет успешно создана, см. скриншот:

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

Создание динамической диаграммы Парето в Excel

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

Чтобы создать этот тип диаграммы Парето, выполните следующие шаги по порядку:

1. Сначала рассчитайте накопленный процент в столбце C, примените эту формулу в ячейку C2:

2. Затем перетащите маркер заполнения вниз по ячейкам и отформатируйте десятичные числа как процентные значения, см. скриншот:

3. Далее вам понадобятся три вспомогательных ячейки: Цель, Накопленное значение, Связанное значение полосы прокрутки для выполнения некоторых расчетов, как показано на скриншоте ниже:

4. После создания трех ячеек нажмите Разработчик > Вставить > Полоса прокрутки (Элемент управления формы), и затем нарисуйте полосу прокрутки, как показано на скриншотах ниже:

5. Затем щелкните правой кнопкой мыши по полосе прокрутки и выберите Формат элемента управления из контекстного меню, в открывшемся диалоговом окне Формат объекта, на вкладке Элемент управления установите соответствующие значения и укажите связанную ячейку, как показано на скриншоте ниже:

6. Затем нажмите кнопку OK, чтобы закрыть диалоговое окно. Теперь введите следующие две формулы в ячейки B14 и B15 отдельно, а затем отформатируйте их как процентные значения:

7. А теперь создайте два вспомогательных столбца рядом с исходными данными и введите следующие две формулы в ячейки D2 и E2:

8. Затем скопируйте формулы в другие ячейки, как показано на следующем скриншоте:

9. После создания данных выберите данные в столбце A, столбце C, столбце D, столбце E, удерживая клавишу Ctrl, затем нажмите Вставка > Вставить столбчатую или гистограммную диаграмму > Группированная гистограмма, и диаграмма будет вставлена, как показано на скриншоте ниже:

10. Затем выберите диаграмму и нажмите Конструктор > Изменить тип диаграммы, в диалоговом окне Изменение типа диаграммы, на вкладке Все диаграммы, выберите Комбинированный слева, затем нажмите Пользовательская комбинированная диаграмма, затем в разделе Выберите тип диаграммы и ось для ваших рядов данных укажите следующие операции для каждого ряда данных:

• Накопленный %: Тип линейчатой диаграммы, и также отметьте флажок Вспомогательная ось;
• Выделенные столбцы: Тип группированной гистограммы;
• Остальные столбцы: Тип группированной гистограммы.

11. Затем нажмите кнопку OK, чтобы закрыть диалоговое окно, и вы получите диаграмму, как показано на скриншоте ниже:

12. Затем щелкните правой кнопкой мыши по оси процентов и выберите Формат оси, в расширенной панели Формат оси, на вкладке Параметры оси, рядом с Максимум, установите значение 1.0 в текстовое поле, см. скриншот:

13. Затем продолжайте щелкать правой кнопкой мыши по любому ряду столбцов на диаграмме и выберите Формат ряда данных, в панели Формат ряда данных, на вкладке Параметры ряда введите 100% в текстовое поле Перекрытие ряда, см. скриншот:

14. В конце вы можете переместить полосу прокрутки под диаграмму и ввести эту формулу: ="Цель "&TEXT(B14,"0%") в ячейку под полосой прокрутки, чтобы получить целевое процентное значение, см. скриншот:

15. Теперь динамическая диаграмма Парето создана, вы можете изменить цвет заливки выделенного столбца по своему усмотрению, и когда вы измените целевое значение с помощью полосы прокрутки, диаграмма Парето будет обновляться автоматически, как показано в демо ниже:

Скачать пример файла с диаграммой Парето

Видео: Создание диаграммы Парето в Excel