Inhalte
Sie haben vielleicht schon vom Pareto-Gesetz oder dem 20/80-Prinzip gehört. Ende des 19. Jahrhunderts entdeckte der italienische Soziologe und Ökonom Vilfredo Pareto, dass die Vermögensverteilung in der Gesellschaft ungleichmäßig ist und einer gewissen Abhängigkeit unterliegt: Mit zunehmendem Vermögen nimmt die Zahl der Reichen exponentiell mit einem konstanten Koeffizienten ab ( in italienischen Haushalten entfielen 80 % des Einkommens auf 20 % der Familien). Später wurde diese Idee in seinem Buch von Richard Koch entwickelt, der die Formulierung des universellen „Prinzips 20/80“ vorschlug (20 % der Bemühungen ergeben 80 % des Ergebnisses). In der Praxis drückt sich dieses Gesetz meist nicht in so schönen Zahlen aus (lesen Sie „The Long Tail“ von Chris Anderson), zeigt aber deutlich die ungleiche Verteilung von Ressourcen, Gewinnen, Kosten etc.
In der Geschäftsanalyse wird häufig ein Pareto-Diagramm erstellt, um diese Ungleichmäßigkeit darzustellen. Damit kann beispielsweise visuell dargestellt werden, welche Produkte oder Kunden den größten Gewinn bringen. Normalerweise sieht es so aus:
Seine Hauptmerkmale:
- Jede blaue Spalte des Histogramms stellt den Gewinn für das Produkt in absoluten Einheiten dar und wird entlang der linken Achse aufgetragen.
- Der orangefarbene Graph stellt den kumulativen Prozentsatz des Gewinns dar (dh den Gewinnanteil auf kumulierter Basis).
- An einer bedingten Grenze von 80 % wird zur Verdeutlichung normalerweise eine horizontale Schwellenlinie gezogen. Alle Waren links vom Schnittpunkt dieser Linie mit dem Diagramm des kumulierten Gewinns bringen uns 80 % des Geldes, alle Waren rechts – die restlichen 20 %.
Sehen wir uns an, wie Sie selbst ein Pareto-Diagramm in Microsoft Excel erstellen.
Option 1. Ein einfaches Pareto-Diagramm basierend auf vorgefertigten Daten
Wenn die Quelldaten in Form einer ähnlichen Tabelle (dh bereits in fertiger Form) zu Ihnen gekommen sind:
… dann machen wir folgendes.
Sortieren Sie die Tabelle nach absteigendem Gewinn (Tab Daten – Sortierung) und fügen Sie eine Spalte mit der Formel zur Berechnung des kumulierten Prozentsatzes des Gewinns hinzu:
Diese Formel dividiert den kumulierten Gesamtgewinn vom Beginn der Liste bis zum aktuellen Element durch den Gesamtgewinn für die gesamte Tabelle. Wir fügen auch eine Spalte mit einer Konstante von 80 % hinzu, um im Zukunftsdiagramm eine gestrichelte horizontale Schwellenlinie zu erstellen:
Wir wählen alle Daten aus und erstellen ein regelmäßiges Histogramm auf der Registerkarte Einfügen – Histogramm (Einfügen – Säulendiagramm). Es sollte ungefähr so aussehen:
Die Prozentreihe im resultierenden Diagramm sollte entlang der sekundären (rechten) Achse gesendet werden. Dazu müssen Sie die Zeilen mit der Maus auswählen, was jedoch schwierig sein kann, da sie vor dem Hintergrund großer Gewinnspalten schwer zu erkennen sind. Daher ist es besser, die Dropdown-Liste auf der Registerkarte zum Hervorheben zu verwenden Layout or Format:
Klicken Sie dann mit der rechten Maustaste auf die ausgewählte Zeile und wählen Sie den Befehl aus Datenreihen formatieren und wählen Sie im angezeigten Fenster die Option aus Auf der Sekundärachse (Secondary Axis). Als Ergebnis sieht unser Diagramm wie folgt aus:
Für die Serien „Accumulated Profit Share“ und „Threshold“ müssen Sie den Diagrammtyp von „Spalten“ in „Linien“ ändern. Klicken Sie dazu auf jede dieser Zeilen und wählen Sie den Befehl aus Ändern Sie den Seriendiagrammtyp.
Es bleibt nur noch, die horizontale Zeile Schwellenwert auszuwählen und sie so zu formatieren, dass sie eher wie eine Abschneidelinie als wie Daten aussieht (dh die Markierungen entfernen, die Linie rot gestrichelt machen usw.). All dies kann durch Rechtsklick auf die Zeile und Auswahl des Befehls erfolgen Datenreihen formatieren. Jetzt nimmt das Diagramm seine endgültige Form an:
Demnach können wir schlussfolgern, dass 80 % des Gewinns von den ersten 5 Waren gebracht werden und alle anderen Waren rechts von der Kartoffel nur 20 % des Gewinns ausmachen.
In Excel 2013 können Sie es sogar noch einfacher machen – verwenden Sie den neuen integrierten Combo-Diagrammtyp sofort beim Plotten:
Option 2: PivotTable und Pivot-Pareto-Diagramm
Was tun, wenn keine vorgefertigten Daten für die Konstruktion vorhanden sind, sondern nur die ursprünglichen Rohdaten? Nehmen wir an, dass wir am Anfang eine Tabelle mit Verkaufsdaten wie diese haben:
Um darauf ein Pareto-Diagramm aufzubauen und herauszufinden, welche Produkte sich am besten verkaufen, müssen Sie zunächst die Quelldaten analysieren. Am einfachsten geht das mit einer Pivot-Tabelle. Wählen Sie eine beliebige Zelle in der Quelltabelle aus und verwenden Sie den Befehl Einfügen – Pivot-Tabelle (Einfügen – Pivot-Tabelle). Im erscheinenden Zwischenfenster nichts ändern und klicken OK, ziehen Sie dann im rechts angezeigten Bereich die Quelldatenfelder vom oberen in den unteren Bereich des Layouts der zukünftigen Pivot-Tabelle:
Das Ergebnis sollte eine zusammenfassende Tabelle mit dem Gesamtumsatz für jedes Produkt sein:
Sortieren Sie es in absteigender Reihenfolge des Umsatzes, indem Sie die aktive Zelle auf die Spalte setzen Betrag im Feld Umsatz und über die Schaltfläche Sortieren От Я до А (Von Z bis A) Tab Datum.
Jetzt müssen wir eine berechnete Spalte mit kumulierten Zinserträgen hinzufügen. Ziehen Sie dazu das Feld erneut Einnahmen zum Gebiet Werte im rechten Bereich, um eine doppelte Spalte im Pivot zu erhalten. Klicken Sie dann mit der rechten Maustaste auf die geklonte Spalte und wählen Sie Befehl Zusätzliche Berechnungen – % der laufenden Summe im Feld (Daten anzeigen als – % laufende Summe in). Wählen Sie im angezeigten Fenster das Feld aus Name und Vorname, auf dem sich der Prozentsatz des Umsatzes von oben nach unten ansammelt. Die Ausgabe sollte wie in dieser Tabelle aussehen:
Wie Sie sehen können, ist dies fast eine fertige Tabelle aus dem ersten Teil des Artikels. Zum vollen Glück fehlt nur noch eine Spalte mit einem Schwellwert von 80% zur Konstruktion einer Cut-off-Linie in einem Zukunftsdiagramm. Eine solche Spalte kann einfach mit einem berechneten Feld hinzugefügt werden. Markieren Sie eine beliebige Zahl in der Zusammenfassung und klicken Sie dann auf die Registerkarte Start – Einfügen – Berechnetes Feld (Start – Einfügen – Berechnetes Feld). Geben Sie im sich öffnenden Fenster den Feldnamen und seine Formel (in unserem Fall eine Konstante) ein:
Nach dem Klicken auf OK Eine dritte Spalte wird der Tabelle mit einem Wert von 80 % in allen Zellen hinzugefügt und nimmt schließlich die gewünschte Form an. Dann können Sie den Befehl verwenden Pivot-Diagramm (Pivot-Diagramm) Tab Parameter (Optionen) or Analyse (Analyse) und richten Sie das Diagramm genauso ein wie bei der ersten Option:
Hervorhebung wichtiger Produkte
Um die wichtigsten Einflussfaktoren hervorzuheben, können die Säulen links vom Schnittpunkt der orangefarbenen Zinskurve mit der horizontalen Cutoff-Linie von 80 % markiert werden. Dazu müssen Sie der Tabelle eine weitere Spalte mit der Formel hinzufügen:
Diese Formel gibt 1 aus, wenn sich das Produkt links vom Schnittpunkt befindet, und 0, wenn es sich rechts befindet. Dann müssen Sie Folgendes tun:
- Wir fügen dem Diagramm eine neue Spalte hinzu – am einfachsten geht das durch einfaches Kopieren, dh Spalte markieren Rücklicht, Kopiere es (Ctrl + C), wählen Sie das Diagramm aus und fügen Sie ein (Strg + V).
- Wählen Sie die hinzugefügte Zeile aus und tauschen Sie sie wie oben beschrieben entlang der sekundären Achse aus.
- Typ des Seriendiagramms Rücklicht zu Spalten wechseln (Histogramm).
- Wir entfernen den Seitenabstand in den Eigenschaften der Zeile (Rechtsklick auf die Zeile Beleuchtung – Reihenformat – Seitenabstand), sodass die Spalten zu einem Ganzen verschmelzen.
- Wir entfernen die Ränder der Säulen und machen die Füllung durchscheinend.
Als Ergebnis erhalten wir so ein schönes Highlight der besten Produkte:
PS
Ab Excel 2016 wurde das Pareto-Diagramm zum Standardsatz von Excel-Diagrammen hinzugefügt. Um es jetzt zu erstellen, wählen Sie einfach den Bereich und auf der Registerkarte aus Insert (Einfügen) Wählen Sie den passenden Typ:
Ein Klick – und das Diagramm ist fertig:
- So erstellen Sie einen Bericht mit einer Pivot-Tabelle
- Richten Sie Berechnungen in PivotTables ein
- Was ist neu in Diagrammen in Excel 2013
- Wikipedia-Artikel zum Pareto-Gesetz