Wie erstellt man ein gestapeltes gruppiertes Säulen-/Balkendiagramm in Excel?

Bei der professionellen Datenanalyse kann die Visualisierung mehrerer Kategorien und Datengruppen innerhalb eines einzigen Diagramms einen klareren Vergleich und eine effizientere Interpretation der Daten ermöglichen. Eine häufige Anforderung besteht darin, eine Kombination aus gestapelten und gruppierten Daten anzuzeigen, wie zum Beispiel Umsatzwerte nach Region und Quartal, gruppiert nebeneinander. Dieser Artikel führt Sie Schritt für Schritt durch die Erstellung eines gestapelten gruppierten Säulendiagramms in Excel, mit dem Sie individuelle Kategoriewerte als gruppierte Balken vergleichen können, während jedes Cluster aus gestapelten Segmenten besteht. Dieser Ansatz ermöglicht eine leistungsstarke Vergleichsanalyse zwischen Gruppen und über mehrere Variablen hinweg, wie im folgenden Screenshot dargestellt.

Erstellen eines gestapelten gruppierten Säulendiagramms in Excel

Um ein gestapeltes gruppiertes Säulendiagramm in Excel zu erstellen, ist es wichtig zu wissen, dass Excel diesen Diagrammtyp nicht nativ unterstützt. Der Effekt kann jedoch durch sorgfältige Vorbereitung der Daten und Anpassung des Diagrammlayouts simuliert werden.

✅ Was Sie zuerst wissen müssen:

• Excel bietet keinen integrierten Typ „gestapeltes gruppiertes Säulendiagramm“ an. Das Ergebnis wird durch Tricks bei der Datenanordnung erzielt.
• Sie müssen Ihre Quelldaten neu strukturieren, um Gruppierungen zu simulieren.
• Zwischen Kategoriegruppen werden leere Zeilen hinzugefügt, um jeden Cluster visuell zu trennen.

Gehen wir den Prozess schrittweise anhand von Beispieldaten zu Verkaufszahlen für Produkte über mehrere Quartale durch.

1. Organisieren Sie Ihre Rohdaten: In diesem Beispiel haben wir Produktnamen in Spalte A und Verkaufsdaten (z. B. Ist vs. Ziel für Q1 und Q2) in benachbarten Spalten. Das Ziel ist es, die Daten jedes Produkts nebeneinander zu gruppieren und die Ist/Ziel-Werte innerhalb jedes Clusters gestapelt anzuzeigen.

2. Restrukturieren der Daten: Kopieren Sie jede Gruppe von Daten (z. B. jede Produktzeile) in ein neues Layout und fügen Sie eine leere Zeile zwischen jeder Gruppe ein. Dies hilft Excel, jede Gruppe als separaten Cluster im gestapelten Säulendiagramm zu interpretieren.

3. Erstellen des Diagramms: Wählen Sie Ihre neu strukturierten Daten aus. Gehen Sie dann auf Einfügen > Säulen- oder Balkendiagramm > Gestapelte Säule.

4. Formatieren der Reihe: Klicken Sie mit der rechten Maustaste auf eine beliebige Säule im Diagramm und wählen Sie Datenreihe formatieren.

5. Reduzieren der Lückenbreite: Im Bereich Datenreihe formatieren gehen Sie zu Reihenoptionen und setzen Lückenbreite = 0 %, um jede Gruppe optisch in einen gestapelten Cluster zu komprimieren.

6. Legende und Layout anpassen: Klicken Sie mit der rechten Maustaste auf die Legende > Legende formatieren.

7. Legendenposition auswählen: Im Bereich Legende formatieren, unter den Legendenoptionen, wählen Sie die bevorzugte Legendenposition (rechts, oben, links oder unten), um das beste Layout für Ihr Diagramm zu finden und Überlappungen mit den Daten zu vermeiden.

