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

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

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

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

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

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

2. Затем щёлкните Вставка > Диаграммы статистики > Парето. См. снимок экрана:

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

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

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

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

Сначала подготовьте данные для создания диаграммы Парето:

1. Отсортируйте данные по убыванию: щёлкните ячейку B2, затем нажмите Данные > Сортировка от Я до А, см. снимок экрана:

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. Затем нажмите кнопку ОК, чтобы закрыть диалоговое окно, и вы получите диаграмму, как показано на снимке экрана ниже:

12. Далее щелкните правой кнопкой мыши по оси процентов и выберите Формат оси. В раскрывшейся области Формат оси, на вкладке Параметры оси, рядом с полем Максимум введите число 1,0 в текстовое поле (см. снимок экрана):

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

14. Наконец, переместите полосу прокрутки под диаграмму и введите следующую формулу: =«Target »&TEXT(B14,"0 %") — в ячейку под полосой прокрутки, чтобы отобразить целевое процентное значение (см. снимок экрана):

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

Скачать образец файла диаграммы Парето

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