Excel OFFSET-Funktion (Inhaltsverzeichnis)
- OFFSET in Excel
- OFFSET-Formel in Excel
- Wie verwende ich die OFFSET-Funktion in Excel?
OFFSET in Excel
Die OFFSET-Funktion in Excel hilft dem Benutzer, eine Zelle oder einen Zellbereich mit der angegebenen Nummer zurückzugeben. von Zeilen und Spalten aus einer Zelle. Wir können die Nr. Angeben. von Zeilen und Nr. Anzahl der zurückzugebenden Spalten.
Unten ist die OFFSET-Formel in Excel:
Die OFFSET-Funktion in Excel besteht aus folgenden Argumenten:
- Referenz: Es ist das Argument, auf das wir den Offset gründen wollen. Dies kann eine Zellreferenz oder ein Bereich benachbarter Zellen sein. Andernfalls gibt Offset den Wert #VALUE! Fehlerwert.
- Zeilen: Es ist die Nr. Anzahl der zu versetzenden Zeilen. Wenn wir eine positive Zahl verwenden, versetzt es die Zeilen darunter und wenn eine negative Zahl verwendet wird, versetzt es die Zeilen darüber.
- Spalten: Es ist für keine. Anzahl der zu versetzenden Spalten. Es ist also dasselbe Konzept wie bei Rows, wenn wir eine positive Zahl verwenden, versetzt es sich zu den Spalten auf der rechten Seite, und wenn eine negative Zahl verwendet wird, versetzt es sich zu den Spalten auf der linken Seite.
- Höhe: Dieses Argument ist optional. Es sollte eine positive Zahl sein. Dies ist eine Zahl, die die Anzahl der Zeilen in der zurückgegebenen Referenz darstellt.
- Breite: Dies ist auch ein optionales Argument. Es muss eine positive Zahl sein. Dies ist eine Zahl, die die Anzahl der Spalten in der zurückgegebenen Referenz darstellt.
Anmerkungen:
- Die OFFSET-Funktion in Excel gibt das #REF zurück! Fehlerwert, wenn Zeilen und Spalten den Bezug über die Kante des Arbeitsblatts versetzen.
- In der OFFSET-Funktion wird davon ausgegangen, dass die Höhe oder Breite der Referenz entspricht, wenn Höhe oder Breite weggelassen werden.
- OFFSET gibt eine Referenz zurück, es werden keine Zellen oder Zellbereiche verschoben oder die Auswahl geändert. Es kann mit jeder Funktion verwendet werden, die ein Referenzargument erwartet.
Wie verwende ich die OFFSET-Funktion in Excel?
Die OFFSET-Funktion in Excel ist sehr einfach und benutzerfreundlich. Machen Sie sich anhand eines Beispiels für eine OFFSET-Formel mit der Funktionsweise der OFFSET-Funktion in Excel vertraut.
Sie können diese Excel-Vorlage für die OFFSET-Funktion hier herunterladen - Excel-Vorlage für die OFFSET-FunktionBeispiel 1
Die Versatzformel gibt eine Zellreferenz basierend auf einem Startpunkt, Zeilen und Spalten zurück, die wir angeben. Wir können es in folgendem Beispiel sehen:
= OFFSET (A1, 3, 1)
Die Formel weist Excel an, Zelle A1 als Startpunkt (Referenz) zu betrachten und dann 3 Zeilen nach unten (Zeilen) und 1 Spalte nach links (Spaltenargument) zu verschieben. Danach gibt die OFFSET-Formel den Wert in Zelle B4 zurück.
Das Bild links zeigt, dass die Funktionsroute und der Screenshot rechts zeigen, wie wir die OFFSET-Formel auf Echtzeitbasis verwenden können. Der Unterschied zwischen den beiden Formeln besteht darin, dass die zweite (rechts) einen Zellverweis (D1) im Argument der Zeile enthält. Da Zelle D1 jedoch die Nummer 1 enthält und im Zeilenargument der ersten Formel genau dieselbe Nummer erscheint, würden beide ein identisches Ergebnis zurückgeben - den Wert in B2.
Beispiel # 2 Verwendung der OFFSET-Funktion für die Summe
Da wir wissen, dass die OFFSET-Funktion auch mit der anderen Excel-Funktion verwendet werden kann, sehen wir dies in diesem Beispiel mit der SUM-Funktion.
Nehmen wir im obigen Bild an, wir müssen die Summe der Markierungen herausfinden, dann können wir auch die OFFSET-Funktion verwenden.
Die Formel ist unten:
In der obigen Formel ist D5 also die erste Zelle, in der die Daten beginnen. Dies ist eine Referenz als Startzelle. Danach ist der Zeilen- und Spaltenwert 0, also können wir ihn als 0, 0 setzen. Dann gibt es 4, dh 4 Zeilen unter der Referenz, für die Excel die Summe berechnen muss, und dann 1, dh 1 Spalte als Breite.
Wir können auf das obige Bild verweisen, um das erläuterte Beispiel miteinander in Beziehung zu setzen.
Und unten ist das Ergebnis:
Wir müssen nur die Eingabetaste drücken, nachdem wir eine Formel in die gewünschte Zelle eingegeben haben, und dann haben wir das Ergebnis.
Wichtige Informationen zur OFFSET-Funktion in Excel
- Die OFFSET-Funktion in Excel gibt nur eine Referenz zurück. Es werden keine Zellen oder Zellbereiche in der Realität verschoben.
- Wenn die OFFSET-Formel einen Bereich von Zellen zurückgibt, beziehen sich die Zeilen und Spalten immer auf die obere linke Zelle im zurückgegebenen Bereich.
- Die Referenz sollte eine Zelle oder einen Zellbereich enthalten, da sonst die Formel den Fehler #WERT zurückgibt.
- Wenn die angegebenen Zeilen und Spalten über dem Rand der Tabelle liegen, gibt die Versatzformel in Excel den Wert #REF! Error.
- Die OFFSET-Funktion kann in jeder anderen Excel-Funktion verwendet werden, die eine Zellen- oder Bereichsreferenz in ihren Argumenten akzeptiert.
Wenn wir zum Beispiel die Formel = OFFSET (A1, 3, 1, 1, 3) alleine verwenden, wird ein #WERT! Fehler, da ein Rückgabebereich (1 Zeile, 3 Spalten) nicht in eine Zelle passt. Wenn wir es jedoch wie folgt in die SUMME-Funktion einsortieren:
= SUMME (OFFSET (A1, 3, 1, 1, 3))
Die Offset-Formel gibt die Summe der Werte in einer Zeile mit drei Spalten, also drei Zeilen darunter und einer Spalte rechts von Zelle A1, zurück, dh die Summe der Werte in den Zellen B4: D4.
Wie wir sehen können, ist die Summe von 36 + 63 + 82 des hervorgehobenen Mar gleich 181, was nach Anwendung der Summen- und Versatzformel zu G2 führte. (hervorgehoben in Gelb)
Verwendung der OFFSET-Funktion in Excel
Wie wir anhand des obigen Beispiels gesehen haben, was die OFFSET-Funktion tatsächlich bewirkt, können wir Fragen haben: Warum sollte man die etwas komplexe gesungene OFFSET-Formel stören, warum nicht einfach eine direkte Referenz wie sum oder B4: D4 verwenden?
Die OFFSET-Funktion in Excel ist sehr gut für Folgendes:
- Abrufen der Reichweite von der Startzelle:
Manchmal kennen wir die genaue oder tatsächliche Adresse des Bereichs nicht, wir wissen nur, wo sie von einer bestimmten Zelle ausgeht. In diesem Fall können wir die OFFSET-Funktion verwenden, die einfach zu bedienen ist.
- Dynamischen Bereich erstellen:
Die Referenzen wie im obigen Beispiel B1: C4 sind statisch, dh sie beziehen sich immer auf einen vorgegebenen oder festen Bereich. In einigen Fällen sind die Aufgaben jedoch mit Dynamikbereichen einfacher auszuführen. Dies ist besonders hilfreich, wenn wir mit sich ändernden Daten arbeiten. Zum Beispiel könnten wir eine Tabelle haben, in der jeden Tag / jede Woche / jeden Monat eine neue Zeile oder Spalte eingefügt oder hinzugefügt wird. In diesem Fall hilft die OFFSET-Funktion.
Einschränkungen und Alternativen der OFFSET-Funktion in Excel
Da jede Formel in Excel ihre eigenen Einschränkungen und Alternativen hat, haben wir gesehen, wie und wann die OFFSET-Funktion in Excel verwendet werden muss.
Nachfolgend sind die kritischen Einschränkungen der OFFSET-Funktion in Excel aufgeführt:
- Ressourcenhungrige Funktion:
Dies bedeutet, dass bei jeder Änderung der Quelldaten die Formel für den gesamten Satz neu berechnet wird, wodurch Excel länger beschäftigt bleibt. Wenn eine kleine Tabelle vorhanden ist, hat dies keine großen Auswirkungen. Wenn jedoch mehrere Tabellen vorhanden sind, kann die Neuberechnung von Excel einige Zeit in Anspruch nehmen.
- Schwierigkeiten bei der Überprüfung:
Wie wir wissen, sind die von der Offset-Funktion zurückgegebenen Referenzen dynamisch oder ändern sich und enthalten möglicherweise eine große Formel, deren Korrektur oder Bearbeitung je nach Anforderung schwierig sein kann.
Alternativen der OFFSET-Funktion in Excel:
- INDEX-Funktion:
Die INDEX-Funktion in Excel kann auch zum Erstellen eines dynamischen Referenzbereichs verwendet werden. Es ist nicht genau dasselbe wie OFFSET. Aber wie OFFSET ist die INDEX-Funktion nicht sehr flüchtig und verlangsamt daher nicht das Excel, was eine Einschränkung für OFFSET darstellt.
- INDIREKTE Funktion:
Wir können auch die Funktion INDIRECT verwenden, um einen dynamischen Referenzbereich aus vielen Quellen wie Zellwerten , Text und den benannten Bereichen zu erstellen.
Empfohlene Artikel
Dies war eine Anleitung zu OFFSET in Excel. Hier werden die OFFSET-Formel in Excel und die Verwendung der OFFSET-Funktion in Excel zusammen mit praktischen Beispielen und einer herunterladbaren Excel-Vorlage erläutert. Sie können auch unsere anderen Artikelvorschläge durchgehen -
- MS Excel: LOOKUP-Funktion
- Bestes Beispiel für eine Excel XIRR-Funktion
- Erstaunliche Anleitung zu NOT Function in Excel
- IPMT in Excel mit Beispielen