Erstellen Sie ein Pareto-Diagramm in Excel

In Excel besteht ein Pareto-Diagramm aus einem Säulen- und einem Liniendiagramm, wobei die Säulen die Häufigkeitswerte in absteigender Reihenfolge darstellen und die Linie die kumulativen Summen auf einer sekundären Achse anzeigt. Es wird verwendet, um die wesentlichen Bereiche von Defekten in einem Produkt zu analysieren und Verbesserungen zu ermitteln, die den Wert eines Unternehmens steigern. Um ein Pareto-Diagramm zu erstellen, das die häufigsten Gründe für Fehler oder Produktdefekte in einem Excel-Arbeitsblatt anzeigt, wird dieser Artikel Ihnen helfen.

• Erstellen eines einfachen Pareto-Diagramms in Excel 2016 und neueren Versionen
• Erstellen eines einfachen Pareto-Diagramms in Excel 2013 und älteren Versionen
• Erstellen eines dynamischen Pareto-Diagramms in Excel
• Beispieldatei für Pareto-Diagramm herunterladen
• Video: Pareto-Diagramm in Excel erstellen

Erstellen eines einfachen Pareto-Diagramms in Excel 2016 und neueren Versionen

Wenn Sie Excel 2016 oder neuere Versionen verwenden, gibt es eine integrierte Funktion – Pareto-Diagramm, mit der Sie schnell und einfach ein Pareto-Diagramm einfügen können. Gehen Sie wie folgt vor:

1. Wählen Sie den Datenbereich aus, auf dessen Grundlage Sie ein Pareto-Diagramm erstellen möchten.

2. Klicken Sie dann auf Einfügen > Statistisches Diagramm einfügen > Pareto, siehe Screenshot:

3. Ein Pareto-Diagramm wurde sofort erstellt, wie im folgenden Screenshot zu sehen:

4. Nun können Sie den Diagrammtitel bearbeiten und Datenbeschriftungen hinzufügen, wie im Screenshot zu sehen:

Erstellen eines einfachen Pareto-Diagramms in Excel 2013 und älteren Versionen

Wenn Sie Excel 2013 oder ältere Versionen verwenden, sollten Sie die folgenden Schritte nacheinander ausführen:

Zuerst bereiten Sie die Daten für die Erstellung des Pareto-Diagramms vor:

1. Sortieren Sie die Daten in absteigender Reihenfolge, indem Sie Zelle B2 auswählen und dann auf Daten > Absteigend sortieren (Z bis A) klicken, siehe Screenshot:

2. Der Datenbereich wurde nun in absteigender Reihenfolge sortiert, siehe Screenshot:

3. Berechnen Sie als Nächstes die kumulative Anzahl, indem Sie diese Formel =B2 in Zelle C2 eingeben und die Eingabetaste drücken. Siehe Screenshot:

4. Geben Sie anschließend diese Formel =C2+B3 in Zelle C3 ein und ziehen Sie den Ausfüllkursor nach unten in die Zellen, siehe Screenshots:

5. Nachdem Sie die kumulative Anzahl erhalten haben, berechnen Sie weiter den kumulativen Prozentsatz, indem Sie diese Formel: =C2/$C$11 in Zelle D2 eingeben und den Ausfüllkursor nach unten in die benötigten Zellen ziehen, siehe Screenshot:

6. Konvertieren Sie dann die Dezimalwerte in Prozentwerte, indem Sie die Formelzellen auswählen und auf Start > Prozentformat klicken, siehe Screenshot:

Als Zweites erstellen Sie das Diagramm basierend auf den Daten

7. Nachdem Sie die Daten vorbereitet haben, wählen Sie die Daten in Spalte A, Spalte B und Spalte D mithilfe der Strg-Taste aus, siehe Screenshot:

8. Klicken Sie dann auf Einfügen > Säulen- oder Balkendiagramm einfügen > Gruppiertes Säulendiagramm, siehe Screenshot:

9. Und Sie erhalten ein Diagramm wie im folgenden Screenshot gezeigt:

10. Klicken Sie dann mit der rechten Maustaste auf einen roten Balken (Kumulativer Prozentsatz) und wählen Sie im Kontextmenü „Diagrammtyp der Reihe ändern“, siehe Screenshot:

11. Im Dialogfeld „Diagrammtyp ändern“ klicken Sie unter dem Tab „Alle Diagramme“ im linken Bereich auf die Option „Kombination“. Wählen Sie im Listenfeld „Diagrammtyp und Achse für Ihre Datenreihen“ aus der Dropdown-Liste des kumulativen Felds den Diagrammtyp „Linie mit Markierungen“ und aktivieren Sie das Kontrollkästchen, siehe Screenshot:

12. Klicken Sie dann auf die Schaltfläche OK, und Sie erhalten ein Diagramm wie im folgenden Screenshot gezeigt:

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

