Flexibel wählen, welche Daten dargestellt werden

Mehr Überblick mit dynamischen Excel-Diagrammen

Dynamische Exceltabellen
Im Laufe des Projekts werden Ihre Datenlisten immer umfangreicher – schnell drängt sich da das Gefühl auf, den Wald vor lauter Bäumen nicht mehr zu sehen. Dynamische Diagramme bieten da Abhilfe: Dieter Schiecke und Hildegard Hügemann zeigen, wie Sie damit Daten übersichtlich darstellen können und z.B. nur die Kosten der letzten x Monate anzeigen lassen.
Für eilige Leser (Management Summary)
  • 💡Um schnell den Überblick über die Daten eines bestimmten Zeitraums zu erhalten, verwenden Sie in Excel ein dynamisches Diagramm.
  • 💡Dafür geben Sie über Ihrer Datentabelle in einer Zelle den Zeitraum von Monaten an, der im Diagramm angezeigt werden soll – z.B. die Anzahl der letzten 6 Monate.
  • 💡Mit Bereichsnamen sorgen Sie für die gewünschte Dynamik bei der Wahl der Daten, die im Diagramm erscheinen sollen. Hierzu nutzen Sie u.a. die Funktion BEREICH.VERSCHIEBEN.
  • 💡Nach diesen Vorarbeiten erstellen Sie ein Säulendiagramm und passen es mit Hilfe der Bereichsnamen so an, dass es nur die Daten des gewünschten Zeitraums zeigt. Zum Abschluss verbessern Sie noch die Optik und Aussage des Diagramms.

Bei umfangreichen Datenlisten ist es meist wenig sinnvoll, alle Daten in einem Diagramm zu zeigen: Oft interessiert nur ein bestimmter Zeitraum oder der Vergleich mit dem Vorjahreswert. Doch wie lassen sich flexibel nur die Werte der letzten sechs, neun oder zwölf Monate darstellen, ohne dass erst eine separate Datentabelle für das Diagramm angelegt werden muss? In diesem Tipp erfahren Sie, wie Sie

  • eine Lösung aufbauen, die ohne Zusatztabelle auskommt,
  • Bereichsnamen mit Dynamik verwenden und
  • abschließend das Diagramm und seinen Titel dynamisch machen.

Bild 1: Im Diagramm wird jeweils nur die Anzahl von Monaten angezeigt, die in D1 eingegeben wird; der Diagrammtitel weist darauf hin
Bild vergrößern

Die fertige Lösung finden Sie in der Datei Im_Diagramm_nur_die_letzten_x_Monate_anzeigen_LOE.xlsx.
In der Mappe mit dem Kürzel UEB können Sie das hier beschriebene Vorgehen Schritt für Schritt nachvollziehen.

Die Datenbasis anlegen und Tabellennamen vergeben

Die Beispielliste enthält eine Spalte mit Datumsangaben sowie eine Spalte mit Kostenwerten. Um die Datenbasis für die Lösung anlegen zu können und die Tabelle zu benennen, gehen Sie wie folgt vor:

  1. Klicken Sie in die Liste und betätigen Sie Strg+T. So wird aus der Liste eine »intelligente Tabelle«. Das hat für Sie den Vorteil, dass sich die Tabelle bei neuen Werten automatisch nach unten erweitert und somit die Formeln und das Diagramm immer auf die aktuellen Daten zugreifen (siehe Bild 2).
  2. Vergeben Sie über das Register Tabellentools / Entwurf ganz links den Tabellennamen tblKosten.

Bild 2: Links die Ausgangsliste, rechts die intelligente Tabelle, die an der rechten unteren Ecke ganz leicht erweitert werden kann
Bild vergrößern

Bereichsnamen definieren und dynamisch machen

Bereichsnamen für Anzahl der Monate

Damit sich später die Formeln verständlich und fehlerfrei anlegen lassen, definieren Sie Bereichsnamen:

  1. Markieren Sie Zelle D1, in der Sie später die Anzahl der Monate eintragen, die das Diagramm anzeigen soll.
  2. Klicken Sie in das Namenfeld links neben der Bearbeitungsleiste. Tippen Sie dort AnzahlMonate ein.
    Wichtig: Schließen Sie die Eingabe mit Enter ab.

Bild 3: Links oben in das Namenfeld tragen Sie den Bereichsnamen AnzahlMonate ein

Dynamische Bereichsnamen mit BEREICH.VERSCHIEBEN

