To je še en primer hitrosti formule. Recimo, da morate narediti 12 stolpcev VLOOKUP. Hitreje lahko z eno funkcijo MATCH in 12 INDEX.
Na naslednji sliki boste morali opraviti 12 funkcij VLOOKUP za vsako številko računa. VLOOKUP je zmogljiv, vendar za izračun potrebuje veliko časa.
Poleg tega je treba formulo med kopiranjem urejati v vsaki celici. Tretji argument se mora spremeniti z 2 na 3 za februar, nato za 4 za marec itd.
Ena rešitev je dodati vrstico s številkami stolpcev. Nato lahko tretji argument VLOOKUP kaže na to vrstico. Vsaj isto kopijo lahko kopirate iz B4 in prilepite v C4: M4, preden kopirate celoten niz.
Tu pa je veliko hitrejši pristop. Dodajte nov stolpec B s Kje? kot naslov. Stolpec B vsebuje funkcijo MATCH. Ta funkcija je zelo podobna VLOOKUP: V stolpcu P4: P227 iščete vrednost v A4. 0 na koncu je kot False na koncu VLOOKUP-a. Določa, da želite natančno ujemanje. Tu je velika razlika: MATCH vrne, kjer je vrednost najdena. Odgovor 208 pravi, da je A308 208. celica v območju P4: P227. Iz časovnega zornega kota sta MATCH in VLOOKUP približno enaka.
Slišim, kaj razmišljate. »Kaj je dobro vedeti, kje se kaj nahaja? Nikoli nisem poklical upravitelja in ga vprašal: "V kateri vrstici je to terjatev?"
Medtem ko se ljudje redko vprašamo, v kateri vrstici je nekaj, funkcija INDEX lahko uporabi ta položaj. Naslednja formula pravi, da Excel vrne 208. element iz četrtega četrtletja: Q227.
Ko kopirate to formulo, se niz vrednosti premika po iskalni tabeli. Za vsako vrstico izvajate eno funkcijo MATCH in 12 INDEX. Funkcija INDEX je v primerjavi z VLOOKUP neverjetno hitra. Celoten nabor formul izračuna 85% hitreje kot 12 stolpcev VLOOKUP.
Oglejte si video
- Recimo, da morate narediti 12 stolpcev VLOOKUP
- Pred stolpcem iskalne vrednosti previdno uporabite znak za en dolar
- Za iskalno tabelo previdno uporabite znake štirih dolarjev
- Še vedno težko kodirate argument tretjega stolpca.
- Ena pogostih rešitev je dodati vrstico pomožnih celic s številko stolpca.
- Druga manj učinkovita rešitev je uporaba stolpca (B2) znotraj formule VLOOKUP.
- Toda izvajanje 12 VLOOKUP za vsako vrstico je zelo neučinkovito
- Namesto tega dodajte pomožni stolpec z naslovom WHERE in izvedite eno tekmo.
- TEKME traja toliko časa kot VLOOKUP za januar.
- Nato lahko uporabite 12 funkcij INDEX. Ti so v primerjavi z VLOOKUP neverjetno hitri.
- INDEX bo kazal na en stolpec odgovorov z $ pred vrsticami.
- INDEX bo kazal na pomožni stolpec z $ pred stolpcem.
Video zapis
Naučite se Excel iz podcasta, epizoda 2028 - Zamenjava številnih VLOOKUP-ov z enim MATCH!
Kliknite ta »i« v zgornjem desnem kotu, da pridete do seznama predvajanja, celotno knjigo bom podkastiral!
Hej, dobrodošli nazaj v oddaji, jaz sem Bill Jelen! No, to je klasična težava, VLOOKUP moramo opraviti enkrat na mesec, kajne? In tukaj ste lahko neverjetno previdni, da pritisnete F4 3-krat, da to zaklenete do stolpca, in nato pritisnete F4, ko zaklenete celo vrstico. Toda ko pridete do te točke, je 2, FALSE, da je 2 težko kodirano, in ko to kopirate, boste morali 2 urediti v 3, kajne? Zdaj je en neučinkovit način za to, način, ki mi ni všeč, uporaba stolpca B1. Stolpec B1 je seveda 2, toda ko to kopirate, glejte, da se bo spremenil v stolpec C1, ki je 3, vendar pomislite, to vedno znova ugotavlja številko stolpca. Torej, kar vidim, da ljudje počnejo, in zakaj, veste, raje imam več kot stolpce, je to, da bomo Ctrl povlekli,postavimo številke 2-13 tam v pomožno celico, nato pa, ko pridemo do te točke, gremo gor in določimo to številko stolpca. Dvakrat pritisnite F4, da ga zaklenete na vrstico,, FALSE in tako naprej. Toda tudi s to metodo je VLOOKUP neverjetno neučinkovit, saj mora tu preiskati vse te elemente, dokler ne najde A308 in to je slika B4. Ko se nato premakne na C4, pozabi, da je šel in pogledal, in se začne znova, v redu. Tako imate eno najpočasnejših funkcij v celotnem Excelu, VLOOKUP, FALSE, ki se vedno znova in znova izvaja za isti element.ker mora iti po vseh teh predmetih tukaj, dokler ne najde A308 in to je slika B4. Ko se nato premakne na C4, pozabi, da je šel in pogledal, in se začne znova, v redu. Tako imate eno najpočasnejših funkcij v celotnem Excelu, VLOOKUP, FALSE, ki se vedno znova in znova izvaja za isti element.ker mora iti po vseh teh predmetih tukaj, dokler ne najde A308 in to je slika B4. Ko se nato premakne na C4, pozabi, da je šel in pogledal, in se začne znova, v redu. Tako imate eno najpočasnejših funkcij v celotnem Excelu, VLOOKUP, FALSE, ki se vedno znova in znova izvaja za isti element.
Torej, tukaj je veliko, veliko hitrejša pot, vstavili bomo pomožni stolpec in temu pomožnemu stolpcu pravim Kam? Kot pri A308? Uporabili bomo = MATCH, v prvi vrstici tabele poiščemo A308, tam pritisnite F4,, 0 za natančno ujemanje, v redu, pove nam, da "Hej, poglej, to je v vrstici, 6, kako super je to? " Ko pa kopiramo, vidite, da je ves čas na različnih mestih. V redu, zdaj ta tekma traja toliko časa, kot traja januarski VLOOKUP, tam so celo mrtvi, toda tukaj je neverjetna stvar. Od tam nam nikoli ni treba narediti VLOOKUP-a do konca vrstice, lahko bi naredili = INDEX, INDEX pravi: "Tukaj je vrsta odgovorov." Odšel bom v januarske celice in tukaj zelo previdno dvakrat pritisnil F4, da ga zaprem na 4: 227,vendar se Q lahko spreminja, ko se premikam. Vejica in potem želi vedeti, v kateri vrstici, no, to bo odgovor v B4, pritisnil bom trikrat F4, da dobim $ pred B, v redu, kopirajte to čez.
Ta formula, te formule INDEX, teh 12 se bodo zgodile v manj kot času, potrebnem za februarski VLOOKUP, prav. Če na to postavimo časovnik Charlesa Williamsa, bo celotna stvar izračunala približno 14% časa 12 VLOOKUP-ov. Vaš upravitelj noče videti kje? V redu, samo skrij to rubriko, vse deluje, prav, to je čudovit način za pospešitev 12-mesečnega ali 52-tedenskega VLOOKUP-a. V redu, ta nasvet in toliko več nasvetov je v tej knjigi. Kliknite »i« v zgornjem desnem kotu, tam lahko kupite knjigo, e-knjigo 10 USD, za tiskano knjigo 25 USD, v redu.
Torej, danes smo imeli težavo, ko lahko v 12 stolpcev VLOOKUP-a previdno vnesete $, potem pa je treba tretji argument še vedno trdo kodirati. Lahko uporabite stolpec (B2), nisem navdušen nad tem, ker je na stotine vrstic * 12 stolpcev, kjer to vedno znova izračunamo. Samo uporabite pomožno celico v vrsti, postavite številki 2-12 in pokažite na to, vendar je še vedno neučinkovit, ker mora VLOOKUP po tem, ko ugotovi januar, začeti že na začetku februarja. Zato priporočam, da dodate stolpec z naslovom "Kam?" in tam naredite en sam TEKME. Ta TEKMA traja tako dolgo kot VLOOKUP za januar, potem pa bo 12 funkcij INDEX vzelo manj časa kot VLOOKUP za februar, vi pa ste obrezali cel kup časa. Še enkrat, previdno s funkcijo $ v funkciji INDEX na obeh mestih, eno tik pred vrsticami,drugi pa pred stolpci, mešan sklic v obeh.
Hej, zahvaljujem se vam, da ste se ustavili, se vidimo naslednjič za novo oddajo od!
Prenesite datoteko
Prenesite vzorčno datoteko tukaj: Podcast2028.xlsx
