Der Diagrammassistent von Excel bietet bereits einsatzfähige Vorlagen für gestapelte und verteilte Säulen- und Balkendiagramme. Dabei entsteht bei vielen Nutzern aber die Frage, wie ein gemischtes Diagramm erstellt werden kann, das die Darstellungsformen gestapelt und verteilt miteinander kombiniert.

Im folgenden Tutorial zeige ich, wie sich ein solches kombiniertes Diagramm mit standardmäßig in Excel integrierten Routinen bewerkstelligen lässt. Diese Routinen sind bereits in Excel-Versionen vor 2003 abrufbar. Die Methode ist etwas verschachtelt und die Datenbasis muss passend aufbereitet werden, um das gewünschte Ergebnis zu erhalten. Außerdem kann die Platzierung der Achsenbeschriftungen etwas kniffliger ausfallen.

Säulen- und Balkendiagramme nach Vorlage

Für das Tutorial warden wir der untenstehende Datensatz verwenden. Es werden Budget und tatsächliche Werte dreier Konsumgüter miteinander verglichen, über einen Zeitraum von zwei Quartalen des Jahres. Es soll sich um ein verteiltes Diagramm handeln, wobei die Quartale auf die Kategorien aufgeteilt werden. So entspricht dann also jeweils ein Balken/ eine Säule einem Quartal. Jeder dieser Balken soll die Daten für Budget und tatsächliche Ausgaben in gestapelter Form zeigen.

Die Erstellung des verteilten Diagramms stellt zunächst keine hohen Ansprüche, immerhin bietet Excel hierfür komplett verwendungsfertige Vorlagen. Auch für die Erstellung eines reinen Stapel-Diagramms existieren Vorlagen, entsprechend einfach fällt die Erstellung.

Excel bietet allerdings keine auf den ersten Blick ersichtliche Möglichkeit, um diese beiden Diagrammtypen zu kombinieren. Die in diesem Tutorial vorgestellte Vorgehensweise beinhaltet zunächst das Hinzufügen leerer Zellen und Spalten zu einem Datenbereich, der für ein verteiltes oder gestapeltes Diagramm erstellt wurde. Im Ergebnis werden dem Diagramm Zwischenräume hinzugefügt, die keine Daten enthalten. Werden diese Zwischenräume richtig angeordnet, bilden Sie die passende optische Trennung zwischen den gestapelten Säulen oder Balken.

Beginnen wir mit dem Diagramm

Es empfiehlt sich, nicht direkt die Originaldaten zu manipulieren und stattdessen einen Bearbeitungsbereich zu etablieren, der mit den Originaldaten verknüpft ist.

Dies ist nicht nur aus Gründen der Auditierung zu empfehlen, sondern auch weil wir die Daten in Zukunft wohl in ähnlichem Format erhalten werden, was uns dann beim aktulisieren des Diagramms Arbeit erspart!

Der Bearbeitungsbereich, der als Datenquelle für das Diagramm angelegt wird, kann später auch verborgen oder durch das Diagramm verdeckt werden.

Hier die Excel Datei mit den Beispieldaten und auch dem fertigen Diagramm zum anschauen:

 Das Tutorial wird direkt mit der Erstellung eines Diagramms beginnen. So kann im weiteren Verlauf Schritt für Schritt beobachtet werden, wie die Änderungen an der Datenbasis das Diagramm beeinflussen.

In der Praxis würden wir natürlich zuerst die komplett aufbereitete Datenbasis erstellen und dann erst das Diagramm erzeugen, was viel zeitaufwändiger ware.

Erstellen wir jetzt also ein gestapeltes Säulen- oder Balkendiagramm.

Zuerst markieren wir den Bereich B7:E10 und verwenden die entsprechende Vorlage aus dem Diagrammassistenten. Es wurden keine Kategorienamen ausgewählt, Excel behilft sich deswegen mit Standardwerten und nummeriert die Kategorien von 1 bis 3.

