In diesem Artikel erfahren Sie, wie Sie die OFFSET-Funktion in Excel verwenden.
Was ist OFFSET-Funktion?
Die Offset-Funktion ist eine Funktion zum Abrufen von Informationen. Wenn Sie eine große Tabelle mit bestimmten Daten haben und Daten daraus abrufen möchten, fügen Sie am besten eine Versatzformel für die Tabelle in dieses Arbeitsblatt oder ein anderes Blatt in derselben Arbeitsmappe ein. Sobald Sie also die Offset-Retrieval-Tabelle erstellt haben, müssen Sie einfach die Daten eingeben, zum Beispiel "Monat", und Sie erhalten den "Umsatz" oder "Einkommen" dieses Monats aus dieser Tabelle.
Das Durchsuchen der Tabelle ist eine weitere Option, aber würden Sie die Option in Betracht ziehen, wenn Sie Blätter und Blätter mit Daten haben, die alle eine Datentabelle mit 1000 Spalten enthalten? Hier wird Offset mit anderen Funktionen kombiniert.
Das Konzept der Pivot-Tabelle stammt aus dem Offset. Eine Pivot-Tabelle ist eine allgemeine Tabelle und aus dieser spezifischen Kombination von Daten und Grafiken wird bei Bedarf abgerufen. Auf die gleiche Weise können auch dynamische Tabellen erstellt werden. Für diese Tabellen müssen Sie die Excel-Offset-Formel erstellen
Syntax von OFFSET
| =OFFSET(Referenz,Zeilen,Spalten, [Höhe], [Breite]) |
Reihen - Sie bitten Excel, die Anzahl der Zeilen zu verschieben, um die Informationen zu erhalten. In diesem Fall muss es sich um den Tisch herum bewegen und so einfach ein Leerzeichen, gekennzeichnet durch ein Komma (,), lassen. Andernfalls, um eine Spalte nach vorne zu verschieben, geben Sie den Wert 1 ein und um eine Spalte nach vorne zu verschieben, geben Sie einen Wert -1 ein.
Säulen - dies dient dazu, die Funktion auf die Anzahl der Spalten zu treiben. Das Wertesystem ist das gleiche wie für Zeile.
Höhe - die Höhe des Tisches, in diesem Fall A11.
Breite - Breite des Tisches, in diesem Fall D11.
Nachdem Sie die Offset-Funktion eingerichtet haben, ist es wichtig, einen Versuch durchzuführen, um alle Fehler auszusortieren.
Nehmen wir eine Tabelle mit Name, E-Mail-ID, Geburtsdatum und Alter. Die Tabelle beginnt bei Zelle A1 und enthält die Überschrift „Name“. Die Daten laufen für sagen wir A11. Und die Reihen laufen bis D1. Die gesamte Tabelle läuft A1:D11. Sie möchten ein Abrufsystem, das Ihnen den Namen bei der Eingabe der E-Mail-ID oder die E-Mail-ID bei der Eingabe des Namens zusammen mit anderen Details liefert. Sie erstellen eine Formel, indem Sie die 5 Argumente erfüllen.
Bezugspunkt ist die Zelle, von der aus die Suche beginnen soll. In diesem Fall sollte es A2 sein. Das ist der allgemeine Standard.
Beispiel:
Zum Beispiel geben Sie die Referenz von B4 und Offset-Zeile 0 und Offset-Spalte 1 an, dann wird der Wert von C4 zurückgegeben. Verwirrend? Lassen Sie uns noch einige Beispiele haben. Das letzte Beispiel der OFFSET-Funktion erklärt, wie man dynamisch summiert.
OFFSET-Funktion zum Abrufen von Werten von rechts und links einer Zelle
Wir haben diesen Tisch.
Nun, wenn ich den Wert von 2 Zellen direkt nach B2 (was D2 bedeutet) abrufen möchte. Ich schreibe diese OFFSET-Formel:
| =OFFSET(B2,0,2) |
Die OFFSET-Funktion verschiebt 2 Zellen nach rechts von Zelle B4 und gibt ihren Wert zurück. Siehe Bild unten
Wenn ich den Wert von 1 Zelle links bis B2 abrufen möchte (was A2 bedeutet). Ich schreibe diese OFFSET-Formel:
| =OFFSET(B2,0,-1) |
Das Minuszeichen (-) zeigt den Versatz an, um sich umgekehrt zu bewegen.
OFFSET-Funktion zum Abrufen von Werten von unten und oben einer Zelle
Also noch einmal in der obigen Tabelle, wenn ich einen Wert von 2 Zellen zu B3 (was B5 bedeutet) erhalten möchte. Ich schreibe diese OFFSET-Formel:
| =OFFSET(B2,2,0) |
Wenn ich den Wert von 1 Zelle links bis B2 abrufen möchte (was A2 bedeutet). Ich schreibe diese OFFSET-Formel:
| =OFFSET(B2,-2,0) |
- Profi-Tipp: Behandeln Sie einen OFFSET als Graphenzeiger, wobei Referenz der Ursprung ist und Zeile und Spalte die x- und y-Achse sind.
OFFSET-Funktion zum dynamischen Summieren des Bereichs
Sehen wir uns dieses Beispiel an.
In obiger Tabelle Gesamtzeile am Ende der Tabelle. Im Laufe der Zeit werden Sie dieser Tabelle Zeilen hinzufügen. Dann müssen Sie den Summenbereich in der Formel ändern. Hier können wir die Funktion OFFSET verwenden, um dynamisch zu summieren. Derzeit haben wir in Zelle C11 =SUMME(C2:C10). Ersetzen Sie dies durch die folgende Formel.
| =SUMME(C2:OFFSET(C11,-1,0)) |
Diese Formel nimmt nur die erste Datenzeile und summiert dann den Bereich über der Gesamtzeile.
Offset zum Abrufen des Arrays
Die OFFSET-Funktion hat zwei optionale Argumente, [Höhe] und [Breite]. Nehmen Sie sie nicht als selbstverständlich hin. Wenn die OFFSET-Funktion die Argumente [Höhe] und [Breite] liefert, gibt sie ein zweidimensionales Array zurück. Wenn nur einer von ihnen als Argument übergeben wird, wird ein dimensionales Array von Werten zurückgegeben.
Wenn Sie diese Formel in eine beliebige Zelle eingeben:
| =SUMME(OFFSET(C1,1,0,9,3)) |
Es summiert Werte im Bereich von C2 bis 9 Zeilen darunter und 3 Spalten rechts.
OFFSET(C1,1,0,9,3) : Dies entspricht {8,8,7;6,1,2;8,5,8;5,1,5;8,3,5;8,3,9;8,9,2;3,5 ,4;6,6,5}.
Zusammenfassend lässt sich sagen, dass Offset eine sehr nützliche Funktion von Excel ist, die Ihnen helfen kann, viele erdrosselte Threads zu lösen. Lassen Sie mich wissen, wie Sie die OFFSET-Funktion in Ihrer Formel verwenden und wo sie Ihnen am meisten geholfen hat.
Die Lookup-Funktion entspricht der Offset-Funktion, verwendet jedoch Funktionen wie Match, Counta und IF, um eine dynamische Tabelle zu bearbeiten.
Die Verwendung von Offset in Excel erfordert Geschick und gute Kenntnisse der Excel-Funktionen und ihrer Zusammenarbeit. Das fehlerfreie Durchziehen ist die größte Hürde.
Hier sind alle Beobachtungshinweise zur Verwendung der Formel.
Anmerkungen:
- Diese Formel funktioniert sowohl mit Text als auch mit Zahlen.
- Es gibt fünf Argumente des Offsets, die erfüllt werden müssen, um eine fehlerfreie Information zu erhalten. Wenn Sie eine falsche Formel schreiben, tritt ein Ref-Fehler auf.
Ich hoffe, Sie haben verstanden, was die Excel-Offset-Funktion ist und wie man sie in Excel verwendet. Entdecken Sie hier weitere Artikel zum Nachschlagen in Excel und zum Abgleichen von Werten mithilfe von Formeln. Wenn Ihnen unsere Blogs gefallen haben, teilen Sie sie mit Ihren Freunden auf Facebook. Und Sie können uns auch auf Twitter und Facebook folgen. Wir würden uns freuen, von Ihnen zu hören, lassen Sie uns wissen, wie wir unsere Arbeit verbessern, ergänzen oder erneuern und für Sie verbessern können. Schreiben Sie uns auf der E-Mail-Site.
SVERWEIS-Funktion zum Berechnen der Note in Excel : Um die Noten zu berechnen, sind IF und IFS nicht die einzigen Funktionen, die Sie verwenden können. Der SVERWEIS ist für solche bedingten Berechnungen effizienter und dynamischer. Um Noten mit SVERWEIS zu berechnen, können wir diese Formel verwenden.
17 Dinge über Excel SVERWEIS : SVERWEIS wird am häufigsten zum Abrufen übereinstimmender Werte verwendet, aber SVERWEIS kann noch viel mehr. Hier sind 17 Dinge über SVERWEIS, die Sie wissen sollten, um sie effektiv zu nutzen.
LOOKUP den ersten Text aus einer Liste in Excel : Die SVERWEIS-Funktion funktioniert gut mit Platzhalterzeichen. Wir können dies verwenden, um den ersten Textwert aus einer bestimmten Liste in Excel zu extrahieren. Hier ist die generische Formel.
LOOKUP-Datum mit letztem Wert in der Liste : Um das Datum abzurufen, das den letzten Wert enthält, verwenden wir die LOOKUP-Funktion. Diese Funktion sucht nach der Zelle, die den letzten Wert in einem Vektor enthält, und verwendet dann diese Referenz, um das Datum zurückzugeben.
So rufen Sie den neuesten Preis in Excel ab : Es ist üblich, die Preise in jedem Unternehmen zu aktualisieren und die Verwendung der neuesten Preise für jeden Kauf oder Verkauf ist ein Muss. Um den aktuellen Preis aus einer Liste in Excel abzurufen, verwenden wir die LOOKUP-Funktion. Die LOOKUP-Funktion ruft den neuesten Preis ab.
Populäre Artikel:
50 Excel-Shortcut zur Steigerung Ihrer Produktivität : Erledigen Sie Ihre Aufgabe schneller. Mit diesen 50 Tastenkombinationen arbeiten Sie noch schneller mit Excel.
So verwenden Sie die IF-Funktion in Excel : Die IF-Anweisung in Excel überprüft die Bedingung und gibt einen bestimmten Wert zurück, wenn die Bedingung WAHR ist, oder einen anderen bestimmten Wert, wenn FALSE.
So verwenden Sie die SVERWEIS-Funktion in Excel : Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, die verwendet wird, um Werte aus verschiedenen Bereichen und Blättern zu suchen.
So verwenden Sie die ZÄHLENWENN-Funktion in Excel : Zählen Sie Werte mit Bedingungen mit dieser erstaunlichen Funktion. Sie müssen Ihre Daten nicht filtern, um bestimmte Werte zu zählen. Die Countif-Funktion ist unerlässlich, um Ihr Dashboard vorzubereiten.
So verwenden Sie die SUMIF-Funktion in Excel : Dies ist eine weitere wesentliche Funktion des Dashboards. Dies hilft Ihnen, Werte unter bestimmten Bedingungen zusammenzufassen.