Wenn Sie ein großes Arbeitsblatt haben, können viele VLOOKUPs anfangen, die Dinge zu verlangsamen. Haben Sie aufgrund von VLOOKUP ein langsames Arbeitsblatt? Ich spreche von einem Arbeitsblatt, dessen Berechnung 40 Sekunden oder 4 Minuten dauert. Im heutigen Artikel wird eine erstaunliche Formel mit zwei VLOOKUPs, die die Bereichssuche verwenden, das Problem lösen.
VLOOKUP ist eine relativ teure Funktion. Wenn Sie nach einer genauen Übereinstimmung suchen, muss Excel die Nachschlagetabelle zeilenweise durchsuchen.
Die Arbeitsmappe, die ich heute verwende, führt 7000 VLOOKUPs in einer Tabelle mit 116.000 Elementen aus. Auf einem sehr schnellen 64-Bit-Computer mit 8 Kernen beträgt die Neuberechnungszeit 3,01 Sekunden.
Eine Möglichkeit, VLOOKUP zu verbessern, besteht darin, die meistverkauften Artikel an die Spitze der Nachschlagetabelle zu verschieben. Holen Sie sich einen Bericht über die 100 meistverkauften Artikel und verschieben Sie diese Artikel an den Anfang der Liste. Durch Sortieren nach Beliebtheit wird die Neuberechnungszeit auf 0,369 Sekunden verbessert. Dies ist achtmal schneller als das erste Ergebnis.
Aber es gibt eine Möglichkeit, die Dinge noch schneller zu machen. Wenn Sie beim Erstellen Ihres VLOOKUP zum vierten Argument für die Auswahl von False gelangen, gibt es eine andere Option, die kaum jemals verwendet wird. Excel sagt, dass "True" eine "ungefähre Übereinstimmung" ergibt. Das ist überhaupt nicht richtig. Wenn das Excel-Team ehrlich wäre, würde es erklären, dass True „oft die richtige Antwort liefert, aber manchmal werden wir ohne Vorwarnung die falsche Antwort einfügen. Ich hoffe, es macht Ihnen nichts aus, Ihre Zahlen an die Securities and Exchange Commission weiterzuleiten. “
Sicher, es gibt einen richtigen Zeitpunkt, um True zu verwenden. Siehe diesen Artikel. Aber es wäre wirklich schlecht, True zu verwenden, wenn Sie versuchen, eine genaue Übereinstimmung zu erzielen.
Wenn Sie versuchen, True für eine genaue Übereinstimmung zu verwenden, erhalten Sie häufig die richtige Antwort. Wenn sich das gesuchte Element jedoch nicht in der Tabelle befindet, gibt Excel Ihnen den Wert aus einer anderen Zeile. Dies ist der Teil, der „True“ zu einem Nichtstarter für alle im Rechnungswesen macht. Schließen ist im Rechnungswesen niemals korrekt.
Hinweis
Ich habe den folgenden Trick von Charles Williams gelernt. Er ist der weltweit führende Experte für Arbeitsblattgeschwindigkeit. Wenn Sie ein langsames Arbeitsbuch haben, beauftragen Sie Charles Williams mit einem halben Beratungstag. Er kann die Engpässe finden und Ihr Arbeitsblatt schneller machen. Finden Sie Charles unter http://www.decisionmodels.com.
Während ich und alle Buchhalter das „wahre“ Argument von VLOOKUP wegen der Unvorhersehbarkeit ablehnen, argumentiert Charles Williams für wahr. Er weist darauf hin, dass das Wahre viel schneller ist als das Falsche. Hunderte Male schneller. Er räumt ein, dass man manchmal die falsche Antwort bekommt. Aber er hat eine Möglichkeit, mit den falschen Antworten umzugehen.
Charles möchte eigentlich, dass du zwei VLOOKUPs machst. Führen Sie zunächst einen VLOOKUP durch und geben Sie Spalte 1 aus der Tabelle zurück. Überprüfen Sie, ob das Ergebnis das ist, wonach Sie zuerst gesucht haben. Wenn dieses Ergebnis übereinstimmt, wissen Sie, dass es sicher ist, den echten VLOOKUP auszuführen, um eine andere Spalte aus der Tabelle zurückzugeben:
=IF(VLOOKUP(A2,Table,1,True)=A2,"All is good","The Answer will be wrong")
Auf den ersten Blick scheint dies verrückt zu sein. Um Charles 'Methode zu verwenden, müssen Sie doppelt so viele VLOOKUPs ausführen. Wenn Sie jedoch die Berechnungszeit für diese Methode zeitlich festlegen, ist sie 35-mal schneller als der normale VLOOKUP.
Beachten Sie, dass die meisten Nachschlagetabellen nicht sortiert werden müssen. Wenn Sie jedoch True als viertes Argument verwenden, muss die Tabelle sortiert werden. Eine 7-minütige Diskussion darüber, wie die True-Version von VLOOKUP durch die Nachschlagetabelle springt, finden Sie unter http://mrx.cl/TrueVLOOKUP.
Vielen Dank an Charles Williams, der mir dieses Feature beigebracht hat, und an Scott St. Amant, der es für einen Top-40-Tipp nominiert hat.
Schau Video
- VLOOKUP ist bei Verwendung mit False eine langsame Funktion
- Das Sortieren der Daten AZ beschleunigt die Funktion nicht
- Das Sortieren nach Beliebtheit kann die Funktion beschleunigen
- Das Wechseln zu VLOOKUP mit True ist schneller, es wird jedoch die falsche Antwort gemeldet, wenn das Element nicht gefunden wird
- Um das Problem zu beheben, führen Sie einen VLOOKUP (A2, Tabelle, 1, True) durch, um festzustellen, ob das Ergebnis zuerst A2 ist
- 14000 VLOOKUP (True) und 7000 IF laufen schneller als 7000 VLOOKUP (False)
Automatisch generiertes Transkript
- Lernen Sie Excel aus dem Podcast
- Folge 2031 schneller vlookup Ich bin
- Podcasting aller Tipps in diesem Buch
- Klicken Sie auf das I in der oberen rechten Ecke
- um zur Beobachtungsliste zu gelangen
- hey willkommen zurück im mr. Hutnik Besetzung
- Ich bin Bill Jelen. Ich habe das getan
- Video, bevor es eines meiner Favoriten ist
- Tricks, wenn Sie den Blick haben, wenn Sie
- habe vlookup star 30 40 50 nehmen
- Sekunden vier Minuten wissen Sie nichts
- Du wirst dieses Video lieben, wenn du
- vlookup stick eine sekunde einfach auf Weiter klicken
- und weiter zum nächsten Video II habe eine
- vlookup hier schaut es in einen Tisch
- von 115.000 Artikeln, die 7000 vlookup so tun
- Wir werden Charles Williams benutzen
- von schnellem Excel-Code, um zu sehen, wie lange es dauert
- dauert, um diese Ansicht in Ordnung vier zu tun
- Punkt Null neun Sekunden das ist die
- typisches vlookup mit Komma false am
- Ende und das alles kam da lange auf
- Vor langer Zeit wurde ich von einem Typen angezogen
- Twitter, der sagte, das wäre besser, wenn
- Sie würden Ihre Nachschlagetabelle sortieren a
- Senden Ich sagte nein, das ist überhaupt nicht wahr
- es ist egal ob wir a gehen
- Senden oder Absteigen oder vollständig
- zufällig muss das vlookup nur schauen gehen
- von Artikel zu Artikel zu Artikel und so, wenn wir
- Sortieren Sie die Tabelle, um zu sehen, ob es tatsächlich dauert
- länger vier Punkt acht vier Sekunden so
- Sie wissen, dass es nicht stimmt, das zu sortieren
- Der Tisch wird es aber schneller machen
- wirklich das, was es schaffen könnte
- schneller, wenn Sie irgendwie sortieren könnten
- Popularität, wenn Sie das Beste bekommen könnten
- Verkauf von Artikeln ganz oben auf der Liste
- Sogar du kennst deine Top fünfzig, die du kennst
- Was sind Ihre 50 meistverkauften Artikel?
- Bringen Sie diese an den Anfang der Liste und
- Beobachten Sie, dass für Sekunden auf 0,36 sinkt
- Sekunden eine zehnfache Verbesserung der Zeit
- mit sort nach Popularität jetzt hey ein paar
- Vor Jahren hatte ich das Glück zu sein
- nach Amsterdam eingeladen, um auf einer zu präsentieren
- Excel-Gipfel dort und es ist nicht so
- Die meisten meiner Seminare sind nur ich
- genau da waren zwei spuren also raum a
- und Raum B und ich waren drüben in Raum sein
- Sprechen über Lookups und über im Raum
- eine Vermutung, wer in diesem Raum saß
- war Charles Williams in Ordnung und Charles
- hier ist
- sein Name wird durch die erwähnt
- Wand, also kommt er herüber, um es ihm anzusehen
- schaut mir dort meine kleine Demo an, wohin ich gehe
- von vier Sekunden bis 0,36 Sekunden er
- kommt danach auf mich zu und sagt, ich wette
- du bist ziemlich glücklich damit
- Verbesserung
- Ich sage ja, das ist ein Zelt voll
- Verbesserung jetzt hat Charles Charles die
- Service von schnellem Excel unser Entscheidungsmodell
- Die Entscheidungsmodelle sind begrenzt
- einen halben Tag wird er Ihre Arbeitsmappe analysieren
- und er behauptet, macht es hundert
- mal schneller richtig findet er das
- Engpässe Annette und Charles Charles
- kommt von er sagt schau auf komma falsch
- dass Sie und Ihre Buchhalter Freunde sind
- Dies zu tun ist die langsamste Sache in Excel
- Wenn Sie ein wahres Komma machen würden, wäre es ein
- tausendmal schneller und dann Charles
- sagt diese nächste Klausel ist, wenn es nicht ist
- wirklich wichtig, sagt er jetzt manchmal ist es
- falsch oh warte Charles du nicht
- Verstehe, dass ein Buchhalter manchmal ist
- falsch ist ein Nichtstarter, den wir nicht akzeptieren
- manchmal ist es falsch und und die Zeit
- dass es falsch ist das Komma wahr wenn
- Du machst ein Komma wahr, wir gehen und schauen
- für ein P 3 2 2 1 1 und es wird nicht gefunden
- Sie geben dir nur den Gegenstand
- weniger in Ordnung und sie werden es nicht sagen
- Sie konnten es nicht finden, sie sind gerecht
- werden sie dir nur Adam geben
- nur weniger, dass das inakzeptabel ist und
- Charles sagt gut, hier ist was wir könnten
- Stellen Sie sich vor, Sie hätten einen Blick auf P 3 2 geworfen
- 2 1 1 into just column G ask for the
- first column comma true and see if what
- you get back is what you were looking
- for if what you get back is what you're
- looking for then you know it's safe to
- go to the second vlookup if it's not
- what you were looking for then you have
- an if statement there that says not
- found alright so we do a vlookup of a2
- into the table comma 1 see if it's equal
- to 82 if it is then it's safe to go on
- and do the second vlookup otherwise they
- not found I said Charles do you realize
- I'm doing 7000 vlookups and now you're
- gonna be doing 7 14000 vlookups and 7000
- if statements I I said you really think
- this is gonna be faster sure I said well
- I'll bet you a pint alright so here we
- go remember the vlookup that all of us
- are doing with the comma falls 4.0 9
- seconds the sort by popularity which is
- kind of hard to do
- Oh point three seconds here we go here's
- Charles Williams are you ready look at
- that point zero four four one hundredths
- of a second from four seconds down to
- four one hundreds of a second imagine if
- you had a spreadsheet that was taking
- for 40 seconds to calculate and how much
- faster would be using this to vlookup
- method it's an amazing trick and yeah I
- guess I stole the trick and put it in
- the book although I put a great cartoon
- version of Charles Williams in there
- saying he's the fastest guy in all of
- Excel you can buy this tip and all of
- the other tips in this book click that I
- at the top right hand corner
- all right so recap vlookup when used
- with false it's a slow function sorting
- the data a disease does not speed up the
- function unless you sell a lot of things
- to begin with a and B sorting by
- popularity does it's about a tenfold
- erhöhen Sie könnten zu vlookup wechseln
- mit wahr, aber es wird das Falsche melden
- antworte, wenn die Gegenstände nicht gefunden wurden, also sind wir
- Ich werde tatsächlich zwei Lookups durchführen
- zwei in der Spalte eins am Tisch und
- Sehen Sie, ob es das ist, was wir zurückbekommen, wenn es zwei sind
- Es ist sicher, die Suche in zu machen
- die gemeinsame Spalte, um sonst eine zu haben
- if Aussage heißt es nicht in Ordnung gefunden
- oh hey danke an Charles Williams für
- lehre mich diesen erstaunlichen Trick und
- Vielen Dank für Ihren Besuch
- Sie das nächste Mal für eine andere Netzbesetzung aus
- MrExcel
Download-Datei
Laden Sie die Beispieldatei hier herunter: Podcast2031.xlsm
