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.
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
Um das beschriebene Ziel zu erreichen, brauchen wir die in Tabelle 1 aufgeführten Komponenten.
| Funktion/Befehl | Verwendung |
|---|---|
| MTRANS | Funktion, die die Monate von Zeile in Spalte transponiert. Sie wird benötigt, um die Kombinationsfelder Anfangsmonat und Endmonat zu füllen. Die Syntax der Matrixformel lautet =MTRANS(Matrix). Matrixformeln werden immer mit Strg+Umschalt+Enter abgeschlossen. |
| Namen festlegen | anstatt mit fixen Bezügen zu arbeiten |
| BEREICH.VERSCHIEBEN | Funktion, um einen dynamischen Bereich zu erstellen |
| INDEX | Funktion, um den dynamischen Titel zu erstellen |
| Kombinationsfeld | Steuerelement, um Anfangsmonat, Endmonat und Projekt auszuwählen |
| Säulendiagramm | Diagramm, das mit Hilfe der oben genannten Elementen dynamisch angepasst wird |
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.
