Ak máte veľký pracovný hárok, veľa VLOOKUPOV môže začať veci spomaľovať. Máte pomalý pracovný hárok kvôli VLOOKUP? Hovorím pracovný list, ktorého výpočet vyžaduje 40 sekúnd alebo 4 minúty. V dnešnom článku problém vyrieši úžasný vzorec s dvoma VLOOKUPMI využívajúci vyhľadávanie rozsahu.
VLOOKUP je pomerne drahá funkcia. Ak hľadáte presnú zhodu, musí program Excel vyhľadávať vo vyhľadávacej tabuľke jeden riadok po druhom.
V zošite, ktorý dnes používam, sa robí 7 000 VLOOKUPOV do tabuľky so 116 000 položkami. Na skutočne rýchlom 64-bitovom stroji s 8 jadrami je čas prepočtu 3,01 sekundy.
Jedným zo spôsobov, ako vylepšiť funkciu VLOOKUP, je presunúť najpredávanejšie položky do hornej časti vyhľadávacej tabuľky. Získajte správu o 100 najpredávanejších položkách a presuňte ich na začiatok zoznamu. Triedenie podľa popularity zlepšuje čas prepočtu na 0,369 sekundy. To je osemkrát rýchlejšie ako pri prvom výsledku.
Existuje však spôsob, ako veci ešte viac urýchliť. Pri vytváraní VLOOKUPU, keď sa dostanete k štvrtému argumentu a zvolíte False, existuje ešte jedna možnosť, ktorá sa takmer nikdy nepoužíva. Excel tvrdí, že hodnota „True“ predstavuje „približnú zhodu“. To nie je vôbec správne. Ak by bol tím Excelu úprimný, vysvetlil by, že True „často poskytuje správnu odpoveď, ale inokedy, bez varovania, tam vkĺzneme nesprávnu odpoveď. Dúfam, že vám nebude vadiť prepracovať vaše čísla Komisii pre cenné papiere. “
Iste, na použitie True je vhodný čas. Pozri tento článok. Bolo by však naozaj zlé používať True, keď sa snažíte o presnú zhodu.
Ak sa pokúsite použiť True na presnú zhodu, budete často dostávať správnu odpoveď. Ale ak hľadaná položka nie je v tabuľke, Excel vám dá hodnotu z iného riadku. Toto je časť, ktorá robí z “Pravda” v účtovníctve pre všetkých neplatič. Zatvorenie nie je v účtovníctve nikdy správne.
Poznámka
Nasledujúci trik som sa naučil od Charlesa Williamsa. Je popredným svetovým expertom na rýchlosť pracovných hárkov. Ak máte pomalý zošit, najmite si na pol dňa konzultácie Charlesa Williamsa. Môže nájsť úzke miesta a zrýchliť váš pracovný hárok. Charlesa nájdete na stránke http://www.decisionmodels.com.
Zatiaľ čo ja a všetci účtovníci odmietame argument „Pravda“ funkcie VLOOKUP z dôvodu nepredvídateľnosti, Charles Williams tvrdí za pravdu. Poukazuje na to, že Pravda je oveľa rýchlejšia ako False. Stokrát rýchlejšie. Priznáva, že niekedy dostanete nesprávnu odpoveď. Má však spôsob, ako sa vyrovnať s nesprávnymi odpoveďami.
Charles vlastne chce, aby ste urobili dva VLOOKUPY. Najskôr urobte SVYHLEDANIE a vráťte stĺpec 1 z tabuľky. Uvidíte, či je výsledok to, čo ste hľadali. Ak sa tento výsledok zhoduje, potom viete, že je bezpečné vykonať skutočný VLOOKUP, aby sa z tabuľky vrátil nejaký ďalší stĺpec:
=IF(VLOOKUP(A2,Table,1,True)=A2,"All is good","The Answer will be wrong")
Na prvý pohľad to vyzerá šialene. Ak chcete použiť Karlovu metódu, musíte urobiť dvakrát toľko VLOOKUPOV. Keď si však načasujete čas výpočtu pre túto metódu, je to 35-krát rýchlejšie ako bežné VLOOKUP.
Všimnite si, že zatiaľ čo väčšina vyhľadávacích tabuliek sa nemusí triediť, keď ako štvrtý argument používate True, tabuľka sa musí triediť. Sedemminútovú diskusiu o tom, ako sa Pravá verzia VLOOKUP-u presúva cez vyhľadávaciu tabuľku, nájdete na stránke http://mrx.cl/TrueVLOOKUP.
Ďakujem Charlesovi Williamsovi za to, že ma túto funkciu naučil, a Scottovi St. Amantovi za nomináciu na špičku 40 najlepších.
Pozeraj video
- VLOOKUP pri použití s False je pomalá funkcia
- Triedenie údajov AZ túto funkciu neurýchľuje
- Triedenie podľa obľúbenosti by mohlo túto funkciu urýchliť
- Prepnutie na VLOOKUP pomocou True je rýchlejšie, ale nenájde nesprávnu odpoveď, ak sa položka nenájde
- Na zmiernenie problému urobte VLOOKUP (A2, tabuľka, 1, pravda), aby ste zistili, či je najskôr výsledok A2
- 14 000 VLOOKUP (True) a 7000 IF beží rýchlejšie ako 7000 VLOOKUP (False)
Automaticky generovaný prepis
- Naučte sa Excel z Podcastu
- epizóda 2031 rýchlejšie vlookup Ja som
- podcasting všetkých tipov v tejto knihe
- kliknite na I v pravom hornom rohu
- sa dostanete do sledovaného zoznamu
- hej vitaj späť k pánovi hutnik cast
- Som Bill Jelen. Toto som urobil
- video skôr, ako bude jedno z mojich najobľúbenejších
- triky, ak máte vzhľad, ak áno
- mať hviezdu vlookup, ktorá užíva 30 40 50
- sekundy štyri minúty viete čokoľvek
- toto video sa vám bude páčiť, ak
- vlookup stick jednu sekundu stačí kliknúť na Ďalej
- a pokračujte na ďalšie video II have a
- vlookup tu to pozerá do tabuľky
- zo 115 000 položiek, ktoré sa zúčastňujú 7 000 vlookupov
- použijeme Charlesa Williamsa
- z rýchleho excelového kódu zistiť, ako dlho to trvá
- trvá to urobiť vlookup v poriadku štyri
- bod nula deväť sekúnd, to je
- typický vlookup s čiarkou na serveri nepravdivý
- koniec a toto všetko prišlo, pretože dlho
- davno ma navnadil nejaky chlap na
- Twitter, ktorý povedal, že by bolo lepšie, keby
- zoradili by ste svoju vyhľadávaciu tabuľku a
- zasielanie som povedal nie, to nie je vôbec pravda
- nezáleží na tom, či pôjdeme a
- odosielanie alebo zostupovanie alebo úplne
- náhodne sa vlookup musí ísť pozrieť
- od položky k položke k položke a tak keď my
- zoradiť tabuľku a zistiť, či to skutočne trvá
- dlhšie štyri body osem štyri sekundy tak
- viete, že nie je pravda, že triedenie
- podľa tabuľky to pôjde rýchlejšie, ale
- skutočne tá vec, ktorá by to mohla zariadiť
- rýchlejšie, ak by sa nejako dalo zoradiť podľa
- popularita, ak by ste mohli získať to najlepšie
- predaj tovaru v hornej časti zoznamu
- aj ty poznáš svoju najlepšiu päťdesiatku, ktorú vieš
- aké sú vaše 50 najpredávanejšie položky
- posuňte ich na začiatok zoznamu a
- sledujte, že na pár sekúnd klesne na 0,36
- sekúnd desaťnásobné zlepšenie času
- pomocou zoradenia podľa popularity teraz hej niekoľko
- pred rokmi som mal to stastie
- pozval do Amsterdamu, aby predstavil na
- Excel summit tam a nie je to ako
- väčšina mojich seminárov, kde som len ja
- hneď tam boli dve koľaje, takže miestnosť a
- a izba B a ja som bol v miestnosti be
- hovorí o vyhliadkach a znova v miestnosti
- hádajte, kto v tej miestnosti sedel
- bol Charles Williams v poriadku a Charles
- tu je
- jeho meno bolo uvedené v
- múr, takže sa príde na to pozrieť
- sleduje moje malé demo tam, kam idem
- zo štyroch sekúnd na 0,36 sekundy he
- príde za mnou a potom povie, že som sa stavil
- si s tým celkom spokojný
- zlepšenie
- Hovorím, že je to plný stan
- zlepšenie teraz Charles Charles má
- služba rýchleho Excelu náš rozhodovací model
- obmedzené sú modely rozhodovania, v ktorých sa nachádzame
- pol dňa bude analyzovať váš pracovný zošit
- a tvrdí, že je z neho sto
- krát rýchlejšie, správne nájde
- prekážky Annette a Charles Charles
- pochádza z hovorí, že pohľad na čiarku je nepravdivý
- že ste vy a vaši priatelia účtovníci
- robiť to je v programe Excel najpomalšia vec
- ak by ste robili čiarku, je to
- tisíckrát rýchlejšie a potom Charles
- hovorí, že táto ďalšia klauzula je, ak nie
- naozaj záleží, hovorí, že niekedy to tak je
- zle ach počkaj Charles ty nie
- rozumej účtovníkovi niekedy je
- nesprávne je neštartujúci, ktorého neprijímame
- niekedy je zle a a cas
- že je zle čiarka pravdivá keď
- robíš čiarku, že sa ideme pozrieť
- pre P 3 2 2 1 1 a nenašlo sa
- dajú ti ten predmet len
- menej v poriadku a oni to nepovedia
- nemohli sme nájsť, že sú to len oni
- len ti dajú Adama
- len to je neprijateľné a
- Charles hovorí, že tu je to, čo sme mohli
- predstavte si, že ste urobili prieskum 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
- zvýšenie, ktoré by ste mohli prepnúť na vlookup
- s true, ale bude to hlásiť nesprávne
- odpovedzte, ak sa položky nenájdu, takže sme
- Vlastne urobím dva vlookupy a
- dva v stĺpci jeden pri stole a
- uvidíme, či to, čo dostaneme späť, je dvojka ak
- je bezpečné ísť sa pozrieť na
- spoločný stĺpec inak mať
- ak vyhlásenie hovorí, že nebolo nájdené, je v poriadku
- ach hej vďaka Charlesovi Williamsu za
- učí ma ten úžasný trik a
- ďakujem ti, že si sa zastavil, uvidím
- nabudúce pre ďalšie obsadenie siete z
- MrExcel
Stiahnuť súbor
Stiahnutie vzorového súboru nájdete tu: Podcast2031.xlsm
