Pokud máte velký list, spousta VLOOKUPŮ může začít věci zpomalovat. Máte pomalý list kvůli SVYHLEDAT? Mluvím o listu, jehož výpočet vyžaduje 40 sekund nebo 4 minuty. V dnešním článku problém vyřeší úžasný vzorec se dvěma VLOOKUPY využívající vyhledávání rozsahu.
VLOOKUP je relativně drahá funkce. Když hledáte přesnou shodu, Excel musí prohledávat vyhledávací tabulku jeden řádek po druhém.
Sešit, který dnes používám, dělá 7000 VLOOKUPů do tabulky 116 000 položek. Na opravdu rychlém 64bitovém stroji s 8 jádry je doba přepočítání 3,01 sekundy.
Jedním ze způsobů, jak vylepšit SVYHLEDÁNÍ, je přesunout nejprodávanější položky do horní části vyhledávací tabulky. Získejte přehled 100 nejprodávanějších položek a přesuňte je na začátek seznamu. Řazení podle popularity zlepší dobu přepočítání na 0,369 sekundy. To je osmkrát rychlejší než první výsledek.
Existuje však způsob, jak věci ještě více urychlit. Při vytváření SVYHLEDÁVÁNÍ, když se dostanete ke čtvrtému argumentu a zvolíte False, existuje další možnost, která se téměř nikdy nepoužije. Excel říká „True“ dělá „přibližnou shodu“. To není vůbec správné. Pokud by byl tým Excel upřímný, vysvětlil by, že True „často poskytuje správnou odpověď, ale jindy, bez jakéhokoli varování, tam vložíme špatnou odpověď. Doufám, že vám nebude vadit přepracovat svá čísla Komisi pro cenné papíry. “
Jistě, je správný čas použít True. Viz tento článek. Bylo by však opravdu špatné použít True, když se snažíte udělat přesnou shodu.
Pokud se pokusíte použít True pro přesnou shodu, budete často dostávat správnou odpověď. Ale když hledaná položka není v tabulce, Excel vám dá hodnotu z jiného řádku. To je ta část, která z „Pravdy“ dělá v účetnictví pro všechny nespuštění. Zavřít není v účetnictví nikdy správné.
Poznámka
Následující trik jsem se naučil od Charlese Williamse. Je předním světovým odborníkem na rychlost pracovních listů. Pokud máte pomalý sešit, najměte si Charlese Williamse na půl dne konzultací. Může najít úzká místa a zrychlit váš list. Charlese najdete na adrese http://www.decisionmodels.com.
Zatímco já a všichni účetní odmítáme argument „True“ VLOOKUP kvůli nepředvídatelnosti, Charles Williams argumentuje pro True. Poukazuje na to, že Pravda je mnohem rychlejší než False. Stokrát rychleji. Připouští, že někdy dostanete špatnou odpověď. Má však způsob, jak se vypořádat se špatnými odpověďmi.
Charles vlastně chce, abyste udělali dva VLOOKUPY. Nejprve proveďte VLOOKUP a vraťte sloupec 1 z tabulky. Zjistěte, zda je výsledkem to, co jste hledali. Pokud se tento výsledek shoduje, pak víte, že je bezpečné provést skutečný VLOOKUP, aby se vrátil nějaký další sloupec z tabulky:
=IF(VLOOKUP(A2,Table,1,True)=A2,"All is good","The Answer will be wrong")
Na první pohled to vypadá šíleně. Abyste mohli použít Charlesovu metodu, musíte udělat dvakrát tolik VLOOKUPŮ. Ale když načasujete čas výpočtu pro tuto metodu, je to 35krát rychlejší než normální VLOOKUP.
Všimněte si, že zatímco většina vyhledávacích tabulek nemusí být tříděna, pokud jako čtvrtý argument používáte True, tabulka musí být tříděna. Sedmiminutovou diskusi o tom, jak se True verze VLOOKUPu dostává do vyhledávací tabulky, naleznete na adrese http://mrx.cl/TrueVLOOKUP.
Děkuji Charlesi Williamsu za to, že mě naučil tuto funkci, a Scottovi St. Amantovi za to, že ji nominoval na špičkový tip 40.
Sledovat video
- VLOOKUP při použití s False je pomalá funkce
- Třídění dat AZ nezrychluje funkci
- Třídění podle popularity by mohlo tuto funkci urychlit
- Přepnutí na SVYHLEDAT s True je rychlejší, ale pokud položku nenajdete, nahlásí nesprávnou odpověď
- Chcete-li problém zmírnit, proveďte VLOOKUP (A2, tabulka, 1, True), abyste zjistili, zda je nejprve výsledek A2
- 14000 VLOOKUP (True) a 7000 IF běží rychleji než 7000 VLOOKUP (False)
Automaticky generovaný přepis
- Naučte se Excel z Podcastu
- epizoda 2031 rychlejší vlookup Jsem
- podcasting všech tipů v této knize
- klikněte na I v pravém horním rohu
- přejdete na seznam sledovaných
- ahoj vítej zpět u pana. hutník obsazení
- Jsem Bill Jelen, tohle jsem udělal
- video, než je jedno z mých oblíbených
- triky, pokud máte vzhled, pokud ano
- mít hvězdu vlookup s 30 30 50
- sekundy čtyři minuty víte něco
- toto video se vám bude líbit, pokud vaše
- vlookup stick jednu sekundu stačí kliknout na Další
- a přejděte k dalšímu videu II
- vlookup zde se dívá do tabulky
- ze 115 000 položek při 7 000 vlookupech
- použijeme Charlese Williamse
- z rychlého kódu Excel, abyste zjistili, jak dlouho to trvá
- trvá to udělat vlookup v pořádku čtyři
- bod nula devět sekund, to je
- typický vlookup s čárkou nepravdivou na
- konec a tohle všechno přišlo, protože dlouho
- už dávno mě na to nalákal nějaký chlap
- Twitter, který řekl, že by bylo lepší, kdyby
- roztřídili byste vyhledávací tabulku a
- zasílání jsem řekl, že to není vůbec pravda
- nezáleží na tom, zda půjdeme a
- odesílání nebo sestupování nebo úplně
- náhodně se vlookup musí jít podívat
- od položky k položce k položce a tak, když
- třídit tabulku a vidět, že to vlastně trvá
- delší čtyři body osm čtyři sekundy tak
- víte, že není pravda, že třídění
- díky tabulce to půjde rychleji, ale
- opravdu ta věc, která by to mohla udělat
- rychleji, pokud se to nějak dá třídit
- popularita, pokud byste mohli získat to nejlepší
- prodej zboží v horní části seznamu
- dokonce i ty znáš svou nejlepší padesátku
- jaké jsou vaše 50 nejprodávanější položky
- přenést je na začátek seznamu a
- sledujte, že na několik sekund klesne na 0,36
- sekund desetinásobné zlepšení času
- pomocí třídění podle popularity nyní hej pár
- před lety jsem měl to štěstí
- pozval do Amsterdamu, aby představil na
- Excel summit tam a není to jako
- většina mých seminářů, kde jsem jen já
- právě tam byly dvě stopy, takže místnost a
- a pokoj B a já jsme skončili v pokoji
- mluví o vlookups a znovu v místnosti
- hádejte, kdo v té místnosti seděl
- byl Charles Williams v pořádku a Charles
- tady je
- jeho jméno bylo zmíněno prostřednictvím
- zeď, takže přijde se na to dívat
- sleduje moje malé demo tam, kam jdu
- ze čtyř sekund na 0,36 sekundy he
- přijde za mnou a potom řekne, že vsadím
- jsi s tím docela spokojený
- zlepšení
- Říkám, jo, to je plný stan
- vylepšení nyní Charles Charles má
- služba rychlého Excelu náš rozhodovací model
- omezené rozhodovací modely, ve kterých jsme
- půl dne bude analyzovat váš sešit
- a on tvrdí, že je to stovka
- krát rychlejší, najde
- úzká místa Annette a 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
- zvýšení můžete přepnout na vlookup
- s true, ale ohlásí to špatně
- Odpovězte, pokud položky nebyly nalezeny, takže jsme
- ve skutečnosti provede dva vlookupy a
- dva ve sloupci jeden u stolu a
- uvidíme, jestli to, co dostaneme zpět, je dva, pokud
- je bezpečné jít udělat vlookup dovnitř
- společný sloupec, který by jinak měl
- pokud prohlášení říká, že nenalezeno v pořádku
- ach hej díky Charlesi Williamsu za
- učí mě ten úžasný trik a
- děkuji, že jste se zastavili, uvidíte
- příště pro další síťové obsazení od
- MrExcel
Stáhnout soubor
Stáhněte si ukázkový soubor zde: Podcast2031.xlsm
