Wie erstelle ich ein Pareto-Diagramm in Excel?

Eingestellt: 19.12.2007
Letzte Änderung: 09.03.2011


Keywords: Pareto-Prinzip • 80/20 Regel • Qualitätsmanagement

Verwandte Themen: ABC-Analyse • SummenhäufigkeitsfunktionWerthäufigkeitsverteilung • Säule-Linie-Diagramm


 

Zum Pareto-Diagramm

Das Pareto-Prinzip besagt, dass die meisten Auswirkungen eines Problems (80%) häufig nur auf eine kleine Anzahl von Ursachen (20%) zurückzuführen sind. Um eine Entscheidungshilfe zu erlangen, die Auskunft darüber gibt mit welcher Priorität welche Ursachen bekämpft werden sollen, sind dieser 80/20-Regel folgend die wichtigsten Ursachen ausfindig zu machen.

Nachstehende Anleitung zeigt eine Möglichkeit der Erstellung eines Pareto-Diagramms auf. Es handelt sich beim Pareto-Diagramm um ein kombiniertes Diagramm, wobei  auf der X-Achse die Fehlerarten (Ursachen, Fehlerquellen, Kategorien), auf der primären Y-Achse die absolute Häufigkeit sowie auf der sekundären Y-Achse die relative Häufigkeiten in Prozent abgetragen werden. Die Werte einer Fehlerart werden als Säulen, die kumulierten Werte als Linie (Summenkurve) dargestellt.

Es gibt zahlreiche Variationen eines Pareto-Diagramms, wovon hier zwei vorgestellt werden sollen. Zum Einen wird ein Pareto-Diagramm für das Kriterium "Fehlerhäufigkeit" erzeugt, bei dem die absolute und relative Fehlerhäufigkeit auf den Y-Achsen abgetragen wird, zum Anderen für das Kriterium "Kosten", bei dem die absoluten und relativen Gesamtkosten der Fehlerarten dargestellt werden.

 

Beispiel

Im Folgenden wird davon ausgegangen, dass die wichtigsten Fehlerarten und die zugehörigen absoluten Häufigkeiten bzw. Gesamtkosten bereits ermittelt (bspw. durch Erfahrungswerte, Schätzungen) und in wie im unten stehenden Beispiel in einer Fehlersammelliste festgehalten sind:

Die Daten zum Beispiel können Sie hier herunterladen:

Pareto-Diagramm


Beispiel Pareto-Diagramm: Fehlersammelliste

In Spalte A sind die Fehlerarten, in Spalte B die absolute Häufigkeit der jeweiligen Fehlerart und in Spalte C die Gesamtkosten pro Fehlerart aufgeführt. Zu beachten ist, dass Fehlerart H alle weiteren Fehlerarten umfasst und im Diagramm als letzte Kategorie dargestellt werden soll, auch wenn diese nicht die Kleinste ist.

 

Pareto-Diagramm für das Kriterium "Häufigkeit"

Zur Darstellung eines Pareto-Diagramms für das Kriterium "Häufigkeit" werden zunächst die Daten entsprechend folgender Arbeitstabelle aufbereitet:


Arbeitstabelle 01: Kriterium Fehlerhäufigkeit

In Spalte B finden sich die absoluten Häufigkeiten in absteigend sortierter Reihenfolge, wobei die Fehlerart H als letzte mit aufgeführt ist. Die zugehörigen Fehlerarten stehen in Spalte A, die kumulierten Fehlerhäufigkeiten in Prozent in Spalte C. Mit Hilfe der Fehlerindikatoren Y in den Spalten D und E werden im Pareto-Diagramm vertikale Linien hinzugefügt. Die Hilfszellen C15:E15 sind mit dem Fehlertyp #NV belegt.

Formeln in der Tabelle:
B16: =KGRÖSSTE($B$2:$B$8;ZEILE(A1))
Formel nach unten kopieren bis Zelle B22
B23: =B9
B24: =SUMME(B16:B23)
A16: =INDEX($A$2:$A$8;VERGLEICH(B16;$B$2:$B$8;0))
Formel nach unten kopieren bis Zelle A22
A23: =A9
C15: =NV()
C16: =SUMME($B$16:B16)/$B$24*100
Formel nach unten kopieren bis Zelle C23
D15: =NV()
D16: =100-C16
Formel nach unten kopieren bis Zelle D23
E15: =NV()
E16: =0
E17: =C16
Formel nach unten kopieren bis Zelle E23

