Mukati
Ichi chidzidzo chinotsanangura nzira yekukurumidza kubata nemamiriro ezvinhu apo basa VPR (VLOOKUP) haidi kushanda muExcel 2013, 2010, 2007 uye 2003, uye nzira yekuziva nekugadzirisa zvikanganiso zvakajairika uye kukunda zvisingakwanisi. VPR.
Muzvinyorwa zvakawanda zvakapfuura, takaongorora zvakasiyana-siyana zvebasa racho VPR muExcel. Kana wakaaverenga nokungwarira, unofanira kuva nyanzvi munyaya iyi. Nekudaro, hazvisi pasina chikonzero icho nyanzvi dzakawanda dzeExcel dzinotenda VPR chimwe chezvinhu zvakaoma zvikuru. Iyo ine boka rezvipimo uye maficha anove sosi yematambudziko mazhinji uye kukanganisa.
Muchikamu chino uchawana tsananguro dziri nyore dzezvikanganiso #AT (#N/A), #NAME (#NAME?) uye #VALUE! (#VALUE!) inooneka paunenge uchishanda nebasa VPR, pamwe chete nemaitiro uye nzira dzekubata navo. Tichatanga neaya akajairika kesi uye zvikonzero zviri pachena nei. VPR haishande, saka zviri nani kudzidza mienzaniso mukurongeka kwayakapihwa muchinyorwa.
Kugadzirisa #N/A kukanganisa muVLOOKUP basa muExcel
Mune mafomula ne VPR error message #AT (#N/A) zvinoreva kwete (hapana data) - inoonekwa kana Excel isingakwanise kuwana kukosha kwauri kutsvaga. Izvi zvinogona kuitika nokuda kwezvikonzero zvakawanda.
1. Hukoshi yaidiwa haina kupereterwa
Pfungwa yakanaka yekutanga kutarisa chinhu ichi! Typos inowanzoitika kana iwe ukashanda neakawanda kwazvo data, ine zviuru zvemitsara, kana kukosha kwauri kutsvaga kwakanyorwa mufomula.
2. #N/A kukanganisa paunenge uchitsvaga inofananidzwa neVLOOKUP
Kana ukashandisa fomula ine fungidziro yemamiriro ekutsvaga, kureva nharo range_lookup (range_lookup) ndeye CHOKWADI kana kuti haina kutaurwa, fomula yako inogona kutaura kukanganisa # N / A muzviitiko zviviri:
- Kukosha kwekutarisa kudiki pane diki kukosha muhurongwa huri kutariswa kumusoro.
- Koramu yekutsvaga haina kurongwa muhurongwa hwekukwira.
3. #N/A kukanganisa paunenge uchitsvaga inofananidzwa neVLOOKUP
Kana uri kutsvaga anowirirana chaizvo, kureva nharo range_lookup (range_lookup) ndeye FALSE uye kukosha chaiko hakuna kuwanikwa, fomula yacho inoshumawo kukanganisa. # N / A. Dzidza zvakawanda nezve matsvagiro chaiwo uye akaenzana machisi ane basa VPR.
4. Koramu yekutsvaga haisi kuruboshwe
Sezvaungangove uchiziva, chimwe chezvipingamupinyi zvakakosha VPR ndezvekuti haikwanise kutarisana nekuruboshwe, saka iyo mbiru yekutarisa mutafura yako inofanira kunge iri kuruboshwe. Mukuita, isu tinowanzo kukanganwa pamusoro peizvi, izvo zvinotungamirira kune isiri-kushanda formula uye kukanganisa. # N / A.
Sarudzo: Kana zvisingabviri kushandura chimiro che data kuitira kuti mutsara wekutsvaga uri kuruboshwe, unogona kushandisa musanganiswa wemabasa INDEX (INDEX) uye ZVAKAWANDA AKAFUMURWA (MATCH) seimwe nzira inoshanduka ye VPR.
5. Nhamba dzakarongwa sechinyorwa
Imwe bviro yekukanganisa # N / A mumafomula ne VPR inhamba dziri muchimiro chemavara mutafura huru kana tafura yekutarisa.
Izvi zvinowanzoitika kana iwe uchiunza ruzivo kubva kune ekunze dhatabhesi, kana paunonyora apostrophe pamberi penhamba kuti uchengetedze inotungamira zero.
Zviratidzo zvinonyanya kujeka zvenhamba mune zvinyorwa zvinoratidzwa mumufananidzo uri pazasi:
Mukuwedzera, nhamba dzinogona kuchengetwa mufomati General (General). Muchiitiko ichi, pane chinhu chimwe chete chinoonekwa - nhamba dzakarongedzerwa kumucheto wekuruboshwe wesero, nepo nekusarudzika dzakarongedzerwa kumucheto wekurudyi.
Sarudzo: Kana iri kukosha kumwe chete, ingodzvanya pane yekukanganisa icon uye sarudza Shandura kuita Nhamba (Shandura kuChiverengo) kubva pane zviri mukati menyu.
Kana aya ari iwo mamiriro ane nhamba dzakawanda, sarudza iwo uye tinya-kurudyi panzvimbo yakasarudzwa. Mumenu yemamiriro ezvinhu inooneka, sarudza Fomati Masero (Format Masero) > tab nhamba (Nhamba) > fomati nhamba (Numeric) wobva wadzvanya OK.
6. Pane nzvimbo pakutanga kana kumagumo
Ichi ndicho chikonzero chidiki chiri pachena chekukanganisa. # N / A mubasa VPR, sezvo zvinoratidzika zvakaoma kuona idzi nzvimbo dzekuwedzera, kunyanya kana uchishanda nematafura makuru, kana yakawanda yedata iri kure-skrini.
Solution 1: Dzimwe nzvimbo mutafura huru (pane VLOOKUP basa)
Kana dzimwe nzvimbo dzichionekwa mutafura huru, unogona kuona kuti mafomula anoshanda nemazvo nekuvharira kupokana lookup_value (lookup_value) kuita basa TRIM (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Solution 2: Yakawedzerwa nzvimbo mutafura yekutarisa (muchikamu chekutarisa)
Kana dzimwe nzvimbo dziri mumutsara wekutsvaga - nzira dziri nyore # N / A mufomula ne VPR haigoni kunzvengwa. Panzvimbo ye VPR Iwe unogona kushandisa array formula ine musanganiswa wemabasa INDEX (INDEX), ZVAKAWANDA AKAFUMURWA (MATCH) uye 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))
Sezvo iyi iri array formula, usakanganwa kudzvanya Ctrl + Shift + Pinda panzvimbo penguva dzose pindakuisa fomula nemazvo.
Error #VALUE! mune mafomula ane VLOOKUP
Kazhinji, Microsoft Excel inoshuma kukanganisa #VALUE! (#VALUE!) kana kukosha kwakashandiswa mufomura kusingaenderane nerudzi rwe data. About VPR, saka kazhinji pane zvikonzero zviviri zvekukanganisa #VALUE!.
1. Kukosha kwauri kutsvaga kwakareba kupfuura 255 mavara
Ngwarira: basa VPR haikwanise kutsvaga kukosha dzine anopfuura 255 mavara. Kana kukosha kwauri kutsvaga kwakapfuura muganhu uyu, uchagamuchira meseji yezvikanganiso. #VALUE!.
Sarudzo: Shandisa boka rezvimiro INDEX+MATCH (INDEX + MATCH). Pazasi pane formula ichaita zvakanaka kune iri basa:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Nzira yakazara yebhuku rekutsvaga haina kutaurwa
Kana uri kutora data kubva kune rimwe bhuku rekushanda, unofanira kutsanangura nzira yakazara kune iyo faira. Kunyanya, iwe unofanirwa kusanganisira zita rebhuku rebasa (kusanganisira yekuwedzera) mumabhuraketi masikweya [], richiteverwa nezita reshizha, richiteverwa neinongedzo. Kuvakwa kwese uku kunofanirwa kuvharirwa mumapostrophes, kana bhuku kana zita repepa riine nzvimbo.
Heino chimiro chakakwana chebasa racho VPR kutsvaga mune rimwe bhuku:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Iyo chaiyo formula inogona kutaridzika seizvi:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Iyi fomula ichatarisa kukosha kwesero A2 muchikamu B pabepa Sheet1 mubhuku rebasa Mitengo Mitsva uye bvisa kukosha kunoenderana kubva mukoramu D.
Kana chero chikamu chenzira yetafura chikasiiwa, basa rako VPR haishande uye ichataura kukanganisa #VALUE! (kunyangwe bhuku rebasa rine tafura yekutarisa rakavhurika parizvino).
Kuti uwane rumwe ruzivo nezve basa VPRuchirevera rimwe faira reExcel, ona chidzidzo: Kutsvaga rimwe bhuku rekushandisa uchishandisa VLOOKUP.
3. Nharo Column_num isingasviki 1
Zvakaoma kufungidzira mamiriro ezvinhu apo mumwe munhu anopinda mumutengo wakaderera pane 1kuratidza koramu yekutora kukosha kwairi. Kunyangwe zvichigoneka kana kukosha kwenharo iyi kuverengerwa nerimwe basa reExcel rakaiswa mukati VPR.
Saka, kana zvikaitika kuti nharo col_index_num (column_nhamba) ishoma pane 1basa VPR ichataurawo kukanganisa #VALUE!.
Kana nharo col_index_num (column_number) yakakura kudarika nhamba yemakoramu muhurongwa hwakapihwa, VPR achataura kukanganisa #REF! (#SYL!).
Error #NAME? muVLOOKUP
Nyaya iri nyore kukanganisa #NAME (#NAME?) - ichaonekwa kana ukangonyora zita rebasa riine chikanganiso.
Mhinduro iri pachena - tarisa zviperengo zvako!
VLOOKUP haishande (zvisingakwanisi, caveats uye sarudzo)
Pamusoro peiyo syntax yakaoma kunzwisisa, VPR ine zvakawanda zvinogumira kupfuura chero rimwe basa reExcel. Nekuda kwezvipimo izvi, mafomula anoita seakapusa ane VPR kazhinji zvinotungamirira kumigumisiro isingatarisirwi. Pazasi iwe unowana mhinduro kune akati wandei akajairika mamiriro uko VPR zvakaipa.
1. VLOOKUP haisi nyaya inonzwisisika
basa VPR haisiyanisi pakati penyaya uye inogamuchira mavara madiki uye makuru akafanana. Naizvozvo, kana paine zvinhu zvakati wandei mutafura zvinongosiyana chete kana, basa reVLOOKUP rinodzosa chinhu chekutanga chawanikwa, zvisinei nenyaya.
Sarudzo: Shandisa rimwe basa reExcel rinogona kuita tsvakiridzo yakatwasuka (LOOKUP, SUMPRODUCT, INDEX, uye MATCH) pamwe chete BASAA inosiyanisa nyaya. Kuti uwane rumwe ruzivo, iwe unogona kudzidza kubva kuchidzidzo - 4 nzira dzekuita VLOOKUP kesi-inonzwa muExcel.
2. VLOOKUP inodzosa kukosha kwekutanga kwawanikwa
Sezvaunenge uchitoziva, VPR inodzosa kukosha kubva mukoramu yakapihwa inoenderana neyekutanga match yawanikwa. Nekudaro, iwe unogona kuita kuti iburitse iyo 2nd, 3rd, 4th, kana chero imwe kudzokorora kukosha kwaunoda. Kana iwe uchida kuburitsa ese akadhindwa kukosha, iwe unozoda musanganiswa wemabasa INDEX (INDEX), LEAST (DUKU) uye LINE (MUTSARA).
3. Koramu yakawedzerwa kana kubviswa patafura
Zvinosuruvarisa, mafomula VPR rega kushanda pese painowedzerwa koramu kana kubviswa patafura yekutarisa. Izvi zvinoitika nekuti syntax VPR inoda kuti iwe utsanangure huwandu hwakazara hwekutsvaga uye iyo chaiyo column nhamba yekutora data. Sezvingatarisirwa, zvese zvakapihwa chiyero uye nhamba yekoramu zvinoshanduka paunodzima koramu kana kuisa imwe nyowani.
Sarudzo: Uye zvakare mabasa ari mukukurumidza kubatsira INDEX (INDEX) uye ZVAKAWANDA AKAFUMURWA (MATCH). Muchidimbu INDEX+MATCH Iwe unotsanangura zvakasiyana makoramu ekutsvaga nekutora, uye semhedzisiro, unogona kudzima kana kuisa makoramu akawanda sezvaunoda usinganetseki nekugadzirisa ese ane hukama mafomula ekutsvaga.
4. Mareferenzi emasero anovhiringika pakukopa fomula
Musoro uyu unotsanangura kukosha kwedambudziko zvakazara, handiti?
Sarudzo: Nguva dzose shandisa absolute cell reference (nechiratidzo $) pamarekodha emhando, semuenzaniso $A$2:$C$100 or $A:$C. Mune fomula bar, unogona kukurumidza kushandura rudzi rwekubatanidza nekudzvanya F4.
VLOOKUP - kushanda nemabasa IFERROR uye ISERROR
Kana iwe usingade kutyisidzira vashandisi nemashoko ekukanganisa # N / A, #VALUE! or #NAME, unogona kuratidza sero isina chinhu kana yako meseji. Iwe unogona kuita izvi nekuisa VPR muchiitiko IFERROR (IFERROR) muExcel 2013, 2010 uye 2007 kana kushandisa boka remabasa IF+ISERROR (IF+ISERROR) mumashanduro ekutanga.
VLOOKUP: kushanda neIFERROR basa
Basa rechirevo IFERROR (IFERROR) iri nyore uye inotaura yega:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Kureva kuti, panharo yekutanga unoisa kukosha kuti kutariswe kukanganisa, uye kune yechipiri nharo unotsanangura chekudzosera kana kukanganisa kwawanikwa.
Semuenzaniso, iyi fomula inodzosa sero isina chinhu kana kukosha kwauri kutsvaga kukasawanikwa:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Kana iwe uchida kuratidza yako meseji pachinzvimbo cheiyo function's standard error message VPR, zviise mumakotesheni, sekudaro:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: kushanda neISERROR basa
Sezvo basa IFERROR yakaonekwa muExcel 2007, kana uchishanda mumavhezheni apfuura uchafanirwa kushandisa musanganiswa IF (IF) uye EOSHIBKA (ISERROR) sezvizvi:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Somuenzaniso, formula IF+ISERROR+VLOOKUP, zvakafanana nefomu IFERROR+VLOOKUPinoratidzwa pamusoro:
=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;ЛОЖЬ))
Ndizvo zvanhasi chete. Ndinovimba ichi chidzidzo chipfupi chichakubatsira kubata nezvikanganiso zvese zvinogoneka. VPR uye ita kuti mafomula ako ashande nemazvo.