Подивіться відео - Як створити діаграму Парето в Excel
Діаграма Парето заснована на принципі Парето (також відомому як правило 80/20), який є добре відомою концепцією в управлінні проектами.
Відповідно до цього принципу, ~ 80% проблем можна віднести приблизно до 20% проблем (або ~ 80% ваших результатів можуть бути прямим результатом ~ 20% ваших зусиль тощо).
Відсоткове значення 80/20 може змінюватись, але ідея полягає в тому, що серед усіх питань/зусиль є декілька, які приносять максимальний вплив.
Це широко використовувана концепція в управлінні проектами для визначення пріоритетів роботи.
Створення діаграми Парето в Excel
У цьому уроці я покажу вам, як зробити:
- Проста (статична) діаграма Парето в Excel.
- Динамічна (інтерактивна) діаграма Парето в Excel.
Створити діаграму Парето в Excel дуже просто.
Вся хитрість прихована в тому, як ви упорядковуєте дані в бекенді.
Давайте візьмемо приклад готелю, для якого дані скарг можуть виглядати так, як показано нижче:
ПРИМІТКА. Щоб створити діаграму Парето в Excel, потрібно упорядкувати дані в порядку спадання.
Створення простої (статичної) діаграми Парето в Excel
Ось кроки для створення діаграми Парето в Excel:
- Налаштуйте свої дані, як показано нижче.
- Обчисліть сукупний % у стовпці C. Використовуйте таку формулу: = SUM ($ B $ 2: B2)/SUM ($ B $ 2: $ B $ 1)
- Виберіть весь набір даних (A1: C10), перейдіть до пункту Вставка -> Діаграми -> 2 -D стовпець -> Кластерна колонка. Це додає стовпчасту діаграму з 2 серіями даних (# скарг та сукупний відсоток).
- Клацніть правою кнопкою миші на будь-якій смузі та виберіть Змінити тип діаграми серії.
- У діалоговому вікні Змінити тип діаграми виберіть Комбіноване на лівій панелі.
- Внесіть такі зміни:
- Кількість скарг: Кластерна колонка.
- Сукупний %: Лінія (також установіть прапорець Вторинна вісь).
[Якщо ви використовуєте Excel 2010 або 2007, це буде двоетапний крок процесу. Спочатку змініть тип діаграми на лінійну діаграму. Потім клацніть правою кнопкою миші на лінійній діаграмі, виберіть Формат ряду даних і виберіть Вторинна вісь у параметрах ряду]
- Ваша діаграма Pareto в Excel готова. Налаштуйте значення вертикальної осі та назву діаграми.
Як інтерпретувати цю діаграму Парето в Excel
У цій таблиці Парето висвітлюються основні питання, на які готель повинен зосередитися, щоб відсортувати максимальну кількість скарг. Наприклад, орієнтування на перші 3 питання автоматично вирішить ~ 80% скарг.
Наприклад, орієнтування на перші 3 питання автоматично вирішить ~ 80% скарг.
Створення динамічної (інтерактивної) діаграми Парето в Excel
Тепер, коли у нас є статична/проста діаграма Парето в Excel, давайте зробимо крок далі і зробимо її трохи інтерактивною.
Щось, як показано нижче:
У цьому випадку користувач може вказати % скарг, які необхідно вирішити (за допомогою панелі прокрутки Excel), і діаграма автоматично виділить питання, які слід розглянути.
Ідея тут полягає в тому, щоб мати 2 різні бари.
Червоний виділяється, коли сукупне відсоткове значення близьке до цільового.
Ось кроки для створення цієї інтерактивної діаграми Парето в Excel:
- У комірці В14 у мене є цільове значення, яке пов’язане з смугою прокрутки (значення якої змінюється від 0 до 100).
- У комірці В12 я використав формулу = В14/100. Оскільки ви не можете вказати відсоткове значення для смуги прокрутки, ми просто поділяємо значення смуги прокрутки (у B14) на 100, щоб отримати значення відсотка.
- У клітинку В13 введіть таку комбінацію функцій INDEX, MATCH та IFERROR:
= IFERROR (ІНДЕКС ($ C $ 2: $ C $ 10, IFERROR (МАТЧ ($ B $ 12, $ C $ 2: $ C $ 10,1), 0) +1), 1)
Ця формула повертає сукупне значення, яке охоплює цільове значення. Наприклад, якщо у вас цільове значення 70%, воно поверне 77%, що означає, що ви повинні спробувати вирішити перші три проблеми.
- У клітинці D2 введіть таку формулу (і перетягніть або скопіюйте для всієї комірки - D2: D10):
= IF ($ B $ 13> = C2, B2, NA ()) - У комірку E2 введіть таку формулу (і перетягніть або скопіюйте для всієї комірки - E2: E10):
= ЯКЩО ($ B $ 13<> - Виберіть Дані у стовпцях A, C, D & E (натисніть клавішу управління та виберіть за допомогою миші).
- Перейдіть до Вставка -> Діаграми -> 2 -D стовпець -> Кластерна колонка. Буде вставлено стовпчасту діаграму з 3 -ма рядами даних (сукупний відсоток, смуги, які потрібно виділити для досягнення цілі, та інші залишки)
- Клацніть правою кнопкою миші на будь-якій смузі та виберіть Змінити тип діаграми серії.
- У діалоговому вікні Змінити тип діаграми виберіть Комбіноване на лівій панелі та внесіть такі зміни:
- Сукупний %: Лінія (також установіть прапорець Вторинна вісь).
- Виділені смуги: кластерна колонка.
- Залишаються стовпчики: Кластерна колонка.
- Клацніть правою кнопкою миші на будь-якій виділеній смузі та змініть колір на Червоний.
Це воно!
Ви створили інтерактивну діаграму Парето в Excel.
Тепер, коли ви змінюєте ціль за допомогою смуги прокрутки, діаграма Парето оновлюється відповідно.
Чи використовуєте ви діаграму Парето в Excel?
Я хотів би почути вашу думку про цю техніку та про те, як ви її використовували. Залишайте свої сліди в розділі коментарів 🙂
- Аналіз скарг на ресторан за допомогою діаграми Парето.
- Створення діаграми Ганта в Excel.
- Створення діаграми етапу в Excel.
- Створення гістограми в Excel.
- Шаблон калькулятора розкладу Excel.
- Шаблон відстеження відходу співробітників.
- Розрахунок середньозваженого в Excel.
- Створення кривої дзвінка в Excel.
- Розширені діаграми Excel
- Як додати вторинну вісь у діаграми Excel.
