image

VLOOKUP, HLOOKUP und INDEX-MATCH sind bekannte und gebräuchliche Methoden zum Nachschlagen von Werten in Excel-Tabellen. Dies sind jedoch nicht die einzigen Möglichkeiten, Werte in Excel nachzuschlagen. In diesem Artikel erfahren Sie, wie Sie die OFFSET-Funktion zusammen mit der MATCH-Funktion in Excel verwenden. Ja, Sie haben es richtig gelesen. Wir werden die berüchtigte OFFSET-Funktion von Excel verwenden, um einen bestimmten Wert in einer Excel-Tabelle nachzuschlagen.

Generische Formel

=OFFSET(StartCell,MATCH(RowLookupValue,RowLookupRange,0),MATCH(ColLookupValue,ColLookupRange,0))

image

Lesen Sie dies sorgfältig durch:

StartCell: Dies ist die Startzelle der Nachschlagetabelle. Angenommen, Sie möchten im Bereich A2: A10 nachschlagen, dann ist die StartCell A1.

RowLookupValue: Dies ist der Suchwert, den Sie in Zeilen unterhalb der StartCell suchen möchten. RowLookupRange: Dies ist der Bereich, in dem Sie den RowLookupValue nachschlagen möchten. Dies ist der Bereich unterhalb von StartCell (A2: A10). ColLookupValue: Dies ist der Suchwert, den Sie in Spalten (Überschriften) suchen möchten.

ColLookupRange: Dies ist der Bereich, in dem Sie den ColLookupValue nachschlagen möchten. Dies ist der Bereich auf der rechten Seite von StartCell (wie B1: D1).

Also genug von der Theorie. Beginnen wir mit einem Beispiel.

Beispiel: Nachschlagen von Verkäufen mit OFFSET- und MATCH-Funktion Aus Excel-Tabelle Hier haben wir eine Beispieltabelle mit Verkäufen, die von verschiedenen Mitarbeitern in verschiedenen Monaten getätigt wurden.

Jetzt bittet uns unser Chef, die Verkäufe von Id 1004 im Juni-Monat anzugeben. Es gibt viele Möglichkeiten, dies zu tun, aber da wir etwas über die OFFSET-MATCH-Formel lernen, werden wir sie verwenden, um den gewünschten Wert zu suchen.

Wir haben bereits die obige generische Formel. Wir müssen nur diese Variablen in dieser Tabelle identifizieren.

StartCell ist hier B1. RowLookupValue ist M1. RowLookupRange ist B2: B1o (Bereich unterhalb der Startzelle).

ColLookupValue befindet sich in der M2-Zelle. ColLookupRange ist C1: I1 (Header-Bereich rechts von der StartCell).

Wir haben alle Variablen identifiziert. Fügen wir sie in eine Excel-Formel ein.

Schreiben Sie diese Formel in Zelle M3 und drücken Sie die Eingabetaste.

=OFFSET(B1,MATCH(M1,B2:B10,0),MATCH(M2,C1:I1,0))*

Wenn Sie die Eingabetaste drücken, erhalten Sie sofort das Ergebnis. Der Verkauf ID 1004 im Juni Monat ist 177.

Wie funktioniert es?

Die Aufgabe von „link: / counting-the-offset-function-in-excel [OFFSET-Funktion]“ besteht darin, von der angegebenen Startzelle zu bestimmten Zeilen und Spalten zu wechseln und dann den Wert aus dieser Zelle zurückzugeben. Wenn ich beispielsweise OFFSET (B1,1,1) schreibe, geht die OFFSET-Funktion in Zelle C2 (1 Zelle nach unten, 1 Zelle nach rechts) und gibt den Wert zurück.

Hier haben wir die Formel

OFFSET(B1,MATCH(M1,B2:B10,0),MATCH(M2,C1:I1,0))

Der erste link: / lookup-formulas-excel-match-function [MATCH-Funktion] gibt den Index von M1 (1004) im Bereich B2: B10 zurück, der 4 ist.

Jetzt lautet die Formel

OFFSET(B1,4,MATCH(M2,C1:I1,0))

Die nächste MATCH-Funktion gibt den Index von M2 (‚Jun‘) im Bereich C1: I1 zurück, der I7. Jetzt lautet die Formel OFFSET (B1,4,7). Jetzt verschiebt die OFFSET-Funktion einfach 4 Zellen nach unten in die Zelle B1, die sie zu B5 bringt. Dann bewegt sich die OFFSET-Funktion zu 7 links nach B5, wodurch sie zu I5 führt. Das ist es. Die OFFSET-Funktion gibt einen Wert aus Zelle I5 zurück, der 177.

Vorteile dieser Nachschlagetechnik?

Das geht schnell. Der einzige Vorteil dieser Methode ist, dass sie schneller ist als die anderen Methoden. Dasselbe kann mit der INDEX-MATCH-Funktion oder der VLOOKUP-Funktion durchgeführt werden. Aber es ist gut, einige Alternativen zu kennen.

