Okuqukethwe
Lesi sifundo sichaza ukuthi ungabhekana kanjani ngokushesha nesimo lapho umsebenzi VPR (I-VLOOKUP) ayifuni ukusebenza ku-Excel 2013, 2010, 2007 kanye no-2003, kanye nendlela yokuhlonza nokulungisa amaphutha avamile kanye nokunqoba imikhawulo VPR.
Ezihlokweni ezimbalwa ezedlule, sihlole izici ezihlukahlukene zomsebenzi VPR ku-Excel. Uma uzifunde ngokucophelela, kufanele manje ube uchwepheshe kulo mkhakha. Kodwa-ke, asikho ngaphandle kwesizathu ukuthi ochwepheshe abaningi be-Excel bakholelwa VPR esinye sezici eziyinkimbinkimbi. Inenqwaba yemikhawulo nezici eziba umthombo wezinkinga namaphutha amaningi.
Kulesi sihloko uzothola izincazelo ezilula zamaphutha #AT (#N/A), # IGAMA (#NAME?) kanye #INANI! (#INANI!) evelayo uma usebenza nomsebenzi VPR, kanye namasu nezindlela zokubhekana nazo. Sizoqala ngamacala ajwayelekile kanye nezizathu ezisobala kakhulu zokuthi kungani. VPR ayisebenzi, ngakho-ke kungcono ukufunda izibonelo ngokulandelana kwazo esihlokweni.
Kulungiswa iphutha elingu-#N/A kumsebenzi we-VLOOKUP ku-Excel
Kumafomula nge VPR umlayezo wephutha #AT (#N/A) kusho akutholakali (ayikho idatha) - ivela lapho i-Excel ingakwazi ukuthola inani olifunayo. Lokhu kungenzeka ngezizathu eziningana.
1. Inani elifunekayo alipeliwe kahle
Kuhle ukuhlola le nto kuqala! Ama-typos avame ukwenzeka lapho usebenza ngenani elikhulu kakhulu ledatha, ehlanganisa izinkulungwane zemigqa, noma lapho inani olifunayo libhalwa ifomula.
2. Iphutha #N/A lapho ucinga okufanayo nge-VLOOKUP
Uma usebenzisa ifomula enesimo sokucinga sokufanisa, okungukuthi i-agumenti bheka_ububanzi (range_lookup) IQINISO noma ayicacisiwe, ifomula yakho ingase ibike iphutha # N / A ezimweni ezimbili:
- Inani okufanele libhekwe lingaphansi kwenani elincane ohlwini olubhekwayo.
- Ikholomu yosesho ayihlelwanga ngohlelo olukhuphukayo.
3. #N/A iphutha uma ufuna okufanayo ngqo ne-VLOOKUP
Uma ufuna okufana ncamashí, okungukuthi i-agumenti bheka_ububanzi (range_lookup) ithi FALSE futhi inani eliqondile alitholakalanga, ifomula izophinde ibike iphutha. # N / A. Funda kabanzi mayelana nendlela yokusesha okufana ncamashi nokucishe kufane nomsebenzi VPR.
4. Ikholomu yokusesha ayigcini kwesokunxele
Njengoba kungenzeka uyazi, omunye wemikhawulo ebaluleke kakhulu VPR wukuthi ayikwazi ukubheka kwesokunxele, yingakho ikholomu yokubheka etafuleni lakho kufanele ibe kwesokunxele kakhulu. Eqinisweni, sivame ukukhohlwa ngalokhu, okuholela kufomula engasebenzi kanye nephutha. # N / A.
Isinqumo: Uma kungenzeki ukushintsha ukwakheka kwedatha ukuze ikholomu yosesho ibe kwesokunxele, ungasebenzisa inhlanganisela yemisebenzi INDEX (INDEX) kanye OKUNINGI KUDANWE (MATCH) njengendlela evumelana nezimo kakhudlwana ye VPR.
5. Izinombolo zifomethwe njengombhalo
Omunye umthombo wephutha # N / A kumafomula nge VPR ziyizinombolo ngefomethi yombhalo kuthebula elikhulu noma ithebula lokubheka.
Lokhu kuvame ukwenzeka uma ungenisa ulwazi kusuka kusizindalwazi sangaphandle, noma uma uthayipha i-aphostrophe ngaphambi kwenombolo ukuze ugcine uziro oholayo.
Izimpawu ezisobala kakhulu zenombolo ngefomethi yombhalo ziboniswa esithombeni esingezansi:
Ngaphezu kwalokho, izinombolo zingagcinwa ngefomethi General (General). Kulesi simo, kunesici esisodwa kuphela esibonakalayo - izinombolo ziqondaniswe kwesokunxele seseli, kuyilapho ngokuzenzakalelayo ziqondaniswe kwesokudla.
Isinqumo: Uma kuyinani elilodwa, mane uchofoze kusithonjana sephutha bese ukhetha Guqulela kuNombolo (Guqula ube Inombolo) kusuka kumenyu yokuqukethwe.
Uma lesi simo sinezinombolo eziningi, zikhethe bese uchofoza kwesokudla endaweni ekhethiwe. Kumenyu yokuqukethwe evelayo, khetha Fometha Amaseli (Fometha Amaseli) > ithebhu Inombolo (Inombolo) > ifomethi Inombolo (Izinombolo) bese ucindezela OK.
6. Kukhona isikhala ekuqaleni noma ekugcineni
Lesi yisizathu esincane esisobala sephutha. # N / A emsebenzini VPR, njengoba kubonakala kunzima ukubona lezi zikhala ezengeziwe, ikakhulukazi uma usebenza ngamathebula amakhulu, lapho idatha eminingi ingekho esikrinini.
Isixazululo 1: Izikhala ezengeziwe kuthebula elikhulu (lapho umsebenzi we-VLOOKUP ukhona)
Uma izikhala ezengeziwe zivela kuthebula elikhulu, ungaqinisekisa ukuthi amafomula asebenza ngendlela efanele ngokuvala impikiswano ukubheka (lokup_value) kumsebenzi I-TRIM (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Isixazululo 2: Izikhala ezengeziwe kuthebula lokubheka (kukholomu yokubheka)
Uma izikhala ezengeziwe zikukholomu yokusesha - izindlela ezilula # N / A kufomula nge VPR ayinakugwenywa. Esikhundleni se VPR Ungasebenzisa ifomula yamalungu afanayo enenhlanganisela yemisebenzi INDEX (INDEX), OKUNINGI KUDANWE (QAPHELA) futhi I-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))
Njengoba lena kuyifomula yamalungu afanayo, ungakhohlwa ukucindezela Ctrl + Shift + Faka esikhundleni sokujwayelekile Fakaukufaka ifomula ngendlela efanele.
Iphutha #VALUE! kumafomula ane-VLOOKUP
Ezimweni eziningi, iMicrosoft Excel ibika iphutha #INANI! (#INANI!) lapho inani elisetshenziswe kufomula lingafani nohlobo lwedatha. Mayelana VPR, bese kuba nezizathu ezimbili zephutha #INANI!.
1. Inani olifunayo lide kunezinhlamvu ezingama-255
Qaphela: umsebenzi VPR ayikwazi ukusesha amanani aqukethe izinhlamvu ezingaphezu kuka-255. Uma inani olifunayo lidlula lo mkhawulo, uzothola umlayezo wephutha. #INANI!.
Isinqumo: Sebenzisa izici eziningi INDEX+MATCH (INDEX + MATCH). Ngezansi ifomula ezokwenza kahle kulo msebenzi:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Indlela ephelele eya encwadini yokusebenzela yokusesha ayicacisiwe
Uma ubuyisela idatha kwenye incwadi yokusebenzela, kufanele ucacise indlela egcwele eya kulelo fayela. Ngokucacile, kufanele ufake igama lencwadi yokusebenzela (kuhlanganise nesandiso) kubakaki abayisikwele [ ], kulandelwe igama leshidi, kulandelwe iphuzu lesibabazo. Konke lokhu kwakhiwa kufanele kufakwe kuma-aphostrophe, uma kwenzeka incwadi noma igama leshidi liqukethe izikhala.
Nasi isakhiwo esiphelele somsebenzi VPR ukusesha kwenye incwadi:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Ifomula yangempela ingase ibukeke kanje:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Le fomula izobheka inani leseli A2 kukholamu B eshidini Ishidi1 encwadini yokusebenzela Amanani Amasha futhi ukhiphe inani elihambisanayo kukholamu D.
Uma noma iyiphi ingxenye yendlela yethebula ishiywe, umsebenzi wakho VPR ngeke isebenze futhi izobika iphutha #INANI! (ngisho noma ibhuku lokusebenza elinethebula lokubheka livuliwe okwamanje).
Ukuze uthole ulwazi olwengeziwe mayelana nomsebenzi VPRubhekisela kwelinye ifayela le-Excel, bheka isifundo: Ukusesha enye incwadi yokusebenzela usebenzisa i-VLOOKUP.
3. Ikholomu_Yengxabano ingaphansi kuka-1
Kunzima ukucabanga isimo lapho umuntu engena inani elingaphansi 1ukukhombisa ikholomu okufanele kukhishwe kuyo inani. Nakuba kungenzeka uma inani lale mpikiswano libalwa ngomunye umsebenzi we-Excel ofakwe ngaphakathi VPR.
Ngakho, uma kwenzeka ukuthi ingxabano inombolo_yenkomba (inombolo_yekholomu) ingaphansi kuka 1umsebenzi VPR izophinde ibike iphutha #INANI!.
Uma ingxabano inombolo_yenkomba (inombolo_yekholomu) inkulu kunenombolo yamakholomu ohlwini olunikeziwe, VPR izobika iphutha #REF! (#SYL!).
Iphutha #NAME? ku-VLOOKUP
Icala elilula iphutha # IGAMA (#NAME?) – izovela uma ngephutha ubhala igama lomsebenzi elinephutha.
Isixazululo sisobala - hlola isipelingi sakho!
I-VLOOKUP ayisebenzi (imikhawulo, izixwayiso nezinqumo)
Ngaphezu kwe-syntax eyinkimbinkimbi, VPR inemikhawulo eminingi kunanoma yimuphi omunye umsebenzi we-Excel. Ngenxa yale mikhawulo, amafomula abonakala elula nge VPR ngokuvamile kuholela emiphumeleni engalindelekile. Ngezansi uzothola izixazululo zezimo ezimbalwa ezivamile lapho VPR akulungile.
1. I-VLOOKUP ayizweli kakhulu
Umsebenzi VPR ayihlukanisi phakathi kofeleba futhi yamukela izinhlamvu ezingosonhlamvukazi abancane ngokufanayo. Ngakho-ke, uma kunezinto ezimbalwa kuthebula ezihluka kuphela uma kwenzeka, umsebenzi we-VLOOKUP uzobuyisela isici sokuqala esitholiwe, kungakhathaliseki isimo.
Isinqumo: Sebenzisa omunye umsebenzi we-Excel ongenza ukusesha okuqondile (LOOKUP, SUMPRODUCT, INDEX, kanye ne-MATCH) kuhlanganiswe no ISEXOLOA ehlukanisa icala. Ukuze uthole imininingwane eyengeziwe, ungafunda esifundweni - izindlela ezi-4 zokwenza ukuthi i-VLOOKUP izwele ku-Excel.
2. I-VLOOKUP ibuyisela inani lokuqala elitholiwe
Njengoba usuvele uyazi, VPR ibuyisela inani elisuka kukholomu enikeziwe elihambisana nokufanayo kokuqala okutholiwe. Nokho, ungenza ukuthi ikhiphe okwesi-2, kwesi-3, kwesi-4, noma okunye ukuphindaphinda kwenani olifunayo. Uma udinga ukukhipha wonke amanani ayimpinda, uzodinga inhlanganisela yemisebenzi INDEX (INDEX), OKUPHELELE (OKUNCANE) kanye LINE (UMGWAQO).
3. Ikholomu yengezwe noma yakhishwa etafuleni
Ngeshwa, amafomula VPR yeka ukusebenza njalo uma ikholomu entsha yengezwa noma ikhishwa kuthebula lokubheka. Lokhu kwenzeka ngenxa ye-syntax VPR idinga ukuthi ucacise uhla oluphelele kosesho kanye nenombolo yekholomu ethile yokukhipha idatha. Ngokwemvelo, kokubili ububanzi obunikeziwe kanye nenombolo yekholomu kuyashintsha lapho ususa ikholomu noma ufaka entsha.
Isinqumo: Futhi futhi imisebenzi iyashesha ukusiza INDEX (INDEX) kanye OKUNINGI KUDANWE (MESHI). Kufomula INDEX+MATCH Uchaza ngokwehlukana amakholomu okusesha kanye nokubuyisa, futhi ngenxa yalokho, ungakwazi ukususa noma ukufaka amakholomu amaningi ngendlela ofuna ngayo ngaphandle kokukhathazeka ngokuthi kufanele ubuyekeze wonke amafomula okusesha ahlobene.
4. Izinkomba zamaseli ziyaphazamiseka uma kukopishwa ifomula
Lesi sihloko sichaza ingqikithi yenkinga ngokuphelele, akunjalo?
Isinqumo: Ngaso sonke isikhathi sebenzisa amareferensi amaseli aphelele (nophawu $) kumarekhodi ububanzi, isibonelo $A$2:$C$100 or I-$A:$C. Kubha yefomula, ungashintsha ngokushesha uhlobo lwesixhumanisi ngokuchofoza F4.
I-VLOOKUP – isebenza ngemisebenzi ethi IFERROR kanye ne-ISERROR
Uma ungafuni ukuthusa abasebenzisi ngemilayezo yephutha # N / A, #INANI! or # IGAMA, ungabonisa iseli engenalutho noma umlayezo wakho. Ungakwenza lokhu ngokubeka VPR abe umsebenzi IFERROR (IFERROR) ku-Excel 2013, 2010 kanye no-2007 noma sebenzisa inqwaba yemisebenzi IF+ISERROR (IF+ISERROR) kuzinguqulo zangaphambili.
I-VLOOKUP: isebenza nomsebenzi we-IFERROR
I-syntax yomsebenzi IFERROR (IFERROR) ilula futhi izikhulumela yona:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Okusho ukuthi, ku-agumenti yokuqala ufaka inani elizohlolelwa iphutha, bese ku-agumenti yesibili usho ukuthi yini okufanele ibuyiswe uma iphutha litholwa.
Isibonelo, le fomula ibuyisela iseli elingenalutho uma inani olifunayo lingatholakali:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Uma ufuna ukuveza owakho umyalezo esikhundleni somlayezo wephutha ojwayelekile womsebenzi VPR, yibeke ngezingcaphuno, kanje:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
I-VLOOKUP: isebenza nomsebenzi we-ISERROR
Kusukela umsebenzi IFERROR kuvele ku-Excel 2007, lapho usebenza ezinguqulweni zangaphambili kuzodingeka usebenzise inhlanganisela IF (IF) kanye EOSHIBKA (ISERROR) kanje:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Ngokwesibonelo, ifomula IF+ISERROR+VLOOKUP, efana nefomula IFERROR+VLOOKUPkuboniswe ngenhla:
=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;ЛОЖЬ))
Yilokho kuphela okwanamuhla. Ngethemba ukuthi lesi sifundo esifushane sizokusiza ukuthi ubhekane nawo wonke amaphutha angenzeka. VPR futhi wenze amafomula akho asebenze ngendlela efanele.