Hinweis: In diesem Tutorial sollen jeweils die folgenden 4 Werte übereinander gestapelt werden: Q1 Ausgaben, Q2 Ausgaben / Q1 Budget, Q2 Budget. Die drei Säulen oder Balken entsprechen dann den Produkten Kaffee, Milch und Zucker. Je nach Excel-Version kann es vorkommen, dass der Auto-Diagramm-Assistent die Zuteilung umgekehrt vornimmt. In dem Fall würden wir auf das Diagramm ein rechts Klick machen und die Funktion Daten auswählen klicken. Dann nutzen wir die Funktion Zeile/Spalte wechseln. Alternativ kann man dieselbe Funktion auch unter der Registerkarte Diagramm/Entwurf im Funktionsbereich Daten (versionsabhängig) finden. Die korrekte Zuordnung erkennen wir daran, dass 3 Säulen/Balken vorhanden sind, nicht 4.

Was uns am automatisch erstellten Balkendiagramm (rechtes Bild) noch nicht gefällt, ist die Anordnung, die Excel vornimmt.

Excel trägt die Kategorien in ihrer Reihenfolge bei automatisch erstellten Balkendiagrammen immer von unten nach oben ein. Wir wollen aber eine Anordnung von oben nach unten.

Die Anordnung kann durch die Formatierung der vertikalen Achse verändert werden. Rufen wir dazu mit einem Rechtsklick auf die vertikale Achse “Achse formatieren” auf. Im neuen Dialogfenster aktivieren wir die Optionen Kategorien in umgekehrter Reihenfolge und Horizontale Achse schneidet Bei größter Rubrik.

Anpassung der Datenbasis

Im Moment haben wir nicht mehr vor uns als ein gestapeltes Diagramm, deshalb soll jetzt die Datenbasis bearbeitet werden. Dazu fügen wir jetzt einige Zeilen in die Datenbasis ein, welche dann im Diagramm sofort übernommen werden.

Das Ergebnis ist schnell an den Diagrammen ersichtlich: Äquivalent zu den Leerzeilen entstehen Zwischenräume, die die Säulen/Balken weiter verteilen.

Wir wollen jetzt je die Budget-Daten aufeinanderstapeln. Sie sollen dazu jeweils in eine Zeile geschrieben werden. Das erwartete Ergebnis lautet, dass die Budget-Anteile aus den bisherigen Säulen/Balken entfernt werden und auf die entstandenen freien Plätze wandern.

Das wird allerdings nicht ganz automatisch von statten gehen. Zunächst muss Excel noch einige Anweisungen erhalten, genauer gesagt der Datenbereich muss erneut zugewiesen werden.

Öffnen wir deshalb mit einem Rechtsklick auf dem Diagramm das zugehörige Kontextmenü und klicken wieder auf “Daten auswählen” (in machen Excel-Versionen auch “Datenquelle bearbeiten” genannt). Dann beginnen wir damit, dem Wert Diagrammdatenbereich Daten zuzuweisen, indem wir über die Auswahlfunktion den Bereich B7 bis E15 markieren, also die komplette neu definierte Datenbasis wie im Bild oben zu sehen.

Für eine bessere Darstellung könnte man auch in Betracht ziehen, dass vor dem ersten und nach dem letzten Säulen- oder Balkenelement Zwischenräume platziert werden. Dazu sind einfach entsprechende Leerzeilen in die Datenbasis einzufügen. Danach muss Excel wieder wie eben schon aufgeführt über den neuen Diagrammdatenbereich informiert werden.

Um das Layout eines verteilten Diagrammes zu erreichen, müssen jetzt noch die Zwischenräume zwischen den Säulen/Balken reduziert werden.

Öffnen wir dazu einfach das Kontextmenü durch einen Rechtsklick auf eines der Säulenelemente und wählen Sie die Datenreihen formatieren aus. In dem Dialog, der sich nun öffnet, setzen wir den Wert Abstandsbreite auf 0 %.

In der Praxis würde man mit der vollständigen Erstellung der Datenbasis anfangen. Danach erfolgt erst die Erstellung des Diagramms. So muss man nicht nach jeder Manipulation der Datenbasis das Diagramm neu anpassen.

Im nächsten Schritt versehen wir das Diagramm mit den passenden Achsenbeschriftungen wie unten beschrieben.

Hinzufügen der Achsenbeschriftungen