✅ Ergebnis: Sie haben nun ein gestapeltes gruppiertes Säulendiagramm, in dem die Ist/Ziel-Daten jedes Produkts gruppiert und nebeneinander gestapelt sind, um einen schnellen Vergleich zu ermöglichen.

⚠️ Einschränkung: Diese Technik funktioniert gut für kleinere Datensätze. Für große Datensätze oder häufig sich ändernde Daten kann die manuelle Neustrukturierung fehleranfällig sein. Siehe die folgenden Abschnitte für VBA- und Formellösungen, um diesen Prozess zu automatisieren.

VBA-Code – Automatisieren der Neustrukturierung von Daten und Diagrammerstellung

Wenn Sie große Datensätze oder regelmäßig aktualisierte Berichte bearbeiten, kann das manuelle Neuanordnen von Daten zur Erstellung gestapelter gruppierter Diagramme mühsam und fehleranfällig sein. Mit VBA (Visual Basic for Applications) können Sie den gesamten Prozess automatisieren – von der Neustrukturierung der Quelltabelle bis zum Einfügen des Diagramms – was Zeit und Mühe spart.

Diese Methode ist am besten für Benutzer geeignet, die mit Excel-Makros vertraut sind oder in Umgebungen arbeiten, in denen VBA erlaubt ist. Besonders effektiv ist sie, wenn Ihre Diagrammstruktur gleich bleibt, aber die Daten regelmäßig aktualisiert werden. So implementieren Sie sie:

Schritt 1: Drücken Sie Alt + F11, um den VBA-Editor zu öffnen. Klicken Sie im Editor auf Einfügen > Modul.

Schritt 2: Fügen Sie den folgenden VBA-Code in das Modulfenster ein:

Sub CreateStackedClusteredChart()
    Dim ws As Worksheet
    Dim rngData As Range
    Dim chartObj As ChartObject
    Dim chartRange As Range
    Dim xTitleId As String

    On Error Resume Next
    Set ws = ActiveSheet
    xTitleId = "KutoolsforExcel"

    ' Prompt user to select original data
    Set rngData = Application.InputBox("Select the original grouped data (including all headers):", xTitleId, Selection.Address, Type:=8)
    If rngData Is Nothing Then Exit Sub

    ' Create new worksheet for reshaped data
    Dim wsChartData As Worksheet
    Set wsChartData = Worksheets.Add
    wsChartData.Name = "ChartData_" & Format(Now(), "hhmmss")

    Dim numRows As Long, numCols As Long, i As Long, j As Long, outRow As Long
    numRows = rngData.Rows.Count
    numCols = rngData.Columns.Count
    outRow = 1

    ' Add headers
    wsChartData.Cells(outRow, 1).Value = "Category"
    For j = 2 To numCols
        wsChartData.Cells(outRow, j).Value = rngData.Cells(1, j).Value
    Next j
    outRow = outRow + 1

    ' Copy data and insert blank rows
    For i = 2 To numRows
        For j = 1 To numCols
            wsChartData.Cells(outRow, j).Value = rngData.Cells(i, j).Value
        Next j
        outRow = outRow + 1
        If i < numRows Then
            wsChartData.Cells(outRow, 1).Value = ""
            outRow = outRow + 1
        End If
    Next i

    ' Define chart data range
    Set chartRange = wsChartData.Range(wsChartData.Cells(1, 1), wsChartData.Cells(outRow - 1, numCols))

    ' Insert chart
    Set chartObj = wsChartData.ChartObjects.Add(Left:=100, Top:=30, Width:=500, Height:=350)
    With chartObj.Chart
        .SetSourceData Source:=chartRange
        .ChartType = xlColumnStacked
        .HasTitle = True
        .ChartTitle.Text = "Stacked Clustered Column Chart"
        .Legend.Position = xlLegendPositionRight
        .ChartGroups(1).GapWidth = 0
    End With

    MsgBox "Chart generated successfully.", vbInformation, "KutoolsforExcel"
End Sub

