Opprette et pareto-diagram i Excel (statisk og dynamisk)

Se video – Hvordan lage en Pareto Chart i Excel

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

The 80 / 20 prosentverdi kan variere, men ideen er at av alle problemene / innsatsene er det noen få som gir 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 et:

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

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

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

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

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

Last ned Excel Pareto-diagrammalen

Opprette et enkelt (statisk) Pareto-diagram i Excel

Her er trinn 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 – > Clustered Column. Dette setter inn et kolonnediagram med to serier med data (antall klager og den kumulerte prosentandelen).
4. Høyreklikk på en av stolpene og velg Endre Seriekarttype.
5. I dialogboksen Endre diagramtype velger du Kombi i venstre rute.
6. Gjør følgende endringer:
   • Antall klager: Clustered Column.
   • Kumulativ%: Linje (sjekk også avkrysningsruten Secondary Axis).
7. Pareto-diagrammet ditt i Excel er klart. Juster vertikale akseverdier og diagramtittel.

Slik tolker du dette Pareto-diagrammet i Excel

Denne pareto Diagrammet fremhever de viktigste problemene som hotellet bør fokusere på for å sortere maksimalt antall klager. For eksempel vil målretting mot de tre første utgavene automatisk ta seg av ~ 80% av klagene.

For eksempel vil målretting mot de tre første utgavene 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% av klager som må håndteres (ved hjelp av excel-rulle bar), og diagrammet fremhever automatisk problemene som skal undersøkes.

Tanken her er å ha to forskjellige søyler.

Den røde er uthevet når den kumulative prosentandelen verdien 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 koblet 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 bare rullefeltverdien (i B14) med 100 for å få prosentverdien.
3. I celle B13 skriver du inn følgende kombinasjon av INDEKS-, MATCH- og IFERROR-funksjoner:
   = FEIL (INDEKS ($ C $ 2: $ C $ 10, FEIL (MATCH ($ B $ 12, $ C $ 2: $ C $ 10,1) ), 0) +1), 1)
   Denne formelen returnerer den kumulative verdien som dekker målverdien. Hvis du for eksempel har målverdien 70%, vil den returnere 77%, noe som indikerer at du bør prøve å løse de tre første problemene.

1. I celle D2 skriver du inn følgende formel (og drar eller kopierer for alle celler – D2: D10):
   = IF ($ B $ 13 > = C2, B2, NA ())
2. I celle E2 skriver du inn følgende formel (og drar eller kopier for alle celler – E2: E10):
   = IF ( $ B $ 13 < C2, B2, NA ())
3. Velg dataene i kolonne A, C, D & E (trykk på kontrollen og velg med musen).
4. Gå til Sett inn – > Diagrammer – > 2-D-kolonne – > Klyngekolonne.Dette vil sette inn kolonnediagram med 3 serier med data (kumulativ prosentandel, stolpene som skal utheves for å oppfylle målet, og gjenværende alle andre søyler)
5. Høyreklikk på hvilken som helst av stolpene og velg Endre seriediagram Type.
6. I dialogboksen Endre diagramtype velger du Kombi i venstre rute og gjør følgende endringer:
   • Kumulativ%: Linje (sjekk også avkrysningsruten Sekundær akse).
   • Fremhevede søyler: Klyngekolonne.
   • Gjenværende søyler: Klyngekolonne.
7. 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å, når du endrer målet ved hjelp av rullefeltet, ville Pareto-diagrammet oppdateres tilsvarende.

Last ned Excel Pareto-diagrammalen

Bruker du Pareto-diagrammet i Excel?

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

Relatert prosjektledelse og kartopplæring:

• Analyser restaurantklager ved hjelp av Pareto-kart.
• Opprette en Gantt Diagram i Excel.
• Opprette et milepælskart i Excel.
• Opprette et histogram i Excel.
• Excel-kalkulatormal for timeliste.
• Mal for tracker for ansattes permisjon.
• Beregning av vektet gjennomsnitt i Excel.
• Opprette en bjellekurve i Excel.
• Avanserte Excel-diagrammer
• Hvordan for å legge til en sekundær akse i Excel-diagrammer.