Flexibles Reporting leicht gemacht: Dynamische Excel-Diagramme

Wer in Microsoft Excel häufig mit Diagrammen arbeitet, kennt das Problem: Man möchte etwas darstellen, dessen Inhalt sich regelmäßig ändert. Eine heute erstellte Analyse ist aber schon im nächsten Monat nicht mehr aktuell, wenn Daten ergänzt werden. Erst wenn man den Quellbereich des Diagramms anpasst, zeigt Microsoft Excel das richtige Diagramm. Man kann Microsoft Excel aber auch dazu bringen, die Anpassung vollautomatisch vorzunehmen. Wie das geht und wie man auf diese Weise ein gut funktionierendes dynamisches Reporting aufbauen kann, beschreibt Tanja Kuhn in diesem Artikel. Die fertige Lösung können Sie als Microsoft Excel-Datei herunterladen.

Flexibles Reporting leicht gemacht: Dynamische Excel-Diagramme

Wer in Microsoft Excel häufig mit Diagrammen arbeitet, kennt das Problem: Man möchte etwas darstellen, dessen Inhalt sich regelmäßig ändert. Eine heute erstellte Analyse ist aber schon im nächsten Monat nicht mehr aktuell, wenn Daten ergänzt werden. Erst wenn man den Quellbereich des Diagramms anpasst, zeigt Microsoft Excel das richtige Diagramm. Man kann Microsoft Excel aber auch dazu bringen, die Anpassung vollautomatisch vorzunehmen. Wie das geht und wie man auf diese Weise ein gut funktionierendes dynamisches Reporting aufbauen kann, beschreibt Tanja Kuhn in diesem Artikel. Die fertige Lösung können Sie als Microsoft Excel-Datei herunterladen.

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.

Tabelle 1: Funktionen und Befehle, die für den Lösungsansatz benötigt werden.
Funktion/BefehlVerwendung
MTRANSFunktion, 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 festlegenanstatt mit fixen Bezügen zu arbeiten
BEREICH.VERSCHIEBENFunktion, um einen dynamischen Bereich zu erstellen
INDEXFunktion, um den dynamischen Titel zu erstellen
KombinationsfeldSteuerelement, um Anfangsmonat, Endmonat und Projekt auszuwählen
SäulendiagrammDiagramm, das mit Hilfe der oben genannten Elementen dynamisch angepasst wird

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

Um zu einer dynamischen Lösung zu kommen, dürfen wir nicht mit fixen Bezügen arbeiten, sondern müssen den einzelnen Zellen und Bereichen Namen zuweisen. Die Namen brauchen wir später für diverse Zellbezüge. Namen für Bereiche haben den Vorteil, dass später bei Änderungen (z.B. Hinzufügen neuer Monate) nur der Namensbereich, nicht jedoch die Formeln angepasst werden müssen. Zudem erleichtert die Vergabe von Namen die Lesbarkeit von Formeln.

  • Markieren Sie den Bereich (zum Beispiel A13:A18).
  • Wählen Sie das Menü Einfügen / Name / Definieren.
  • Geben Sie den Namen ein (zum Beispiel Monate), siehe Bild 2.

Bild 2: Namen festlegen

  • Klicken Sie auf "Hinzufügen", um den Namen festzulegen.
  • Wiederholen Sie dies für jeden Bereich entsprechend Tabelle 2 oder Bild 3.

Bild 3: Für die markierten Bereiche werden Namen definiert.

Tabelle 2: Verwendung der Bezüge.
NameBezugVerwendung
Monate=$A$13:$A$18Wird benötigt, um die Kombinationsfelder der Monate mit den entsprechenden Daten zu füllen.
Beginn=$C$13Ausgabeverknüpfung für das Kombinationsfeld Anfangsmonat
Ende=$C$14Ausgabeverknüpfung für das Kombinationsfeld Endmonat
Projekt=$C$15Ausgabeverknüpfung für das Kombinationsfeld Projekt
Datenmatrix=$C$3Hiermit wird die linke obere Ecke der Datenmatrix für die Funktion BEREICH.VERSCHIEBEN festgelegt.
Projekte=$B$4:$B$11Listenbereich für das Kombinationsfeld Projekte

Diagrammbereich dynamisch bilden

Bewertungen und Kommentare

(nur angemeldete Benutzer)

Diese Funktion steht nur eingeloggten Nutzern zur Verfügung. Jetzt einloggen
Gesamt
Bewertungen 3
Alle anzeigen