Schritt 3: Drücken Sie Alt + F8, um das Makrodialogfeld zu öffnen. Wählen Sie CreateStackedClusteredChart und klicken Sie auf Ausführen.

Schritt 4: Wenn Sie dazu aufgefordert werden, wählen Sie Ihren ursprünglichen Datensatz (mit Überschriften aus). Das Makro generiert ein neues Arbeitsblatt mit eingefügten Leerzeilen und erstellt automatisch das gestapelte gruppierte Säulendiagramm.

📝 Tipps:

• Stellen Sie sicher, dass Ihre ursprüngliche Tabelle konsistente Spaltenüberschriften und Formatierungen hat.
• Sie können das Makro jederzeit neu ausführen, wenn sich Ihr Datensatz aktualisiert – keine Notwendigkeit, manuelle Schritte zu wiederholen.

✅ Vorteile: Zeitersparnis, genaues Layout, ideal für wiederkehrende Berichte.
⚠️ Nachteile: Benötigt makrofähiges Excel und grundlegende VBA-Kenntnisse.

Excel-Formel – Dynamisches Neustrukturieren von Daten für gestapelte gruppierte Diagramme

Wenn Sie VBA nicht verwenden möchten oder eine Lösung benötigen, die dynamische Diagrammupdates ermöglicht, wenn sich Ihre Rohdaten ändern, können Sie Formeln verwenden, um Ihre Quelldaten in das richtige Layout für gestapelte gruppierte Diagramme umzustrukturieren. Durch die Nutzung integrierter Funktionen wie INDEX, TRANSPOSE und Hilfsspalten können Sie einen Datenbereich erstellen, der immer die korrekte Struktur für Ihr Diagramm liefert, mit minimalem Aufwand.

Dieser Ansatz ist besonders praktisch, wenn Ihre Rohdaten regelmäßig aktualisiert werden (neue Zeiträume, Kategorien usw.) und Sie möchten, dass sich Ihr Diagramm automatisch anpasst, ohne manuelle Neustrukturierung. Die Hauptanforderung ist, einen „Hilfs“-Abschnitt zu erstellen, der Blöcke von Daten und Leerzeilen mithilfe von Formeln zieht und anordnet, sodass Ihre Diagrammquelle immer aktuell bleibt.

Hier ist ein Beispiel, wie Sie dies einrichten könnten:

• Angenommen, Ihre ursprünglichen Daten befinden sich in A1:D7 (mit A1 als oberer linker Header), strukturiert als Region/Kategorie in Spalte A und Unterkategorie-Werte (z. B. Q1, Q2, Q3) in den Spalten B, C, D.
• Sie möchten jede Kategorie als Cluster mit gestapelten Q-Werten anzeigen und Leerzeilen verwenden, um Cluster zu trennen.

1. Erstellen Sie in Ihrem neuen Blatt oder einem angrenzenden Bereich eine Hilfsstruktur, um jede Gruppe zu extrahieren und Leerzeilen einzufügen. Zum Beispiel, um die erste Datenzeile nach E2:G2 zu kopieren:

=INDEX($A$2:$D$7,INT((ROW()-2)/2)+1,COLUMN()-4+1)

Ziehen Sie diese Formel bei Bedarf nach unten. Um Leerzeilen zwischen Gruppen einzufügen, richten Sie eine WENN-Formel ein, um Leerzeichen ("") in alternativen Zeilen zurückzugeben:

=IF(ISODD(ROW()), "", INDEX($A$2:$D$7,ROW()/2,COLUMN()-4+1))

Verwenden Sie eine Kombination dieser Formeln mit sorgfältig strukturierten Bezügen, um Ihre neu strukturierten Daten auszugeben, einschließlich regelmäßiger Leerzeilen in regelmäßigen Abständen.

2. Sobald Ihr Transformationsbereich abgeschlossen ist (mit Stapeln und Clustern), wählen Sie diesen neuen Bereich aus und erstellen Sie Ihr gestapeltes Säulendiagramm entsprechend der ursprünglichen Methode (Einfügen > Gestapelte Säule). Das Diagramm wird nun automatisch alle Änderungen widerspiegeln, die Sie an der ursprünglichen Datentabelle vornehmen.

