Як створити діаграму Парето в Excel

Цей підручник покаже, як створити діаграму Парето у всіх версіях Excel: 2007, 2010, 2013, 2016 та 2022 роки.

Діаграма Парето - це гібрид стовпчастої діаграми та лінійного діаграми, що вказує на відносну важливість факторів або елементів у даному наборі даних разом з їх сукупними відсотками. Своєю назвою діаграма зобов'язана принципу Парето, також відому як закон кількох життєво важливих, який стверджує, що приблизно 20% причин сприяють 80% наслідків.

Мета діаграми - розрізняти критичні фактори в наборі даних, відрізняючи їх від незначних.

Графік Парето став рідним типом діаграми в Excel 2016, але для користувачів Excel 2013 або старіших версій єдиний спосіб - це побудувати діаграму вручну з нуля.

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

Починаємо

Щоб показати вам мотузки, нам потрібні деякі необроблені дані для роботи. З цієї причини припустимо, що ви збираєтесь проаналізувати розбивку предметів, що повертаються до магазину одягу, намагаючись з’ясувати, які категорії товарів викликають найбільше неприємностей.

З огляду на це, врахуйте такі дані:

Отже, давайте зануримось прямо.

Як створити діаграму Парето в Excel 2016+

По-перше, дозвольте мені показати вам, як побудувати діаграму Парето за допомогою відповідного вбудованого типу діаграми Excel, представленого в Excel 2016.

За допомогою цього методу для налаштування діаграми потрібно всього кілька кліків, але ви втрачаєте деякі можливості налаштування-наприклад, ви не можете додати ні мітки даних, ні маркери до рядка Парето.

Отже, якщо вам потрібен додатковий рівень налаштування, має сенс побудувати діаграму з нуля. Але по -перше, ось алгоритм для тих, хто хоче пройти легший шлях.

Крок №1: Побудуйте діаграму Парето.

Знову ж таки, якщо ви використовуєте Excel 2016 або пізнішу версію, Excel дозволяє створити просту діаграму Парето, ледь піднявши палець:

1. Виділіть свої фактичні дані (A1: B11).
2. Перейдіть до Вставити вкладка.
3. Натисніть «Вставити статистичну діаграму.”
4. Виберіть "Парето.”

Чарівним чином відразу з’явиться діаграма Парето:

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

Крок 2: Додайте мітки даних.

Почніть з додавання міток даних до діаграми. Клацніть правою кнопкою миші будь-який стовпець і виберіть "Додати мітки даних.”

Налаштуйте колір, шрифт та розмір міток, щоб вони виділялися (Додому > Шрифт).

Крок №3: Додайте заголовки осей.

В якості «черешки» назви осей надають додатковий контекст для того, що являє собою діаграма.

1. Виберіть область діаграми.
2. Перейдіть до Дизайн вкладка.
3. Натисніть кнопку "Додати елемент діаграми”.
4. Виберіть "Назви осі»Зі спадного меню.
5. Виберіть обидва "Первинна горизонтальна”Та“Основна вертикаль.”

Крок №4: Додайте останні штрихи.

Оскільки ми продовжуємо налаштовувати діаграму, тепер ви можете видалити лінії сітки, які взагалі не мають ніякої практичної мети. Клацніть правою кнопкою миші на лініях сітки та натисніть «Видалити”, Щоб стерти їх.

Нарешті, змініть назви осей і діаграм, і оновлена ​​діаграма Парето готова!

Як створити діаграму Парето в Excel 2007, 2010 та 2013

Це було досить швидко. Однак користувачам версій до Excel 2016 не залишається нічого іншого, як виконати всю роботу самостійно. Але є срібна підкладка: таким чином, ви отримаєте набагато більше місця для налаштувань.

Незважаючи на те, що всі скріншоти були зроблені в Excel 2022, метод, який ви збираєтесь вивчити, залишається сумісним з версіями до Excel 2007. Просто дотримуйтесь і налаштуйте відповідним чином.

Крок №1: Сортуйте дані за спаданням.

Для початку відсортуйте дані за кількістю повернутих елементів (стовпець В) у порядку спадання.

