Če imate velik delovni list, lahko veliko VLOOKUP-ov začne upočasnjevati stvari. Ali imate zaradi VLOOKUPA počasen delovni list? Govorim o delovnem listu, ki za izračun potrebuje 40 sekund ali 4 minute. V današnjem članku bo težavo rešila neverjetna formula z dvema VLOOKUP-om, ki uporabljata iskanje obsega.
VLOOKUP je razmeroma draga funkcija. Ko iščete natančno ujemanje, mora Excel eno vrstico pregledovati po iskalni tabeli.
Delovni zvezek, ki ga danes uporabljam, v tabelo s 116.000 elementi vnese 7000 VLOOKUPOV. Na res hitrem 64-bitnem stroju z 8 jedri je čas ponovitve 3,01 sekunde.
Eden od načinov za izboljšanje VLOOKUP-a je premikanje najbolje prodajanih izdelkov na vrh iskalne tabele. Pridobite poročilo o 100 najbolj prodajanih artiklih in jih premaknite na vrh seznama. Razvrščanje po priljubljenosti podaljša čas ponovitve na 0,369 sekunde. To je osemkrat hitreje kot prvi rezultat.
Obstaja pa način, kako stvari še bolj pospešiti. Ko gradite svoj VLOOKUP, ko pridete do četrtega argumenta, da izberete False, obstaja še ena možnost, ki je komaj kdaj uporabljena. Excel pravi, da »True« pomeni »približno ujemanje«. To sploh ni pravilno. Če bi bila Excelova ekipa iskrena, bi nam razložili, da True »velikokrat daje pravilen odgovor, toda včasih bomo brez kakršnega koli opozorila vtaknili napačen odgovor. Upam, da vas ne moti, da svoje številke ponovno pošljete Komisiji za vrednostne papirje. "
Seveda je pravi čas za uporabo True. Glej ta članek. Toda res bi bilo slabo, če bi uporabili True, ko bi se radi natančno ujemali.
Če poskusite uporabiti True za natančno ujemanje, boste velikokrat dobili pravi odgovor. Ko pa iskanega elementa ni v tabeli, vam Excel prikaže vrednost iz druge vrstice. To je del, zaradi katerega »True« ni začetnik za vse v računovodstvu. Zapri nikoli ni pravilno v računovodstvu.
Opomba
Naslednjega trika sem se naučil od Charlesa Williamsa. Je največji svetovni strokovnjak za hitrost delovnih listov. Če imate počasen delovni zvezek, najemite Charlesa Williamsa za poldnevno svetovanje. Poišče ozka grla in hitreje pripravi vaš delovni list. Poiščite Charlesa na http://www.decisionmodels.com.
Medtem ko jaz in vsi računovodje zavrnemo argument »True« VLOOKUP-a zaradi nepredvidljivosti, Charles Williams zagovarja True. Poudarja, da je resnično veliko hitreje kot napačno. Stokrat hitreje. Priznava, da včasih dobiš napačen odgovor. Vendar ima način, kako se spoprijeti z napačnimi odgovori.
Charles dejansko želi, da narediš dva VLOOKUP-a. Najprej izvedite VLOOKUP in vrnite stolpec 1 iz tabele. Poglejte, ali je rezultat sploh tak, ki ste ga iskali. Če se ta rezultat ujema, potem veste, da je varno narediti pravi VLOOKUP, da iz tabele vrnete kak drug stolpec:
=IF(VLOOKUP(A2,Table,1,True)=A2,"All is good","The Answer will be wrong")
Na prvi pogled se zdi to noro. Če želite uporabiti Charlesovo metodo, morate narediti dvakrat več VLOOKUP-ov. Ko pa čas izračuna izračuna za to metodo, je 35-krat hitrejši od običajnega VLOOKUP-a.
Upoštevajte, da medtem ko večine iskalnih tabel ni treba razvrščati, če uporabljate True kot četrti argument, je treba tabelo razvrstiti. Za 7-minutno razpravo o tem, kako resnična različica VLOOKUP skoči skozi iskalno tabelo, glejte http://mrx.cl/TrueVLOOKUP.
Zahvaljujem se Charlesu Williamsu, ker me je naučil te funkcije, in Scottu St. Amantu, ker jo je nominiral za top 40 nasvetov.
Oglejte si video
- VLOOKUP, kadar se uporablja s False, je počasna funkcija
- Razvrščanje podatkov AZ ne pospeši funkcije
- Razvrščanje po priljubljenosti lahko funkcijo pospeši
- Prehod na VLOOKUP s True je hitrejši, vendar bo sporočil napačen odgovor, če elementa ne najdete
- Če želite težavo ublažiti, naredite VLOOKUP (A2, tabela, 1, res), da vidite, ali je rezultat najprej A2
- 14000 VLOOKUP (True) in 7000 IF deluje hitreje kot 7000 VLOOKUP (False)
Samodejno ustvarjen prepis
- Naučite se Excel iz Podcasta
- epizoda 2031 hitrejši vlookup sem
- podcasting vseh nasvetov v tej knjigi
- kliknite I v zgornjem desnem kotu
- da pridete do seznama za spremljanje
- hej dobrodošli nazaj pri g. hutniška zasedba
- Sem Bill Jelen. To sem naredil
- video, preden je eden mojih najljubših
- triki, če ste videti, če imate
- imajo vlookup zvezdo ob 30 40 50
- sekunde štiri minute karkoli veste
- vam bo všeč ta video, če vaš
- vlookup stick eno sekundo samo kliknite Naprej
- in pojdite na naslednji video II
- vlookup tukaj išče v tabelo
- od 115.000 predmetov, ki opravljajo 7000 vlookup tako
- uporabili bomo nekaj Charlesa Williamsa
- iz hitre kode Excel, da vidite, kako dolgo
- potrebuje za to vlookup v redu štiri
- točka nič devet sekund, to je
- tipičen vlookup z vejico false na
- konec in vse to se je zgodilo, ker dolgo
- že zdavnaj me je vabil nek tip
- Twitter, ki je dejal, da bi bilo bolje, če
- bi razvrstili svojo iskalno tabelo a
- pošiljanje sem rekel ne, to sploh ni res
- ni pomembno, ali gremo a
- pošiljanje ali spuščanje ali popolnoma
- naključno vlookup mora iti iskat
- od predmeta do elementa do predmeta in tako tudi takrat, ko smo
- razvrsti tabelo in poglej, kaj dejansko potrebuje
- daljše štiri točke osem štiri sekunde
- veste, da ni res, da razvrščanje
- tabela bo šla hitreje, vendar
- res tisto, kar bi ga lahko sprožilo
- hitreje, če bi nekako lahko uredil
- priljubljenost, če bi lahko dobili najboljše
- prodaja predmetov na vrhu seznama
- tudi sami poznate svojih petdeset, ki jih poznate
- kakšnih je 50 najbolj prodajanih izdelkov
- jih postavite na vrh seznama in
- pazi, da se za nekaj sekund zniža na 0,36
- sekunde desetkratno izboljšanje časa
- z uporabo razvrščanja po priljubljenosti zdaj hej nekaj
- pred leti sem imel to srečo
- povabljen v Amsterdam, da se predstavi na
- Excel vrh tam in ni všeč
- večino mojih seminarjev, kjer sem samo jaz
- desno sta bila dva tira, torej soba a
- in sobo B sva bila v sobi be
- govorimo o vlookupih in več v sobi
- ugibati, kdo je sedel v tej sobi
- je bil Charles Williams v redu in Charles
- tukaj je
- njegovo ime se omenja prek
- steno, da ga pride gledat
- gleda moj mali demo tam, kamor grem
- od štirih sekund do 0,36 sekunde he
- potem pride do mene in reče, da stavim
- s tem ste precej zadovoljni
- izboljšava
- Pravim, da šotor je poln
- izboljšanje zdaj Charles Charles ima
- storitev hitrega Excela naš odločitveni model
- modeli odločanja so omejeni
- pol dneva bo analiziral vaš delovni zvezek
- in trdi, da jih je sto
- krat hitreje desno bo našel
- ozka grla Annette in Charles Charles
- prihaja iz pravi, da je pogled na vejico napačen
- kar ste vi in vaši prijatelji računovodje
- to počne najpočasneje v Excelu
- če bi naredil vejico res, je a
- tisočkrat hitreje in nato Charles
- pravi, da je naslednja klavzula, če ne
- res pomembno, pravi zdaj, včasih je
- narobe, čakaj, Charles, ne
- razumeti, da je računovodja včasih
- narobe je ne-starter, ki ga ne sprejemamo
- včasih je narobe in in čas
- da je narobe vejica res kdaj
- delaš vejico res je gremo pogledat
- za P 3 2 2 1 1 in ni najden
- ti bodo samo dali izdelek
- manj v redu in ne bodo povedali
- ne bi mogli najti, da so samo
- ti bodo samo dali Adama
- še manj, da je to nesprejemljivo in
- Charles pravi, da je to kar bi lahko
- predstavljajte si, če ste opravili iskanje 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
- povečanje lahko preklopite na vlookup
- z true, vendar bo prijavil napako
- odgovoriti, če elementov ni mogoče najti, torej smo
- pravzaprav naredil dva vlookups loakup a
- dva v stolpcu ena za mizo in
- poglej, če je to, kar dobimo nazaj, dva, če
- varno je iti vlookup v
- skupni stolpec, da bi sicer imel
- če izjava piše, da ni našel v redu
- oh hej hvala Charlesu Williamsu za
- naučil me je tega neverjetnega trika in
- hvala, ker ste se ustavili, bomo videli
- naslednjič za še en neto igralec od
- MrExcel
Prenesite datoteko
Prenesite vzorčno datoteko tukaj: Podcast2031.xlsm
