Bendroji formulė
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Santrauka
Turėdami didelius duomenų rinkinius, tiksli atitiktis „VLOOKUP“ gali būti skausmingai lėta, tačiau „VLOOKUP“ galite greitai apšviesti naudodami du „VLOOKUPS“, kaip paaiškinta toliau.
Pastabos:
- Jei turite mažesnį duomenų rinkinį, šis metodas yra per didelis. Su dideliais duomenų rinkiniais naudokite jį tik tada, kai greitis tikrai svarbus.
- Kad šis triukas veiktų, turite rūšiuoti duomenis pagal paieškos vertę.
- Šiame pavyzdyje naudojami pavadinti diapazonai. Jei nenorite naudoti pavadintų diapazonų, naudokite absoliučias nuorodas.
Tikslių rungtynių VLOOKUP yra lėtas
Kai naudojate „VLOOKUP“ „tikslios atitikties režimu“ dideliame duomenų rinkinyje, tai gali sulėtinti skaičiavimo laiką darbalapyje. Jei, tarkime, 50 000 įrašų arba 100 000 įrašų, skaičiavimas gali užtrukti kelias minutes.
Tiksli atitiktis nustatoma pateikiant FALSE arba nulį kaip ketvirtą argumentą:
=VLOOKUP(val,data,col,FALSE)
VLOOKUP šiame režime yra lėta, nes ji turi patikrinti kiekvieną atskirą duomenų rinkinio įrašą, kol bus rasta atitiktis. Tai kartais vadinama linijine paieška.
Apytikslė rungtynių VLOOKUP yra labai greita
Apytikslio rungtynių režimu VLOOKUP yra ypač greitas. Norėdami naudoti apytikslės atitikties VLOOKUP, turite rūšiuoti duomenis pagal pirmąjį stulpelį (paieškos stulpelį), tada 4-ajam argumentui nurodykite TRUE:
=VLOOKUP(val,data,col,TRUE)
(Pagal numatytuosius nustatymus „VLOOKUP“ yra teisinga, o tai yra baisu numatytuoju nustatymu, bet tai jau kita istorija).
Esant labai dideliems duomenų rinkiniams, perėjimas prie apytikslės atitikties VLOOKUP gali reikšti dramatišką greičio padidėjimą.
Taigi, be proto, tiesa? Tiesiog surūšiuokite duomenis, naudokite apytikslę atitiktį ir viskas.
Ne taip greitai (heh).
„VLOOKUP“ problema „apytikslės atitikties“ režime yra tokia: VLOOKUP nerodys klaidos, jei neegzistuoja paieškos vertė. Blogiau, kad rezultatas gali atrodyti visiškai normalus, nors ir visiškai neteisingas (žr. Pavyzdžius). Ne tai, ką norite paaiškinti savo viršininkui.
Sprendimas yra naudoti „VLOOKUP“ du kartus, abu kartus apytiksliame rungtynių režime:
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Paaiškinimas
Pirmasis „VLOOKUP“ egzempliorius tiesiog ieško paieškos vertės ( šiame pavyzdyje pateiktas ID ):
=IF(VLOOKUP(id,data,1,TRUE)=id
ir grąžina TRUE tik radus paieškos vertę. Tokiu atveju
formulė vėl paleidžia „VLOOKUP“ apytiksliame atitikties režime, kad iš šios lentelės gautų vertę:
VLOOKUP(id,data,col,TRUE)
Nėra pavojaus, kad trūks paieškos vertės, nes pirmoji formulės dalis jau patikrinta, ar ji yra.
Jei paieškos reikšmė nerandama, veikia IF funkcijos „value if FALSE“ dalis ir galite grąžinti bet kurią jums patinkančią vertę. Šiame pavyzdyje mes naudojame NA (), mes pateikiame klaidą # N / A, bet taip pat galite grąžinti pranešimą, pvz., „Trūksta“ arba „Nerasta“.
Atminkite: kad šis triukas veiktų, turite rūšiuoti duomenis pagal paieškos vertę.
