Hvis du har et stort regneark, kan mange VLOOKUP-er begynne å bremse ting. Har du et tregt regneark på grunn av VLOOKUP? Jeg snakker et regneark som tar 40 sekunder eller 4 minutter å beregne. I dagens artikkel vil en fantastisk formel med to VLOOKUP-er som bruker rekkeviddeoppslaget, løse problemet.
VLOOKUP er en relativt kostbar funksjon. Når du leter etter en nøyaktig samsvar, må Excel se gjennom oppslagstabellen en rad om gangen.
Arbeidsboken som jeg bruker i dag, gjør 7000 VLOOKUPs til en tabell med 116.000 artikler. På en veldig rask 64-bits maskin med 8 kjerner er omberegningstiden 3,01 sekunder.
En måte å forbedre VLOOKUP på er å flytte de bestselgende varene til toppen av oppslagstabellen. Få en rapport om de 100 mest solgte artiklene, og flytt disse elementene til toppen av listen. Sortering etter popularitet forbedrer gjenberegningstiden til 0,369 sekunder. Dette er åtte ganger raskere enn det første resultatet.
Men det er en måte å få fart på ting enda mer. Når du bygger din VLOOKUP, når du kommer til det fjerde argumentet om å velge False, er det et annet alternativ som nesten aldri brukes. Excel sier "True" gjør en "omtrentlig kamp." Dette er ikke riktig. Hvis Excel-teamet var ærlig, ville de forklare at True “gir et riktig svar mye av tiden, men andre ganger, uten noen advarsel, kommer vi til å gi feil svar der inne. Jeg håper du ikke har noe imot å overføre tallene dine til Securities and Exchange Commission. ”
Visst, det er riktig tid å bruke True. Se denne artikkelen. Men det ville være veldig ille å bruke True når du prøver å gjøre en nøyaktig samsvar.
Hvis du prøver å bruke True for en nøyaktig samsvar, får du riktig svar mye av tiden. Men når varen du leter etter ikke er i tabellen, vil Excel gi deg verdien fra en annen rad. Dette er den delen som gjør “True” til en ikke-startpakke for alle innen regnskap. Lukking er aldri riktig i Regnskap.
Merk
Jeg lærte følgende triks av Charles Williams. Han er verdens fremste ekspert på regnearkhastighet. Hvis du har en langsom arbeidsbok, ansett Charles Williams for en halv dag med konsulenttjenester. Han kan finne flaskehalsene og gjøre regnearket raskere. Finn Charles på http://www.decisionmodels.com.
Mens jeg og alle regnskapsførere avviser det "sanne" argumentet til VLOOKUP på grunn av uforutsigbarheten, argumenterer Charles Williams for True. Han påpeker at den sanne er mye raskere enn falsk. Hundrevis av ganger raskere. Han innrømmer at du noen ganger får feil svar. Men han har en måte å takle feil svar på.
Charles vil faktisk at du skal gjøre to VLOOKUP. Først gjør du en VLOOKUP og returnerer kolonne 1 fra tabellen. Se om resultatet er det du så opp i utgangspunktet. Hvis resultatet stemmer overens, så vet du at det er trygt å gjøre den virkelige VLOOKUP for å returnere en annen kolonne fra tabellen:
=IF(VLOOKUP(A2,Table,1,True)=A2,"All is good","The Answer will be wrong")
På forsiden av dette virker dette vanvittig. For å bruke Charles 'metode, må du gjøre dobbelt så mange VLOOKUP-er. Men når du bestemmer beregningstiden for denne metoden, er den 35 ganger raskere enn vanlig VLOOKUP.
Merk at selv om de fleste oppslagstabellene ikke trenger å bli sortert, må du sortere tabellen når du bruker True som det fjerde argumentet. For en 7-minutters diskusjon om hvordan den sanne versjonen av VLOOKUP hopper gjennom oppslagstabellen, se http://mrx.cl/TrueVLOOKUP.
Takk til Charles Williams for at han lærte meg denne funksjonen og til Scott St. Amant for å nominere den til et topp 40-tips.
Se på video
- VLOOKUP når den brukes med False er en langsom funksjon
- Sortering av data AZ gir ikke raskere funksjon
- Sortering etter popularitet kan øke funksjonen
- Å bytte til VLOOKUP med True er raskere, men det vil rapportere feil svar hvis varen ikke blir funnet
- For å avhjelpe problemet, gjør en VLOOKUP (A2, tabell, 1, sann) for å se om resultatet først er A2
- 14000 VLOOKUP (True) og 7000 IF kjører raskere enn 7000 VLOOKUP (False)
Autogenerert transkripsjon
- Lær Excel fra Podcast
- episode 2031 raskere vlookup Jeg er
- podcasting av alle tipsene i denne boka
- klikk på I øverst til høyre
- for å komme til overvåkningslisten
- hei velkommen tilbake til hr. hutnik rollebesetning
- Jeg er Bill Jelen, jeg har gjort dette
- video før den er en av favorittene mine
- triks hvis du har blikket hvis du
- har vlookup-stjerne som tar 30 40 50
- sekunder fire minutter vet du noe
- du kommer til å elske denne videoen hvis din
- vlookup stick ett sekund, bare klikk Neste
- og fortsett til neste video II har en
- vlookup her det ser inn i et bord
- av 115 000 gjenstander som gjør 7000 vlookup så
- vi bruker Charles Williams
- fra rask Excel-kode for å se hvor lenge den er
- tar å gjøre dette vlookup ok fire
- punkt null ni sekunder det er
- typisk vlookup med komma falsk på
- slutten, og alt dette kom opp for lenge
- for lenge siden ble jeg agnet av en fyr på
- Twitter som sa at det ville være bedre hvis
- du ville sortere oppslagstabellen a
- sendingen sa jeg nei det er ikke sant i det hele tatt
- det spiller ingen rolle om vi går a
- sende eller synke eller helt
- tilfeldig må vlookup bare se
- fra vare til vare og så når vi
- sorter tabellen, se den faktisk tar
- lengre fire poeng åtte fire sekunder så
- du vet at det ikke stemmer at sortering av
- bordet vil få det til å gå raskere, men
- virkelig tingen som kan få det til å gå
- raskere hvis du på en eller annen måte kunne sortere etter
- popularitet hvis du kunne få det beste
- selge varer øverst på listen
- til og med du kjenner de femti beste du kjenner
- hva dine topp 50 bestselgende varer er
- bringe dem til toppen av listen og
- se på at i sekunder går ned til 0,36
- sekunder en tidobling av tiden
- bruker sorter etter popularitet nå hei noen få
- for mange år siden var jeg så heldig å være
- invitert til Amsterdam for å presentere på en
- Excel-toppmøtet der, og det er ikke som
- de fleste av seminarene mine der det bare er meg
- akkurat var det to spor så rom a
- og rom B og jeg var over i rommet være
- snakker om vlookups og over i rommet
- en gjetning hvem som satt i det rommet det
- var Charles Williams ok og Charles
- her er
- navnet hans blir nevnt gjennom
- vegg så han kommer bort for å se på det han
- ser på min lille demo der hvor jeg går
- fra fire sekunder til 0,36 sekunder han
- kommer opp til meg etterpå sier han at jeg vedder
- du er ganske fornøyd med det
- forbedring
- Jeg sier ja det er et telt fullt
- forbedring nå Charles Charles har
- service av rask Excel vår beslutningsmodell
- beslutningsmodellene begrenset vi er i
- en halv dag vil han analysere arbeidsboken din
- og han hevder gjør det til hundre
- ganger raskere vil han finne
- flaskehalser Annette og Charles Charles
- kommer fra han sier se på komma falsk
- at du og dine regnskapsvenner er
- å gjøre det er den tregeste tingen i Excel
- hvis du gjør et komma sant, er det en
- tusen ganger raskere og deretter Charles
- sier denne neste klausulen er hvis den ikke gjør det
- virkelig betyr noe han sier nå noen ganger er det
- galt å vent Charles du ikke gjør det
- forstå en regnskapsfører noen ganger er
- feil er en ikke-startpakke vi ikke godtar
- noen ganger er det galt og og tiden
- at det er feil komma sant når
- du gjør et komma sant er vi ser
- for en P 3 2 2 1 1 og den ble ikke funnet
- de skal gi deg varen bare
- mindre ok, og de kommer ikke til å fortelle det
- du vi kunne ikke finne det de er bare
- de vil bare gi deg Adam
- bare mindre at det er uakseptabelt og
- Charles sier godt her er hva vi kunne
- forestill deg om du gjorde en oppsøk 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
- øke kan du bytte til vlookup
- med sant, men det vil rapportere feil
- svar hvis elementene ikke er funnet så vi er
- faktisk kommer til å gjøre to oppslagsoppslag a
- to i kolonnen en ved bordet og
- se om det det vi får tilbake er et to hvis
- det er trygt å gå inn i oppslaget
- den vanlige kolonnen for ellers å ha en
- hvis uttalelse det står ikke funnet greit
- å hei takk til Charles Williams for
- lærer meg det fantastiske trikset og
- takk til deg for at du var innom
- neste gang for en ny nettstøp fra
- MrExcel
Last ned fil
Last ned eksempelfilen her: Podcast2031.xlsm
