vsebina
Ta lekcija pojasnjuje, kako hitro ravnati v situaciji, ko funkcija VPR (VLOOKUP) ne želi delovati v Excelu 2013, 2010, 2007 in 2003 ter kako prepoznati in popraviti pogoste napake ter preseči omejitve VPR.
V več prejšnjih člankih smo raziskali različne vidike funkcije VPR v Excelu. Če ste jih natančno prebrali, bi morali biti zdaj strokovnjak na tem področju. Vendar pa mnogi strokovnjaki za Excel ne verjamejo brez razloga VPR ena bolj zapletenih lastnosti. Ima kup omejitev in funkcij, ki postanejo vir številnih težav in napak.
V tem članku boste našli preproste razlage napak #AT (#N/A), #name? (#IME?) in #VALUE! (#VREDNOST!), ki se prikažejo pri delu s funkcijo VPR, kot tudi tehnike in metode spopadanja z njimi. Začeli bomo z najpogostejšimi primeri in najbolj očitnimi razlogi, zakaj. VPR ne deluje, zato je bolje preučiti primere v vrstnem redu, kot so navedeni v članku.
Odpravljanje napake #N/A v funkciji VLOOKUP v Excelu
V formulah z VPR sporočilo o napaki #AT (#N/A) pomeni ni na voljo (ni podatkov) – pojavi se, ko Excel ne najde vrednosti, ki jo iščete. To se lahko zgodi zaradi več razlogov.
1. Želena vrednost je napačno črkovana
Dobro je, da najprej preverite to postavko! Tipkarske napake se pogosto pojavijo, ko delate z zelo velikimi količinami podatkov, sestavljenimi iz več tisoč vrstic, ali ko je vrednost, ki jo iščete, zapisana v formulo.
2. Napaka #N/A pri iskanju približnega ujemanja z VLOOKUP
Če uporabite formulo s približnim pogojem iskanja ujemanja, tj iskanje_razpona (range_lookup) je TRUE ali ni navedeno, lahko vaša formula sporoči napako # N / A v dveh primerih:
- Vrednost, ki jo želite poiskati, je manjša od najmanjše vrednosti v matriki, ki jo iščete.
- Iskalni stolpec ni razvrščen v naraščajočem vrstnem redu.
3. Napaka #N/A pri iskanju natančnega ujemanja z VLOOKUP
Če iščete natančno ujemanje, tj iskanje_razpona (range_lookup) je FALSE in točne vrednosti ni bilo mogoče najti, bo formula tudi javila napako # N / A. Izvedite več o iskanju natančnih in približnih ujemanj s funkcijo VPR.
4. Iskalni stolpec ni skrajno levi
Kot verjetno veste, ena najpomembnejših omejitev VPR to je, da ne more biti obrnjen na levo, zato mora biti stolpec za iskanje v vaši tabeli skrajno levo. V praksi na to pogosto pozabimo, kar vodi do nedelujoče formule in napake. # N / A.
Sklep: Če podatkovne strukture ni mogoče spremeniti tako, da je iskalni stolpec skrajno levo, lahko uporabite kombinacijo funkcij INDEX (KAZALO) in BOLJ IZPOSTAVLJENO (MATCH) kot bolj prilagodljiva alternativa za VPR.
5. Številke so oblikovane kot besedilo
Še en vir napake # N / A v formulah z VPR so številke v besedilni obliki v glavni tabeli ali iskalni tabeli.
To se običajno zgodi, ko uvozite informacije iz zunanjih baz podatkov ali ko vnesete apostrof pred številko, da ohranite prvo ničlo.
Najočitnejši znaki števila v besedilni obliki so prikazani na spodnji sliki:
Poleg tega je mogoče številke shraniti v formatu splošno (Splošno). V tem primeru je opazna samo ena lastnost – številke so poravnane na levi rob celice, medtem ko so privzeto poravnane na desni rob.
Sklep: Če gre za eno samo vrednost, samo kliknite ikono napake in izberite Pretvori v številko (Pretvori v število) v kontekstnem meniju.
Če je to situacija z veliko številkami, jih izberite in z desno miškino tipko kliknite izbrano območje. V kontekstnem meniju, ki se prikaže, izberite Oblikuj celice (Oblikuj celice) > zavihek Število (Številka) > oblika Število (Številka) in pritisnite OK.
6. Na začetku ali na koncu je presledek
To je najmanj očiten razlog za napako. # N / A v funkciji VPR, saj je vizualno težko videti te dodatne prostore, zlasti pri delu z velikimi tabelami, ko je večina podatkov izven zaslona.
1. rešitev: dodatni prostori v glavni tabeli (kjer je funkcija VLOOKUP)
Če se v glavni tabeli pojavijo dodatni presledki, lahko zagotovite pravilno delovanje formul tako, da priložite argument lookup_value (lookup_value) v funkcijo TRIM (OBREZI):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
2. rešitev: dodatni presledki v iskalni tabeli (v stolpcu za iskanje)
Če so v iskalnem stolpcu dodatni presledki – preprosti načini # N / A v formuli s VPR ni mogoče izogniti. Namesto VPR Matrično formulo lahko uporabite s kombinacijo funkcij INDEX (KAZALO), BOLJ IZPOSTAVLJENO (TEKMA) и TRIM (OBREZI):
=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))
Ker je to matrična formula, ne pozabite pritisniti Ctrl + Shift + Enter namesto običajnega Vnesiteda pravilno vnesete formulo.
Napaka #VALUE! v formulah z VLOOKUP
V večini primerov Microsoft Excel sporoči napako #VALUE! (#VREDNOST!), ko se vrednost, uporabljena v formuli, ne ujema s tipom podatkov. Glede VPR, sta običajno dva razloga za napako #VALUE!.
1. Vrednost, ki jo iščete, je daljša od 255 znakov
Bodite previdni: funkcija VPR ne more iskati vrednosti, ki vsebujejo več kot 255 znakov. Če vrednost, ki jo iščete, presega to omejitev, boste prejeli sporočilo o napaki. #VALUE!.
Sklep: Uporabite kup funkcij INDEKS+UJEMANJE (KAZALO + UJEMANJE). Spodaj je formula, ki bo v redu za to nalogo:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Celotna pot do iskalnega delovnega zvezka ni navedena
Če pridobivate podatke iz drugega delovnega zvezka, morate podati celotno pot do te datoteke. Natančneje, vključiti morate ime delovnega zvezka (vključno s pripono) v oglatih oklepajih [ ], ki mu sledi ime lista in klicaj. Vse te konstrukcije morajo biti obdane z apostrofi, če ime knjige ali lista vsebuje presledke.
Tukaj je celotna struktura funkcije VPR za iskanje v drugi knjigi:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Prava formula bi lahko izgledala takole:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Ta formula bo poiskala vrednost celice A2 v stolpcu B na listu Sheet1 v delovnem zvezku Nove cene in izvlecite ustrezno vrednost iz stolpca D.
Če je kateri koli del poti tabele izpuščen, je vaša funkcija VPR ne bo delovalo in bo javilo napako #VALUE! (tudi če je delovni zvezek z iskalno tabelo trenutno odprt).
Za več informacij o funkciji VPRsklicevanja na drugo Excelovo datoteko si oglejte lekcijo: Iskanje drugega delovnega zvezka z VLOOKUP.
3. Argument Column_num je manjši od 1
Težko si je predstavljati situacijo, ko nekdo vnese vrednost, manjšo od 1da označite stolpec, iz katerega želite izvleči vrednost. Čeprav je to mogoče, če vrednost tega argumenta izračuna druga Excelova funkcija, ki je ugnezdena znotraj VPR.
Torej, če se zgodi, da argument col_index_num (številka_stolpca) manj kot 1funkcija VPR poroča tudi o napaki #VALUE!.
Če argument col_index_num (številka_stolpca) je večje od števila stolpcev v dani matriki, VPR bo javil napako #REF! (#SSYL!).
Napaka #NAME? v VLOOKUP
Najenostavnejši primer je napaka #name? (#IME?) – prikaže se, če pomotoma napišete ime funkcije z napako.
Rešitev je očitna – preverite črkovanje!
VLOOKUP ne deluje (omejitve, opozorila in odločitve)
Poleg precej zapletene sintakse, VPR ima več omejitev kot katera koli druga Excelova funkcija. Zaradi teh omejitev na videz preproste formule z VPR pogosto vodi do nepričakovanih rezultatov. Spodaj boste našli rešitve za več pogostih scenarijev, kjer VPR je narobe.
1. VLOOKUP ne razlikuje med velikimi in malimi črkami
funkcija VPR ne razlikuje med velikimi in malimi črkami ter sprejema male in velike črke kot enake. Če je torej v tabeli več elementov, ki se razlikujejo samo po velikih in malih črkah, bo funkcija VLOOKUP vrnila prvi najdeni element, ne glede na velike in male črke.
Sklep: Uporabite drugo Excelovo funkcijo, ki lahko izvede navpično iskanje (LOOKUP, SUMPRODUCT, INDEX in MATCH) v kombinaciji z TočnoA, ki razlikuje velike in male črke. Za več podrobnosti se lahko naučite iz lekcije – 4 načini, kako narediti VLOOKUP občutljiv na velike in male črke v Excelu.
2. VLOOKUP vrne prvo najdeno vrednost
Kot že veste, VPR vrne vrednost iz podanega stolpca, ki ustreza prvemu najdenemu ujemanju. Vendar pa lahko naročite, da izvleče 2., 3., 4. ali katero koli drugo ponovitev vrednosti, ki jo želite. Če morate ekstrahirati vse podvojene vrednosti, boste potrebovali kombinacijo funkcij INDEX (KAZALO), VSAJ (MAJHNO) in LINE (VRSTICA).
3. Stolpec je bil dodan ali odstranjen iz tabele
Na žalost, formule VPR preneha delovati vsakič, ko se v iskalno tabelo doda ali odstrani nov stolpec. To se zgodi zaradi sintakse VPR zahteva, da določite celoten obseg iskanja in določeno številko stolpca za ekstrakcijo podatkov. Seveda se dani obseg in številka stolpca spremenita, ko izbrišete stolpec ali vstavite novega.
Sklep: In spet funkcije hitijo na pomoč INDEX (KAZALO) in BOLJ IZPOSTAVLJENO (TEKMA). V formuli INDEKS+UJEMANJE Ločeno definirate stolpce za iskanje in iskanje, posledično pa lahko izbrišete ali vstavite poljubno število stolpcev, ne da bi vas skrbelo, da bi morali posodobiti vse povezane iskalne formule.
4. Sklici na celice so pri kopiranju formule popačeni
Ta naslov izčrpno pojasnjuje bistvo problema, kajne?
Sklep: Vedno uporabite absolutne sklice na celice (s simbolom $) na zapisih obsega, na primer 2 A $ 100: XNUMX C $ or $ A: $ C. V vrstici s formulami lahko hitro zamenjate vrsto povezave s klikom F4.
VLOOKUP – delo s funkcijama IFERROR in ISERROR
Če ne želite strašiti uporabnikov s sporočili o napakah # N / A, #VALUE! or #name?, lahko prikažete prazno celico ali svoje sporočilo. To lahko storite tako, da postavite VPR v funkcijo NAPAKA (IFERROR) v Excelu 2013, 2010 in 2007 ali uporabite več funkcij ČE+JENAPAKA (IF+ISERROR) v starejših različicah.
VLOOKUP: delo s funkcijo IFERROR
Sintaksa funkcije NAPAKA (IFERROR) je preprosta in govori sama zase:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
To pomeni, da za prvi argument vstavite vrednost, za katero želite preveriti napako, za drugi argument pa določite, kaj naj se vrne, če je najdena napaka.
Na primer, ta formula vrne prazno celico, če vrednost, ki jo iščete, ni najdena:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Če želite prikazati svoje lastno sporočilo namesto standardnega sporočila o napaki funkcije VPR, v narekovajih, takole:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: delo s funkcijo ISERROR
Od funkcije NAPAKA pojavil v Excelu 2007, pri delu v prejšnjih različicah boste morali uporabiti kombinacijo IF (ČE) in EOŠIBKA (ISERROR) takole:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Na primer, formula ČE+ISNAPAKA+VLOOKUP, podobno formuli IFERROR+VLOOKUPprikazano zgoraj:
=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))
=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));"";ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))
To je vse za danes. Upam, da vam bo ta kratka vadnica pomagala pri odpravljanju vseh možnih napak. VPR in poskrbite, da vaše formule delujejo pravilno.