Tõrke #N/A parandamine funktsioonis VLOOKUP

Teemas kirjeldatakse levinumaid põhjuseid, miks võib esineda funktsiooniga VLOOKUP tõrkeid.

Näpunäide. : Viide Kiirülevaate kaart: funktsiooni VLOOKUP tõrkeotsingu näpunäited, kus on käepärases PDF-vormingu failis kirjeldatud levinumad funktsiooni VLOOKUP 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 olulisemaid piiranguid on see, et see saab otsida väärtusi ainult tabelimassiivi vasakpoolseimast veerust. Seega 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

Tõrge tekib, kuna otsinguväärtus „Lehtkapsas“ on teises veerus (Saadus), aga argumendi tabeli_massiiv vahemik A2:C10, seega 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 ümber paigutada. See võib olla 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.

Funktsiooni VLOOKUP asemel funktsioonide INDEX ja MATCH kasutamine

Funktsioone INDEX ja MATCH saab kasutada siis, kui VLOOKUP ei vasta teie vajadustele. Funktsioonide INDEX ja MATCH suurimaks eeliseks on see, et saate otsida väärtust veerus, olenemata selle asukohast otsingutabelis. Funktsioon INDEX tagastab väärtuse määratud tabelist/vahemikust vastavalt oma asukohale ja funktsioon MATCH tagastab väärtuse suhtelise asukoha tabelis/vahemikus. Kasutades funktsioone INDEX ja MATCH valemis koos, saate väärtust otsida tabelis/massiivis määratledes väärtuse suhtelise asukoha tabelis/massiivis.

Funktsioonide INDEX ja MATCH kasutamisel on funktsiooni VLOOKUP ees mitmeid eeliseid:

  • Erinevalt VLOOKUP-ist, kus tagastusväärtus peab olema määratud vahemikus, ei pea funktsioonide INDEX ja MATCH kasutamisel olema tagastusväärtus otsinguveeruga samas veerus. 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 peaksite midagi oma tabelisse lisama või 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 isegi 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 ning INDEX ja MATCH ei katke. 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 ning 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. Nii et see näeb välja umbes selline.

= 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 (soovin tagastusväärtust 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äärtuseleLehtkapsas (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 argument 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, veenduge, 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 arvuna, mitte tekstina.

Kasutage lahtrites andmete puhastamiseks funktsioone CLEAN või TRIM.

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. 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. (Pange tähele, et Excel talletab ajaväärtused ujukomaarvuna). Excel ei saa talletada 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 kogukonnafoorumis

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õrke #N/A parandamine

VLOOKUP: tõrketa #NA (inglise keeles)

Funktsioonid HLOOKUP, VLOOKUP, LOOKUP tagastavad Excelis valesid väärtusi (inglise keeles)

Ujukomaga arvutus võib anda Excelis valesid tulemeid (inglise keeles)

Kiirülevaate kaart: funktsiooni VLOOKUP näpunäited

VLOOKUP

Exceli valemite ülevaade

Katkenud valemite vältimine

Valemivigade tuvastamine

Exceli funktsioonid (tähestikuliselt)

Exceli funktsioonid (kategooriate kaupa)

Täiendage oma 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.

×