Als u een groot werkblad heeft, kunnen veel VERT.ZOEKEN de zaken vertragen. Heeft u een traag werkblad vanwege VERT.ZOEKEN? Ik heb het over een werkblad dat 40 seconden of 4 minuten nodig heeft om te berekenen. In het artikel van vandaag zal een geweldige formule met twee VERT.ZOEKEN met behulp van de bereikzoekopdracht het probleem oplossen.
VERT.ZOEKEN is een relatief dure functie. Als u op zoek bent naar een exacte match, moet Excel de opzoektabel rij voor rij doorzoeken.
De werkmap die ik vandaag gebruik, voert 7000 VERT.ZOEKEN uit in een tabel met 116.000 items. Op een echt snelle 64-bits machine met 8 cores is de herberekeningstijd 3,01 seconden.
Een manier om VERT.ZOEKEN te verbeteren, is door de best verkochte items bovenaan de opzoektabel te plaatsen. Ontvang een rapport van de 100 best verkochte items en verplaats die items naar de top van de lijst. Sorteren op populariteit verbetert de herberekeningstijd tot 0,369 seconden. Dit is acht keer sneller dan het eerste resultaat.
Maar er is een manier om de zaken nog meer te versnellen. Terwijl u uw VERT.ZOEKEN samenstelt en u bij het vierde argument komt om False te kiezen, is er een andere optie die bijna nooit wordt gebruikt. Excel zegt dat 'True' een 'geschatte overeenkomst' is. Dit is helemaal niet correct. Als het Excel-team eerlijk was, zouden ze uitleggen dat True “vaak een juist antwoord geeft, maar soms, zonder enige waarschuwing, zullen we het verkeerde antwoord erin schuiven. Ik hoop dat je het niet erg vindt om je cijfers opnieuw aan de Securities and Exchange Commission te geven. "
Natuurlijk is er een geschikt moment om True te gebruiken. Zie dit artikel. Maar het zou erg slecht zijn om True te gebruiken als u een exacte match probeert te maken.
Als u True probeert te gebruiken voor een exacte overeenkomst, krijgt u vaak het juiste antwoord. Maar als het item dat u zoekt niet in de tabel staat, geeft Excel u de waarde uit een andere rij. Dit is het deel dat "True" een niet-starter maakt voor iedereen in de boekhouding. Sluiten is nooit correct in Boekhouding.
Opmerking
Ik heb de volgende truc geleerd van Charles Williams. Hij is 's werelds belangrijkste expert op het gebied van werkbladsnelheid. Als je een traag werkboek hebt, huur Charles Williams dan in voor een halve dag advies. Hij kan de knelpunten vinden en uw werkblad sneller maken. Vind Charles op http://www.decisionmodels.com.
Terwijl ik en alle accountants het "True" -argument van VERT.ZOEKEN afwijzen vanwege de onvoorspelbaarheid, pleit Charles Williams voor True. Hij wijst erop dat True veel sneller is dan False. Honderden keer sneller. Hij geeft toe dat je soms het verkeerde antwoord krijgt. Maar hij heeft een manier om met de verkeerde antwoorden om te gaan.
Charles wil eigenlijk dat je twee VERT.ZOEKEN doet. Voer eerst VERT.ZOEKEN uit en retourneer kolom 1 uit de tabel. Kijk of het resultaat is wat je in de eerste plaats zocht. Als dat resultaat overeenkomt, weet je dat het veilig is om de echte VERT.ZOEKEN uit te voeren om een andere kolom uit de tabel te retourneren:
=IF(VLOOKUP(A2,Table,1,True)=A2,"All is good","The Answer will be wrong")
Op het eerste gezicht lijkt dit krankzinnig. Om de methode van Charles te gebruiken, moet je twee keer zoveel VERT.ZOEKEN doen. Maar wanneer u de rekentijd voor deze methode timt, is deze 35 keer sneller dan de normale VERT.ZOEKEN.
Merk op dat hoewel de meeste opzoektabellen niet hoeven te worden gesorteerd, wanneer u True als vierde argument gebruikt, de tabel wel gesorteerd moet worden. Zie http://mrx.cl/TrueVLOOKUP voor een bespreking van 7 minuten over hoe de True-versie van VERT.ZOEKEN door de opzoektabel springt.
Dank aan Charles Williams voor het leren van deze functie en aan Scott St. Amant voor het nomineren ervan voor een top 40-tip.
Bekijk video
- VERT.ZOEKEN bij gebruik met False is een langzame functie
- Het sorteren van de gegevens AZ versnelt de functie niet
- Sorteren op populariteit kan de functie versnellen
- Overschakelen naar VERT.ZOEKEN met True gaat sneller, maar het zal het verkeerde antwoord rapporteren als het item niet wordt gevonden
- Om het probleem te verhelpen, voert u VERT.ZOEKEN uit (A2, Tabel, 1, Waar) om te zien of het resultaat eerst A2 is
- 14000 VERT.ZOEKEN (waar) en 7000 ALS sneller worden uitgevoerd dan 7000 VERT.ZOEKEN (niet waar)
Automatisch gegenereerd transcript
- Leer Excel van Podcast
- aflevering 2031 snellere vlookup I'm
- podcasting alle tips in dit boek
- klik op de I in de rechterbovenhoek
- om naar de watchlist te gaan
- hey welkom terug bij de dhr. hutnik gegoten
- Ik ben Bill Jelen. Ik heb dit dit gedaan
- video voordat het een van mijn favorieten is
- trucs als je de look hebt als je
- heb vlookup star met 30 40 50
- seconden vier minuten weet je iets
- je zult deze video geweldig vinden als je
- vlookup stick één seconde klik gewoon op Volgende
- en ga verder met de volgende video. Ik heb een
- vlookup hier kijkt het in een tafel
- van 115.000 items die 7000 vlookup dus doen
- we gaan wat Charles Williams gebruiken
- van snelle Excel-code om te zien hoe lang het duurt
- neemt om deze vlookup goed vier te doen
- punt nul negen seconden dat is het
- typische vlookup met komma false bij de
- einde en dit kwam allemaal omdat lang
- lang geleden werd ik door een of andere kerel gelokt
- Twitter die zei dat het beter zou zijn als
- je zou je opzoektabel a sorteren
- verzenden zei ik nee dat is helemaal niet waar
- het maakt niet uit of we een
- verzenden of aflopend of volledig
- random moet de vlookup gewoon gaan kijken
- van item tot item tot item en dus wanneer we
- sorteer de tafel, zie dat het echt duurt
- langer vier komma acht vier seconden dus
- je weet dat het niet waar is dat het sorteren van de
- de tafel zal het sneller laten gaan, maar
- echt het ding dat het zou kunnen laten gaan
- sneller als je op de een of andere manier zou kunnen sorteren
- populariteit als je het beste kon krijgen
- items verkopen bovenaan de lijst
- zelfs jij kent je top vijftig, weet je
- wat uw top 50 best verkochte items zijn
- breng die naar de top van de lijst en
- let op dat voor seconden daalt tot 0,36
- seconden een tienvoudige verbetering in de tijd
- gebruik sorteren op populariteit nu hey een paar
- jaren geleden had ik het geluk om te zijn
- uitgenodigd in Amsterdam om te presenteren op een
- Excel-top daar en het is niet zoals
- de meeste van mijn seminars waar ik het alleen ben
- rechts waren er twee sporen dus kamer a
- en kamer B en ik waren in kamer be
- praten over vlookups en meer in de kamer
- een gok wie er in die kamer zat
- was Charles Williams in orde en Charles
- hier is
- zijn naam wordt genoemd via de
- muur dus hij komt naar hem kijken hij
- kijkt naar mijn kleine demo daar waar ik ga
- van vier seconden tot 0,36 seconden hij
- komt later naar me toe, hij zegt dat ik wed
- daar ben je best blij mee
- verbetering
- Ik zeg ja, dat is een tent vol
- verbetering nu Charles Charles de
- service van snelle Excel ons beslissingsmodel
- de beslissingsmodellen zijn beperkt waarin we ons bevinden
- een halve dag analyseert hij je werkboek
- en hij beweert dat het honderd wordt
- keer sneller, hij zal de
- knelpunten Annette en 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
- verhogen zou je kunnen overschakelen naar vlookup
- met waar, maar het rapporteert het verkeerde
- antwoord als de items niet zijn gevonden, dus we zijn
- gaat eigenlijk twee vlookups opzoeken a
- twee in de kolom één aan de tafel en
- kijk of het wat we terugkrijgen een twee is als
- het is veilig om de vlookup in te doen
- de gemeenschappelijke kolom om anders een
- als de verklaring zegt: niet goed gevonden
- oh hey bedankt aan Charles Williams voor
- leerde me die geweldige truc en
- dankzij u voor het langskomen zal zien
- je volgende keer voor een ander net uitwerpen
- MrExcel
Download bestand
Download het voorbeeldbestand hier: Podcast2031.xlsm
