Maak een pareto-diagram in Excel

In Excel bestaat een pareto-diagram uit een kolom- en een lijndiagram, waarbij de kolommen de frequentiewaarden in aflopende volgorde weergeven en de lijn de cumulatieve totalen op een secundaire as aangeeft. Het wordt gebruikt om de belangrijke gebieden van defecten in een product te analyseren en de verbeteringen te bepalen die de waarde van een bedrijf verhogen. Om een pareto-diagram te maken dat de meest voorkomende oorzaken van falen en productdefecten in een Excel-werkblad weergeeft, zal dit artikel u helpen.

• Maak een eenvoudig pareto-diagram in Excel 2016 en latere versies
• Maak een eenvoudig pareto-diagram in Excel 2013 en eerdere versies
• Maak een dynamisch pareto-diagram in Excel
• Download Pareto-diagram voorbeeldbestand
• Video: Maak een pareto-diagram in Excel

Maak een eenvoudig pareto-diagram in Excel 2016 en latere versies

Als u Excel 2016 of latere versies heeft, is er een ingebouwde functie - Pareto-diagram, die u kan helpen om snel en gemakkelijk een pareto-diagram in te voegen. Volg de volgende stappen:

1. Selecteer het gegevensbereik waarop u een pareto-diagram wilt maken.

2. Klik vervolgens op Invoegen > Statistische grafiek invoegen > Pareto, zie screenshot:

3. Er is nu meteen een pareto-diagram gemaakt zoals in onderstaande schermafbeelding te zien is:

4. Vervolgens kunt u de titel van het diagram bewerken en de gegevenslabels toevoegen zoals u nodig hebt, zie screenshot:

Maak een eenvoudig pareto-diagram in Excel 2013 en eerdere versies

Als u Excel 2013 of eerdere versies gebruikt, moet u de volgende stappen één voor één toepassen:

Ten eerste, bereid de gegevens voor om het pareto-diagram te maken:

1. U moet de gegevens in aflopende volgorde sorteren, selecteer cel B2 en klik vervolgens op Gegevens > Sorteren Z naar A, zie screenshot:

2. En vervolgens zijn de gegevens in aflopende volgorde gesorteerd, zie screenshot:

3. Typ vervolgens deze formule =B2 in cel C2 om de Cumulatieve Telling te berekenen en druk op Enter. Zie screenshot:

4. Voer vervolgens deze formule =C2+B3 in cel C3 in en sleep de vulgreep naar beneden naar de cellen, zie screenshots:

5. Nadat u de cumulatieve telling hebt verkregen, gaat u verder met het berekenen van het cumulatieve percentage, voer deze formule in: =C2/$C$11 in cel D2 en sleep de vulgreep naar beneden naar de cellen die u nodig hebt, zie screenshot:

6. Vervolgens moet u de decimale waarden converteren naar percentage waarden, selecteer de formulecellen en klik vervolgens op Start > Percentagestijl, zie screenshot:

Ten tweede, maak het diagram op basis van de gegevens

7. Nadat u de gegevens hebt voorbereid, selecteert u de gegevens in kolom A, kolom B en kolom D door de Ctrl-toets, zie screenshot:

8. Klik vervolgens op Invoegen > Kolom- of staafdiagram invoegen > Gegroepeerde kolom, zie screenshot:

9. U krijgt nu een diagram zoals in onderstaande schermafbeelding te zien is:

10. Klik vervolgens met de rechtermuisknop op één rode balk (Cumulatief Percentage) en kies Type Grafiekreeks Wijzigen in het contextmenu, zie screenshot:

11. In het dialoogvenster Grafiektype wijzigen, onder het tabblad Alle grafieken, klik op de optie Combinatie aan de linkerkant, in het keuzelijstvenster Kies het grafiektype en de as voor uw gegevensreeksen, klik op de vervolgkeuzelijst van het Cumulatieve veld en kies Lijn met Markeringen grafiektype, en vink het selectievakje aan, zie screenshot:

12. Klik vervolgens op de knop OK en u krijgt een diagram zoals in onderstaande schermafbeelding te zien is:

13. Klik vervolgens met de rechtermuisknop op de procentuele as en kies de optie As Opmaak in het contextmenu, zie screenshot:

14. In het deelvenster As Opmaak, onder het tabblad As Opties, naast Maximum, stelt u het nummer in op 1.0 in het tekstvak, en het pareto-diagram is succesvol gemaakt, zie screenshot:

