Maak een pareto-diagram in Excel
In Excel bestaat een pareto-diagram uit een kolom- en lijndiagram, waarbij de kolombalken de frequentiewaarden in aflopende volgorde weergeven en de lijn de cumulatieve totalen op een secundaire as aangeeft. Het wordt gebruikt om de significante defecten in een product te analyseren en de verbeteringen te bepalen die de waarde van een bedrijf verhogen. Om een pareto-diagram te maken om de meest voorkomende redenen voor mislukking, productdefecten in Excel-werkblad, weer te geven, helpt dit artikel u.
- Maak een eenvoudig pareto-diagram in Excel 2016 en latere versies
- Maak een eenvoudig pareto-diagram in Excel 2013 en eerdere versies
- Maak een dynamische pareto-grafiek in Excel
- Download het voorbeeldbestand van het Pareto-diagram
- Video: maak een pareto-diagram in Excel
Maak een eenvoudig pareto-diagram in Excel 2016 en latere versies
Als u Excel 2016 en latere versies heeft, is er een ingebouwde functie - Pareto-diagram, waarmee u snel en gemakkelijk een pareto-diagram kunt invoegen. Ga als volgt te werk:
1. Selecteer het gegevensbereik waarop u een pareto-diagram wilt maken op basis van.
2. Dan klikken Invoegen > Statistisch diagram invoegen > Pareto, zie screenshot:
3. En dan is er in één keer een pareto-diagram gemaakt, zoals onder screenshot:
4. Vervolgens kunt u de diagramtitel bewerken en de gegevenslabels naar behoefte toevoegen, zie screenshot:
Maak een eenvoudig pareto-diagram in Excel 2013 en eerdere versies
Als u Excel 2013 en eerdere versies gebruikt, moet u de volgende stappen een voor een uitvoeren:
Bereid eerst 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 Data > Sorteer Z naar A, zie screenshot:
2. En dan is het gegevensbereik in aflopende volgorde gesorteerd, zie screenshot:
3. Bereken vervolgens de cumulatieve telling door deze formule te typen = B2 in dit geval in cel C2 en druk op Enter sleutel. Zie screenshot:
4. En voer vervolgens deze formule in = C2 + B3 in cel C3, en sleep de vulgreep naar de cellen, zie screenshots:
 |
 |
 |
5. Nadat u de cumulatieve telling heeft ontvangen, 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 de cellen die je nodig hebt, zie screenshot:
6. Vervolgens moet u de decimale waarden converteren naar procentuele waarden, de formulecellen selecteren en vervolgens klikken Home > Percentage stijl, zie screenshot:
Ten tweede: maak de grafiek op basis van de gegevens
7. Selecteer na het voorbereiden van de gegevens de gegevens in kolom A, kolom B en kolom D door Ctrl key, zie screenshot:
8. En klik dan Invoegen > Kolom- of staafdiagram invoegen > Geclusterde kolom, zie screenshot:
9. En je krijgt een grafiek zoals hieronder getoond screenshot:
10. Klik vervolgens met de rechtermuisknop op een rode balk (cumulatief percentage) en kies Wijzig serie grafiektype zie screenshot vanuit het contextmenu:
11. In de Wijzig het diagramtype dialoogvenster onder het Alle grafieken tab, klik Combo optie in het linkerdeelvenster, in het Kies het diagramtype en de as voor uw gegevensreeks keuzelijst, klik op de vervolgkeuzelijst in het veld Cumulatief en kies Lijn met markeringsgrafiek typ en vink het selectievakje aan, zie screenshot:
12. En klik dan OK knop, en je krijgt een grafiek zoals hieronder getoond screenshot:
13. Klik vervolgens met de rechtermuisknop op de percentage-as en kies vervolgens Formaat Axis optie uit het contextmenu, zie screenshot:
14. In de Formaat Axis deelvenster, onder het Axis-opties tabblad, naast het maximaal, zet het nummer op 1.0 in het tekstvak en de pareto-grafiek is met succes gemaakt, zie screenshot:
15. Eindelijk kunt u de diagramtitel wijzigen en de gegevenslabels naar behoefte toevoegen.
Maak een dynamische pareto-grafiek in Excel
In dit gedeelte zal ik het hebben over het maken van een dynamische pareto-grafiek met behulp van een schuifbalk. Wanneer u de doelwaarde wijzigt door op de schuifbalk te klikken of deze te verplaatsen, zal de grafiek de balk met de problemen automatisch markeren zoals hieronder getoond.
Om dit type pareto-diagram te maken, voert u de volgende stap voor stap uit:
1. Bereken eerst het cumulatieve percentage in kolom C, pas deze formule toe in cel C2:
2. En sleep vervolgens de vulgreep naar de cellen en formatteer de decimale getallen als percentagewaarden, zie screenshot:
3. Vervolgens heb je drie helpercellen nodig die Target, Cumulative Value, Scroll Bar Link Value zijn om enkele berekeningen uit te voeren, zoals hieronder wordt getoond:
4. Nadat u de drie cellen heeft gemaakt, klikt u op Ontwikkelaar > Invoegen > Schuifbalk (formulierbeheer)en teken vervolgens een schuifbalk zoals hieronder weergegeven schermafbeeldingen:
 |