1. Виділіть свої фактичні дані (A1: B11).
2. Перейдіть до Дані вкладка.
3. Натисніть кнопку «Сортувати”.
4. В Сортувати діалогове вікно, виконайте наведені нижче дії.
   1. За “Колонка", Виберіть"Повернуті товари.”
   2. За “Сортувати по, "Вибрати"Значення клітинки.”
   3. За “Замовлення, "Вибрати"Від найбільшого до найменшого.”

Крок №2: Обчисліть сукупні відсотки.

Наш наступний крок - створити допоміжний стовпець з назвою “Сукупний %» який додасть відносні частки категорій продуктів у наборі даних, які будуть використовуватися як значення для складання діаграми лінії Парето.

Щоб знайти значення, введіть таку формулу C2 і скопіюйте його вниз, перетягнувши маркер у нижньому правому куті комірки вниз до комірки C11:

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

Не забудьте відформатувати вихідну формулу у відсотках (Додому > Група номерів > Процентний стиль).

Крок 3: Створіть кластерну діаграму стовпців.

Після належного упорядкування даних діаграми побудуйте просту кластерну діаграму стовпців.

1. Виділіть усі дані діаграми (A1: C11).
2. Перейдіть до Вставити вкладка.
3. Натисніть «Вставити стовпчасту або стовпчасту діаграму.”
4. Виберіть "Кластерні колонки.”

Потрапивши туди, майбутній графік Парето повинен виглядати так:

Крок №4: Створіть комбіновану діаграму.

Настав час переглянути ряд даних, що ілюструє сукупні відсотки (Серія «Сукупний %») у лінійну діаграму.

Клацніть правою кнопкою миші на будь-якій крихітній помаранчевій смужці, що представляє Серія «Сукупний %» і виберіть "Змінити тип діаграми серії»З меню, що з'явиться.

В Змінити тип діаграми діалогове вікно, перетворіть кластерну діаграму на комбіновану діаграму:

1. Перейдіть до Комбо вкладка.
2. За Серія "Сукупний %", змінити "Тип діаграми"До"Лінія з маркерами"І перевірте"Вторинна вісь”Коробка.

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

Після цього ваша комбінована діаграма має виглядати так:

Крок #5: Налаштуйте вторинну шкалу вертикальної осі.

У діаграмі Парето значення лінійної діаграми ніколи не можуть перевищувати сто відсотків, тому давайте відповідно відрегулюємо діапазони вторинних вертикальних осей.

Клацніть правою кнопкою миші на вторинній вертикальній осі (цифри вздовж правої сторони) і виберіть “Формат осі.”

Одного разу Формат осі з'явиться панель завдань, виконайте наведені нижче дії.

1. Перейдіть до Параметри осі
2. Змінити Максимальні межі до “1.”

Крок #6: Змініть ширину зазору стовпців.

Зробіть стовпці ширшими, змінивши ширину зазору відповідного ряду даних (Серія «Повернуті товари»).

Для цього клацніть правою кнопкою миші будь-який із синіх стовпців, що представляє Серія «Повернуті товари» і натисніть «Форматувати серію даних.”

Потім виконайте кілька простих кроків:

1. Перейдіть до Опції серії вкладка.
2. Змінити "Ширина зазору"До"3%.”

Крок #7: Додайте мітки даних

Настав час додати мітки даних для обох рядів даних (Клацніть правою кнопкою миші > Додати мітки даних).

Як ви, можливо, помітили, мітки для лінії Парето виглядають трохи безладно, тому давайте розмістимо їх прямо над лінією. Щоб змінити розташування міток, клацніть правою кнопкою миші мітки даних для Серія «Сукупний %» і виберіть "Форматувати мітки даних.”

В Форматувати мітки даних панелі завдань, змініть положення міток, виконавши наступні дії:

1. Перейдіть до Параметри міток вкладка.
2. Під "Позиція етикетки,"Вибрати"Вище.”

Ви також можете змінити колір і розмір етикеток, щоб відрізняти їх один від одного.

Крок #8: Очистіть діаграму.

Нарешті, коли ми закінчимо полірувати графік, не забудьте позбутися легенди діаграми та ліній сітки. Не забудьте додати заголовки осей, виконавши кроки, показані раніше (Дизайн > Додати елемент діаграми > Назви осі > Первинна горизонтальна + Основна вертикаль).

Ваша розширена діаграма Pareto готова!