Створіть діаграму парето в Excel
У програмі Excel діаграма парето складається із стовпців та лінійних діаграм, де стовпчики стовпців представляють значення частоти в порядку зменшення, а рядок вказує сукупні підсумки на вторинній осі. Він використовується для аналізу значних ділянок дефектів товару та визначення вдосконалень, що збільшують вартість компанії. Ця стаття допоможе вам створити діаграму Парето для відображення найпоширеніших причин несправності, дефектів продукту на аркуші Excel.
- Створіть просту діаграму парето в Excel 2016 та пізніших версіях
- Створіть просту діаграму парето в Excel 2013 та попередніх версіях
- Створіть динамічну діаграму парето в Excel
- Завантажте зразок файлу діаграми Парето
- Відео: Створення діаграми Парето в Excel
Створіть просту діаграму парето в Excel 2016 та пізніших версіях
Якщо у вас є Excel 2016 та пізніші версії, є вбудована функція - діаграма Парето, яка допоможе вам швидко та легко вставити діаграму парето. Будь ласка, виконайте наступне:
1. Виберіть діапазон даних, на основі якого потрібно створити діаграму парето.
2. Потім натисніть кнопку Insert > Вставити статистичну діаграму > Парето, див. скріншот:
3. А потім була створена діаграма парето відразу, як показано на знімку екрана:
4. Потім ви можете редагувати заголовок діаграми та додавати мітки даних, як вам потрібно, див. Знімок екрана:
Створіть просту діаграму парето в Excel 2013 та попередніх версіях
Якщо ви використовуєте Excel 2013 та попередні версії, вам слід застосувати такі кроки по одному:
Спочатку підготуйте дані для створення діаграми парето:
1. Вам слід відсортувати дані за спаданням, виберіть клітинку B2, а потім натисніть дані > Відсортуйте Z до A, див. скріншот:
2. А потім діапазон даних було відсортовано за спаданням, див. Знімок екрана:
3. Потім обчисліть сукупний рахунок, набравши цю формулу = B2 в цьому випадку в клітинку C2 і натисніть Що натомість? Створіть віртуальну версію себе у ключ. Дивіться знімок екрана:
4. А потім введіть цю формулу = C2 + B3 в комірку C3 і перетягніть маркер заповнення вниз до комірок, див. скріншоти:
 |
 |
 |
5. Після отримання сукупного підрахунку, будь ласка, продовжуйте обчислювати сукупний відсоток, введіть цю формулу: = C2 / $ C $ 11 в комірку D2 і перетягніть маркер заповнення до потрібних комірок, див. знімок екрана:
6. Потім вам слід перетворити десяткові значення у відсотки, виділити клітинки формули та клацнути Головна > Процентний стиль, див. скріншот:
По-друге, створіть діаграму на основі даних
7. Після підготовки даних, будь ласка, виберіть дані у стовпці А, стовпці В та стовпчику D за Ctrl ключ, див. знімок екрана:
8. А потім натисніть Insert > Вставити стовпчик або стовпчасту діаграму > Кластерна колонка, див. скріншот:
9. І ви отримаєте діаграму, як показано на знімку екрана нижче:
10. Потім клацніть правою кнопкою миші одну червону смужку (Сукупний відсоток) і виберіть Змінити тип діаграми серії у контекстному меню див. знімок екрана:
11, в Змінити тип діаграми у діалоговому вікні під Усі діаграми вкладка, клацніть Combo з лівої панелі в Виберіть тип діаграми та вісь для вашого ряду даних списку, клацніть випадаючий список із сукупного поля та виберіть Лінія з маркерами діаграми введіть і встановіть прапорець, див. знімок екрана:
12. А потім натисніть OK , і ви отримаєте діаграму, як показано на знімку екрана нижче:
13. Потім клацніть правою кнопкою миші процентну вісь і виберіть Формат осі з контекстного меню, див. знімок екрана:
14, в Формат осі під панеллю Параметри осі , поруч із Максимальний, встановіть номер на 1.0 в текстове поле, і діаграма парето була успішно створена, див. знімок екрана:
15. Нарешті, ви можете змінити заголовок діаграми та додати мітки даних, як вам потрібно.
Створіть динамічну діаграму парето в Excel
У цьому розділі я розповім про те, як створити динамічну діаграму парето за допомогою смуги прокрутки. Коли ви змінюєте цільове значення, натискаючи або пересуваючи смугу прокрутки, діаграма автоматично підсвічує панель проблем, як показано нижче.
Щоб створити діаграму парето цього типу, виконайте наступні кроки:
1. Спочатку обчисліть сукупний відсоток у стовпці C, застосуйте цю формулу до комірки C2:
2. А потім перетягніть маркер заповнення вниз до комірок і відформатуйте десяткові числа як відсотки, див. Знімок екрана:
3. Далі вам потрібні три допоміжні комірки, які є Ціль, Сукупне значення, Значення посилання смуги прокрутки, щоб зробити деякі обчислення, як показано на знімку екрана нижче:
4. Створивши три клітинки, натисніть Розробник > Insert > Смуга прокрутки (керування формою), а потім намалюйте смугу прокрутки, як показано на скріншотах нижче:
 |
