Excel OFFSET Funktion

Das Excel OFFSET-Funktion Gibt einen Verweis auf eine Zelle oder einen Zellbereich zurück, der von einer bestimmten Zelle um eine bestimmte Anzahl von Zeilen und Spalten versetzt ist.

Syntax

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

Argumente

Rückgabewert

Die OFFSET-Funktion gibt einen Zellreferenzoffset von einem bestimmten Startpunkt zurück.

Funktionshinweise

1. #WERT! Der Fehlerwert wird zurückgegeben, wenn sich die Referenz auf einen Bereich diskontinuierlicher Zellen bezieht.
2. #REF! Der Fehlerwert wird zurückgegeben, wenn die Zeilen und Spalten den Verweis über den Rand des Arbeitsblatts verschieben.

Beispiele

Beispiel 1: Grundlegende Verwendung für die OFFSET-Funktion

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

In diesem Fall ist B2 der Startpunkt. Nummer 3 und 1 bedeuten, dass 3 Zeilen nach unten und 1 Spalte nach rechts aus Zelle B2 verschoben werden und der Wert in C5 schließlich zurückgegeben wird. Siehe Screenshot:

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

In diesem Fall erhalten Sie die Ergebnisse eines Bereichs von 2 x 2, der 3 Zeilen darunter und 1 Spalte rechts von Zelle B2 liegt.

Hinweis: #WERT! Ein Fehler tritt auf, wenn Sie nur eine Zelle auswählen, um die OFFSET-Funktion zum Zurückgeben eines Zellbereichs anzuwenden. Sie müssen einen 2 x 2-Bereich auswählen (sagt 4 leere Zellen), die Formel eingeben und die Taste drücken Ctrl + Verschiebung + Enter Schlüssel, um die Ergebnisse zu erhalten.

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

Wie oben erwähnt, wenn Sie versuchen, die OFFSET-Funktion wie z =OFFSET(B2,3,1,2,2) Allein in einer einzelnen Zelle wird ein #WERT zurückgegeben! Error. Wenn Sie jedoch die SUM- und OFFSET-Funktion wie im folgenden Screenshot kombinieren, wird die Summe der Werte im Bereich C5: D6 direkt zurückgegeben.

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

Beispiel 3: Summenspalten basierend auf bestimmten Kriterien

Wie der folgende Screenshot zeigt, wie kann man den Gesamtumsatz von Mango von Sun bis Di erzielen? Bitte versuchen Sie es wie unten.

1. Wählen Sie eine leere Zelle aus, kopieren Sie die folgende Formel hinein und drücken Sie die Taste Enter Schlüssel, um das Ergebnis zu erhalten.
=SUM(OFFSET(C2:E2,MATCH(G4,B3:B8,),))

Notizen:

1. In der obigen Formel MATCH(G4,B3:B8) sucht Mango und gibt seine Position in Reichweite zurück B3: B8. Mal sehen, Mango findet 5 Zeilen unter Zelle B2, also gibt es die Nummer 5 zurück;
2. Da das MATCH-Ergebnis 5 ist und das letzte Komma hier 0 darstellt, wird die OFFSET-Funktion jetzt als angezeigt OFFSET(C2:E2,5,0), was bedeutet, dass der Versatz ab Bereich C2: E2 beginnt, 5 Zeilen nach unten und 0 Spalte nach rechts verschieben, um den Verweis auf Bereich C7: E7 zu erhalten;
3. Dann wird die SUMME-Funktion als angezeigt SUM(C7:E7)und geben schließlich die Summe der Werte im Bereich C7: E7 zurück.

Mehr Beispiele

Wie summiere ich alle n Zeilen in Excel?

Wie erstelle ich einen dynamischen Namensbereich in Excel?

Wie kann der Zellenwert alle fünf oder n-ten Zeilen in Excel zurückgegeben werden?