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 belangrijkste gebieden met defecten in een product te analyseren en verbeteringen te bepalen die de waarde van een bedrijf verhogen. Als u een pareto-diagram wilt maken om de meest voorkomende oorzaken van storingen of productdefecten in een Excel-werkblad weer te geven, helpt dit artikel u daarbij.

• Maak een eenvoudig pareto-diagram in Excel 2016 en nieuwere versies
• Maak een eenvoudig pareto-diagram in Excel 2013 en oudere versies
• Maak een dynamisch pareto-diagram in Excel
• Download het voorbeeldbestand voor het pareto-diagram
• Video: Maak een pareto-diagram in Excel

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

Als u Excel 2016 of een nieuwere versie gebruikt, beschikt u over de ingebouwde functie **Pareto-diagram**, waarmee u in slechts enkele stappen snel en eenvoudig een Pareto-diagram kunt invoegen. Volg hiervoor de onderstaande stappen:

1. Selecteer het gegevensbereik waarop u een Pareto-diagram wilt baseren.

2. Klik vervolgens op Invoegen > Statistiekdiagram invoegen > Pareto, zie schermafbeelding:

3. Er wordt direct een pareto-diagram gemaakt, zoals te zien is op de onderstaande schermafbeelding:

4. U kunt daarna de grafiektitel bewerken en gegevenslabels toevoegen, precies zoals u dat nodig hebt — zie de schermafbeelding:

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

Als u Excel 2013 of een oudere versie gebruikt, moet u de volgende stappen één voor één toepassen:

Bereid eerst de gegevens voor om het pareto-diagram te maken:

1. Sorteer de gegevens in aflopende volgorde: klik op cel B2 en vervolgens op Gegevens > Sorteren van Z naar A, zie schermafbeelding:

2. Vervolgens is het gegevensbereik gesorteerd in aflopende volgorde, zie de schermafbeelding:

3. Bereken vervolgens het cumulatieve aantal door in cel C2 de formule =B2 in te voeren en op Enter te drukken. Zie schermafbeelding:

4. Voer daarna in cel C3 de formule =C2+B3 in en sleep de vulgreep naar beneden naar de gewenste cellen. Zie de schermafbeeldingen:

5. Nadat u het cumulatieve aantal heeft, berekent u het cumulatieve percentage door de volgende formule in cel D2 in te voeren: =C2/$C$11, en sleep vervolgens de vulgreep naar beneden naar de gewenste cellen. Zie de schermafbeelding:

6. Converteer de decimale waarden vervolgens naar percentages: selecteer de formulecellen en klik op Start > Percentage-opmaak, zie schermafbeelding:

Maak vervolgens 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 toets Ctrl ingedrukt te houden. Zie schermafbeelding:

8. Klik vervolgens op Invoegen > Kolom- of staafgrafiek invoegen > Gegroepeerde kolommen, zie de schermafbeelding:

9. U krijgt dan een diagram zoals op de onderstaande schermafbeelding:

10. Klik met de rechtermuisknop op één van de rode balken (Cumulatief percentage) en kies Reeks grafiektype wijzigen in het contextmenu, zie schermafbeelding:

11. In het dialoogvenster Grafiektype wijzigen, onder het tabblad Alle diagrammen, klikt u in het linkerdeelvenster op de optie Combinatie. Klik vervolgens in het vak Kies het grafiektype en de as voor uw gegevensreeksen op de vervolgkeuzelijst bij Cumulatief, kies het Lijn met markeringen-diagram-type en vink het selectievakje aan. Zie schermafbeelding:

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

13. Klik met de rechtermuisknop op de percentage-as en kies vervolgens in het contextmenu de optie As opmaken, zie schermafbeelding:

14. In het deelvenster As opmaken, onder het tabblad Asopties, stelt u naast Maximum het getal in op 1,0 in het tekstvak. Het pareto-diagram is nu succesvol gemaakt—zie schermafbeelding:

15. Tenslotte kunt u de grafiektitel aanpassen en gegevenslabels toevoegen, precies zoals u dat nodig hebt.

Maak een dynamisch pareto-diagram in Excel

