V tej temi so opisani najpogostejši razlogi za napako v funkciji VLOOKUP in predlogi za uporabo funkcij INDEX in MATCH.
Namig: Glejte tudi kartico s hitrim sklicem: Namigi za odpravljanje težav s funkcijo VLOOKUP, v katerih so opisani pogosti razlogi 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 za funkcijo VLOOKUP je, da funkcija lahko išče le vrednosti v najbolj levem stolpcu v matriki tabele. Če iskane vrednosti ni v prvem stolpcu matrike, boste videli napako #N/V.
V tej tabeli želimo pridobiti število prodanih enot za ohrovt.
Rezultat #N/A, ker je vrednost za iskanje »ohrovt« prikazana v drugem stolpcu (pridelek) argumenta table_array A2:C10. V tem primeru Excel išče v stolpcu A, 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 nepraktično, če imate velike ali zapletene preglednice, kjer so vrednosti celic rezultati drugih izračunov, ali pa obstajajo drugi logični razlogi, zakaj preprosto 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.
Premislite o uporabi funkcije INDEX/MATCH
Index in MATCH sta dobri možnosti za številne primere, v katerih funkcija VLOOKUP ne ustreza vašim potrebam. Ključna prednost index/MATCH je v tem, da lahko vrednost v stolpcu na katerem koli mestu v tabeli za iskanje. INDEX vrne vrednost iz določene tabele/obsega – glede na njen položaj. Funkcija MATCH vrne relativni položaj vrednosti v tabeli/obsegu. Skupaj uporabite funkcijo INDEX in MATCH v formuli za iskanje vrednosti 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 index in MATCH ni treba, da je vrnjena vrednost v istem stolpcu kot stolpec za iskanje. Ta se razlikuje od vrste 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.
-
Pri argumentih INDEX in MATCH 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, v kateri lahko vrednost v prvem stolpcu v tabeli najdete le– funkcija INDEX in MATCH delujeta, če je iskana vrednost v prvem stolpcu, zadnjem stolpcu ali kjer koli vmes.
-
Index in MATCH ponujata prilagodljivost pri omogočanju dinamičnega sklica na stolpec, ki vsebuje vrnjeno vrednost. To pomeni, da lahko v tabelo dodate stolpce, ne da bi prekinili index in MATCH. Po drugi strani pa se funkcija VLOOKUP prelomi, če morate v tabelo dodati stolpec, saj vsebuje statične sklice na tabelo.
-
Index in MATCH ponujata več prilagodljivosti pri ujemanj. Index in MATCH lahko najdeta 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 znotraj funkcije MATCH ugnezvati sintakso funkcije MATCH. To ima obliko:
=INDEX(matrika ali sklic, MATCH(lookup_value,lookup_array,[match_type])
Uporabimo funkcijo INDEX/MATCH, da nadomestimo funkcijo VLOOKUP v primeru zgoraj. 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 MATCH(ohrovt, ki je nekje v matriki B2:B10, v katerem 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 argument FALSE in funkcija VLOOKUP ne najde 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 vsebuje, vzemite čas in se prepričajte, da sklicevane celice ne vsebujejo skritih presledkov ali nenatisnih znakov. Poleg tega se prepričajte, da celice sledijo pravilnim podatkovnim tipom. Celice s števili morate na primer oblikovati kot »Število«in ne kot »Besedilo«.
Za čiščenje podatkov v celicah lahko uporabite tudi funkcijo CLEAN ali TRIM.
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 vrednosti za iskanje ne morete spremeniti in potrebujete večjo prilagodljivost pri ujemajočih se vrednostih, premislite o uporabi funkcije 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 range_lookup argument nastavljen na TRUE in eden od iskanih stolpcev 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 ni potrebno 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 shrani časovne vrednosti kot števila s plavajočo vejico.) Excel shraniti števil z zelo 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.
Potrebujete dodatno pomoč?
Kadar koli lahko zastavite vprašanje strokovnjaku v skupnosti Excel Tech Community ali pa pridobite podporo v skupnosti Communities.