Es kann eines Tages nützlich sein.

Hier einige Hinweise zur Verwendung der Versatzformel in Excel.

Hinweise:

  1. OFFSET gibt nur eine Referenz zurück, es werden keine Zellen verschoben.

  2. Sowohl Zeilen als auch Spalten können als negative Zahlen angegeben werden, um ihre normale Versatzrichtung umzukehren – negative Spalten nach links versetzt und negative Zeilen oben versetzt.

  3. OFFSET ist eine „flüchtige Funktion“ – sie wird bei jeder Änderung des Arbeitsblatts neu berechnet. Durch flüchtige Funktionen können größere und komplexere Arbeitsmappen langsam ausgeführt werden.

  4. OFFSET zeigt das #REF an! Fehlerwert, wenn der Versatz außerhalb des Randes des Arbeitsblatts liegt.

  5. Wenn Höhe oder Breite weggelassen wird, werden Höhe und Breite der Referenz verwendet.

  6. OFFSET kann mit jeder anderen Funktion verwendet werden, die eine Referenz erwartet.

  7. In der Excel-Dokumentation heißt es, dass Höhe und Breite nicht negativ sein dürfen, negative Werte jedoch funktionieren.

Hoffe, dieser Artikel über die Verwendung der Versatzformel in Microsoft Excel ist erklärend. Weitere Artikel zu Suchwerten und verwandten Excel-Formeln finden Sie hier. Wenn Ihnen unsere Blogs gefallen haben, teilen Sie sie Ihren Freunden auf Facebook mit. Und Sie können uns auch auf Twitter und Facebook folgen. Wir würden gerne von Ihnen hören, uns mitteilen, wie wir unsere Arbeit verbessern, ergänzen oder innovieren und für Sie verbessern können. Schreiben Sie uns unter [email protected].

Verwandte Artikel:

link: / lookup-formulas-use-index-and-match-to-lookup-value [Verwenden Sie INDEX und MATCH, um den Wert zu suchen]: Die INDEX-MATCH-Formel wird verwendet, um einen Wert in einer bestimmten Tabelle dynamisch und präzise zu suchen. Dies ist eine Alternative zur VLOOKUP-Funktion und überwindet die Mängel der VLOOKUP-Funktion.

link: / tips-sum-by-offset-Gruppen-in-Zeilen-und-Spalten [Summe nach OFFSET-Gruppen in Zeilen und Spalten]: Mit der OFFSET-Funktion können Gruppen von Zellen dynamisch summiert werden. Diese Gruppen können sich an einer beliebigen Stelle im Blatt befinden.

link: / Lookup-Formeln-Abrufen-jedes-n-ten-Wert-in-einem-Bereich [So rufen Sie jeden n-ten Wert in einem Bereich in Excel ab]: Mit der OFFSET-Funktion von Excel können wir Werte aus abwechselnden Zeilen oder abrufen Säulen. Diese Formel verwendet die ROW-Funktion, um durch Zeilen zu wechseln, und die COLUMN-Funktion, um in Spalten zu wechseln.

link: / counting-the-offset-function-in-excel [Verwendung der OFFSET-Funktion in Excel]: Die OFFSET-Funktion ist eine leistungsstarke Excel-Funktion, die von vielen Benutzern unterschätzt wird. Experten kennen jedoch die Leistungsfähigkeit der OFFSET-Funktion und wissen, wie wir diese Funktion verwenden können, um einige magische Aufgaben in der Excel-Formel auszuführen. Hier sind die Grundlagen der OFFSET-Funktion.

Beliebte Artikel:

link: / Tastatur-Formel-Verknüpfungen-50-Excel-Verknüpfungen-zur-Steigerung-Ihrer-Produktivität [50 Excel-Verknüpfungen zur Steigerung Ihrer Produktivität]: Schneller bei Ihrer Aufgabe. Mit diesen 50 Verknüpfungen können Sie noch schneller in Excel arbeiten.

link: / formeln-und-funktionen-einführung-der-vlookup-funktion [Verwendung der Excel VLOOKUP-Funktion]: Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, die zum Nachschlagen von Werten aus verschiedenen Bereichen und Tabellen verwendet wird .

link: / tips-countif-in-microsoft-excel [Verwendung des]

link: / formeln-und-funktionen-einführung-der-vlookup-funktion [Excel]

link: / tips-countif-in-microsoft-excel [COUNTIF-Funktion]: Zähle Werte mit Bedingungen, die diese erstaunliche Funktion verwenden.

Sie müssen Ihre Daten nicht filtern, um bestimmte Werte zu zählen. Die COUNTIF-Funktion ist wichtig, um Ihr Dashboard vorzubereiten.

link: / excel-formel-und-funktion-excel-sumif-funktion [Verwendung der SUMIF-Funktion in Excel]: Dies ist eine weitere wichtige Dashboard-Funktion. Auf diese Weise können Sie Werte unter bestimmten Bedingungen zusammenfassen.