Создать диаграмму Парето в 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. Затем вам понадобятся три вспомогательные ячейки: Target, Cumulative Value, Scroll Bar Link Value, чтобы выполнить некоторые вычисления, как показано ниже:
4. После создания трех ячеек нажмите Застройщик > Вставить > Полоса прокрутки (элемент управления формой), а затем нарисуйте полосу прокрутки, как показано ниже:
 |
|
 |
5. Затем щелкните полосу прокрутки правой кнопкой мыши и выберите Управление форматом из контекстного меню во всплывающем Форматировать объект диалоговое окно под Control на вкладке, установите относительные значения и укажите связанную ячейку, как показано ниже:
6. А затем нажмите OK кнопку, чтобы закрыть диалоговое окно. Теперь введите следующие две формулы в ячейки B14 и B15 отдельно, а затем отформатируйте их как процентные значения:
B15: =IFERROR(INDEX($C$2:$C$11,IFERROR(MATCH($B$14,$C$2:$C$11,1),0)+1),1)
7. И теперь вы должны создать два вспомогательных столбца рядом с исходными данными и ввести две формулы ниже в ячейки D2 и E2:
E2: =IF($B$15<C2,B2,NA())
8. Затем скопируйте формулы в другие ячейки, как показано на следующем снимке экрана:
9. После создания данных выберите данные в столбце A, столбце C, столбце D, столбце E, удерживая Ctrl ключ, затем нажмите Вставить > Вставить столбец или гистограмму > Кластерный столбец, и диаграмма вставлена, как показано на скриншоте ниже:
10. Затем выберите диаграмму и нажмите Проект > Изменить тип диаграммы, В Изменить тип диаграммы диалоговое окно под Все диаграммы вкладку нажмите Комбо на левой панели, а затем щелкните Таблица пользовательских комбинаций, то в Выберите тип диаграммы и ось для ряда данных В разделе, пожалуйста, укажите следующие операции для каждой серии данных:
- Совокупный%: Тип линейной диаграммы и проверьте Вторичная ось также флажок;
- Выделенные полосы: Тип столбчатой диаграммы с кластерами;
- Оставшиеся бары: Тип кластерной столбчатой диаграммы.
11, Затем нажмите OK кнопку, чтобы закрыть диалоговое окно, и вы получите диаграмму, как показано на скриншоте ниже:
12. Затем щелкните правой кнопкой мыши процентную ось и выберите Ось формата вариант, в расширенном Ось формата панель, под Параметры оси вкладка рядом с максимальная, установите номер на 1.0 в текстовое поле, см. снимок экрана:
13. Затем щелкните правой кнопкой мыши любую серию столбцов на диаграмме и выберите Форматировать ряд данных, В Форматировать ряд данных панель, под Варианты серий вкладка, введите 100% в Перекрытие серий текстовое поле, см. снимок экрана:
14. Наконец, вы можете переместить полосу прокрутки под диаграмму и ввести эту формулу: = "Цель" & ТЕКСТ (B14, "0%") в ячейку под полосой прокрутки, чтобы получить целевое процентное значение, см. снимок экрана:
15. Теперь, когда динамическая диаграмма Парето создана, вы можете изменить цвет заливки выделенной полосы по своему усмотрению, а при изменении целевого значения с помощью полосы прокрутки диаграмма Парето будет обновляться автоматически, как показано ниже:
Скачать образец файла диаграммы Парето
Видео: создание диаграммы Парето в Excel
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Kutools для Excel может похвастаться более чем 300 функциями, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...
Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