Sofern sich für mehrere Fehlerarten die gleiche Anzahl an Fehlern ergeben (können) bzw. vorliegen, sind die Formeln in Spalte A zu ersetzen bspw. durch eine der beiden folgenden Arrayformeln:

A16: {=INDEX(A:A;KKLEINSTE(WENN(($B$2:$B$9=B16);ZEILE($2:$9));ZÄHLENWENN($B$16:B16;B16)))}
bzw.
A16: {=INDEX($A$2:$A$9;KKLEINSTE(WENN(($B$2:$B$9=B16);ZEILE($1:$8));ZÄHLENWENN($B$16:B16;B16)))}
Formel nach unten kopieren

Auf Grundlage der Arbeitstabelle 01 kann ein Pareto-Diagramm in Excel nun mit folgenden Schritten erstellt werden: 

Schritt 01: A15:B23 markieren / Diagramm / Diagrammtyp: Säule / Diagrammuntertyp: Gruppierte Säulen / Fertig stellen.

Schritt 02: Rechtsklick im Diagramm / Datenquelle / Datenreihe hinzufügen / Name: =C14 / Werte: =C15:C23 / OK.


Abbildung 01: Pareto-Diagramm "Fehlerhäufigkeit" nach Schritt 02

Schritt 03: Rechtsklick auf Datenreihe "kumulierte Häufigkeit in %" / Datenreihen formatieren / Achsen / Datenreihe zeichnen auf: Sekundärachse / OK.

Schritt 04: Doppelklick auf sekundäre (rechte) Größenachse (Y) / Skalierung / Maximum: 100 / Minimum: 0 / Hauptintervall: (bspw.) 20 / OK.

Schritt 05: Doppelklick auf primäre (linke) Größenachse (Y) / Skalierung / Maximum: 84 / Minimum: 0 / Hauptintervall: (bspw.) 10 / OK.

Bei Schritt 04 ist zu beachten, dass als Minimum 0 und als Maximum 100 gewählt wird. Ebenso ist bei Schritt 05 als Minimum 0 zu wählen sowie als Maximum die Summe der absoluten Fehlerhäufigkeiten. Dies entspricht dem Wert aus Zelle B24 (hier: 84).


Abbildung 02: Pareto-Diagramm "Fehlerhäufigkeit" nach Schritt 05

Schritt 06: Rechtsklick auf Datenreihe "kumulierte Häufigkeit in %" / Diagrammtyp: Linie / Diagrammuntertyp: Linie mit Datenpunkten / OK.

Schritt 07: Rechtsklick im Diagramm / Diagrammoptionen:
-    Achsen / sekundäre Rubrikenachse (X) aktivieren
-    Gitternetzlinien / Gitternetzlinien deaktivieren
OK.

Schritt 08: Doppelklick auf sekundäre (obere) Rubrikenachse (X):
-    Skalierung / Größenachse (Y) schneidet zwischen den Rubriken deaktivieren
-    Muster / Hauptstriche: Keine / Teilstrichbeschriftungen: Keine
OK.


Abbildung 03: Pareto-Diagramm "Fehlerhäufigkeit" nach Schritt 08

Schritt 09: Doppelklick auf Datenreihe "Anzahl":
-    Optionen / Abstandsbreite: 0
-    Datenbeschriftung / Wert aktivieren
OK.

Schritt 10: Doppelklick auf Datenreihe "kumulierte Häufigkeit in %":
-    Datenbeschriftung / Wert aktivieren
-    Fehlerindikator Y / Anpassen: / +: =D15:D23 / -: =E15:E23
OK.


Abbildung 04: Pareto-Diagramm "Fehlerhäufigkeit" nach Schritt 10

Schritt 11: Doppelklick auf Datenbeschriftung der Datenreihe "kumulierte Häufigkeit in %" (Linie) / Datenbeschriftungen formatieren / Zahlen: Benutzerdefiniert / Typ: (0"%") / OK.