|
 |
5. Потім клацніть правою кнопкою миші смугу прокрутки та виберіть Контроль формату з контекстного меню, що з’явиться Формат об'єкта у діалоговому вікні під Контроль на вкладці, встановіть відносні значення та вкажіть пов'язану комірку, як показано на знімку екрана нижче:
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 клавішу, а потім натисніть Insert > Вставити стовпчик або стовпчасту діаграму > Кластерна колонка, і діаграма вставляється, як показано на знімку екрана нижче:
10. Потім виберіть діаграму та натисніть дизайн > Змінити тип діаграми, В Змінити тип діаграми у діалоговому вікні під Усі діаграми вкладка, клацніть Combo на лівій панелі, а потім клацніть Спеціальна діаграма комбінацій, потім в Виберіть тип діаграми та вісь для вашого ряду даних вкажіть наступні операції для кожного ряду даних:
- Кумулятивний%: Тип лінійної діаграми та перевірте Вторинна вісь прапорець також;
- Виділені смуги: Тип кластерної діаграми стовпців;
- Залишилися бари: Тип діаграми кластерної колонки.
11. Потім натисніть кнопку OK , щоб закрити діалогове вікно, і ви отримаєте діаграму, як показано нижче:
12. А потім клацніть правою кнопкою миші процентну вісь і виберіть Формат осі варіант, в розгорнутому Формат осі під панеллю Параметри осі , поруч із Максимальний, встановіть номер на 1.0 у текстовому полі див. знімок екрана:
13. А потім клацніть правою кнопкою миші будь-яку серію стовпчиків на діаграмі, а потім виберіть Форматувати ряд даних, В Форматувати ряд даних під панеллю Параметри серії , введіть 100% в Накладання серії текстове поле, див. знімок екрана:
14. Нарешті, ви можете перемістити смугу прокрутки під діаграмою та ввести цю формулу: = "Ціль" і ТЕКСТ (B14, "0%") в клітинку під смугою прокрутки, щоб отримати цільове відсоткове значення, див. знімок екрана:
15. Тепер, динамічну діаграму парето створено, ви можете змінювати колір заливки виділеної смуги, як вам потрібно, а при зміні цільового значення за допомогою смуги прокрутки діаграма парето оновлюється автоматично, як показано нижче:
Завантажте зразок файлу діаграми Парето
Відео: Створення діаграми Парето в Excel
Найкращі інструменти для підвищення продуктивності офісу
Kutools для Excel - допомагає виділитися з натовпу
Kutools для Excel має понад 300 функцій, Переконайтеся, що те, що вам потрібно, знаходиться лише на відстані одного кліка...
Вкладка Office - увімкніть читання та редагування вкладок у Microsoft Office (включаючи Excel)
- Одна секунда для перемикання між десятками відкритих документів!
- Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
- Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
- Додає ефективні вкладки в Office (включно з Excel), як у Chrome, Edge та Firefox.
