Tõrke #N/A parandamine funktsioonis VLOOKUP

Selles temas kirjeldatakse peamiseid funktsiooni VLOOKUP vigaste tulemite tekkepõhjuseid ja antakse soovitusi selle asemel funktsioonide INDEX ja MATCH kasutamiseks.

Näpunäide.: Samuti viidatakse teemale Kiirülevaate kaart: funktsiooni VLOOKUP tõrkeotsingu näpunäited, kus on käepärases PDF-vormingu failis kirjeldatud levinumad tõrke #NA põhjused. 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.

Tõrge #NA funktsioonis VLOOKUP. Otsinguväärtus ei ole tabelimassiiv esimene veerg

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

INDEX ja MATCH funktsioonid on hea kasutada 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 tagastusväärtust sisaldavale veerule dünaamiliselt viidata. 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.

  • Funktsioonide INDEX ja MATCH abil leiate vasted paindlikumalt. 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“))

Funktsioone INDEX ja MATCH saab kasutada funktsiooni VLOOKUP asemel

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.

Tõrge N/A funktsioonis VLOOKUP, kui  otsinguväärtus on massiivi väikseimast väärtusest väiksem

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 teil on konkreetse funktsiooniga seotud küsimus?

Postitage oma küsimus Exceli kogukonnafoorumisse

Aidake meil Excelit täiustada

Kas teil on soovitusi selle kohta, kuidas saaksime Exceli järgmist versiooni täiustada? Sellisel juhul tutvuge nende teemadega Excel User Voice’i veebilehel.

Vt ka

Täiendage Office'i kasutamise oskusi
Tutvuge koolitusmaterjalidega
Kasutage uusi funktsioone enne teisi
Liituge Office Insideri programmiga

Kas sellest teabest oli abi?

Täname tagasiside eest!

Täname tagasiside eest! Tundub, et võiksime teid kokku viia ühega meie Office'i tugiagentidest, kes aitab teil probleemi lahendada.

×