Edukiak
Ikasgai honek funtzio bat nola azkar aurre egin azaltzen du VPR (VLOOKUP) ez du Excel 2013, 2010, 2007 eta 2003-en lan egin nahi eta ohiko erroreak identifikatu eta konpondu eta mugak nola gainditu VPR.
Aurreko hainbat artikulutan, funtzioaren hainbat alderdi aztertu ditugu VPR Excel-en. Arretaz irakurri badituzu, orain arlo honetan aditua izan beharko zenuke. Hala ere, ez da arrazoirik gabe Excel-eko aditu askok uste dutena VPR ezaugarri konplexuenetako bat. Arazo eta akats askoren iturri bihurtzen diren muga eta ezaugarri mordoa ditu.
Artikulu honetan akatsen azalpen errazak aurkituko dituzu #AT (#N/A), # Izena? (#IZENA?) eta #VALUE! (#BALIOA!) funtzioarekin lan egitean agertzen direnak VPR, baita horiei aurre egiteko teknikak eta metodoak ere. Kasu ohikoenekin eta arrazoi nabarienekin hasiko gara. VPR ez du funtzionatzen, beraz, hobe da adibideak artikuluan ematen diren ordenan aztertzea.
# N / A errorea konpontzen Excel-en VLOOKUP funtzioan
duten formuletan VPR error message #AT (#N/A) esan nahi du ez dago eskuragarri (daturik ez) - Excel-ek bilatzen ari zaren balioa aurkitu ezin duenean agertzen da. Hau hainbat arrazoirengatik gerta daiteke.
1. Nahi duzun balioa gaizki idatzita dago
Ideia ona da elementu hau lehenbailehen egiaztatzeko! Akatsak sarritan gertatzen dira datu kopuru handiekin lan egiten duzunean, milaka lerroz osatua, edo bilatzen ari zaren balioa formula batean idazten denean.
2. #N/A errorea VLOOKUP-ekin gutxi gorabeherako bat-etortze bat bilatzean
Gutxi gorabeherako bat-etortzeen bilaketa-baldintza duen formula erabiltzen baduzu, hau da, argumentua barruti_bilaketa (range_lookup) EGIA da edo ez dago zehaztuta, baliteke zure formulak errore baten berri ematea # N / A bi kasutan:
- Bilatu beharreko balioa bilatzen ari den array-ko balio txikiena baino txikiagoa da.
- Bilaketa-zutabea ez dago goranzko ordenan.
3. #N/A errorea VLOOKUP-rekin bat-etortze zehatz bat bilatzean
Partidu zehatz baten bila bazabiltza, hau da, argumentua barruti_bilaketa (range_lookup) GEZURRA da eta ez da balio zehatza aurkitu, formulak errore baten berri emango du ere # N / A. Lortu informazio gehiago funtzio batekin bat-etortze zehatzak eta gutxi gorabeherakoak bilatzeko moduari buruz VPR.
4. Bilaketa-zutabea ez dago ezkerrean
Seguruenik dakizuenez, muga esanguratsuenetako bat VPR ezin duela ezkerrera begira jarri da, beraz, zure taulako bilaketa-zutabeak ezkerrean egon behar du. Praktikan, askotan ahazten dugu hau, eta horrek funtzionatzen ez duen formula eta akats bat dakar. # N / A.
Erabakia: Ezin bada datu-egitura aldatzea bilaketa-zutabea ezkerrean egon dadin, funtzioen konbinazio bat erabil dezakezu INDEX (AURKIBIDEA) eta ERABILERA GEHIAGO (MATCH) alternatiba malguago gisa VPR.
5. Zenbakiak testu gisa formateatzen dira
Beste akats iturri bat # N / A duten formuletan VPR taula nagusian edo bilaketa-taulan testu formatuan dauden zenbakiak dira.
Hori gertatzen da kanpoko datu-baseetatik informazioa inportatzen duzunean edo zenbaki baten aurretik apostrofo bat idazten duzunean hasierako zeroa mantentzeko.
Testu formatuan zenbaki baten seinalerik nabarmenenak beheko irudian agertzen dira:
Gainera, zenbakiak formatuan gorde daitezke General (Orokorrean). Kasu honetan, ezaugarri nabarmen bakarra dago: zenbakiak gelaxkaren ezkerreko ertzean lerrokatzen dira, eta lehenespenez eskuineko ertzean lerrokatzen dira.
Erabakia: Balio bakarra bada, egin klik errorearen ikonoan eta hautatu Bihurtu Zenbakira (Bihurtu Zenbakira) laster-menutik.
Zenbaki askoren egoera hau bada, hautatu itzazu eta egin klik eskuineko botoiarekin aukeratutako eremuan. Agertzen den testuinguru-menuan, hautatu Formatu gelaxkak (Formatu gelaxkak) > fitxa Zenbakia (Zenbakia) > formatua Zenbakia (Zenbakizkoa) eta sakatu OK.
6. Hasieran edo amaieran tarte bat dago
Hau da akatsaren arrazoirik agerikoena. # N / A funtzioan VPR, bisualki zaila baita espazio gehigarri horiek ikustea, batez ere mahai handiekin lan egiten denean, datu gehienak pantailatik kanpo daudenean.
1. irtenbidea: taula nagusiko espazio gehigarriak (non VLOOKUP funtzioa dagoen)
Taula nagusian espazio gehigarriak agertzen badira, formulak behar bezala funtzionatzen dutela ziurtatu dezakezu argumentua sartuz bilatu_balioa (lookup_value) funtzio batean TRIM (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
2. irtenbidea: espazio gehigarriak bilaketa-taulan (bilaketa zutabean)
Bilaketa zutabean espazio gehigarriak badaude, modu errazak # N / A formulan VPR ezin saihestu. Ordez VPR Funtzio konbinazio batekin matrize formula erabil dezakezu INDEX (AURKIBIDEA), ERABILERA GEHIAGO (PARTIDA) eta 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))
Hau array formula denez, ez ahaztu sakatzea Ktrl + Shift + Sartu ohikoaren ordez Sartuformula zuzen sartzeko.
Errore bat #VALUE! VLOOKUP duten formuletan
Kasu gehienetan, Microsoft Excel-ek errore baten berri ematen du #VALUE! (#BALIOA!) formulan erabilitako balioa datu-motarekin bat ez datorrenean. Buruz VPR, orduan errorearen bi arrazoi egon ohi dira #VALUE!.
1. Bilatzen ari zaren balioa 255 karaktere baino luzeagoa da
Kontuz ibili: funtzionamendua VPR ezin dira bilatu 255 karaktere baino gehiago dituzten balioak. Bilatzen ari zaren balioak muga hori gainditzen badu, errore-mezu bat jasoko duzu. #VALUE!.
Erabakia: Erabili ezaugarri mordoa AURKIBIDEA+BATEKIN (AURKIBIDEA + PARTIDA). Jarraian, zeregin honetarako ondo funtzionatuko duen formula dago:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Bilaketa libururako bide osoa ez dago zehaztuta
Beste lan-liburu batetik datuak berreskuratzen ari bazara, fitxategi horren bide osoa zehaztu behar duzu. Zehazkiago, lan-liburuaren izena (luzapena barne) sartu behar duzu kortxete artean [ ], ondoren orriaren izena eta harridura-puntua. Eraikuntza hori guztia apostrofoen artean sartu behar da, liburuaren edo fitxaren izenak hutsuneak baditu.
Hona hemen funtzioaren egitura osoa VPR beste liburu batean bilatzeko:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Benetako formula hau izan liteke:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Formula honek gelaxken balioa bilatuko du A2 zutabe batean B fitxan Sheet1 lan-koadernoan Prezio berriak eta atera dagokion balioa zutabetik D.
Taularen bide-zatiren bat baztertzen bada, zure funtzioa VPR ez du funtzionatuko eta errore baten berri emango du #VALUE! (nahiz eta une honetan bilaketa-taula duen lan-liburua irekita egon).
Funtzioari buruzko informazio gehiago lortzeko VPRbeste Excel fitxategi bat erreferentzia eginez, ikusi ikasgaia: Beste lan-liburu bat bilatu VLOOKUP erabiliz.
3. Argumentua Zutabe_zenbakia 1 baino txikiagoa da
Zaila da imajinatzea norbaitek baino balio gutxiago sartzen duen egoera bat 1balioa ateratzeko zutabea adierazteko. Argumentu honen balioa habiaratuta dagoen beste Excel funtzio batek kalkulatzen badu ere posible den arren VPR.
Beraz, argumentua gertatzen bada col_index_num (zutabe_zenbakia) baino txikiagoa 1funtzioa VPR errore baten berri ere emango du #VALUE!.
Argudioa bada col_index_num (zutabe_zenbakia) emandako matrizeko zutabe kopurua baino handiagoa da, VPR errore baten berri emango du #REF! (#SSIL!).
Errore bat #NAME? VLOOKUP-en
Kasurik errazena akats bat da # Izena? (#IZENA?) - Errore batekin funtzio-izena ustekabean idazten baduzu agertuko da.
Irtenbidea begi-bistakoa da: egiaztatu zure ortografia!
VLOOKUP ez dabil (mugak, oharrak eta erabakiak)
Sintaxi korapilatsu samarraz gain, VPR Excel beste edozein funtzio baino muga gehiago ditu. Muga horiek direla eta, itxuraz sinpleak dituzten formulak VPR askotan ustekabeko emaitzak ekartzen ditu. Jarraian, hainbat agertoki arruntetarako irtenbideak aurkituko dituzu non VPR gaizki dago.
1. VLOOKUP ez da maiuskulak eta minuskulak bereizten
Funtzio VPR ez ditu maiuskulak bereizten eta letra xeheak eta larriak berdin onartzen ditu. Hori dela eta, taulan maiuskulak eta kasuak bakarrik desberdintzen diren hainbat elementu badaude, BLOOKUP funtzioak aurkitutako lehen elementua itzuliko du, kasua edozein dela ere.
Erabakia: Erabili bilaketa bertikala egin dezakeen Excel beste funtzio bat (BILAKETA, BURUZKO PRODUKTUA, AURKIBIDEA eta MATCH) honekin batera. EXACTKasua bereizten duena. Xehetasun gehiago lortzeko, ikasgaitik ikas dezakezu - VLOOKUP Excel-en maiuskulak eta minuskulak bereizteko 4 modu.
2. BILAKETA aurkitutako lehen balioa itzultzen du
Dagoeneko dakizuen moduan, VPR aurkitutako lehenengo bat-etortzeari dagokion zutabetik emandako balioa itzultzen du. Hala ere, nahi duzun balioaren 2., 3., 4. edo beste edozein errepikapena atera dezakezu. Bikoiztutako balio guztiak atera behar badituzu, funtzio konbinazio bat beharko duzu INDEX (AURKIBIDEA), GUTXIENEZ (TXIKIA) eta LINE (ILARA).
3. Zutabe bat gehitu edo kendu zen taulara
Zoritxarrez, formulak VPR lanean utzi bilaketa-taulan zutabe berri bat gehitzen edo kentzen den bakoitzean. Hau sintaxia delako gertatzen da VPR bilaketaren barruti osoa eta datuak ateratzeko zutabe-zenbaki zehatza zehaztea eskatzen dizu. Jakina, emandako barrutia eta zutabe-zenbakia aldatzen dira zutabe bat ezabatzen duzunean edo berri bat txertatzen duzunean.
Erabakia: Eta berriro funtzioak presaka daude laguntzeko INDEX (AURKIBIDEA) eta ERABILERA GEHIAGO (PARTIDA). Formulan AURKIBIDEA+BATEKIN Bilaketa- eta berreskurapen-zutabeak bereizita definitzen dituzu, eta, ondorioz, nahi adina zutabe ezabatu edo txerta ditzakezu erlazionatutako bilaketa-formula guztiak eguneratu behar izateaz kezkatu gabe.
4. Gelaxken erreferentziak nahastuta daude formula bat kopiatzean
Izenburu honek arazoaren funtsa zehatz-mehatz azaltzen du, ezta?
Erabakia: Erabili beti gelaxka-erreferentzia absolutuak (sinboloarekin $) erregistroetan tartea, adibidez $A$2:$C$100 or $A:$C. Formula-barran, esteka mota azkar alda dezakezu klik eginez F4.
VLOOKUP - IFERROR eta ISERROR funtzioekin lan egitea
Errore mezuekin erabiltzaileak ikaratu nahi ez badituzu # N / A, #VALUE! or # Izena?, gelaxka huts bat edo zure mezua erakutsi dezakezu. Hau jarriz egin dezakezu VPR funtzio batean sartu ERROREA (IFERROR) Excel 2013, 2010 eta 2007-en edo erabili funtzio mordoa IF+DA ERROREA (IF+ISERROR) aurreko bertsioetan.
VLOOKUP: IFERROR funtzioarekin lan egiten du
Funtzio sintaxia ERROREA (IFERROR) sinplea da eta berez hitz egiten du:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Hau da, lehenengo argumentuan errorerik dagoen egiaztatu beharreko balioa txertatzen duzu, eta bigarren argumentuan errore bat aurkituz gero zer itzuli behar den zehazten duzu.
Adibidez, formula honek gelaxka huts bat itzultzen du bilatzen ari zaren balioa aurkitzen ez bada:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Zure mezua bistaratu nahi baduzu funtzioaren errore-mezu estandarraren ordez VPR, jarri komatxo artean, honela:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: ISERROR funtzioarekin lan egiten du
Funtzioaz geroztik ERROREA Excel 2007-n agertu zen, aurreko bertsioetan lan egiten duzunean konbinazioa erabili beharko duzu IF (BALDINTZA) eta EOSHIBKA (ISERROR) honela:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Adibidez, formula BALDIN+ERROREA+VLOOKUP, formularen antzekoa ERROREA+BILAKETAgoian agertzen dena:
=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;ЛОЖЬ))
Hori da dena gaurko. Espero dut tutorial labur honek akats posible guztiei aurre egiten lagunduko dizula. VPR eta zure formulak behar bezala funtzionatu.