Excel OFFSET Formula (Inhaltsverzeichnis)

  • Was ist die OFFSET-Formel in Excel?
  • Wie verwende ich die OFFSET-Formel in Excel?

Was ist die OFFSET-Formel in Excel?

OFFSET-Formel in Excel gibt eine Zelle oder einen Bereich von dynamischen rechteckigen Zellen als Ausgabe an, die eine Teilmenge des gesamten angegebenen Bereichs mit einer angegebenen Anzahl von Zeilen, Spalten, Höhe und Breite ist.

Syntax der OFFSET-Formel in Excel

Erläuterung der OFFSET-Funktion in Excel:

  • Referenz: Der Startpunkt für OFFSET oder ein Zellbereich, der als Basis für OFFSET angesehen werden kann.
  • Zeilen : Die Anzahl der Zeilen, die unterhalb oder oberhalb der Basisreferenz auf OFFSET gesetzt werden sollen.
  • cols: Die Anzahl der Spalten, die rechts oder links von der Basisreferenz auf OFFSET gesetzt werden sollen.
  • height: Anzahl der Zeilen in der zurückgegebenen Referenz.
  • width: Anzahl der Spalten in der zurückgegebenen Referenz.

Die Höhe und Breite sind die optionalen Parameter für diese Funktion und können verwendet werden, um die Größe der erstellten Referenz zu bestimmen.

Wie verwende ich die OFFSET-Formel in Excel?

Wir werden nun lernen, wie man eine Offset-Formel schreibt, um den Nachschlagewert zu erhalten. Lassen Sie uns die Versatzformel anhand einiger Beispiele in Excel verstehen.

Sie können diese OFFSET-Formel-Excel-Vorlage hier herunterladen - OFFSET-Formel-Excel-Vorlage

Excel OFFSET Formula - Beispiel # 1

Angenommen, wir haben die unten angegebenen Daten.

  • Geben Sie in Zelle E2 die folgende Formel ein:

= OFFSET (A1, 11, 1)

  • Wenn Sie mit der Formel fertig sind, drücken Sie die Eingabetaste und sehen Sie die Ausgabe.

