Splošna formula
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Povzetek
Z velikimi nabori podatkov je VLOOKUP z natančnim ujemanjem lahko boleče počasen, vendar lahko VLOOKUP hitro posvetlite z uporabo dveh VLOOKUPS, kot je razloženo spodaj.
Opombe:
- Če imate manjši nabor podatkov, je ta pristop pretiran. Uporabljajte ga samo pri velikih naborih podatkov, ko hitrost resnično šteje.
- Če želite, da ta trik deluje, morate podatke razvrstiti po vrednosti iskanja.
- Ta primer uporablja imenovane obsege. Če ne želite uporabljati imenovanih obsegov, raje uporabite absolutne reference.
VLOOKUP z natančnim ujemanjem je počasen
Ko VLOOKUP uporabljate v "načinu natančnega ujemanja" za velik nabor podatkov, lahko resnično upočasni čas izračuna na delovnem listu. Z recimo 50.000 zapisi ali 100.000 zapisi lahko izračun traja nekaj minut.
Natančno ujemanje se nastavi tako, da se kot četrti argument navede FALSE ali nič:
=VLOOKUP(val,data,col,FALSE)
Razlog, da je VLOOKUP v tem načinu počasen, je, da mora preveriti vsak posamezen zapis v naboru podatkov, dokler ne najde ujemanja. To se včasih imenuje linearno iskanje.
Približno ujemanje VLOOKUP je zelo hitro
V načinu približnega ujemanja je VLOOKUP izjemno hiter. Če želite uporabiti VLOOKUP s približnim ujemanjem, morate podatke razvrstiti po prvem stolpcu (iskalni stolpec), nato za četrti argument določiti TRUE:
=VLOOKUP(val,data,col,TRUE)
(VLOOKUP je privzeto nastavljen na true, kar je strašljivo privzeto, vendar je to že druga zgodba).
Z zelo velikimi nabori podatkov lahko prehod na VLOOKUP s približnim ujemanjem pomeni dramatično povečanje hitrosti.
Torej, brez pomislekov, kajne? Samo razvrstite podatke, uporabite približno ujemanje in končali ste.
Ne tako hitro (heh).
Težava z VLOOKUP-om v načinu »približno ujemanje« je ta: VLOOKUP ne bo prikazal napake, če iskalna vrednost ne obstaja. Še huje, rezultat je lahko videti povsem normalen, čeprav je popolnoma napačen (glej primere). Ni nekaj, kar bi želeli razložiti svojemu šefu.
Rešitev je, da VLOOKUP uporabite dvakrat, obakrat v načinu približnega ujemanja:
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Pojasnilo
Prvi primerek VLOOKUP preprosto poišče iskalno vrednost ( id v tem primeru):
=IF(VLOOKUP(id,data,1,TRUE)=id
in vrne TRUE le, ko je najdena vrednost iskanja. V tem primeru
formula ponovno zažene VLOOKUP v načinu približnega ujemanja, da pridobi vrednost iz te tabele:
VLOOKUP(id,data,col,TRUE)
Ne obstaja nevarnost manjkajoče vrednosti iskanja, saj je prvi del formule že preverjen, da se prepriča, ali je tam.
Če iskalne vrednosti ni mogoče najti, se v funkciji IF zažene del "value if FALSE" in lahko vrnete poljubno vrednost. V tem primeru uporabljamo NA (), vrnemo napako # N / A, lahko pa vrnete tudi sporočilo, kot je "Manjka" ali "Ni najdeno".
Ne pozabite: če želite, da ta trik deluje, morate podatke razvrstiti po vrednosti iskanja.
