4 Möglichkeiten, die Excel-Funktion BEREICH.VERSCHIEBEN zu nutzen

Die Funktion BEREICH.VERSCHIEBEN ist eine sehr beliebte Excel-Funktion. Sie gehört zur Kategorie Nachschlage- und Verweisfunktionen. Mit ihr können einzelne Werte abgerufen und Bereiche dimensioniert werden.

 Einen Zellwert auslesen

Die Syntax ist ganz einfach:

=BEREICH.VERSCHIEBEN(Bezug;Zeilen;Spalten;Höhe;Breite)

Bezug ist der Ausgangspunkt, von dem aus verschoben werden soll. Vielfach wird dieser Bezug auch Startpunkt oder nur Start genannt.

Zeilen gibt an, um wieviel Zeilen vom Bezug aus nach oben oder nach unten verschoben werden soll. Spalten gibt an, um wieviel Spalten nach links oder nach rechts verschoben werden soll.

Bei Verwendung nur dieser drei Argumente wird eine einzelne Zelle erreicht und deren Wert ausgegeben.

Ein Beispiel:

B20 soll der Bezug, der Start sein. Du willst den Wert für Haus2 im Monat März wissen. Haus2 befindet sich in Zeile 2, der März in Spalte 3 der Tabelle.

=BEREICH.VERSCHIEBEN(B20;2;3) =409

Definiere B20 in Formeln / Namen definieren als Start und schreibe jetzt so:

=BEREICH.VERSCHIEBEN(Start;2;3) =409

Jetzt könntest Du das Ganze noch dynamisieren und die Abfrage von einer Auswahl abhängig machen.

Definiere B21:B23 als Haus und C20:E20 als Monat. Zwei Zellen erhalten mit der Datenüberprüfung die Listen Haus bzw. Monat als zulässige Werte.

Die Formel lautet jetzt so:

=BEREICH.VERSCHIEBEN(Start;VERGLEICH(B38;Haus;0);VERGLEICH(B39;Monat;0)) =409

Um die Positionen von Haus2 in der Liste Haus bzw. von Mrz in der Liste Monat zu finden, hast Du zusätzlich die Funktion VERGLEICH eingesetzt.

Wenn Du nun die Abfragekriterien änderst, rechnet die Formel den neuen Wert aus. Ändere z.B. auf Haus3 und Jan, dann lautet das Ergebnis 415.

Einen Bereich dimensionieren

Im nächsten Schritt sollen nicht einzelne Monatswert, sondern die Summe aller Monate für ein bestimmtes Haus abgefragt werden. Dazu brauchst Du die beiden noch fehlenden Argumente Höhe und Breite.

Du beginnst mit der Erweiterung der ersten Formel, es soll die Summe Jan-Mrz für Haus2 berechnet werden.

=BEREICH.VERSCHIEBEN(B20;2;1;1;3) =#WERT!

Das Ergebnis ist ‘WERT!, warum? Du hast noch nicht die Funktion SUMME drumherum gelegt.

=SUMME(BEREICH.VERSCHIEBEN(B20;2;1;1;3)) =1217

Zusammengefasst: Mit der Funktion BEREICH.VERSCHIEBEN und den Argumenten Zeilen, Spalten, Höhe und Breite dimensionierst Du einen Bereich, eine Matrix. Die Funktion SUMME schließlich addiert alle darin enthaltenen Werte.

So wie Du die Summe errechnen kannst, ist es auch möglich, sich den Mittelwert ausgeben zu lassen.

=MITTELWERT(BEREICH.VERSCHIEBEN(B20;2;1;1;3)) =405,6667

Oder den INDEX der zweiten Zeile und der zweiten Spalte:

=INDEX(BEREICH.VERSCHIEBEN(B20;2;1;3;3);2;2) =387

Das heißt, in jeder Funktion, die als Argument eine Matrix oder einen Bereich benötigt, kann dieses Argument mit BEREICH.VERSCHIEBEN bestimmt werden.

Zeilen und Spalten verschieben

Eine weitere Nutzungsmöglichkeit ist das Verschieben ganzer Zeilen oder Spalten:

=SUMME(BEREICH.VERSCHIEBEN(C20:E20;2;)) =1217

Als Bezug wird hier nicht eine einzelne Zelle, sondern ein Teil der Kopfzeile verwendet und um zwei Zeilen nach unten verschoben. Eine Spaltenverschiebung findet nicht statt, das Semikolon muss aber dennoch geschrieben werden.

Mit der Verschiebung einer Spalte sieht das so aus:

=SUMME(BEREICH.VERSCHIEBEN(B21:B23;;2)) =1199

Hier entfällt die Zeilenangabe und es findet nur eine Verschiebung um 2 Spalten nach rechts statt.

Einen Bezug bestimmen

Zuletzt willst Du noch sehen, ob die Funktion auch zur Bestimmung eines nächsten Bezugs geeignet ist.

Vorab, sie ist es.

Ein Bezug verlangt die Adressangaben einer Zelle. In der Ausgangstabelle befindet sich in der zweiten Zeile und der ersten Spalte, also in C22 der Wert 415. C22 soll der neue Bezug sein. Wie kannst Du vorgehen?

=BEREICH.VERSCHIEBEN(B20;2;1) =415

Hiermit hast Du den Wert, aber noch keine Adresse. Mit der Funktion ADRESSE kommst Du weiter. Sie verlangt eine Zeilen- und eine Spaltenangabe.

Du legst die Funktion ZEILE um die Formel.

=ZEILE(BEREICH.VERSCHIEBEN(B20;2;1)) =22

Du legst die Funktion SPALTE um die Formel.

=SPALTE(BEREICH.VERSCHIEBEN(B20;2;1)) =3

Nun kannst Du die Adressfunktion einsetzen. Statt der Zeilen- bzw. Spaltenangabe, verwendest Du die soeben verwendeten Formeln ZEILE bzw. SPALTE.

=ADRESSE(ZEILE(BEREICH.VERSCHIEBEN(B20;2;1));SPALTE(BEREICH.VERSCHIEBEN(B20;2;1))) =$C$22

Das ist richtig, der Wert 415 befindet sich in Zelle C22.

Gehe jetzt noch einen Schritt weiter und verwende diese Adresse als Bezug für eine weitere BEREICH.VERSCHIEBEN-Aktion. Du willst von dort aus noch eine Zeile nach unten und zwei Spalten nach rechts verschieben. Die ADRESSE-Formel muß demnach als Bezug eingesetzt werden.

=BEREICH.VERSCHIEBEN(INDIREKT(ADRESSE(ZEILE(BEREICH.VERSCHIEBEN(B20;2;1)); SPALTE(BEREICH.VERSCHIEBEN(B20;2;1))));1;2) =417

Um die Adressfunktion hast Du zusätzlich noch INDIREKT setzen müssen, weil die Adresse auch erst noch zu berechnen war. Die gesamte Funktion hat jetzt den richtigen Wert (417) ausgegeben.

Kennst Du weitere Möglichkeiten zur Nutzung der Funktion BEREICH.VERSCHIEBEN? Dann schreib doch einen Kommentar!