Abhängig davon, welcher Wert in Zelle D1 steht, soll in den Spalten B und C automatisch der passende Datenbereich für das Diagramm ermittelt werden. Diese Dynamik der Datenauswahl erreichen Sie mit der Funktion BEREICH.VERSCHIEBEN. Sie sorgt dafür, dass im Diagramm nicht statisch alle Daten aus B4:B16 bzw. C4:C16 gezeigt werden, sondern nur die, die über Zelle D1 festgelegt werden. Deshalb verwenden Sie beim Definieren der folgenden zwei Bereichsnamen keine festen Zelladressen, sondern Formeln.

So gehen Sie vor:

  1. Rufen Sie mit Strg+F3 den Dialog Namens-Manager auf.
  2. Klicken Sie auf die Schaltfläche Neu und geben Sie den Bereichsnamen Monatswahl ein.
  3. Als Bereich für diesen Namen wählen Sie nicht die gesamte Mappe, sondern nur das aktuelle Blatt Kosten.
  4. Bei Bezieht sich auf geben Sie folgende Formel ein:
    =BEREICH.VERSCHIEBEN(tblKosten[[#Kopfzeilen];[Monat]];ANZAHL(tblKosten[Monat])-AnzahlMonate+1;0;AnzahlMonate;1)
  5. Nachdem Sie mit OK den ersten dynamischen Bereichsnamen definiert haben, klicken Sie ein zweites Mal auf die Schaltfläche Neu und vergeben nun den Namen Kostenwahl.
  6. Wählen Sie als Bereich für diesen Namen wieder nur das aktuelle Arbeitsblatt aus.
  7. Bei Bezieht sich auf geben Sie diesmal als Formel ein:
    =BEREICH.VERSCHIEBEN(tblKosten[[#Kopfzeilen];[Kosten]];ANZAHL(tblKosten[Monat])-AnzahlMonate+1;0;AnzahlMonate;1)

Bild 4: Die beiden dynamischen Bereichsnamen anlegen, die statt eines festen Zellbezugs eine Formel mit BEREICH.VERSCHIEBEN haben
Bild vergrößern

So funktioniert die Formel:

Die Funktion BEREICH.VERSCHIEBEN hat die folgende Syntax:
BEREICH.VERSCHIEBEN(Bezug;Zeilen;Spalten;[Höhe];[Breite]).

  • Das erste Argument namens Bezug ist der Ausgangspunkt des auszuwertenden Bereichs, in unserem Fall also die Überschrift der Monatsspalte: tblKosten[[#Kopfzeilen];[Monat]].
  • Für das Argument Zeilen geben Sie ANZAHL(tblKosten[Monat])-AnzahlMonate+1 ein.
    Das verschiebt den auszuwertenden Bereich vom Argument Bezug zum ersten der auszuwertenden Monate.
  • Das Argument Spalten belegen Sie mit 0, denn ein Verschieben des auszuwertenden Bereichs nach rechts ist nicht erforderlich.
  • Als viertes Argument ist die Höhe des Bereichs anzugeben. Den liefert der Bereichsname AnzahlMonate.
  • Das fünfte Argument ist die Breite des Bereichs. Hier im Beispiel handelt es sich nur um eine Spalte, geben Sie dort also den Wert 1 ein.

Das Diagramm erstellen und anpassen

Nach den Vorarbeiten an der Datenbasis legen Sie das Diagramm an, das dynamisch die jeweils gewählte Anzahl der Monate anzeigt. So gehen Sie vor:

  1. Klicken Sie in den Datenbereich und legen Sie mit der Tastenkombination Alt+F1 ein Säulendiagramm im gleichen Arbeitsblatt an.
  2. Weisen Sie über Diagrammtools / Entwurf > Diagrammlayout das Layout 2 zu. Es entfernt fast alle überflüssigen Elemente und setzt Datenbeschriftungen über die Säulen (Bild 2).

Bild 5: Mit der Wahl des passenden Diagrammlayouts – hier Layout 2 – sparen Sie Zeit und Mühe

Nur die gewünschten Monate im Diagramm anzeigen

Im Moment zeigt das Diagramm noch alle Monate an.

So ändern Sie das:

  1. Klicken Sie auf eine der Säulen. Oben in der Bearbeitungsleiste sehen Sie nun die DATENREIHE-Formel. Sie lautet: =DATENREIHE(KOSTEN!$C$3;KOSTEN!$B$4:$B$16;KOSTEN!$C$4:$C$16;1).
  2. Damit nur die in D1 festgelegten Monate im Diagramm angezeigt werden, ersetzen Sie die festen Adressen $B$4:$B$16 sowie $C$4:$C$16 durch die zwei dynamischen Bereichsnamen Monatswahl bzw. Kostenwahl.

Bild 6: Ersetzen Sie in der DATENREIHE-Formel die Zellbezüge durch Bereichsnamen

Achten Sie darauf, dass beim Anpassen der DATENREIHE-Formel vor dem Bereichsnamen der Blattname steht, gefolgt von einem Ausrufezeichen – z.B. KOSTEN! Besteht der Blattname aus mehr als einem Wort – z.B. Kosten 2018 –, setzen Sie diesen Blattnamen zwischen zwei Hochkommata: 'KOSTEN 2018'!.

Zum Schluss die Optik des Diagramms anpassen

Das Format der Datumsangaben optimieren

Wie in Bild 2 gezeigt, stehen in der Spalte B für die Monate Datumsangaben wie 1.10.2017, 1.11.2017, etc. Damit im Diagramm bei einer großen Anzahl von Monaten stets auch das Jahr gut zu erkennen ist, weisen Sie der Monatsspalte ein spezielles Zahlenformat zu.

So geht's:

  1. Markieren Sie alle Datumsangaben in Spalte B. Klicken Sie dazu einfach genau über der Spaltenüberschrift Monat. Die Maus erscheint als kleiner, senkrechter, schwarzer Pfeil.
  2. Rufen Sie mit Strg+1 das Dialogfeld zum Formatieren von Zellen auf und zeigen Sie das Register Zahlen an (Bild 7).
  3. Klicken Sie links unter Kategorie auf den Eintrag Benutzerdefiniert. Tragen Sie rechts in das Eingabefeld unter Typ folgenden Formatcode ein: MM-JJ.
  4. Nach einem Klick auf OK erscheint dieses Zahlenformat auch an der waagerechten Achse des Diagramms.

Bild 7: Ein Zahlenformat definieren, das neben den Monaten auch die Jahre zeigt

Das Format der angezeigten Werte anpassen

Die Werte über den Säulen sind zu lang. Lassen Sie sie daher verkürzt anzeigen, also 28 T€ statt 27.703 €. Auch das erledigen Sie über ein benutzerdefiniertes Zahlenformat. Diesmal aber nicht für einen Zellbereich, sondern für das Diagramm.

  1. Klicken Sie eine der Datenbeschriftungen an, um alle zu markieren.
  2. Rufen Sie mit Strg+1 erneut den Dialog zum Formatieren auf. Wählen Sie die Registerkarte Zahlen und ab Excel 2013 die Rubrik Zahl. Stellen Sie zunächst Währung ein. Wechseln Sie dann zu Benutzerdefiniert.
  3. Passen Sie bei Typ den Formatcode wie folgt an: 0. “T€” (Bild 8).

Bild 8: Mit diesem Zahlenformat wird die Anzeige der Zahlen verkürzt dargestellt.

Den Diagrammtitel mit einer Formel ebenfalls dynamisch machen

Damit über dem Diagramm stets sofort zu sehen ist, wie viele Monate ausgewertet werden, ersetzen Sie den Standardtitel durch einen dynamischen.

Das erledigen Sie mit einer Formel:

  1. Platzieren Sie das Diagramm mit seiner linken oberen Ecke über der Zelle E3.
  2. Löschen Sie den Diagrammtitel mit Entf.
  3. Bewegen Sie die Maus in einen freien Bereich des Diagramms (z.B. in eine Ecke des Diagramms). Per rechtem Mausklick rufen Sie nun die Minisymbolleiste auf und wählen dort Fülleffekt > Keine Füllung. Damit ist der obere Teil des Diagramms durchsichtig.
  4. Steuern Sie nun mit den Pfeiltasten der Tastatur die Zelle E3 an, die hinter dem Diagramm zu sehen ist. Geben Sie dort die folgende Formel ein, die für den dynamischen Diagrammtitel sorgt (Bild 9):
    "Die Projektausgaben in den letzten "&AnzahlMonate&"Monaten"
  5. Klicken Sie zum Schluss den Rand des Diagramms mit gedrückter Strg-Taste an. Nun können Sie es mit den vier Richtungstasten so verschieben, dass der Titel perfekt oben links im Diagramm angeordnet ist.

Bild 9: Mit dieser Formel machen Sie den Diagrammtitel dynamisch

So funktioniert die Formel für die Diagrammtitel

  • In der oben gezeigten Formel wird im Text “Die Projektausgaben in den letzten x Monaten” das x durch den Wert in D1 ersetzt.
  • Das &-Zeichen sorgt dafür, dass Sie drei Teile – zwei Texte und einen Zellbezug – innerhalb der Formel zusammensetzen können.
 
Tech Link