|
 |
5. Klik vervolgens met de rechtermuisknop op de schuifbalk en kies Formaatcontrole vanuit het contextmenu, in de pop-out Object opmaken dialoogvenster onder het Controle tab, stel de relatieve waarden in en specificeer de gekoppelde cel zoals hieronder afgebeeld:
6. En klik dan OK knop om het dialoogvenster te sluiten. Typ nu de volgende twee formules afzonderlijk in cel B14 en B15 en maak ze vervolgens op als percentagewaarden:
B15: =IFERROR(INDEX($C$2:$C$11,IFERROR(MATCH($B$14,$C$2:$C$11,1),0)+1),1)
7. En nu moet u twee hulpkolommen naast de originele gegevens maken en de onderstaande twee formules invoeren in cel D2 en E2:
E2: =IF($B$15<C2,B2,NA())
8. Kopieer vervolgens de formules naar andere cellen zoals in het volgende screenshot wordt getoond:
9. Selecteer na het aanmaken van de gegevens de gegevens in kolom A, kolom C, kolom D, kolom E door de Ctrl toets en klik vervolgens op Invoegen > Kolom- of staafdiagram invoegen > Geclusterde kolom, en een grafiek wordt ingevoegd zoals onderstaand screenshot getoond:
10. Selecteer vervolgens het diagram en klik op Design > Wijzig het diagramtype, in de Wijzig het diagramtype dialoogvenster onder het Alle grafieken tab, klik Combo in het linkerdeelvenster en klik vervolgens op Aangepaste combinatiegrafiek, dan in de Kies het diagramtype en de as voor uw gegevensreeks specificeer de volgende bewerkingen voor elke gegevensreeks:
- Cumulatief%: Lijndiagramtype en vink het Secundaire as selectievakje ook;
- Gemarkeerde balken: Geclusterd kolomdiagramtype;
- Resterende staven: Geclusterd kolomdiagramtype.
11. Dan klikken OK knop om het dialoogvenster te sluiten, en je krijgt een grafiek zoals hieronder getoond screenshot:
12. Klik vervolgens met de rechtermuisknop op de percentage-as en kies vervolgens Formaat Axis optie, in het uitgevouwen Formaat Axis deelvenster, onder het Axis-opties tabblad, naast het maximaal, zet het nummer op 1.0 in het tekstvak, zie screenshot:
13. En klik vervolgens met de rechtermuisknop op een staafreeks in de grafiek en kies Formatteer gegevensreeksen, in de Formatteer gegevensreeksen deelvenster, onder het Serie-opties tab, voer in 100% in de Series overlappen tekstvak, zie screenshot:
14. Eindelijk kunt u de schuifbalk onder de grafiek verplaatsen en deze formule invoeren: = "Doel" & TEXT (B14; "0%") in een cel onder de schuifbalk om de doelpercentagewaarde te krijgen, zie screenshot:
15. Nu is de dynamische pareto-grafiek gemaakt, u kunt de vulkleur van de gemarkeerde balk naar behoefte wijzigen en wanneer u de doelwaarde wijzigt met behulp van de schuifbalk, wordt de pareto-grafiek automatisch bijgewerkt zoals hieronder wordt getoond:
Download het voorbeeldbestand van het Pareto-diagram
Video: maak een Pareto-diagram in Excel
De beste tools voor kantoorproductiviteit
Kutools for Excel - Helpt u zich te onderscheiden van de menigte
Kutools voor Excel beschikt over meer dan 300 functies, Ervoor zorgen dat wat u nodig heeft slechts één klik verwijderd is...
Office-tabblad - Schakel lezen en bewerken met tabbladen in Microsoft Office in (inclusief Excel)
- Een seconde om te schakelen tussen tientallen geopende documenten!
- Verminder elke dag honderden muisklikken voor u, zeg maar dag tegen muishand.
- Verhoogt uw productiviteit met 50% bij het bekijken en bewerken van meerdere documenten.
- Brengt efficiënte tabbladen naar Office (inclusief Excel), net als Chrome, Edge en Firefox.
