Een Pareto-diagram maken in Excel (statisch en dynamisch)

Bekijk video – Hoe maak je een Pareto-diagram in Excel

Pareto-diagram is gebaseerd op het Pareto-principe (ook bekend als de 80/20-regel), een bekend concept in projectmanagement.

De 80 / 20 procentuele waarde kan variëren, maar het idee is dat van alle problemen / inspanningen er een paar resulteren in maximale impact.

Dit is een veelgebruikt concept in projectmanagement om prioriteit te geven aan werk.

Een Pareto-diagram maken in Excel

In deze tutorial laat ik je zien hoe je een:

• Eenvoudig (statisch) Pareto-diagram in Excel maakt.
• Dynamisch (interactief) Pareto-diagram in Excel.

Het maken van een Pareto-diagram in Excel is heel eenvoudig.

Alle bedrog is verborgen in hoe u de gegevens in de backend rangschikt.

Laten we een voorbeeld nemen van een hotel waarvan de klachtengegevens er ongeveer zo uit zouden kunnen zien:

OPMERKING: om een Pareto-diagram in Excel te maken, moet u de gegevens in aflopende volgorde hebben gerangschikt.

Download de Excel Pareto-grafieksjabloon

Een eenvoudige (statische) Pareto-grafiek maken in Excel

Hier zijn de stappen om een Pareto-diagram in Excel te maken:

1. Stel uw gegevens in zoals hieronder weergegeven.
2. Bereken het cumulatieve% in kolom C. Gebruik de volgende formule: = SUM ($ B $ 2: B2) / SUM ($ B $ 2: $ B $ 1 )
3. Selecteer de volledige dataset (A1: C10), ga naar Invoegen – > Charts – > 2-D kolom – > geclusterde kolom. Hiermee wordt een kolomdiagram ingevoegd met 2 reeksen gegevens (aantal klachten en het cumulatieve percentage).
4. Klik met de rechtermuisknop op een van de balken en selecteer Wijzigen Serie diagramtype.
5. Selecteer in het dialoogvenster Diagramtype wijzigen Combo in het linkerdeelvenster.
6. Breng de volgende wijzigingen aan:
   • Aantal klachten: geclusterde kolom.
   • Cumulatief%: regel (controleer ook het selectievakje Secundaire as).
7. Uw Pareto-diagram in Excel is klaar. Pas de waarden voor de verticale as en de diagramtitel aan.

Hoe dit Pareto-diagram in Excel te interpreteren

Dit Pareto chart belicht de belangrijkste kwesties waarop het hotel zich moet concentreren om het maximale aantal klachten te regelen. Als u zich bijvoorbeeld op de eerste 3 problemen richt, wordt automatisch ~ 80% van de klachten afgehandeld.

Als u zich bijvoorbeeld op de eerste 3 problemen richt, wordt automatisch ~ 80% van de klachten opgelost.

Een dynamische (interactieve) Pareto-grafiek maken in Excel

Nu we een statische / eenvoudige Pareto-grafiek in Excel hebben, gaan we nog een stap verder en maken we het een beetje interactief.

Iets zoals hieronder weergegeven:

In dit geval kan een gebruiker het percentage klachten specificeren dat moet worden aangepakt (met behulp van de Excel-scroll balk), en de grafiek zal automatisch de problemen markeren die moeten worden onderzocht.

Het idee hier is om 2 verschillende balken te hebben.

De rode wordt gemarkeerd wanneer het cumulatieve percentage waarde ligt dicht bij de doelwaarde.

Hier zijn de stappen om dit interactieve Pareto-diagram in Excel te maken:

1. In cel B14 heb ik de doelwaarde die is gekoppeld naar de schuifbalk (waarvan de waarde varieert van 0 tot 100).
2. In cel B12 heb ik de formule = B14 / 100 gebruikt. Aangezien u geen percentagewaarde voor een schuifbalk kunt specificeren, delen we de waarde van de schuifbalk (in B14) eenvoudig door 100 om de procentuele waarde te krijgen.
3. Voer in cel B13 de volgende combinatie van INDEX-, MATCH- en IFERROR-functies in:
   = IFERROR (INDEX ($ C $ 2: $ C $ 10, IFERROR (MATCH ($ B $ 12, $ C $ 2: $ C $ 10,1) ), 0) +1), 1)
   Deze formule retourneert de cumulatieve waarde die de doelwaarde zou dekken. Als u bijvoorbeeld de doelwaarde 70% heeft, zou dit 77% retourneren, wat aangeeft dat u moet proberen de eerste drie problemen op te lossen.

1. Voer in cel D2 de volgende formule in (en sleep of kopieer voor alle cellen – D2: D10):
   = IF ($ B $ 13 > = C2, B2, NA ())
2. Voer in cel E2 de volgende formule in (en sleep of kopieer voor alle cellen – E2: E10):
   = ALS ( $ B $ 13 < C2, B2, NA ())
3. Selecteer de gegevens in kolom A, C, D & E (druk op control en selecteer met de muis).
4. Ga naar Invoegen – > Grafieken – > 2D-kolom – > Geclusterde kolom.Hiermee wordt een kolomdiagram ingevoegd met 3 reeksen gegevens (cumulatief percentage, de balken die moeten worden gemarkeerd om aan het doel te voldoen en de overige balken blijven alle andere balken).
5. Klik met de rechtermuisknop op een van de balken en selecteer Reeksgrafiek wijzigen Type.
6. Selecteer in het dialoogvenster Diagramtype wijzigen Combo in het linkerdeelvenster en breng de volgende wijzigingen aan:
   • Cumulatief%: lijn (vink ook het selectievakje Secundaire as aan).
   • Gemarkeerde staven: geclusterde kolom.
   • Resterende staven: geclusterde kolom.
7. Klik met de rechtermuisknop op een van de gemarkeerde staven en verander de kleur in Rood.

Dat is alles!

Je hebt een interactieve Pareto-grafiek gemaakt in Excel.

Nu, wanneer je verandert het doel met behulp van de schuifbalk, wordt het Pareto-diagram dienovereenkomstig bijgewerkt.

Download de Excel Pareto-diagramsjabloon

Gebruikt u de Pareto-grafiek in Excel?

Ik zou graag horen wat u van deze techniek vindt en hoe u heb het gebruikt. Laat uw sporen achter in het commentaargedeelte 🙂

Gerelateerde projectmanagement- en grafiekenhandleidingen:

• Restaurantklachten analyseren met behulp van Pareto-diagram.
• Een Gantt maken Grafiek in Excel.
• Een mijlpaalgrafiek maken in Excel.
• Een histogram maken in Excel.
• Excel-urenstaatcalculator-sjabloon.
• Sjabloon voor verlofregistratie voor werknemers.
• Gewogen gemiddelde berekenen in Excel.
• Een belcurve maken in Excel.
• Geavanceerde Excel-grafieken
• Hoe om een secundaire as toe te voegen aan Excel-grafieken.