Dynamische Diagramme erstellen

In Excel 2010 kann man dynamische Diagramme erstellen. Exceltabellen verändern sich ständig, neue Daten kommen hinzu oder verändern sich. Doch die häufig eingesetzten Diagramme ändern sich deshalb noch lange nicht. Über einen raffinierten Trick können Sie ein Diagramm aber so gestalten, dass es bei Zunahme des Datenumfangs automatisch erweitert wird und die grafische Darstellung um die entsprechenden Säulen erweitert.

Schritt 1: Fertigen Sie eine einfache Tabelle nach unserem Muster an, oder öffnen Sie ein bestehendes Worksheet, an dem Sie unbeschadet arbeiten können. Klicken Sie in den Datenbereich und erstellen Sie ein Diagramm durch Aktivieren des Registers Einfügen und der Auswahl eines Diagrammtyps, z. B. Säulen und im Untermenü z. B. 2D_Säule auf das Symbol Gruppierte Säulen. Möglicherweise müssen Sie die Darstellung noch anpassen, beispielsweise die Achsen vertauschen. Dies machen Sie über einen Rechtsklick auf das Diagramm Daten auswählen, Zellen/Spalten wechseln. Speichern Sie die Arbeitsmappe unter dem Namen "DynamischesDiagramm" ab, um eine spätere Zuweisung zu erleichtern.

Schritt 2: Erweitern Sie den vorhandenen Datenbereich um eine weitere Spalte - in unserem Beispiel wäre das der Monat April. Nutzen Sie dazu auf jeden Fall die direkt daneben angrezende Spalte. Der Standard in Excel: Eine Veränderung im Diagramm ergibt sich nur, wenn Sie den Datenbereich manuell anpassen. Klicken Sie dazu auf Ihr Diagramm, und ziehen Sie den blauen Rahmen um den erweiterten Datenbereich. Mit Dynamik hat diese Vorgehensweise aber herzlich wenig zu tun.

Schritt 3: Die dynamische Komponente kommt erst mit dem Einsatz von Funktionen ins Spiel. In einem Diagramm können Sie nicht direkt mit Formeln arbeiten, wohl aber mit festen Namen, die Sie über den "Namensmanager" vergeben. Und jedem Namen können Sie eine Formel zuweisen, und so tricksen Sie das statische Diagramm aus. Klicken Sie in der Tabelle auf den ersten numerischen Wert (in unserem Beispiel in die Zelle "B2"). Aktivieren Sie anschließend das Register Formeln.

Schritt 4: Dort befindet sich die Gruppe "Definierte Namen". Klicken Sie in dieser Gruppe auf den Befehl Namen definieren. Das Dialogfenster Neuer Name wird eingeblendet, und im Feld Name sollte ein Begriff stehen, in unserem Beispiel "Laufende Fixkosten". Ist dies nicht der Fall, dann geben Sie ihn ein. Im Auswahlfenster Bereich lassen Sie als Bereich den Eintrag Arbeitsmappe stehen. In das Eingabefeld "Bezieht sich auf" kommt jetzt die Funktion:

=BEREICH.VERSCHIEBEN(Tabelle1!$B$2;;;;ANZAHL2(Tabelle1!$2:$2)-1)

Die erste Funktion bezieht sich auf die "Tabelle" und den Startbereich "B2". Die verschachtelte Funktion "ANZAHL2" zählt die belegten Zellen der zweiten Zeile aus. Da in der ersten Zelle bereits etwas steht, muss diese in der Berechnung wieder abgezogen werden (-1). So wird die aktuelle "Breite" des Verschiebevorgangs berechnet. Beachten Sie die Anzahl der Semikolons. Mit einem Klick auf die Schaltfläche OK wird der Name abgespeichert. Nach der Festlegung des ersten Namens werden weitere Namen benötigt, und zwar für Variable Kosten, Einnahmen und GV. Gehen Sie genauso vor wie eben beschrieben und geben in die Zeile Bezieht sich auf ein:

=BEREICH.VERSCHIEBEN(Tabelle1!$B$3;;;;ANZAHL2(Tabelle1!$3:$3)-1)

Dies ist das gleiche Schema wie im ersten Namen, nur um eine Zeile nach unten verschoben. Der Startpunkt ist also die Zelle "B3". Analog verfahren Sie mit weiteren Zeileneinträgen, in unserem Beispiel Einnahmen und GV. Achten Sie darauf, dass Sie im Dialogfenster Neuer Name nicht mit Pfeiltasten arbeiten können, am besten Sie bereiten den gesamten Formelbefehl in einer Textverarbeitung vor und fügen ihn dann nur mehr mit Copy & Paste ein.

Schritt 5: Jetzt ist die Voraussetzung geschaffen, Wertbereiche in einem Diagramm automatisch zu erweitern. Um auch die Überschriften (Monte) in das dynamische Diagramm einzubinden, benötigen Sie eine weitere Formel. Klicken Sie im Register Formeln auf den Befehl Namen definieren. Im dann eingeblendeten Dialogfenster vergeben Sie den Namen "Monat". In das Eingabefeld "Bezieht sich auf" geben Sie folgende Funktion ein:

=BEREICH.VERSCHIEBEN(Tabelle1!$B$1;;;;ANZAHL2(Tabelle1!$1:$1)-1)

Die Funktion ist identisch zu den bisherigen Funktionen, auch wenn sich im Auswahlbereich keine Zahlen befinden. Der Startpunkt ist die Zelle "B1".

Schritt 6: Jetzt müssen Sie die Namen mit den hinterlegten Formeln in ein neues Diagramm integrieren. Klicken Sie auf eine freie Zelle, und aktivieren Sie das Register Einfügen, dann Säule, Gruppierte Säulen. Es wird ein leeres Diagrammfenster eingeblendet. Aktivieren Sie im Menüband das Register Entwurf, und klicken Sie in der Gruppe Daten auf den Befehl Daten auswählen. Im Dialogfenster Datenquelle auswählen klicken Sie auf die Schaltfläche Hinzufügen. Jetzt wird das Dialogfenster Datenreihe bearbeiten eingeblendet. In das Feld Reihenname kommt der Verweis des ersten Standortes, also:

=Tabelle1!$A$2

Anstatt den Verweis manuell einzugeben, klicken Sie zunächst in das Feld Reihenname und dann in die Zelle A2. Der Verweis wird dann automatisch eingetragen. Jetzt geht es an die Dynamik. Diese wird in das Feld Reihenwerte eingebaut:

=DynamischesDiagramm.xlsx!Laufende_Fixkosten

Klicken Sie erneut auf Hinzufügen, und wiederholen Sie den Vorgang für die Spalten Variable Kosten, Einnahmen, etc. Folgen Sie dabei der vorgegebenen Syntax für die Reihenwerte, und vergessen Sie ggf. nicht einen Unterstrich anstelle eines Leerzeichens:

=Dateiname.xlsx!angelegter_name

Jetzt muss noch die horizontale Achse beschriftet werden. Klicken Sie dazu im Dialogfenster Datenquelle auswählen auf der rechten Seite des Dialogfensters ("Horizontale Achsenbeschriftung") auf die Schaltfläche Bearbeiten. Das Dialogfenster Achsenbeschriftungsbereich wird eingeblendet. Geben Sie hier ein:

=DynamischesDiagramm.xlsx!Monat

Und klicken Sie anschließend auf die Schaltfläche OK. Sobald Sie nun einen Wert in der Tabelle eingeben, wird dieser automatisch im Diagramm übernommen. Zum Schluss müssen Sie noch das ursprüngliche, statische Diagramm löschen.