Immer nur die letzten x Monate im Diagramm anzeigen

Eine Statistik wird monatlich geführt, aber im Diagramm sollen immer nur die Werte der letzten 4 oder 5 oder 6 Monate erscheinen. Wie lässt sich diese Dynamik realisieren, ohne eine separate Datenbasis für das Diagramm anlegen zu müssen? Das folgende Bild gibt eine Vorschau auf die fertige Lösung, die ohne zusätzliche Tabelle auskommt.

Vorschau auf die fertige Lösung

Die folgende Anleitung zeigt Schritt für Schritt, wie die Lösung aufgebaut wird.

Mit diesen 5 Schritten entsteht die Lösung

Nach dem folgenden Fahrplan wird die dynamische Diagrammauswertung aufgebaut:

  • Zuerst wird die Liste der Monatswerte in eine intelligente Tabelle umgewandelt.
  • Darüber in Zelle D2 wird bestimmt, welche Anzahl der letzten Monate im Diagramm angezeigt werden soll. Noch komfortabler ist natürlich ein Drehfeld.
  • Dann werden einige Bereichsnamen definiert; zwei davon sind dynamisch, was mit Hilfe der Funktion BEREICH.VERSCHIEBEN erreicht wird.
  • Auf Basis der gesamten Tabelle wird ein Säulendiagramm angelegt. Dieses wird über DATENREIHE so angepasst, dass es nur die Werte der letzten x Monate zeigt. 
  • Zum Schluss wird die Optik der Tabelle und des Diagramms noch verbessert.

So funktioniert die dynamische Wahl der Monate

  • Der Wert in Zelle D2 bestimmt, wie viele Monate im Diagramm dargestellt werden. Der Wert wird entweder direkt in D2 eingegeben oder komfortabel über ein Drehfeld festgelegt (1).
  • Ausgehend vom Wert in Zelle D2 werden mit zwei dynamischen Bereichsnamen automatisch die Daten markiert, die im Diagramm erscheinen sollen (2).
  • Im Diagramm werden die gewünschten Monatswerte angezeigt (3).
  • Auch der Diagrammtitel wird dynamisch angelegt und zeigt stets die gewählte Anzahl der letzten Monate an (4). 

Die Funktionsweise der Lösung 

Die Datenbasis aufbauen

Im vorliegenden Beispiel besteht die Liste aus zwei Spalten: In der ersten stehen die Monate, in der zweiten die Werte (hier Umsatzzahlen).

  • Mit einem Klick in die Liste und durch Betätigen der Tastenkombination Strg+T wird die Liste mit den Monatswerten in eine intelligente Tabelle umgewandelt.
  • Die so angelegte Tabelle erhält nun – wie unten gezeigt – über das Register Tabellentools/Entwurf ganz links als Tabellenname > tblStatistik.

Der Tabelle einen sprechenden Namen geben 

  • Zelle D2 wird für die Eingabe des Wertes eingerichtet, der die Anzahl der zu zeigenden Monate im Diagramm bestimmt. Spalte D wird dazu schmaler gemacht.

Für die Formeln 4 Bereichsnamen definieren

Die später erforderlichen Formeln sollen verständlich und fehlerfrei angelegt werden. Dazu werden nun einige Bereichsnamen definiert:

  • Zelle D2 wird markiert und nach einem Klick in das Namenfeld (links neben der Bearbeitungsleiste) wird der Name AnzahlMonate eingetippt und die Eingabe mit Enter abgeschlossen (siehe folgendes Bild links).
  • Dann wird in der Tabelle die Spalte Monat markiert. Das geht am einfachsten, wenn die Maus direkt über den Spaltentitel Monat bewegt wird. Sie verwandelt sich in einen kleinen schwarzen Pfeil. Mit zwei Klicks werden nun die Werte und der Spaltentitel markiert (siehe folgendes Bild rechts).

Die ersten beiden Bereichsnamen vergeben

Links: Für Zelle D2 wird der Name über das Namenfeld eingetragen
Rechts: Die Spalte Monat schnell und einfach markieren

  • Mit Strg+Umschalt+F3 wird nun der markierten Tabellenspalte der Bereichsname Monat zugewiesen.

Dynamische Bereichsnamen mit BEREICH.VERSCHIEBEN

Abhängig davon, welcher Wert in Zelle D2 steht, soll in den Spalten B und C automatisch die richtige Anzahl der Datensätze für das Diagramm ermittelt werden. Diese Dynamik der Datenauswahl lässt sich über die Funktion BEREICH.VERSCHIEBEN erreichen.

