Гледайте видео - Как да направите диаграма на Парето в Excel
Pareto Chart се основава на принципа на Pareto (известен още като правило 80/20), който е добре позната концепция в управлението на проекти.
Според този принцип ~ 80% от проблемите могат да бъдат приписани на около 20% от проблемите (или ~ 80% от вашите резултати могат да бъдат пряк резултат от ~ 20% от вашите усилия и т.н. …).
Процентната стойност 80/20 може да варира, но идеята е, че от всички проблеми/усилия има няколко, които водят до максимално въздействие.
Това е широко използвана концепция в управлението на проекти за приоритизиране на работата.
Създаване на диаграма на Парето в Excel
В този урок ще ви покажа как да направите:
- Проста (статична) диаграма на Парето в Excel.
- Динамична (интерактивна) диаграма на Парето в Excel.
Създаването на диаграма на Pareto в Excel е много лесно.
Цялата хитрост е скрита в това как подреждате данните в бекенда.
Нека вземем пример за хотел, за който данните за жалбите биха могли да изглеждат както е показано по -долу:
ЗАБЕЛЕЖКА: За да направите диаграма на Pareto в Excel, трябва да подредите данните в низходящ ред.
Създаване на проста (статична) диаграма на Парето в Excel
Ето стъпките за създаване на диаграма на Pareto в Excel:
- Настройте данните си, както е показано по -долу.
- Изчислете кумулативния % в колона C. Използвайте следната формула: = SUM ($ B $ 2: B2)/SUM ($ B $ 2: $ B $ 1)
- Изберете целия набор от данни (A1: C10), отидете на Insert -> Charts -> 2 -D Column -> Clustered Column. Това вмъква колонна диаграма с 2 серии данни (# жалби и кумулативен процент).
- Щракнете с десния бутон върху някоя от лентите и изберете Промяна на типа серия диаграма.
- В диалоговия прозорец Промяна на типа диаграма изберете Комбо в левия прозорец.
- Направете следните промени:
- Брой на жалбите: Клъстерна колона.
- Кумулативен %: Линия (също поставете отметка в квадратчето Вторична ос).
[Ако използвате Excel 2010 или 2007, това ще бъде две стъпки процес. Първо променете типа диаграма на линейна диаграма. След това щракнете с десния бутон върху линейната диаграма и изберете Форматиране на серия от данни и изберете Вторична ос в Опции за серия]
- Вашата диаграма на Pareto в Excel е готова. Регулирайте стойностите на вертикалната ос и заглавието на диаграмата.
Как да тълкуваме тази диаграма на Парето в Excel
Тази диаграма на Pareto подчертава основните проблеми, върху които хотелът трябва да се съсредоточи, за да сортира максималния брой жалби. Например, насочването към първите 3 въпроса автоматично ще се погрижи за ~ 80% от жалбите.
Например, насочването към първите 3 въпроса автоматично ще се погрижи за ~ 80% от жалбите.
Създаване на динамична (интерактивна) диаграма на Парето в Excel
Сега, когато имаме статична/проста диаграма на Парето в Excel, нека да направим крачка напред и да я направим малко интерактивна.
Нещо, както е показано по -долу:
В този случай потребителят може да посочи % от жалбите, които трябва да бъдат разгледани (използвайки лентата за превъртане на Excel), а диаграмата автоматично ще подчертае проблемите, които трябва да бъдат разгледани.
Идеята тук е да има 2 различни бара.
Червеното се подчертава, когато кумулативната процентна стойност е близо до целевата стойност.
Ето стъпките, за да направите тази интерактивна диаграма на Pareto в Excel:
- В клетка В14 имам целевата стойност, която е свързана с лентата за превъртане (чиято стойност варира от 0 до 100).
- В клетка В12 използвах формулата = В14/100. Тъй като не можете да посочите процентна стойност на лентата за превъртане, ние просто разделяме стойността на лентата за превъртане (в B14) със 100, за да получим процентната стойност.
- В клетка B13 въведете следната комбинация от функции INDEX, MATCH и IFERROR:
= IFERROR (INDEX ($ C $ 2: $ C $ 10, IFERROR (MATCH ($ 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 серии данни (кумулативен процент, лентите, които трябва да бъдат подчертани, за да постигнат целта, и останалите всички други ленти)
- Щракнете с десния бутон върху някоя от лентите и изберете Промяна на типа серия диаграма.
- В диалоговия прозорец Промяна на типа диаграма изберете Комбо в левия панел и направете следните промени:
- Кумулативен %: Ред (също така поставете отметка в квадратчето Вторична ос).
- Маркирани ленти: Клъстерна колона.
- Останали ленти: Клъстерна колона.
- Щракнете с десния бутон върху някоя от маркираните ленти и променете цвета на Червен.
Това е!
Създали сте интерактивна диаграма на Pareto в Excel.
Сега, когато промените целта с помощта на лентата за превъртане, диаграмата на Pareto ще се актуализира съответно.
Използвате ли диаграма Pareto в Excel?
Ще се радвам да чуя вашите мисли за тази техника и как сте я използвали. Оставете следите си в секцията за коментари 🙂
- Анализ на оплакванията от ресторанти с помощта на диаграма Pareto.
- Създаване на диаграма на Гант в Excel.
- Създаване на диаграма на етапа в Excel.
- Създаване на хистограма в Excel.
- Шаблон за калкулатор на график на Excel.
- Шаблон за проследяване на напускане на служители.
- Изчисляване на среднопретеглена стойност в Excel.
- Създаване на крива на звънец в Excel.
- Разширени диаграми на Excel
- Как да добавите вторична ос в диаграми на Excel.
