Die Funktion BEREICH.VERSCHIEBEN gibt einen Bezug zurück, der gegenüber dem angegebenen Bezug verschoben ist.
Eine ausführliche Erläuterung zum Thema 'Die Funktion gibt einen Bezug zurück' ist ▶ hier zu finden.
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.
Das 'Ergebnis' der Funktion BEREICH.VERSCHIEBEN ist also kein berechneter Wert, sondern eine berechnete Adresse. Diese Funktion gibt keinen Wert zurück, sondern einen Bezug, nämlich den Bezug zum Bereich $F$5:$F$16.
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 die Adresse einer einzelnen Zelle, sondern die Adresse eines Bereichs bestehend aus mehreren Zellen zurückgibt.
Wenn nun die Funktion BEREICH.VERSCHIEBEN nicht in eine Summenformel integriert ist, wo die Werte des zurückgegebenen Bereichs zu einem einzigen Wert addiert werden, sondern direkt in einer Zelle steht, ergibt sich die Frage, was nun in dieser Zelle als 'Ergebnis' angezeigt wird.
Grundsätzlich gilt: Gibt eine Funktion einen Wert zurück (hat sie also eine Zahl, einen Text oder ein Datum berechnet), wird dieser Wert in die betreffende Zelle eingetragen. Gibt eine Funktion einen Bezug zurück (hat sie also die Adresse einer anderen Zelle ermittelt), wird der Inhalt dieser anderen Zelle in die betreffende Zelle eingetragen. Intern wird ein Bezug (eine Zelladresse) zurückgegeben, extern wird jedoch der Inhalt dieser Zelladresse angezeigt. Nach außen entsteht also der Eindruck, als ob die Funktion einen Wert ermittelt und diesen Wert in die betreffende Zell eingetragen hätte.
Wenn die ermittelte Adresse nun ein Bereich von mehreren Zellen ist, müssen die Inhalte von mehreren Zellen irgendwo eingetragen und somit sichtbar gemacht werden. 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.
