Všeobecný vzorec
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Zhrnutie
Pri veľkých množinách údajov môže byť VLOOKUP s presnou zhodou bolestivo pomalý, ale zosvetlenie VLOOKUP môžete dosiahnuť rýchlo pomocou dvoch VLOOKUPS, ako je vysvetlené nižšie.
Poznámky:
- Ak máte menší súbor údajov, je tento prístup prehnaný. Používajte ho iba s veľkými súbormi údajov, keď sa skutočne počíta rýchlosť.
- Aby tento trik fungoval, musíte údaje triediť podľa vyhľadávacej hodnoty.
- V tomto príklade sa používajú pomenované rozsahy. Ak nechcete používať pomenované rozsahy, použite radšej absolútne odkazy.
VLOOKUP s presnou zhodou je pomalý
Keď použijete VLOOKUP v „režime presnej zhody“ na veľkú množinu údajov, môže to skutočne spomaliť čas výpočtu v hárku. So povedzme 50 000 záznamami alebo 100 000 záznamami môže výpočet trvať niekoľko minút.
Presná zhoda sa nastaví zadaním FALSE alebo nula ako štvrtý argument:
=VLOOKUP(val,data,col,FALSE)
VLOOKUP v tomto režime je pomalý preto, že musí skontrolovať každý jeden záznam v množine údajov, kým sa nenájde zhoda. Toto sa niekedy označuje ako lineárne vyhľadávanie.
Približný zápas VLOOKUP je veľmi rýchly
V režime približnej zhody je VLOOKUP extrémne rýchly. Ak chcete použiť VLOOKUP s približnou zhodou, musíte svoje údaje zoradiť podľa prvého stĺpca (vyhľadávací stĺpec) a pre štvrtý argument zadať hodnotu TRUE:
=VLOOKUP(val,data,col,TRUE)
(VLOOKUP je predvolene nastavený na true, čo je strašidelné predvolené nastavenie, ale to je už iný príbeh).
Pri veľmi veľkých súboroch údajov môže zmena na VLOOKUP s približnou zhodou znamenať dramatické zvýšenie rýchlosti.
Takže, nerozmýšľajte, že? Stačí zoradiť údaje, použiť približnú zhodu a hotovo.
Nie tak rýchlo (heh).
Problém s VLOOKUP v režime „približnej zhody“ je tento: VLOOKUP nezobrazí chybu, ak vyhľadávaná hodnota neexistuje. Horšie však je, že výsledok môže vyzerať úplne normálne, aj keď je úplne nesprávny (pozri príklady). Nie niečo, čo by ste chceli vysvetliť svojmu šéfovi.
Riešením je použiť VLOOKUP dvakrát, oba v režime približnej zhody:
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Vysvetlenie
Prvá inštancia VLOOKUP jednoducho vyhľadá vyhľadávaciu hodnotu ( ID v tomto príklade):
=IF(VLOOKUP(id,data,1,TRUE)=id
a vráti TRUE, len keď sa nájde vyhľadávacia hodnota. V takom prípade
vzorec spustí VLOOKUP znova v režime približnej zhody, aby sa získala hodnota z tejto tabuľky:
VLOOKUP(id,data,col,TRUE)
Neexistuje nebezpečenstvo chýbajúcej vyhľadávacej hodnoty, pretože prvá časť vzorca už bola skontrolovaná, aby sa zabezpečilo, že je tam.
Ak sa vyhľadávacia hodnota nenájde, spustí sa časť funkcie IF „value if FALSE“ a môžete vrátiť ľubovoľnú hodnotu, ktorá sa vám páči. V tomto príklade použijeme NA (), vrátime chybu # N / A, ale môžete tiež vrátiť správu ako „Chýba“ alebo „Nenašiel sa“.
Pamätajte: aby tento trik fungoval, musíte dáta triediť podľa vyhľadávacej hodnoty.
