зміст
Можливо, ви чули про закон Парето або принцип 20/80. В кінці 19 століття італійський соціолог і економіст Вільфредо Парето виявив, що розподіл багатства в суспільстві є нерівномірним і підпорядковується певній залежності: зі збільшенням багатства число багатих людей експоненціально зменшується з постійним коефіцієнтом ( серед італійських домогосподарств 80% доходу припадало на 20% сімей). Пізніше цю ідею розвинув у своїй книзі Річард Кох, який запропонував формулювання універсального «Принципу 20/80» (20% зусиль дають 80% результату). На практиці цей закон зазвичай не виражається такими красивими цифрами (читайте «Довгий хвіст» Кріса Андерсона), але чітко показує нерівномірний розподіл ресурсів, прибутків, витрат і т.д.
У бізнес-аналізі часто будують діаграму Парето, щоб відобразити цю нерівномірність. Його можна використовувати, щоб наочно показати, наприклад, які продукти або клієнти приносять найбільший прибуток. Зазвичай це виглядає так:
Його основні особливості:
- Кожен синій стовпчик гістограми відображає прибуток продукту в абсолютних одиницях і відкладається на лівій осі.
- Помаранчевий графік представляє кумулятивний відсоток прибутку (тобто частку прибутку на кумулятивній основі).
- На умовній межі 80% зазвичай для наочності проводять порогову горизонтальну лінію. Всі товари ліворуч від точки перетину цієї лінії з графіком накопиченого прибутку приносять нам 80% грошей, всі товари праворуч – решта 20%.
Давайте подивимося, як самостійно побудувати діаграму Парето в Microsoft Excel.
Варіант 1. Проста діаграма Парето на основі готових даних
Якщо вихідні дані прийшли до вас у вигляді подібної таблиці (тобто вже в готовому вигляді):
… тоді ми робимо наступне.
Відсортуйте таблицю в порядку спадання прибутку (табл Дані – Сортування) і додайте стовпець з формулою для розрахунку накопиченого відсотка прибутку:
Ця формула ділить загальний накопичений прибуток від початку списку до поточного елемента на загальний прибуток для всієї таблиці. Ми також додаємо стовпець з константою 80%, щоб створити горизонтальну порогову пунктирну лінію на майбутньому графіку:
Вибираємо всі дані і будуємо звичайну гістограму на вкладці Вставити – Гістограма (Вставити – Стовпчаста діаграма). Це має виглядати приблизно так:
Відсоткові ряди в результуючому діаграмі повинні бути відправлені вздовж другорядної (правої) осі. Для цього потрібно виділяти рядки мишкою, але це може бути складно, оскільки їх погано видно на тлі великих стовпців прибутку. Тому для виділення краще використовувати розкривний список на вкладці макет or сформований:
Потім клацніть правою кнопкою миші на вибраному рядку та виберіть команду Форматувати ряд даних і у вікні, що з’явиться, виберіть опцію На вторинній осі (Secondary Axis). В результаті наша діаграма буде виглядати так:
Для ряду накопиченої частки прибутку та порогового значення вам потрібно змінити тип діаграми зі стовпців на лінії. Для цього клацніть кожен із цих рядків і виберіть команду Змінити тип діаграми серії.
Все, що залишилося, це вибрати горизонтальний рядок «Поріг» і відформатувати його так, щоб він виглядав як лінія обрізання, а не як дані (тобто видалити маркери, зробити лінію пунктирною червоною тощо). Все це можна зробити, клацнувши правою кнопкою миші на рядку та вибравши команду Форматувати ряд даних. Тепер діаграма набуде остаточного вигляду:
Відповідно до нього можна зробити висновок, що 80% прибутку приносять перші 5 товарів, а на всі інші товари праворуч від картоплі припадає лише 20% прибутку.
У Excel 2013 ви можете зробити це ще простіше – використовуйте новий вбудований комбінований тип діаграми одразу під час побудови:
Варіант 2: зведена таблиця та зведена діаграма Парето
Що робити, якщо немає готових даних для побудови, а є вихідна необроблена інформація? Припустімо, що на початку у нас є таблиця з такими даними про продажі:
Щоб побудувати на ній діаграму Парето та з’ясувати, які товари продаються найкраще, спочатку потрібно проаналізувати вихідні дані. Найпростіший спосіб зробити це за допомогою зведеної таблиці. Виберіть будь-яку клітинку у вихідній таблиці та скористайтеся командою Вставити – зведену таблицю (Вставити – зведену таблицю). У проміжному вікні, що з'явилося, нічого не змінюйте і натисніть OK, а потім на панелі, що з’явилася праворуч, перетягніть поля вихідних даних з верхньої в нижню області макета майбутньої зведеної таблиці:
У результаті має вийти зведена таблиця із загальним доходом для кожного продукту:
Відсортуйте його в порядку спадання доходу, встановивши активну клітинку для стовпця Сума в полі Дохід і за допомогою кнопки сортування Від Я до А (Від Я до А) таб дані.
Тепер нам потрібно додати обчислений стовпець із накопиченими процентними доходами. Для цього знову перетягніть поле Доходи до області Цінності на правій панелі, щоб отримати дублікат стовпця у зведеній частині. Потім клацніть правою кнопкою миші на клонованому стовпці та виберіть команду Додаткові обчислення – % поточної суми в полі (Показати дані як – % поточної суми в). У вікні, що з’явилося, виберіть поле ІМ'Я, на якому буде накопичуватися відсоток доходу зверху вниз. Результат має виглядати як ця таблиця:
Як бачите, це майже готова таблиця з першої частини статті. Для повного щастя не вистачає лише стовпчика з пороговим значенням 80% для побудови лінії відсікання на майбутній діаграмі. Такий стовпець можна легко додати за допомогою обчислюваного поля. Виділіть будь-яке число в підсумку, а потім натисніть вкладку Головна – Вставити – Обчислюване поле (Головна сторінка – Вставити – Обчислюване поле). У вікні, що відкрилося, введіть назву поля та його формулу (в нашому випадку константу):
Натиснувши на OK до таблиці буде додано третій стовпець зі значенням 80% у всіх комірках, який остаточно набуде потрібного вигляду. Тоді ви можете використовувати команду Зведена діаграма (Зведена діаграма) таб параметри (Параметри) or аналіз (Аналіз) і налаштуйте діаграму точно так само, як і перший варіант:
Виділення ключових продуктів
Щоб виділити найбільш впливові фактори, можна виділити стовпці, розташовані ліворуч від точки перетину помаранчевої кривої накопичених відсотків із горизонтальною лінією відсічення 80%. Для цього вам необхідно додати до таблиці ще один стовпець з формулою:
Ця формула виводить 1, якщо добуток знаходиться ліворуч від точки перетину, і 0, якщо вона знаходиться праворуч. Тоді потрібно зробити наступне:
- Додаємо в діаграму новий стовпець – найпростіше це зробити простим копіюванням, тобто виділяємо стовпець підсвічування, скопіюйте (Ctrl + C), виберіть діаграму та вставте (Ctrl + V).
- Виберіть доданий рядок і перемістіть його вздовж другорядної осі, як описано вище.
- Серія Тип діаграми підсвічування змінити на стовпці (гістограма).
- Прибираємо бічний зазор у властивостях рядка (правий клік по рядку Освітлення – Формат рядків – Бічний зазор) так, щоб колонки злилися в єдине ціле.
- Прибираємо межі стовпчиків, а заливку робимо напівпрозорою.
В результаті ми отримуємо таку приємну родзинку кращих продуктів:
PS
Починаючи з Excel 2016, до стандартного набору діаграм Excel додано діаграму Парето. Тепер, щоб створити його, просто виберіть діапазон і на вкладці Insert (Вставити) виберіть відповідний тип:
Один клік – і діаграма готова:
- Як побудувати звіт за допомогою зведеної таблиці
- Налаштування обчислень у зведених таблицях
- Що нового в діаграмах в Excel 2013
- Стаття у Вікіпедії про закон Парето