Erstellen Sie ein Pareto-Diagramm in Excel

In Excel besteht ein Pareto-Diagramm aus einem Säulen- und einem Liniendiagramm, wobei die Säulenbalken die Häufigkeitswerte in absteigender Reihenfolge darstellen und die Linie die kumulierten Summen auf einer Sekundärachse anzeigt. Es wird verwendet, um signifikante Fehlerbereiche eines Produkts zu analysieren und Verbesserungen zu ermitteln, die den Unternehmenswert steigern. Um ein Pareto-Diagramm zur Darstellung der häufigsten Fehlerursachen bzw. Produktmängel in einem Excel-Arbeitsblatt zu erstellen, hilft Ihnen dieser Artikel weiter.

• Erstellen Sie ein einfaches Pareto-Diagramm in Excel 2016 und neueren Versionen
• Erstellen Sie ein einfaches Pareto-Diagramm in Excel 2013 und früheren Versionen
• Erstellen Sie ein dynamisches Pareto-Diagramm in Excel
• Beispieldatei für Pareto-Diagramm herunterladen
• Video: Pareto-Diagramm in Excel erstellen

Erstellen Sie ein einfaches Pareto-Diagramm in Excel 2016 und neueren Versionen

Wenn Sie Excel 2016 oder eine neuere Version verwenden, steht Ihnen mit dem integrierten Pareto-Diagramm eine praktische Funktion zur Verfügung, mit der Sie im Handumdrehen ein professionelles Pareto-Diagramm erstellen können. Gehen Sie dazu wie folgt vor:

1. Markieren Sie den Datenbereich, auf dessen Basis Sie ein Pareto-Diagramm erstellen möchten.

2. Klicken Sie dann auf Einfügen > Statistikdiagramm einfügen > Pareto, siehe Abbildung:

3. Anschließend wurde sofort ein Pareto-Diagramm erstellt – so wie in der folgenden Abbildung dargestellt:

4. Anschließend können Sie den Diagrammtitel bearbeiten und bei Bedarf Datenbeschriftungen hinzufügen – siehe Abbildung:

Erstellen Sie ein einfaches Pareto-Diagramm in Excel 2013 und früheren Versionen

Wenn Sie Excel 2013 oder eine frühere Version verwenden, führen Sie bitte die folgenden Schritte nacheinander aus:

Bereiten Sie zunächst die Daten für die Erstellung des Pareto-Diagramms vor:

1. Sortieren Sie die Daten in absteigender Reihenfolge: Klicken Sie dazu auf Zelle B2 und anschließend auf Daten > Sortieren von Z bis A (siehe Abbildung):

2. Anschließend wurde der Datenbereich in absteigender Reihenfolge sortiert – siehe Abbildung:

3. Berechnen Sie nun die kumulierte Anzahl, indem Sie in Zelle C2 die Formel =B2 eingeben und die Eingabetaste drücken. Siehe Abbildung:

4. Geben Sie anschließend in Zelle C3 die Formel =C2+B3 ein und ziehen Sie den Ausfüllknauf bis zu den gewünschten Zellen – siehe Abbildungen:

5. Sobald Sie die kumulierte Anzahl haben, berechnen Sie den kumulierten Prozentsatz, indem Sie folgende Formel in Zelle D2 eingeben:=C2/$C$11 und den Ausfüllknauf bis zu den gewünschten Zellen ziehen – siehe Abbildung:

6. Konvertieren Sie anschließend die Dezimalwerte in Prozentwerte: Markieren Sie hierzu die Formelzellen und klicken Sie auf Start > Prozentformat, siehe Abbildung:

Erstellen Sie als Nächstes das Diagramm auf Grundlage der Daten

7. Markieren Sie nach der Datenvorbereitung die Daten in Spalte A, Spalte B und Spalte D, indem Sie gleichzeitig die Strg-Taste drücken – siehe Abbildung:

8. Klicken Sie anschließend auf Einfügen > Säulen- oder Balkendiagramm einfügen > Gruppierte Säulen, siehe Abbildung:

9. Sie erhalten anschließend ein Diagramm wie in der folgenden Abbildung gezeigt:

10. Klicken Sie anschließend mit der rechten Maustaste auf einen roten Balken (kumulierter Prozentsatz) und wählen Sie im Kontextmenü Diagrammtyp der Datenreihe ändern, siehe Abbildung:

11. Im Dialogfeld Diagrammtyp der Datenreihe ändern klicken Sie unter der Registerkarte Alle Diagramme im linken Bereich auf die Option Kombination. Wählen Sie im Listenfeld Wählen Sie den Diagrammtyp und die Achse für Ihre Datenreihen aus der Dropdown-Liste des Felds „Kumuliert“ den Typ Liniendiagramm mit Markern aus, und aktivieren Sie das Kontrollkästchen (siehe Abbildung):

12. Klicken Sie anschließend auf die Schaltfläche OK, und Sie erhalten ein Diagramm wie in der folgenden Abbildung gezeigt:

13. Klicken Sie danach mit der rechten Maustaste auf die Prozentachse und wählen Sie im Kontextmenü die Option Achse formatieren, siehe Abbildung:

14. Wechseln Sie im Bereich Achse formatieren zur Registerkarte Achsenoptionen und geben Sie neben dem Feld Maximum die Zahl 1,0 in das Textfeld ein. Damit wurde das Pareto-Diagramm erfolgreich erstellt – siehe Abbildung:

15. Abschließend können Sie den Diagrammtitel anpassen und bei Bedarf Datenbeschriftungen hinzufügen.

Erstellen Sie ein dynamisches Pareto-Diagramm in Excel

In diesem Abschnitt zeige ich Ihnen, wie Sie mithilfe eines Scrollbalkens ein dynamisches Pareto-Diagramm erstellen. Sobald Sie den Zielwert durch Klicken oder Verschieben des Scrollbalkens anpassen, hebt das Diagramm automatisch den Balken der entsprechenden Probleme hervor – wie in der folgenden Demo veranschaulicht.

Um dieses Pareto-Diagramm zu erstellen, gehen Sie Schritt für Schritt wie folgt vor:

1. Berechnen Sie zunächst den kumulierten Prozentsatz in Spalte C. Verwenden Sie dazu folgende Formel in Zelle C2:

2. Ziehen Sie anschließend den Ausfüllknauf bis zu den gewünschten Zellen, und formatieren Sie die Dezimalzahlen als Prozentwerte – siehe Abbildung:

3. Als Nächstes benötigen Sie drei Hilfszellen mit den Bezeichnungen „Zielwert“, „Kumulierter Wert“ und „Scrollbalken-Verknüpfungswert“, um einige Berechnungen durchzuführen, wie in der folgenden Abbildung gezeigt:

4. Nachdem Sie die drei Zellen erstellt haben, klicken Sie auf Entwicklertools > Einfügen > Scrollleiste (Formularsteuerelement) und zeichnen Sie eine Scrollleiste wie in den folgenden Abbildungen gezeigt:

5. Klicken Sie danach mit der rechten Maustaste auf die Scrollleiste und wählen Sie im Kontextmenü Steuerelement formatieren. Legen Sie im erscheinenden Dialogfeld Objekt formatieren unter der Registerkarte Steuerelement die entsprechenden Werte fest und geben Sie die verknüpfte Zelle wie in der folgenden Abbildung gezeigt an:

6. Klicken Sie anschließend auf die Schaltfläche OK, um das Dialogfeld zu schließen. Geben Sie nun die folgenden beiden Formeln jeweils in die Zellen B14 und B15 ein, und formatieren Sie diese als Prozentwerte:

7. Erstellen Sie jetzt zwei Hilfsspalten direkt neben Ihren Originaldaten und tragen Sie in die Zellen D2 und E2 jeweils die folgenden Formeln ein:

8. Anschließend kopieren Sie die Formeln wie in der folgenden Abbildung gezeigt in die übrigen Zellen:

9. Markieren Sie nach dem Erstellen der Daten gleichzeitig die Inhalte in Spalte A, Spalte C, Spalte D und Spalte E, während Sie die Strg-Taste gedrückt halten. Klicken Sie anschließend auf Einfügen > Säulen- oder Balkendiagramm einfügen > Gruppierte Säulen, und es wird ein Diagramm wie in der folgenden Abbildung eingefügt:

10. Wählen Sie anschließend das Diagramm aus und klicken Sie auf Entwurf > Ändern Diagrammtyp. Im Ändern Diagrammtyp-Dialogfeld wechseln Sie zur Registerkarte Alle Diagramme, wählen im linken Bereich Kombiniert aus und klicken dann auf Benutzerdefiniertes Kombinationsdiagramm. Anschließend legen Sie im Abschnitt Wählen Sie den Diagrammtyp und die Achse für Ihre Datenreihe für jede Datenreihe die folgenden Einstellungen fest:

• Kumuliertes %: Linie Diagrammtyp, und aktivieren Sie auch die SekundärachseKontrollkästchen;
• Hervorgehobene Balken: Gruppiertes Säulendiagramm-Typ;
• Verbleibende Balken: Gruppiertes Säulendiagramm-Typ.

11. Klicken Sie anschließend auf die Schaltfläche OK, um das Dialogfeld zu schließen. Das Ergebnis sehen Sie im folgenden Screenshot:

12. Klicken Sie anschließend mit der rechten Maustaste auf die Prozentachse und wählen Sie die Option Achse formatieren. Im geöffneten Bereich Achse formatieren wechseln Sie zur Registerkarte Achsenoptionen und tragen Sie im Textfeld neben Maximum die Zahl 1,0 ein (siehe Screenshot):

13. Klicken Sie danach mit der rechten Maustaste auf eine beliebige Balkenreihe im Diagramm und wählen Sie Datenreihe formatieren. Im Bereich Datenreihe formatieren wechseln Sie zur Registerkarte Reihenoptionen und geben Sie 100 % in das Textfeld Überlappung der Reihen ein (siehe Screenshot):

14. Zuletzt verschieben Sie die Bildlaufleiste unter dem Diagramm und geben diese Formel ein:=„Target "&TEXT(B14,"0 %") in eine Zelle unter der Bildlaufleiste, um den gewünschten Prozentwert zu erhalten (siehe Screenshot):

15. Das dynamische Pareto-Diagramm ist jetzt erstellt. Sie können die Füllfarbe des hervorgehobenen Balkens jederzeit anpassen. Sobald Sie den Zielwert mithilfe der Bildlaufleiste ändern, aktualisiert sich das Pareto-Diagramm automatisch – wie in der folgenden Demo gezeigt:

Beispieldatei für Pareto-Diagramm herunterladen

Video: Erstellen eines Pareto-Diagramms in Excel