Vlookup in Excel - Welche Formel ist am schnellsten?

Heute werden wir Vlookup in Excel mit verschiedenen Formeln durchführen, ihre Berechnungsgeschwindigkeit messen und die Konsequenz bewerten, damit Sie Ihren Gewinner auswählen können.

Informationen in verschiedenen Tabellen zu finden ist eine der häufigsten Aufgaben in Excel. Bedauerlicherweise ist die klassische Vlookup -Funktion berüchtigt, wenn es um die Verarbeitung von Kraft und Flexibilität geht. Kein Wunder, dass Excel -Benutzer im Laufe der Jahre ihre eigenen Lösungen wie eine Indexübereinstimmungsformel entwickelt haben. Glücklicherweise hat Microsoft endlich erkannt, dass Vlookup zu viele Schwächen hat und einen leistungsfähigeren Nachfolger veröffentlicht hat - die Xlookup -Funktion. Also, welches ist am besten zu bedienen?

Wenn Ihre Tabellen nur ein paar Dutzend Zeilen haben, ist höchstwahrscheinlich eine Methode schnell genug, und Sie werden den Unterschied nicht bemerken. Wenn jedoch die Anzahl der Zeilen in Tausenden gemessen wird, ist die korrekte Auswahl der Funktion von entscheidender Bedeutung - der Leistungsunterschied kann mehr als 10 Mal betragen! Beginnen wir den Timer und sehen, wer am schnellsten ist :)

• Quelldaten
• Vlookup -Funktion
• Index Match -Formel
• Offset Match -Formel
• Xlookup -Funktion
• Vlookup in Excel -Tischen
• Vlookup mit dynamischen Arrays
• Übereinstimmungen mit Power -Abfrage ziehen
• Zusätzlicher Bonus: Zauberer des Tische verschmelzen
• Zusammenfassung und Schlussfolgerungen

Quelldaten

In allen Beispielen verwenden wir den folgenden Datensatz gut:

• Haupttisch mit 500.000 Zeilen
• Suchtabelle mit 500 Zeilen enthält

Unser Ziel ist es, den Artikelnamen zu entsprechen und die Preise aus dem Nachschlagtisch in den Haupttisch zu ziehen.

Für jede Methode werden wir die Formel in C2 eingeben und sie bis C500001 kopieren, um die Zeit zu messen, die Excel benötigt, um eine halbe Million Zellen zu berechnen.

Natürlich hängt das Ergebnis von vielen Faktoren ab, wie z. B. Ihre CPU -Leistung, die RAM -Größe, die Excel -Version usw. In diesem Experiment sind es keine absolute Zahlen. Es ist wichtiger, die Leistung jeder Formel im Vergleich, ihre Vorteile und Nachteile zu verstehen.

Unsere Beispiele gehen davon aus, dass Sie über die Grundkenntnisse der Funktionen verfügen, und wir werden nicht viel auf ihre Syntax wohnen. Links zu eingehenden Tutorials sind zu Ihrer Bequemlichkeit enthalten.

Alle Tests wurden auf meinem Dell-Laptop (Intel Core i5-8250u; RAM 16 GB) mit Microsoft 365-Apps für geschäftliche Apps durchgeführt. Excel 32-Bit, Version 2011, Build 13415, Beta-Kanal. Die gleichen Tests wurden auch von meinem Kollegen auf Excel 64-Bit durchgeführt, und einige Ergebnisse sind drastisch unterschiedlich!

Vlookup -Formel

Wenn es darum geht, übereinstimmende Daten in Excel nachzuschauen und abzurufen, ist die erste Funktion, die Ihnen in den Sinn kommt, der gute alte Vlookup. Tatsächlich ist es nicht so gut, wie es sein könnte, aber wir werden etwas später darüber reden :)

Vlookup (Lookup_value, Table_Array, col_index_num, [range_lookup])

Um die Preise aus der Nachschlagtabelle (E2: F501) an die Haupttabelle (A2: B500001) zu ziehen, definieren wir die folgenden Argumente für unsere Vlookup -Formel:

