Yaliyomo
Somo hili linaelezea jinsi ya kukabiliana haraka na hali ambapo utendaji VPR (VLOOKUP) haitaki kufanya kazi katika Excel 2013, 2010, 2007 na 2003, na jinsi ya kutambua na kurekebisha makosa ya kawaida na kuondokana na mapungufu. VPR.
Katika makala kadhaa zilizopita, tumechunguza vipengele mbalimbali vya kazi VPR katika Excel. Ikiwa umezisoma kwa uangalifu, unapaswa sasa kuwa mtaalam katika uwanja huu. Walakini, sio bila sababu kwamba wataalam wengi wa Excel wanaamini VPR moja ya vipengele ngumu zaidi. Ina rundo la mapungufu na vipengele ambavyo huwa chanzo cha matatizo na makosa mengi.
Katika makala hii utapata maelezo rahisi ya makosa #KATIKA (#N/A), #NAME? (#JINA?) na #THAMANI! (#VALUE!) inayoonekana wakati wa kufanya kazi na chaguo la kukokotoa VPR, pamoja na mbinu na mbinu za kukabiliana nao. Tutaanza na kesi za kawaida na sababu zilizo wazi zaidi kwa nini. VPR haifanyi kazi, kwa hivyo ni bora kusoma mifano kwa mpangilio ambao wamepewa katika kifungu hicho.
Inarekebisha hitilafu ya #N/A katika chaguo za kukokotoa za VLOOKUP katika Excel
Katika fomula na VPR ujumbe wa makosa #KATIKA (#N/A) inamaanisha haipatikani (hakuna data) - inaonekana wakati Excel haiwezi kupata thamani unayotafuta. Hii inaweza kutokea kwa sababu kadhaa.
1. Thamani inayotakiwa haijaandikwa vibaya
Ni wazo nzuri kuangalia bidhaa hii kwanza! Typos mara nyingi hutokea unapofanya kazi na kiasi kikubwa sana cha data, inayojumuisha maelfu ya mistari, au wakati thamani unayotafuta imeandikwa kwenye fomula.
2. Hitilafu #N/A wakati wa kutafuta takriban inayolingana na VLOOKUP
Ikiwa unatumia fomula iliyo na takriban hali ya utafutaji inayolingana, yaani hoja tafuta_masafa (range_lookup) ni KWELI au haijabainishwa, fomula yako inaweza kuripoti hitilafu # N / A katika kesi mbili:
- Thamani ya kuangalia ni chini ya thamani ndogo zaidi katika safu inayotazamwa.
- Safu wima ya utafutaji haijapangwa kwa mpangilio wa kupanda.
3. Hitilafu #N/A wakati wa kutafuta inayolingana kabisa na VLOOKUP
Ikiwa unatafuta mechi kamili, yaani hoja tafuta_masafa (range_lookup) ni FALSE na thamani halisi haikupatikana, fomula pia itaripoti hitilafu. # N / A. Pata maelezo zaidi kuhusu jinsi ya kutafuta mlinganisho kamili na wa kukadiria na chaguo la kukokotoa VPR.
4. Safu wima ya utafutaji haiko kushoto kabisa
Kama unavyojua, moja ya mapungufu muhimu zaidi VPR ni kwamba haiwezi kuelekeza upande wa kushoto, kwa hivyo safu wima ya utafutaji kwenye jedwali lako lazima iwe kushoto kabisa. Katika mazoezi, mara nyingi tunasahau kuhusu hili, ambayo inaongoza kwa formula isiyo ya kazi na kosa. # N / A.
Uamuzi: Ikiwa haiwezekani kubadilisha muundo wa data ili safu wima ya utafutaji iwe kushoto kabisa, unaweza kutumia mchanganyiko wa vipengele INDEX (INDEX) na ZAIDI WAZI (MATCH) kama njia mbadala inayonyumbulika zaidi ya VPR.
5. Nambari zimeumbizwa kama maandishi
Chanzo kingine cha makosa # N / A katika fomula na VPR ni nambari katika umbizo la maandishi katika jedwali kuu au jedwali la utafutaji.
Kwa kawaida hii hutokea unapoingiza maelezo kutoka kwa hifadhidata za nje, au unapoandika apostrofi kabla ya nambari ili kuweka sifuri inayoongoza.
Ishara dhahiri zaidi za nambari katika muundo wa maandishi zinaonyeshwa kwenye takwimu hapa chini:
Kwa kuongeza, nambari zinaweza kuhifadhiwa katika muundo ujumla (Jenerali). Katika kesi hii, kuna kipengele kimoja tu kinachoonekana - namba zimewekwa kwenye makali ya kushoto ya seli, wakati kwa default zimewekwa kwenye makali ya kulia.
Uamuzi: Ikiwa ni thamani moja, bonyeza tu kwenye ikoni ya hitilafu na uchague Badilisha hadi Nambari (Badilisha hadi Nambari) kutoka kwa menyu ya muktadha.
Ikiwa hii ndio hali na nambari nyingi, chagua na ubofye kulia kwenye eneo lililochaguliwa. Katika menyu ya muktadha inayoonekana, chagua Umbiza Seli (Viini vya Umbizo) > kichupo Idadi (Nambari) > umbizo Idadi (Nambari) na bonyeza OK.
6. Kuna nafasi mwanzoni au mwishoni
Hii ni sababu angalau dhahiri ya kosa. # N / A katika utendaji VPR, kwa kuwa ni vigumu kuona nafasi hizi za ziada, hasa wakati wa kufanya kazi na meza kubwa, wakati data nyingi haziko kwenye skrini.
Suluhisho la 1: Nafasi za ziada kwenye jedwali kuu (ambapo kazi ya VLOOKUP iko)
Ikiwa nafasi za ziada zinaonekana kwenye jedwali kuu, unaweza kuhakikisha kuwa fomula zinafanya kazi kwa usahihi kwa kuambatanisha hoja kupakua_value (lookup_value) kwenye chaguo la kukokotoa TRIM (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Suluhisho la 2: Nafasi za ziada kwenye jedwali la kuangalia (kwenye safu wima)
Ikiwa nafasi za ziada ziko kwenye safu ya utafutaji - njia rahisi # N / A katika fomula na VPR haiwezi kuepukika. Badala ya VPR Unaweza kutumia fomula ya safu iliyo na mchanganyiko wa vitendakazi INDEX (INDEX), ZAIDI WAZI (MATCH) na 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))
Kwa kuwa hii ni fomula ya safu, usisahau kubonyeza Ctrl + Shift + Ingiza badala ya kawaida kuingiaili kuingiza formula kwa usahihi.
Hitilafu #VALUE! katika fomula zilizo na VLOOKUP
Katika hali nyingi, Microsoft Excel huripoti hitilafu #THAMANI! (#VALUE!) wakati thamani iliyotumika katika fomula hailingani na aina ya data. Kuhusu VPR, basi kuna kawaida sababu mbili za kosa #THAMANI!.
1. Thamani unayotafuta ni ndefu zaidi ya vibambo 255
Kuwa makini: kazi VPR haiwezi kutafuta thamani zilizo na zaidi ya vibambo 255. Ikiwa thamani unayotafuta itazidi kikomo hiki, utapokea ujumbe wa hitilafu. #THAMANI!.
Uamuzi: Tumia rundo la vipengele INDEX+MATCH (INDEX + MATCH). Ifuatayo ni fomula ambayo itafanya vizuri kwa kazi hii:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Njia kamili ya kitabu cha utafutaji haijabainishwa
Ikiwa unarejesha data kutoka kwa kitabu kingine cha kazi, lazima ueleze njia kamili ya faili hiyo. Hasa zaidi, lazima ujumuishe jina la kitabu cha kazi (pamoja na kiendelezi) katika mabano ya mraba [ ], ikifuatiwa na jina la laha, ikifuatiwa na alama ya mshangao. Ujenzi huu wote lazima uambatanishwe katika apostrofi, ikiwa kitabu au jina la karatasi lina nafasi.
Hapa kuna muundo kamili wa kazi VPR kutafuta katika kitabu kingine:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Fomula halisi inaweza kuonekana kama hii:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Fomula hii itatafuta thamani ya seli A2 katika safu B kwenye karatasi Sheet1 katika kitabu cha kazi Bei Mpya na utoe thamani inayolingana kutoka kwa safu D.
Ikiwa sehemu yoyote ya njia ya jedwali imeachwa, kazi yako VPR haitafanya kazi na itaripoti kosa #THAMANI! (hata kama kitabu cha kazi kilicho na jedwali la kuangalia kimefunguliwa kwa sasa).
Kwa habari zaidi kuhusu kipengele VPRukirejelea faili nyingine ya Excel, angalia somo: Kutafuta kitabu kingine cha kazi kwa kutumia VLOOKUP.
3. Nambari ya Safu_ya Hoja ni chini ya 1
Ni vigumu kufikiria hali ambapo mtu anaingia thamani chini ya 1ili kuonyesha safu ambayo thamani itatolewa. Ingawa inawezekana ikiwa thamani ya hoja hii inakokotolewa na chaguo jingine la kukokotoa la Excel lililowekwa ndani VPR.
Hivyo, kama hutokea kwamba hoja col_index_num (nambari_ya_safu) chini ya 1kazi VPR pia itaripoti hitilafu #THAMANI!.
Ikiwa hoja col_index_num (nambari_ya_safu) ni kubwa kuliko idadi ya safu wima katika safu uliyopewa, VPR itaripoti hitilafu #REF! (#SYL!).
Hitilafu #NAME? katika VLOOKUP
Kesi rahisi ni kosa #NAME? (#NAME?) - itaonekana ikiwa utaandika kwa bahati mbaya jina la chaguo la kukokotoa lenye hitilafu.
Suluhisho ni dhahiri - angalia tahajia yako!
VLOOKUP haifanyi kazi (vizuizi, tahadhari na maamuzi)
Mbali na sintaksia ngumu zaidi, VPR ina mapungufu zaidi kuliko kitendakazi kingine chochote cha Excel. Kwa sababu ya mapungufu haya, fomula zinazoonekana kuwa rahisi na VPR mara nyingi husababisha matokeo yasiyotarajiwa. Hapo chini utapata suluhisho kwa matukio kadhaa ya kawaida ambapo VPR ni makosa.
1. VLOOKUP si nyeti kwa ukubwa
kazi VPR haitofautishi kati ya herufi kubwa na inakubali herufi ndogo na kubwa kuwa sawa. Kwa hivyo, ikiwa kuna vipengee kadhaa kwenye jedwali ambavyo vinatofautiana katika hali tu, chaguo la kukokotoa la VLOOKUP litarudisha kipengele cha kwanza kilichopatikana, bila kujali kesi.
Uamuzi: Tumia kitendakazi kingine cha Excel ambacho kinaweza kufanya utafutaji wima (LOOKUP, SUMPRODUCT, INDEX, na MATCH) pamoja na SURAA ambayo inatofautisha kesi. Kwa maelezo zaidi, unaweza kujifunza kutoka kwa somo - njia 4 za kufanya VLOOKUP iwe nyeti kwa kadiri katika Excel.
2. VLOOKUP hurejesha thamani ya kwanza iliyopatikana
Kama unavyojua tayari, VPR hurejesha thamani kutoka kwa safu wima iliyotolewa inayolingana na inayolingana ya kwanza iliyopatikana. Hata hivyo, unaweza kuifanya itoe marudio ya 2, 3, 4, au nyingine yoyote ya thamani unayotaka. Ikiwa unahitaji kutoa maadili yote yanayorudiwa, utahitaji mchanganyiko wa chaguo za kukokotoa INDEX (INDEX), KATIKA (NDOGO) na LINE (SAFU).
3. Safu iliongezwa au kuondolewa kwenye jedwali
Kwa bahati mbaya, formula VPR acha kufanya kazi kila wakati safu mpya inapoongezwa au kuondolewa kwenye jedwali la utafutaji. Hii hutokea kwa sababu syntax VPR inakuhitaji ubainishe safu kamili ya utafutaji na nambari mahususi ya safu wima kwa ajili ya uchimbaji wa data. Kwa kawaida, safu uliyopewa na nambari ya safu wima hubadilika unapofuta safu au kuingiza mpya.
Uamuzi: Na tena utendaji una haraka kusaidia INDEX (INDEX) na ZAIDI WAZI (MECHI). Katika fomula INDEX+MATCH Unafafanua kando safu wima za utafutaji na urejeshaji, na kwa hivyo, unaweza kufuta au kuingiza safu wima nyingi unavyotaka bila kuwa na wasiwasi kuhusu kusasisha fomula zote zinazohusiana za utafutaji.
4. Marejeleo ya seli huharibika wakati wa kunakili fomula
Kichwa hiki kinaelezea kiini cha tatizo kikamilifu, sivyo?
Uamuzi: Tumia marejeleo kamili ya seli kila wakati (pamoja na ishara $) kwenye rekodi masafa, kwa mfano $A$2:C$100 or $A:$C. Katika upau wa fomula, unaweza kubadili haraka aina ya kiungo kwa kubofya F4.
VLOOKUP - kufanya kazi na vipengele vya IFERROR na ISERROR
Ikiwa hutaki kuwatisha watumiaji na ujumbe wa makosa # N / A, #THAMANI! or #NAME?, unaweza kuonyesha kisanduku tupu au ujumbe wako mwenyewe. Unaweza kufanya hivyo kwa kuweka VPR katika utendaji IFERRO (IFERROR) katika Excel 2013, 2010 na 2007 au tumia rundo la vitendaji IF+ISERROR (IF+ISERROR) katika matoleo ya awali.
VLOOKUP: kufanya kazi na chaguo za kukokotoa za IFERROR
Sintaksia ya utendaji IFERRO (IFERROR) ni rahisi na inajieleza yenyewe:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Hiyo ni, kwa hoja ya kwanza unaingiza thamani ili kuangaliwa kwa hitilafu, na kwa hoja ya pili unabainisha nini cha kurejesha ikiwa kosa limepatikana.
Kwa mfano, fomula hii hurejesha seli tupu ikiwa thamani unayotafuta haipatikani:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Ikiwa unataka kuonyesha ujumbe wako mwenyewe badala ya ujumbe wa kawaida wa hitilafu wa kitendakazi VPR, weka katika nukuu, kama hivyo:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: kufanya kazi na kitendakazi cha ISERROR
Tangu utendaji IFERRO ilionekana katika Excel 2007, wakati wa kufanya kazi katika matoleo ya awali utalazimika kutumia mchanganyiko IF (IF) na EOSHIBKA (ISERROR) kama hii:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Kwa mfano, formula IF+ISERROR+VLOOKUP, sawa na fomula IFERRO+VLOOKUPiliyoonyeshwa hapo juu:
=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;ЛОЖЬ))
Ni hayo tu kwa leo. Natumaini mafunzo haya mafupi yatakusaidia kukabiliana na makosa yote iwezekanavyo. VPR na ufanye fomula zako zifanye kazi ipasavyo.