Stängel-Blatt-Diagramm Excel – Vorlage Download

In diesem Tutorial wird gezeigt, wie Sie ein Stängel-und-Blatt-Diagramm in allen Versionen von Excel erstellen können: 2007, 2010, 2013, 2016 und 2019.

Eine Stamm-und-Blatt-Darstellung (auch Stemplot genannt) ist ein Diagramm, das es Ihnen ermöglicht, die Verteilung eines bestimmten Datensatzes schnell zu beurteilen. Im Grunde genommen teilt das Diagramm zweistellige Zahlen in zwei Hälften:

• Stängel – Die erste Ziffer
• Blätter – Die zweite Ziffer

Betrachten Sie zum Beispiel das folgende Diagramm. Das Diagramm zeigt die Altersverteilung einer kleinen Population. Der Stängel (schwarz, y-Achse) zeigt die erste Ziffer des Alters, während die roten Datenpunkte die zweite Ziffer zeigen. Sie können schnell erkennen, dass es sechs Personen in den Zwanzigern gibt, was die zweitgrößte Altersgruppe ist.

 

 

 

 

Das Diagramm wird jedoch nicht von Excel unterstützt, d. h. Sie müssen es manuell von Grund auf erstellen. Schauen Sie sich unser Chart Creator Add-In an, ein Tool, mit dem Sie mit wenigen Klicks beeindruckende fortschrittliche Excel-Diagramme erstellen können.

In diesem Tutorial lernen Sie Schritt für Schritt, wie Sie ein dynamisches Stängel-Blatt-Diagramm in Excel von Grund auf erstellen können.

Erste Schritte

Zur Veranschaulichung: Nehmen wir an, Sie haben 24 Datenpunkte, die das Alter Ihrer Kunden enthalten. Um zu visualisieren, welche Altersgruppen sich von der Masse abheben, erstellen Sie ein Stammdiagramm:

 

 

Die Technik, die Sie gleich lernen werden, ist auch dann anwendbar, wenn Ihr Datensatz Hunderte von Werten enthält. Aber um sie anzuwenden, müssen Sie zunächst die Grundlagen schaffen. Also, fangen wir gleich an.

Schritt #1: Sortieren Sie die Werte in aufsteigender Reihenfolge.

Sortieren Sie zunächst Ihre eigentlichen Daten in aufsteigender Reihenfolge.

1. Wählen Sie eine beliebige Zelle innerhalb des Datensatzbereichs (A2:A25).
2. Gehen Sie auf die Registerkarte Daten.
3. Klicken Sie auf die Schaltfläche „Sortieren„.
4. Sortieren Sie in jedem Dropdown-Menü nach den folgenden Kriterien:
   1. Wählen Sie für „Spalte“ „Kundenalter“ (Spalte A).
   2. Wählen Sie für „Sortieren nach“ die Option „Zellenwerte“.
   3. Wählen Sie für „Reihenfolge“ die Option „Nach der Größe (aufsteigend)„.

 

 

Schritt #2: Richten Sie eine Hilfstabelle ein.

Sobald die Datenspalte sortiert ist, richten Sie eine separate Hilfstabelle ein, in der alle Diagrammdaten wie folgt gespeichert werden:

 

 

Ein paar Worte zu jedem Element der Tabelle:

• Stängel (Spalte C) – Dieser enthält die erste Ziffer aller Altersangaben.
• Blatt (Spalte D) – Dies enthält die zweite Ziffer aller Altersangaben.
• Blattposition (Spalte E) – Diese Hilfsspalte dient der Positionierung der Blätter im Diagramm.

Schritt #3: Ermitteln Sie die Stammwerte.

Berechnen Sie zunächst die Stammwerte(Spalte C) mithilfe der Funktionen LINKS und WERT. Mit der Funktion LINKS, die die angegebene Anzahl von Zeichen ab dem Anfang einer Zelle zurückgibt, können wir die erste Ziffer aus jedem Wert extrahieren, während die Funktion WERT die Formelausgabe als Zahl formatiert (das ist entscheidend). Geben Sie diese Formel in Zelle C2 ein:

=WERT(LINKS(A2,1))

 

Sobald Sie den ersten Stammwert gefunden haben, ziehen Sie den Füllgriff an den unteren Rand der Spalte, um die Formel für die übrigen Zellen (C3:C25) auszuführen.

Schritt #4: Finden Sie die Blatt-Werte.

