Excel Dynamische Bereiche I
Workshops > Excel
Dynamische Bereiche in Excel sind eine sehr praktische Sache, wenn es darum geht, Bereiche flexibel auszuwählen bzw. zu verwenden.
Eine eigens dafür geschaffene Funktion erfüllt genau diesen Zweck: BEREICH.VERSCHIEBEN. Mit deren Hilfe lassen sich zum Beispiel Diagramme dynamisch gestalten, aber auch einfache Aufgaben wie die automatische Anpassung von zu summierenden Bereichen sind möglich. Die Einsatzmöglichkeiten sind vielfältig.
In diesem Workshop erfahren Sie, wie diese Funktion eingesetzt werden kann, und wie Sie gekonnt mir ihr arbeiten.
Grundsätzliches
Die Funktion BEREICH.VERSCHIEBEN verschiebt einen Zellbereich um eine angegebene Anzahl Zeilen und Spalten. Man kann sich die Funktion vorstellen wie ein Navigationsgerät, dem man Koordinaten übergibt und das diese anschließend ansteuert.
Die Syntax der Funktion lautet:
=BEREICH.VERSCHIEBEN(Bezug;Zeilen;Spalten;[Höhe]:[Breite])
Die Parameter haben folgende Bedeutung:
- Bezug
Mussparameter.
Gibt den Zellbezug des Startpunktes für die Verschiebeaktion an. - Zeilen
Mussparameter.
Definiert die Anzahl Zeilen, um die dieser Startpunkt verschoben werden soll.
Der Parameter kann positive (nach unten) oder negative Zahlen (nach oben) verarbeiten. - Spalten
Mussparameter.
Definiert die Anzahl Spalten, um die dieser Startpunkt verschoben werden soll.
Der Parameter kann positive (nach rechts) oder negative Zahlen (nach links) verarbeiten.
- Höhe
Optionaler Parameter.
Gibt an, wie hoch (wieviele Zeilen) der zurückzugebende Zellbezug sein soll.
Wird der Parameter nicht angegeben, wird der Wert 1 angenommen. - Breite
Optionaler Parameter.
Gibt an, wie breit (wieviele Spalten) der zurückzugebende Zellbezug sein soll.
Wird der Parameter nicht angegeben, wird der Wert 1 angenommen.
Beispiel 1
In diesem Beispiel sehen Sie, wie die Funktion grundsätzlich funktioniert.
In einer Tabelle stehen in Spalte A2:A5 Beträge. In Zelle C5 soll der Betrag ausgegeben werden, der der Position aus Zelle C3 in dieser Matrix entspricht.
In dieser einfachen Aufgabenstellung könnten wir das mit der Funktion INDEX erledigen (wie im Workshop Such- und Bezugsversionen II beschrieben):
Wir übergeben der Funktion die Position der Matrix (A2:A5) sowie die Zeilenzahl in dieser MAtrix (3 aus Zelle C2). Da die Spaltenangabe nicht angegeben wurde, erfolgt keine horizontale Verschiebung.
=INDEX(A2:A5;C2)
Stattdessen verwenden wir aber hier in Zelle C5 die Funktion BEREICH.VERSCHIEBEN.
=BEREICH.VERSCHIEBEN(A1;C2;0)
Als Bezug übergeben wir die Startzelle A1, definieren die Anzahl der zu verschiebenden Zeilen mit dem Wert aus Zelle C2 und geben 0 als zu verschiebende Spalten an. Somit erhalten wir den Wert aus Zelle A1 + 3 = Zelle A4 = 300.
Beispiel 2
Im zweiten Beispiel soll die Summe über einen Bereich erstellt werden. Wird dieser Bereich erweitert, soll dies in der Summenformel automatisch berücksichtigt werden. So kann die Liste wachsen, die Summenfunktion past sich automatisch an.
Die Formel für diese dynamische Summierung lautet:
=SUMME(BEREICH.VERSCHIEBEN(B2;;;ANZAHL2($A:$A)-1;1))
Als Startpunkt der Summierung wird B2 übergeben (die erste Zahl mit Werten). Die Angaben zu den zu verschiebenden Zeilen und Spalten entfallen, da der komplette Bereich verwendet werden soll. Die Höhe des Bereichs ist variabel und wird mithilfe der Funktion ANZAHL2 für Spalte A berechnet, die alle nichtleeren Zellen zählt. Da die Überschrift ebenfalls enthalten ist, wird anschließend der Wert 1 abgezogen. Die Breite des Bereichs wird mit 1 vorgegeben.
Somit ergibt sich ein Bereich in diesem Beispiel von B2:B6, der mit der Funktion SUMME summiert wird.
Beispiel 3
Ziel in diesem Beispiel ist es, ein Diagramm zu erstellen, das seine Daten aus einer dynamischen Liste erhält. Werden dieser Liste weitere Daten hinzugefügt, soll dies im Diagramm automatisch berücksichtigt werden.
Der im Diagramm dargestellte Datenbereich erstreckt sich aktuell von Zelle A1:B6 (Überschriften eingeschlossen). Wird nun in Zeile 7 ein weiterer Eintrag vorgenommen, soll dieser ebenfalls im Diagramm berücksichtigt werden. Dieses soll sich automatisch an den geänderten Bereich anpassen.
Erzeugen Sie nach dem Markieren der Zellen von A1:B6 ein Balkendiagramm.
Die aktuelle Befehlszeile für die Datenreihe des Diagramms auf Tabelle Preise lautet: danach:
=DATENREIHE(Preise!$B$1;Preise!$A$2:$A$6;Preise!$B$2:$B$6;1)
Eigene Formeln sind in Diagrammbezügen nicht zulässig. Eine direkte Einbindung der Funktion BEREICH.VERSCHIEBEN ist daher nicht möglich, es muss ein Umweg gegangen werden. Für unsere Zwecke erzeugen wir eine benutzerdefinierte Funktion und geben dieser einen Namen, den wir anschließend in die Befehlszeile zur Datenreihe angeben.
- Öffnen Sie im Register Formeln den Namensmanager.
- Geben Sie im sich öffnenden Dialog nach einem Klick auf Neu... den Namen Ort ein.
- Im Eingabefeld Bezieht sich auf... geben Sie die folgende Formel ein
=BEREICH.VERSCHIEBEN(Preise!$A$1;1;0;ANZAHL2(Preise!$A:$A)-1;1)
- Speichern Sie diese Formel.
- Klicken Sie nochmals auf Neu... und geben als Name Preise_qm und als Formel die Folgende ein.
=BEREICH.VERSCHIEBEN(Preise!$A$1;1;1;ANZAHL2(Preise!$A:$A)-1;1)
- Bestätigen Sie erneut und schließen das Dialogfeld.
Die definierten Namen im Namensmanager:
Wie Sie sehen, gehen beide Formeln von der Startzelle A1 aus. Im ersten Fall (Orte) wird um keine Spalte verschoben, im zweiten Fall um eine Spalte (Beträge). Die Anzahl der Zeilen für die Berechnung der Bereichshöhe wird wieder mithilfe der Funktion ANZAHL2 berechnet.
Als nächsten Schritt werden diese beiden Namen in der Formel der Datenreihe eingesetzt.
- Markieren Sie die Datenreihe.
- Ändern Sie die enthaltene Formel dahingehend ab, dass Sie im zweiten Parameter den Dateinamen (hier Hauspreise.xlsx), gefolgt vom Namen Ort eingeben (statt bisher Tabellenname und Bezug zu den Orten). Der dritte Parameter enthält ebenfalls den Dateinamen und den Namen Preise_qm (statt bisher Tabellenname und Bezug zu den Preisen).
- Bestätigen Sie mit Return.
Die Formel lautet komplett
=DATENREIHE(Preise!$B$1;Hauspreise.xlsx!Ort;Hauspreise.xlsx!Preise_qm;1)
Geben Sie nun Daten in die weiteren Zeilen der Datentabelle ein, erweitert sich das Diagram automatisch und passt sich den neuen Daten an.
Im zweiten Teil des Workshops sehen Sie, wie Sie die Diagrammautomatik weiter ausbauen und komfortabler nutzen können. Den Workshop finden Sie hier.