Vizionați video - Cum să creați o diagramă Pareto în Excel
Diagrama Pareto se bazează pe principiul Pareto (cunoscut și ca regula 80/20), care este un concept bine cunoscut în managementul proiectelor.
Conform acestui principiu, ~ 80% din probleme pot fi atribuite aproximativ ~ 20% din probleme (sau ~ 80% din rezultatele dvs. ar putea fi un rezultat direct de ~ 20% din eforturile dvs. și așa mai departe …).
Valoarea procentuală 80/20 poate varia, dar ideea este aceea 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 unei diagrame Pareto în Excel
În acest tutorial, vă voi arăta cum să realizați un:
- Diagramă simplă (statică) Pareto în Excel.
- Diagrama dinamică (interactivă) Pareto în Excel.
Crearea unei diagrame Pareto în Excel este foarte ușoară.
Toate șmecheriile sunt ascunse în modul în care aranjați datele în backend.
Să luăm un exemplu de hotel pentru care datele despre reclamații ar putea arăta 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.
Crearea unui diagramă Pareto simplă (statică) în Excel
Iată pașii pentru a crea o diagramă Pareto în Excel:
- Configurați datele așa cum se arată mai jos.
- Calculați% cumulativ în coloana C. Folosiți următoarea formulă: = SUM ($ B $ 2: B2) / SUM ($ B $ 2: $ B $ 1)
- Selectați întregul set de date (A1: C10), accesați Insert -> Charts -> 2-D Column -> Clustered Column. Aceasta introduce o diagramă coloană cu 2 serii de date (# de reclamații și procentul cumulativ).
- Faceți clic dreapta pe oricare dintre bare și selectați Modificare tip grafic serie.
- În caseta de dialog Modificare tip diagramă, selectați Combo în panoul din stânga.
- Efectuați următoarele modificări:
- # de reclamații: coloană grupată.
- % Cumulativ: linie (bifați și caseta de selectare Axa secundară).
[Dacă utilizați Excel 2010 sau 2007, acesta va fi în doi pași proces. Mai întâi, schimbați tipul graficului într-un grafic liniar. Apoi faceți clic dreapta pe diagrama liniară și selectați Formatare serie de date și selectați Axa secundară în Opțiuni serie]
- Diagrama dvs. Pareto din Excel este gata. Reglați valorile axei verticale și titlul diagramei.
Cum se interpretează această diagramă Pareto în Excel
Această diagramă Pareto evidențiază problemele majore pe care hotelul ar trebui să se concentreze pentru a sorta numărul maxim de reclamații. De exemplu, vizarea primelor 3 probleme ar avea automat grijă de ~ 80% din reclamații.
De exemplu, vizarea 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, să mergem mai departe și să o facem un pic interactivă.
Ceva așa cum se arată mai jos:
În acest caz, un utilizator poate specifica procentul de reclamații care trebuie abordate (folosind bara de derulare Excel), 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ă atunci când valoarea procentuală cumulată este apropiată de valoarea țintă.
Iată pașii pentru a crea această diagramă interactivă Pareto în Excel:
- În celula B14, am valoarea țintă care este legată de bara de derulare (a cărei valoare variază de la 0 la 100).
- În celula B12, am folosit formula = B14 / 100. Deoarece nu puteți specifica o valoare procentuală pentru o bară de derulare, împărțim pur și simplu valoarea barei de derulare (în B14) cu 100 pentru a obține valoarea procentuală.
- Î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 că ar trebui să încercați să rezolvați primele trei probleme.
- Î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 ()) - În celula E2 introduceți următoarea formulă (și trageți sau copiați pentru toate celulele - E2: E10):
= IF ($ B $ 13<> - Selectați datele din coloanele A, C, D & E (apăsați butonul control și selectați cu mouse-ul).
- Accesați Inserare -> 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)
- Faceți clic dreapta pe oricare dintre bare și selectați Modificare tip grafic serie.
- Î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ă.
- 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 ținta folosind bara de derulare, diagrama Pareto se va actualiza în consecință.
Folosiți diagrama Pareto în Excel?
Mi-ar plăcea să vă aud gândurile despre această tehnică și despre modul în care ați folosit-o. Nu lăsați amprentele în secțiunea de comentarii 🙂
- Analizarea reclamațiilor la restaurant folosind diagrama Pareto.
- Crearea unei diagrame Gantt în Excel.
- Crearea unei diagrame Milestone în Excel.
- Crearea unei histograme în Excel.
- Șablon Calculator Excel Foaie de timp.
- Șablon de urmărire a concediilor pentru angajați.
- Calculul mediei ponderate în Excel.
- Crearea unei curbe de clopot în Excel.
- Diagramele avansate Excel
- Cum se adaugă o axă secundară în diagrame Excel.
