Flexibel wählen, welche Daten dargestellt werden

Mehr Überblick mit dynamischen Excel-Diagrammen

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.

Management Summary

Flexibel wählen, welche Daten dargestellt werden

Mehr Überblick mit dynamischen Excel-Diagrammen

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.

Management Summary

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…

Bewertungen und Kommentare

(nur angemeldete Benutzer)

Diese Funktion steht nur eingeloggten Nutzern zur Verfügung. Jetzt einloggen
Gesamt
Bewertungen 1
Alle anzeigen