Hoe maak je een Pareto-diagram in Excel

Deze zelfstudie laat zien hoe u een Pareto-diagram maakt in alle versies van Excel: 2007, 2010, 2013, 2016 en 2022.

Een Pareto-diagram is een hybride van een kolomdiagram en een lijndiagram dat het relatieve belang van de factoren of items in een bepaalde dataset samen met hun cumulatieve percentages aangeeft. De kaart dankt zijn naam aan het Pareto-principe, ook wel bekend als de wet van de vitale weinigen, die stelt dat ongeveer 20% van de oorzaken bijdraagt ​​aan 80% van de gevolgen.

Het doel van de grafiek is om de kritieke factoren in een dataset te onderscheiden en ze te onderscheiden van de onbeduidende.

Een Pareto-grafiek werd een native grafiektype in Excel 2016, maar voor gebruikers van Excel 2013 of oudere versies is de enige manier om de grafiek handmatig vanaf het begin op te bouwen.

In deze zelfstudie leert u hoe u een aanpasbare Pareto-grafiek kunt maken, zowel handmatig als met behulp van de ingebouwde grafiektool in Excel.

Beginnen

Om u de kneepjes van het vak te laten zien, hebben we wat ruwe gegevens nodig om mee te werken. Laten we er daarom van uitgaan dat u de uitsplitsing van de artikelen die naar een kledingwinkel zijn teruggestuurd, gaat analyseren, om erachter te komen welke productcategorieën de meeste problemen veroorzaken.

Houd daarbij rekening met de volgende gegevens:

Dus laten we er meteen in duiken.

Een Pareto-diagram maken in Excel 2016+

Laat me u eerst laten zien hoe u een Pareto-grafiek plot met behulp van het overeenkomstige ingebouwde Excel-grafiektype dat in Excel 2016 is geïntroduceerd.

Met deze methode kost het slechts een paar klikken om de grafiek in te stellen, maar u verliest enkele aanpassingsfuncties - u kunt bijvoorbeeld geen gegevenslabels of markeringen toevoegen aan de Pareto-regel.

Dus als u dat extra niveau van maatwerk nodig heeft, is het logisch om de grafiek vanaf de grond op te bouwen. Maar eerst is hier het algoritme voor diegenen die de gemakkelijkere route willen nemen.

Stap 1: Plot een Pareto-diagram.

Nogmaals, als u Excel 2016 of hoger gebruikt, kunt u met Excel een eenvoudig Pareto-diagram maken zonder een vinger op te tillen:

1. Markeer uw werkelijke gegevens (A1:B11).
2. Ga naar de Invoegen tabblad.
3. Klik "Statistiekdiagram invoegen.”
4. Kiezen "Pareto.”

Op magische wijze verschijnt er onmiddellijk een Pareto-diagram:

Technisch gezien kunnen we ermee stoppen, maar om de grafiek te helpen het verhaal te vertellen, moet u misschien wat extra werk verzetten.

Stap 2: Voeg gegevenslabels toe.

Begin met het toevoegen van gegevenslabels aan het diagram. Klik met de rechtermuisknop op een van de kolommen en selecteer "Voeg gegevenslabels toe.”

Pas de kleur, het lettertype en de grootte van de labels aan om ze te laten opvallen (Huis > Lettertype).

Stap 3: voeg de astitels toe.

Als kers op de taart bieden astitels extra context voor waar de grafiek over gaat.

1. Selecteer het grafiekgebied.
2. Schakel over naar de Ontwerp tabblad.
3. Druk op de "Grafiekelement toevoegen" knop.
4. Kies “Astitels” in het vervolgkeuzemenu.
5. Selecteer beide "Primair horizontaal" en "Primair verticaal.”

Stap 4: Voeg de laatste hand toe.

Terwijl we de grafiek blijven verfijnen, kunt u nu de rasterlijnen verwijderen die geen enkel praktisch doel dienen. Klik met de rechtermuisknop op de rasterlijnen en klik op "Verwijderen” om ze te wissen.

Wijzig ten slotte de as- en diagramtitels en het bijgewerkte Pareto-diagram is klaar!

Een Pareto-diagram maken in Excel 2007, 2010 en 2013

Dat was best snel. Gebruikers van versies vóór Excel 2016 hebben echter geen andere keuze dan al het werk zelf te doen. Maar er is een zilveren randje: op die manier krijg je veel meer speelruimte voor maatwerk.

Hoewel alle schermafbeeldingen zijn gemaakt in Excel 2022, blijft de methode die u gaat leren compatibel met versies terug naar Excel 2007. Volg gewoon en pas dienovereenkomstig aan.

Stap 1: Sorteer de gegevens in aflopende volgorde.

Sorteer om te beginnen de gegevens op het aantal geretourneerde items (kolom B) in aflopende volgorde.