In dit gedeelte leg ik uit hoe u een dynamisch pareto-diagram maakt met behulp van een schuifbalk. Wanneer u de doelwaarde wijzigt door op de schuifbalk te klikken of deze te verplaatsen, markeert het diagram automatisch de balk van de problemen, zoals in de onderstaande demo wordt getoond.

Volg de volgende stappen om dit type pareto-diagram te maken:

1. Bereken eerst het cumulatieve percentage in kolom C door de volgende formule in cel C2 toe te passen:

2. Sleep vervolgens de vulgreep naar beneden naar de cellen en formatteer de decimale getallen als percentages. Zie de schermafbeelding:

3. Vervolgens hebt u drie hulpvelden nodig – Doel, Cumulatieve waarde en Koppelwaarde schuifbalk – om enkele berekeningen uit te voeren, zoals te zien is op de onderstaande schermafbeelding:

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

5. Klik met de rechtermuisknop op de schuifbalk en kies Besturingselement opmaken in het contextmenu. Stel in het verschijnende dialoogvenster Object opmaken, onder het tabblad Besturingselement, de relatieve waarden in en geef de gekoppelde cel op, zoals te zien is in de onderstaande schermafbeelding:

6. Klik daarna op de knop OK om het dialoogvenster te sluiten. Typ nu de volgende twee formules in cel B14 en B15 en formatteer ze als percentages:

7. Maak direct twee hulpkolommen naast de oorspronkelijke gegevens en voer de volgende formules in cel D2 en E2 in:

8. Kopieer de formules daarna naar de overige cellen, zoals te zien is in de schermafbeelding hieronder:

9. Nadat u de gegevens hebt gemaakt, selecteert u de gegevens in kolom A, kolom C, kolom D en kolom E door de toets Ctrl ingedrukt te houden. Klik vervolgens op Invoegen > Kolom- of Staafgrafiek invoegen > Gegroepeerde kolommen. Er wordt dan een diagram ingevoegd, zoals op de onderstaande schermafbeelding:

10. Selecteer vervolgens de grafiek en klik op Ontwerp > Wijzig grafiektype. In het dialoogvenster Wijzig grafiektype, onder het tabblad Alle grafieken, klikt u in het linkerdeelvenster op Combo en daarna op Aangepaste combinatiegrafiek. Vervolgens geeft u in het gedeelte Kies het grafiektype en de as voor uw gegevensreeks de volgende instellingen op voor elke gegevensreeks:

• Cumulatief %: lijn Grafiektype, en vink ook de secundaire asSelectievakje aan;
• Gemarkeerde balken: Gegroepeerde kolomgrafiek-type;
• Overige balken: gegroepeerde kolomgrafiek.

11. Klik vervolgens op de knop OK om het dialoogvenster te sluiten. U krijgt dan een grafiek zoals hieronder in de schermafbeelding wordt weergegeven:

12. Klik daarna met de rechtermuisknop op de percentageschaal en kies vervolgens de optie As opmaken. In het uitgevouwen deelvenster As opmaken, onder het tabblad Asopties, stelt u naast Maximum het getal in op 1,0 in het tekstvak; zie schermafbeelding:

13. Klik daarna met de rechtermuisknop op een willekeurige staafreeks in de grafiek en kies vervolgens Gegevensreeks opmaken. In het deelvenster Gegevensreeks opmaken, onder het tabblad Reeksopties, voert u 100 % in het tekstvak Overlap gegevensreeksen in; zie schermafbeelding:

14. Tot slot verplaatst u de schuifbalk onder de grafiek en voert u deze formule in:=„Target "&TEXT(B14,"0 %") in een cel onder de schuifbalk om de doelpercentage-waarde te verkrijgen; zie schermafbeelding:

15. De dynamische Pareto-grafiek is nu klaar! U kunt de vulkleur van de gemarkeerde balk naar wens aanpassen. Wanneer u de doelwaarde aanpast met de schuifbalk, wordt de Pareto-grafiek automatisch bijgewerkt—zoals te zien is in de demo hieronder:

Download het voorbeeldbestand voor het pareto-diagram

Video: Pareto-grafiek maken in Excel