Hvordan lage et Pareto -diagram i Excel (statisk og interaktiv)

Se video - Hvordan lage et Pareto -diagram i Excel

Pareto Chart er basert på Pareto-prinsippet (også kjent som 80/20-regelen), som er et velkjent konsept innen prosjektledelse.

I henhold til dette prinsippet kan ~ 80% av problemene tilskrives omtrent ~ 20% av problemene (eller ~ 80% av resultatene dine kan være et direkte resultat av ~ 20% av din innsats, og så videre …).

80/20 prosentverdien kan variere, men tanken er at av alle problemene/innsatsen er det noen få som resulterer i maksimal innvirkning.

Dette er et mye brukt konsept i prosjektledelse for å prioritere arbeid.

Opprette et Pareto -diagram i Excel

I denne opplæringen vil jeg vise deg hvordan du lager en:

• Enkelt (statisk) Pareto -diagram i Excel.
• Dynamisk (interaktivt) Pareto -diagram i Excel.

Å lage et Pareto -diagram i Excel er veldig enkelt.

All lureri er skjult i hvordan du ordner dataene i backend.

La oss ta et eksempel på et hotell der klagedataene kan se noe ut som vist nedenfor:

MERK: For å lage et Pareto -diagram i Excel, må du ha dataene ordnet i synkende rekkefølge.

Opprette et enkelt (statisk) Pareto -diagram i Excel

Her er trinnene for å lage et Pareto -diagram i Excel:

1. Sett opp dataene dine som vist nedenfor.
2. Beregn kumulativ % i kolonne C. Bruk følgende formel: = SUM ($ B $ 2: B2)/SUM ($ B $ 2: $ B $ 1)
3. Velg hele datasettet (A1: C10), gå til Sett inn -> Diagrammer -> 2 -D -kolonne -> Gruppert kolonne. Dette setter inn et kolonnediagram med 2 dataserier (antall klager og den kumulative prosentandelen).
4. Høyreklikk på en av stolpene og velg Endre seriekart.
5. Velg Kombinasjon i dialogboksen Endre diagramtype i venstre rute.
6. Gjør følgende endringer:
   • Antall klager: gruppert kolonne.
   • Kumulativ %: Linje (merk også av for Sekundær akse).[Hvis du bruker Excel 2010 eller 2007, vil det være et totrinns prosess. Endre først diagramtypen til et linjediagram. Høyreklikk deretter på linjediagrammet og velg Format Data Series og velg Secondary Axis in Series Options]
7. Pareto -diagrammet i Excel er klart. Juster de vertikale aksens verdier og diagramtittelen.

Hvordan tolke dette Pareto -diagrammet i Excel

Dette Pareto -diagrammet fremhever de viktigste problemene hotellet bør fokusere på for å sortere maksimalt antall klager. For eksempel vil målretting av de tre første problemene automatisk ta seg av ~ 80% av klagene.

For eksempel vil målretting mot de tre første problemene automatisk ta seg av ~ 80% av klagene.

Opprette et dynamisk (interaktivt) Pareto -diagram i Excel

Nå som vi har et statisk/enkelt Pareto -diagram i Excel, la oss ta det et skritt videre og gjøre det litt interaktivt.

Noe som vist nedenfor:

I dette tilfellet kan en bruker spesifisere prosentandelen klager som må håndteres (ved hjelp av Excel -rullefeltet), og diagrammet vil automatisk markere problemene som bør undersøkes.

Tanken her er å ha 2 forskjellige barer.

Den røde er uthevet når den kumulative prosentverdien er nær målverdien.

Her er trinnene for å lage dette interaktive Pareto -diagrammet i Excel:

1. I celle B14 har jeg målverdien som er knyttet til rullefeltet (hvis verdi varierer fra 0 til 100).
2. I celle B12 har jeg brukt formelen = B14/100. Siden du ikke kan spesifisere en prosentverdi til en rullefelt, deler vi ganske enkelt rullefeltverdien (i B14) med 100 for å få prosentverdien.
3. I celle B13 skriver du inn følgende kombinasjon av INDEX-, MATCH- og IFERROR -funksjoner:
   = IFERROR (INDEX ($ C $ 2: $ C $ 10, IFERROR (MATCH ($ B $ 12, $ C $ 2: $ C $ 10,1), 0) +1), 1)
   Denne formelen returnerer den kumulative verdien som dekker målverdien. For eksempel, hvis du har målverdien som 70%, vil den returnere 77%, noe som indikerer at du bør prøve å løse de tre første problemene.

4. Skriv inn følgende formel i celle D2 (og dra eller kopier for hele cellen - D2: D10):
   = HVIS ($ B $ 13> = C2, B2, NA ())
5. Skriv inn følgende formel i celle E2 (og dra eller kopier for hele cellen - E2: E10):
   = HVIS ($ B $ 13<>
6. Velg dataene i kolonne A, C, D & E (trykk på kontroll og velg med musen).
7. Gå til Sett inn -> Diagrammer -> 2 -D -kolonne -> Gruppert kolonne. Dette vil sette inn kolonnediagram med 3 serier med data (kumulativ prosentandel, stolpene som skal markeres for å nå målet, og resten av andre søyler)
8. Høyreklikk på en av stolpene og velg Endre seriekart.
9. I dialogboksen Endre diagramtype velger du Kombinasjon i venstre rute og gjør følgende endringer:
   • Kumulativ %: Linje (sjekk også avkrysningsboksen Sekundær akse).
   • Uthevede stolper: Gruppert kolonne.
   • Gjenværende stolper: Gruppert kolonne.
10. Høyreklikk på en av de markerte stolpene og endre fargen til rød.

Det er det!

Du har opprettet et interaktivt Pareto -diagram i Excel.

Når du endrer målet ved hjelp av rullefeltet, vil Pareto -diagrammet oppdatere deretter.

Bruker du Pareto -diagrammet i Excel?

Jeg vil gjerne høre tankene dine om denne teknikken og hvordan du har brukt den. Legg igjen dine fotavtrykk i kommentarfeltet 🙂

• Analyserer restaurantklager ved hjelp av Pareto -diagram.
• Opprette et Gantt -diagram i Excel.
• Opprette et milepældiagram i Excel.
• Opprette et histogram i Excel.
• Kalkulatormal for Excel -timeliste.
• Employee Leave Tracker -mal.
• Beregning av veid gjennomsnitt i Excel.
• Opprette en Bell Curve i Excel.
• Avanserte Excel -diagrammer
• Hvordan legge til en sekundær akse i Excel -diagrammer.