• Lookup_Value : B2 - Der Wert, nach dem in der Nachschlagtabelle gesucht wird.
• TABLE_Array : $ e $ 3: $ f $ 501 - Bitte beachten Sie, dass wir die Referenzen mit dem $ -Schark sperrten, damit sie beim Kopieren der Formel nicht verschoben werden.
• COL_INDEX_NUM : 2 - Die Daten sind aus der ^2. Spalte der Such -Tabelle abgerufen.
• Range_lookup : Falsch - sucht nach exakter Übereinstimmung.

Die vollständige Formel lautet:

=VLOOKUP(B2, $E$3:$F$501, 2, FALSE)

Die obige Formel geht zu C2 und dann klicken wir auf das Plus -Zeichen in der unteren rechten Ecke, um die Formel über die gesamte Spalte zu kopieren. Im Moment des Doppelklickens führen wir eine Stoppuhr durch und sehen, dass diese Berechnung 6,6 Sekunden dauert.

Bei Vlookup in realen Arbeitsblättern liefern viele Menschen die gesamten Spalten, damit TABLE_Array in Zukunft mögliche Ergänzungen erledigt werden soll:

=VLOOKUP(B2, E:F, 2, FALSE)

Beeinflusst es die Leistung? Ja, das tut es. Es dauert 14,2 Sekunden, um die gesamte Spalte zu berechnen. Das war kaum zu glauben, also habe ich noch einmal geprüft. Das gleiche Ergebnis - weniger als die Hälfte der Geschwindigkeit der Bereiche.

Abgesehen davon, dass Sie schnell sind, muss Ihre Formel auch robust und langlebig sein, oder? Bedauerlicherweise kann Vlookup keine Zuverlässigkeit und Belastbarkeit rühmen.

Vlookup -Einschränkungen

Wie bereits erwähnt, hat Excel Vlookup eine Reihe irritierender Einschränkungen. Die wichtigsten sind:

• Kann nicht links schauen. Die Vlookup -Funktion kann nur in der Spalte links des Tabellenarrays schauen und Informationen von rechts zurückgeben.
• Überlebt nicht die Spalteninsertion oder Löschung. Da die Rückgabespalte als Indexnummer angegeben ist, funktioniert eine Vlookup -Formel nicht mehr, sobald eine neue Spalte hinzugefügt oder aus dem Tabellenarray entfernt oder entfernt wird.
• Ein Suchwert ist auf 255 Zeichen begrenzt.

Berechnungszeit : Bereich - 6,6 Sekunden; Ganze Spalten - 14,2 Sekunden.

Tutorial ausführlich : Excel Vlookup-Beispiele für Anfänger

Index Match -Formel

Für viele Benutzer ist eine fortschrittliche Form der Suche in Excel die magische Indexübereinstimmung. In allgemeiner Form sieht es so aus:

Index ( return_column , Match ( Lookup_value , Lookup_Column , 0))

Für unseren Datensatz nimmt die Formel dieses Formular an:

=INDEX($F$2:$F$501, MATCH(B2, $E$2:$E$501, 0))

Wie bei Vlookup denken Sie bitte daran, absolute Referenzen für Such- und Rückkehrbereiche zu verwenden, um sicherzustellen, dass die Formel korrekt in die folgenden Zellen kopiert.

Bei beiden Tabellen auf demselben Blatt zeigte sich die Index -Match viel langsamer als Vlookup (8,9 gegen 6,6 Sekunden).

Wenn wir die Nachschlagtabelle jedoch in ein anderes Arbeitsblatt verschieben, funktioniert die Formel viel schneller (~ 5 Sekunden), was besser ist als Vlookup.

Wie auch immer, das Index Match macht die Zeit mit einer Reihe wichtiger Vorteile.

Index Match -Vorteile

