Excel-OFFSET-Funktion

Die Excel-OFFSET-Funktion gibt einen Bezug auf eine Zelle oder einen Zellbereich zurück, der um eine bestimmte Anzahl von Zeilen und Spalten gegenüber einer Ausgangszelle versetzt ist.

Syntax

=OFFSET ()reference, rows, cols, [height], [width])

Argumente

Rückgabewert

Die OFFSET-Funktion gibt einen Eine Zellreferenz zurück, der gegenüber einem gegebenen Ausgangspunkt versetzt ist.

Hinweise zur Funktion

1. Der Fehlerwert #WERT! wird zurückgegeben, wenn der Bezug auf einen Bereich nicht zusammenhängender Zellen verweist.
2. Der Fehlerwert #BEZUG! wird zurückgegeben, wenn der durch Zeilen- und Spaltenversatz definierte Bezug über den Rand des Arbeitsblatts hinausgeht.

Beispiele

Beispiel 1: Grundlegende Verwendung der OFFSET-Funktion

Geben Sie mit der folgenden Formel einen Bezug auf eine Zelle zurück:
=OFFSET(B2,3,1)

In diesem Fall ist B2 der Ausgangspunkt: Die Zahlen 3 und 1 bedeuten, dass drei Zeilen nach unten und eine Spalte nach rechts von Zelle B2 verschoben wird – und schließlich der Wert aus Zelle C5 zurückgegeben wird. Siehe Screenshot:

Geben Sie mit der folgenden Formel einen Bezug auf einen Zellbereich zurück:
=OFFSET(B2,3,1,2,2)

In diesem Fall erhalten Sie das Ergebnis des 2×2-Bereichs, der sich drei Zeilen unterhalb und eine Spalte rechts von Zelle B2 befindet.

Hinweis: Der Fehler #WERT! tritt auf, wenn Sie nur eine einzelne Zelle auswählen, um die OFFSET-Funktion für die Rückgabe eines Zellbereichs zu verwenden. Wählen Sie stattdessen einen 2×2-Bereich (z. B. vier leere Zellen) aus, geben Sie die Formel ein und drücken Sie Strg+Umschalttaste+Eingabetaste, um die Ergebnisse zu erhalten.

Beispiel 2: Verwenden der OFFSET-Funktion, um einen Wertebereich zu summieren

Wie oben erwähnt, führt die alleinige Verwendung der OFFSET-Funktion – beispielsweise =OFFSET(B2,3,1,2,2) in einer einzelnen Zelle – zu einem #WERT!-Fehler. Kombiniert man hingegen die SUMME- und die OFFSET-Funktion wie im folgenden Screenshot dargestellt, wird direkt die Summe der Werte im Bereich C5:D6 zurückgegeben.

1. Wählen Sie eine leere Zelle aus, kopieren Sie die folgende Formel hinein und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten.
=SUM(OFFSET(B2,3,1,2,2)))

Beispiel 3: Summieren von Spalten basierend auf bestimmten Kriterien

Wie im folgenden Screenshot gezeigt: Wie erhält man den Gesamtumsatz von Mango von Sonntag bis Dienstag? Gehen Sie dazu wie folgt vor.

1. Wählen Sie eine leere Zelle aus, fügen Sie die folgende Formel ein und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten.
=SUM(OFFSET(C2:E2,MATCH(G4,B3:B8,),))

Hinweise:

1. In der obigen Formel sucht MATCH(G4;B3:B8) nach „Mango“ und gibt deren Position im Bereich B3:B8 zurück. Da sich „Mango“ fünf Zeilen unterhalb von Zelle B2 befindet, wird die Zahl 5 zurückgegeben;
2. Da das Ergebnis der VERGLEICH-Funktion 5 lautet und das letzte Argument hier 0 repräsentiert, lautet die OFFSET-Funktion nun OFFSET(C2:E2;5;0). Das bedeutet, dass vom Bereich C2:E2 ausgehend fünf Zeilen nach unten und null Spalten nach rechts verschoben wird, um den Bezug auf den Bereich C7:E7 zu erhalten;
3. Anschließend lautet die SUMME-Funktion SUM(C7:E7) und gibt schließlich die Summe der Werte im Bereich C7:E7 zurück.

Weitere Beispiele

Wie summiert man alle n Zeilen nach unten in Excel?

Wie erstellt man einen dynamischen benannten Bereich in Excel?

Wie gibt man in Excel den Zellenwert jeder fünften oder n-ten Zeile zurück?