Schritt 12: Doppelklick auf Fehlerindikatoren / Fehlerindikatoren formatieren / Muster / Benutzerdefiniert / gestrichelte Linie wählen / Markierung: ohne Balken wählen / OK.


Abbildung 05: Pareto-Diagramm "Fehlerhäufigkeit" nach Schritt 12

Schritt 13: Abschließende Formatierungen vornehmen.


Abbildung 06: Pareto-Diagramm "Fehlerhäufigkeit" nach Schritt 13

Um die Fehlerarten (Balken) farblich voneinander abzuheben, kann mit Doppelklick auf Datenreihe "Anzahl" / Datenreihen formatieren / Option / Punktfarbunterscheidung aktiviert werden.


Abbildung 07: Pareto-Diagramm "Fehlerhäufigkeit" mit Punktfarbunterscheidung

 

Pareto-Diagramm für das Kriterium "Kosten"

Bei der Erstellung eines Pareto-Diagramms für das Kriterium "Kosten" kann analog vorgegangen werden. Die für das betrachtete Beispiel zugehörige Arbeitstabelle lautet dann:


Arbeitstabelle 02: Kriterium Gesamtkosten

Formeln in der Tabelle:
B16: =KGRÖSSTE($C$2:$C$8;ZEILE(A1))
Formel nach unten kopieren bis Zelle B22
B23: =C9
B24: =SUMME(B16:B23)
A16: =INDEX($A$2:$A$8;VERGLEICH(B16;$C$2:$C$8;0))
Formel nach unten kopieren bis Zelle A22
A23: =A9
C15: =NV()
C16: =SUMME($B$16:B16)/$B$24*100
Formel nach unten kopieren bis Zelle C23
D15: =NV()
D16: =100-C16
Formel nach unten kopieren bis Zelle D23
E15: =NV()
E16: =0
E17: =C16

Sofern sich für mehrere Fehlerarten die gleichen Gesamtkosten ergeben (können) bzw. vorliegen, sind die Formeln in Spalte A zu ersetzen bspw. durch eine der beiden folgenden Arrayformeln:

A16: {=INDEX(A:A;KKLEINSTE(WENN(($C$2:$C$9=B16);ZEILE($2:$9));ZÄHLENWENN($B$16:B16;B16)))}
bzw.
A16: {=INDEX($A$2:$A$9;KKLEINSTE(WENN(($C$2:$C$9=B16);ZEILE($1:$8));ZÄHLENWENN($B$16:B16;B16)))}
Formel nach unten kopieren

Anschließend Schritte 01 bis 13 durchführen, wobei zu beachten ist, dass das Maximum der primären (linken) Größenachse (Y) auf 3.051 (Summe der Gesamtkosten, Wert in Zelle B24) zu skalieren ist, um eine korrekte Darstellung zu erzielen.


Abbildung 08: Pareto-Diagramm "Kosten" nach Schritt 13

 

Weitere Variationen des Pareto-Diagramms

In der Literatur finden sich weitere Variationen, bei denen bspw. nur  absolute oder nur relative Häufigkeiten verwendet werden. In diesen Fällen kann weitgehend wiederum oben beschriebene Anleitung verwendet und darüber hinaus die Beschriftungen der sekundäre Größenachse (Y) ausgeblendet werden mit Doppelklick auf sekundäre (rechte) Größenachse (Y) / Muster / Hauptstriche: Keine / Teilstrichbeschriftungen: Keine / OK.


Abbildung 09: Pareto-Diagramm "Fehlerhäufigkeit" nur relative Häufigkeiten

Im Diagramm der Abbildung 09 weisen beide Größenachsen (Y) ein Maximum von 100 auf, wobei die sekundäre (rechte) Größenachse (Y) ausgeblendet ist.


Abbildung 10: Pareto-Diagramm "Kosten" nur absolute Häufigkeiten

Im Diagramm der Abbildung 10 weisen beide Größenachsen (Y) ein Maximum von 3.051 auf, wobei die sekundäre (rechte) Größenachse (Y) ausgeblendet ist.

 

Haben Sie Fragen oder Anregungen zu diesem Artikel?

Dann senden Sie einfach eine Nachricht an:

E-Mail:

 

Lesen Sie auch:
___________________________________________________________________

Wie erstelle ich ein ABC-Diagramm? - ABC-Analyse in Excel