Excel - Diagrammtipps - Tipp Nr. 821
Dynamische Diagramme
Tipp getestet unter Excel 2007, 2010, 2013, 2013;
Diagramme sind statisch. Sie definieren einen Diagrammbereich (Datenquelle), der die Rubrikenachse, die Namen und natürlich auch und vor allem die Werte der Datenreihen enthält. Die Adressierung erfolgt dabei über absolute Bezüge.
Bei Diagrammen, die "wachsen", wo also regelmäßig neue Datenpunkte dazukommen, wird die Anpassung zur mühsamen Prozedur, auf die man noch dazu oft vergisst.
Mit dem folgenden Workaround können Sie Ihr Diagramm dynamisch machen. Sie müssen sich dann nie wieder um die Ergänzung neuer Daten kümmern:
Als Datenbasis für das Diagramm dient diese Tabelle:
Und so wird's gemacht:
- Erstellen Sie dynamischen Bereichnsnamen für die Rubrikenachse (= für die Monate) und die Datenreihen (=Werte)des Diagramms :
- Menüband Register Formeln - Namens-Manager - Neu .
- Im Dialog schreiben Sie ins Feld Name "Datum". Ins Feld Bezieht sich auf kommt folgende Formel
=BEREICH.VERSCHIEBEN(Tabelle1!$A$3;0;0;ANZAHL2(Tabelle1!$A:$A)-2;1)1
- Bestätigen Sie den neuen Namen mit OK.
- Für die Datenreihen gehen Sie sinngemäß vor:
Der Name "Umsatz" erhält den Bezugsbereich
=BEREICH.VERSCHIEBEN(Tabelle1!$B$3;0;0;ANZAHL2(Tabelle1!$A:$A)-2;1)
,
der Name "Ertrag" bekommt als Bezug
=BEREICH.VERSCHIEBEN(Tabelle1!$C$3;0;0;ANZAHL2(Tabelle1!$A:$A)-2;1)
- Diese Formeln sind so zu lesen: "Bilde einen Bereich, der in der Zelle A3, B3 bzw. C3 beginnt und zwei Zeilen weniger enthält, als sich in der Spalte A nichtleere Zellen finden." Damit haben wir dynamische Bereiche erzeugt, Bereiche, die sich bei zusätzlichen Dateneinträgen selbstädnig erweitern.
- Erstellen Sie jetzt ein Balkendiagramm für den Datenbereich "A2:C6"
- Datenbereich markieren.
- Taste .
- Rechtsklick ins neue Diagramm und Speicherort
- Option Als Objekt in Tabelle wählen und OK.
- Im Diagramm führen Sie nun einen Rechtsklick auf die Datenreihe "Umsatz" aus.
- Wählen Sie im Kontextmenü Daten auswählen
.
-
- In der Liste Legendeneinträge (Reihen) klicken Sie auf die Umsatz-Datenreihe und anschließend auf Bearbeiten.
- Ins Feld Reihenwerte tragen Sie ein: "=Tabelle1!Umsatz" und OK.
- Anschließend klicken Sie auf Bearbeiten im Bereich Horizontale Achsenbeschriftungen (Rubrik).
- In den Achsenbeschriftungsbereich kommt: "=Tabelle1!Datum" und OK.
- Für die Spalte "Gewinn" gehen Sie sinngemäß vor.
- Schließen Sie den Datenquellen-Dialog mit OK.
Uff! Fertig!
Sie werden gleich feststellen, die Mühe hast sich gelohnt!
Ergänzen Sie einfach zwei oder drei neue Zeilen!
und freuen Sie sich.
1 Der Wert "-2" in der Funktion ANZAHL2 muss gesetzt werden, da zwei nichtleere Zellen (A1 und A2) nicht zum Bereich zählen sollen.