=BEREICH.VERSCHIEBEN(A1;3;2) liefert die Zelle 3 Zeilen unter und 2 Spalten neben A1, also C4. Gibst du zusätzlich Höhe und Breite an, liefert BEREICH.VERSCHIEBEN (englisch OFFSET) einen ganzen Bereich, und dafür wird die Funktion meist verwendet: Summen und Durchschnitte über einen Bereich, der wandert oder wächst. Die Tabelle zeigt die englische Schreibweise, =OFFSET(A1,E2,F2); du kannst die Formeln dort aber auch deutsch eingeben, mit Semikolons.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Rows | Cols | Result | |
| 2 | Apple | Fruit | $1.20 | 3 | 2 | $0.80 | |
| 3 | Pear | Fruit | $1.50 | ||||
| 4 | Carrot | Vegetable | $0.80 | ||||
| 5 | Bread | Bakery | $2.40 | ||||
| 6 | Milk | Dairy | $1.10 |
=BEREICH.VERSCHIEBEN(A1;E2;F2)3 Zeilen nach unten und 2 nach rechts von A1 landet auf C4, dem Preis von Carrot, $0.80. Setz Cols auf 0 für den Namen Carrot, oder Rows auf 5 für die Zeile von Milk. Zeilen und Spalten können negativ sein, um nach oben oder zurück zu gehen, und ein Schritt über den oberen oder seitlichen Rand des Blatts hinaus ergibt #BEZUG! (englisch #REF!).
Syntax von BEREICH.VERSCHIEBEN
=OFFSET(reference, rows, cols, [height], [width])
reference(Bezug): die Startzelle (oder der Startbereich).rows,cols(Zeilen, Spalten): wie weit verschoben wird. 0 heißt stehen bleiben.height,width(Höhe, Breite): die Größe des zurückgegebenen Bereichs, gezählt ab der Zielzelle. Fehlen sie, gilt die Größe vonreference.
Allein in einer Zelle läuft ein BEREICH.VERSCHIEBEN, das mehrere Zellen liefert, in Excel 365 über; ältere Versionen zeigen meist #WERT! (englisch #VALUE!). Die Tabellen hier zeigen die englischen Fehlernamen. In SUMME, MITTELWERT, ANZAHL oder MAX (englisch SUM, AVERAGE, COUNT, MAX) funktioniert es als Bereich.
Die letzten N Zeilen summieren
Die klassische Aufgabe für BEREICH.VERSCHIEBEN: eine Summe, die immer die neuesten Zeilen abdeckt, egal wie viele dazugekommen sind. ANZAHL findet heraus, wie viele Werte es gibt, BEREICH.VERSCHIEBEN geht nach unten zum ersten der letzten N, und die Höhe nimmt N Zeilen.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | Last N | Total | ||
| 2 | Jan | 4,200 | 3 | 14,900 | ||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 | ||||
| 8 | Jul | 5,000 |
=SUMME(BEREICH.VERSCHIEBEN(B1;ANZAHL(B2:B13)-E2+1;0;E2;1))Es gibt 7 Werte, also beginnt BEREICH.VERSCHIEBEN 7-3+1, 5 Zeilen unter B1, bei B6, und nimmt 3 Zeilen: May bis Jul, 14,900. In deutschem Excel lautet die Formel =SUMME(BEREICH.VERSCHIEBEN(B1;ANZAHL(B2:B13)-E2+1;0;E2;1)). Tippe 4900 in B9 (August), und die Summe wandert zu Jun, Jul und Aug, weil ANZAHL jetzt 8 findet. Der Bereich B2:B13 lässt Platz für den Rest des Jahres. Die Spalte darf keine leeren Zellen dazwischen haben, sonst zählt ANZAHL zu wenig, und das Fenster landet an der falschen Stelle.
Ein gleitender Durchschnitt
Nach unten ausgefüllt gibt BEREICH.VERSCHIEBEN mit einer negativen Zeilenverschiebung jeder Zeile ein Fenster über die Zeilen darüber: hier den Durchschnitt des aktuellen Monats und der beiden davor.
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | 3-month average |
| 2 | Jan | 4,200 | |
| 3 | Feb | 3,900 | |
| 4 | Mar | 4,800 | 4,300 |
| 5 | Apr | 5,100 | 4,600 |
| 6 | May | 4,600 | 4,833 |
| 7 | Jun | 5,300 | 5,000 |
| 8 | Jul | 5,000 | 4,967 |
=MITTELWERT(BEREICH.VERSCHIEBEN(B4;-2;0;3;1))C4 bildet den Durchschnitt von B2:B4 (Jan bis Mar), 4,300. Jede Zeile darunter verschiebt das Fenster um eins nach unten. Ändere die 3 in 6 und die -2 in -5 für einen Sechsmonatsdurchschnitt (und beginne die Formel dann in Zeile 7). Genau dieser Fall braucht gar kein BEREICH.VERSCHIEBEN: =MITTELWERT(B2:B4), ab C4 nach unten ausgefüllt, macht dasselbe, weil relative Bezüge ohnehin wandern. BEREICH.VERSCHIEBEN lohnt sich, wenn die Fenstergröße aus einer Zelle kommt.
Warum INDEX oft die bessere Wahl ist
BEREICH.VERSCHIEBEN ist volatil: Excel berechnet jedes BEREICH.VERSCHIEBEN nach jeder Bearbeitung irgendwo in der Arbeitsmappe neu, weil es nicht im Voraus wissen kann, auf welche Zellen die Funktion zeigen wird. Ein Blatt mit Tausenden davon wird langsam. INDEX liefert auch einen Bezug, und ein Bereich, geschrieben als Start:INDEX(...), wächst genauso, ohne volatil zu sein:
=SUM(OFFSET(B2, 0, 0, E2, 1)) first E2 rows, volatile
=SUM(B2:INDEX(B2:B13, E2)) same rows, not volatile
In deutschem Excel: =SUMME(BEREICH.VERSCHIEBEN(B2;0;0;E2;1)) und =SUMME(B2:INDEX(B2:B13;E2)).
Beide lesen die ersten E2 Zeilen der Spalte. BEREICH.VERSCHIEBEN ist außerdem schwerer zu prüfen: "Spur zum Vorgänger" und die farbigen Umrandungen, die Excel beim Bearbeiten der Formel zeichnet, zeigen die Startzelle und die Argumente, nicht den Bereich, den BEREICH.VERSCHIEBEN am Ende liefert. Nimm BEREICH.VERSCHIEBEN für ein schnelles Modell oder einen Diagrammbereich; in großen Arbeitsmappen ist INDEX besser. INDEX zeigt mehr zum Zurückgeben von Bereichen, und INDIREKT (englisch INDIRECT) ist die andere volatile Bezugsfunktion.
Übung: Summe der ersten N Monate
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | First N | Total | ||
| 2 | Jan | 4,200 | 4 | |||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 | ||||
| 8 | Jul | 5,000 |
Du bist dran: Summiere in F2 mit BEREICH.VERSCHIEBEN in SUMME die ersten N Monate, wobei N in E2 steht.
Häufig gestellte Fragen
Was macht BEREICH.VERSCHIEBEN in Excel?
Die Funktion liefert einen Bezug, der eine bestimmte Zahl von Zeilen und Spalten von einer Startzelle entfernt liegt, auf Wunsch in anderer Größe. =BEREICH.VERSCHIEBEN(A1;3;2) ist die Zelle 3 Zeilen unter und 2 Spalten rechts von A1, also C4.
Wie summiere ich die letzten N Zeilen in Excel?
Starte bei der Überschrift und geh nach unten zum ersten der letzten N Werte: =SUMME(BEREICH.VERSCHIEBEN(B1;ANZAHL(B2:B100)-N+1;0;N;1)). ANZAHL findet heraus, wie viele Werte es gibt, und die Höhe N nimmt so viele Zeilen. Das funktioniert nur, wenn die Spalte keine Lücken hat.
Warum ist BEREICH.VERSCHIEBEN volatil?
Excel berechnet jedes BEREICH.VERSCHIEBEN nach jeder Änderung in der Arbeitsmappe neu, weil die Zellen, auf die es zeigt, erst nach der Berechnung bekannt sind. In großen Arbeitsmappen bremst das. Ein mit INDEX gebauter Bereich wie B2:INDEX(B2:B100;N) erledigt dieselbe Aufgabe, ohne volatil zu sein.
Welche Argumente hat BEREICH.VERSCHIEBEN?
BEREICH.VERSCHIEBEN(Bezug; Zeilen; Spalten; [Höhe]; [Breite]): die Startzelle, wie viele Zeilen nach unten (negativ heißt nach oben), wie viele Spalten nach rechts (negativ heißt zurück) und optional die Größe des zurückgegebenen Bereichs.
