Excel OFFSET-Funktion (Beispiel + Video)
Wann Sie die Excel OFFSET-Funktion verwenden sollten
Die Excel OFFSET-Funktion kann verwendet werden, wenn Sie eine Referenz erhalten möchten, die die angegebene Anzahl von Zeilen und Spalten vom Ausgangspunkt versetzt.
Was es zurückgibt
Sie gibt die Referenz zurück, auf die die OFFSET-Funktion zeigt.
Syntax
=OFFSET(Referenz, Zeilen, Spalten, [Höhe], [Breite])
Eingabeargumente
- Hinweis - Die Referenz, von der Sie einen Offset durchführen möchten. Dies kann eine Zellreferenz oder ein Bereich benachbarter Zellen sein.
- Reihen - die Anzahl der zu versetzenden Zeilen. Wenn Sie eine positive Zahl verwenden, wird sie zu den darunter liegenden Zeilen verschoben, und wenn eine negative Zahl verwendet wird, wird sie zu den darüber liegenden Zeilen verschoben.
- Spalten - die Anzahl der zu versetzenden Spalten. Wenn Sie eine positive Zahl verwenden, wird sie zu den Spalten rechts verschoben, und wenn eine negative Zahl verwendet wird, wird sie zu den Spalten links verschoben.
- [Höhe] - Dies ist eine Zahl, die die Anzahl der Zeilen in der zurückgegebenen Referenz darstellt.
- [Breite] - Dies ist eine Zahl, die die Anzahl der Spalten in der zurückgegebenen Referenz darstellt.
Die Grundlagen der Excel OFFSET-Funktion verstehen
Die Excel OFFSET-Funktion ist möglicherweise eine der verwirrendsten Funktionen in Excel.
Nehmen wir ein einfaches Beispiel für ein Schachspiel. Es gibt eine Schachfigur namens Rook (auch bekannt als Turm, Marquis oder Rektor).
[Bild mit freundlicher Genehmigung: Wunderbare Wikipedia]
Nun hat ein Turm wie alle anderen Schachfiguren einen festen Weg, auf dem er sich auf dem Brett bewegen kann. Es kann geradeaus gehen (nach rechts/links oder auf dem Brett nach oben/unten), aber nicht diagonal.
Angenommen, wir haben ein Schachbrett in Excel (wie unten gezeigt), in einem bestimmten Feld (D5 in diesem Fall) kann der Turm nur in die vier hervorgehobenen Richtungen gehen.
Wenn ich Sie nun bitte, den Turm von seiner aktuellen Position zu der gelb markierten zu bringen, was werden Sie dem Turm sagen?
Sie werden ihn bitten, zwei Schritte nach unten und zwei Schritte nach rechts zu machen … nicht wahr?
Und das ist eine enge Annäherung an die Funktionsweise der OFFSET-Funktion.
Sehen wir uns nun an, was dies in Excel bedeutet. Ich möchte mit der Zelle D5 beginnen (wo der Turm ist) und dann zwei Zeilen nach unten und zwei Spalten nach rechts gehen und den Wert aus der Zelle dort abrufen.
Die Formel wäre:
=OFFSET(von wo anfangen, wie viele Zeilen nach unten, wie viele Spalten nach rechts)
Wie Sie sehen, lautet die Formel im obigen Beispiel in Zelle J1 =OFFSET(D5,2,2).
Es begann bei D5, ging dann zwei Reihen nach unten und zwei Spalten nach rechts und erreichte die Zelle F7. Es gab dann den Wert in Zelle F7 zurück.
Im obigen Beispiel haben wir uns die OFFSET-Funktion mit 3 Argumenten angesehen. Es gibt jedoch zwei weitere optionale Argumente, die verwendet werden können.
Schauen wir uns hier ein einfaches Beispiel an:
Angenommen, Sie möchten den Verweis auf Zelle A1 (in Gelb) verwenden und in einer Formel auf den gesamten blau hervorgehobenen Bereich (C2:E4) verweisen.
Wie würdest du das mit einer Tastatur machen? Sie würden zuerst zu Zelle C2 gehen und dann alle Zellen in C2:E4 auswählen.
Sehen wir uns nun an, wie Sie dies mit der OFFSET-Formel tun:
=OFFSET(A1,1,2,3,3)
Wenn Sie diese Formel in einer Zelle verwenden, wird ein #VALUE zurückgegeben! Fehler, aber wenn Sie in den Bearbeitungsmodus gelangen und die Formel auswählen und F9 drücken, werden Sie sehen, dass alle blau hervorgehobenen Werte zurückgegeben werden.
Sehen wir uns nun an, wie diese Formel funktioniert:
=OFFSET(A1,1,2,3,3)
- Das erste Argument ist die Zelle, in der es beginnen soll.
- Das zweite Argument ist 1, was Excel anweist, eine Referenz zurückzugeben, die um 1 Zeile versetzt wurde.
- Das dritte Argument ist 2, was Excel anweist, eine Referenz zurückzugeben, die um 2 Spalten versetzt wurde.
- Das vierte Argument ist 3. Dies gibt an, dass die Referenz 3 Zeilen umfassen soll. Dies wird als Höhenargument bezeichnet.
- Das fünfte Argument ist 3. Dies gibt an, dass die Referenz 3 Spalten umfassen soll. Dies wird als width-Argument bezeichnet.
Nachdem Sie nun den Verweis auf einen Zellbereich (C2:E4) haben, können Sie ihn in anderen Funktionen verwenden (z. B. SUM, COUNT, MAX, AVERAGE).
Hoffentlich haben Sie ein gutes Grundverständnis für die Verwendung der Excel-OFFSET-Funktion. Schauen wir uns nun einige praktische Beispiele für die Verwendung der OFFSET-Funktion an.
Excel OFFSET-Funktion - Beispiele
Hier sind zwei Beispiele für die Verwendung der Excel OFFSET-Funktion.
Beispiel 1 - Finden der letzten gefüllten Zelle in der Spalte
Angenommen, Sie haben einige Daten in einer Spalte wie unten gezeigt:
Um die letzte Zelle in der Spalte zu finden, verwenden Sie die folgende Formel:
=OFFSET(A1,ZÄHL(A:A)-1,0)
Diese Formel geht davon aus, dass außer den angezeigten keine Werte vorhanden sind und diese Zellen keine leere Zelle enthalten. Es funktioniert, indem es die Gesamtzahl der gefüllten Zellen zählt und die Zelle A1 entsprechend versetzt.
In diesem Fall gibt es beispielsweise 8 Werte, also gibt COUNT(A:A) 8 zurück. Wir versetzen die Zelle A1 um 7, um den letzten Wert zu erhalten.
Beispiel 2 – Erstellen eines dynamischen Dropdown-Menüs
Sie können das Konzept in Beispiel 1 erweitern, um dynamische benannte Bereiche zu erstellen. Diese können nützlich sein, wenn Sie die benannten Bereiche in Formeln oder beim Erstellen von Dropdowns verwenden und wahrscheinlich Daten aus der Referenz hinzufügen/löschen, die den benannten Bereich erstellt.
Hier ist ein Beispiel:
Beachten Sie, dass das Dropdown-Menü automatisch angepasst wird, wenn das Jahr hinzugefügt oder entfernt wird.
Dies geschieht, da die Formel, die zum Erstellen des Dropdown-Menüs verwendet wird, dynamisch ist und jedes Hinzufügen oder Löschen identifiziert und den Bereich entsprechend anpasst.
So geht's:
- Wählen Sie die Zelle aus, in die Sie das Dropdown-Menü einfügen möchten.
- Gehen Sie zu Daten -> Datentools -> Datenvalidierung.
- Wählen Sie im Dialogfeld Datenvalidierung auf der Registerkarte Einstellungen die Option Liste aus der Dropdown-Liste aus.
- Geben Sie in der Quelle die folgende Formel ein: =OFFSET(A1,0,0,COUNT(A:A),1)
- OK klicken.
Mal sehen, wie diese Formel funktioniert:
- Die ersten drei Argumente der OFFSET-Funktion sind A1, 0 und 0. Dies bedeutet im Wesentlichen, dass dieselbe Referenzzelle (also A1) zurückgegeben würde.
- Das vierte Argument ist für die Höhe und hier gibt die COUNT-Funktion die Gesamtzahl der Elemente in der Liste zurück. Es wird davon ausgegangen, dass die Liste keine Leerzeichen enthält.
- Das fünfte Argument ist 1, was angibt, dass die Spaltenbreite eins sein sollte.
Zusätzliche Bemerkungen:
- OFFSET ist eine flüchtige Funktion (mit Vorsicht verwenden).
- Es wird jedes Mal neu berechnet, wenn die Excel-Arbeitsmappe geöffnet ist oder wenn eine Berechnung im Arbeitsblatt ausgelöst wird. Dies könnte die Verarbeitungszeit verlängern und Ihre Arbeitsmappe verlangsamen.
- Wenn der Wert für Höhe oder Breite weggelassen wird, wird er als Referenzwert verwendet.
- Ob Reihen und cols negative Zahlen sind, wird die Offset-Richtung umgekehrt.
Excel OFFSET-Funktionsalternativen
Aufgrund einiger Einschränkungen der Excel OFFSET-Funktion möchten Sie viele Alternativen dazu in Betracht ziehen:
- INDEX-Funktion: Die INDEX-Funktion kann auch verwendet werden, um einen Zellbezug zurückzugeben. Klicken Sie hier, um ein Beispiel zum Erstellen eines dynamischen benannten Bereichs mit der INDEX-Funktion anzuzeigen.
- Excel-Tabelle: Wenn Sie in der Excel-Tabelle strukturierte Verweise verwenden, müssen Sie sich keine Sorgen machen, dass neue Daten hinzugefügt werden und die Formeln angepasst werden müssen.
Excel OFFSET-Funktion - Video-Tutorial
- Excel SVERWEIS-Funktion.
- Excel HLOOKUP-Funktion.
- Excel-INDEX-Funktion.
- Excel INDIRECT-Funktion.
- Excel MATCH-Funktion.
