Dynamische Bereiche II

Dynamische Bereiche in Excel sind praktisch. Das haben Sie bereits im ersten Workshop zu diesem Thema gesehen. Aber über das bereits Gesehene hinaus bieten sie noch einige Möglichkeiten mehr.

Dieser Workshop dreht sich um die Nutzung dynamischer Bereiche zur flexiblen Anpassung und Darstellung von Diagrammen. Sie erfahren, wie Sie mithilfe von Steuerelementen die in einem Diagramm angezeigten Daten sehr komfortabel auswählen können. 

Wie bereits im ersten Teil verwenden wir auch hier die Funktion BEREICH.VERSCHIEBEN, die einiges mehr zu bieten hat, als bisher gezeigt. Sollten Sie mit der Funktion noch nicht vertraut sein, schauen Sie sich bitte zuerst den Workshop Dynamische Bereiche I an. In diesem wird die Funktionsweise detalliert mit Beispielen erklärt.

Beispiel 1

Strom-VerbrauchstabelleFür unser erstes Beispiel liegen uns Strom-Verbrauchsdaten über zwei Jahre (24 Monate) vor. In einem Diagramm sollen jeweils sechs Monate gezeigt werden, wobei die Auswahl der Monate einfach und ohne manuelle Anpassung des Diagramms zu bewerkstelligen sein.

Die Daten für unser Diagramm stehen in den beiden Spalten A und B (A1:B25). Da wir immer nur sechs Monate zeigen wollen, erstellen wir das Diagramm nicht aus diesen Daten sondern erzeugen eine kleine Tabelle, in die die Daten der betreffenden Monate dynmaisch, d.h., per Formel eingetragen werden.

Diesen Tabellenbereich habe ich auf demselben Tabellenblatt in Spalte D und E (D1:E7) integriert, wie die rechts angezeigten Daten. Selbstverständlich können Sie die Daten aber auch auf einem anderen Blatt unterbringen.

Der selektive BereichZur Selektion, bei welchem Monat die sechs anzuzeigenden Monate beginnen sollen, benötigen wir eine weitere Zelle, in der die Angabe steht. In diesem Fall verwende ich Zelle D9 und trage dort die Zahl 9 ein (siehe linkes Bild).

In Zelle D2 beginne ich mit der ersten Formel:

  • =BEREICH.VERSCHIEBEN($A$1;$D$9+ZEILE()-ZEILE($D$2);0)

Der erste Parameter zeigt auf den Startpunkt in A1 unserer Verbrauchsliste. Da die Formel kopiert werden soll, wurde er absolut definiert, was an den Dollarzeichen zu erkennen ist.

Von diesem Startpunkt aus soll in unserem Beispiel der neunte Monat als Start gelten. Demnach wird die Zeile um den Wert aus Zelle D9 nach unten verschoben. Würde diese Formel kopiert, stünde in jeder Zelle der September 2012, was ja nicht stimmt. Also müssen wir einen Zähler einbauen, der sich bei jeder Kopieraktion um den Wert eins erhöht: die Zeilennummer. Die Funktion ZEILE() liefert ohne Angabe von Parametern die Nummer der Zeile, in der die Formel steht, in diesem Beispiel 2, da wir in D2 die Formel eingeben. Den so erhaltenen Wert addieren wir auf die Zahl aus D9.

Auf diese Weise kämen wir auf 11 (9+2), weshalb wir die Zeilennummer der Zelle D2 abziehen (also 2) und zum richtigen Ergebnis 9 kommen.

Kopieren Sie anschließend diese Formel nach unten bis in D7.

In Zelle E2 fügen Sie dieselbe Formel ein, wobei der letzte Parameter statt 0 eine 1 erhält, da wir den Zeiger um eine Spalte nach rechts bewegen müssen. Kopieren Sie auch diese Formel nach unten.

Um die Zahl nicht manuell eingeben zu müssen verwenden wir stattdessen ein Steuerlement.

  • Öffnen Sie das Register Entwicklertools. Sollte dieses nicht angezeigt werden, schalten Sie es unter Start - Optionen - Menüband anpassen durch Aktivierung des Optionskästchens in der rechten Auflistung ein.
  • Drehfeld erzeugenAuf dieser Registerkarte finden Sie im Abschnitt Steuerelemente die Dropdownliste Einfügen und dort unter Formularsteuerelemente das Element Drehfeld.
  • Klicken auf dieses Element und ziehen anschließend auf dem Tabellenblatt ein Drehfeld auf. Halten Sie dabei die Alt-Taste gedrückt, dann richtet sich die Größe nach der Zellgröße, über die Sie aufziehen.
  • Im Bild sehen Sie das fertige Steuerelement.
  • Im Kontextmenü dieses Drehfelds rufen Sie den Befehl Steuerelement formatieren auf und geben dort die folgenden Werte ein:
    Minimalwert: 1; Maximalwert: 19; Schrittweite: 1; Zellverknüpfung: D9
    Der Maximalwert wird auf 19 gestellt, sodass alle Monate ausgewählt werden können (19+6 jeweils einschließlich = sechs Monate).
  • Probieren Sie das Drehfeld aus, indem Sie vorwärts oder zurück blättern. Die kleine dynamische Tabelle muss sich entsprechend anpassen.
  • Markieren Sie den Zellbereich von D1:E7 und erzeugen ein Säulendiagramm (oder beliebiges anderes).
  • Platzieren Sie das Diagramm. Ihre dynamische Anpassung ist fertiggestellt.

 Das fertige Diagramm

Anstatt eines normalen Diagramms sind auch Sparklines möglich, die es allerdings erst ab Excel Version 2010 gibt.

Sparklines in Aktion