Wir sind nun schon fast beim Endergebnis angelangt. Jetzt geht es noch darum, die vorhandenen Säulen/Balken und Elemente aussagekräftig zu beschriften. Dazu werden wir eine Hilfsdatenreihe an die Sekundärachse anfügen, die dieser Achse die gewünschten Beschriftungen verleiht (Im Beispiel in Spalte F angetragen).

Markieren wir nacheinander F1:F4 und A1:A4 (Strg halten, um mehrere Bereiche zu markieren). Darauf achetn, wirklich für beide markierten Blöcke die Zeilen 1 bis 4 zu überspannen, denn nur mit gleicher Zeilenzahl entstehen die richtigen Bezüge. Kopieren wir jetzt die Auswahl und fügen sie über die Einfügen-Option als neue Datenreihe in das Diagramm mit ein.

In Excel 2003 oder früheren Versionen bleiben die ursprünglichen Achsenbeschriftungen erhalten (1, 2, 3 …). Ab Excel 2007 werden diese Beschriftungen jedoch automatisch durch die neu hinzugefügten ersetzt, selbst wenn im Einfügen-Dialog (versionsspezifisch) die Option Bestehende Kategorien ersetzen nicht aktiviert wurde.

Im Diagramm selbst macht sich die Datenreihe jedoch nicht direkt bemerkbar, schließlich wurden Ihr durchgehend der Wert Null zugeordnet. Um noch einmal zu demonstrieren, wie die neue Datenreihe hinzugefügt wurde, zeigt der unten stehende Screenshot die Datenreihe mit dem Wert 10 statt 0.

Diese neue Datenreihe wandeln wir in einen Linienverlauf um wenn wir an einem verteilten und gestapelten Säulendiagramm arbeiten.

Wir benötigen natürlich einen Legendeneintrag, um die neue Datenreihe anwählen zu können, schließlich wird sie im Diagramm gar nicht dargestellt. Ziehen wir das Diagramm im Zweifelsfall breiter, falls die Legende nicht richtig angezeigt wird (vor allem Excel 2003 und ältere). Alternativ gehen wir über die Registerkarten Diagramm/Format zum Funktionsbereich Aktuelle Auswahl.

Die Achsen anpassen

In diesem Schritt wird die Datenreihe Achse formatiert, um sie auf die Sekundärachse zu legen. Wählen wir dazu zunächst die Datenreihe Achse an. Wenn man nirgendswo ein zugehöriges Element findet, über das die Datenreihe ausgewählt werden könnte, dann geht man in die Registerkarte Diagramm/ Format und nutzt hier den Funktionsbereich Aktuelle Auswahl, um die Datenreihe auszuwählen. Danach können wir über die Tastenkombination Strg + 1 den Dialog Datenreihen formatieren aufrufen.

Aktivieren wir unter Datenreihe zeichnen auf die Option Sekundärachse.

Jetzt fügen wir die Sekundärachse zum Diagramm hinzu. Ab Excel 2007 benutzen wir den Achsentypus Sekundär horizontal und für Balkendiagramme den Achsentypus Sekundär vertikal. Das macht man in der  Registerkarte Diagramm / Entwurf und in der Dropdownliste Diagrammelement hinzufügen unter dem Stichpunkt Achsen.

Jetzt muss noch für das Balkendiagramm die Anordnung der Sekundärachse invertiert werden. Schließlich wurde hier zu Beginn auch für eine bessere Darstellung die Primärachse invertiert. Andernfalls erhalten wir wieder eine vertauschte Zuordnungen und damit ein falsches Diagramm.

Rufen wir also die Achsenformatierung für die Sekundärachse im Balkendiagramm auf und aktivieren das Argument Kategorien in umgekehrter Reihenfolge. Außerdem sollte der Schnittpunkt der horizontalen Achse mit der vertikalen Achse auf Automatisch festgelegt werden, was normalerweise Null oder dem Minimalwert entspricht.

Die Kategorienbezeichnungen sollen nun aber noch ihre Position ändern. Wenn wir an einem Säulendiagramm arbeiten, dann formatieren wir die links liegende vertikale Achse so, dass die horizontale Achse beim Maximum schneidet. Formatieren wir außerdem die rechte vertikale Achse so, dass die horizontale Achse sie nach automatischer Festlegung schneidet (beim Minimum).

