Verwendung der Excel OFFSET-Funktion -

Inhaltsverzeichnis

Beispiele
Ähnliche Videos

Zusammenfassung

Die Excel OFFSET-Funktion gibt einen Verweis auf einen Bereich zurück, der aus fünf Eingaben besteht: (1) einem Startpunkt, (2) einem Zeilenversatz, (3) einem Spaltenversatz, (4) einer Höhe in Zeilen, (5) einer Breite in Säulen. OFFSET ist praktisch in Formeln, die einen Dynamikbereich erfordern.

Zweck

Erstellen Sie einen Referenzversatz von einem bestimmten Startpunkt

Rückgabewert

Eine Zellreferenz.

Syntax

= OFFSET (Referenz, Zeilen, Spalten, (Höhe), (Breite))

Argumente

Referenz - Der Startpunkt, der als Zellreferenz oder Bereich angegeben wird.
Zeilen - Die Anzahl der Zeilen, die unterhalb der Startreferenz versetzt werden sollen.
cols - Die Anzahl der Spalten, die rechts von der Startreferenz versetzt werden sollen.
height - (optional) Die Höhe in Zeilen der zurückgegebenen Referenz.
width - (optional) Die Breite in Spalten der zurückgegebenen Referenz.

Ausführung

Excel 2003

Verwendungshinweise

Die Excel OFFSET-Funktion gibt einen Dynamikbereich zurück, der aus fünf Eingaben besteht: (1) einem Startpunkt, (2) einem Zeilenversatz, (3) einem Spaltenversatz, (4) einer Höhe in Zeilen, (5) einer Breite in Spalten.

Der Startpunkt (das Referenzargument ) kann eine Zelle oder ein Zellbereich sein. Die Zeilen- und Spaltenargumente geben die Anzahl der Zellen an, die vom Startpunkt "versetzt" werden sollen. Die Argumente für Höhe und Breite sind optional und bestimmen die Größe des erstellten Bereichs. Wenn Höhe und Breite weggelassen werden, wird standardmäßig die Höhe und Breite der Referenz verwendet .

Um beispielsweise C5 ab A1 zu referenzieren, ist die Referenz A1, die Zeilen 4 und die Spalten 2:

=OFFSET(A1,4,2) // returns reference to C5

Um C1: C5 von A1 zu referenzieren, ist die Referenz A1, die Zeilen sind 0, die Spalten sind 2, die Höhe ist 5 und die Breite ist 1:

=OFFSET(A1,0,2,5,1) // returns reference to C1:C5

Hinweis: Die Breite kann weggelassen werden, da standardmäßig 1 verwendet wird.

Es ist üblich, dass OFFSET in eine andere Funktion eingeschlossen ist, die einen Bereich erwartet. Zum Beispiel zu SUMME C1: C5, beginnend bei A1:

=SUM(OFFSET(A1,0,2,5,1)) // SUM C1:C5

Der Hauptzweck von OFFSET besteht darin, Formeln die dynamische Anpassung an verfügbare Daten oder Benutzereingaben zu ermöglichen. Mit der OFFSET-Funktion kann ein dynamischer benannter Bereich für Diagramme oder Pivot-Tabellen erstellt werden, um sicherzustellen, dass die Quelldaten immer auf dem neuesten Stand sind.

Hinweis: In der Excel-Dokumentation wird angegeben, dass Höhe und Breite nicht negativ sein dürfen. Negative Werte scheinen jedoch seit Anfang der 90er Jahre einwandfrei zu funktionieren. Die OFFSET-Funktion in Google Sheets lässt keinen negativen Wert für Höhen- oder Breitenargumente zu.

Beispiele

Die folgenden Beispiele zeigen, wie OFFSET so konfiguriert werden kann, dass verschiedene Arten von Bereichen zurückgegeben werden. Diese Bildschirme wurden mit Excel 365 erstellt, sodass OFFSET ein dynamisches Array zurückgibt, wenn das Ergebnis mehr als eine Zelle ist. In älteren Excel-Versionen können Sie mit der Taste F9 die von OFFSET zurückgegebenen Ergebnisse überprüfen.

Beispiel 1

Im folgenden Bildschirm verwenden wir OFFSET, um den dritten Wert (März) in der zweiten Spalte (West) zurückzugeben. Die Formel in H4 lautet:

=OFFSET(B3,3,2) // returns D6

Beispiel 2

Im folgenden Bildschirm verwenden wir OFFSET, um den letzten Wert (Juni) in der dritten Spalte (Nord) zurückzugeben. Die Formel in H4 lautet:

=OFFSET(B3,6,3) // returns E9

Beispiel 3

Im Folgenden verwenden wir OFFSET, um alle Werte in der dritten Spalte (Nord) zurückzugeben. Die Formel in H4 lautet:

=OFFSET(B3,1,3,6) // returns E4:E9

Beispiel 4

Im Folgenden verwenden wir OFFSET, um alle Werte für Mai (fünfte Zeile) zurückzugeben. Die Formel in H4 lautet:

=OFFSET(B3,5,1,1,4) // returns C8:F8

Beispiel 5

Im Folgenden verwenden wir OFFSET, um den Wert für April, Mai und Juni für die Region West zurückzugeben. Die Formel in H4 lautet:

=OFFSET(B3,4,2,3,1) // returns D7:D9

Beispiel 6

Im Folgenden verwenden wir OFFSET, um den Wert für April, Mai und Juni für West und Nord zurückzugeben. Die Formel in H4 lautet:

=OFFSET(B3,4,2,3,2) // returns D7:E9

Anmerkungen

OFFSET gibt nur eine Referenz zurück, es werden keine Zellen verschoben.
Sowohl Zeilen als auch Spalten können als negative Zahlen angegeben werden, um ihre normale Versatzrichtung umzukehren - negative Spalten nach links versetzt und negative Zeilen oben versetzt.
OFFSET ist eine "flüchtige Funktion" - sie wird bei jeder Änderung des Arbeitsblatts neu berechnet. Durch flüchtige Funktionen können größere und komplexere Arbeitsmappen langsam ausgeführt werden.
OFFSET zeigt das #REF an! Fehlerwert, wenn der Versatz außerhalb des Randes des Arbeitsblatts liegt.
Wenn Höhe oder Breite weggelassen wird, werden Höhe und Breite der Referenz verwendet.
OFFSET kann mit jeder anderen Funktion verwendet werden, die eine Referenz erwartet.
In der Excel-Dokumentation heißt es, dass Höhe und Breite nicht negativ sein dürfen, negative Werte jedoch funktionieren.