У Excel 2016 розробники додали кілька нових діаграм. Одна з них – гістограма Парето, яка відбиває графічне зображення відомого принципу Парето чи закону 20/80. Насправді цей спосіб досить добре прижився, оскільки його простота, і водночас ефективність, приносять непогані плоди.
Діаграма (крива) Парето містить як стовпці, відсортовані за спаданням, так і лінію, що означає загальний сукупний відсоток. За допомогою діаграми закону Парето в бізнес-аналізі наочно показують, наприклад, які товари або клієнти приносять найбільший прибуток. З її допомогою можна легко побачити найпоширеніші проблеми.
Діаграма Парето в Excel 2013 та ранніх версіях
Не в усіх користувачів встановлено новітню версію Excel. Розглянемо, як побудувати діаграму Парето в Excel 2013 і попередніх версіях на конкретному прикладі. Є таблиця, що містить прибуток за товарами.
Для побудови діаграми потрібно:
- Відсортувати дані за спаданням у стовпі «Прибуток», щоб значення, що мають найбільший внесок, були на початку списку. Можна ознайомитись з матеріалом, як виконати сортування даних в Excel.
- Розрахувати стовпець з накопиченими частками за такою формулою:
=SUM($B$2:B2)/SUM($B$2:$B$10)
- Виділити всі дані та на вкладці «Вставлення», у розділі діаграми, будуємо звичайну гістограму. Легенду можна видалити або розмістити у зручному місці.
- Перенести ряди із відсотками на допоміжну вісь.
Виділити ці ряди на отриманій діаграмі за допомогою миші складно, тому що їх погано видно на тлі великих стовпців прибутку. Тому, щоб виділити прибуток, потрібно виділити діаграму, і на вкладці «Формат» у розділі «Поточний виділений фрагмент» вибрати потрібні ряди.
- Клацнути по виділеному ряду правою кнопкою миші та вибрати команду «Формат ряду даних…» і у вікні вибрати опцію «По допоміжній осі».
- Змінити тип діаграми зі стовпців на лінію. Для цього натисніть на цьому ряду правою кнопкою миші і виберіть команду «Змінити тип діаграми для рядів…». Як новий тип діаграми для ряду «Поріг» вибрати Точкова, а для «Накопичений прибуток» – Точкова діаграма з плавними лініями.
- Відформатувати ряди «Прибуток» та «Накопичений прибуток» так, щоб вони стали схожими на лінію відсічення, а не на дані. Для цього можна змінити маркери, зробити лінію червоною пунктирною тощо. Всі ці операції виконуються, якщо клацнути правою кнопкою миші по ряду і вибравши команду «Формат ряду даних…».
Основні особливості діаграми:
- Кожен синій стовпець гістограми є прибуток за товаром в абсолютних величинах.
- Помаранчевий графік є накопичений відсоток прибутку (тобто частку прибутку зростальним підсумком).
- На умовному кордоні 80% вказана порогова горизонтальна лінія. Всі товари лівіше точки перетину цієї лінії з графіком накопиченого прибутку – приносять 80% грошей, всі товари правіше – 20%.
Побудова діаграми Парето в Excel 2016 та вище
Загалом побудови діаграми Парето вказаним способом не викликає труднощів. Однак при оновленні даних, як мінімум сортування даних доведеться провести заново. Можливо, доведеться налаштовувати нові діапазони та формули, якщо кількість даних змінилася.
У Excel 2016 більше не потрібно сортувати дані, розраховувати окремий стовпець із накопиченими частками та колупатися в налаштуваннях. Достатньо мати вихідні дані, щоб у пару натискань мишкою побудувати гістограму Парето.
Допустимо, аналізується дохідність груп товарів, розташованих за абеткою.
Зобразимо на основі цих даних гістограму Парето в Excel 2016. Спочатку активуємо будь-яку клітинку у вихідних даних. Потім на стрічці «Вставлення» через рекомендовані діаграми або безпосередньо виберемо гістограму Парето.
За замовчуванням отримаємо наступний вид діаграми Парето.
Як бачимо процес створення набагато легший і швидший. Нам не довелося робити сортування товарів у таблиці, на діаграмі вони автоматично розташувалися у порядку максимального прибутку. Залишилося підібрати дизайн діаграми через меню «Конструктор» у розділі «Стилі діаграм» або налаштувати окремі її елементи через контекстне меню.
Зручність цієї діаграми у тому, що вона залежить від порядку розташування вихідних даних. Найважливіші елементи завжди будуть на першому місці. Тож якщо дані оновити, то все працюватиме, як треба. Дані для діаграм найкраще зберігати як Таблиця. Це дозволить діаграмі автоматично підлаштовуватись під розмір діапазону.
А ви вже будували діаграму Парето, якщо так, то в якій версії Ексель і які труднощі у вас виникли. Поділіться своїм досвідом у коментарях чи оцініть матеріал.