• Kann von rechts nach links schauen. Ja, eine Index -Match -Formel kümmert sich nicht darum, wo sich die Lookup -Spalte befindet, da sie im Gegensatz zu VLOOKUP explizit einen Suchbereich als ein Tabellenarray definiert.
• Immun gegen Säuleneinfügung und Löschung. Mit der Indexübereinstimmung können Sie Spalten sicher hinzufügen und entfernen, da Sie einen Rückkehrbereich und keine Indexnummer angeben.
• Keine Grenze für die Größe eines Suchwerts. Während Vlookup auf 255 Zeichen begrenzt ist, hat die Indexübereinstimmung keine Probleme bei der Verarbeitung längerer Zeichenfolgen.
• Kann vlookup mit mehreren Kriterien durchführen, wie im obigen verknüpften Beispiel gezeigt.
• Kann eine zweidimensionale Suche durchführen und einen Wert an der Schnittstelle einer bestimmten Zeile und Spalte zurückgeben.

Berechnungszeit : Bereiche - 8,9 Sekunden; ganze Spalten - 17,7 Sekunden; Aus einem anderen Blatt - 5,2 Sekunden.

Eingehende Tutorial : Index-Match-Formel in Excel

Offset Match -Formel

Hier ist eine weitere Formel, um vertikal in Excel nachzuschlagen, was frei von vielen Einschränkungen von Vlookup ist:

Offset ( Lookup_table , Match ( Lookup_value , Offset ( Lookup_table , 0, N , Zeilen ( Lookup_table ), 1), 0) -1, M , 1, 1)

Wo:

• N - ist der Suchspaltenversatz, der angibt, wie viele Spalten vom Beginn der Tabelle in die Suchspalte bewegt werden sollen.
• m - ist der Rückset der Rückgabespalte, der bestimmt, wie viele Spalten zur Rückgabespalte wechseln sollen.

In unserem Fall beträgt der Suchspaltenversatz (n) 0, da wir in der ersten Spalte suchen, sodass keine Verschiebung erforderlich ist. Der Rücksetversatz (M) der Rückgabespalte beträgt 1, da sich die Übereinstimmungen in der zweiten Spalte befinden und wir 1 Spalte nach rechts verschieben müssen, um sie zu erreichen:

=OFFSET($E$2:$F$501, MATCH(B2, OFFSET($E$2:$F$501, 0, 0, ROWS($E$2:$F$501), 1), 0) -1, 1, 1, 1)

Im Vergleich zu den vorherigen Lösungen ist die Formel zu umständlich, oder? Es ist jedoch viel schneller als Vlookup oder Index Match. Die Berechnung von 500.000 Zeilen dauert weniger als 3 Sekunden! Bei ganzen Spalten ist der Offset nur etwas langsamer - 3,5 Sekunden.

=OFFSET(E:F, MATCH(B2, OFFSET(E:F, 0, 0, ROWS(E:F), 1), 0) -1, 1, 1, 1)

In Excel 64 -Bit ist das Ergebnis jedoch nicht so beeindruckend - ungefähr 7,5 Sekunden. Warum ist das? Eine gute Frage an Microsoft Guys :)

Offset Match Vorteile

Abgesehen von der Geschwindigkeit hat diese Formel einige andere Verdienste:

• Kann einen Vlookup und einen oberen Hlookup durchführen.
• Kann eine Zwei-Wege-Lookup basierend auf den Spalten- und Zeilenwerten durchführen.
• Bricht nicht, wenn Spalten in die Nachschlagtabelle eingefügt oder gelöscht werden.

Offset Match Nightback

Komplexe Syntax.

Berechnungszeit : Bereiche - 2,9 Sekunden; Ganze Spalten - 3,5 Sekunden.

Eingehende Tutorials :

• Offset -Funktion mit Formel -Beispielen
• So verwenden Sie die Übereinstimmungsfunktion in Excel

Xlookup -Formel

Microsoft 365 -Abonnenten erhalten eine neue und leistungsstärkere Funktion, um Informationen in ihren Arbeitsblättern zu suchen:

Xlookup (Lookup_value, Lookup_array, return_array, [match_mode], [search_mode], [if_not_found]))

Für unseren Zweck funktionieren die Standardeinstellungen der letzten 3 Argumente gut, sodass wir nur die ersten 3 Parameter angeben, die erforderlich sind. Die Namen der Argumente sind intuitiv, und ich glaube, Sie können die Formel ohne zusätzliche Erklärungen verstehen:

=XLOOKUP(B2, $E$2:$E$501, $F$2:$F$501)

Xlookup Vorteile

Im Vergleich zu herkömmlichem VLOOKUP hat die Xlookup -Funktion viele Verbesserungen wie:

• Vereinfachte und aussagekräftigere Syntax
• Fähigkeit, vertikal und horizontal in eine Richtung nachzuschlagen: rechts, links, unten oder nach oben.
• Für sortierte Daten verfügt es über einen speziellen Binär -Suchmodus, der viel schneller ist als die reguläre Suche.
• Suche in umgekehrter Reihenfolge, um das letzte Ereignis zu erhalten.
• Fähigkeit, mehrere Werte zurückzugeben.
• Behandeln Sie mehr als einen Zustand, wie in Excel Xlookup mit mehreren Kriterien erläutert.
• Inbuilt wenn Fehlerfunktionalität.

Xlookup -Nachteile

Xlookup ist nur in Excel 365 und 2021 erhältlich. In Excel 2019, Excel 2016 und früheren Versionen wird es nicht unterstützt.

Und jetzt sehen wir sehen, wie schnell diese neue Funktion ist. 11,2 Sekunden - ziemlich enttäuschend :(

Was ist, wenn wir Spaltenreferenzen anstelle von Bereichen verwenden? =XLOOKUP(B2, E:E, F:F)

24,5 Sekunden. Keine Worte ... fast doppelt so langsam wie Vlookup.

Berechnungszeit : Bereiche - 11,2 Sekunden; Ganze Spalten - 24,1 Sekunden.

Umfassendes Tutorial : Excel Xlookup -Funktion mit Beispielen

Vlookup in Excel -Tischen

Wie Sie wahrscheinlich wissen, können Daten in Excel -Tabellen auf besondere Weise referenziert werden - durch Verwendung von Tabellen- und Spaltennamen anstelle von Zelladressen. Dies wird als strukturierte Referenz bezeichnet, und ich frage mich, ob es sich auf die Berechnungsgeschwindigkeit auswirkt.

Um dies zu überprüfen, konvertieren wir die Bereiche in Tabellen und testen unsere Formeln erneut.

Für den Einfachheit halber werden unsere Tabellen als Main_Table (A1: C500001) und Lookup_table (E1: F5001) ausgezeichnet.

Um eine Tabellenreferenz zu erstellen, tippen Sie die Formel in der ersten Zelle (C2), wählen Sie die Zellen und Bereiche, auf die Sie sich beziehen möchten, und fügt Excel die strukturierten Referenzen automatisch ein.

So sieht die Vlookup -Formel beispielsweise aus:

=VLOOKUP([@Item], Lookup_table, 2, FALSE)

Eine großartige Merkmale von Excel -Tabellen ist, dass sie in nur einer Zelle sofort in allen anderen Zellen in derselben Spalte in eine Formel eintreten. Außerdem sind Tabellen von Natur aus dynamisch und erweitern sich automatisch um neue Daten, die Sie neben einer Tabelle eingeben.

In unserer Tabelle wurde die Vlookup -Formel in 2,3 Sekunden, die Indexübereinstimmung in 2,6 Sekunden, den Versatz und die Übereinstimmung in 2,7 Sekunden und Xlookup in 3,3 Sekunden berechnet. Wie Sie sehen, steigt die Berechnungsgeschwindigkeit im Vergleich zu den Bereichen erheblich an.

Die Formeln sind unten als Referenz aufgeführt:

=INDEX(Lookup_table[Price], MATCH([@Item], Lookup_table[Item], 0))

=OFFSET(Lookup_table, MATCH([@Item], OFFSET(Lookup_table, 0, 0, ROWS(Lookup_table), 1), 0) -1, 1, 1, 1)

=XLOOKUP([@Item], Lookup_table[Item], Lookup_table[Price])

Interessanterweise sind Excel -Tabellen selbst bei regelmäßigen Referenzen sehr schnell. Wenn Sie nur den ersten Bereich (A1: C500001) in eine Tabelle konvertieren und eine normale Vlookup -Formel verwenden, um Daten aus dem Suchbereich zu ziehen, wird die gesamte Spalte in der Haupttabelle in etwa 2,5 Sekunden berechnet!

Berechnungsgeschwindigkeit : je nach Formel von 2,3 bis 3,3 Sekunden.

End-to-End-Tutorials :

• Excel -Tabelle mit Beispielen
• Strukturierte Referenzen in Excel -Tabellen

Vlookup mit dynamischen Arrays

Die bahnbrechende Änderung der Excel 365-Berechnungsmotor, die im Januar 2020 auftrat, hat die sogenannten dynamischen Arrays unterstützt. Kurz gesagt, es handelt sich um resizbare Arrays, die automatisch berechnet und Werte in mehreren Zellen zurückgeben, basierend auf einer in einer Zelle eingegebenen Formel.

Eines der besten Dinge an dynamischen Arrays ist, dass sie mit fast jeder herkömmlichen Excel -Funktion verwendet werden können. Für unsere Vlookup -Formel wird sie so aussehen:

=VLOOKUP(B2:B500001, E2:F500001, 2, FALSE)

Der Unterschied zur klassischen Vlookup -Funktion besteht darin, dass Sie das gesamte Lookup -Array für das erste Argument liefern, nicht für einen einzigen Suchwert. Da die Formel nur in einer Zelle eingegeben wird, müssen Sie sich keine Sorgen machen, die Bereiche mit absoluten Referenzen zu sperren.

In Bezug auf die Leistung funktionieren dynamische Arrays noch schneller als Excel -Tische! Eine halbe Million Zellen sind fast sofort mit den Ergebnissen gefüllt: 1,8 Sekunden - sehr beeindruckend!

Weitere Ergebnisse sind unten aufgeführt:

Index Match - 4,4 Sekunden

=INDEX(F2:F501, MATCH(B2:B500001, E2:E501, 0))

Xlookup - 7,3 Sekunden

=XLOOKUP(B2:B500001, E2:E501, F2:F501)

Hmm… Xlookup, das durch Design dynamisch sein soll, funktioniert schlechter als die älteren Funktionen. Seltsam!

Berechnungsgeschwindigkeit : je nach Formel von 1,8 bis 7,3 Sekunden.

Tutorial ausführlich : Excel-dynamische Arrays, Funktionen und Formeln

Übereinstimmungen mit Power -Abfrage ziehen

Lassen Sie uns aus Gründen der Vollständigkeit eine weitere mögliche Lösung für unsere Aufgabe testen - Power -Abfrage. Natürlich ist es nicht richtig, die Berechnung von Formeln mit der Aktualisierung der Abfrage zu vergleichen, aber ich bin nur neugierig, was schneller ist :)