Für große Datensätze ist es oft hilfreich, die OFFSET-Funktion zu verwenden, um den Extraktionsprozess flexibler zu gestalten oder dynamische benannte Bereiche für die Definition der Diagrammquelle anzuwenden.

Vorteile: Kein VBA oder Makros erforderlich, ideal für Umgebungen mit eingeschränktem Skripting.
Nachteile: Komplexe Formeleinrichtung für große Datenmengen, möglicher Leistungsengpass bei sehr großen dynamischen Bereichen.

Fehlerbehebung: Wenn Ihr Diagramm nicht korrekt aktualisiert wird, überprüfen Sie auf Bezugsfehler oder Unstimmigkeiten in den Hilfsformeln. Stellen Sie sicher, dass Leerzeilen ordnungsgemäß eingefügt wurden, da diese entscheidend für das Erreichen des „gruppierten“ Aussehens sind.

Weitere relative Diagrammartikel:

---

Die besten Tools zur Büroproduktivität

Kutools für Excel löst die meisten Ihrer Probleme und steigert Ihre Produktivität um 80 %

• Super Formel-Leiste (F) (mehrere Zeilen Text und Formeln einfach bearbeiten); Lese-Layout (einfaches Lesen und Bearbeiten einer großen Anzahl von Zellen); Einfügen in gefilterte Bereiche...
• Zellen/Zeilen/Spalten zusammenführen und Daten behalten; Zellinhalt aufteilen; Doppelte Zeilen zusammenführen und Summe/Durchschnitt berechnen... Doppelte Zellen verhindern; Bereiche vergleichen...
• Doppelte oder eindeutige Zeilen auswählen; Leere Zeilen auswählen (alle Zellen sind leer); Super Suchen und unscharfes Suchen in vielen Arbeitsmappen; Zufällige Auswahl...
• Exakte Kopie mehrerer Zellen ohne Änderung der Formelreferenz; Automatisches Erstellen von Referenzen zu mehreren Blättern; Aufzählungszeichen, Kontrollkästchen und mehr einfügen...
• Formeln, Bereiche, Diagramme und Bilder favorisieren und schnell einfügen; Zellen mit Passwort verschlüsseln; Mailingliste erstellen und E-Mails senden...
• Text extrahieren, Text hinzufügen, Zeichen an einer bestimmten Position entfernen, Leerzeichen entfernen; Druckstatistik erstellen und drucken; Zwischen Zelleninhalt und Kommentaren konvertieren...
• Super Filter (Filter-Schemata speichern und auf andere Blätter anwenden); Erweiterte Sortierung nach Monat/Woche/Tag, Häufigkeit und mehr; Spezialfilter nach Fett, Kursiv...
• Arbeitsmappen und Arbeitsblätter konsolidieren; Tabellen basierend auf Schlüsselspalten zusammenführen; Daten auf mehrere Blätter aufteilen; Stapelkonvertierung von xls, xlsx und PDF...
• Gruppierung von PivotTables nach Kalenderwoche, Wochentag und mehr... Entsperre und gesperrte Zellen durch unterschiedliche Farben anzeigen; Zellen mit Formel/Name hervorheben...

Weiterlesen... Vollständige Funktionen 30-Tage-Testversion... Kaufen... 

---

Office Tab - bringt das Register-Interface ins Office und macht Ihre Arbeit viel einfacher

• Aktivieren Sie die Registerkarten-Bearbeitung und den Lese-Modus in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
• Öffnen und erstellen Sie mehrere Dokumente in neuen Tabs desselben Fensters, anstatt in neuen Fenstern.
• Steigert Ihre Produktivität um 50 % und reduziert Hunderte von Mausklicks täglich!

Weiterlesen... Vollständige Funktionen 30-Tage-Testversion... Kaufen...