V tej temi so opisani najpogostejši razlogi za napačno delovanje funkcije VLOOKUP in predlogi za uporabo funkcij INDEX in MATCH .
Namig: Glejte tudi kartico s kratkimi referencami: namigi za odpravljanje težav s funkcijo VLOOKUP , ki predstavlja pogoste razloge za #NA težav v priročni datoteki 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 omejitev funkcije VLOOKUP je, da lahko išče le vrednosti v najbolj levem stolpcu v matriki tabele. Če iskana vrednost ni v prvem stolpcu polja, boste videli napako #N/V.
V tej tabeli želimo pridobiti število prodanih enot za ohrovt.
Rezultat #N/A je napaka, ker se iskana vrednost » ohrovt« pojavi v drugem stolpcu (pridelek) 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, v katerih so vrednosti celic rezultati drugih izračunov, ali pa obstajajo drugi logični razlogi, zakaj ne morete 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. Oglejte si naslednji razdelek.
Priporočamo vam, da namesto tega uporabite funkcijo INDEX/MATCH
Funkciji INDEX in MATCH sta dobri možnosti za številne primere, v katerih funkcija VLOOKUP ne ustreza vašim potrebam. Ključna prednost funkcije INDEX/MATCH je v tem, da lahko poiščete vrednost v stolpcu na katerem koli mestu v iskalni tabeli. INDEX vrne vrednost iz določene tabele/obsega – glede na njen položaj. MATCH vrne relativni položaj vrednosti v tabeli/obsegu. Uporabite funkciji INDEX in MATCH skupaj v formuli, če želite poiskati vrednost v tabeli/matriki tako, da določite relativni položaj vrednosti v tabeli/matriki.
Obstaja več prednosti uporabe funkcije INDEX/MATCH namesto funkcije VLOOKUP:
-
Pri funkcijah INDEX in MATCH ni treba, da je vrnjena vrednost v istem stolpcu kot iskalni stolpec. To se razlikuje od funkcije VLOOKUP, v kateri mora biti vrnjena vrednost v določenem obsegu. Zakaj je to pomembno? Za funkcijo VLOOKUP morate poznati številko stolpca, ki vključuje vrnjeno vrednost. Čeprav se to morda ne zdi zahtevno, je lahko težavno, če imate veliko tabelo in morate prešteti število stolpcev. Če dodate/odstranite stolpec v tabeli, morate znova prešteti in posodobiti argument col_index_num tabelo. Pri funkcijah INDEX in MATCH ni zahtevano štetje, saj iskani stolpec ni stolpec z vrnjeno vrednostjo.
-
V funkcijah INDEX in MATCH lahko določite vrstico ali stolpec v matriki ali pa oba. To pomeni, da lahko iščete vrednosti navpično in vodoravno.
-
Funkciji INDEX in MATCH se lahko uporabita za iskanje vrednosti v poljubnih stolpcih. Za razliko od funkcije VLOOKUP – v katerem lahko poiščete le vrednost v prvem stolpcu v tabeli – funkciji INDEX in MATCH delujeta, če je iskana vrednost v prvem stolpcu, zadnjem stolpcu ali kjer koli vmes.
-
Funkciji INDEX in MATCH ponujata prilagodljivost pri dinamičnem sklicu na stolpec, ki vsebuje vrnjeno vrednost. To pomeni, da lahko v tabelo dodate stolpce, ne da bi prekinili funkciji INDEX in MATCH. Po drugi strani pa se funkcija VLOOKUP prelomi, če morate v tabelo dodati stolpec, saj vsebuje statičen sklic na tabelo.
-
Funkciji INDEX in MATCH ponujata več prilagodljivosti pri ujemanj. Funkciji INDEX in MATCH lahko poiščeta natančno ujemanje ali vrednost, ki je večja ali manjša 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 v njej ugneznesti sintakso funkcije MATCH. V tej obliki:
=INDEX(matrika ali sklic, MATCH(lookup_value,lookup_array,[match_type])
S funkcijo INDEX/MATCH zamenjajte funkcijo VLOOKUP iz zgornjega primera. Sintaksa bo videti tako:
=INDEX(C2:C10,MATCH(B13,B2:B10,0))
Povedano preprosto, to pomeni:
=INDEX(vrni vrednost od C2:C10, ki bo MATCH(ohrovt, ki je nekje v matriki B2:B10, v kateri je vrnjena vrednost prva vrednost, ki ustreza ohrovtu))
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 range_lookup false in funkcija VLOOKUP ne najde natančnega ujemanja v vaših podatkih, vrne napako #N/V.
Rešitev: Če ste prepričani, da ustrezni podatki obstajajo v preglednici in jih funkcija VLOOKUP ne ujame, si vzemite čas in preverite, ali sklicevane celice nimajo skritih presledkov ali nenatisljivih znakov. Poleg tega se prepričajte, da celice sledijo pravilnim podatkovnim tipom. Celice s števili bi morale biti na primer oblikovane kot »Število« in ne »Besedilo«.
Prav tako razmislite o uporabi funkcije CLEAN ali TRIM za čiščenje podatkov v celicah.
Težava: Iskana vrednost je manjša od najmanjše vrednosti v matriki
Če je range_lookup argument nastavljen na TRUE in je iskana vrednost manjša od najmanjše vrednosti v matriki, boste videli napako #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.
Rešitev:
-
Po potrebi popravite iskano vrednost.
-
Če iskane vrednosti ne morete spremeniti in potrebujete večjo prilagodljivost pri ujemajočih se vrednostih, uporabite funkcijo INDEX/MATCH namesto funkcije VLOOKUP – glejte zgornji razdelek 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 range_lookup argument nastavljen na TRUE in eden od stolpcev za iskanje ni razvrščen v naraščajočem vrstnem redu (A–Z), se prikaže 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 razvrščanje ni potrebno.
-
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 shrani časovne vrednosti kot števila s plavajočo vejico.) Excel ne more shraniti števil z zelo velikimi plavajočimi točkami, zato je treba števila s plavajočo vejico zaokrožiti 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.
Potrebujete dodatno pomoč?
Kadar koli lahko zastavite vprašanje strokovnjaku v skupnosti tehničnih strokovnjakov za Excel ali pa pridobite podporo v skupnostih.
