Die Funktion BEREICH.VERSCHIEBEN gibt einen Bezug zurück, der gegenüber dem angegebenen Bezug verschoben ist.
Was bedeutet "Einen Bezug zurückgeben"?
Die meisten Excel-Funktionen berechnen oder ermitteln ein 'Ergebnis', d.h. einen Zahlenwert, ein Datum oder auch einen Text, und zeigen dieses 'Ergebnis' in der Zelle an, in der sich die Formel mit dieser Funktion befindet. Man sagt, sie geben einen 'Wert' zurück. Zum Beispiel berechnet die Funktion SUMME einen Zahlenwert und trägt ihn in die Zelle ein, in der die Summenformel steht.
Es gibt aber auch Funktionen wie zum Beispiel INDIREKT oder BEREICH.VERSCHIEBEN, die einen Zellbezug - oder kürzer einen Bezug - zurückgeben. Ein Zellbezug, d.h. ein Bezug zu einer Zelle oder einem ganzen Bereich von Zellen, ist nichts anderes als die Adresse dieser Zelle oder dieses Zellbereichs, also so etwas wie "A24" oder "B2:D35".
Die Funktion BEREICH.VERSCHIEBEN wird da gebraucht, wo der Bezug, also die Adresse des Zellbereichs nicht festgelegt sein soll, sondern in Abhängigkeit von einer Eingabe in einer anderen Zelle variabel bleiben soll. Dies verdeutlicht das folgende Beispiel.
Beispiel 1
Die Umsätze der einzelnen Mitarbeiter sind für die Monate Januar bis April aufgelistet. Wenn man in Zelle I3 einen Monatsnamen eingibt, bekommt man in Zelle I4 die Summe der Umsätze dieses Monats angezeigt.
Formel in der Zelle I4:
=SUMME(BEREICH.VERSCHIEBEN($B$5:$B$16;;MONAT(1&I5)))
| Bezug: | $B$5:$B$16 |
| Zeilen: | keine Angabe, also Standardwert 0 |
| Spalten: | MONAT(1&I5) |
| Höhe: | - - |
| Breite: | - - |
Zum Ausprobieren:
Wie der Name der Funktion erkennen lässt, wird hier ein Bereich verschoben. Darunter darf man sich allerdings nicht vorstellen, dass die Inhalte eines bestimmten Bereichs auf dem Tabellenblatt an einen anderen Ort verschoben werden, sondern die Funktion BEREICH.VERSCHIEBEN erzeugt nur die Adresse eines Bereichs, der gegenüber dem angegebenen Bereich (hier $B$5:$B$16) verschoben ist. In der Formel summiert die Funktion SUMME dann alle Zahlen dieses verschobenen Bereiches.
Die Adresse des verschobenen Bereichs wird folgendermaßen gebildet:
Ausgangspunkt ist die linke obere Ecke des angegebenen Bereichs $B$5:$B$16. Das wäre bei diesem Beispiel die Zelle $B$5. Der Parameter 'Zeilen' gibt an, um wie viele Zeilen diese linke obere Ecke nach unten - oder bei negativem Wert nach oben - verschoben wird. Entsprechend gibt der Parameter 'Spalten' an, um wie viele Spalten diese linke obere Ecke nach rechts - bei negativem Wert nach links - verschoben wird.
In unserem Beispiel ist für den Parameter 'Zeilen' kein Wert angegeben. Das hat zur Folge, dass automatisch der Standardwert 0 verwendet wird. Es findet also keine Verschiebung in vertikaler Richtung statt.
Der Wert für den Parameter 'Spalten' wird durch den Ausdruck MONAT(1&I5) berechnet. Dieser sieht auf den ersten Blick etwas komisch aus. Das Zeichen '&' in der Klammer ist die sog. Textverknüpfung. Hier werden zwei Texte zu einem einzigen vereinigt. Der erste Text ist die Zahl 1 und der zweite Text ist der Inhalt der Zelle I5. Sauberer sähe es aus, wenn da stünde: "1"&I5. Aber bei Zahlen ist Excel großzügig und wandelt die 1 automatisch in ein internes Textformat um.
Wenn in Zelle I5 nun der Monat April eingetragen wird, entsteht in der Klammer der Gesamttext "1April". Auch hier ist Excel sehr großzügig, indem es in diesem Text das Datum "1. April" erkennt, so dass die Funktion MONAT aus diesem Datum die Monatsnummer 4 generiert. Dies ist nun der Wert für den Parameter 'Spalten'. Damit wird die linke obere Ecke um 4 nach rechts verschoben, das heißt zur Zelle F5.
Die Zelle F5 ist also die linke obere Ecke des verschobenen Bereichs. Wären die letzten beiden Parameter 'Höhe' und 'Breite' vorhanden, würden sie angeben, wie weit sich der verschobene Bereich von F5 aus nach unten und nach rechts ausdehnt. Fehlen diese Parameter (wie in unserem Beispiel), so sind die Höhe und die Breite des verschobenen Bereichs identisch mit der Höhe und der Breite des im ersten Parameter angegebenen Bereichs $B$5:$B$16, also Höhe 12 und Breite 1.
Die Funktion BEREICH.VERSCHIEBEN erzeugt somit in diesem Fall die Adresse $F$5:$F$16. Die Funktion SUMME addiert alle Umsätze des Monats April.
Beispiel 2
Das zweite Beispiel soll die direkte Verwendung der Funktion BEREICH.VERSCHIEBEN zeigen. Dabei ist nämlich die Besonderheit zu beachten, dass diese Funktion dann meistens nicht ein einzelnes Ergebnis, sondern einen ganzen Bereich von Werten zurückgibt. Damit fällt die Formel in der Zelle in die Kategorie "Matrixformel".
Eine grundlegende und ausführliche Einführung in das Thema "Matrixformeln" gibt es ▸hier.
Verwendet man eine ältere Excelversion (Excel 2019 oder älter), so muss man vor der Formeleingabe einen Bereich passender Größe selektieren und die Formeleingabe mit STRG-SHIFT-RETURN abschließen. Bei neueren Excelversionen kann man wie gewohnt die Formel in eine Zelle eingeben und mit RETURN abschließen. Excel erkennt automatisch, dass es sich um eine Matrixformel handelt, und füllt einen Bereich passender Größe mit den ermittelten Werten.
Formel in der Zelle F5:
{=BEREICH.VERSCHIEBEN(A5;5;1;4;3)}
| Bereich: | A5 |
| Zeilen: | 5 |
| Spalten: | 1 |
| Höhe: | 4 |
| Breite: | 3 |
Die letzten beiden Parameter 'Höhe' und 'Breite' geben hier die Ausdehnung des verschobenen Bereichs an: 4 Zeilen und 3 Spalten. Deshalb ist es egal, ob als erster Parameter (Parameter 'Bereich') die Adresse eines mehrzelligen Bereichs angegeben wird oder nur die Adresse einer einzelnen Zelle.
Der Parameter 'Zeilen' besagt, dass die linke obere Ecke, also die Zelle A5, um 5 Zeilen nach unten verschoben wird, und der Parameter 'Spalten' besagt, dass außerdem eine Verschiebung um 1 Spalte nach rechts stattfindet. Die linke obere Ecke des verschobenen Bereichs ist somit die Zelle B10. Von dieser Zelle aus wird nun ein Bereich aufgezogen, der sich 4 Zeilen nach unten und 3 Spalten nach rechts erstreckt. Das ist der Bereich B10:D13.
Die Funktion BEREICH.VERSCHIEBEN gibt in diesem Beispiel also die Adresse B10:D13 zurück. Man hätte denselben Effekt, wenn man in die Bearbeitungszeile die Formel =B10:D13 eintippen würde. Wie gesagt, muss bei älteren Excelversionen der Bereich F5:H8 vor der Formeleingabe selektiert und die Formeleingabe mit STRG-SHIFT-RETURN abgeschlossen werden. Die geschweiften Klammern dürfen nicht mit eingegeben werden. Sie werden automatisch gesetzt.