1. Markeer uw werkelijke gegevens (A1:B11).
2. Ga naar de Gegevens tabblad.
3. Klik op de "Soort" knop.
4. In de Soort dialoogvenster, doet u het volgende:
   1. Voor "Kolom”, selecteer “Artikelen geretourneerd.”
   2. Voor "Sorteren op," Kiezen "Cel Waarden.”
   3. Voor "Volgorde, " kies "Grootste naar kleinste.”

Stap 2: Bereken de cumulatieve percentages.

Onze volgende stap is het maken van een hulpkolom met de naam "Cumulatief %” die de relatieve aandelen van de productcategorieën in de dataset optelt om te worden gebruikt als de waarden voor het in kaart brengen van de Pareto-lijn.

Voer de volgende formule in om de waarden te vinden: C2 en kopieer het naar beneden door de hendel in de rechterbenedenhoek van de cel naar beneden te slepen naar cel C11:

1	=SOM($B$2:B2)/SOM($B$2:$B$11)

Vergeet niet om de formule-uitvoer op te maken als percentages (Huis > Nummergroep > Percentage stijl).

Stap 3: Maak een geclusterd kolomdiagram.

Nadat de grafiekgegevens correct zijn gerangschikt, plot u een eenvoudig geclusterd kolomdiagram.

1. Markeer alle grafiekgegevens (A1:C11).
2. Ga naar de Invoegen tabblad.
3. Klik "Kolom- of staafdiagram invoegen.”
4. Selecteer "Geclusterde kolommen.”

Eenmaal daar zou de toekomstige Pareto-grafiek er als volgt uit moeten zien:

Stap 4: Maak een combografiek.

Nu is het tijd om de gegevensreeks om te draaien die de cumulatieve percentages illustreert (Serie “Cumulatief %”) in een lijndiagram.

Klik met de rechtermuisknop op een van de kleine oranje balken die Serie “Cumulatief %” en kies "Grafiektype serie wijzigen” in het menu dat verschijnt.

In de Grafiektype wijzigen dialoogvenster, zet u de geclusterde staafgrafiek om in een combinatiegrafiek:

1. Schakel over naar de combinatie tabblad.
2. Voor Serie "Cumulatief %", verandering "Grafiektype" tot "Lijn met markeringen” en controleer de “Secundaire as" doos.

Excel 2010 of oudere versies: In de Grafiektype wijzigen tabblad, ga naar de Lijn tabblad en selecteer "Lijn met markeringen.” Klik vervolgens met de rechtermuisknop op de regel die verschijnt, selecteer Gegevensreeks opmaken, en in de Serie-opties tabblad, kies "Secundaire as.”

Als je dat hebt gedaan, zou je combo-diagram er als volgt uit moeten zien:

Stap #5: Pas de schaal van de secundaire verticale as aan.

In een Pareto-diagram kunnen de lijndiagramwaarden nooit meer dan honderd procent bedragen, dus laten we de secundaire verticale asbereiken dienovereenkomstig aanpassen.

Klik met de rechtermuisknop op de secundaire verticale as (de nummers aan de rechterkant) en selecteer "Formaat as.”

Zodra de Formaat as taakvenster verschijnt, doet u het volgende:

1. Ga naar de Asopties
2. Verander de Maximale grenzen tot "1.”

Stap #6: Wijzig de spleetbreedte van de kolommen.

Maak de kolommen breder door de spleetbreedte van de bijbehorende gegevensreeks aan te passen (Serie "Te retourneren artikelen").

Om dat te doen, klikt u met de rechtermuisknop op een van de blauwe kolommen die Serie "Te retourneren artikelen" en klik op "Gegevensreeks opmaken.”

Volg dan een paar eenvoudige stappen:

1. Navigeer naar de Serie-opties tabblad.
2. Wijziging "Breedte spleet" tot "3%.”

Stap #7: Gegevenslabels toevoegen

Het is tijd om gegevenslabels toe te voegen voor zowel de gegevensreeks (Klik met de rechtermuisknop > Gegevenslabels toevoegen).

Zoals je misschien hebt gemerkt, zien de labels voor de Pareto-lijn er een beetje rommelig uit, dus laten we ze recht boven de lijn plaatsen. Om de labels te verplaatsen, klikt u met de rechtermuisknop op de gegevenslabels voor: Serie “Cumulatief %” en kies "Gegevenslabels opmaken.”

In de Gegevenslabels opmaken taakvenster, wijzigt u de positie van de labels door het volgende te doen:

1. Ga naar de Labelopties tabblad.
2. Onder "Labelpositie," Kiezen "Bovenstaand.”

U kunt ook de kleur en grootte van de labels wijzigen om ze van elkaar te onderscheiden.

Stap #8: Ruim de grafiek op.

Als we klaar zijn met het oppoetsen van de grafiek, moet u er ten slotte voor zorgen dat u de diagramlegenda en rasterlijnen verwijdert. Vergeet niet de astitels toe te voegen door de eerder getoonde stappen te volgen (Ontwerp > Grafiekelement toevoegen > Astitels > Primair horizontaal + Primair verticaal).

Uw verbeterde Pareto-grafiek is klaar!