Om du har ett stort kalkylblad kan många VLOOKUPs börja sakta ner saker. Har du ett långsamt kalkylblad på grund av VLOOKUP? Jag pratar om ett kalkylblad som tar 40 sekunder eller 4 minuter att beräkna. I dagens artikel kommer en fantastisk formel med två VLOOKUPs som använder intervalluppslagningen att lösa problemet.
VLOOKUP är en relativt dyr funktion. När du letar efter en exakt matchning måste Excel titta igenom uppslagstabellen en rad i taget.
Arbetsboken som jag använder idag gör 7000 VLOOKUPs i en tabell med 116.000 artiklar. På en riktigt snabb 64-bitars maskin med åtta kärnor är återberäkningstiden 3,01 sekunder.
Ett sätt att förbättra VLOOKUP är att flytta de bästsäljande artiklarna till toppen av uppslagstabellen. Få en rapport om de 100 bästa bästsäljande artiklarna och flytta dessa till toppen av listan. Sortering efter popularitet förbättrar omräkningstiden till 0,369 sekunder. Detta är åtta gånger snabbare än det första resultatet.
Men det finns ett sätt att snabba upp saker ännu mer. När du bygger din VLOOKUP, när du kommer till det fjärde argumentet för att välja False, finns det ett annat alternativ som nästan aldrig används. Excel säger "True" gör en "ungefärlig matchning." Detta är inte alls korrekt. Om Excel-teamet var ärligt skulle de förklara att True “ger ett korrekt svar mycket tid, men andra gånger, utan någon varning, kommer vi att släppa fel svar där inne. Jag hoppas att du inte har något emot att återställa dina siffror till Securities and Exchange Commission. ”
Visst, det finns rätt tid att använda True. Se den här artikeln. Men det skulle vara riktigt dåligt att använda True när du försöker göra en exakt matchning.
Om du försöker använda True för en exakt matchning får du rätt svar mycket tid. Men när objektet du letar efter inte finns i tabellen kommer Excel att ge dig värdet från en annan rad. Det här är den del som gör “True” till en icke-starter för alla inom redovisning. Stängning är aldrig korrekt i bokföring.
Notera
Jag lärde mig följande trick från Charles Williams. Han är världens främsta expert på kalkylhastighet. Om du har en långsam arbetsbok anställer du Charles Williams för en halv dags konsultation. Han kan hitta flaskhalsarna och göra ditt kalkylblad snabbare. Hitta Charles på http://www.decisionmodels.com.
Medan jag och alla revisorer avvisar VLOOKUP: s ”Sanna” argument på grund av oförutsägbarheten, argumenterar Charles Williams för True. Han påpekar att den sanna är mycket snabbare än falsk. Hundratals gånger snabbare. Han medger att du ibland får fel svar. Men han har ett sätt att hantera fel svar.
Charles vill faktiskt att du gör två VLOOKUPs. Först gör du en VLOOKUP och returnerar kolumn 1 från tabellen. Se om resultatet är det du letade upp i första hand. Om resultatet matchar, vet du att det är säkert att göra den verkliga VLOOKUP för att returnera någon annan kolumn från tabellen:
=IF(VLOOKUP(A2,Table,1,True)=A2,"All is good","The Answer will be wrong")
På det hela taget verkar detta galet. För att använda Charles metod måste du göra dubbelt så många VLOOKUP. Men när du beräknar tiden för denna metod är den 35 gånger snabbare än den vanliga VLOOKUP.
Observera att även om de flesta uppslagstabeller inte behöver sorteras, måste du sortera tabellen när du använder True som det fjärde argumentet. För en 7-minuters diskussion om hur den sanna versionen av VLOOKUP hoppar genom uppslagstabellen, se http://mrx.cl/TrueVLOOKUP.
Tack till Charles Williams för att han lärde mig den här funktionen och till Scott St. Amant för att ha nominerat den till ett topp 40-tips.
Kolla på video
- VLOOKUP när det används med False är en långsam funktion
- Sortering av data AZ påskyndar inte funktionen
- Sortering efter popularitet kan påskynda funktionen
- Att byta till VLOOKUP med True är snabbare, men det kommer att rapportera fel svar om objektet inte hittas
- För att mildra problemet, gör en VLOOKUP (A2, Tabell, 1, True) för att se om resultatet är A2 först
- 14000 VLOOKUP (True) och 7000 IF kör snabbare än 7000 VLOOKUP (False)
Autogenererat avskrift
- Lär dig Excel från Podcast
- avsnitt 2031 snabbare vlookup Jag är
- podcasting av alla tips i den här boken
- klicka på jag i det övre högra hörnet
- för att komma till bevakningslistan
- hej välkommen tillbaka till herr. hutnik-gjutning
- Jag heter Bill Jelen. Jag har gjort det här
- video innan det är en av mina favoriter
- knep om du har blicken om du
- har vlookup-stjärna som tar 30 40 50
- sekunder fyra minuter vet du någonting
- du kommer att älska den här videon om din
- vlookup stick en sekund, klicka bara på Nästa
- och fortsätt till nästa video II har en
- vlookup här det tittar in i ett bord
- av 115 000 objekt som gör 7000 vlookup så
- vi ska använda Charles Williams
- från snabb Excel-kod för att se hur lång den är
- tar för att göra detta vlookup okej fyra
- punkt noll nio sekunder är det
- typisk vlookup med komma falsk vid
- slutet och allt detta kom för länge
- för länge sedan blev jag betad av någon kille på
- Twitter som sa att det skulle vara bättre om
- du skulle sortera din uppslagstabell a
- skicka sa jag nej det stämmer inte alls
- det spelar ingen roll om vi går a
- sändande eller fallande eller helt
- slumpmässigt måste vlookup bara titta
- från artikel till artikel och så när vi
- sortera tabellen se det faktiskt tar
- längre fyra poäng åtta fyra sekunder så
- du vet att det inte är sant att sortera
- bordet gör att det går snabbare men
- verkligen det som kan få det att gå
- snabbare om du på något sätt kunde sortera efter
- popularitet om du kunde få det bästa
- sälja föremål högst upp på listan
- även du känner till dina femtio bästa vet du
- vad dina 50 bästa bästsäljande artiklar är
- ta dem till toppen av listan och
- titta på att i sekunder går ner till 0,36
- sekunder en tiofaldig förbättring av tiden
- använder sortera efter popularitet nu hej några
- för år sedan hade jag turen att vara det
- inbjuden till Amsterdam för att presentera vid en
- Excel toppmöte där och det är inte som
- de flesta av mina seminarier där det bara är jag
- precis fanns det två spår så rum a
- och rum B och jag var över i rummet vara
- pratar om vlookups och över i rummet
- en gissning vem som satt i det rummet det
- var Charles Williams okej och Charles
- här är
- hans namn nämns genom
- väggen så att han kommer över för att titta på den han
- tittar på min lilla demo där jag går
- från fyra sekunder till 0,36 sekunder han
- kommer fram till mig efteråt säger han att jag slår vad
- du är ganska nöjd med det
- förbättring
- Jag säger ja det är ett tält fullt
- förbättring nu Charles Charles har
- service av snabb Excel vår beslutsmodell
- beslutsmodellerna begränsade vi är i
- en halv dag analyserar han din arbetsbok
- och han hävdar gör det till hundra
- gånger snabbare kommer han att hitta
- flaskhalsar Annette och Charles Charles
- kommer från han säger titta på komma falskt
- att du och dina revisorsvänner är
- att göra det är det långsammaste i Excel
- om du skulle göra ett komma sant är det en
- tusen gånger snabbare och sedan Charles
- säger detta nästa klausul är om det inte gör det
- spelar ingen roll han säger nu ibland är det
- fel Åh vänta Charles du inte
- förstår en revisor ibland är
- fel är en icke-starter vi inte accepterar
- ibland är det fel och och tiden
- att det är fel kommatecknet när
- du gör ett komma sant om vi ska titta
- för en P 3 2 2 1 1 och den hittades inte
- de kommer bara att ge dig artikeln
- mindre okej och de kommer inte att berätta
- du vi kunde inte hitta det de är bara
- de kommer bara att ge dig Adam
- bara mindre att det är oacceptabelt och
- Charles säger bra här är vad vi kunde
- föreställ dig om du gjorde en genomgång av P 3 2
- 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
- öka kan du växla till vlookup
- med sant men det kommer att rapportera fel
- svara om objekten inte hittades så vi är
- faktiskt kommer att göra två vlookups-uppslag a
- två i kolumnen en vid bordet och
- se om det vad vi får tillbaka är ett två om
- det är säkert att gå in i flockupen
- den gemensamma kolumnen för att annars ha en
- om uttalande det står inte hittat okej
- åh hej tack till Charles Williams för
- lär mig det fantastiska tricket och
- tack till dig för att du stannade förbi kommer att se
- nästa gång för en ny nätbesättning från
- MrExcel
Nedladdning fil
Ladda ner exempelfilen här: Podcast2031.xlsm
