Inhoud
Je hebt misschien gehoord van de Pareto-wet of het 20/80-principe. Aan het einde van de 19e eeuw ontdekte de Italiaanse socioloog en econoom Vilfredo Pareto dat de verdeling van rijkdom in de samenleving ongelijk is en onderhevig aan een zekere afhankelijkheid: bij een toename van de welvaart neemt het aantal rijken exponentieel af met een constante coëfficiënt ( onder Italiaanse huishoudens was 80% van het inkomen in 20% van de gezinnen). Later werd dit idee ontwikkeld in zijn boek door Richard Koch, die de formulering van het universele "Principe 20/80" voorstelde (20% van de inspanningen levert 80% van het resultaat op). In de praktijk wordt deze wet meestal niet in zulke mooie cijfers uitgedrukt (lees “The Long Tail” door Chris Anderson), maar toont duidelijk de ongelijke verdeling van middelen, winsten, kosten, etc.
In bedrijfsanalyses wordt vaak een Pareto-diagram gebouwd om deze oneffenheid weer te geven. Het kan gebruikt worden om bijvoorbeeld visueel te laten zien welke producten of klanten de meeste winst opleveren. Het ziet er meestal zo uit:
De belangrijkste kenmerken:
- Elke blauwe kolom van het histogram vertegenwoordigt de winst voor het product in absolute eenheden en is uitgezet langs de linkeras.
- De oranje grafiek geeft het cumulatieve winstpercentage weer (dwz het aandeel in de winst op cumulatieve basis).
- Op een voorwaardelijke grens van 80% wordt voor de duidelijkheid meestal een horizontale drempellijn getekend. Alle goederen links van het snijpunt van deze lijn met de grafiek van de geaccumuleerde winst brengen ons 80% van het geld, alle goederen rechts - de resterende 20%.
Laten we eens kijken hoe u zelf een Pareto-diagram in Microsoft Excel kunt bouwen.
Optie 1. Een eenvoudig Pareto-diagram op basis van kant-en-klare gegevens
Als de brongegevens naar u toe kwamen in de vorm van een vergelijkbare tabel (dat wil zeggen, al in voltooide vorm):
… dan doen we het volgende.
Sorteer de tabel in aflopende volgorde van winst (tab Gegevens – Sorteren) en voeg een kolom toe met de formule voor het berekenen van het geaccumuleerde winstpercentage:
Deze formule deelt de totale geaccumuleerde winst vanaf het begin van de lijst tot het huidige item door de totale winst voor de hele tabel. We voegen ook een kolom toe met een constante van 80% om een horizontale drempellijn in de toekomstige grafiek te maken:
We selecteren alle gegevens en bouwen een regelmatig histogram op het tabblad Invoegen – Histogram (Invoegen – Kolomdiagram). Het zou er ongeveer zo uit moeten zien:
De percentagereeksen in de resulterende grafiek moeten langs de secundaire (rechter) as worden verzonden. Om dit te doen, moet u de rijen met de muis selecteren, maar dit kan moeilijk zijn, omdat ze moeilijk te zien zijn tegen de achtergrond van grote winstkolommen. Het is dus beter om de vervolgkeuzelijst op het tabblad te gebruiken om te markeren Layout or Formaat:
Klik vervolgens met de rechtermuisknop op de geselecteerde rij en selecteer de opdracht Formatteer gegevensreeksen en in het venster dat verschijnt, selecteer de optie Op de secundaire as (secundaire as). Als resultaat ziet ons diagram er als volgt uit:
Voor de series Accumulated Profit Share en Threshold moet u het grafiektype wijzigen van kolommen naar lijnen. Om dit te doen, klikt u op elk van deze rijen en selecteert u het commando Wijzig serie grafiektype.
Het enige dat overblijft is om de horizontale rij Drempel te selecteren en deze zo op te maken dat deze eruitziet als een scheidingslijn in plaats van gegevens (dwz de markeringen verwijderen, de lijn rood maken, enz.). Dit alles kan worden gedaan door met de rechtermuisknop op de rij te klikken en de opdracht te selecteren Formatteer gegevensreeksen. Nu zal het diagram zijn definitieve vorm aannemen:
Volgens het kunnen we concluderen dat 80% van de winst wordt gebracht door de eerste 5 goederen, en alle andere goederen rechts van de aardappel vertegenwoordigen slechts 20% van de winst.
In Excel 2013 kunt u het nog eenvoudiger doen - gebruik het nieuwe ingebouwde combo-diagramtype onmiddellijk bij het plotten:
Optie 2: draaitabel en pareto-draaigrafiek
Wat te doen als er geen kant-en-klare gegevens voor constructie zijn, maar alleen de originele ruwe informatie? Laten we aannemen dat we in het begin een tabel hebben met verkoopgegevens zoals deze:
Om er een Pareto-diagram op te bouwen en erachter te komen welke producten het beste verkopen, moet je eerst de brongegevens analyseren. De eenvoudigste manier om dit te doen is met een draaitabel. Selecteer een cel in de brontabel en gebruik de opdracht Invoegen – Draaitabel (Invoegen – Draaitabel). In het tussenliggende venster dat verschijnt, verander niets en klik op OKen sleep vervolgens in het paneel dat aan de rechterkant verschijnt de brongegevensvelden van de boven- naar de onderkant van de lay-out van de toekomstige draaitabel:
Het resultaat moet een samenvattende tabel zijn met de totale opbrengst voor elk product:
Sorteer het in aflopende volgorde van inkomsten door de actieve cel in de kolom te zetten Bedrag in het veld Opbrengst en met behulp van de sorteerknop От Я до А (Van Z tot A) tab Data.
Nu moeten we een berekende kolom met geaccumuleerde rente-inkomsten toevoegen. Om dit te doen, sleept u het veld opnieuw Inkomsten naar het gebied Values in het rechterdeelvenster om een dubbele kolom in de spil te krijgen. Klik vervolgens met de rechtermuisknop op de gekloonde kolom en kies opdracht Aanvullende berekeningen – % van het lopende totaal in het veld (Gegevens weergeven als – % lopend totaal in). Selecteer in het venster dat verschijnt het veld Naam, waarop het percentage van de omzet zich van boven naar beneden zal ophopen. De uitvoer zou er als volgt uit moeten zien:
Zoals je kunt zien, is dit bijna een kant-en-klare tabel uit het eerste deel van het artikel. Alleen ontbreekt voor volledig geluk een kolom met een drempelwaarde van 80% voor het construeren van een grenslijn in een toekomstig diagram. Zo'n kolom kan eenvoudig worden toegevoegd met behulp van een berekend veld. Markeer een willekeurig nummer in de samenvatting en klik vervolgens op het tabblad Home – Invoegen – Berekend veld (Home – Invoegen – Berekend veld). Voer in het geopende venster de veldnaam en de formule in (in ons geval een constante):
Na het klikken op OK een derde kolom zal aan de tabel worden toegevoegd met een waarde van 80% in alle cellen, en deze zal uiteindelijk de gewenste vorm aannemen. Dan kun je het commando Draaigrafiek (draaigrafiek) tab parameters (Opties) or Analyse (Analyse) en stel de grafiek op precies dezelfde manier in als de eerste optie:
Belangrijkste producten uitlichten
Om de meest beïnvloedende factoren te markeren, dwz kolommen die zich links van het snijpunt van de oranje geaccumuleerde rentecurve met de horizontale afkaplijn van 80% bevinden, kunnen worden gemarkeerd. Om dit te doen, moet u een andere kolom aan de tabel toevoegen met de formule:
Deze formule geeft als resultaat 1 als het product zich links van het snijpunt bevindt en 0 als het rechts is. Dan moet je het volgende doen:
- We voegen een nieuwe kolom toe aan de grafiek - de gemakkelijkste manier om dit te doen is door eenvoudig te kopiëren, dwz kolom markeren achtergrondverlichting, kopieer het (CTRL + C), selecteer het diagram en voeg (Ctrl + V).
- Selecteer de toegevoegde rij en verwissel deze langs de secundaire as, zoals hierboven beschreven.
- Seriegrafiektype achtergrondverlichting veranderen naar kolommen (histogram).
- We verwijderen de speling aan de zijkant in de eigenschappen van de rij (klik met de rechtermuisknop op de rij Verlichting – Rij-indeling – Zijopening) zodat de kolommen samensmelten tot één geheel.
- We verwijderen de randen van de kolommen en maken de vulling doorschijnend.
Als resultaat krijgen we zo'n mooi hoogtepunt van de beste producten:
PS
Vanaf Excel 2016 is het Pareto-diagram toegevoegd aan de standaardset Excel-diagrammen. Om het nu te bouwen, selecteert u gewoon het bereik en op het tabblad Invoegen (Invoegen) kies het juiste type:
Eén klik – en het diagram is klaar:
- Een rapport maken met behulp van een draaitabel
- Berekeningen instellen in draaitabellen
- Wat is er nieuw in grafieken in Excel 2013
- Wikipedia-artikel over de wet van Pareto