Im Ergebnis sind die Beschriftungen Kaffee, Milch und Zucker nun jeweils an das Fußende der Balken/Säulen gerutscht.

Wichtig: die Ausrichtung der Beschriftungen

Im Moment sind die Achsenbeschriftungen noch nicht unter den Säulen- oder Balkenpaaren zentriert, sondern erscheinen versetzt. Hier lässt sich aber mit ein paar Formatierungshandgriffen Abhilfe schaffen.

Öffnen wir dazu durch einen Rechtsklick das Menü Achse formatieren – für das Säulendiagramm die nun oben liegende horizontale Achse, für das Balkendiagramm die nun rechts liegende vertikale Achse – und definieren die Achsenposition als Auf Teilstrichen. Dabei sollte jeweils die Achse, an der inzwischen die Beschriftungen angetragen sind, also die horizontale Achse (Säulendiagramm) beziehungsweise die linke vertikale Achse (Balkendiagramm), die Achsenposition  Zwischen den Teilstrichen aufweisen.

Jetzt werden noch durch finale Formatierungsmaßnahmen einige unnötige Diagrammelemente ausgeblendet, um zu einer sauberen Darstellung zu gelangen.

In beiden Diagrammen finden wir eine Sekundärachse, die von 0 bis 1 skaliert ist (Säulendiagramm: vertikale Achse rechts; Balkendiagramm: horizontale Achse oben): Diese Achse können wir jeweils löschen.

In beiden Diagramme finden wir außerdem eine Kategorieachse, die von 0 bis 10 skaliert ist (Säulendiagramm: horizontale Achse oben; Balkendiagramm: vertikale Achse rechts): Diese Achse dürfen wir nicht löschen, stattdessen blenden wir über die Achsenformatierung sämtliche Teilstriche, Achsenbeschriftungen und Linienformatierungen aus.

Zuletzt markieren wir noch jeweils den Eintrag Achse in der Diagrammlegende durch einen Doppelklick (darauf achten in Excel 2003, tatsächlich nur den Texteintrag zu markieren und nicht den Legendeneintrag an sich). Dann löschen wir den Texteintrag Achse in der Legende (diese Datenreihe war schließlich nur ein Hilfsmittel). Falls von der Datenreihe Achse noch irgendwelche Diagrammlinien oder sonstige sichtbare Markierungen bestehen, blenden wir diese aus.

Damit ist der Hauptteil des Tutorials fertig. Allzu komplexe Berechnungen tauchen darin nicht auf, aber der zeitliche Aufwand ist nicht ganz zu vernachlässigen. Dieses Problem haben wir aber nur beim ersten Mal, denn für zukünftige Datensätze können wir dieses Diagramm jetzt als Vorlage abspeichern!

Eine Trendlinie in das verteilte Stapelsäulendiagramm einfügen

Für die Erstellung der Achsenbeschriftungen Kaffee, Milch und Zucker wurde eine Hilfsdatenreihe namens Achse definiert. Diese Hilfsdatenreihe führt ein nützliches Doppelleben. Bisher wurde in den Zellen F2:F4 der Wert Null eingetragen. Das diente dazu, um die Datenreihe im Diagramm unsichtbar zu machen, denn der Wert Null ist nicht darstellbar.

Wenn wir nun also eine Trendlinie in das verteilte Säulendiagramm einfügen wollen, dann tragen wir dazu einfach in die Zellen F2:F4 die zugehörigen Werte ein (nicht die Nullen überschreiben!). Danach können wir den Namen Achse durch einen passenden Titel für die neue Datenreihe ersetzen. Außerdem müssen wir natürlich noch mit zwei kurzen Handgriffen das Kontextmenü der Datenreihe aufrufen und den Datenreihen Diagrammtyp ändern. Wählen wir dafür den Typ Liniendiagramm aus. Der Typ Punkt (X,Y) wird übrigens nicht funktionieren, aus diesem Grund kann die Trendlinie auch nur in das Säulendiagramm und nicht in das Balkendiagramm eingefügt werden.