Der nächste Schritt besteht darin, die Werte für die Blatt-Spalte(Spalte D) zu ermitteln, indem die letzte Ziffer jeder Zahl aus der ursprünglichen Datenspalte (Spalte A) gezogen wird. Zum Glück kann die Funktion RECHTS die Drecksarbeit für Sie erledigen. Geben Sie die folgende Funktion in Zelle D2 ein:

=RECHTS(A2,1)

 

Sobald Sie die Formel in der Zelle haben, ziehen Sie sie über die restlichen Zellen (D3:D25).

Schritt #5: Ermitteln Sie die Werte für die Blattposition.

Da ein Streudiagramm zum Aufbau der Stängel-und-Blatt-Darstellung verwendet wird, müssen Sie jedem Blatt mit Hilfe der Funktion ZÄHLENWENN eine Zahl zuweisen, die seine Position im Diagramm angibt, damit alles an seinem Platz bleibt. Geben Sie diese Formel in die Zelle E2 ein:

=ZÄHLENWENN($C$2:C2,C2)

Im Klartext: Die Formel vergleicht jeden einzelnen Wert in der Spalte Stamm(Spalte C) miteinander, um doppelte Vorkommen zu erkennen und zu markieren, so dass den Blättern, die einen gemeinsamen Stamm haben, eindeutige Kennungen zugewiesen werden.

 

 

Kopieren Sie die Formel auch in die übrigen Zellen (E3:E25).

Schritt #6: Erstellen Sie ein XY-Streudiagramm.

Sie haben nun alle Puzzleteile zusammengetragen, die zur Erstellung eines Streudiagramms erforderlich sind. Setzen wir sie nun zusammen.

1. Markieren Sie alle Werte in den Spalten Stängel und Blattposition, indem Sie die Datenzellen in Spalte C auswählen und dann die Strg-Taste gedrückt halten, während Sie die Datenzellen in Spalte E auswählen, wobei Sie die Zellen der Kopfzeile (C2:C25 und E2:E25) auslassen. HINWEIS: Die Spalte D wird zu diesem Zeitpunkt nicht ausgewählt.
2. Wechseln Sie zur Registerkarte Einfügen.
3. Klicken Sie auf das Symbol „Punkt (X Y) oder Blasendiagramm einfügen„.
4. Wählen Sie „Punkt“.

 

 

Schritt #7: Ändern Sie die X- und Y-Werte.

Positionieren Sie nun die horizontale Achse, die für die vertikale Darstellung der Stämme verantwortlich ist. Klicken Sie mit der rechten Maustaste auf das Diagramm und wählen Sie „Daten auswählen“ aus dem angezeigten Menü.

 

 

Klicken Sie dann auf die Schaltfläche „Bearbeiten„.

 

 

Dort müssen Sie die X- und Y-Werte manuell ändern:

1. Wählen Sie für „Werte der Reihe X“ alle Werte aus der Spalte Blattposition (E2:E25).
2. Markieren Sie für „Werte der Reihe Y“ alle Werte aus der Spalte Stängel (C2:C25).
3. Klicken Sie auf „OK“ für das Dialogfeld Datenreihe bearbeiten.
4. Klicken Sie auf „OK“ für das Dialogfeld „Datenquelle auswählen“.

 

 

Schritt #8: Ändern Sie die vertikale Achse.

Nachdem Sie das Diagramm neu angeordnet haben, müssen Sie es erneut umdrehen, um die Stängel in aufsteigender Reihenfolge zu sortieren. Klicken Sie mit der rechten Maustaste auf die vertikale Achse und wählen Sie „Achse formatieren“.

 

 

Führen Sie dann die folgenden einfachen Schritte aus:

1. Navigieren Sie zur Registerkarte “ Achsenoptionen „.
2. Ändern Sie den Wert Maximum auf „6„, da die größte Zahl im Datensatz 60 ist.
3. Setzen Sie den Wert Hauptstriche auf „1″
4. Aktivieren Sie das Kontrollkästchen „Werte in umgekehrter Reihenfolge„.

 

 

Schritt #9: Hinzufügen und Ändern der Achsenmarkierungen.

Die Häkchen entlang der vertikalen Achse werden als Trennzeichen zwischen den Stängeln und den Blättern verwendet. Ohne den Arbeitsbereich „Achse formatieren“ zu schließen, blättern Sie nach unten zum Abschnitt “ Teilstriche“ und wählen Sie neben „Haupttyp“ die Option „Innen“.

 

 

Heben Sie die Häkchen hervor, indem Sie ihre Farbe und Breite ändern.

