Zamkatimu
Phunziroli likufotokoza momwe mungathanirane mwachangu ndi vuto lomwe limagwira ntchito VPR (VLOOKUP) sikufuna kugwira ntchito mu Excel 2013, 2010, 2007 ndi 2003, komanso momwe mungadziwire ndikukonza zolakwika zomwe zimachitika nthawi zambiri ndikugonjetsa malire. VPR.
M'nkhani zingapo zapitazo, tafufuza mbali zosiyanasiyana za ntchitoyi VPR mu Excel. Ngati mwawawerenga mosamala, muyenera tsopano kukhala katswiri pa ntchitoyi. Komabe, sizopanda chifukwa kuti akatswiri ambiri a Excel amakhulupirira VPR chimodzi mwazinthu zovuta kwambiri. Ili ndi zolephera zambiri komanso mawonekedwe omwe amakhala magwero amavuto ndi zolakwika zambiri.
M'nkhaniyi mupeza mafotokozedwe osavuta a zolakwika #AT (#N / A), #NAME (#NAME?) ndi #VALUE! (#VALUE!) zomwe zimawoneka mukamagwira ntchito ndi ntchitoyi VPR, komanso njira ndi njira zochitira nawo. Tiyamba ndi milandu yodziwika bwino komanso zifukwa zomveka bwino. VPR sizikugwira ntchito, choncho ndi bwino kuphunzira zitsanzo mu ndondomeko yomwe zaperekedwa m'nkhaniyo.
Kukonza cholakwika cha #N/A mu ntchito ya VLOOKUP mu Excel
Mu ma formula ndi VPR uthenga wolakwika #AT (#N/A) amatanthauza sakupezeka (palibe deta) - imawoneka pamene Excel sichipeza mtengo womwe mukuyang'ana. Izi zikhoza kuchitika pazifukwa zingapo.
1. Mtengo wofunidwa sunalembedwe molakwika
Ndibwino kuti muyang'ane chinthuchi kaye! Ma typos amapezeka nthawi zambiri mukamagwira ntchito ndi data yochuluka kwambiri, yokhala ndi mizere masauzande ambiri, kapena mtengo womwe mukuyang'ana ulembedwa mu fomula.
2. #N/A cholakwika pofufuza chofananira ndi VLOOKUP
Ngati mugwiritsa ntchito fomu yomwe ili ndi mawonekedwe ofananirako, mwachitsanzo, kukangana range_kuyang'ana (range_lookup) ndi CHOONA kapena sichinatchulidwe, fomula yanu ikhoza kuwonetsa cholakwika # N / A muzochitika ziwiri:
- Mtengo woti muyang'ane ndi wocheperako poyerekeza ndi mtengo wocheperako womwe umayang'aniridwa.
- Gawo losakira silinasanjidwe mokwera.
3. #N/A cholakwika pofufuza chofanana ndendende ndi VLOOKUP
Ngati mukuyang'ana chofanana ndendende, mwachitsanzo range_kuyang'ana (range_lookup) ndi FALSE ndipo mtengo wake sunapezeke, ndondomekoyi ifotokozanso zolakwika. # N / A. Dziwani zambiri za momwe mungafufuzire machesi enieni komanso ofanana ndi ntchito VPR.
4. Tsamba lofufuzira silikumanzere kwenikweni
Monga mukudziwira, chimodzi mwazolepheretsa kwambiri VPR ndikuti sichingayang'ane kumanzere, chifukwa chake gawo loyang'ana patebulo lanu liyenera kukhala kumanzere kwambiri. M'zochita, nthawi zambiri timayiwala za izi, zomwe zimatsogolera ku njira yosagwira ntchito komanso zolakwika. # N / A.
Kusankha: Ngati sikutheka kusintha mawonekedwe a data kuti gawo lofufuzira likhale kumanzere, mutha kugwiritsa ntchito ntchito zingapo INDEX (INDEX) ndi ZAMBIRI ZOVUTIKA (MATCH) ngati njira ina yosinthira VPR.
5. Manambala amapangidwa ngati malemba
Gwero lina la zolakwika # N / A mu ma formula ndi VPR ndi manambala m'mawu olembedwa mu tebulo lalikulu kapena tebulo loyang'ana.
Izi zimachitika nthawi zambiri mukatumiza uthenga kuchokera ku database yakunja, kapena mukalemba apostrophe pamaso pa nambala kuti musunge ziro patsogolo.
Zizindikiro zodziwika bwino za nambala mumtundu wamalemba zikuwonetsedwa pachithunzi pansipa:
Komanso, manambala akhoza kusungidwa mu mtundu General (General). Pachifukwa ichi, pali chinthu chimodzi chokha chodziwika - manambala amagwirizana ndi kumanzere kwa selo, pamene mwachisawawa akugwirizana ndi malire oyenera.
Kusankha: Ngati ndi mtengo umodzi, ingodinani pa chithunzi cholakwika ndikusankha Sinthani kukhala Nambala (Sinthani ku Nambala) kuchokera pazosankha.
Ngati zili choncho ndi manambala ambiri, sankhani ndikudina kumanja pagawo lomwe mwasankha. Mu menyu yankhani yomwe ikuwoneka, sankhani Sakani Maselo (Maselo a Format) > tabu Number (Nambala) > mtundu Number (Nambala) ndikusindikiza OK.
6. Pali danga pachiyambi kapena kumapeto
Ichi ndi chifukwa chochepa chodziwikiratu cha cholakwikacho. # N / A mu ntchito VPR, popeza ndizovuta kuwona malo owonjezerawa, makamaka pogwira ntchito ndi matebulo akulu, pomwe zambiri sizimawonekera.
Yankho 1: Mipata yowonjezera pa tebulo lalikulu (komwe ntchito ya VLOOKUP ili)
Ngati mipata yowonjezera ikuwonekera patebulo lalikulu, mutha kuwonetsetsa kuti mafomuwo akugwira ntchito moyenera potseka mkanganowo kumachika (lookup_value) mu ntchito TRIM (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Yankho 2: Mipata yowonjezera pa tebulo loyang'ana (mugawo loyang'ana)
Ngati mipata yowonjezera ili mugawo losaka - njira zosavuta # N / A mu formula ndi VPR sangapewedwe. M'malo mwa VPR Mutha kugwiritsa ntchito fomula yophatikizika yokhala ndi magwiridwe antchito INDEX (INDEX), ZAMBIRI ZOVUTIKA (MATCH) ndi 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))
Popeza iyi ndi njira yosanja, musaiwale kukanikiza Ctrl + Shift + Lowani m'malo mwa masiku onse Lowanikuti mulowetse fomu molondola.
Vuto #VALUE! m'ma formula ndi VLOOKUP
Nthawi zambiri, Microsoft Excel imafotokoza zolakwika #VALUE! (#VALUE!) pamene mtengo wogwiritsidwa ntchito mu fomula sukugwirizana ndi mtundu wa data. Zokhudza VPR, ndiye nthawi zambiri pamakhala zifukwa ziwiri zolakwitsa #VALUE!.
1. Mtengo womwe mukuyang'ana ndi wautali kuposa zilembo 255
Samalani: ntchito VPR sindingathe kusaka zinthu zomwe zili ndi zilembo zopitilira 255. Ngati mtengo womwe mukuyang'ana udutsa malire awa, mudzalandira uthenga wolakwika. #VALUE!.
Kusankha: Gwiritsani ntchito zinthu zambiri INDEX+MATCH (INDEX + MATCH). Pansipa pali chilinganizo chomwe chingathandize bwino ntchitoyi:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Njira yonse yopita ku bukhu lofufuzira sinatchulidwe
Ngati mukupeza deta kuchokera ku bukhu lina, muyenera kufotokoza njira yonse yopita ku fayiloyo. Mwachindunji, muyenera kuphatikiza dzina la bukhu lantchito (kuphatikiza kukulitsa) m'mabulaketi masikweya [ ], kutsatiridwa ndi dzina la pepala, ndikutsatiridwa ndi mawu okweza. Zomanga zonsezi ziyenera kutsekedwa mu apostrophes, ngati bukhu kapena dzina la pepala liri ndi mipata.
Pano pali dongosolo lathunthu la ntchitoyi VPR kufufuza m'buku lina:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Fomula yeniyeni ikhoza kuwoneka motere:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Fomula iyi iwona mtengo wa cell A2 mgawo B pa pepala Sheet1 mu buku la ntchito Mitengo Yatsopano ndi kuchotsa mtengo wofananawo kuchokera pamzati D.
Ngati gawo lililonse la njira ya tebulo lasiyidwa, ntchito yanu VPR sizigwira ntchito ndipo zidzanena zolakwika #VALUE! (ngakhale bukhu lantchito lomwe lili ndi tebulo loyang'ana lili lotseguka).
Kuti mudziwe zambiri za ntchitoyi VPRpofotokoza za fayilo ina ya Excel, onani phunziro: Kusaka buku lina pogwiritsa ntchito VLOOKUP.
3. Mkangano Column_nambala ndi yochepera 1
Ndizovuta kulingalira momwe munthu angalowerere mtengo wocheperako 1kusonyeza ndime yochotsamo mtengowo. Ngakhale ndizotheka ngati mtengo wa mkanganowu ukuwerengedwa ndi ntchito ina ya Excel yomwe ili mkati VPR.
Choncho, ngati izo zichitika kuti mkangano nambala_index_nambala (gawo_nambala) zochepa kuposa 1ntchito VPR adzanenanso cholakwika #VALUE!.
Ngati mkangano nambala_index_nambala (column_nambala) ndi yayikulu kuposa kuchuluka kwa magawo mugulu lomwe laperekedwa, VPR adzanena cholakwika #REF! (#SYL!).
Vuto #NAME? mu VLOOKUP
Mlandu wosavuta ndikulakwitsa #NAME (#NAME?) - idzawoneka ngati mwangozi mwalemba dzina lantchito ndi cholakwika.
Yankho lake ndi lodziwikiratu - fufuzani kalembedwe kanu!
VLOOKUP sikugwira ntchito (zolepheretsa, chenjezo ndi zisankho)
Kuphatikiza pa syntax yovuta kwambiri, VPR ili ndi malire ambiri kuposa ntchito ina iliyonse ya Excel. Chifukwa cha zofooka izi, zooneka ngati zosavuta ndi VPR nthawi zambiri zimabweretsa zotsatira zosayembekezereka. Pansipa mupeza njira zothetsera zochitika zingapo zomwe wamba VPR ndi zolakwika.
1. VLOOKUP simakhudzidwa ndi nkhani
ntchito VPR sichisiyanitsa pakati pa zazikulu ndipo imavomereza zilembo zazing'ono ndi zazikulu mofanana. Choncho, ngati pali zinthu zingapo patebulo zomwe zimasiyana pokhapokha, ntchito ya VLOOKUP idzabwezera chinthu choyamba chomwe chapezeka, mosasamala kanthu.
Kusankha: Gwiritsani ntchito ntchito ina ya Excel yomwe imatha kufufuza molunjika (LOOKUP, SUMPRODUCT, INDEX, ndi MATCH) kuphatikiza ndi CHITSANZOA zomwe zimasiyanitsa mlandu. Kuti mumve zambiri, mutha kuphunzira kuchokera mu phunziroli - Njira 4 zopangira VLOOKUP kukhala tcheru mu Excel.
2. VLOOKUP ibweza mtengo woyamba womwe wapezeka
Monga mukudziwa kale, VPR imabweretsanso mtengo kuchokera pagawo loperekedwa lomwe likugwirizana ndi machesi oyamba omwe apezeka. Komabe, mutha kuyitulutsa ya 2, 3, 4, kapena kubwereza kwina kulikonse kwa mtengo womwe mukufuna. Ngati mukufuna kuchotsa zikhalidwe zonse zobwereza, mudzafunika kuphatikiza ntchito INDEX (INDEX), KAPENA (ZANG'ONO) ndi LINE (ROW).
3. Chigawo chinawonjezeredwa kapena kuchotsedwa patebulo
Tsoka ilo, ma formula VPR siyani kugwira ntchito nthawi iliyonse pomwe gawo latsopano likuwonjezeredwa kapena kuchotsedwa patebulo loyang'ana. Izi zimachitika chifukwa cha syntax VPR zimafuna kuti mutchule mndandanda wonse wakusaka ndi nambala yeniyeni yazambiri kuti muchotse deta. Mwachilengedwe, zonse zomwe zaperekedwa komanso nambala yagawo zimasintha mukachotsa ndime kapena kuyika ina.
Kusankha: Ndipo kachiwiri ntchito akufulumira kuthandiza INDEX (INDEX) ndi ZAMBIRI ZOVUTIKA (MATCH). Mu chilinganizo INDEX+MATCH Mumatanthauzira padera mizati yosaka ndi kubweza, ndipo chifukwa chake, mutha kufufuta kapena kuyika mizati yambiri momwe mukufunira popanda kuda nkhawa kuti mukufunika kusintha mitundu yonse yakusaka.
4. Maumboni a ma cell amasokonekera pokopera fomula
Mutuwu ukufotokoza bwino lomwe tanthauzo la vuto, sichoncho?
Kusankha: Nthawi zonse gwiritsani ntchito maumboni amtundu wa cell (ndi chizindikiro $) pamawunidwe osiyanasiyana, mwachitsanzo $A$2:$C$100 or $A:$C. Mu formula bar, mutha kusintha mtundu wa ulalo mwachangu podina F4.
VLOOKUP - imagwira ntchito ndi IFERROR ndi ISERROR
Ngati simukufuna kuwopseza ogwiritsa ntchito ndi mauthenga olakwika # N / A, #VALUE! or #NAME, mutha kuwonetsa cell yopanda kanthu kapena uthenga wanu. Mutha kuchita izi poyika VPR mu ntchito IFERROR (IFERROR) mu Excel 2013, 2010 ndi 2007 kapena gwiritsani ntchito mulu wa ntchito IF+ISERROR (IF+ISERROR) m'matembenuzidwe akale.
VLOOKUP: imagwira ntchito ndi IFERROR
Ntchito syntax IFERROR (IFERROR) ndi yosavuta ndipo imadzilankhula yokha:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Ndiko kuti, pa mkangano woyamba mumayika mtengo kuti muwone ngati pali cholakwika, ndipo pa mkangano wachiwiri mumafotokoza zomwe mungabwezere ngati cholakwika chapezeka.
Mwachitsanzo, fomula iyi imabweza selo yopanda kanthu ngati mtengo womwe mukuyang'ana sunapezeke:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Ngati mukufuna kuwonetsa uthenga wanu m'malo mwa uthenga wolakwika wanthawi zonse VPR, ikani m'mawu, monga:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: imagwira ntchito ndi ISERROR
Popeza ntchito IFERROR adawonekera mu Excel 2007, mukamagwira ntchito m'mitundu yakale muyenera kugwiritsa ntchito kuphatikiza IF (IF) ndi EOSHIBKA (ISERROR) monga chonchi:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Mwachitsanzo, formula IF+ISERROR+VLOOKUP, zofanana ndi ndondomeko IFERROR+VLOOKUPzowonetsedwa pamwambapa:
=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;ЛОЖЬ))
Ndizo zonse za lero. Ndikukhulupirira kuti phunziro lalifupili likuthandizani kuthana ndi zolakwa zonse zomwe zingatheke. VPR ndi kupanga ma fomula anu kuti azigwira ntchito moyenera.