Bekijk video - Hoe maak je een Pareto-diagram in Excel
Pareto Chart is gebaseerd op het Pareto-principe (ook bekend als de 80/20-regel), een bekend concept in projectmanagement.
Volgens dit principe kan ~80% van de problemen worden toegeschreven aan ongeveer ~20% van de problemen (of ~80% van je resultaten kan een direct resultaat zijn van ~20% van je inspanningen, enzovoort…).
De waarde van 80/20 procent kan variëren, maar het idee is dat van alle problemen/inspanningen er een paar zijn die maximale impact hebben.
Dit is een veelgebruikt concept in projectmanagement om prioriteiten te stellen.
Een Pareto-diagram maken in Excel
In deze tutorial laat ik je zien hoe je een:
- Eenvoudige (statische) Pareto-grafiek in Excel.
- Dynamische (interactieve) Pareto-grafiek in Excel.
Het maken van een Pareto-diagram in Excel is heel eenvoudig.
Alle bedrog zit verborgen in hoe u de gegevens in de backend rangschikt.
Laten we een voorbeeld nemen van een hotel waarvan de klachtengegevens er ongeveer als volgt uit kunnen zien:
OPMERKING: Om een Pareto-diagram in Excel te maken, moet u de gegevens in aflopende volgorde hebben gerangschikt.
Een eenvoudige (statische) Pareto-grafiek maken in Excel
Dit zijn de stappen om een Pareto-diagram in Excel te maken:
- Stel uw gegevens in zoals hieronder weergegeven.
- Bereken cumulatief % in kolom C. Gebruik de volgende formule: =SUM($B$2:B2)/SUM($B$2:$B$1)
- Selecteer de volledige dataset (A1:C10), ga naar Invoegen -> Grafieken -> 2-D Kolom -> Geclusterde Kolom. Hiermee wordt een kolomdiagram ingevoegd met 2 reeksen gegevens (# klachten en het cumulatieve percentage).
- Klik met de rechtermuisknop op een van de balken en selecteer Grafiektype serie wijzigen.
- Selecteer in het dialoogvenster Wijzig diagramtype de optie Combo in het linkerdeelvenster.
- Breng de volgende wijzigingen aan:
- Aantal klachten: geclusterde kolom.
- Cumulatief %: lijn (vink ook het selectievakje Secundaire as aan).
[Als u Excel 2010 of 2007 gebruikt, gaat het in twee stappen Verwerken. Wijzig eerst het diagramtype in een lijndiagram. Klik vervolgens met de rechtermuisknop op het lijndiagram en selecteer Gegevensreeks opmaken en selecteer Secundaire as in Reeksopties]
- Uw Pareto-diagram in Excel is klaar. Pas de waarden voor de verticale as en de grafiektitel aan.
Hoe deze Pareto-grafiek in Excel te interpreteren?
Deze Pareto-grafiek belicht de belangrijkste problemen waarop het hotel zich moet concentreren om het maximale aantal klachten te sorteren. Als u zich bijvoorbeeld richt op de eerste 3 problemen, zou ongeveer 80% van de klachten automatisch worden verholpen.
Als u zich bijvoorbeeld richt op de eerste 3 problemen, zou ongeveer 80% van de klachten automatisch worden verholpen.
Een dynamische (interactieve) Pareto-grafiek maken in Excel
Nu we een statisch / eenvoudig Pareto-diagram in Excel hebben, laten we een stap verder gaan en het een beetje interactief maken.
Iets zoals hieronder weergegeven:
In dit geval kan een gebruiker het percentage klachten specificeren dat moet worden aangepakt (met behulp van de Excel-schuifbalk), en de grafiek zal automatisch de problemen markeren die moeten worden onderzocht.
Het idee hier is om 2 verschillende bars te hebben.
De rode wordt gemarkeerd wanneer de cumulatieve procentuele waarde dicht bij de doelwaarde ligt.
Hier zijn de stappen om dit interactieve Pareto-diagram in Excel te maken:
- In cel B14 heb ik de doelwaarde die is gekoppeld aan de schuifbalk (waarvan de waarde varieert van 0 tot 100).
- In cel B12 heb ik de formule =B14/100 gebruikt. Aangezien u geen procentuele waarde voor een schuifbalk kunt opgeven, delen we de schuifbalkwaarde (in B14) gewoon door 100 om de procentuele waarde te krijgen.
- 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 deze 77% opleveren, wat aangeeft dat u moet proberen de eerste drie problemen op te lossen.
- Voer in cel D2 de volgende formule in (en sleep of kopieer voor alle cellen - D2:D10):
=ALS($B$13>=C2,B2,NA()) - Voer in cel E2 de volgende formule in (en sleep of kopieer voor alle cellen - E2:E10):
=ALS($B$13<> - Selecteer de gegevens in kolom A, C, D & E (druk op control en selecteer met de muis).
- Ga naar Invoegen -> Grafieken -> 2-D-kolom -> Geclusterde kolom. Hiermee wordt een kolomdiagram ingevoegd met 3 reeksen gegevens (cumulatief percentage, de balken die moeten worden gemarkeerd om het doel te bereiken en alle andere balken overblijven)
- Klik met de rechtermuisknop op een van de balken en selecteer Grafiektype serie wijzigen.
- Selecteer in het dialoogvenster Grafiektype wijzigen Combo in het linkerdeelvenster en breng de volgende wijzigingen aan:
- Cumulatief %: lijn (vink ook het selectievakje Secundaire as aan).
- Gemarkeerde balken: geclusterde kolom.
- Resterende balken: geclusterde kolom.
- Klik met de rechtermuisknop op een van de gemarkeerde balken en verander de kleur in Rood.
Dat is het!
U hebt een interactieve Pareto-grafiek gemaakt in Excel.
Als u nu het doel wijzigt met behulp van de schuifbalk, wordt het Pareto-diagram dienovereenkomstig bijgewerkt.
Gebruikt u de Pareto-grafiek in Excel?
Ik zou graag uw mening horen over deze techniek en hoe u deze hebt gebruikt. Laat je voetafdrukken achter in de comments
- Analyseren van restaurantklachten met behulp van Pareto Chart.
- Een Gantt-diagram maken in Excel.
- Een mijlpaaldiagram maken in Excel.
- Een histogram maken in Excel.
- Excel Timesheet Calculator-sjabloon.
- Sjabloon voor het bijhouden van werknemersverlof.
- Gewogen gemiddelde berekenen in Excel.
- Een belcurve maken in Excel.
- Geavanceerde Excel-grafieken
- Een secundaire as toevoegen in Excel-diagrammen