Dazu werden zwei weitere Bereichsnamen angelegt. Diese dürfen aber nicht statisch sein und immer nur B5:B11 bzw. C5C11 umfassen, sondern müssen sich dynamisch nach dem Wert in Zelle D2 richten. Daher werden diesmal beim Festlegen der Bereichsnamen keine festen Zelladressen, sondern Formeln eingegeben.

  • Strg+F3 ruft das Dialogfeld Namens-Manager auf.
  • Per Klick auf Neu wird nun der Bereichsname Monatsanzeige angelegt.
  • Als Bereich für diesen Namen wird nicht die gesamte Mappe, sondern nur das aktuelle Arbeitsblatt – hier Statistik – festgelegt.
  • Unten in die Eingabezeile Bezieht sich auf wird die folgende Formel eingetragen:
    =BEREICH.VERSCHIEBEN(tblStatistik[[#Kopfzeilen];[Monat]];ANZAHL(tblStatistik[Monat])-AnzahlMonate+1;0;AnzahlMonate;1).

Zwei dynamische Bereichsnamen dank einer Formel

Im Namens-Manager werden diese beiden dynamischen Bereichsnamen definiert

  • Der zweite dynamische Bereichsname, der noch definiert wird, ist Umsatzanzeige.
  • Auch hier wird als Bereich das Arbeitsblatt Statistik festgelegt.
  • Bei Bezieht sich auf wird diesmal folgende Formel eingetippt: =BEREICH.VERSCHIEBEN(tblStatistik[[#Kopfzeilen];[Umsatz]];ANZAHL(tblStatistik[Monat])-AnzahlMonate+1;0;AnzahlMonate;1).

Das Diagramm erstellen und anpassen

Im Anschluss an diese Vorarbeiten ist nun das Diagramm an der Reihe:

  • Nach einem Klick in eine beliebige Zelle des Datenbereichs in Spalte B und C liefert die Tastenkombination Alt+F1 sofort ein Säulendiagramm.
  • Über Diagrammtools/Entwurf > Diagrammlayout wird das Layout 2 zugewiesen. Es entfernt fast alle überflüssigen Elemente und setzt informative Datenbeschriftungen über die Säulen.
  • Im Moment zeigt das Diagramm noch alle Monate an.
  • Ein Klick auf eine der Säulen bewirkt, dass oben in der Bearbeitungsleiste die DATENREIHE-Formel zu sehen ist. Sie lautet im Moment: =DATENREIHE(Statistik!$C$4;Statistik!$B$5:$B$11;Statistik!$C$5:$C$11;1).
  • Damit nicht alle Monate im Diagramm angezeigt werden, müssen nun die festen Adressen $B$5:$B$11 sowie $C$5:$C$11 durch die zuvor definierten zwei dynamischen Bereichsnamen ersetzt werden.
  • $B$5:$B$11 wird oben in der DATENREIHE-Formel ersetzt durch Monatsanzeige und $C$5:$C$11 wird ersetzt durch Umsatzanzeige.

Die angepasste DATENREIHE-Formel

Wichtig beim Anpassen der DATENREIHE-Formel ist, dass vor den Bereichsnamen jeweils erst der Name des Arbeitsblatts stehen muss, gefolgt von einem Ausrufezeichen. Im vorliegenden Fall ist dies also Statistik!
Besteht der Blattname aus mehr als einem Wort – z. B. Statistik seit 2014 –, dann muss der Arbeitsblattname zwischen zwei Hochkommata gesetzt werden.

Die Optik der Auswertung anpassen

In Spalte B (Monat) stehen normale Datumsangaben wie 1.1.2015, 1.2.2015, 1.3.2015. Damit bei mehr als 12 Werten die Jahreszahl gut zu erkennen ist, werden die Werte der Monatsspalte mit einem speziellen Zahlenformat versehen:

  • Dazu werden die Datumsangaben markiert.
  • Über Strg+1 wird das benutzerdefinierte Zahlenformat JJJJ | MMMM zugewiesen.

Für die waagerechte Achse im Diagramm (die Rubrikenachse) wird ebenfalls mit Strg+1 und einen Klick auf Zahl das folgende Zahlenformat definiert: MM-JJJJ.

Damit die Werte über den Säulen verkürzt angezeigt werden, kommt hier das benutzerdefinierte Zahlenformat 0. “T€” zum Einsatz.

Die drei benutzerdefinierten Zahlenformate

Die benutzerdefinierten Zahlenformate für die Monatsspalte,
für die Rubrikenachse und für die Datenbeschriftungen der Säulen

Zum Schluss: Den Diagrammtitel ebenfalls dynamisch machen

Damit über dem Diagramm sofort zu sehen ist, wie viele Monate ausgewertet werden, wird der Standardtitel durch eine Formel ersetzt. So geht’s:

  • Der Diagrammtitel wird mit Entf gelöscht.
  • Für die die Diagrammfläche wird der Fülleffekt > Keine Füllung festgelegt. Damit ist zumindest der obere Teil des Diagramms durchsichtig.
  • In eine der Zellen, die nun hinter dem Diagramm zu sehen ist – hier ist es F5 – wird nun die folgende Formel eingegeben, die für den dynamischen Diagrammtitel sorgt:
    =“Umsatz der letzten „&AnzahlMonate&“ Monate“.

Diese Formel macht den Diagrammtitel dynamisch

Hier wird also in dem Text “Umsatz der letzten x Monate” das x durch den Wert in D2 ersetzt. Das &-Zeichen sorgt dafür, dass drei Teile – zwei Texte und ein Zellbezug – zu einer Formel zusammengesetzt werden können.

Dieser Beitrag wurde unter Excel abgelegt und mit , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.

2 Antworten auf Immer nur die letzten x Monate im Diagramm anzeigen

  1. Sandro R. sagt:

    Hallo. Super Beitrag. Komme aber beim Anpassen der Datenreihe nicht weiter.
    Wäre super, wenn sie sich mit mir in Verbindung setzen könnten.

    Gruß
    Sandro

  2. Patrick Rückert sagt:

    Top Beitrag. Löst genau mein Problem. Nur leider komme ich an einer Stelle nicht weiter. Ist es möglich per Email von ihnen die Mustervorlage zu bekommen?

    MfG Patrick Rückert

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.