Kako popraviti napako #N/A v funkciji VLOOKUP

V tej temi so opisani najpogostejši razlogi za neuspešnost funkcije VLOOKUP.

Namig : Preberite Kartica s kratkimi navodili: namigi za odpravljanje težav s funkcijo VLOOKUP, ki opisuje pogoste razloge za težave #N/V s funkcijo VLOOKUP v priročni datoteki PDF (Portable Document Format). Datoteko PDF lahko daste v skupno rabo z drugimi ali pa jo natisnete za lastno uporabo.

Težava: Iskana vrednost ni v prvem stolpcu v argumentu matrika_tabele

Ena od največjih omejitev funkcije VLOOKUP je, da lahko funkcija išče le vrednosti v skrajno levem stolpcu v matriki tabele. Če vaša iskana vrednost ni v prvem stolpcu matrike, bo prikazana napaka #N/V.

V tej tabeli želimo pridobiti število prodanih enot za ohrovt.

Napaka #NA v funkciji VLOOKUP: Iskane vrednosti ni v prvem stolpcu matrike tabele

Do napake pride, ker je iskana vrednost »ohrovt« v drugem stolpcu (pridelki) v argumentu table_array A2:C10, tako da jo Excel išče v stolpcu A, ne pa v stolpcu B.

Rešitev: To lahko poskusite odpraviti s prilagajanjem funkcije VLOOKUP tako, da se sklicuje na pravilen stolpec. Če to ni mogoče, poskusite pomikati stolpce. To je lahko zelo nepraktično, če imate velike ali zapletene preglednice, v katerih so vrednosti celic rezultati drugih izračunov, ali pa, če obstajajo drugi logični razlogi, zakaj ni mogoče premikati stolpcev. Kot rešitev lahko uporabite kombinacijo funkcij INDEX in MATCH, s katerima lahko poiščete vrednost v stolpcu, ne glede na položaj vrednosti v iskalni tabeli.

Uporabite INDEX/MATCH namesto VLOOKUP

Funkcijo INDEX/MATCH lahko uporabite, če VLOOKUP ne ustreza vašim potrebam. Največja prednost funkcije INDEX/MATCH je v tem, da lahko vrednost poiščete v stolpcu na katerem koli mestu v iskalni tabeli. Funkcija INDEX vrne vrednost iz določene tabele/obsega na podlagi položaja, funkcija MATCH pa vrne relativni položaj vrednosti v tabeli/obsegu. Če uporabljate funkciji INDEX in MATCH skupaj v formuli, lahko poiščete vrednost v tabeli/matriki tako, da določite relativni položaj vrednosti v tabeli/matriki.

Obstajajo številne prednosti uporabe funkcij INDEX/MATCH namesto funkcije VLOOKUP:

  • Pri funkcijah INDEX in MATCH ni zahtevano, da je vrnjena vrednost v istem stolpcu, kot je iskani stolpec, medtem ko mora biti vrnjena vrednost pri funkciji VLOOKUP v določenem obsegu. Zakaj je to pomembno? Za funkcijo VLOOKUP morate poznati številko stolpca, ki vključuje vrnjeno vrednost. Čeprav se to ne zdi zaskrbljujoče, je lahko težavno, če imate veliko tabelo in morate šteti število stolpcev. Poleg tega je treba znova prešteti in posodobiti argument »col_index_num«, če želite dodati/odstraniti vrednost v tabeli. Pri funkcijah INDEX in MATCH ni zahtevano štetje, saj iskani stolpec ni stolpec z vrnjeno vrednostjo.

  • Pri funkcijah INDEX in MATCH lahko določite vrstico ali stolpec v matriki ali celo določite oboje. To pomeni, da lahko iščete vrednosti navpično in vodoravno.

  • Funkciji INDEX in MATCH se lahko uporabita za iskanje vrednosti v poljubnih stolpcih. V nasprotju s funkcijo VLOOKUP, s katero lahko poiščete le vrednost v prvem stolpcu v tabeli, bosta funkciji INDEX in MATCH delovali, če je iskana vrednost v prvem stolpcu, zadnjem stolpcu ali kjer koli vmes.

  • Funkciji INDEX in MATCH ponujata prilagodljivost pri ustvarjanju dinamičnega sklica na stolpec, ki vsebuje vrnjeno vrednost. To pomeni, da lahko v tabelo dodate stolpce, ne da bi prišlo do neveljavnosti funkcij INDEX in MATCH. Nasprotno pa pride do neveljavnosti funkcije VLOOKUP, če morate v tabelo dodati stolpec, saj vsebuje statične sklice na tabelo.

  • INDEX in MATCH ponujata več prilagodljivosti pri ujemanjih. Funkciji INDEX in MATCH lahko poiščeta natančno ujemanje ter večjo ali manjšo vrednost od iskane vrednosti. VLOOKUP poišče le največji približek vrednosti (privzeto) ali natančno vrednost. Funkcija VLOOKUP tudi privzeto predvideva, da je v matriki tabele prvi stolpec razvrščen po abecedi, in če vaša tabela ni tako nastavljena, bo funkcija VLOOKUP vrnila prvi največji približek v tabeli; ta približek pa morda ne bodo podatki, ki jih iščete.

Sintaksa

Če želite ustvariti sintakso za funkcijo INDEX/MATCH, morate uporabiti argument matrike/sklica iz funkcije INDEX in vanjo ugnezditi sintakso funkcije MATCH. Zato je videti tako:

