Skapa ett Pareto-diagram i Excel (statiskt och dynamiskt)

Titta på video – Hur man gör en Pareto-diagram i Excel

Pareto-diagram är baserat på Pareto-principen (även känd som 80/20-regeln), vilket är ett välkänt koncept inom projektledning.

The 80 / 20 procentvärde kan variera, men tanken är att av alla frågor / ansträngningar finns det några som resulterar i maximal påverkan.

Detta är ett allmänt använt koncept inom projektledning för att prioritera arbete.

Skapa ett Pareto-diagram i Excel

I denna handledning visar jag dig hur du skapar ett:

• Enkelt (statiskt) Pareto-diagram i Excel.
• Dynamiskt (interaktivt) Pareto-diagram i Excel.

Att skapa ett Pareto-diagram i Excel är väldigt enkelt.

Allt knep är gömt i hur du ordnar data i backend.

Låt oss ta ett exempel på ett hotell för vilket klagomålsuppgifterna kan se något ut som visas nedan:

OBS: För att skapa ett Pareto-diagram i Excel måste du ha data ordnade i fallande ordning.

Ladda ner Excel Pareto-diagrammallen

Skapa ett enkelt (statiskt) Pareto-diagram i Excel

Här är steg för att skapa ett Pareto-diagram i Excel:

1. Ställ in dina data enligt nedan.
2. Beräkna kumulativ% i kolumn C. Använd följande formel: = SUMMA ($ B $ 2: B2) / SUM ($ B $ 2: $ B $ 1 )
3. Välj hela datamängden (A1: C10), gå till Infoga – > Diagram – > 2-D-kolumn – > Clustered Column. Detta infogar ett kolumndiagram med två dataserier (antal klagomål och den kumulativa procentandelen).
4. Högerklicka på någon av staplarna och välj Ändra Seriediagramtyp.
5. I dialogrutan Ändra diagramtyp väljer du Kombination i den vänstra rutan.
6. Gör följande ändringar:
   • Antal klagomål: Clustered Column.
   • Kumulativ%: Rad (markera även kryssrutan Sekundär axel).
7. Ditt Pareto-diagram i Excel är klart. Justera de vertikala axelvärdena och diagramtiteln.

Hur man tolkar detta Pareto-diagram i Excel

Denna Pareto Diagrammet belyser de viktigaste frågorna som hotellet bör fokusera på för att sortera maximalt antal klagomål. Om du till exempel riktar in dig mot de tre första problemen automatiskt tar vi hand om ~ 80% av klagomålen.

Till exempel skulle inriktning på de tre första problemen automatiskt ta hand om ~ 80% av klagomålen.

Skapa ett dynamiskt (interaktivt) Pareto-diagram i Excel

Nu när vi har ett statiskt / enkelt Pareto-diagram i Excel, låt oss ta det ett steg längre och göra det lite interaktivt.

Något som visas nedan:

I det här fallet kan en användare specificera% av klagomål som behöver åtgärdas (med excel-rullning stapeln), och diagrammet markerar automatiskt de frågor som bör undersökas.

Tanken här är att ha två olika staplar.

Den röda markeras när den kumulativa procenten värdet är nära målvärdet.

Här är stegen för att göra detta interaktiva Pareto-diagram i Excel:

1. I cell B14 har jag målvärdet som är länkat till rullningsfältet (vars värde varierar från 0 till 100).
2. I cell B12 har jag använt formeln = B14 / 100. Eftersom du inte kan ange ett procentvärde till ett rullningsfält delar vi helt enkelt rullningsfältets värde (i B14) med 100 för att få det procentuella värdet.
3. I cell B13 anger du följande kombination av funktioner INDEX, MATCH och FEL:
   = FEL (INDEX ($ C $ 2: $ C $ 10, FEL (MATCH ($ B $ 12, $ C $ 2: $ C $ 10,1) ), 0) +1), 1)
   Denna formel returnerar det kumulativa värdet som skulle täcka målvärdet. Om du till exempel har målvärdet 70% skulle det returnera 77%, vilket indikerar att du bör försöka lösa de tre första problemen.

1. I cell D2 anger du följande formel (och drar eller kopierar för alla celler – D2: D10):
   = IF ($ B $ 13 > = C2, B2, NA ())
2. Ange följande formel i cell E2 (och dra eller kopiera för alla celler – E2: E10):
   = IF ( $ B $ 13 < C2, B2, NA ())
3. Välj data i kolumn A, C, D & E (tryck på kontrollen och välj med musen).
4. Gå till Infoga – > Diagram – > 2-D-kolumn – > Clustered Column.Detta infogar kolumndiagram med tre serier av data (kumulativ procentsats, staplarna som ska markeras för att möta målet och återstående alla andra staplar)
5. Högerklicka på någon av staplarna och välj Ändra seriediagram Typ.
6. I dialogrutan Ändra diagramtyp väljer du Kombination i den vänstra rutan och gör följande ändringar:
   • Kumulativ%: Linje (kryssa också i kryssrutan Sekundär axel).
   • Markerade staplar: Clustered Column.
   • Återstående staplar: Clustered Column.
7. Högerklicka på någon av de markerade staplarna och ändra färgen till röd.

Det är det!

Du har skapat ett interaktivt Pareto-diagram i Excel.

Nu när du ändrar målet med hjälp av rullningsfältet, Pareto-diagrammet uppdateras därefter.

Ladda ner Excel Pareto-diagrammallen

Använder du Pareto-diagrammet i Excel?

Jag skulle gärna höra dina tankar om den här tekniken och hur du har Jag har använt det. Lämna dina fotspår i kommentarsektionen 🙂

Relaterade projektlednings- och kartguider:

• Analysera restaurangklagomål med Pareto-diagram.
• Skapa en Gantt Diagram i Excel.
• Skapa ett milstolpediagram i Excel.
• Skapa ett histogram i Excel.
• Excel-tidskalkylatormall.
• Mall för medarbetarledighetsspårare.
• Beräkning av viktat genomsnitt i Excel.
• Skapa en klockkurva i Excel.
• Avancerade Excel-diagram
• Hur för att lägga till en sekundär axel i Excel-diagram.