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

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!