14. Im Bereich „Achse formatieren“ unter dem Tab „Achsenoptionen“ setzen Sie neben „Maximum“ die Zahl auf 1,0 im Textfeld, und das Pareto-Diagramm wurde erfolgreich erstellt, siehe Screenshot:

15. Zum Schluss können Sie den Diagrammtitel ändern und Datenbeschriftungen hinzufügen, wie Sie es benötigen.

Erstellen eines dynamischen Pareto-Diagramms in Excel

In diesem Abschnitt werde ich darüber sprechen, wie man ein dynamisches Pareto-Diagramm mithilfe einer Bildlaufleiste erstellt. Wenn Sie den Zielwert durch Klicken oder Bewegen der Bildlaufleiste ändern, hebt das Diagramm automatisch den Balken der Probleme hervor, wie in der folgenden Demo gezeigt.

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

1. Berechnen Sie zunächst den kumulativen Prozentsatz in Spalte C, indem Sie diese Formel in Zelle C2 eingeben:

2. Ziehen Sie dann den Ausfüllkursor nach unten in die Zellen und formatieren Sie die Dezimalzahlen als Prozentwerte, siehe Screenshot:

3. Als Nächstes benötigen Sie drei Hilfszellen: Zielwert, Kumulativer Wert, Bildlaufleisten-Link-Wert, um einige Berechnungen durchzuführen, wie im folgenden Screenshot gezeigt:

4. Nachdem Sie die drei Zellen erstellt haben, klicken Sie auf Entwickler > Einfügen > Bildlaufleiste (Formular-Steuerelement), und zeichnen Sie eine Bildlaufleiste wie in den folgenden Screenshots gezeigt:

5. Klicken Sie dann mit der rechten Maustaste auf die Bildlaufleiste und wählen Sie im Kontextmenü „Steuerelement formatieren“. Im erscheinenden Dialogfeld „Objekt formatieren“ setzen Sie unter dem Tab „Steuerelement“ die entsprechenden Werte und geben die verknüpfte Zelle wie im folgenden Screenshot gezeigt an:

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

7. Erstellen Sie nun zwei Hilfsspalten neben den Originaldaten und geben Sie die folgenden beiden Formeln in Zelle D2 und E2 ein:

8. Kopieren Sie die Formeln dann in die anderen Zellen, wie im folgenden Screenshot gezeigt:

9. Nachdem Sie die Daten erstellt haben, wählen Sie die Daten in Spalte A, Spalte C, Spalte D und Spalte E aus, indem Sie die Strg-Taste gedrückt halten, klicken Sie dann auf Einfügen > Säulen- oder Balkendiagramm einfügen > Gruppiertes Säulendiagramm, und ein Diagramm wird wie im folgenden Screenshot gezeigt eingefügt:

10. Wählen Sie dann das Diagramm aus und klicken Sie auf Design > Diagrammtyp ändern. Im Dialogfeld Diagrammtyp ändern klicken Sie unter dem Tab Alle Diagramme im linken Bereich auf Kombination und dann auf Benutzerdefinierte Kombinationsdiagramm. Geben Sie im Abschnitt Diagrammtyp und Achse für Ihre Datenreihen die folgenden Operationen für jede Datenreihe an:

• Kumulative %: Liniendiagrammtyp, und aktivieren Sie auch das Kontrollkästchen Sekundäre Achse;
• Hervorgehobene Balken: Gruppiertes Säulendiagramm;
• Verbleibende Balken: Gruppiertes Säulendiagramm.

11. Klicken Sie dann auf die Schaltfläche OK, um das Dialogfeld zu schließen, und Sie erhalten ein Diagramm wie im folgenden Screenshot gezeigt:

12. Klicken Sie dann mit der rechten Maustaste auf die Prozentachse und wählen Sie im erweiterten Bereich „Achse formatieren“. Im Bereich „Achse formatieren“ unter dem Tab „Achsenoptionen“ setzen Sie neben „ Maximum“ die Zahl auf 1,0 im Textfeld, siehe Screenshot:

13. Klicken Sie dann mit der rechten Maustaste auf eine beliebige Balkenreihe im Diagramm und wählen Sie „Datenreihe formatieren“. Im Bereich „Datenreihe formatieren“ unter dem Tab „Reihenoptionen“ geben Sie 100% in das Textfeld „Reihenüberlappung“ ein, siehe Screenshot:

14. Zum Schluss können Sie die Bildlaufleiste unter das Diagramm verschieben und diese Formel eingeben: ="Ziel "&TEXT(B14;"0%") in eine Zelle unter der Bildlaufleiste, um den Zielprozentsatzwert zu erhalten, siehe Screenshot:

15. Das dynamische Pareto-Diagramm wurde nun erstellt. Sie können die Füllfarbe des hervorgehobenen Balkens nach Bedarf ändern, und wenn Sie den Zielwert über die Bildlaufleiste ändern, wird das Pareto-Diagramm automatisch aktualisiert, wie in der folgenden Demo gezeigt:

Beispieldatei für Pareto-Diagramm herunterladen

Video: Pareto-Diagramm in Excel erstellen