Wer in Excel häufig mit Diagrammen arbeitet, kennt das Problem: Man möchte etwas darstellen, dessen Inhalt sich häufig, manchmal auch regelmäßig ändert. So ist beispielsweise eine Analyse von Zeitreihen, die Sie heute erstellt haben, im nächsten Monat schon nicht mehr aktuell, weil die Daten um eine weitere Periode, zum Beispiel einen Monat, ein Quartal oder ein Jahr, ergänzt wurden. Wird ein neuer Eintrag hinzugefügt oder soll der Inhalt des Diagramms nicht immer der gleiche sein, muss der Quellbereich des Diagramms jedes Mal geändert werden, weil Excel standardmäßig nur feste Bereiche darstellen kann.

Dieses Manko von Excel ließe sich beheben, indem man im Diagramm gleich den Bezug auf die gesamte Tabelle herstellt. Von Nachteil ist jedoch, dass am Ende des Diagramms ein größerer freier Bereich entsteht, der langsam von neuen Balken ausgefüllt wird.

Dieser Artikel zeigt, wie Sie Excel ab Version 97 zu einer vollautomatischen Anpassung des Diagramms bewegen und auf diese Weise ein gut funktionierendes dynamisches Reporting aufbauen können.

Um die Bezugsangaben gewissermaßen automatisch zu aktualisieren, benötigen wir die Funktion BEREICH.VERSCHIEBEN in Verbindung mit anderen Elementen von Excel.

Ziel: Zeitreihen analysieren

Die Teilprojekte und der entsprechende Zeitraum sollen dynamisch über Kombinationsfelder ausgewählt werden können. Deshalb muss das Diagramm automatisch auf diese Konstanten angepasst werden, ohne dass die Quellarten neu zu definieren sind -  und ohne Programmierung.

In Bild 1 sehen Sie acht Zeitreihen für die Kosten und das Budget der Teilprojekte A, B, C und für das Gesamtprojekt. Diese Daten werden in unserem Beispiel analysiert. Falls Sie das Beispiel nachzuvollziehen möchten, finden Sie die abgebildete Tabelle in der Datei "DynamischeDiagramme.xls", die Sie zusammen mit dem Artikel herunterladen können. Die nachfolgenden Angaben zu Zeilen und Spalten beziehen sich auf diese Beispieldatei.

Beachten Sie bitte auch, dass der Name der Datei "DynamischeDiagramme.xls" lautet und der Name der Tabelle "Auswertung". Falls Sie einen anderen Datei- oder Tabellennamen verwenden, müssen Sie die Formeln entsprechend anpassen.

Bild 1: Die fertige Lösung in unserem Beispiel

Der Lösungsansatz

Um das beschriebene Ziel zu erreichen, brauchen wir die in Tabelle 1 aufgeführten Komponenten.

Monate für Kombinationsfeld aufbereiten

Da ein Kombinationsfeld nur mit den Daten einer Spalte und nicht einer Zeile gefüllt werden kann, müssen wir die Funktion MTRANS verwenden, die uns die Daten der Zeile 3 in eine Spalte transponiert. Dadurch erreichen wir, dass bei Anpassung der Monate später auch die Kombinationsfelder Anfangsmonat und Endmonat automatisch angepasst werden.

• Markieren Sie den Bereich A13:A18. Die Anzahl der markierten Zellen in Spalte A soll der Anzahl an Monaten in Zeile 3 entsprechen.
• Geben Sie folgende Formel ein: =MTRANS(D3:I3)
• Schließen Sie die Formel mit Strg+Umschalt+Enter ab, da MTRANS eine Matrixformel ist.
• Da es sich in unserem Beispiel um Monate handelt, müssen nun noch die Zellen A13:A18 mit dem benutzerdefinierten Format MMM JJJJ formatiert werden (Menü Format / Zellen / Zahlen / Benutzerdefiniert).

Namen zuweisen