Du willst ein Diagramm erstellen, das bestimmte Daten aus dem Zeitraum der letzten 12 Monate wiedergibt. Idealerweise wollen wir es so aufbauen dass wir das Diagramm nicht 12 mal im Jahr anpassen müssen nur weil wieder ein Monat hinzugekommen ist.

Wie können wir also die Datenquelle des Diagramms dynamisch machen damit sie fliessen wie der Verkehr im Bild?

Im folgenden Beispiel zeige ich wie wir ein Diagramm durch dynamische Verweise automatisch aktualisieren.

Unsere Beispiel Daten

Hier die Werte für unser Beispiel:

Beim Erstellen eines normalen Diagrammes würden wir jetzt einfach auf den Diagrammerstellungs-Assistenten klicken oder direkt auf das gewünschte Diagramm im Menüband. Um ein dynamisches Diagramm zu erstellen müssen wir aber ein wenig Vorarbeit leisten.

Wer das Beispiel selber in Excel  anschauen möchte kann dies mit dieser Datei tun:

Dynamische Bereiche erstellen im Namensmanager

Die Erstellung eines dynamischen Diagramms beginnt mit der Festlegung dynamischer Bezugsnamen. Das lässt sich sehr effizient über den Namens-Manager bewerkstelligen. Nutzen Sie die Tastenkombination Strg + F3, alternativ finden Sie den Namens-Manager unter der Registerkarte Formeln in der Gruppe Definierte Namen. Klicken Sie auf Neu, um einen neuen Namen anzulegen. Eine weitere Alternative um schnell benannte Bereiche zu erstellen habe ich auf in diesem Beitrag schon erklärt.

Im folgenden Dialog tragen wir im Feld Name den Wert DiagLänge ein. Das Feld soll sich auf den Wert in =Tabelle1!$D$2 beziehen. Dieses Feld wird sich auf die Länge der Datenreihe für das Diagramm beziehen (die Anzahl der Monate in D2).

Als nächstes legen wir den zweiten Namen an: DiagGruppen. In das Feld Bezieht sich auf tragen wir die folgende Formel ein:

=BEREICH.VERSCHIEBEN(Tabelle1!$A$1;ANZAHL2(Tabelle1!$A:$A)-1;0;-MIN(DiagLänge; ANZAHL2(Tabelle1!$A:$A)-1);1)

Diese Formel definiert den Bereich in Spalte A, der die letzten 12 Monate (oder Datengruppen) enthält. Wenn Ihr die Funktionen BEREICH.VERSCHIEBEN noch nicht kennt könnt Ihr in diesem Beitrag darüber nachlesen.

Legen wir jetzt den Namen DiagWertA an und übertragen die folgende Formel in das Feld Bezieht sich auf: =BEREICH.VERSCHIEBEN(DiagGruppen;0;1).

Der nächste Name lautet DiagWertB und der Bezieht sich auf erhält die Formel: =BEREICH.VERSCHIEBEN(DiagGruppen;0;2). Diese beiden Namen stellen den Bezug zu den Werten her, die in unserem dynamischen Diagramm dargestellt werden sollen.

Hinweis zur Namenswahl: Namen können nicht völlig beliebig vergeben werden. Excel erlaubt zum Beispiel keine Einzelbuchstaben, weil diese auch als Spaltenmarkierung verwendet werden. Auch ganze Zellenbezüge sind dementsprechend nicht erlaubt. Nutzer von Excel 2007 werden außerdem feststellen, dass das Wort ‚Diagramm’ nicht als Bestandteil des Namens akzeptiert wird. Hier schafft die Kurzform ‚Diag’ Abhilfe.

Erstellen des Diagrammes mit den dynamischen Bezügen

Jetzt wählen wir eine leere Zelle aus die sich auf demselben Tabellenblatt befindet und fügen über die Registerkarte Einfügen ein neues Diagramm ein.

Im ersten Schritt legen wir den gewünschten Diagrammtyp fest (im Beispiel habe ich ein gruppiertes Säulendiagramm ausgewählt).

Über Daten auswählen (Register Diagrammtools>Entwurf) fügen wir die erste Datenreihe hinzu.

Zunächst definieren wir den Reihennamen durch den Wert: =Tabelle1!DiagGruppen. Danach setzen wir den Cursor in das Feld “Reihenwerte” und wählen anschließend Zelle B1 aus. Folgender Wert wird angezeigt: =Tabelle1!$B$1. Dies ändern wir durch den vorher erstellten dynamischen Bezug: =Tabelle1!DiagWertA.

Hinweis: Die Angabe des Tabellenblatt mit Ausrufzeichen ist wichtig. Wird das Diagramm z.B. auf einem Tabellenblatt erstellt welches die Ursprungsdaten nicht beinhaltet, so weigert sich Excel den Benannten Bereich als Eingabe zu akzeptieren, es sei denn es wird eben mit diesem Verweis angeführt (dies auch wenn der benannte Bereich in Excel so definiert ist das es für die gesamte Arbeitsmappe Gültigkeit haben sollte)!

Nun wiederholen wir diese Schritte für die zweite Datenreihe: Der Name der Datenreihe lautet auch hier =Tabelle1!DiagGruppen. Im Feld Reihenwerte ändern wir den angezeigten Wert auf: =Tabelle1!DiagWertB.

Im Datenquellen-Dialog bearbeiten wir die horizontale Achsenbeschriftung, denn auch diese soll dynamisch sein. Für den Bezug wählen wir =Tabelle1!DiagGruppen aus.

Das so erstellte Diagramm sieht zunächst nicht anders aus als jedes andere 12-Monats-Diagramm, aber…

“Wieso funktioniert das?” oder “Das dynamische Verhalten des Diagrammes”

Jetzt springen wir ein paar Monate nach Vorne und geben neue Werte für die kommenden Monate ein z.B.:

Schon während der Eingabe übernimmt Excel die neuen Werte im Diagramm! Das Diagramm umfasst jetzt die Monate Julie 15 bis Juni 16.

Bei der bisherigen Definition des Namens DiagGruppen haben wir einen kleinen Trick angewandt,
um zu verhindern, dass Datenreihen kleiner 12 Monate zu einem Fehler führen.

Wenn aber die Länge der Datenreihe (DiagLänge) immer mindestens 12 Zeilen beträgt, dann können Sie den Namen DiagLänge auch mit folgendem Bezug definieren:

=BEREICH.VERSCHIEBEN(Tabelle1!$A$1;ANZAHL2(Tabelle1!$A:$A)-1;0;-DiagLänge;1)

Die Datenreihe sollte dann aber nicht kleiner 12 werden, denn die Formel BEREICH.VERSCHIEBEN würde dann Bezüge zu Zeilen herstellen, die oberhalb der ersten Zeile der Tabelle liegen. Aus diesem Grund wird der Ausdruck MIN(DiagLänge; ANZAHL2(Tabelle1!$A:$A)-1) eingesetzt. Der Ausdruck gibt entweder den Wert DiagLänge aus oder, wenn die Datenreihe kürzer ausfällt als DiagLänge, die tatsächlich vorhandene Zeilenzahl des Datenbereichs:

=BEREICH.VERSCHIEBEN(Tabelle1!$A$1;ANZAHL2(Tabelle1!$A:$A)-1;0;-MIN(DiagLänge; ANZAHL2(Tabelle1!$A:$A)-1);1)

Testen wir also jetzt zum Abschluss noch die Reaktion des Diagramms auf einen verkürzten Datenbereich. Löschen wir dazu alle Daten ausgenommen der ersten 9 Monate. Das Diagramm verarbeitet den verkürzten Datenbereich ohne Probleme.