Kako popraviti napako #N/A v funkciji VLOOKUP

V tej temi so opisani najpogostejši razlogi VLOOKUP za napačen rezultat v funkciji, zato so na voljo predlogi za uporabo indeksa in ujemanja .

Namig: Glejte tudi na kartico» kartica s kratkimi naročnine « : VLOOKUP odpravljanje težav , ki predstavlja pogoste razloge za #NA težave v PRIROČNO datoteko PDF. 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 omejitev funkcije VLOOKUP je, da lahko išče le vrednosti v stolpcu z levo večino v polju tabele. Če vaše iskalne vrednosti ni v prvem stolpcu matrike, bo prikazana napaka #N/A.

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

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

Rezultat napake #N/A, ker je iskalna vrednost » Kale « prikazana v drugem stolpcu (proizvod) argumenta» table_array «a2: C10. V tem primeru ga Excel išče v stolpcu A in ne 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 premakniti stolpce. To je lahko tudi zelo neizvedljivo, če imate velike ali zapletene preglednice, kjer so vrednosti celic rezultat drugih izračunov, ali morda obstajajo drugi logični razlogi, zakaj preprosto ne morete premakniti stolpcev okoli. 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. Oglejte si naslednji odsek.

Namesto tega razmislite o uporabi INDEKSa/UJEMAnja

Kazalo in ujemanje sta dobri možnosti za številne primere, v katerih Funkcija VLOOKUP ne ustreza vašim potrebam. Ključna prednost INDEKSa/UJEMAnja je, da lahko poiščete vrednost v stolpcu na poljubnem mestu v iskalni tabeli. INDEX vrne vrednost iz določene tabele/obsega, glede na njegov položaj. MATCH vrne relativni položaj vrednosti v tabeli/obsegu. Uporabite INDEX in MATCH skupaj v formuli, da poiščete vrednost v tabeli/matriki, tako da določite relativni položaj vrednosti v tabeli/matriki.

Namesto funkcije VLOOKUP je na voljo več prednosti uporabe INDEKSa/UJEMAnja:

  • Z INDEKSom in UJEMAnjem ni treba, da je vrnjena vrednost v istem stolpcu kot stolpec za iskanje. To se razlikuje od funkcije VLOOKUP, v katerem mora biti vrnjena vrednost v navedenem obsegu. Zakaj je to pomembno? Za funkcijo VLOOKUP morate poznati številko stolpca, ki vključuje vrnjeno vrednost. Čeprav to morda ni videti zahtevno, je lahko okorna, če imate veliko tabelo in želite prešteti število stolpcev. Če dodate ali odstranite stolpec v tabeli, morate znova šteti in posodobiti argument col_index_num . Pri funkcijah INDEX in MATCH ni zahtevano štetje, saj iskani stolpec ni stolpec z vrnjeno vrednostjo.

  • Z INDEKSom in UJEMAnjem lahko določite vrstico ali stolpec v matriki – ali pa 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 – če si lahko ogledate le vrednost v prvem stolpcu v tabeli – indeks in UJEMANJE bosta delovala, če je vaša Iskana vrednost v prvem stolpcu, zadnji ali kateri koli vmes.

  • KAZALO in UJEMANJE omogočata prilagodljivost ustvarjanja dinamičnega sklica na stolpec, ki vsebuje vrnjeno vrednost.To pomeni, da lahko v tabelo dodate stolpce brez prekinitve KAZALa in UJEMAnja. Na drugi strani Funkcija VLOOKUP odlomi, če želite dodati stolpec v tabelo, saj omogoča statični sklic na tabelo.

  • INDEX in MATCH omogočata večjo fleksibilnost z ujemanji.KAZALO in UJEMANJE lahko poiščeta natančno ujemanje ali vrednost, ki je večja ali manjša od vrednosti iskanja. 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 INDEX/MATCH, morate uporabiti argument» matrika/sklic «iz funkcije INDEX in ugnezditi sintakso UJEMAnja v notranjost. To naredite v obrazcu:

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

Uporabite INDEX/MATCH, da zamenjate VLOOKUP iz zgornjega primera. Sintaksa bo videti tako:

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

Povedano preprosto, to pomeni:

= INDEX (vrne vrednost iz C2: C10, ki se bo UJEMAla (Kale, ki je nekje v polju B2: B10, kjer je vrnjena vrednost prva vrednost, ki ustreza Kale))

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

Ko je argument» range_lookup «FALSE – in Funkcija VLOOKUP ne more najti natančnega ujemanja v vaših podatkih – vrne napako #N/a.

Rešitev: Če ste prepričani, da ustrezni podatki obstajajo v preglednici in ga Funkcija VLOOKUP ne lovi, si vzemite čas, da preverite, ali sklicne celice nimajo skritih presledkov ali znakov za tiskanje. Poleg tega zagotovite, da celice upoštevajo pravilni podatkovni tip. Na primer, celice s števili bi morale biti oblikovane kot številoin ne besedilo.

Če želite počistiti podatke v celicah, razmislite o uporabi funkcije Clean ali trim .

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

Če je argument» range_lookup «nastavljen na» TRUE «in je Iskana vrednost manjša od najmanjše vrednosti v matriki, bo prikazana napaka #N/a. 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 ne morete spremeniti vrednosti za iskanje in potrebujete večjo prilagodljivost z ujemajočimi se vrednostmi, uporabite INDEX/MATCH namesto funkcije VLOOKUP – glejte razdelek zgoraj v tem članku. 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 «, eden od stolpcev za iskanje pa ni razvrščen v naraščajočem vrstnem redu (A-Z), bo prikazana napaka #N/a.

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 potrebna nobena 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. (Excel shranjuje časovne vrednosti kot plavajoče vejice.) Excel ne more shraniti številk z zelo velikimi plavajočimi točkami, da bo funkcija pravilno delovala, morajo biti številke plavajočih vejic zaokrožene na 5 decimalnih mest.

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

Opomba:  Ta stran je bila prevedena z avtomatizacijo in lahko vsebuje slovnične napake ali nepravilnosti. Želimo, da bi bila ta vsebina za vas uporabna. Ali nam lahko sporočite, če so bile te informacije uporabne? Tukaj je referenčni članek v angleščini.

Ali potrebujete dodatno pomoč?

Razširite poznavanje Officea
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.

×