=INDEX(matrika ali sklic, MATCH(lookup_value,lookup_array,[match_type])

Uporabimo funkcijo INDEX/MATCH, da nadomestimo funkcijo VLOOKUP v zgornjem primeru. Sintaksa bo videti tako:

=INDEX(C2:C10,MATCH(B13,B2:B10,0))

Povedano preprosto, to pomeni:

=INDEX(želim vrnjeno vrednost iz C2:C10, ki se bo MATCH(ujemala z)(ohrovt, ki je nekje v matriki B2:B10, pri čemer je vrnjena vrednost prva vrednost, ki ustreza besedi ohrovt))

Funkciji INDEX in MATCH je mogoče uporabiti kot zamenjavo za funkcijo VLOOKUP

Formula poišče prvo vrednost v obsegu C2:C10, ki ustreza besedi ohrovt (v B7) in vrne vrednost iz celice C7 (100), ki je prva vrednost, ki se ujema z besedo ohrovt.

Težava: Natančno ujemanje ni najdeno

Če je argument range_lookup FALSE in funkcija VLOOKUP ne more najti natančnega ujemanja v vaših podatkih, vrne napako #N/V.

Rešitev: Če ste prepričani, da relevantni podatki obstajajo v vaši preglednici, funkcija VLOOKUP pa jih ne zajame, se prepričajte, da sklicevane celice ne vsebujejo skritih presledkov ali nenatisljivih znakov. Poleg tega se prepričajte, da celice upoštevajo pravilno vrsto podatkov. Na primer, celice s številkami je treba oblikovati kot »Število« in ne kot »Besedilo«.

Za čiščenje podatkov v celicah uporabite funkcijo CLEAN ali TRIM.

Težava: Iskana vrednost je manjša od najmanjše vrednosti v matriki

Če je argument range_lookup nastavljen na TRUE, iskana vrednost pa je manjša od najmanjše vrednosti v matriki, je prikazana napaka #N/V. TRUE išče približno ujemanje v matriki in vrne največjo približno vrednost, ki je manjša od iskane vrednosti.

V tem primeru je iskana vrednost 100, vendar v obsegu B2:C10 ni vrednosti, ki so manjše od 100, zato je vrnjena napaka.

Napaka N/A v funkciji VLOOKUP, ko je iskana vrednost manjša od najmanjše vrednosti v matriki

Rešitev:

  • Po potrebi popravite iskano vrednost.

  • Če iskane vrednosti ne morete spremeniti in potrebujete večjo prilagodljivost pri ujemajočih se vrednostih, premislite o uporabi funkcije INDEX/MATCH namesto funkcije VLOOKUP. S funkcijo INDEX/MATCH lahko poiščete vrednosti, ki so večje, manjše ali enake kot iskana vrednost. Če želite več informacij o uporabi funkcije INDEX/MATCH namesto funkcije VLOOKUP, glejte prejšnji odsek v tej temi.

Težava: Iskani stolpec ni razvrščen v naraščajočem vrstnem redu

Če je argument range_lookup nastavljen na TRUE in eden od iskanih stolpcev ni razvrščen v naraščajočem vrstnem redu (A–Z), je prikazana napaka #N/V.

Rešitev:

  • Spremenite funkcijo VLOOKUP, da poiščete natančno ujemanje. Če želite najti natančno ujemanje, argument range_lookup nastavite na FALSE. Za FALSE ni zahtevano razvrščanje.

  • Za iskanje vrednosti v nerazvrščeni tabeli uporabite funkcijo INDEX/MATCH.

Težava: Vrednost je veliko število s plavajočo vejico

Če so v celicah časovne vrednosti ali velika decimalna števila, Excel vrne napako #N/V zaradi natančnosti plavajoče vejice. Števila s plavajočo vejico so števila, ki so za decimalno vejico. (Upoštevajte, da Excel shranjuje časovne vrednosti kot števila s plavajočo vejico.) Excel ne more shranjevati števil z velikimi plavajočimi vejicami, zato je treba števila s plavajočimi vejicami zaokroževati na 5 decimalnih mest, če želite, da funkcija pravilno deluje.

Rešitev: Skrajšajte številke z zaokroževanjem na pet decimalnih mest s funkcijo ROUND.

Ali imate vprašanje o določeni funkciji?

Objavite vprašanje v forumu skupnosti za Excel

Pomagajte nam izboljšati Excel

Ali imate predloge za izboljšanje naslednje različice Excela? Če jih imate, si oglejte teme na spletnem mestu Excel UserVoice.

Glejte tudi

Popravljanje napake #N/V

VLOOKUP: Ni več #NV

Funkcije HLOOKUP, VLOOKUP, LOOKUP vrnejo nepravilne vrednosti v Excelu

Aritmetika s plavajočo vejico lahko vrne nepravilne rezultate v Excelu

Kartica s kratkimi navodili: obnovitev znanja o funkciji VLOOKUP

Funkcija VLOOKUP

Pregled formul v Excelu

Kako se izogniti nedelujočim formulam

Uporaba iskanja napak za zaznavanje napak v formulah

Vse Excelove funkcije (po abecedi)

Vse Excelove funkcije (po kategoriji)

Razširite svoja znanja
Oglejte si izobraževanje
Prvi dobite nove funkcije
Pridružite se programu Office Insider

Vam je bila informacija v pomoč?

Zahvaljujemo se vam za povratne informacije.

Zahvaljujemo se vam za povratne informacije. Videti je, da bi vam prišla prav pomoč enega od naših Officeovih agentov za podporo.

×