Toto je ďalší príklad rýchlosti vzorca. Povedzme, že musíš urobiť 12 stĺpcov VLOOKUP. Môžete to zrýchliť použitím jednej MATCH a 12 funkcií INDEX.
Na nasledujúcom obrázku budete musieť urobiť 12 funkcií VLOOKUP pre každé číslo účtu. VLOOKUP je výkonný, ale výpočty si vyžadujú veľa času.
Navyše, vzorec musí byť pri kopírovaní upravený v každej bunke. Tretí argument sa musí zmeniť z 2 na 3 pre február, potom 4 pre marec atď.
Jedným z riešení je pridať riadok s číslami stĺpcov. 3. argument VLOOKUP potom môže ukazovať na tento riadok. Aspoň môžete skopírovať rovnaký vzorec z B4 a vložiť do C4: M4 pred skopírovaním celého súboru.
Ale tu je oveľa rýchlejší prístup. Pridajte nový stĺpec B pomocou tlačidla Kam? ako nadpis. Stĺpec B obsahuje funkciu MATCH. Táto funkcia je veľmi podobná funkcii VLOOKUP: Hľadáte hodnotu v A4 v stĺpci P4: P227. 0 na konci je ako False na konci VLOOKUP. Určuje, že chcete dosiahnuť presnú zhodu. Tu je veľký rozdiel: MATCH sa vráti tam, kde sa nachádza hodnota. Odpoveď z roku 208 hovorí, že A308 je 208. bunkou v rozmedzí P4: P227. Z pohľadu prepočítaného času sú MATCH a VLOOKUP približne rovnaké.
Počujem, na čo myslíš. "Čo je dobré vedieť, kde sa niečo nachádza?" Nikdy som nemal manažéra zavolať a spýtať sa: „V akom rade je to možné?“ “
Zatiaľ čo sa ľudia zriedka pýtajú, v ktorom rade sa niečo nachádza, funkcia INDEX môže túto pozíciu využiť. Nasledujúci vzorec hovorí programu Excel, aby vrátil 208. položku z Q4: Q227.
Pri kopírovaní tohto vzorca sa pole hodnôt posúva vo vyhľadávacej tabuľke. Pre každý riadok robíte jednu ZHODU a 12 INDEXOVÝCH funkcií. Funkcia INDEX je v porovnaní s VLOOKUP neuveriteľne rýchla. Celá sada vzorcov vypočíta o 85% rýchlejšie ako 12 stĺpcov VLOOKUP.
Pozeraj video
- Povedzme, že musíš urobiť 12 stĺpcov VLOOKUP
- Pred stĺpcom vyhľadávanej hodnoty opatrne použite jeden znak dolára
- Pre vyhľadávaciu tabuľku opatrne použite štyri znaky dolára
- Stále tvrdo kódujete argument tretieho stĺpca.
- Jedným z bežných riešení je pridanie radu pomocných buniek s číslom stĺpca.
- Ďalším menej efektívnym riešením je použitie stĺpca (B2) vo vzorci VLOOKUP.
- Urobiť 12 VHLEDÁVANÍ pre každý riadok je však veľmi neefektívne
- Namiesto toho pridajte pomocný stĺpec s hlavičkou WHERE a urobte jednu zhodu.
- ZÁPAS trvá tak dlho ako VÝHĽAD na január.
- Potom môžete použiť 12 funkcií INDEX. V porovnaní s VLOOKUP sú neuveriteľne rýchle.
- INDEX bude ukazovať na jeden stĺpec odpovedí s $ pred riadkami.
- INDEX bude smerovať na pomocný stĺpec so znakom $ pred stĺpcom.
Prepis videa
Naučte sa program Excel z podcastu, epizóda 2028 - Nahradenie mnohých ZÁHĽADOV jedným ZÁPASOM!
Kliknutím na „i“ v pravom hornom rohu sa dostanete do zoznamu skladieb. Budem podcastovať celú túto knihu!
Ahoj, vitaj späť na netcastu, som Bill Jelen! Je to klasický problém, VLOOKUP musíme robiť raz za každý mesiac, však? A tu môžete byť neuveriteľne opatrní, keď trikrát stlačíte kláves F4, aby ste to uzamkli až na stĺpec, a potom stlačte F4, čím uzamknete celý riadok. Ale keď sa dostanete do tohto bodu, je 2, FALSE, že 2 je pevne zakódované, a keď to budete kopírovať, budete musieť upraviť číslo 2 na číslo 3, nie? Jeden neefektívny spôsob, ako to urobiť, ktorý sa mi nepáči, je použitie stĺpca B1. Stĺpec B1 je samozrejme 2, ale keď to budete kopírovať, uvidíte, že sa to zmení na stĺpec C1, ktorý je 3, ale premýšľajte o tom, toto neustále zisťuje číslo stĺpca znova a znova. Takže to, čo vidím, že ľudia robia, a prečo, viete, radšej ako stĺpce, je to, že to Ctrl pretiahneme,vložte čísla 2 - 13 tam hore do pomocnej bunky a potom, keď sa dostaneme k tomuto bodu, ideme hore a určíme číslo tohto stĺpca. Stlačením klávesu F4 dvakrát ho uzamknete na riadok, FALSE a tak ďalej. Ale aj pri tejto metóde je VLOOKUP neuveriteľne neefektívny, pretože tu musí prechádzať všetky tieto položky, kým nenájde A308 a to je údaj B4. Keď sa potom presunie na C4, zabudne, že sa to len pozrelo a pozrelo sa to, znova to začne znova. Takže máte jednu z najpomalších funkcií v celom Exceli, VLOOKUP, FALSE sa robí stále dokola pre tú istú položku.pretože tu musí prechádzať všetky tieto položky, kým nenájde A308 a to je údaj B4. Keď sa potom presunie na C4, zabudne, že sa to len pozrelo a pozrelo sa to, znova to začne znova. Takže máte jednu z najpomalších funkcií v celom Exceli, VLOOKUP, FALSE sa robí stále dokola pre tú istú položku.pretože tu musí prechádzať všetky tieto položky, kým nenájde A308 a to je údaj B4. Keď sa potom presunie na C4, zabudne, že sa to len pozrelo a pozrelo sa to, znova to začne znova. Takže máte jednu z najpomalších funkcií v celom Exceli, VLOOKUP, FALSE sa robí stále dokola pre tú istú položku.
Takže tu je oveľa, oveľa rýchlejšia cesta, ako vložiť pomocný stĺpec a tento pomocný stĺpec mu hovorím Kde? Rovnako ako v prípade, kde je sakra A308? Použijeme a = MATCH, vyhľadáme A308 v prvom riadku tabuľky, stlačíme tam F4,, 0 na presnú zhodu, dobre, hovorí nám to: „Hej, pozri sa na to, je to v rade, 6, ako úžasné je to? “ Ale ako kopírujeme, vidíme, že je to stále na rôznych miestach. Dobre, teraz tento zápas trvá tak dlho, ako trvá januárový VLOOKUP, tam sú dokonca mŕtvi, ale tu je úžasná vec. Odtiaľ už nikdy nebudeme musieť robiť VLOOKUP po zvyšok riadku, môžeme urobiť = INDEX, INDEX hovorí „Tu je rad odpovedí.“ Idem do januárových komôr a veľmi opatrne tu dvakrát stlačím F4, takže to uzamknem na 4: 227,ale Q sa môže meniť, keď sa hýbem. Čiarka, a potom to chce vedieť, aký riadok to bude odpoveď v B4, stlačím 3x F4, aby som dostal $ pred B, v poriadku, napíš to.
Tento vzorec, tieto vzorce INDEX, týchto 12 sa stane za menej, ako je čas potrebný na februárový VLOOKUP, v poriadku. Ak na to dáme časovač Charlesa Williamsa, bude to celé počítať asi 14% času z 12 VLOOKUPOV. Váš manažér nechce vidieť Kde? Fajn, len skryte tento stĺpec, všetko funguje, dobre, toto je nádherný spôsob, ako urýchliť 12 mesiacov alebo 52 týždňov VLOOKUPOV. Dobre, tento tip a ešte oveľa viac tipov sa nachádza v tejto knihe. Kliknite na „i“ v pravom hornom rohu, môžete si kúpiť knihu, elektronická kniha za 10 dolárov, 25 dolárov za tlačenú knihu, v poriadku.
Takže dnes sme mali problém, keď 12 stĺpcov VLOOKUP-u môžete opatrne vložiť $, ale potom musí byť ten tretí argument napevno. Môžete použiť stĺpec (B2), nie som jeho fanúšikom, pretože sú to stovky riadkov * 12 stĺpcov, ktoré to neustále počítajú. Stačí použiť pomocnú bunku v rade, dať čísla 2 - 12 a ukázať na to, že je to stále neefektívne, pretože VLOOKUP po tom, čo vyjde v januári, musí začínať späť začiatkom februára. Odporúčam teda pridať stĺpec s nadpisom „Kde?“ a robiť tam jediný ZÁPAS. Tento ZÁPAS trvá tak dlho ako VLOOKUP za január, ale potom 12 funkcií INDEXU zaberie menej času ako VLOOKUP za február a vy ste orezali kopu času. Opäť opatrne s funkciou $ vo funkcii INDEX na oboch miestach, jeden tesne pred riadkami,a druhý pred stĺpcami, v obidvoch zmiešaný odkaz.
Ahoj, chcem sa ti poďakovať, že si sa zastavil, uvidíme sa nabudúce pri ďalšom netcaste od!
Stiahnuť súbor
Stiahnutie vzorového súboru nájdete tu: Podcast2028.xlsx
