Діаграма парето в excel.

Відео: Як побудувати діаграму Парето в Microsoft Excel

Є багато способів провести аналіз даних в Excel. Тут розглянемо, як побудувати діаграму Парето в Excel, щоб провести аналіз ефективності нашої діяльності. Наприклад, які товари приносять бо`льшую прибуток, які причини, помилки заважають роботі, гальмують бізнес, які зусилля не приносять результат, тощо

Закон Парето - це правило 80/20 (закон природи). Суть закону в тому, що всього 20% ефективних дій (витрат) дають 80% хорошого результату, великого прибутку. А решта 80% (витрат) зусиль дають всього 20% результату, прибутку. Виявивши найбільш ефективні моменти нашої діяльності, ми зможемо їх розвивати для поліпшення якості нашої роботи.
Діаграма Парето в Excel допомагає виявити ці 20% ефективних дій, найменування товару, який дає основний прибуток, виявити основні причини помилок, які заважають отримати хороший результат наших дій.
Ну, все, вистачить теорії. Приступимо до практики.
За допомогою діаграми Парето проведемо аналіз продажів в Excel.
Ми складемо діаграму Парето з продажу продуктів. У нас є такий звіт з продажу. Ми відсортували його від більшого до меншого (в порядку убування). Кнопка «Сортування і фільтр» знаходиться на закладці «Головна» в розділі «Редагування».

У стовпці C встановлюємо формулу, яка порахує, скільки відсотків доходу дає кожен товар із загальної суми доходу за всіма перерахованими в таблиці товарам. Наприклад, сума по стовпчику B береться за 100%. І вираховується, скільки відсотків прибутку приносить кожен окремий товар. Формулу напишемо таку. = B2 / $ B $ 8 * 100
Тепер нам потрібно прорахувати відсотки для діаграми Парето наростаючим підсумком в стовпці D. Формула буде вважати відсоток в комірці D2 як зазвичай. В осередку D3 буде стояти результат осередку D2 + D3, тощо В результаті в останній комірці стовпчика D буде стояти 100%. Формули в комірках стовпчика D будуть такі, дивіться на малюнку стовпець E.

Це ми розглянули детальний варіант, щоб можна було зрозуміти, які дані потрібні для складання діаграми Парето.
Замість цих двох формул можна написати одну. У стовпці F встановлюємо формат комірки - процентний. В осередку F2 пишемо таку формулу. = СУММ ($ B $ 2: B2) / СУММ ($ B $ 2: $ B $ 7)

Копіюємо формулу по стовпчику. Вийшло так. Результат такої ж, як в стовпці D.

А в стовпці G напишемо - «80%». Це поріг ефективності згідно із законом Парето.
Отже, приберемо з таблиці стовпці C, D, E, тому що вони у нас були для вивчення. Додамо стовпець "80%". У нас повинна бути така робоча таблиця з даними для діаграми Парето.

Будуємо діаграму звичайним способом. Як побудувати діаграму, налаштувати її, тощо, дивіться в статті «Як зробити діаграму в Excel».

Виділяємо таблицю з шапкою таблиці. На закладці «Вставка» в розділі «Діаграми» натискаємо кнопку «Гістограма». Вийшла така діаграма.

Легенду можна прибрати вниз. Тепер нам потрібно перенести відсотки (наростаючий відсоток прибутку) на допоміжну вісь. Натискаємо на діаграму. На закладці «Робота з діаграмами» - «Макет» в розділі «Поточний фрагмент» вибираємо зі списку «Ряд« наростаючий відсоток прибутку »». Цей ряд виділиться.

Натискаємо на виділений ряд правою кнопкою миші, вибираємо функцію «Формат ряду даних». У діалоговому вікні ставимо галочку у слів «За допоміжної осі».

Вийшла така діаграма.

Міняємо тип діаграми для ряду «Зростаючий відсоток прибутку». Натискаємо на цю діаграму (у нас, в прикладі, стовпці червоного кольору) правою кнопкою миші і вибираємо функцію «Змінити тип діаграми для ряду». Вибираємо «Графік з маркерами». Потім, через функцію «Формат ряду даних» з контекстного меню, налаштовуємо ширину лінії, колір, розмір, вид маркера, тощо Вийшло так.

Залишилося налаштувати ряд «Поріг ефективності». Теж переносимо на допоміжну лінію, міняємо тип графіка на «Лінія». З контекстного меню вибираємо функцію «Формат ряду даних» та налаштовуємо колір, ширину, вид лінії, тощо У нас вийшла така діаграма Парето.
Діаграма Парето, приклад в Excel.

Замість лінії "80%" на діаграмі, можна вставити пряму лінію. Тоді в таблиці стовпець робити не потрібно. На закладці "Вставка" в розділі "Ілюстрації" натискаємо на кнопку "Фігури". Вибираємо пряму лінію. Змінимо колір лінії на більш яскравий. Але, при переміщенні діаграми, лінія не переміститься.
Ну, і найголовніше - висновок.
Дивимося на перетин жовтої і червоної ліній. 80% прибутку приносить продаж перших двох товарів - це хліб і молоко. Решта товарів приносять лише 20% прибутку і, тільки, місце на полицях займають.))
Змінимо цифри прибутку в таблиці. Діаграма Парето зміниться так. Діаграма Парето в Excel.

Тепер за новими даними 80% прибутку приносять три товара - хліб, молоко, огірки.
Так можна контролювати процес будь-якої діяльності - прибуток, шлюб, помилки, тощо

В Excel можна зробити діаграму, яка буде порівнювати планові і фактичні дані. Діаграма допоможе планувати наші дії. Читайте про цю діаграмі у статті "Діаграма в Excel план-факт".

Можна зробити діаграму, яка відображатиме етапи великого довгострокового проекту, етапи роботи. Діаграмою Ганта можна зробити, навіть, графік відпусток. Дивіться статтю "Діаграма Ганта в Excel".

Інші приклади аналізу за допомогою діаграми Парето, наприклад, як провести аналіз часу або виявити причини збою, помилок, тощо, дивіться в статті "Діаграма Парето, приклад в Excel".

Поділися в соціальних мережах:

Схожі