SVERWEIS, HVERWEIS und INDEX-MATCH sind bekannte und gängige Methoden zum Nachschlagen von Werten in einer Excel-Tabelle. Dies sind jedoch nicht die einzigen Möglichkeiten zum Nachschlagen von Werten in Excel. In diesem Artikel erfahren Sie, wie Sie die OFFSET-Funktion zusammen mit der MATCH-Funktion in Excel verwenden. Ja, Sie haben 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)) |
Lesen Sie dies sorgfältig durch:
Startzelle:Dies ist die Startzelle der Nachschlagetabelle. Nehmen wir an, wenn Sie im Bereich A2:A10 suchen möchten, ist die Startzelle A1.
RowLookupValue: Dies ist der Suchwert, den Sie in den Zeilen unter dem suchen möchtenStartzelle.
RowLookupRange:Dies ist der Bereich, in dem Sie den RowLookupValue suchen möchten. Es ist der Bereich unten Startzelle (A2:A10).
ColLookupValue: Dies ist der Nachschlagewert, den Sie in Spalten (Überschriften) suchen möchten.
ColLookupRange:Dies ist der Bereich, in dem Sie den ColLookupValue suchen möchten. Dies ist der Bereich auf der rechten Seite von StartCell (wie B1:D1).
Also genug der Theorie. Beginnen wir mit einem Beispiel.
Beispiel: Lookup Sales mit OFFSET- und MATCH-Funktion aus Excel-Tabelle
Hier haben wir eine Beispieltabelle für Verkäufe, die von verschiedenen Mitarbeitern in verschiedenen Monaten durchgeführt 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 generische Formel oben. Wir müssen nur diese Variablen in dieser Tabelle identifizieren.
Startzelle 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 Range auf der rechten Seite der StartCell) .
Wir haben alle Variablen identifiziert. Setzen 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 umgehend das Ergebnis. Der Verkauf mit der ID 1004 im Juni-Monat ist 177.
Wie funktioniert es?
Die Aufgabe der OFFSET-Funktion besteht darin, sich von der gegebenen Startzelle zu einer gegebenen Zeile und Spalte zu bewegen und dann den Wert aus dieser Zelle zurückzugeben. Wenn ich beispielsweise OFFSET(B1,1,1) schreibe, geht die OFFSET-Funktion zu Zelle C2 (1 Zelle nach unten, 1 Zelle nach rechts) und gibt den Wert zurück.
Hier haben wir FormelOFFSET(B1,MATCH(M1,B2:B10,0),MATCH(M2,C1:I1,0)).
Die erste MATCH-Funktion gibt den Index von M1 (1004) im Bereich B2:B10 zurück, der 4 ist. Nun 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, was i 7 ist. Jetzt lautet die FormelOFFSET(B1,4,7).
Jetzt verschiebt die OFFSET-Funktion einfach 4 Zellen nach unten zur Zelle B1, die sie zu B5 bringt. Dann bewegt sich die OFFSET-Funktion auf 7 links zu B5, was sie zu I5 führt. Das ist es. Die OFFSET-Funktion gibt den Wert aus Zelle I5 zurück, der 177.
Vorteile dieser Nachschlagetechnik?
Das ist schnell. Der einzige Vorteil dieser Methode ist, dass sie schneller ist als die anderen Methoden. Das gleiche kann mit der INDEX-MATCH-Funktion oder der SVERWEIS-Funktion durchgeführt werden. Aber es ist gut, einige Alternativen zu kennen. Es kann eines Tages nützlich sein.
Also ja, Leute, so können Sie die OFFSET- und MATCH-Funktion verwenden, um Werte in Excel-Tabellen nachzuschlagen. Ich hoffe, es war erklärend genug. Wenn Sie Zweifel an diesem Artikel oder einem anderen Thema im Zusammenhang mit Excel/VBA haben, fragen Sie mich im Kommentarbereich unten.
Verwenden Sie INDEX und MATCH, um den Wert zu suchen:Die INDEX-MATCH-Formel wird verwendet, um dynamisch und präzise einen Wert in einer gegebenen Tabelle zu suchen. Dies ist eine Alternative zur SVERWEIS-Funktion und überwindet die Unzulänglichkeiten der SVERWEIS-Funktion.
Nach OFFSET-Gruppen in Zeilen und Spalten summieren: Die Funktion OFFSET kann verwendet werden, um Zellengruppen dynamisch zu summieren. Diese Gruppen können sich an einer beliebigen Stelle im Arbeitsblatt befinden.
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 Spalten abrufen. Diese Formel verwendet die ROW-Funktion, um durch Zeilen zu wechseln, und die COLUMN-Funktion, um in Spalten zu wechseln.
So verwenden Sie die OFFSET-Funktion in Excel: Die OFFSET-Funktion ist eine leistungsstarke Excel-Funktion, die von vielen Benutzern unterschätzt wird. Aber Experten kennen die Leistungsfähigkeit der OFFSET-Funktion und wie können wir diese Funktion verwenden, um einige magische Aufgaben in der Excel-Formel zu erledigen. Hier sind die Grundlagen der OFFSET-Funktion.
Populäre Artikel:
50 Excel-Kurzbefehle zur Steigerung Ihrer Produktivität | Werden Sie schneller bei Ihrer Aufgabe. Mit diesen 50 Tastenkombinationen arbeiten Sie noch schneller mit Excel.
So verwenden Sie die Excel SVERWEIS-Funktion| 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 Excel ZÄHLENWENN-Funktion| Zählen Sie Werte mit Bedingungen mit dieser erstaunlichen Funktion. Sie müssen Ihre Daten nicht filtern, um bestimmte Werte zu zählen. Die ZÄHLENWENN-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.