Note: The other languages of the website are Google-translated. Back to English

Створіть діаграму парето в Excel

У програмі 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 з лівої панелі в Виберіть тип діаграми та вісь для вашого ряду даних списку, клацніть випадаючий список із сукупного поля та виберіть Лінія з маркерами діаграми введіть і встановіть прапорець, див. знімок екрана:

примітки: Якщо у вас Excel 2010 або 2007, по-перше, вам слід змінити тип діаграми на лінійну діаграму, а потім клацнути лінійну діаграму правою кнопкою миші та вибрати Форматувати ряд даних, у в Форматувати ряд даних діалогове вікно натисніть кнопку Параметри серії і перевірте Вторинна вісь у правому розділі.

12. А потім натисніть OK , і ви отримаєте діаграму, як показано на знімку екрана нижче:

13. Потім клацніть правою кнопкою миші процентну вісь і виберіть Формат осі з контекстного меню, див. знімок екрана:

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

15. Нарешті, ви можете змінити заголовок діаграми та додати мітки даних, як вам потрібно.


Створіть динамічну діаграму парето в Excel

У цьому розділі я розповім про те, як створити динамічну діаграму парето за допомогою смуги прокрутки. Коли ви змінюєте цільове значення, натискаючи або пересуваючи смугу прокрутки, діаграма автоматично підсвічує панель проблем, як показано нижче.

Щоб створити діаграму парето цього типу, виконайте наступні кроки:

1. Спочатку обчисліть сукупний відсоток у стовпці C, застосуйте цю формулу до комірки C2:

=SUM($B$2:B2)/SUM($B$2:$B$11)

2. А потім перетягніть маркер заповнення вниз до комірок і відформатуйте десяткові числа як відсотки, див. Знімок екрана:

3. Далі вам потрібні три допоміжні комірки, які є Ціль, Сукупне значення, Значення посилання смуги прокрутки, щоб зробити деякі обчислення, як показано на знімку екрана нижче:

4. Створивши три клітинки, натисніть Розробник > Insert > Смуга прокрутки (керування формою), а потім намалюйте смугу прокрутки, як показано на скріншотах нижче:

5. Потім клацніть правою кнопкою миші смугу прокрутки та виберіть Контроль формату з контекстного меню, що з’явиться Формат об'єкта у діалоговому вікні під Контроль на вкладці, встановіть відносні значення та вкажіть пов'язану комірку, як показано на знімку екрана нижче:

6. А потім натисніть OK , щоб закрити діалогове вікно. Тепер, будь-ласка, введіть наступні дві формули окремо в комірки B14 і B15, а потім відформатуйте їх як відсоткові значення:

B14: =B16/100
B15: =IFERROR(INDEX($C$2:$C$11,IFERROR(MATCH($B$14,$C$2:$C$11,1),0)+1),1)

7. А тепер вам слід створити два допоміжні стовпці поряд із вихідними даними та ввести наступні дві формули в комірки D2 та E2:

D2: =IF($B$15>=C2,B2,NA())
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 потужних розширених функцій (об’єднання робочих книг, підсумовування за кольором, розділення вмісту клітинок, перетворення дати тощо) і заощаджує 80% вашого часу.

  • Розроблений для 1500 сценаріїв роботи, допомагає вирішити 80% проблем Excel.
  • Щодня зменшуйте тисячі клацань клавіатури та миші, знімайте втомлені очі та руки.
  • Станьте експертом Excel за 3 хвилини. Більше не потрібно згадувати будь-які болісні формули та коди VBA.
  • Безкоштовна 30-денна безкоштовна пробна версія. 60-денна гарантія повернення грошей. Безкоштовне оновлення та підтримка протягом 2 років.
Стрічка Excel (із встановленим Kutools для Excel)

Вкладка Office - Увімкнути читання та редагування з вкладками в Microsoft Office (включаючи Excel)

  • Одна секунда для перемикання між десятками відкритих документів!
  • Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
  • Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
  • Приносить ефективні вкладки в Office (включаючи Excel), як і Chrome, Firefox та New Internet Explorer.
Знімок екрана Excel (із встановленою вкладкою Office)
Коментарі (0)
Поки немає оцінок. Оцініть першим!
There are no comments posted here yet
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця

Слідуй за нами

Copyright © 2009 - WWW.extendoffice.com. | Всі права захищені. На основі ExtendOffice. | Карта сайту
Microsoft та логотип Office є товарними знаками або зареєстрованими товарними знаками Microsoft Corporation у США та / або інших країнах.
Захищений Sectigo SSL