Generička formula
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Sažetak
S velikim skupovima podataka, točno podudaranje VLOOKUP-a može biti bolno sporo, ali VLOOKUP-om možete olakšati brzo pomoću dva VLOOKUPS-a, kako je objašnjeno u nastavku.
Bilješke:
- Ako imate manji skup podataka, ovaj je pristup pretjeran. Koristite ga samo s velikim skupovima podataka samo kad se brzina zaista računa.
- Morate sortirati podatke prema vrijednosti pretraživanja kako bi ovaj trik uspio.
- Ovaj primjer koristi imenovane raspone. Ako ne želite koristiti imenovane raspone, umjesto toga upotrijebite apsolutne reference.
VLOOKUP s točnim podudaranjem je spor
Kada VLOOKUP upotrebljavate u "načinu točnog podudaranja" na velikom skupu podataka, to stvarno može usporiti vrijeme izračuna na radnom listu. Uz, recimo, 50 000 zapisa ili 100 000 zapisa, izračunavanje može potrajati nekoliko minuta.
Točno podudaranje postavlja se davanjem FALSE ili nule kao četvrti argument:
=VLOOKUP(val,data,col,FALSE)
Razlog zašto je VLOOKUP u ovom načinu rada spor jer mora provjeriti svaki pojedini zapis u skupu podataka dok se ne pronađe podudaranje. To se ponekad naziva linearnim pretraživanjem.
Približni VLOOKUP koji se podudara vrlo je brz
U načinu približnog podudaranja, VLOOKUP je izuzetno brz. Da biste koristili VLOOKUP s približnim podudaranjem, morate sortirati podatke prema prvom stupcu (stupac pretraživanja), a zatim navesti TRUE za četvrti argument:
=VLOOKUP(val,data,col,TRUE)
(VLOOKUP je prema zadanim postavkama postavljen na true, što je zastrašujuće zadano, ali to je već druga priča).
S vrlo velikim skupovima podataka, promjena u približni VLOOKUP može značiti dramatično povećanje brzine.
Pa, nema smisla, zar ne? Samo sortirajte podatke, upotrijebite približno podudaranje i gotovi ste.
Ne tako brzo (heh).
Problem s VLOOKUP-om u načinu "približnog podudaranja" je sljedeći: VLOOKUP neće prikazati pogrešku ako vrijednost pretraživanja ne postoji. Još gore, rezultat može izgledati sasvim normalno, iako je potpuno pogrešan (vidi primjere). Nije nešto što želite objasniti svom šefu.
Rješenje je koristiti VLOOKUP dva puta, oba puta u približnom načinu podudaranja:
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Obrazloženje
Prva instanca VLOOKUP-a jednostavno traži vrijednost pretraživanja ( id u ovom primjeru):
=IF(VLOOKUP(id,data,1,TRUE)=id
i vraća TRUE samo kada se pronađe vrijednost pretraživanja. U tom slučaju,
formula ponovno pokreće VLOOKUP u načinu približnog podudaranja kako bi dohvatila vrijednost iz te tablice:
VLOOKUP(id,data,col,TRUE)
Ne postoji opasnost od propuštanja vrijednosti pretraživanja jer je prvi dio formule već provjeren da li je tamo.
Ako vrijednost pretraživanja nije pronađena, izvršava se dio "vrijednost ako FALSE" funkcije IF i možete vratiti bilo koju vrijednost koja vam se sviđa. U ovom primjeru koristimo NA () vraćamo grešku # N / A, ali možete i poruku poput "Nedostaje" ili "Nije pronađeno".
Zapamtite: podatke morate razvrstati prema vrijednosti pretraživanja kako bi ovaj trik uspio.
