Toto je další příklad rychlosti vzorce. Řekněme, že musíte udělat 12 sloupců SVYHLEDAT. Můžete to zrychlit pomocí jedné funkce MATCH a 12 INDEX.
Na následujícím obrázku budete muset udělat 12 funkcí VLOOKUP pro každé číslo účtu. VLOOKUP je výkonný, ale výpočty zabere hodně času.
Kromě toho musí být vzorec upraven v každé buňce při kopírování. Třetí argument se musí změnit z 2 na 3 pro únor, poté 4 pro březen atd.
Jedním řešením je přidat řádek s čísly sloupců. Potom třetí argument VLOOKUP může ukazovat na tento řádek. Alespoň můžete zkopírovat stejný vzorec z B4 a vložit do C4: M4 před zkopírováním celé sady.
Ale tady je mnohem rychlejší přístup. Přidejte nový sloupec B pomocí funkce Kde? jako nadpis. Sloupec B obsahuje funkci MATCH. Tato funkce je velmi podobná funkci VLOOKUP: Hledáte hodnotu v A4 ve sloupci P4: P227. 0 na konci je jako False na konci VLOOKUP. Určuje, že chcete přesnou shodu. Zde je velký rozdíl: MATCH se vrátí tam, kde je nalezena hodnota. Odpověď 208 říká, že A308 je 208. buňka v rozsahu P4: P227. Z časového hlediska přepočítání jsou MATCH a VLOOKUP přibližně stejné.
Slyším, na co myslíš. "Jaké dobré je vědět, kde se něco nachází?" Nikdy jsem nezavolal manažera a nezeptal se: „V jaké řadě je to pohledávka?“ “
Zatímco lidé se jen zřídka ptají, v jakém řádku je něco, funkce INDEX může tuto pozici použít. Následující vzorec říká aplikaci Excel, aby vrátila 208. položku z Q4: Q227.
Při kopírování tohoto vzorce se pole hodnot pohybuje ve vyhledávací tabulce. Pro každý řádek děláte jednu MATCH a 12 INDEX funkcí. Funkce INDEX je ve srovnání s VLOOKUP neuvěřitelně rychlá. Celá sada vzorců vypočítá o 85% rychleji než 12 sloupců VLOOKUP.
Sledovat video
- Řekněme, že musíte udělat 12 sloupců SVYHLEDAT
- Před sloupcem vyhledávací hodnoty opatrně použijte jeden znak dolaru
- Pro vyhledávací tabulku opatrně použijte čtyři dolary
- Stále tvrdě kódujete argument třetího sloupce.
- Jedním běžným řešením je přidání řady pomocných buněk s číslem sloupce.
- Dalším méně efektivním řešením je použití COLUMN (B2) uvnitř vzorce VLOOKUP.
- Ale dělat 12 VLOOKUP pro každý řádek je velmi neefektivní
- Místo toho přidejte pomocný sloupec s nadpisem WHERE a proveďte jednu shodu.
- ZÁPAS trvá tak dlouho jako SVĚTOVÝ VÝHLED na leden.
- Poté můžete použít 12 funkcí INDEX. Ve srovnání s VLOOKUP jsou neuvěřitelně rychlé.
- INDEX bude ukazovat na jeden sloupec odpovědí s $ před řádky.
- INDEX bude ukazovat na pomocný sloupec s $ před sloupcem.
Přepis videa
Naučte se Excel z podcastu, epizoda 2028 - Nahrazení mnoha VLOOKUPŮ jedním ZÁPASEM!
Kliknutím na „i“ v pravém horním rohu se dostanete do seznamu skladeb. Podcasting celé této knihy!
Hej, vítej zpět na netcastu, jsem Bill Jelen! Je to klasický problém, VLOOKUP musíme dělat jednou za každý měsíc, že? Tady můžete být neuvěřitelně opatrní, když třikrát stisknete klávesu F4, abyste ji uzamkli dolů ke sloupci, a poté stiskněte klávesu F4, jakmile uzamknete celý řádek. Ale když se dostanete do tohoto bodu, 2, FALSE, že 2 je napevno, a jak to kopírujete napříč, budete muset upravit 2 na 3, že? Jeden neefektivní způsob, jak to udělat, způsob, který se mi nelíbí, je použít sloupec B1. Sloupec B1 je samozřejmě 2, ale když to napíšete, uvidíte, že se to změní na sloupec C1, což je 3, ale přemýšlejte o tom, toto neustále zjišťuje číslo sloupce znovu a znovu. Takže to, co vidím, dělají lidé a proč, víte, dáváte přednost více než sloupcům, je to, že to Ctrl přetáhneme,vložte čísla 2–13 tam nahoru do pomocné buňky a poté, když se dostaneme k tomuto bodu, jdeme nahoru a určíme toto číslo sloupce. Stisknutím klávesy F4 dvakrát ji uzamknete na řádek, FALSE atd. Ale i s touto metodou je VLOOKUP neuvěřitelně neefektivní, protože zde musí procházet všechny tyto položky, dokud nenajde A308 a to je číslo B4. Když se pak přesune na C4, zapomene, že to prostě šlo a podívalo se, a začíná to znovu, dobře. Takže máte jednu z nejpomalejších funkcí v celém Excelu, VLOOKUP, FALSE se provádí znovu a znovu a znovu pro stejnou položku.protože zde musí procházet všechny tyto položky, dokud nenajde A308 a to je číslo B4. Když se pak přesune na C4, zapomene, že to prostě šlo a podívalo se, a začíná to znovu, dobře. Takže máte jednu z nejpomalejších funkcí v celém Excelu, VLOOKUP, FALSE se provádí znovu a znovu a znovu pro stejnou položku.protože zde musí procházet všechny tyto položky, dokud nenajde A308 a to je číslo B4. Když se pak přesune na C4, zapomene, že to prostě šlo a podívalo se, a začíná to znovu, dobře. Takže máte jednu z nejpomalejších funkcí v celém Excelu, VLOOKUP, FALSE se provádí znovu a znovu a znovu pro stejnou položku.
Tady je tedy mnohem, mnohem rychlejší cesta, vložíme pomocný sloupec a tomuto pomocnému sloupci to říkám Kde? Jako kde je sakra A308? Použijeme a = MATCH, vyhledáme A308 v prvním řádku tabulky, stiskneme F4 tam,, 0 pro přesnou shodu, dobře, říká nám to: „Hele, podívej se, to je v řadě, 6, jak úžasné je to? “ Ale jak kopírujeme, vidíte, je to stále na různých místech. Dobře, teď tento zápas trvá tak dlouho, jak trvá lednový VLOOKUP, tam jsou dokonce mrtví, ale tady je ta úžasná věc. Odtamtud už nikdy nebudeme muset VLOOKUP dělat po zbytek řádku, můžeme prostě udělat = INDEX, INDEX říká „Tady je řada odpovědí.“ Půjdu do lednových buněk a velmi opatrně zde dvakrát stisknu F4, takže to uzamknu na 4: 227,ale Q se může měnit, jak se pohybuji. Čárka, a pak to chce vědět, jaký řádek, to bude odpověď v B4, stisknu třikrát F4, abych dostal $ před B, dobře, zkopíruj to napříč.
Tento vzorec, tyto vzorce INDEX, těchto 12 se uskuteční za méně než čas, který by trvalo únorový VLOOKUP, dobře. Pokud na to dáme časovač Charlese Williamse, tato celá věc vypočítá přibližně 14% času 12 VLOOKUPů. Váš manažer nechce vidět Kde? Dobře, prostě skryjte tento sloupec, vše funguje, dobře, je to krásný způsob, jak zrychlit 12 měsíců nebo 52 týdnů VLOOKUPů. Dobře, tento tip a tolik dalších tipů je v této knize. Klikněte na „i“ v pravém horním rohu, můžete si knihu koupit, 10 $ e-book, 25 $ za tištěnou knihu, dobře.
Takže dnes jsme měli problém, kde 12 sloupců VLOOKUP můžete opatrně vložit $, ale ten třetí argument musí být stále napevno. Dalo by se použít sloupec (B2), nejsem fanouškem toho, protože tam jsou stovky řádků * 12 sloupců, kde se to počítá znovu a znovu. Stačí použít pomocnou buňku v řadě, dát čísla 2-12 a ukázat na to, je to stále neefektivní, protože VLOOKUP poté, co zjistí leden, musí začít zpět na začátku února. Doporučuji tedy přidat sloupec s nadpisem „Kde?“ a dělat tam jeden ZÁPAS. Že ZÁPAS trvá tak dlouho jako VLOOKUP za leden, ale pak 12 funkcí INDEX bude trvat méně času než VLOOKUP za únor a vy jste ořezali spoustu času. Opět opatrně s funkcí $ ve funkci INDEX na obou místech, jeden těsně před řádky,a druhý před sloupci, smíšený odkaz v obou z nich.
Hej, chci ti poděkovat, že jsi se zastavil, uvidíme se příště na dalším netcastu od!
Stáhnout soubor
Stáhněte si ukázkový soubor zde: Podcast2028.xlsx
