Laden Sie die Beispielarbeitsmappe herunter
Dieses Tutorial zeigt, wie Sie das Excel OFFSET-Funktion in Excel, um einen Referenzversatz aus einer Anfangszelle zu erstellen.
Übersicht der OFFSET-Funktion
Die OFFSET-Funktion beginnt mit einem definierten Zellbezug und gibt einen Zellbezug mit einer bestimmten Anzahl von Zeilen und Spalten zurück, die vom ursprünglichen Bezug versetzt sind. Referenzen können eine Zelle oder ein Zellbereich sein. Mit Offset können Sie auch die Größe der Referenz um eine bestimmte Anzahl von Zeilen/Spalten ändern.
(Beachten Sie, wie die Formeleingaben angezeigt werden)
IFERROR-Funktionssyntax und Eingaben:
| 1 | =OFFSET(Referenz,Zeilen,Spalten,Höhe,Breite) |
Hinweis - Der anfängliche Zellbezug, von dem Sie einen Offset durchführen möchten.
Reihen - Die Anzahl der zu versetzenden Zeilen.
cols - Die Anzahl der zu versetzenden Spalten.
Höhe - OPTIONAL: Passen Sie die Anzahl der Zeilen in der Referenz an.
Breite - OPTIONAL: Passen Sie die Anzahl der Spalten in der Referenz an.
Was ist die OFFSET-Funktion?
Die OFFSET-Funktion ist eine der leistungsfähigeren Tabellenkalkulationsfunktionen, da sie bei der Erstellung sehr vielseitig sein kann. Es gibt dem Benutzer die Möglichkeit, eine Zelle oder einen Bereich in einer Vielzahl von Positionen und Größen zu definieren.
ACHTUNG: Die OFFSET-Funktion ist eine der flüchtigen Funktionen. Wenn Sie in Ihrer Tabellenkalkulation arbeiten, berechnet der Computer meistens nur dann eine Formel neu, wenn die Eingaben ihre Werte geändert haben. Eine flüchtige Funktion berechnet jedoch neu jeden wenn Sie eine beliebige Zelle ändern. Es ist Vorsicht geboten, um sicherzustellen, dass Sie keine lange Neuberechnungszeit aufgrund übermäßiger Verwendung der flüchtigen Funktion verursachen oder viele Zellen vom Ergebnis einer flüchtigen Funktion abhängen.
Einfache Reihenbeispiele
Bei jeder Verwendung der OFFSET-Funktion müssen Sie einen Startpunkt oder Anker angeben. Schauen wir uns diese Tabelle an, um dies zu verstehen:
Wir verwenden "Bob" in Zelle B3 als unseren Ankerpunkt. Wenn wir den Wert direkt darunter erfassen wollten (Charlie), würden wir sagen, dass wir die Zeile um 1 verschieben möchten. Unsere Formel würde so aussehen
| 1 | =OFFSET(B3, 1) |
Wenn wir nach oben schalten wollten, wäre das eine negative Verschiebung. Sie können sich dies so vorstellen, dass die Zeilennummer abnimmt, also müssen wir subtrahieren. Um den obigen Wert (Adam) zu erhalten, würden wir also schreiben
| 1 | =OFFSET(B2, -1) |
Einfache Spaltenbeispiele
Wir setzen die Idee aus dem vorherigen Beispiel fort und fügen unserer Tabelle eine weitere Spalte hinzu.
Wenn wir den Lehrer für Bob schnappen wollten, könnten wir die Formel verwenden
| 1 | =OFFSET(B2, 0, 1) |
In diesem Fall haben wir gesagt, dass wir null Zeilen versetzen möchten (auch bekannt als in derselben Zeile bleiben), aber wir möchten 1 Spalte versetzen. Bei Spalten bedeutet eine positive Zahl, dass sie nach rechts versetzt wird, und eine negative Zahl bedeutet, dass sie nach links versetzt wird.
OFFSET und MATCH
Angenommen, Sie haben mehrere Datenspalten und möchten dem Benutzer die Möglichkeit geben, auszuwählen, aus welcher Spalte die Ergebnisse abgerufen werden sollen. Sie können die INDEX-Funktion oder OFFSET verwenden. Da MATCH die relative Position eines Werts zurückgibt, müssen wir sicherstellen, dass sich der Ankerpunkt links von unserem ersten möglichen Wert befindet. Betrachten Sie das folgende Layout:
In B2 schreiben wir diese Formel:
| 1 | =OFFSET(B2, 0, MATCH(A2, $C$1:$F$1, 0)) |
Das MATCH wird im Bereich C1:F1 nach „Feb“ suchen und in der 2 . findennd Zelle. Der OFFSET verschiebt dann eine Spalte nach rechts von B2 und nimmt den gewünschten Wert von 9 auf. Beachten Sie, dass OFFSET kein Problem damit hat, dieselbe Zelle, die die Formel enthält, als Ankerpunkt zu verwenden.
HINWEIS: Diese Technik kann als Ersatz für SVERWEIS oder HVERWEIS verwendet werden, wenn Sie einen Wert links/oberhalb Ihres Nachschlagebereichs zurückgeben möchten. Dies liegt daran, dass OFFSET negative Offsets ausführen kann.
OFFSET, um eine Reichweite zu erhalten
Sie können die 4 . verwendenNS und 5NS -Argumente in der OFFSET-Funktion, um einen Bereich und nicht nur eine einzelne Zelle zurückzugeben. Angenommen, Sie möchten 3 Spalten in dieser Tabelle summieren.
| 1 | =MITTEL(OFFSET(A1,MATCH(F2,A2:A5,0),1,1,3)) |
In F2 haben wir den Namen eines Schülers ausgewählt, für den wir seine durchschnittlichen Testergebnisse abrufen möchten. Dazu verwenden wir die Formel
| 1 | =MITTEL(OFFSET(A1,MATCH(F2,A2:A5,0),1,1,3)) |
Der MATCH durchsucht Spalte A nach unserem Namen und gibt die relative Position zurück, die in unserem Beispiel 3 ist. Mal sehen, wie das bewertet wird. Zuerst wird der OFFSET gehen Nieder 3 Zeilen von A1 und 1 Spalte bis zum rechts von A1. Dies bringt uns in Zelle B3.
| 1 | =MITTEL(OFFSET(A1, 3, 1, 1, 3)) |
Als nächstes ändern wir die Größe des Bereichs. Der neue Bereich hat B3 als obere linke Zelle. Es wird 1 Zeile hoch und 3 Spalten hoch sein, was uns den Bereich B4:D4 gibt.
| 1 | =MITTEL(OFFSET(A1,3, 1, 1, 3)) |
Beachten Sie, dass Sie zwar zulässigerweise negative Werte in die Offset-Argumente einfügen können, Sie jedoch nur nicht negative Werte in den Größenargumenten verwenden können.
Am Ende sieht unsere MITTELWERT-Funktion:
| 1 | =MITTEL(B4:D4) |
Somit erhalten wir unsere Lösung von 86.67
OFFSET mit dynamischer SUM
Da OFFSET verwendet wird, um eine Referenz zu finden, anstatt direkt auf die Zelle zu verweisen, ist es am hilfreichsten, wenn Sie mit Daten arbeiten, denen Zeilen hinzugefügt oder gelöscht wurden. Betrachten Sie die folgende Tabelle mit einem Total am Ende
| 1 | =SUMME(B2:B4) |
Wenn wir hier eine einfache SUM-Formel von „=SUM(B2:B4)“ verwendet und dann eine neue Zeile eingefügt hätten, um einen Datensatz für Bill hinzuzufügen, hätten wir die falsche Antwort
Überlegen wir uns stattdessen, wie wir dies aus der Sicht von Total lösen können. Wir wollen wirklich alles von Zelle B2 bis zur Zelle packen knapp über unserer Summe. Wir können dies in einer Formel schreiben, indem wir einen Zeilenversatz von -1 ausführen. Daher verwenden wir dies als Formel für unsere Summe in Zelle B5:
| 1 | =SUMME(B2:OFFSET(B5,-1,0)) |
Diese Formel macht das, was wir gerade beschrieben haben: Beginnen Sie bei B2 und gehen Sie zu 1 Zelle über unserer Gesamtzelle. Sie können sehen, wie nach dem Hinzufügen von Bills Daten unsere Gesamtsumme korrekt aktualisiert wird.
OFFSET, um die letzten N Elemente zu erhalten
Nehmen wir an, Sie erfassen monatliche Verkäufe, möchten aber die letzten 3 Monate betrachten. Anstatt Ihre Formeln manuell aktualisieren zu müssen, um sie beim Hinzufügen neuer Daten anzupassen, können Sie die OFFSET-Funktion mit COUNT verwenden.
Wir haben bereits gezeigt, wie Sie mit OFFSET eine Reihe von Zellen erfassen können. Um zu bestimmen, wie viele Zellen wir verschieben müssen, verwenden wir COUNT, um herauszufinden, wie viele Zahlen in Spalte B. Schauen wir uns unsere Beispieltabelle an.
| 1 | =SUMME(OFFSET($B$1,COUNT(B:B)-$E$1+1,0,$E$1,1)) |
Wenn wir bei B1 beginnen und 4 Zeilen versetzen (die Anzahl der Zahlen in Spalte B), landen wir am unteren Ende unseres Bereichs, B5. Da OFFSET jedoch nicht mit einem negativen Wert skaliert werden kann, müssen wir einige Anpassungen vornehmen, damit wir in B3 landen. Die allgemeine Gleichung dafür wird lauten
| 1 | ANZAHL(… ) - N + 1 |
Wir nehmen die Anzahl der gesamten Spalte, subtrahieren so viele, wie wir zurückgeben möchten (da wir die Größe ändern, um sie zu erfassen), und fügen dann 1 hinzu (da wir unseren Offset im Wesentlichen an Position Null beginnen).
Hier sehen Sie, dass wir einen Bereich eingerichtet haben, um die Summe, den Durchschnitt und das Maximum der letzten N Monate zu erhalten. In E1 haben wir den Wert 3 eingegeben. In E2 lautet unsere Formel
| 1 | =SUMME(OFFSET($B$1,COUNT(B:B)-$E$1+1,0,$E$1,1)) |
Der hervorgehobene Abschnitt ist unsere allgemeine Gleichung, die wir gerade besprochen haben. Wir müssen keine Spalten versetzen. Wir ändern dann die Größe des Bereichs auf eine Höhe von 3 Zellen (bestimmt durch den Wert in E1) und eine Breite von 1 Spalte. Unsere SUM nimmt dann diese Spanne und gibt uns das Ergebnis von 1.850 $. Wir haben auch gezeigt, dass Sie den Durchschnitt von max desselben Bereichs berechnen können, indem Sie einfach die äußere Funktion von SUM auf eine beliebige Situation umschalten.
OFFSET dynamische Validierungslisten
Mit der im letzten Beispiel gezeigten Technik können wir auch benannte Bereiche erstellen, die in der Datenvalidierung oder in Diagrammen verwendet werden könnten. Dies kann hilfreich sein, wenn Sie eine Tabelle einrichten möchten, aber erwarten, dass sich die Größe unserer Listen/Daten ändert. Nehmen wir an, unser Geschäft beginnt mit dem Verkauf von Obst und wir haben derzeit 3 Möglichkeiten.
Um ein Dropdown-Menü für die Datenvalidierung zu erstellen, das wir an anderer Stelle verwenden können, definieren wir den benannten Bereich MyFruit als
| 1 | =$A$2:OFFSET($A$1, ANZAHLA($A:$A)-1, 0) |
Anstelle von COUNT verwenden wir COUNTA, da wir es mit Textwerten zu tun haben. Aus diesem Grund wird unser COUNTA jedoch um eins höher sein, da es die Header-Zelle in A1 zählt und einen Wert von 4 ergibt. Wenn wir jedoch um 4 Zeilen versetzen, würden wir in Zelle A5 enden, die leer ist. Um dies auszugleichen, subtrahieren wir die 1.
Nachdem wir nun unser Named Range-Setup eingerichtet haben, können wir in Zelle C4 eine Datenvalidierung einrichten, indem wir einen Listentyp mit Quelle verwenden:
| 1 | =Meine Frucht |
Beachten Sie, dass das Dropdown-Menü nur unsere drei aktuellen Artikel anzeigt. Wenn wir unserer Liste dann weitere Elemente hinzufügen und zum Dropdown zurückkehren, zeigt die Liste alle neuen Elemente an, ohne dass wir eine der Formeln ändern müssen.
Vorsichtsmaßnahmen bei der Verwendung von OFFSET
Wie am Anfang dieses Artikels erwähnt, ist OFFSET eine flüchtige Funktion. Sie werden dies nicht bemerken, wenn Sie es in nur wenigen Zellen verwenden, aber wenn Sie beginnen, es in Hunderte von Berechnungen einzubeziehen, werden Sie schnell feststellen, dass Ihr Computer jedes Mal, wenn Sie Änderungen vornehmen, merklich viel Zeit mit der Neuberechnung verbringt .
Da OFFSET die Zellen, die es betrachtet, nicht direkt benennt, ist es für andere Benutzer außerdem schwieriger, später vorbeizukommen und Ihre Formeln bei Bedarf zu ändern.
Stattdessen wäre es ratsam, Tabellen (eingeführt in Office 2007) zu verwenden, die strukturelle Referenzen zulassen. Diese halfen den Benutzern, eine einzige Referenz anzugeben, deren Größe automatisch angepasst wurde, wenn neue Daten hinzugefügt oder gelöscht wurden.
Die andere Möglichkeit, anstelle von OFFSET zu verwenden, ist die leistungsstarke INDEX-Funktion. Mit INDEX können Sie alle Dynamikbereiche erstellen, die wir in diesem Artikel gesehen haben, ohne dass es sich um eine volatile Funktion handelt.
Zusätzliche Bemerkungen
Verwenden Sie die OFFSET-Funktion, um einen Zellenwert (oder einen Zellbereich) zurückzugeben, indem Sie eine bestimmte Anzahl von Zeilen und Spalten von einer Startreferenz verschieben. Wenn nur nach einer einzelnen Zelle gesucht wird, erreichen OFFSET-Formeln denselben Zweck wie die INDEX-Formeln, wobei eine etwas andere Technik verwendet wird. Die wahre Stärke der OFFSET-Funktion liegt in ihrer Fähigkeit, einen Zellbereich auszuwählen, der in einer anderen Formel verwendet werden soll.
Wenn Sie die OFFSET-Funktion verwenden, definieren Sie eine anfängliche Startzelle oder einen Zellbereich. Dann geben Sie die Anzahl der Zeilen und Spalten an, die von dieser Ausgangszelle versetzt werden sollen. Sie können auch die Größe des Bereichs ändern; Zeilen oder Spalten hinzufügen oder subtrahieren.
Zurück zur Liste aller Funktionen in Excel
OFFSET in Google Tabellen
Die OFFSET-Funktion funktioniert in Google Sheets genauso wie in Excel:
