Hvis du har et stort regneark, kan mange VLOOKUP'er begynde at bremse tingene. Har du et langsomt regneark på grund af VLOOKUP? Jeg taler om et regneark, der tager 40 sekunder eller 4 minutter at beregne. I dagens artikel vil en fantastisk formel med to VLOOKUP'er, der bruger rækkeviddeopslaget, løse problemet.
VLOOKUP er en relativt dyr funktion. Når du leder efter et nøjagtigt match, skal Excel se gennem opslagstabellen en række ad gangen.
Den arbejdsbog, som jeg bruger i dag, udfører 7000 VLOOKUP'er i en tabel med 116.000 genstande. På en rigtig hurtig 64-bit maskine med 8 kerner er genberegningstiden 3,01 sekunder.
En måde at forbedre VLOOKUP på er at flytte de bedst sælgende varer til toppen af opslagstabellen. Få en rapport over de 100 mest solgte varer, og flyt disse varer til toppen af listen. Sortering efter popularitet forbedrer genberegningstiden til 0,369 sekunder. Dette er otte gange hurtigere end det første resultat.
Men der er en måde at fremskynde tingene endnu mere på. Når du bygger din VLOOKUP, er der en anden mulighed, der næsten aldrig er brugt, når du kommer til det fjerde argument for at vælge False. Excel siger, at "Sandt" matcher "omtrentligt." Dette er slet ikke korrekt. Hvis Excel-teamet var ærligt, ville de forklare, at True “giver et korrekt svar meget tid, men andre gange uden nogen advarsel vil vi give det forkerte svar derinde. Jeg håber, at du ikke har noget imod at videresende dine numre til Securities and Exchange Commission. ”
Sikker på, der er et passende tidspunkt at bruge True. Se denne artikel. Men det ville være rigtig dårligt at bruge True, når du prøver at lave en nøjagtig matchning.
Hvis du prøver at bruge True til et nøjagtigt match, får du det rigtige svar meget af tiden. Men når det emne, du leder efter, ikke findes i tabellen, giver Excel dig værdien fra en anden række. Dette er den del, der gør "True" til en ikke-starter for alle inden for regnskab. Lukning er aldrig korrekt i Regnskab.
Bemærk
Jeg lærte følgende trick fra Charles Williams. Han er verdens førende ekspert på regnearkshastighed. Hvis du har en langsom arbejdsbog, skal du ansætte Charles Williams til en halv dags konsultation. Han kan finde flaskehalse og gøre dit regneark hurtigere. Find Charles på http://www.decisionmodels.com.
Mens jeg og alle revisorer afviser VLOOKUP's "sande" argument på grund af uforudsigeligheden, argumenterer Charles Williams for True. Han påpeger, at den sande er meget hurtigere end falsk. Hundreder af gange hurtigere. Han indrømmer, at du nogle gange får det forkerte svar. Men han har en måde at håndtere de forkerte svar på.
Charles vil faktisk have dig til at lave to VLOOKUP'er. Først skal du lave en VLOOKUP og returnere kolonne 1 fra tabellen. Se om resultatet i første omgang er, hvad du så op. Hvis dette resultat stemmer overens, ved du, at det er sikkert at gøre den rigtige VLOOKUP for at returnere en anden kolonne fra tabellen:
=IF(VLOOKUP(A2,Table,1,True)=A2,"All is good","The Answer will be wrong")
Tilsyneladende virker det sindssygt. For at bruge Charles 'metode skal du gøre dobbelt så mange VLOOKUP'er. Men når du tidsberegner beregningstiden for denne metode, er den 35 gange hurtigere end den normale VLOOKUP.
Bemærk, at mens de fleste opslagstabeller ikke behøver at blive sorteret, skal du sortere tabellen, når du bruger True som det fjerde argument. For en 7-minutters diskussion af, hvordan den sande version af VLOOKUP springer gennem opslagstabellen, se http://mrx.cl/TrueVLOOKUP.
Tak til Charles Williams for at lære mig denne funktion og til Scott St. Amant for at have nomineret den til et top 40-tip.
Se video
- VLOOKUP, når det bruges med False, er en langsom funktion
- Sortering af data AZ fremskynder ikke funktionen
- Sortering efter popularitet kan fremskynde funktionen
- Det er hurtigere at skifte til VLOOKUP med True, men det rapporterer det forkerte svar, hvis varen ikke findes
- For at mindske problemet skal du gøre en VLOOKUP (A2, tabel, 1, sand) for at se, om resultatet først er A2
- 14000 VLOOKUP (True) og 7000 IF kører hurtigere end 7000 VLOOKUP (False)
Autogenereret udskrift
- Lær Excel fra Podcast
- afsnit 2031 hurtigere vlookup Jeg er
- podcasting af alle tipene i denne bog
- klik på I i øverste højre hjørne
- for at komme til overvågningslisten
- hej velkommen tilbage til hr. hutnik støbt
- Jeg er Bill Jelen, jeg har gjort dette her
- video, før det er en af mine favoritter
- tricks, hvis du har et blik, hvis du
- har vlookup-stjerne, der tager 30 40 50
- sekunder fire minutter ved du noget
- du vil elske denne video, hvis din
- vlookup stick et sekund skal du bare klikke på Næste
- og gå videre til næste video II har en
- vlookup her ser det ind i et bord
- af 115.000 genstande, der gør 7000 vlookup så
- vi bruger nogle Charles Williams
- fra hurtig Excel-kode for at se, hvor længe den er
- tager at gøre dette vlookup okay fire
- punkt nul ni sekunder, det er det
- typisk vlookup med komma falsk ved
- ende og alt dette kom op for længe
- for længe siden blev jeg agnet af en fyr på
- Twitter, der sagde, at det ville være bedre, hvis
- du ville sortere din opslagstabel a
- sendte sagde jeg nej, det er slet ikke sandt
- det betyder ikke noget, om vi går en
- afsendelse eller faldende eller helt
- tilfældigt skal vlookup bare kigge
- fra vare til vare til vare, og så når vi
- sorter tabellen, se det faktisk tager
- længere fire punkt otte fire sekunder så
- du ved, det er ikke sandt, at sortering af
- tabellen får det til at gå hurtigere, men
- virkelig den ting, der kunne få det til at gå
- hurtigere, hvis du på en eller anden måde kunne sortere efter
- popularitet, hvis du kunne få det bedste
- sælger varer øverst på listen
- selv du kender din top halvtreds du kender
- hvad dine top 50 bedst sælgende varer er
- bringe dem til toppen af listen og
- se, at i sekunder går ned til 0,36
- sekunder en tidoblet forbedring af tiden
- bruger sorter efter popularitet nu hej et par
- for år siden var jeg så heldig at være
- opfordret til Amsterdam for at præsentere ved en
- Excel-topmøde der, og det er ikke som
- de fleste af mine seminarer, hvor det bare er mig
- lige der var to spor så rum a
- og værelse B og jeg var forbi i værelset være
- taler om vlookups og over i rummet
- et gæt hvem der sad i det rum det
- var Charles Williams okay og Charles
- her er
- hans navn bliver nævnt gennem
- væg, så han kommer hen for at se det han
- ser min lille demo der, hvor jeg går
- fra fire sekunder til 0,36 sekunder han
- kommer op til mig bagefter siger han, at jeg vedder
- du er ret tilfreds med det
- forbedring
- Jeg siger ja, det er et telt fuldt
- forbedring nu Charles Charles har
- service af hurtig Excel vores beslutningsmodel
- beslutningsmodellerne begrænsede vi er i
- en halv dag analyserer han din projektmappe
- og han hævder gør det til hundrede
- gange hurtigere lige finder han
- flaskehalse Annette og Charles Charles
- comes from he says look at comma false
- that you and your accountant friends are
- doing it is the slowest thing in Excel
- if you would do a comma true it's a
- thousand times faster and then Charles
- says this next Clause is if it doesn't
- really matter he says now sometimes it's
- wrong oh wait Charles you don't
- understand an accountant sometimes is
- wrong is a non-starter we do not accept
- sometimes it's wrong and and the time
- that it's wrong the comma true when
- you're doing a comma true is we go look
- for a P 3 2 2 1 1 and it's not found
- they're gonna give you the item just
- less alright and they're not gonna tell
- you we couldn't find it they're just
- gonna they're just gonna give you Adam
- just less that that's unacceptable and
- Charles says well here's what we could
- do imagine if you did a vlookup of 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
- øge du kunne skifte til vlookup
- med sandt, men det rapporterer det forkerte
- svar, hvis varerne ikke findes, så vi er
- faktisk vil gøre to vlookups opslag a
- to i kolonnen en ved bordet og
- se om det hvad vi får tilbage er et to hvis
- det er det er sikkert at tage vlookup ind
- den fælles kolonne for ellers at have en
- hvis udsagn det siger ikke fundet okay
- å hej tak til Charles Williams for
- lærer mig det fantastiske trick og
- tak til dig for at stoppe ved vil se
- du næste gang til endnu et net cast fra
- MrExcel
Download fil
Download eksempelfilen her: Podcast2031.xlsm
