Excel Dynamische Bereiche II
Workshops > Excel
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 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
Für unser 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 soll.
Die Daten für unser Diagramm stehen in den beiden Spalten A und B (A1:B25). Ein Auszug daraus ist im Bild zu sehen. 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 übernommen werden.
Diesen zweiten Tabellenbereich habe ich der Übersichtlichkeit wegen auf demselben Tabellenblatt wie die Originaldaten in Spalte D und E (D1:E7) integriert. Selbstverständlich können Sie die Daten aber auch auf einem anderen Blatt unterbringen.
Zur Selektion, bei welchem Monat die sechs anzuzeigenden Monate beginnen sollen, benötigen wir eine Zelle, in der diese Angabe stehen soll. In diesem Fall verwenden wir Zelle D9 und tragen dort die Zahl 9 ein (siehe Bild).
In Zelle D2 wird die erste Formel eingetragen:
=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 als Absolutbezug definiert, was an den Dollarzeichen zu erkennen ist.
Von diesem Startpunkt aus soll in unserem Beispiel der in Zelle D9 ausgewiesene Monat als Start gelten. Demnach wird die Zeile um den Wert aus dieser Zelle nach unten verschoben. Würde diese Formel kopiert, stünde in jeder Zelle der September 2012. Dies wäre selbstredend nicht korrekt. Wir müssen einen Zähler einbauen, der sich bei jeder Kopieraktion in die nächste Zeile um den Wert eins erhöht. Wir verwenden dazu 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. Da in Formeln keine Absolutwerte stehen sollten, wird der Wert 2 nicht durch direkte EIngabe der Zahl sondern durch die Funktion ZEILE ermittelt. Anpassungen, zum Beispiel durch Einfügen weiterer Zeilen vor der Tabelle, führen dadurch weiterhin zum korrekten Ergebnis.
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 für den Startwert nicht manuell eingeben zu müssen verwenden wir stattdessen ein Steuerlement.
- Öffnen Sie das Register Entwicklertools. Sollte dies bei Ihnen nicht angezeigt werden, schalten Sie es unter Start - Optionen - Menüband anpassen durch Aktivierung des Optionskästchens in der rechten Auflistung ein.
- Auf dieser Registerkarte finden Sie im Abschnitt Steuerelemente die Dropdownliste Einfügen und dort unter Formularsteuerelemente das Element Drehfeld.
- Klicken Sie auf dieses Element, lassen den Maustaste los und ziehen anschließend auf dem Tabellenblatt per gedrückter Masutaste ein Drehfeld auf.
- 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 (Anzahl gesamt - 6) gestellt, sodass alle Monate ausgewählt werden können (19+6 jeweils einschließlich = sechs Monate). - Nach dem Schließen des Dialogs und Deaktivieren des Drehfelds ist dieses funktionstüchtig. Probieren Sie es aus, indem Sie vorwärts oder zurück blättern. Die kleine dynamische Tabelle passt sich entsprechend an.
- Platzieren Sie das Drehfeld über der Zelle D9, sodass der ausgewählte Wert nicht sichtbar ist. Alternativ können Sie den Wert per Format unsichtbar machen.
- Markieren Sie den Zellbereich von D1:E7 und erzeugen ein Diagramm.
- Platzieren Sie das Diagramm. Ihre dynamische Anpassung ist fertiggestellt.
Anstatt eines normalen Diagramms sind zum Beispiel auch Sparklines möglich.
Mit Hilfe des hier gezeigten Prinzips lassen sich dynamische Bereiche für vielfältige EInsatzzwecke erstellen. Probieren Sie's einfach mal aus.