Crearea unei diagrame Pareto în Excel (statică și dinamică)

Vizionați videoclipul – Cum să creați o Diagrama Pareto în Excel

Diagrama Pareto se bazează pe principiul Pareto (cunoscut și ca regula 80/20), care este un concept bine cunoscut în managementul de proiect.

80 Valoarea procentuală de 20 poate varia, dar ideea este că dintre toate problemele / eforturile, există câteva care au ca rezultat un impact maxim.

Acesta este un concept utilizat pe scară largă în managementul de proiect pentru a prioritiza munca.

Crearea unui diagramă Pareto în Excel

În acest tutorial, vă voi arăta cum să faceți un:

• Diagrama Pareto simplă (statică) în Excel.
• Diagrama Pareto dinamică (interactivă) în Excel.

Crearea unei diagrame Pareto în Excel este foarte ușoară.

Toate trucurile sunt ascunse în cum aranjați datele în backend.

Să luăm un exemplu de hotel pentru care datele despre reclamații ar putea arăta ceva așa cum se arată mai jos:

NOTĂ: Pentru a crea o diagramă Pareto în Excel, trebuie să aveți datele aranjate în ordine descrescătoare.

Descărcați șablonul de diagrame Excel Pareto

Crearea unei diagrame Pareto simple (statice) în Excel

Iată pași pentru a crea o diagramă Pareto în Excel:

1. Configurați datele așa cum se arată mai jos.
2. Calculați% cumulat în coloana C. Folosiți următoarea formulă: = SUM ($ B $ 2: B2) / SUM ($ B $ 2: $ B $ 1 )
3. Selectați întregul set de date (A1: C10), accesați Insert – > Diagramele – > Coloana 2-D – > Coloana grupată. Aceasta introduce o diagramă coloană cu 2 serii de date (numărul de reclamații și procentul cumulativ).
4. Faceți clic dreapta pe oricare dintre bare și selectați Modificare Tip diagramă serie.
5. În caseta de dialog Modificare tip diagramă, selectați Combo în panoul din stânga.
6. Efectuați următoarele modificări:
   • # de reclamații: coloană grupată.
   • % cumulativ: linie (bifați și caseta de selectare Axa secundară).
7. Diagrama dvs. Pareto în Excel este gata. Ajustați valorile axei verticale și titlul diagramei.

Cum se interpretează această diagramă Pareto în Excel

Acest Pareto graficul evidențiază problemele majore pe care hotelul ar trebui să se concentreze pentru a sorta numărul maxim de reclamații. De exemplu, direcționarea primelor 3 probleme ar avea automat grijă de ~ 80% din reclamații.

De exemplu, direcționarea primelor 3 probleme ar avea automat grijă de ~ 80% din reclamații.

Crearea unei diagrame dinamice (interactive) Pareto în Excel

Acum că avem o diagramă Pareto statică / simplă în Excel, hai să facem un pas mai departe și să o facem puțin interactivă.

Ceva așa cum se arată mai jos:

În acest caz, un utilizator poate specifica procentul de reclamații care trebuie abordate (utilizând defilarea excel bară), iar graficul va evidenția automat problemele care ar trebui analizate.

Ideea aici este de a avea 2 bare diferite.

Cea roșie este evidențiată când procentul cumulativ valoarea este apropiată de valoarea țintă.

Iată pașii pentru a crea această diagramă interactivă Pareto în Excel:

1. În celula B14, am valoarea țintă care este legată la bara de derulare (a cărei valoare variază de la 0 la 100).
2. În celula B12, am folosit formula = B14 / 100. Deoarece nu puteți specifica o valoare procentuală pentru o bară de defilare, pur și simplu împărțim valoarea barei de defilare (în B14) cu 100 pentru a obține valoarea procentuală.
3. În celula B13, introduceți următoarea combinație de funcții INDEX, MATCH și IFERROR:
   = IFERROR (INDEX ($ C $ 2: $ C $ 10, IFERROR (MATCH ($ B $ 12, $ C $ 2: $ C $ 10,1 ), 0) +1), 1)
   Această formulă returnează valoarea cumulativă care ar acoperi valoarea țintă. De exemplu, dacă aveți valoarea țintă de 70%, aceasta va întoarce 77%, indicând faptul că ar trebui să încercați să rezolvați primele trei probleme.

1. În celula D2, introduceți următoarea formulă (și trageți sau copiați pentru toate celulele – D2: D10):
   = IF ($ B $ 13 > = C2, B2, NA ())
2. În celula E2 introduceți următoarea formulă (și trageți sau copiați pentru toate celulele – E2: E10):
   = IF ( $ B $ 13 < C2, B2, NA ())
3. Selectați datele din coloana A, C, D & E (apăsați butonul de control și selectați cu mouse-ul).
4. Accesați Insert – > Diagrame – > Coloană 2-D – > Coloană grupată.Aceasta va insera graficul de coloane cu 3 serii de date (procentul cumulativ, barele care vor fi evidențiate pentru a atinge ținta și rămânând toate celelalte bare)
5. Faceți clic dreapta pe oricare dintre bare și selectați Schimbați graficul seriei Tastați.
6. În caseta de dialog Modificare tip diagramă, selectați Combo în panoul din stânga și efectuați următoarele modificări:
   • % cumulativ: linie (bifați și caseta de selectare Axa secundară).
   • Bare evidențiate: coloană grupată.
   • Bare rămase: coloană grupată.
7. Faceți clic dreapta pe oricare dintre barele evidențiate și schimbați culoarea în roșu.

Asta e!

Ați creat o diagramă interactivă Pareto în Excel.

Acum, când schimbați țintă utilizând bara de defilare, diagrama Pareto se va actualiza în consecință.

Descărcați șablonul de diagrame Excel Pareto

Folosiți diagrama Pareto în Excel?

Mi-ar plăcea să vă aud gândurile despre această tehnică și despre cum ați l-am folosit. Lăsați-vă amprentele în secțiunea de comentarii 🙂

Managementul proiectelor și tutoriale legate de diagramă:

• Analizarea reclamațiilor restaurantelor utilizând diagrama Pareto.
• Crearea unui Gantt Diagramă în Excel.
• Crearea unei diagrame Milestone în Excel.
• Crearea unei histograme în Excel.
• Șablon de calcul pentru foaia de timp Excel.
• Șablon de urmărire a concediilor pentru angajați.
• Calculul mediei ponderate în Excel.
• Crearea unei curbe Bell în Excel.
• Diagramele avansate Excel
• Cum pentru a adăuga o axă secundară în diagrame Excel.