Contents
Wannan darasi yana bayanin yadda ake saurin magance yanayin da ake aiki VPR (VLOOKUP) baya son yin aiki a cikin Excel 2013, 2010, 2007 da 2003, da kuma yadda ake ganowa da gyara kurakurai na yau da kullun da shawo kan iyakoki. VPR.
A cikin kasidun da suka gabata, mun bincika bangarori daban-daban na aikin VPR a cikin Excel. Idan kun karanta su a hankali, ya kamata ku zama gwani a wannan fannin. Koyaya, ba tare da dalili ba ne masanan Excel da yawa suka yi imani VPR daya daga cikin mafi hadaddun fasali. Yana da tarin iyakoki da fasali waɗanda suka zama tushen matsaloli da kurakurai da yawa.
A cikin wannan labarin za ku sami bayani mai sauƙi na kurakurai #AT (#N/A), #NAME? (#NAME?) kuma #DARAJAR! (#VALUE!) wanda ke bayyana lokacin aiki tare da aikin VPR, da kuma dabaru da hanyoyin magance su. Za mu fara da shari'o'in da aka fi sani da kuma fitattun dalilan da ya sa. VPR ba ya aiki, don haka yana da kyau a yi nazarin misalai a cikin tsari da aka ba su a cikin labarin.
Gyara kuskuren #N/A a cikin aikin VLOOKUP a cikin Excel
A cikin tsari tare da VPR saƙon kuskure #AT (#N/A) yana nufin babu (babu bayanai) - yana bayyana lokacin da Excel ba zai iya samun ƙimar da kuke nema ba. Wannan na iya faruwa saboda dalilai da yawa.
1. Ƙimar da ake so ta kuskure
Yana da kyau a duba wannan abu tukuna! Sau da yawa typos na faruwa lokacin da kake aiki tare da bayanai masu yawa, wanda ya ƙunshi dubban layi, ko lokacin da aka rubuta ƙimar da kake nema a cikin tsari.
2. Kuskure #N/A yayin neman kusan wasa tare da VLOOKUP
Idan kayi amfani da dabara tare da kimanin yanayin neman wasa, watau jayayya range_lokup (range_lookup) GASKIYA ne ko ba a kayyade ba, dabarar ku na iya ba da rahoton kuskure # N / A a lokuta biyu:
- Ƙimar da za a duba bai kai mafi ƙanƙantar ƙima a cikin tsararrun da ake duba sama ba.
- Ba a jera ginshiƙin bincike cikin tsari mai hawa ba.
3. Kuskuren #N/A yayin neman daidai daidai da VLOOKUP
Idan kana neman ainihin wasa, watau jayayya range_lokup (range_lookup) KARYA ne kuma ba a sami ainihin ƙimar ba, ƙirar kuma za ta ba da rahoton kuskure # N / A. Ƙara koyo game da yadda ake nemo madaidaicin daidaitattun matches tare da aiki VPR.
4. Shafin bincike ba a hagu ba
Kamar yadda ka sani, daya daga cikin mafi mahimmancin iyakoki VPR shi ne cewa ba zai iya fuskantar hagu ba, don haka ginshiƙin bincike a cikin tebur ɗinku dole ne ya kasance mafi hagu. A aikace, sau da yawa muna manta game da wannan, wanda ke haifar da tsari mara aiki da kuskure. # N / A.
Yanke shawara: Idan ba zai yiwu a canza tsarin bayanai ba domin ginshiƙin bincike ya kasance hagu, za ku iya amfani da haɗin ayyuka INDEX (INDEX) da MORE BAYYANA (MATCH) a matsayin madadin mafi sassauƙa don VPR.
5. An tsara lambobi azaman rubutu
Wani tushen kuskure # N / A a cikin dabara tare da VPR lambobi ne a tsarin rubutu a cikin babban tebur ko tebur dubawa.
Wannan yawanci yana faruwa ne lokacin da kuka shigo da bayanai daga rumbun adana bayanai na waje, ko kuma lokacin da kuka buga rafke a gaban lamba don kiyaye sifili.
Mafi bayyanar alamun lamba a tsarin rubutu ana nuna su a cikin hoton da ke ƙasa:
Bugu da kari, ana iya adana lambobi a cikin tsari Janar (Gabaɗaya). A wannan yanayin, akwai alama ɗaya kawai da aka sani - lambobin suna daidaitawa zuwa gefen hagu na tantanin halitta, yayin da ta tsohuwa suna daidaitawa zuwa gefen dama.
Yanke shawara: Idan ƙima ɗaya ce, kawai danna gunkin kuskure kuma zaɓi Juya zuwa Lamba (Maida zuwa Lamba) daga menu na mahallin.
Idan wannan shine yanayin tare da lambobi da yawa, zaɓi su kuma danna dama akan yankin da aka zaɓa. A cikin mahallin mahallin da ya bayyana, zaɓi Tsarin Sel (Format Cells) > tab Number (Lambar) > tsari Number (Lambobi) kuma latsa OK.
6. Akwai sarari a farkon ko a karshen
Wannan shine mafi ƙarancin dalili na kuskuren. # N / A cikin aiki VPR, Tun da yana da wuyar gani don ganin waɗannan ƙarin wurare, musamman ma lokacin aiki tare da manyan tebur, lokacin da yawancin bayanai ke kashe allo.
Magani 1: Ƙarin sarari a cikin babban tebur (inda aikin VLOOKUP yake)
Idan ƙarin sarari ya bayyana a cikin babban tebur, za ku iya tabbatar da cewa tsarin yana aiki daidai ta hanyar haɗa hujja dubawa (lookup_value) cikin aiki TASHIYA (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Magani na 2: Ƙarin sarari a cikin teburin dubawa (a cikin ginshiƙi na bincike)
Idan ƙarin wurare suna cikin ginshiƙin bincike - hanyoyi masu sauƙi # N / A a cikin dabara tare da VPR ba za a iya kauce masa. Maimakon VPR Kuna iya amfani da dabarar tsararru tare da haɗin ayyuka INDEX (INDEX), MORE BAYYANA (MATSAYI) da TASHIYA (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))
Tunda wannan tsari ne na tsararru, kar a manta da dannawa Ctrl + Shigar + Shigar maimakon yadda aka saba Shigardon shigar da dabara daidai.
Kuskure #VALUE! a cikin dabara tare da VLOOKUP
A mafi yawan lokuta, Microsoft Excel yana ba da rahoton kuskure #DARAJAR! (#VALUE!) Lokacin da ƙimar da aka yi amfani da ita a cikin dabarar ba ta dace da nau'in bayanai ba. Game da VPR, to yawanci akwai dalilai guda biyu na kuskuren #DARAJAR!.
1. Darajar da kuke nema ta fi haruffa 255 tsayi
Yi hankali: aiki VPR ba zai iya bincika ƙimar da ke ɗauke da haruffa sama da 255 ba. Idan darajar da kuke nema ta wuce wannan iyaka, zaku karɓi saƙon kuskure. #DARAJAR!.
Yanke shawara: Yi amfani da tarin fasali INDEX+MATCH (INDEX + MATCH). A ƙasa akwai dabarar da za ta yi daidai don wannan aikin:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Cikakken hanyar zuwa littafin aikin bincike ba a ƙayyade ba
Idan kana maido da bayanai daga wani littafin aiki, dole ne ka saka cikakken hanyar wannan fayil ɗin. Musamman ma, dole ne ku haɗa sunan littafin aiki (gami da tsawo) a cikin maƙallan murabba'i [], da sunan takardar, sannan sai ma'anar faɗa. Duk waɗannan gine-gine dole ne a rufe su a cikin tarkace, idan littafin ko sunan takarda ya ƙunshi sarari.
Anan ga cikakken tsarin aikin VPR don bincika a wani littafi:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Ainihin dabara na iya zama kamar haka:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Wannan dabarar za ta duba ƙimar tantanin halitta A2 a cikin wani shafi B a kan takardar Takardar1 a cikin littafin aiki Sabbin Farashi kuma cire madaidaicin ƙimar daga ginshiƙi D.
Idan an tsallake kowane bangare na hanyar tebur, aikin ku VPR ba zai yi aiki ba kuma zai ba da rahoton kuskure #DARAJAR! (ko da a halin yanzu littafin aiki tare da teburin dubawa yana buɗewa).
Don ƙarin bayani game da aikin VPRduba wani fayil na Excel, duba darasi: Neman wani littafin aiki ta amfani da VLOOKUP.
3. Hujja Column_num bai kai 1 ba
Yana da wuya a yi tunanin yanayin da wani ya shiga ƙima da bai kai ba 1don nuna ginshiƙin da za a cire ƙimar daga ciki. Ko da yake yana yiwuwa idan an ƙididdige ƙimar wannan hujja ta wani aikin Excel da aka yi a ciki VPR.
Don haka, idan ya faru cewa hujja col_index_num (lambar_lambar) ƙasa da 1aiki VPR zai kuma bayar da rahoton wani kuskure #DARAJAR!.
Idan hujja col_index_num (column_number) ya fi adadin ginshiƙai a cikin tsararrun da aka bayar, VPR zai ba da rahoton kuskure #REF! (#SSYL!).
Kuskure #NAME? a cikin VLOOKUP
Mafi sauki lamarin kuskure ne #NAME? (#NAME?) - zai bayyana idan kun rubuta sunan aiki da kuskure tare da kuskure.
Maganin a bayyane yake - duba rubutun ku!
VLOOKUP baya aiki (iyakance, fa'ida da yanke shawara)
Bugu da ƙari ga ƙayyadaddun kalmomi masu rikitarwa, VPR yana da iyakancewa fiye da kowane aikin Excel. Saboda waɗannan iyakoki, ƙididdiga masu sauƙi tare da VPR sau da yawa haifar da m sakamakon. A ƙasa zaku sami mafita don al'amuran gama gari da yawa inda VPR ba daidai ba ne.
1. VLOOKUP ba shi da hankali
aiki VPR baya bambance tsakanin harka kuma yana karɓar ƙananan haruffa da manyan haruffa iri ɗaya. Sabili da haka, idan akwai abubuwa da yawa a cikin tebur waɗanda suka bambanta kawai a yanayin, aikin VLOOKUP zai dawo da kashi na farko da aka samo, ba tare da la'akari da yanayin ba.
Yanke shawara: Yi amfani da wani aikin Excel wanda zai iya yin bincike a tsaye (LOOKUP, SUMPRODUCT, INDEX, da MATCH) a hade tare da SAURARAA da ke bambanta harka. Don ƙarin cikakkun bayanai, zaku iya koyo daga darasi - hanyoyin 4 don sanya yanayin VLOOKUP a cikin Excel.
2. VLOOKUP yana dawo da ƙimar farko da aka samo
Kamar yadda kuka sani, VPR yana dawo da ƙimar daga ginshiƙin da aka bayar daidai da wasan farko da aka samo. Koyaya, zaku iya cire shi ta 2, 3rd, 4th, ko duk wani maimaita ƙimar da kuke so. Idan kana buƙatar cire duk kwafin dabi'u, kuna buƙatar haɗin ayyuka INDEX (INDEX), KASHE (KARAMIN) da LINE (ROW).
3. An ƙara ko cire ginshiƙi zuwa teburin
Abin baƙin ciki, da dabara VPR daina aiki duk lokacin da aka ƙara ko cire sabon shafi zuwa teburin dubawa. Wannan yana faruwa saboda syntax VPR yana buƙatar ka saka cikakken kewayon bincike da takamaiman lambar shafi don hakar bayanai. A zahiri, duka kewayon da aka bayar da lambar shafi suna canzawa lokacin da kuka share shafi ko saka sabo.
Yanke shawara: Kuma ayyuka suna cikin gaggawa don taimakawa INDEX (INDEX) da MORE BAYYANA (MATSAYI). A cikin dabara INDEX+MATCH Kuna ayyana ginshiƙan bincike daban-daban da maidowa, kuma a sakamakon haka, zaku iya sharewa ko saka ginshiƙan da yawa kamar yadda kuke so ba tare da damuwa game da sabunta duk hanyoyin bincike masu alaƙa ba.
4. Ana tufatar da bayanan salula lokacin yin kwafin dabara
Wannan taken yana bayyana ainihin matsalar gaba ɗaya, ko?
Yanke shawara: Yi amfani da cikakkun bayanan tantanin halitta (tare da alamar $) a kan rikodin kewayon, misali $2:$C$100 or $A:$C. A cikin mashaya dabara, zaku iya saurin canza nau'in hanyar haɗin gwiwa ta dannawa F4.
VLOOKUP - aiki tare da ayyukan IFERROR da ISERROR
Idan ba ka so ka tsoratar da masu amfani da saƙon kuskure # N / A, #DARAJAR! or #NAME?, zaku iya nuna tantanin halitta mara komai ko saƙonku. Kuna iya yin hakan ta sanyawa VPR cikin aiki IFEROR (IFERROR) a cikin Excel 2013, 2010 da 2007 ko amfani da tarin ayyuka IDAN + ISERROR (IF+ISERROR) a cikin sigar farko.
VLOOKUP: aiki tare da aikin IFERROR
Aiki syntax IFEROR (IFERROR) mai sauƙi ne kuma yayi magana da kansa:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Wato hujjar farko za ku saka darajar da za a bincika don yin kuskure, kuma na biyu za ku fayyace abin da za ku dawo idan an sami kuskure.
Misali, wannan dabara tana mayar da fanko tantanin halitta idan ba a sami ƙimar da kuke nema ba:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Idan kuna son nuna saƙon ku maimakon daidaitaccen saƙon kuskuren aikin VPR, sanya shi a cikin maganganu, kamar haka:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: aiki tare da aikin ISEROR
Tun da aikin IFEROR ya bayyana a cikin Excel 2007, lokacin aiki a cikin sigogin da suka gabata dole ne kuyi amfani da haɗin gwiwa IF (IF) kuma EOSHIBKA (ISERROR) kamar haka:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Misali, dabara IDAN+ISERROR+VLOOKUP, kama da dabara IFERROR+VLOOKUPwanda aka nuna a sama:
=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;ЛОЖЬ))
Shi ke nan na yau. Ina fatan wannan ɗan gajeren koyawa zai taimake ku magance duk kurakurai masu yuwuwa. VPR kuma ku sanya tsarin aikinku suyi aiki daidai.