Sisu
See õppetund selgitab, kuidas kiiresti toime tulla olukorras, kus funktsioon VPR (VLOOKUP) ei soovi töötada programmides Excel 2013, 2010, 2007 ja 2003 ning kuidas tuvastada ja parandada levinud vigu ning ületada piiranguid VPR.
Mitmes varasemas artiklis oleme uurinud funktsiooni erinevaid tahke VPR Excelis. Kui olete need hoolikalt läbi lugenud, peaksite nüüd olema selle valdkonna ekspert. Siiski ei usu paljud Exceli eksperdid asjata VPR üks keerulisemaid funktsioone. Sellel on hunnik piiranguid ja funktsioone, mis on paljude probleemide ja vigade allikaks.
Sellest artiklist leiate lihtsad selgitused vigade kohta # AT (#N/A), Nimi? (#NAME?) ja #VALUE! (#VALUE!), mis kuvatakse funktsiooniga töötamisel VPR, samuti nendega toimetulemise tehnikaid ja meetodeid. Alustame kõige tavalisematest juhtumitest ja kõige ilmsematest põhjustest. VPR ei tööta, seetõttu on parem uurida näiteid artiklis toodud järjekorras.
Vea #N/A parandamine Exceli funktsioonis VLOOKUP
Valemites koos VPR veateade # AT (#N/A) tähendab mitte saadaval (andmed puuduvad) – kuvatakse, kui Excel ei leia otsitavat väärtust. See võib juhtuda mitmel põhjusel.
1. Soovitud väärtus on valesti kirjutatud
Hea mõte see üksus enne üle vaadata! Kirjavead tekivad sageli siis, kui töötate väga suurte andmemahtudega, mis koosnevad tuhandetest ridadest, või kui otsitav väärtus on valemisse kirjutatud.
2. #N/A viga VLOOKUP-iga ligikaudse vaste otsimisel
Kui kasutate valemit ligikaudse vaste otsingutingimusega, st argumendiga vahemiku_otsing (vahemiku_otsing) on TRUE või pole määratud, võib teie valem teatada veast # N / A kahel juhul:
- Otsitav väärtus on väiksem kui otsitava massiivi väikseim väärtus.
- Otsinguveerg ei ole järjestatud kasvavas järjekorras.
3. Täpse vaste otsimisel funktsiooniga VLOOKUP viga #N/A
Kui otsite täpset vastet, st argumenti vahemiku_otsing (vahemiku_otsing) on FALSE ja täpset väärtust ei leitud, valem teatab ka veast # N / A. Lisateave funktsiooniga täpsete ja ligikaudsete vastete otsimise kohta VPR.
4. Otsingu veerg ei ole kõige vasakpoolsem
Nagu te ilmselt teate, on üks olulisemaid piiranguid VPR see ei saa olla suunatud vasakule, seega peab teie tabeli otsinguveerg olema kõige vasakpoolsem. Praktikas unustame selle sageli ära, mis toob kaasa mittetöötava valemi ja vea. # N / A.
Otsus: Kui andmestruktuuri ei ole võimalik muuta nii, et otsinguveerg oleks kõige vasakpoolsem, võite kasutada funktsioonide kombinatsiooni INDEX (INDEKS) ja ROHKEM AVALDATUD (MATCH) kui paindlikum alternatiiv VPR.
5. Numbrid vormistatakse tekstina
Veel üks veaallikas # N / A valemites koos VPR on põhitabelis või otsingutabelis olevad numbrid tekstivormingus.
Tavaliselt juhtub see siis, kui impordite teavet välistest andmebaasidest või kui sisestate numbri ette ülakeha, et säilitada esinull.
Tekstivormingus numbri kõige ilmsemad märgid on näidatud alloleval joonisel:
Lisaks saab numbreid vormingus salvestada Üldine (Kindral). Sel juhul on ainult üks märgatav omadus – numbrid on joondatud lahtri vasakusse serva, vaikimisi aga paremasse serva.
Otsus: Kui see on üks väärtus, klõpsake lihtsalt veaikoonil ja valige Teisenda numbriks (Teisenda numbriks) kontekstimenüüst.
Kui see on paljude numbrite olukord, valige need ja paremklõpsake valitud alal. Ilmuvas kontekstimenüüs valige Format Cells (Vorminda lahtrid) > vahekaart Number (Arv) > vorming Number (Numbriline) ja vajutage OK.
6. Alguses või lõpus on tühik
See on vea kõige ilmsem põhjus. # N / A funktsioonis VPR, kuna neid lisaruume on visuaalselt raske näha, eriti kui töötate suurte tabelitega, kui suurem osa andmetest on väljaspool ekraani.
Lahendus 1: lisatühikud põhitabelis (kus on funktsioon VLOOKUP)
Kui põhitabelis ilmuvad lisatühikud, saate argumendi lisamisega tagada valemite korrektse toimimise lookup_value (otsingu_väärtus) funktsiooniks TRIM (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Lahendus 2: lisatühikud otsingutabelis (otsinguveerus)
Kui otsinguveerus on lisatühikuid – lihtsad viisid # N / A valemis koos VPR ei saa vältida. Selle asemel VPR Võite kasutada massiivi valemit koos funktsioonide kombinatsiooniga INDEX (INDEX), ROHKEM AVALDATUD (MATCH) и TRIM (TRIM):
=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))
Kuna tegemist on massiivivalemiga, ärge unustage vajutada Ctrl + Tõstuklahv + Sisesta tavapärase asemel sisenevalemi õigeks sisestamiseks.
Viga #VALUE! VLOOKUP-iga valemites
Enamikul juhtudel teatab Microsoft Excel veast #VALUE! (#VALUE!), kui valemis kasutatav väärtus ei ühti andmetüübiga. Mis puudutab VPR, siis on veal tavaliselt kaks põhjust #VALUE!.
1. Otsitav väärtus on pikem kui 255 tähemärki
Olge ettevaatlik: funktsioon VPR ei saa otsida väärtusi, mis sisaldavad rohkem kui 255 tähemärki. Kui otsitav väärtus ületab selle piiri, kuvatakse tõrketeade. #VALUE!.
Otsus: Kasutage hulga funktsioone INDEX+VASTA (INDEKS + VASTUS). Allpool on valem, mis sobib selle ülesandega suurepäraselt:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Otsingutöövihiku täielikku teed pole määratud
Kui hankite andmeid teisest töövihikust, peate määrama selle faili täieliku tee. Täpsemalt peate lisama töövihiku nime (sh laiendi) nurksulgudesse [ ], millele järgneb lehe nimi ja hüüumärk. Kogu see konstruktsioon peab olema ümbritsetud apostroofidega, kui raamatu või lehe nimi sisaldab tühikuid.
Siin on funktsiooni täielik struktuur VPR teisest raamatust otsimiseks:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Tegelik valem võib välja näha selline:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
See valem otsib lahtri väärtuse A2 veerus B lehel Sheet1 töövihikus Uued hinnad ja eraldage veerust vastav väärtus D.
Kui mõni osa tabeli teest on välja jäetud, on teie funktsioon VPR ei tööta ja teatab veast #VALUE! (isegi kui otsingutabeliga töövihik on hetkel avatud).
Funktsiooni kohta lisateabe saamiseks VPRmõnele teisele Exceli failile viidates vaadake õppetundi: Teise töövihiku otsimine VLOOKUP-i abil.
3. Argumendi veeru_arv on väiksem kui 1
Raske on ette kujutada olukorda, kus keegi sisestab väärtuse, mis on väiksem kui 1veeru märkimiseks, millest väärtus välja võtta. Kuigi see on võimalik, kui selle argumendi väärtus arvutatakse mõne muu Exceli funktsiooni abil VPR.
Niisiis, kui juhtub, et argument col_index_num (veeru_number) väiksem kui 1funktsioon VPR teatab ka veast #VALUE!.
Kui argument col_index_num (veeru_number) on suurem kui antud massiivi veergude arv, VPR teatab veast #REF! (#SSYL!).
Viga #NAME? rakenduses VLOOKUP
Lihtsaim juhtum on viga Nimi? (#NAME?) – kuvatakse, kui kirjutate kogemata veaga funktsiooni nime.
Lahendus on ilmne – kontrolli õigekirja!
VLOOKUP ei tööta (piirangud, hoiatused ja otsused)
Lisaks üsna keerulisele süntaksile VPR on rohkem piiranguid kui ühelgi teisel Exceli funktsioonil. Nende piirangute tõttu on näiliselt lihtsad valemid VPR viivad sageli ootamatute tulemusteni. Altpoolt leiate lahendusi mitmele levinud stsenaariumile, kus VPR on vale.
1. VLOOKUP ei ole tõstutundlik
funktsioon VPR ei tee vahet suur- ja suurtähtede vahel ning aktsepteerib väike- ja suurtähti ühesugusena. Seega, kui tabelis on mitu elementi, mis erinevad ainult käändes, tagastab funktsioon VLOOKUP esimese leitud elemendi, olenemata suur- ja suurtähest.
Otsus: Kasutage mõnda muud Exceli funktsiooni, mis suudab teha vertikaalotsingu (LOOKUP, SUMPRODUCT, INDEX ja MATCH) koos funktsiooniga TÄIELIKA, mis eristab juhtumit. Täpsema teabe saamiseks võite õppida õppetunnist – 4 võimalust VLOOKUPi tõstmiseks Excelis tõstutundlikuks muuta.
2. VLOOKUP tagastab esimese leitud väärtuse
Nagu te juba teate, VPR tagastab väärtuse antud veerust, mis vastab esimesele leitud vastele. Siiski saate lasta sellel välja võtta soovitud väärtuse 2., 3., 4. või mis tahes muu korduse. Kui teil on vaja ekstraktida kõik dubleerivad väärtused, on teil vaja funktsioonide kombinatsiooni INDEX (INDEX), VÄHEMALT (VÄIKE) ja LINE (RIDA).
3. Tabelisse lisati või eemaldati veerg
Kahjuks valemid VPR lõpetage töötamine iga kord, kui otsingutabelisse lisatakse või eemaldatakse uus veerg. See juhtub süntaksi tõttu VPR nõuab, et määraksite kogu otsinguvahemiku ja andmete eraldamiseks konkreetse veeru numbri. Loomulikult muutuvad nii antud vahemik kui ka veeru number, kui kustutate veeru või sisestate uue.
Otsus: Ja jälle kiirustavad funktsioonid appi INDEX (INDEKS) ja ROHKEM AVALDATUD (MATCH). Valemis INDEX+VASTA Saate eraldi määratleda otsingu- ja otsinguveerud ning selle tulemusel saate kustutada või lisada nii palju veerge, kui soovite, ilma et peaksite muretsema kõigi seotud otsinguvalemite värskendamise pärast.
4. Lahtriviited on valemi kopeerimisel moonutatud
See pealkiri selgitab probleemi olemust ammendavalt, eks?
Otsus: Kasutage alati absoluutseid lahtriviiteid (koos sümboliga $) salvestab näiteks vahemiku 2 $: 100 C $ or $A:$C. Valemiribal saate lingi tüüpi kiiresti vahetada, klõpsates nuppu F4.
VLOOKUP – töötab funktsioonidega IFERROR ja ISERROR
Kui te ei soovi kasutajaid veateadetega hirmutada # N / A, #VALUE! or Nimi?, saate näidata tühja lahtrit või oma sõnumit. Seda saate teha asetades VPR funktsiooniks VIGA (IFERROR) programmides Excel 2013, 2010 ja 2007 või kasutage hulga funktsioone KUI+VIGE (IF+ISERROR) varasemates versioonides.
VLOOKUP: töötamine funktsiooniga IFERROR
Funktsiooni süntaks VIGA (IFERROR) on lihtne ja räägib enda eest:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
See tähendab, et esimese argumendi jaoks sisestate väärtuse, mida kontrollitakse vea suhtes, ja teise argumendi jaoks määrate, mida vea leidmisel tagastada.
Näiteks tagastab see valem tühja lahtri, kui otsitavat väärtust ei leitud:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Kui soovite funktsiooni standardse veateate asemel kuvada oma sõnumi VPR, pane see jutumärkidesse, näiteks nii:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: töötamine funktsiooniga ISERROR
Alates funktsioonist VIGA ilmus Excel 2007-s, varasemates versioonides töötades peate kasutama kombinatsiooni IF (IF) ja EOSHIBKA (ISERROR) nagu see:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Näiteks valem IF+ISERROR+VLOOKUP, sarnane valemiga IFERROR+VLOOKUPülal näidatud:
=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;ЛОЖЬ))
See on tänaseks kõik. Loodan, et see lühike õpetus aitab teil toime tulla kõigi võimalike vigadega. VPR ja pane oma valemid õigesti tööle.