Üldine valem
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Kokkuvõte
Suurte andmekogumite korral võib täpse vastega VLOOKUP olla valusalt aeglane, kuid saate VLOOKUP-i kiiresti helendada, kasutades kahte VLOOKUPS-i, nagu allpool selgitatud.
Märkused:
- Kui teil on väiksem andmekogum, on see lähenemine liiga suur. Kasutage seda ainult suurte andmekogumite korral, kui kiirus tõesti loeb.
- Selle triki toimimiseks peate andmed sortima otsinguväärtuse järgi.
- Selles näites kasutatakse nimetatud vahemikke. Kui te ei soovi kasutada nimega vahemikke, kasutage selle asemel absoluutseid viiteid.
Täpne vaste VLOOKUP on aeglane
Kui kasutate VLOOKUP-i "täpse vaste režiimis" suure hulga andmete korral, võib see töölehel arvutusaega tõesti aeglustada. Näiteks 50 000 või 100 000 kirje korral võib arvutamine võtta minuteid.
Täpne vaste määratakse neljanda argumendina FALSE või null:
=VLOOKUP(val,data,col,FALSE)
Selles režiimis on VLOOKUP aeglane seetõttu, et see peab andmekogumis kontrollima iga üksikut kirjet, kuni vaste leitakse. Mõnikord nimetatakse seda lineaarseks otsinguks.
Ligikaudne matši VLOOKUP on väga kiire
Ligikaudse mängu režiimis on VLOOKUP ülikiire. Ligikaudse vaste VLOOKUP-i kasutamiseks peate oma andmed sortima esimese veeru (otsinguveeru) järgi ja seejärel määrama 4. argumendi jaoks TÕENE:
=VLOOKUP(val,data,col,TRUE)
(VLOOKUP on vaikimisi tõene, mis on hirmutav vaikimisi, kuid see on teine lugu).
Väga suurte andmekogumite korral võib üleminek ligikaudsele vastele VLOOKUP tähendada dramaatilist kiiruse kasvu.
Niisiis, pole mõtet, eks? Lihtsalt sorteerige andmed, kasutage ligikaudset vastet ja olete valmis.
Mitte nii kiiresti (heh).
VLOOKUP-i probleem "ligikaudse vaste" režiimis on järgmine: VLOOKUP ei kuva viga, kui otsingu väärtust pole olemas. Veelgi hullem, tulemus võib tunduda täiesti normaalne, kuigi see on täiesti vale (vt näiteid). Mitte midagi, mida soovite oma ülemusele selgitada.
Lahendus on kasutada VLOOKUP-i kaks korda, mõlemad korrad ligikaudses sobitusrežiimis:
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Selgitus
VLOOKUPi esimene eksemplar otsib lihtsalt otsitava väärtuse ( selle näite ID ):
=IF(VLOOKUP(id,data,1,TRUE)=id
ja tagastab väärtuse TRUE ainult siis, kui otsinguväärtus on leitud. Sel juhul
töötab valem VLOOKUP uuesti ligikaudses sobitusrežiimis, et tabelist väärtus tuua:
VLOOKUP(id,data,col,TRUE)
Otsingu väärtuse puudumise ohtu pole, kuna valemi esimene osa on juba kontrollitud, et see oleks olemas.
Kui otsingu väärtust ei leita, töötab funktsiooni IF osa "väärtus kui VÄÄR" ja saate tagastada mis tahes väärtuse. Selles näites kasutame NA (), tagastame vea # N / A, kuid võite tagastada ka sellise teate nagu "Puudub" või "Ei leitud".
Pidage meeles: selle triki toimimiseks peate andmed sortima otsinguväärtuse järgi.
