Selles teemas kirjeldatakse funktsiooni eksliku tulemi levinumaid VLOOKUP-põhjusi ning soovitusi selle asemel funktsioonide INDEX ja MATCH kasutamiseks.
Näpunäide.: Vaadake ka kiirülevaate kaarti: VLOOKUP tõrkeotsingu näpunäiteid , mis on esitatud mugavas PDF-failis esinevate #NA probleemide levinumate põhjuste kohta. Saate PDF-i teistega jagada või printida oma tarbeks.
Probleem: otsinguväärtus ei ole argumendi tabeli_massiiv esimene veerg
Üks VLOOKUP-i piiranguid on see, et see saab otsida väärtusi ainult tabelimassiivi vasakpoolseimast veerust. Kui teie otsinguväärtus pole massiivi esimesest veerust, kuvatakse tõrge #N/A.
Järgmises tabelis soovime saada väärtuse „Lehtkapsas“ müüdud ühikute arvu.
Tulemiks on tõrge #N/A, kuna otsinguväärtus „Lehtkapsas“ on kuvatud argumendi vahemiku A2:C10 tabeli_massiivi teises veerus (Saadus). Sellisel juhul otsib Excel seda veerust A, mitte veerust B.
Lahendus: võite proovida see lahendada kohandades oma VLOOKUP viitama õigele veerule. Kui see pole võimalik, siis proovige veerge paigutada. See võib olla ka väga otstarbetu, kui teil on mahukad või keerukad arvutustabelid, kui lahtriväärtused on muude arvutuste tulemid või võib-olla ei saa te muudel loogilistel põhjustel veerge ümber tõsta. Lahenduseks on kasutada funktsioone INDEX ja MATCH, mis saavad otsida väärtust veerust, sõltumata selle asukohast otsingutabelis. Vaadake järgmist peatükki.
Funktsioonide INDEX ja MATCH kasutamise kaalumine
Funktsioonid INDEX ja MATCH on hea valik paljudel juhtudel, kui VLOOKUP ei vasta teie vajadustele. Funktsioonide INDEX ja MATCH peamine eelis on see, et saate otsida väärtust veerus, olenemata selle asukohast otsingutabelis. Funktsioon INDEX tagastab väärtuse määratud tabelist/vahemikust vastavalt oma asukohale. Funktsioon MATCH tagastab väärtuse suhtelise asukoha tabelis/vahemikus. Kasutage funktsioone INDEX ja MATCH valemis koos, et otsida väärtust tabelis/massiivis määratledes väärtuse suhtelise asukoha tabelis/massiivis.
Funktsioonidel INDEX ja MATCH on funktsiooni VLOOKUP kasutamise asemel mitmeid eeliseid:
-
Funktsioonide INDEX ja MATCH kasutamisel ei pea tagastusväärtus olema otsinguveeruga samas veerus. Funktsiooni VLOOKUP puhul on see erinev, seal peab tagastusväärtus olema kindlas vahemikus. Miks see on oluline? Funktsiooni VLOOKUP korral peate teadma veeru numbrit, mis sisaldab tagastusväärtust. See ei tundu küll suur asi olevat, kuid kui teil on suur tabel ning peate kõik veerunumbrid üle lugema, siis see on üsna tülikas. Samuti kui peate oma tabelisse veeru lisama või selle sealt eemaldama, peate kõik uuesti üle lugema ja värskendama argumenti veeru_indeks. Funktsioonide INDEX ja MATCH puhul ei ole on vaja midagi kokku lugeda, kuna otsinguveerg erineb tagastusväärtusega veerust.
-
Funktsioonidega INDEX ja MATCH saate määrata kas massiivi rea või veeru või määrata mõlemad. See tähendab, et saate otsida väärtusi nii vertikaalselt kui ka horisontaalselt.
-
Funktsioonide INDEX ja MATCH abil saab otsida väärtusi mis tahes veerust. Erinevalt funktsioonist VLOOKUP, kus saab väärtust otsida vaid tabeli esimesest veerust, töötavad funktsioonid INDEX ja MATCH ka siis, kui teie otsinguväärtus on esimeses, viimases või mis tahes vahepealses veerus.
-
Funktsioonid INDEX ja MATCH võimaldavad dünaamiliselt viidata veergu, mis sisaldab tagastusväärtust. See tähendab, et saate oma tabelile lisada veerge ilma, et funktsioonid INDEX ja MATCH katkeks. Seevastu VLOOKUP katkeb, kui peate tabelile lisama veeru, kuna see viitab tabelile staatiliselt.
-
Funktsioonid INDEX ja MATCH pakuvad vastetega rohkem paindlikkust. Funktsioonid INDEX ja MATCH leiavad täpse vaste või otsinguväärtusest suurema või väiksema väärtuse. Funktsioon VLOOKUP otsib ainult väärtuse lähimat vastet (vaikimisi) või täpset väärtust. Funktsioon VLOOKUP eeldab vaikimisi, et tabelimassiivi esimene veerg on sorditud tähestikuliselt ning kui teie tabel ei ole sel viisil sorditud, tagastab VLOOKUP tabeli esimese lähima vaste, milleks ei pruugi olla andmed, mida otsite.
Süntaks
Funktsioonide INDEX ja MATCH jaoks süntaksi loomiseks peate kasutama funktsiooni INDEX argumenti massiiv/viide ning pesastama sellesse MATCH-i süntaksi. See vorm näeb välja järgmine.
= INDEX(massiiv või viide, MATCH(otsitav_väärtus;massiiv;[vastendustüüp])
Asendame ülaltoodud näites funktsiooni VLOOKUP funktsioonidega INDEX ja MATCH. Süntaks näeb välja järgmine.
=INDEX(C2:C10,MATCH(B13,B2:B10,0))
Lihtsamalt öeldes tähendab see järgmist.
= INDEX (tagasta väärtus lahtrivahemikust C2:C10, mis vastab funktsioonile MATCH („lehtkapsas“, mis on kusagil massiivis lahtrivahemikus B2:B10, kus tagastusväärtus on esimene väärtuse, mis vastab väärtusele „lehtkapsas“))
Valem otsib esimest väärtust lahtrivahemikust C2:C10, mis vastab väärtusele Lehtkapsas (lahtris B7) ja tagastab lahtri C7 väärtuse (100), mis on esimene väärtus, mis vastab väärtusele „Lehtkapsas“.
Probleem: täpset vastet ei leita
Kui vastendustüüp on FALSE ja funktsioon VLOOKUP ei leia andmetest täpset vastet, tagastatakse tõrge #N/A.
Lahendus: kui olete kindel, et asjakohased andmed on teie arvutustabelis olemas ning VLOOKUP ei leia neid, tehke kindlaks, et viidatud lahtrites ei oleks peidetud tühikuid või mitteprinditavaid märke. Veenduge ka, et lahtrid järgiksid õiget andmetüüpi. Näiteks numbritega lahtrid peaksid olema vormindatud kui Arv, mitte Tekst.
Kaaluge lahtrites andmete puhastamiseks funktsioonide CLEAN või TRIM kasutamist.
Probleem: otsinguväärtus on massiivi väikseimast väärtusest väiksem
Kui argument vastendustüüp on seatud väärtusele TRUE ja otsinguväärtus on väiksem kui massiivi väikseim väärtus, kuvatakse tõrge #N/A. Funktsioon TRUE otsib massiivis ligikaudset vastet ning tagastab otsinguväärtusest väiksema lähima väärtuse.
Järgmises näites on otsinguväärtus 100, kuid lahtrivahemikus B2:C10 pole väärtusi, mis on väiksemad kui 100, seega tekib tõrge.
Lahendus:
-
Parandage vastavalt vajadusele otsinguväärtust.
-
Kui te ei saa otsinguväärtust muuta ning vajate vastavuses olevate väärtustega rohkem paindlikkust, kaaluge funktsiooni VLOOKUP asemel funktsioonide INDEX ja MATCH kasutamist (vaadake selle artikli eelmist peatükki). Funktsioonide INDEX ja MATCH abil saate otsida väärtusi, mis on otsinguväärtusest suuremad, väiksemad või sellega võrdsed. Lisateavet selle kohta, miks kasutada funktsiooni VLOOKUP asemel funktsioone INDEX ja MATCH, leiate selle artikli eelmisest peatükist.
Probleem: otsinguveerg ei ole sorditud tõusvas järjestuses
Kui argument vastendustüüp on seatud väärtusele TRUE ja üks teie otsinguveergudest ei ole sorditud tõusvas järjestuses (A–Z), kuvatakse tõrge #N/A.
Lahendus:
-
Määrake funktsioon VLOOKUP otsima täpset vastet. Selleks määrake argumendi vastendustüüp väärtuseks FALSE. Väärtuse FALSE jaoks ei ole sortimine vajalik.
-
Kasutage sortimata tabelist väärtuse otsimiseks funktsioone INDEX ja MATCH.
Probleem: väärtus on suur ujukomaarv
Kui teil on lahtrites ajaväärtused või suuri kümnendarve, tagastab Excel ujukoma täpsuse tõttu tõrke #N/A. Ujukomaarvud on arvud, mis järgnevad pärast kümnendkohta. (Excel talletab ajaväärtused ujukomaarvuna). Excel ei saa talletada väga suure ujukomaga arve, seega tuleks funktsiooni õigeks toimimiseks ümardada ujukomaarvud viie kümnendkohani pärast koma.
Lahendus: ümardage arvud funktsiooni ROUND abil viie kümnendkohani pärast koma.
Kas vajate rohkem abi?
Kui teil on küsimusi, saate need esitada Exceli tehnikakogukonnafoorumis, kus teile vastavad asjatundjad, või teistele kasutajatele kogukonnafoorumis.