1. Navigieren Sie zur Registerkarte “ Füllung & Linie „.
2. Klicken Sie unter Linie auf das Symbol „Füllfarbe„, um die Farbpalette zu öffnen, und wählen Sie Schwarz.
3. Setzen Sie die Stärke auf „3 pt„

 

 

Sie können nun die horizontale Achse und die Gitternetzlinien entfernen. Klicken Sie einfach mit der rechten Maustaste auf jedes Element und wählen Sie „Löschen“. Vergrößern Sie außerdem die Schriftart der Stammnummern und machen Sie sie fett, damit sie besser zu sehen sind (wählen Sie die vertikale Achse aus und navigieren Sie zu Startseite > Schriftart).

Schritt #10: Fügen Sie Datenbeschriftungen hinzu.

Wenn Sie sich der Ziellinie nähern, fügen wir dem Diagramm nun die Blätter hinzu. Klicken Sie dazu mit der rechten Maustaste auf einen Punkt, der die Reihe „Reihe 1“ darstellt, und wählen Sie „Datenbeschriftungen hinzufügen“.“

 

 

Schritt #11: Datenbeschriftungen anpassen.

Entfernen Sie dort die Standardbeschriftungen und fügen Sie stattdessen die Werte aus der Spalte Blatt (Spalte D) ein. Klicken Sie mit der rechten Maustaste auf eine Datenbeschriftung und wählen Sie „Datenbeschriftungen formatieren“.

 

 

Wenn das Aufgabenfenster erscheint, führen Sie einige einfache Schritte aus:

1. Wechseln Sie zur Registerkarte Beschriftungsoptionen.
2. Aktivieren Sie das Kontrollkästchen „Wert aus Zellen„.
3. Markieren Sie alle Werte in der Spalte „Blatt“(D2:D25).
4. Klicken Sie auf „OK„
5. Deaktivieren Sie das Kontrollkästchen „Y-Wert„.
6. Deaktivieren Sie das Kontrollkästchen „Führungslinien anzeigen„.

 

 

Ändern Sie die Farbe und Schriftgröße der Blätter, um sie von den Stängeln zu unterscheiden, und vergessen Sie nicht, sie auch fett zu machen (Startseite > Schrift).

Schritt #12: Blenden Sie die Datenmarkierungen aus.

Die Datenmarker (die Punkte) haben Ihnen gute Dienste geleistet, aber Sie brauchen sie nicht mehr, es sei denn, sie dienen zur Positionierung der Datenbeschriftungen. Machen wir sie also transparent. Klicken Sie mit der rechten Maustaste auf einen beliebigen Punkt, der die Reihe „Reihe 1“ darstellt, und wählen Sie „Datenreihen formatieren“.

 

 

Sobald sich das Aufgabenfenster öffnet, gehen Sie wie folgt vor:

1. Klicken Sie auf das Symbol „Füllung & Linie„.
2. Wechseln Sie auf die Registerkarte “ Markierung“.
3. Wählen Sie unter „Markierungsoptionen“ die Option „Keine“.„

 

 

Und vergessen Sie nicht, den Titel des Diagramms zu ändern.

Schritt #13: Fügen Sie die Achsentitel hinzu.

Verwenden Sie die Achsentitel, um beide Elemente des Diagramms zu beschriften.

1. Wählen Sie das Diagrammplot aus.
2. Gehen Sie auf die Registerkarte Diagrammentwurf.
3. Klicken Sie auf „Diagrammelement hinzufügen“.„
4. Wählen Sie „Achsentitel“.„
5. Wählen Sie „Primäre Horizontale“ und „Primäre Vertikale“.

 

 

Wie Sie sehen können, überlagern die Achsentitel die Diagrammfläche. Um das Problem zu beheben, wählen Sie das Diagramm aus und passen Sie die Griffe an, um die Größe des Diagrammbereichs zu ändern. Sie können nun die Achsentitel ändern.

 

 

Schritt #14: Fügen Sie ein Textfeld hinzu.

Fügen Sie schließlich ein Textfeld mit einer Taste hinzu, um die Ablesbarkeit des Stenogramms zu erleichtern.

1. Wählen Sie das Diagramm aus.
2. Gehen Sie auf die Registerkarte Einfügen.
3. Wählen Sie in der Gruppe Text die Option „Textfeld“.

 

 

Geben Sie in das Textfeld ein Beispiel aus Ihren Daten ein, um die Funktionsweise des Diagramms zu erklären