Sådan laver du et Pareto -diagram i Excel (statisk og interaktiv)

Se video - Sådan laver du et Pareto -diagram i Excel

Pareto Chart er baseret på Pareto-princippet (også kendt som 80/20-reglen), som er et velkendt koncept inden for projektledelse.

Ifølge dette princip kan ~ 80% af problemerne tilskrives omkring ~ 20% af problemerne (eller ~ 80% af dine resultater kan være et direkte resultat af ~ 20% af din indsats og så videre …).

Værdien på 80/20 procent kan variere, men tanken er, at der af alle problemerne/bestræbelserne er nogle få, der resulterer i maksimal effekt.

Dette er et meget udbredt koncept i projektledelse til at prioritere arbejde.

Oprettelse af et Pareto -diagram i Excel

I denne vejledning viser jeg dig, hvordan du laver en:

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

Det er meget let at oprette et Pareto -diagram i Excel.

Alt det lure er skjult i, hvordan du arrangerer dataene i backend.

Lad os tage et eksempel på et hotel, for hvilket klagedataene kunne se noget ud som vist nedenfor:

BEMÆRK: For at lave et Pareto -diagram i Excel skal du have dataene arrangeret i faldende rækkefølge.

Oprettelse af et enkelt (statisk) Pareto -diagram i Excel

Her er trinene til at oprette et Pareto -diagram i Excel:

1. Konfigurer dine data som vist herunder.
2. Beregn kumulativ % i kolonne C. Brug følgende formel: = SUM ($ B $ 2: B2)/SUM ($ B $ 2: $ B $ 1)
3. Vælg hele datasættet (A1: C10), gå til Indsæt -> Diagrammer -> 2 -D -kolonne -> Klynge -kolonne. Dette indsætter et kolonnediagram med 2 dataserier (antal klager og den kumulative procentdel).
4. Højreklik på en af ​​søjlerne, og vælg Skift seriekorttype.
5. I dialogboksen Skift diagramtype skal du vælge Kombination i venstre rude.
6. Foretag følgende ændringer:
   • Antal klager: Grupperet kolonne.
   • Kumulativ %: Linje (markér også afkrydsningsfeltet Sekundær akse).[Hvis du bruger Excel 2010 eller 2007, vil det være et totrins behandle. Skift først diagramtypen til et stregdiagram. Højreklik derefter på linjediagrammet, og vælg Formatér dataserier, og vælg Sekundær akse i serieindstillinger]
7. Dit Pareto -diagram i Excel er klar. Juster de lodrette akseværdier og diagramtitlen.

Sådan fortolkes dette Pareto -diagram i Excel

Dette Pareto -diagram fremhæver de store spørgsmål, som hotellet bør fokusere på for at sortere det maksimale antal klager. For eksempel ville målretning mod de første 3 spørgsmål automatisk tage sig af ~ 80% af klagerne.

For eksempel ville målretning mod de første 3 spørgsmål automatisk tage sig af ~ 80% af klagerne.

Oprettelse af et dynamisk (interaktivt) Pareto -diagram i Excel

Nu hvor vi har et statisk/simpelt Pareto -diagram i Excel, lad os tage det et skridt videre og gøre det lidt interaktivt.

Noget som vist herunder:

I dette tilfælde kan en bruger angive procentdelen af ​​klager, der skal håndteres (ved hjælp af Excel -rullelinjen), og diagrammet vil automatisk fremhæve de problemer, der skal undersøges.

Ideen her er at have 2 forskellige barer.

Den røde fremhæves, når den kumulative procentvise værdi er tæt på målværdien.

Her er trinene til at lave dette interaktive Pareto -diagram i Excel:

1. I celle B14 har jeg den målværdi, der er knyttet til rullepanelet (hvis værdi varierer fra 0 til 100).
2. I celle B12 har jeg brugt formlen = B14/100. Da du ikke kan angive en procentværdi til en rullebjælke, deler vi blot rulleværdien (i B14) med 100 for at få procentværdien.
3. I celle B13 skal du indtaste følgende kombination af INDEX-, MATCH- og IFERROR -funktioner:
   = IFERROR (INDEX ($ C $ 2: $ C $ 10, IFERROR (MATCH ($ B $ 12, $ C $ 2: $ C $ 10,1), 0) +1), 1)
   Denne formel returnerer den kumulative værdi, der ville dække målværdien. Hvis du f.eks. Har målværdien til 70%, returnerer den 77%, hvilket indikerer, at du skal prøve at løse de første tre problemer.

4. I celle D2 skal du indtaste følgende formel (og trække eller kopiere for alle celler - D2: D10):
   = HVIS ($ B $ 13> = C2, B2, NA ())
5. I celle E2 skal du indtaste følgende formel (og trække eller kopiere for alle celler - E2: E10):
   = HVIS ($ B $ 13<>
6. Vælg dataene i kolonne A, C, D & E (tryk på kontrol, og vælg med musen).
7. Gå til Indsæt -> Diagrammer -> 2 -D -kolonne -> Klynge -kolonne. Dette indsætter søjlediagram med 3 dataserier (kumulativ procentdel, søjlerne, der skal fremhæves for at nå målet, og resterende alle andre søjler)
8. Højreklik på en af ​​søjlerne, og vælg Skift seriekorttype.
9. I dialogboksen Skift diagramtype skal du vælge Kombination i venstre rude og foretage følgende ændringer:
   • Kumulativ %: Linje (markér også afkrydsningsfeltet Sekundær akse).
   • Fremhævede søjler: Clustered Column.
   • Resterende søjler: Clustered Column.
10. Højreklik på en af ​​de markerede søjler, og skift farven til rød.

Det er det!

Du har oprettet et interaktivt Pareto -diagram i Excel.

Når du nu ændrer målet ved hjælp af rullefeltet, opdateres Pareto -diagrammet i overensstemmelse hermed.

Bruger du Pareto -diagrammet i Excel?

Jeg ville elske at høre dine tanker om denne teknik og hvordan du har brugt den. Efterlad dine fodaftryk i kommentarfeltet 🙂

• Analyse af restaurantklager ved hjælp af Pareto -diagram.
• Oprettelse af et Gantt -diagram i Excel.
• Oprettelse af et milepælsdiagram i Excel.
• Oprettelse af et histogram i Excel.
• Excel -timesedels lommeregner skabelon.
• Medarbejder forlader sporingsskabelon.
• Beregning af vægtet gennemsnit i Excel.
• Oprettelse af en Bell Curve i Excel.
• Avancerede Excel -diagrammer
• Sådan tilføjes en sekundær akse i Excel -diagrammer.