Dacă aveți o foaie de lucru mare, o mulțime de VLOOKUP-uri pot începe să încetinească lucrurile. Aveți o foaie de lucru lentă din cauza VLOOKUP? Vorbesc despre o foaie de lucru care necesită 40 de secunde sau 4 minute pentru a calcula. În articolul de astăzi, o formulă uimitoare cu două VLOOKUP-uri care utilizează căutarea intervalului va rezolva problema.
VLOOKUP este o funcție relativ scumpă. Când căutați o potrivire exactă, Excel trebuie să caute tabelul de căutare câte un rând.
Cartea de lucru pe care o folosesc astăzi face 7000 VLOOKUP-uri într-un tabel de 116.000 de articole. Pe o mașină cu 64 de biți foarte rapidă, cu 8 nuclee, timpul de recalc este de 3,01 secunde.
O modalitate de a îmbunătăți VLOOKUP este de a muta cele mai bine vândute articole în partea de sus a tabelului de căutare. Obțineți un raport al celor mai bine vândute 100 de articole și mutați-le în partea de sus a listei. Sortarea după popularitate îmbunătățește timpul de recalc la 0,369 secunde. Aceasta este de opt ori mai rapidă decât primul rezultat.
Dar există o modalitate de a accelera lucrurile și mai mult. Pe măsură ce vă construiți VLOOKUP-ul, când ajungeți la al patrulea argument pentru a alege False, există o altă opțiune care nu este folosită niciodată. Excel spune că „Adevărat” are o „potrivire aproximativă”. Acest lucru nu este deloc corect. Dacă echipa Excel ar fi sinceră, ar explica că True „oferă un răspuns corect de multe ori, dar alteori, fără niciun avertisment, vom introduce răspunsul greșit acolo. Sper că nu vă deranjează să vă refaceți numerele către Securities and Exchange Commission. ”
Sigur, există un moment adecvat pentru a folosi True. Vezi acest articol. Dar ar fi foarte rău să folosiți True atunci când încercați să faceți o potrivire exactă.
Dacă încercați să utilizați True pentru o potrivire exactă, veți obține răspunsul corect de multe ori. Dar când elementul pe care îl căutați nu este în tabel, Excel vă va oferi valoarea dintr-un rând diferit. Aceasta este partea care face din „Adevărat” un non-starter pentru toată lumea din contabilitate. Închiderea nu este niciodată corectă în contabilitate.
Notă
Am învățat următorul truc de la Charles Williams. El este cel mai important expert din lume în viteza foilor de lucru. Dacă aveți un registru de lucru lent, angajați-l pe Charles Williams pentru o jumătate de zi de consultanță. El poate găsi blocajele și vă poate face foaia de lucru mai rapidă. Găsiți-l pe Charles la http://www.decisionmodels.com.
În timp ce eu și toți contabilii respingem argumentul „True” al VLOOKUP din cauza imprevizibilității, Charles Williams susține True. El subliniază că adevăratul este mult mai rapid decât falsul. De sute de ori mai repede. El recunoaște că uneori primești un răspuns greșit. Dar are o modalitate de a face față răspunsurilor greșite.
Charles vrea de fapt să faci două VLOOKUP-uri. Mai întâi, faceți o VLOOKUP și returnați coloana 1 din tabel. Vedeți dacă rezultatul este ceea ce căutați în primul rând. Dacă acel rezultat se potrivește, atunci știți că este sigur să faceți VLOOKUP-ul real pentru a returna o altă coloană din tabel:
=IF(VLOOKUP(A2,Table,1,True)=A2,"All is good","The Answer will be wrong")
Din față, pare nebun. Pentru a utiliza metoda lui Charles, trebuie să faceți de două ori mai multe VLOOKUP-uri. Dar, când calculați timpul de calcul pentru această metodă, este de 35 de ori mai rapid decât VLOOKUP-ul normal.
Rețineți că, deși majoritatea tabelelor de căutare nu trebuie să fie sortate, atunci când utilizați True ca al patrulea argument, tabelul trebuie să fie sortat. Pentru o discuție de 7 minute despre modul în care versiunea True a VLOOKUP trece prin tabelul de căutare, consultați http://mrx.cl/TrueVLOOKUP.
Mulțumesc lui Charles Williams pentru că m-a învățat această caracteristică și lui Scott St. Amant pentru că a nominalizat-o pentru un top 40 de tip.
Urmăriți videoclipul
- VLOOKUP când este utilizat cu False este o funcție lentă
- Sortarea datelor AZ nu accelerează funcția
- Sortarea după popularitate ar putea accelera funcția
- Trecerea la VLOOKUP cu True este mai rapidă, dar va raporta răspunsul greșit dacă elementul nu este găsit
- Pentru a atenua problema, faceți un VLOOKUP (A2, Tabel, 1, Adevărat) pentru a vedea dacă rezultatul este mai întâi A2
- 14000 VLOOKUP (Adevărat) și 7000 IF rulează mai repede decât 7000 VLOOKUP (Fals)
Transcriere generată automat
- Aflați Excel din Podcast
- episodul 2031 mai rapid vlookup Sunt
- podcasting toate sfaturile din această carte
- faceți clic pe I din colțul din dreapta sus
- pentru a ajunge la lista de urmărire
- Bine ai venit înapoi la dl. distribuție hutnik
- Sunt Bill Jelen. Am făcut asta
- videoclip înainte să fie unul dintre preferatele mele
- trucuri dacă ai aspectul dacă ești tu
- au stele vlookup luând 30 40 50
- secunde patru minute știi orice
- o să-ți placă acest videoclip dacă ai
- vlookup stick o secundă doar faceți clic pe Următorul
- și treceți la următorul videoclip II am un
- vlookup aici se uită într-o masă
- din 115.000 de articole care fac 7000 vlookup deci
- vom folosi niște Charles Williams
- din codul Excel rapid pentru a vedea cât timp
- trebuie să facă acest vlookup bine patru
- punctul zero nouă secunde acesta este
- tipic vlookup cu virgulă falsă la
- sfârșit și toate acestea au apărut pentru că mult timp
- cu mult timp în urmă am fost momit de un tip
- Twitter care a spus că ar fi mai bine dacă
- v-ați sorta tabelul de căutare a
- trimiterea am spus că nu este deloc adevărat
- nu contează dacă mergem a
- trimiterea sau coborârea sau complet
- aleatoriu, vlookup trebuie doar să se uite
- de la articol la articol în articol și așa atunci când noi
- sortați masa pentru a vedea de fapt este nevoie
- mai lung patru punct opt patru secunde deci
- știți că nu este adevărat că sortarea
- masa îl va face să meargă mai repede dar
- într-adevăr lucrul care l-ar putea duce
- mai repede dacă cumva ai putea sorta după
- popularitate dacă ai putea obține cele mai bune
- vânzarea articolelor în partea de sus a listei
- chiar și tu știi primii tăi cincizeci știi
- care sunt cele mai bine vândute 50 de articole
- aduceți-le pe cele din partea de sus a listei și
- ceas care timp de secunde coboară la 0,36
- secunde o îmbunătățire de zece ori în timp
- folosind sortare după popularitate acum hei câteva
- acum ani am avut norocul să fiu
- invitat la Amsterdam pentru a prezenta la un
- Summit-ul Excel acolo și nu este așa
- majoritatea seminariilor mele în care sunt doar eu
- chiar erau două piste, așa că camera a
- iar camera B și eu eram în camera fi
- vorbind despre vlookup-uri și peste în cameră
- o presupunere cine stătea în acea cameră
- era Charles Williams bine și Charles
- iată
- numele său fiind menționat prin
- zid așa că vine să-l privească el
- urmărește micul meu demo acolo unde mă duc
- de la patru secunde la 0,36 secunde el
- vine la mine după aceea spune că pariez
- ești destul de fericit cu asta
- îmbunătăţire
- Zic că da, e un cort plin
- îmbunătățire acum Charles Charles are
- serviciu de Excel rapid modelul nostru de decizie
- modelele de decizie limitate suntem în
- o jumătate de zi vă va analiza registrul de lucru
- și susține că face o sută
- de ori mai repede, el va găsi
- blocaje Annette și Charles Charles
- vine de la el spune uită-te la virgulă falsă
- că tu și prietenii tăi contabili sunteți
- a face acest lucru este cel mai lent lucru din Excel
- dacă ai face o virgulă adevărată este o
- de mii de ori mai repede și apoi Charles
- spune că următoarea clauză este în caz contrar
- chiar contează, spune el, uneori este
- greșit, așteaptă Charles, nu
- înțelege că un contabil este uneori
- greșit este un non-starter pe care nu-l acceptăm
- uneori este greșit și și timpul
- că este greșit virgula adevărată când
- faci o virgulă adevărată este că mergem să căutăm
- pentru un P 3 2 2 1 1 și nu este găsit
- îți vor da obiectul doar
- mai puțin în regulă și nu vor spune
- tu nu am putut găsi că sunt doar
- O să-ți dea doar Adam
- cu atât mai puțin decât asta este inacceptabil și
- Charles spune bine, iată ce am putut
- imaginați-vă dacă ați făcut o vizualizare a 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
- creșterea ați putea trece la vlookup
- cu adevărat, dar va raporta greșitul
- răspundeți dacă articolele nu au fost găsite, așa că suntem
- De fapt, voi face două căutări vlookups
- două în coloana una la masă și
- vezi dacă ceea ce primim înapoi este un doi dacă
- este sigur să mergi să faci vlookup-ul
- coloana comună pentru a avea altfel un
- dacă afirmația spune că nu s-a găsit bine
- oh, mulțumesc lui Charles Williams pentru
- învățându-mă acel truc uimitor și
- mulțumesc pentru că ai trecut pe aici
- data viitoare pentru o altă distribuție netă din
- MrExcel
Descărcare fișier
Descărcați fișierul exemplar aici: Podcast2031.xlsm