15. Tot slot kunt u de titel van het diagram wijzigen en de gegevenslabels toevoegen zoals u nodig hebt.

Maak een dynamisch pareto-diagram in Excel

In deze sectie zal ik bespreken hoe u een dynamisch pareto-diagram maakt met behulp van een schuifbalk. Wanneer u de doelwaarde wijzigt door te klikken of de schuifbalk te verplaatsen, markeert het diagram automatisch de balk van de problemen zoals in onderstaande demo te zien is.

Om dit type pareto-diagram te maken, volgt u de volgende stappen stap voor stap:

1. Bereken eerst het cumulatieve percentage in kolom C, gebruik hiervoor deze formule in cel C2:

2. Sleep vervolgens de vulgreep naar beneden naar de cellen en formatteer de decimale getallen als percentage waarden, zie screenshot:

3. Vervolgens hebt u drie hulprijen nodig: Doel, Cumulatieve Waarde, Schuifbalk Koppelingswaarde om enkele berekeningen uit te voeren zoals in onderstaande schermafbeelding te zien is:

4. Nadat u de drie cellen hebt gemaakt, klikt u op Ontwikkelaar > Invoegen > Schuifbalk (Formulierbesturingselement), en tekent u een schuifbalk zoals in onderstaande schermafbeeldingen te zien is:

5. Klik vervolgens met de rechtermuisknop op de schuifbalk en kies Formaat Besturingselement in het contextmenu, in het dialoogvenster Formaat Object dat verschijnt, onder het tabblad Besturingselement, stelt u de relevante waarden in en specificeert u de gekoppelde cel zoals in onderstaande schermafbeelding te zien is:

6. Klik vervolgens op de knop OK om het dialoogvenster te sluiten. Typ nu de volgende twee formules in cel B14 en B15 apart in, en formatteer ze als percentage waarden:

7. Maak nu twee hulkkolommen naast de originele gegevens en voer de volgende twee formules in cel D2 en E2 in:

8. Kopieer vervolgens de formules naar andere cellen zoals in onderstaande schermafbeelding te zien is:

9. Nadat u de gegevens hebt gemaakt, selecteert u de gegevens in kolom A, kolom C, kolom D, kolom E door de Ctrl-toets vast te houden, klik vervolgens op Invoegen > Kolom- of staafdiagram invoegen > Gegroepeerde kolom, en er wordt een diagram ingevoegd zoals in onderstaande schermafbeelding te zien is:

10. Selecteer vervolgens het diagram en klik op Ontwerp > Grafiektype Wijzigen, in het dialoogvenster Grafiektype Wijzigen, onder het tabblad Alle grafieken, klik op Combinatie aan de linkerkant, en klik vervolgens op Aangepaste Combinatiegrafiek, en in de sectie Kies het grafiektype en de as voor uw gegevensreeksen, specificeer de volgende bewerkingen voor elke gegevensreeks:

• Cumulatief %: Lijndiagramtype, en vink het selectievakje Secundaire As ook aan;
• Gemarkeerde Balken: Gegroepeerde kolomdiagramtype;
• Resterende Balken: Gegroepeerde kolomdiagramtype.

11. Klik vervolgens op de knop OK om het dialoogvenster te sluiten, en u krijgt een diagram zoals in onderstaande schermafbeelding te zien is:

12. Klik vervolgens met de rechtermuisknop op de procentuele as en kies de optie As Opmaak, in het uitgebreide deelvenster As Opmaak, onder het tabblad As Opties, naast Maximum, stelt u het nummer in op 1.0 in het tekstvak, zie screenshot:

13. Ga vervolgens verder met het klikken met de rechtermuisknop op een balkreeks in het diagram en kies Gegevensreeks Opmaak, in het deelvenster Gegevensreeks Opmaak, onder het tabblad Reeksopties, voer 100% in het tekstvak Reeksoverlap in, zie screenshot:

14. Tot slot kunt u de schuifbalk onder het diagram plaatsen en voer deze formule in: ="Doel "&TEKST(B14,"0%") in een cel onder de schuifbalk om de doelpercentage waarde te krijgen, zie screenshot:

15. Nu is het dynamische pareto-diagram gemaakt, u kunt de vulkleur van de gemarkeerde balk naar wens wijzigen, en wanneer u de doelwaarde wijzigt met behulp van de schuifbalk, wordt het pareto-diagram automatisch bijgewerkt zoals in onderstaande demo te zien is:

Download Pareto-diagram voorbeeldbestand

Video: Maak een Pareto-diagram in Excel