Die detaillierten Schritte zur Verwendung von Power -Abfragen werden in einem separaten Tutorial beschrieben, das unten erwähnt wurde. Hier werden wir nur das Ergebnis bewerten:

Die fusionierte Tabelle wurde in 8,5 Sekunden aus dem Power Query -Editor in Excel geladen. Im Gegensatz zu Formeln aktualisieren Abfragen nicht automatisch. Nach jeder Änderung in den Quelldaten müssen Sie die resultierende Tabelle manuell aktualisieren, indem Sie auf die Registerkarte Daten oder Abfrage auf die Schaltfläche Aktualisierung klicken. Unsere 500.000 Zeilen werden in etwa 7 Sekunden aktualisiert. Nicht schlecht, aber Excel -Formeln können es besser machen. In Anbetracht der Tatsache, dass das Einrichten einer Abfrage kein Ein-Klick-Prozess ist, ist dies wahrscheinlich die letzte Methode, die ich anwenden würde, nur wenn nichts anderes funktioniert.

Leistung : Laden auf 8,5 Sekunden; 7,6 Sekunden erfrischen

Eingehende Tutorials :

• So kombinieren Sie Tabellen mit Excel Power -Abfrage
• So verwenden Sie Power -Abfragen in Excel - Praktische Beispiele
• Power -Abfrage im Excel -Tutorial für Anfänger

Zusätzlicher Bonus: Zauberer des Tische verschmelzen

