Eine Zelle entfernt
| A | B | |
|---|---|---|
| 1 | Item | Sales |
| 2 | Pens | 40 |
| 3 | Ink | 90 |
| 4 | Pads | 25 |
Formel: D2: =OFFSET(A1,2,1)
Ergebnis: 90
Zwei Zeilen unterhalb und eine Spalte rechts von A1 liegt B3.
Nachschlagen und Verweisen
BEREICH.VERSCHIEBEN (OFFSET) in Excel
BEREICH.VERSCHIEBEN (OFFSET) in Excel: dynamische Zellbereiche aufbauen, Höhe und Breite steuern und volatile Formeln vermeiden.
OFFSET
Syntax
BEREICH.VERSCHIEBEN(reference, rows, cols, [height], [width])Beispiel: =SUM(OFFSET(B1,COUNT(B:B)-2,0,3,1))
Verfügbar in: Alle Versionen
BEREICH.VERSCHIEBEN gibt einen Bezug zurück, der um eine festgelegte Zahl von Zeilen und Spalten gegenüber einer Ausgangszelle versetzt beginnt und eine bestimmte Höhe und Breite besitzen kann. Die Syntax lautet =OFFSET(reference, rows, cols, [height], [width]).
Wann Sie die Funktion einsetzen
- Die letzten drei Einträge einer wachsenden Liste summieren, indem das Fenster mit steigendem COUNT verschoben wird.
- Einen Wert von einer Position abrufen, die durch die Dropdown-Auswahl eines Benutzers festgelegt ist.
- Für einen gleitenden Mittelwert einen rollierenden Bereich erstellen, der der neuesten Zeile folgt.
Argumente
- reference. Die Ausgangszelle oder der Ausgangsbereich. Verschiebungen werden von der linken oberen Zelle aus gemessen.
- rows. Die Anzahl der zu verschiebenden Zeilen. Positive Werte bewegen nach unten, negative nach oben.
- cols. Die Anzahl der zu verschiebenden Spalten. Positive Werte bewegen nach rechts, negative nach links.
- height. Optionale Höhe des zurückgegebenen Bereichs in Zeilen; standardmäßig entspricht sie der Höhe von reference.
- width. Optionale Breite des zurückgegebenen Bereichs in Spalten; standardmäßig entspricht sie der Breite von reference.
Erklärte Beispiele
Ein Fenster aus drei Zellen
| A | B | |
|---|---|---|
| 1 | Month | Sales |
| 2 | Jan | 10 |
| 3 | Feb | 20 |
| 4 | Mar | 30 |
| 5 | Apr | 40 |
Formel: D2: =SUM(OFFSET(B1,1,0,3,1))
Ergebnis: 60
Der Bereich beginnt eine Zeile unter B1 und ist drei Zeilen hoch, umfasst also B2:B4.
Die neuesten drei Einträge
| A | B | |
|---|---|---|
| 1 | Month | Sales |
| 2 | Jan | 10 |
| 3 | Feb | 20 |
| 4 | Mar | 30 |
| 5 | Apr | 40 |
Formel: D2: =SUM(OFFSET(B1,COUNT(B2:B5)-2,0,3,1))
Ergebnis: 90
COUNT ergibt 4 Einträge; eine Verschiebung um 2 Zeilen nach unten legt das Fenster auf B3:B5.
Jetzt ausprobieren
Ändern Sie eine Formel oder einen Wert in der Beispieltabelle und prüfen Sie das Ergebnis.
Formel in F2: =SUM(OFFSET(D2,1,0,3,1))
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ID | Item | Price | Stock | Result | |
| 2 | 101 | Pen | 2.5 | 120 | 135 | |
| 3 | 102 | Notebook | 4 | 80 | ||
| 4 | 103 | Lamp | 25 | 15 | ||
| 5 | 104 | Mug | 7.5 | 40 | ||
| 6 | 105 | Backpack | 39 | 12 |
Ergebnis in F2: 135
Fehler und Prüfungen
- #REF!. Die Verschiebung führt aus dem Tabellenblatt hinaus, beispielsweise über Zeile 1. Prüfen Sie negative Verschiebungen.
- Langsame Arbeitsmappe. OFFSET ist volatil und wird bei jeder Änderung an beliebiger Stelle neu berechnet. Bevorzugen Sie für große Modelle INDEX.
- #VALUE!. Das Argument reference ist keine Zelle und kein Bereich; damit fehlt ein Ausgangspunkt für die Verschiebung.
- Nur eine Zelle wird angezeigt. Ein mehrzelliges Ergebnis benötigt eine umschließende Funktion wie SUM oder eine ausgabefähige Excel-Version, um den gesamten Bereich darzustellen.
Verwandte Funktionen
Häufige Fragen
- Was kann ich statt BEREICH.VERSCHIEBEN verwenden?
- INDEX kann dynamische Bereiche ohne Volatilität aufbauen: SUM(B2:INDEX(B:B,COUNT(B:B)+1)).
- Verschiebt BEREICH.VERSCHIEBEN die Daten?
- Nein, die Funktion gibt lediglich einen Bezug zurück.
- Wird ein mehrzelliges Ergebnis von BEREICH.VERSCHIEBEN ausgegeben?
- Ja. In Excel 2021, Excel 2024 und Microsoft 365 gibt eine Formel wie =OFFSET(A1,0,0,3,1) drei Zellen aus. Ältere Excel-Versionen zeigen nur den Wert oben links, sofern die Formel nicht als Arrayformel eingegeben wird.