In diesem Beitrag möchte ich zeigen, wie ein dynamisches Excel Diagramm aufgebaut werden kann. Das Beispiel zeigt, wie sich der Monatsumsatz des aktuellen Jahres gegenüber des Vorjahres verhält. Dabei wird das aktuelle Jahr immer nur bis zum aktuellen Monat angezeigt und das vorherige Jahr komplett. Die Besonderheit an diesem Diagramm, dass es für das aktuelle Jahr keine Null-Linie gibt.
Aber nun erstmal von Anfang an. Als erstes erstellen Sie eine leere Excel-Tabelle. Dann erstellen Sie folgende Tabelle für das Vorjahr 2015 :
Aus dieser Tabelle können Sie nun ein Liniendiagramm erstellen, welches die einzelnen Monatsumsätze von 2015 anzeigt, wie in dem folgenden Beispielbild :
Als nächstes erweitern Sie die Tabelle wie im folgenden Bild :
Wenn Sie jetzt das Jahr 2016 wie in dem folgenden Dialog als zweite Datenreihe hinzufügen, dann sehen Sie die Kurve von Januar bis Mai für das Jahr 2016.
Dieses hat aber den Nachteil, dass kein neuer Punkt im Diagramm angezeigt wird, wenn der Wert für Juni und die folgenden Monate hinzukommt. Um jetzt nicht immer manuell den Bereich für die Datenreihe erweitern zu müssen, behelfen wir uns mit dem Namens-Manager von Excel.
Erstellung des dynamischen Bereichs
Sie gehen nun als erstes in den Reiter Formeln und klicken in den Bereich Definierte Namen auf das Icon Namens-Manager. In diesem Manager können Bereiche und Formel auf einen festen Namen definiert werden. Diese können jederzeit in der kompletten Excel Arbeitsmappe über den Namen abgerufen werden. Wenn Sie den Namens-Manager geöffnet haben, sollten Sie folgendes Fenster sehen :
Als nächste auf den Button „Neu“ klicken. In dem nun folgenden Fenster füllen Sie die folgenden Felder wie folgt aus :
Feld Name :
<Hier können Sie einen beliebigen Namen vergeben (max. 255 Zeichen)>
Feld Bezieht sich auf : =BEREICH.VERSCHIEBEN(‚Tabelle1‘!$C$7;;;;ANZAHL(‚Tabelle1‘!$C$7:$N$7))
Als nächstes klicken Sie auf den Button OK. Damit wird der Eintrag gespeichert und Sie sollten jetzt in der Übersicht des Namens-Manager folgenden Eintrag sehen :
Über den Namen dynamischesJahr können Sie nun jederzeit überall in der Excel-Arbeitsmappe die angegebene Formel abrufen. Wenn Sie mehrere dynamische Bereiche für ein Diagramm erstellen wollen, müssen Sie diese Schritte für die jeweiligen Bereiche wiederholen.
Das Diagramm nun dynamisch gestalten
Über die eingegebene Formel im Namens-Manager kann nun der Beriech für die zweite Linie im Diagramm gestaltet werden. Jeweils wenn in der Zeile 7 ein Wert hinzukommt, wird der Bereich automatisch verschoben.
Nun gehen wir wieder in das Diagramm, was in diesem Moment wie folgt aussehen sollte :
Wenn Sie nun auf die orange Linie klicken, sehen Sie in der Zeile 7, dass der Bereich C7 bis G7 mit einem blauen Rahmen markiert ist. Wird jetzt eine Zahl bei H7 eingetragen, wird diese nicht automatisch mit ins Diagramm aufgenommen. Durch das Anklicken der orangen Linie, wird oben die folgende Formel für die Datenreihe angezeigt :
=DATENREIHE(Tabelle1!$B$7;;Tabelle1!$C$7:$G$7;2)
Der erste Wert in der Formel übergibt an das Diagramm den Namen der Datenreihe. Der vorletzte Wert in der Formel übergibt den Bereich der Daten für die X-Achse. Diesen Wert werden wir jetzt wie folgt ändern :
=DATENREIHE(Tabelle1!$B$7;;Mappe1.xlsx!dynamischesJahr;2)
Als nächstes bestätigen Sie die Eingabe mit der Return-Taste. Das Diagramm und der markierte Bereich der Datenreihe wird sich nach dem Bestätigen mit der Return-Taste erstmal nicht verändern. Der Wert Mappe1.xlsx!dynamischesJahr greift jetzt auf die Formel BEREICH.VERSCHIEBEN zu, welche Sie im Namens-Manager unter dem Namen dynamischesJahr erstellt haben. Erst wenn Sie jetzt einen Wert in der Zelle H7 eingeben kommt die Formel hinter dem Namen dynamischesJahr zum Tragen und erweitert automatisch den Bereich für die Datenreihe aus 2016. Wird jetzt das Tabellenblatt automatisch oder manuelle neu berechnet, wird die Linie für 2016 automatisch bis zum Monat Juni (Zelle H7) erweitert.
Abschluss
Mit diesen Beispiel können Sie ganz einfach verschiedene Diagramme dynamisch mit Werten füllen. Sie können die gleichen Schritte auch für ein Balken und Kreisdiagramm verwenden. Werden die Werte in der Tabelle für die Datenreihe 2016 nicht manuell eingegeben, sondern über Formeln berechnet, ist darauf zu achten, dass die Zellen aus dem Bereich immer leer bleiben. Es darf also keine Null erscheinen. Dieses können Sie über eine entsprechende WENN-Formel erreichen.
Zum Schluß können Sie sich hier noch eine Beispiel Excel-Datei herunterladen, die den kompletten Quelltext inkl. Beispieltabelle enthält.