Die Benutzer unserer Ultimate Suite verfügen über ein weiteres Tool in ihrer Excel -Toolbox, um zwei Tabellen auf der Grundlage einer gemeinsamen Spalte zusammenzuführen. Mal sehen, wie es sich mit Excels vergleicht.

Klicken Sie auf der Registerkarte "Den Leerbits" auf die Schaltfläche "Merge Tabellen" auf die Schaltfläche " Zwei Tabellen" zusammen. Und dann befolgen Sie einfach die Schritte des Assistenten, und es wird Sie durch den Prozess führen.

Nun, das Werkzeug dauerte ungefähr 3 Sekunden. Nicht so schlecht für eine halbe Million Rekorde!

Wenn Sie sich die obige Nachricht genauer ansehen, können Sie feststellen, dass nicht alle Übereinstimmungen gefunden wurden. Es bedeutet jedoch nicht, dass das Werkzeug fehlerhaft ist. Sie können nur wissen, dass einige Elemente (Suchwerte) in der Nachschlagtabelle nicht vorhanden sind. Die VLOOKUP -Funktion gibt in diesem Fall einen #n/a -Fehler zurück, während der Assistent der Merge -Tabellen eine Zelle leer lässt.

Leistung : 3,2 Sekunden

Weitere Informationen : In Excel zwei Tabellen verschmelzen

Zusammenfassung und Schlussfolgerungen

Wenn Sie jedoch alle Beispiele sorgfältig lesen, haben Sie höchstwahrscheinlich bereits Ihre eigenen Schlussfolgerungen gezogen. Wenn Sie die Details übersprungen haben, finden Sie in dieser Vergleichstabelle eine kurze Zusammenfassung:

Funktion	Berechnungsgeschwindigkeit in Sekunden
	Bereiche	Ganze Spalten	Tisch	Dynamische Arrays
Vlookup	6.6	14.2	2.3	1.8
Indexübereinstimmung	8.9	17.7	2.6	4.4
Offset Match	2.9	3.5	2.7	- -
Xlookup	11.2	24.1	3.3	7.3
Power -Abfrage	8.5
Tische verschmelzen	3.2

Im Folgenden gibt es einige Beobachtungen, die ich basierend auf den Testergebnissen gemacht habe. Vielleicht werden sie auch für Sie nützlich sein.

• Trotz aller Einschränkungen und Nachteile spielt Vlookup insbesondere bei dynamischen Arrays recht gut.
• Das Indexmatch ist nicht so schnell wie erwartet. Für mich scheint dies sehr seltsam, weil es einzelne Spalten verarbeitet, nicht ein Tabellenarray wie Vlookup.
• Xlookup verfügt über viele erstaunliche Funktionen, ist aber langsamer als Vlookup- und Indexübereinstimmung bei riesigen Datensätzen. Hoffentlich wird Microsoft seine Leistung in den zukünftigen Versionen verbessern.
• Das Offset-Match ist das schnellste in Excel 32-Bit. Aber wegen seiner komplexen Syntax gibt es eine große Chance, einen Fehler zu machen. Außerdem funktioniert es nicht mit dynamischem Array, zumindest konnte ich es nicht zwingen.
• Es gibt keinen Sinn, ganze Spalten zu berechnen, es sei denn, dies ist unbedingt erforderlich. Dies macht die Formeln mehr als zweimal langsamer.
• Excel Tische Rock! Um das Beste aus Ihrem Excel zu nutzen, verwenden Sie sie, wo immer möglich.
• Dynamische Arrays sind die Zukunft.

Bitte beachten Sie, dass diese Beobachtungen auf meinen Tests in Dynamic Excel 365 basieren. Ich hatte keine Gelegenheit, in anderen Versionen zu testen. Wenn Sie es getan haben, sind Ihre Kommentare willkommen und werden sehr geschätzt!

Üben Sie die Arbeitsmappe zum Download

Schnellste Vlookup -Formel in Excel (.xlsx -Datei, 74 MB )

Das obige ist der detaillierte Inhalt vonVlookup in Excel - Welche Formel ist am schnellsten?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!