Diese Formel bezieht sich auf Zelle A1 (Jahr) und wurde nach A1 um 11 Zeilen nach unten verschoben (dh Zeile Nr. 12). Anschließend wurde der Wert ermittelt, der der Koordinate der 11. Zeile nach A1 und der ersten Spalte entspricht ( Spalte A = 0 Spalte B = 1. Wie wir wissen, können wir sowohl nach rechts als auch nach links gehen. Das sind 141, 27 Millionen US-Dollar.

Excel OFFSET Formula - Beispiel 2

Nun sehen wir ein weiteres Beispiel einer Referenzzelle für ein bestimmtes Jahr. Fügen Sie die folgende Formel in Zelle E3 ein:

= OFFSET (A1, 11, 0)

  • Wenn Sie mit der Formel fertig sind, drücken Sie die Eingabetaste und sehen Sie die Ausgabe.

Wenn Sie die Formel gesehen haben, stimmt sie mit der vorherigen überein, die einzige Änderung betrifft die Spaltenreferenz. Die Spaltenreferenz 0 (Null) bedeutet, dass der Wert aus Spalte A für die 11. Zeile nach A1 (das ist 2010) erfasst wird.

Excel OFFSET Formula - Beispiel # 3

In diesem Beispiel berechnen wir die Summe der letzten N Zeilen mit der Excel OFFSET-Funktion. Die allgemeine Formel zum Aufsummieren der letzten N Zahlen lautet:

= SUMME (OFFSET (A1, COUNT (A: A), 0, -N))

Um die letzten N Beobachtungen aus Ihren Daten zusammenzufassen, können Sie die SUMME-Funktion zusammen mit der OFFSET- und der COUNT-Funktion verwenden. In diesem Beispiel werden die letzten 5 Datenpunkte zusammengefasst. Bitte beachten Sie, dass es sich bei N um eine Verallgemeinerung eines beliebigen ganzzahligen Wertes handelt. Sie können also die letzten 3, letzten 4, letzten 5 usw. abhängig von Ihrem Datensatz und Ihrer Anforderung berechnen.

Lassen Sie es uns für unseren Datensatz erledigen:

  • Geben Sie die folgende Formel in Zelle E2 der Excel-Datei ein.

= SUMME (OFFSET (B1, COUNT (B: B), 0, -5))

  • Klicken Sie auf die Eingabetaste, um die Ausgabe anzuzeigen.

Wie wir bereits gesehen haben, gibt die Excel OFFSET-Funktion bei einer Startreferenz rechteckige Dynamikbereiche aus.

  • In diesem Fall wird die Startreferenz in Form von B1 angegeben (als erstes Argument für die OFFSET-Funktion).
  • Wir haben die COUNT-Funktion verwendet, weil wir die Summe der Beobachtungen vom letzten Wert benötigen. Die COUNT-Funktion hilft OFFSET, um zu bestimmen, wie viele Beobachtungen (Zeilen) in Spalte B vorhanden sind. Sie zählt alle numerischen Werte in Spalte B (19 ist die Anzahl).
  • Mit der Zählung 19 wird die OFFSET-Formel zu:

= OFFSET (B1, 19, 0, -5)

  • Diese Formel beginnt bei B1 mit 19 Zeilen und derselben Spalte (0). Dann hilft der Höhenparameter -5 dieser Funktion, den Bereich um 5 Zeilen nach hinten zu erweitern (siehe das negative Vorzeichen in Verbindung mit 5).
  • Der Dynamikbereich der letzten fünf Beobachtungen wird als Eingabe für die Summenfunktion angegeben. Welche Ausbeuten summieren sich zu denen aus Spalte B.

Excel OFFSET Formula - Beispiel 4

In diesem Beispiel berechnen wir den Medianumsatz der letzten 3 Jahre. Die OFFSET-Funktion kann als Argument zur Berechnung derselben verwendet werden.

  • Fügen Sie die folgende Formel in Zelle E2 ein:

= MEDIAN (OFFSET (B1, COUNT (B: B), 0, -3))

  • Drücken Sie die Eingabetaste, um die Ausgabe anzuzeigen.

Diese Formel funktioniert auf den Linien ähnlich der vorherigen.

  • OFFSET Erstellt mit Hilfe der COUNT-Funktion (= OFFSET (A1, 19, 0, -3)) einen dynamischen Bereich der letzten drei Beobachtungen aus Spalte B.
  • Dies kann als Ausgabe an die MEDIAN-Funktion übergeben werden. Diese sortiert dann die Beobachtungen in aufsteigender oder absteigender Reihenfolge und wählt dann den mittelsten Wert aus, wie Sie in der Ausgabe sehen ($ 212.07).

Excel OFFSET Formula - Beispiel # 5

Hier berechnen wir das mittlere Jahr mit der Funktion MEDIAN und OFFSET.

  • Verwenden Sie dazu die folgende Formel in Zelle E3:

= MEDIAN (OFFSET (A1, COUNT (A: A), 0, -3))

  • Klicken Sie auf die Eingabetaste, um die Ausgabe anzuzeigen.

Das gleiche ist hier passiert. Die Formel nahm einen Dynamikbereich ein, der durch die OFFSET-Funktion mit Hilfe von COUNT erstellt wurde. Und verwendet es als Argument für eine MEDIAN-Funktion, die dann die Jahreswerte in aufsteigender oder absteigender Reihenfolge sortiert und dann als 2017 ausgegeben.

Die Verwendung der OFFSET-Formel erfolgt beim Erstellen der Dynamikbereiche. Es ist immer besser, an dynamischen Bereichen zu arbeiten, als an statischen. Da Sie vielleicht Daten haben, die von Zeit zu Zeit hinzugefügt werden. Zum Beispiel Employee Master. Ihre Mitarbeiterstammdaten werden von Zeit zu Zeit erweitert, da einige neue Mitarbeiter hinzukommen müssen. Es macht keinen Sinn, immer dieselbe Formel zu verwenden. Erstellen Sie stattdessen mithilfe der OFFSET-Funktion einen dynamischen Bereich und sehen Sie die Magie. Es wird Ihnen sicherlich Zeit sparen.

Dinge, an die man sich erinnern sollte

  • Die OFFSET-Funktion gibt nur eine Referenz auf einen bestimmten Zellbereich in Excel zurück. Die Zellen werden nicht von ihren ursprünglichen Positionen verschoben.
  • Standardmäßig funktioniert diese Funktion im oberen Teil (in Zeilen) und auf der rechten Seite (in Spalten). Es kann jedoch mit negativen Argumenten versehen werden, um die Standardfunktionalität umzukehren. Wie -negatives Zeilenargument hilft, die Daten von unten nach oben zu versetzen, und negatives Spaltenargument hilft, die Daten nach links zu versetzen.
  • OFFSET-Formel kann die Excel-Funktionalität verlangsamen, da es sich um eine Formel handelt, die bei jeder Änderung im Arbeitsblatt neu berechnet wird.
  • #REF-Fehler wird ausgegeben, wenn Ihr Bereich außerhalb des Bereichs Ihrer Arbeitsmappe liegt.
  • Jede andere Funktion, die eine Referenz erwartet, kann die OFFSET-Funktion verwenden.

Empfohlene Artikel

Dies ist eine Anleitung zur OFFSET-Formel in Excel. Hier besprechen wir die Verwendung der OFFSET-Funktion in Excel zusammen mit praktischen Beispielen und einer herunterladbaren Excel-Vorlage. Sie können auch unsere anderen Artikelvorschläge durchgehen -

  1. Wie binde ich Text in Excel ein?
  2. Einfache Anleitung zum Sortieren in Excel
  3. LOOKUP-Funktion in Excel
  4. Wie benutze ich die FIND-Funktion in Excel?

Kategorie:

Excel-Tipps