contents
Leson sa a eksplike kijan pou byen vit fè fas ak yon sitiyasyon kote yon fonksyon VPR (VLOOKUP) pa vle travay nan Excel 2013, 2010, 2007 ak 2003, ak ki jan yo idantifye ak ranje erè komen ak simonte limit. VPR.
Nan plizyè atik anvan yo, nou te eksplore divès aspè nan fonksyon an VPR nan Excel. Si ou te li yo ak anpil atansyon, ou ta dwe kounye a yon ekspè nan domèn sa a. Sepandan, se pa san rezon ke anpil ekspè Excel kwè VPR youn nan karakteristik yo ki pi konplèks. Li gen yon pakèt limit ak karakteristik ki vin sous la nan anpil pwoblèm ak erè.
Nan atik sa a ou pral jwenn eksplikasyon senp sou erè #AT (#N/A), #NAME? (#NOM?) ak #VALUE! (#VALÈ!) ki parèt lè w ap travay ak fonksyon an VPR, osi byen ke teknik ak metòd pou fè fas ak yo. Nou pral kòmanse ak ka ki pi komen yo ak rezon ki pi evidan poukisa. VPR pa travay, kidonk li se pi bon etidye egzanp yo nan lòd yo bay yo nan atik la.
Fikse #N/A erè nan fonksyon VLOOKUP nan Excel
Nan fòmil ak VPR mesaj erè #AT (#N/A) vle di pa disponib (pa gen done) - parèt lè Excel pa ka jwenn valè w ap chèche a. Sa ka rive pou plizyè rezon.
1. Valè vle a mal eple
Bon lide pou tcheke atik sa a an premye! Typos souvan rive lè w ap travay ak gwo kantite done, ki gen plizyè milye liy, oswa lè valè w ap chèche a ekri nan yon fòmil.
2. #N/A erè lè w ap chèche yon match apwoksimatif ak VLOOKUP
Si w itilize yon fòmil ak yon kondisyon rechèch matche apwoksimatif, sa vle di agiman range_lookup (range_lookup) se VRE oswa pa espesifye, fòmil ou a ka rapòte yon erè # N / A nan de ka:
- Valè pou gade anlè a pi piti pase valè ki pi piti nan etalaj la ap gade anlè a.
- Kolòn rechèch la pa klase nan lòd monte.
3. #N/A erè lè w ap chèche yon match egzak ak VLOOKUP
Si w ap chèche pou yon match egzak, sa vle di agiman range_lookup (range_lookup) se FO ak valè egzak la pa te jwenn, fòmil la pral rapòte tou yon erè # N / A. Aprann plis sou fason pou chèche alimèt egzak ak apwoksimatif ak yon fonksyon VPR.
4. Kolòn rechèch la pa agoch
Kòm pwobableman ou konnen, youn nan limit ki pi enpòtan VPR se ke li pa ka fè fas a goch la, kidonk kolòn rechèch la nan tab ou a dwe pi goch. Nan pratik, nou souvan bliye sou sa a, ki mennen nan yon fòmil ki pa travay ak yon erè. # N / A.
Desizyon: Si li pa posib pou chanje estrikti done a pou kolòn rechèch la se pi goch la, ou ka itilize yon konbinezon de fonksyon. Main Index (ENDEX) ak PLIS Ekspoze (MATCH) kòm yon altènatif pi fleksib pou VPR.
5. Nimewo yo fòma kòm tèks
Yon lòt sous erè # N / A nan fòmil ak VPR se nimewo nan fòma tèks nan tablo prensipal la oswa tablo rechèch la.
Sa rive anjeneral lè w enpòte enfòmasyon ki soti nan baz done ekstèn, oswa lè w tape yon apòt anvan yon nimewo pou kenbe zewo ki mennen an.
Siy ki pi evidan nan yon nimewo nan fòma tèks yo montre nan figi ki anba a:
Anplis de sa, nimewo yo ka estoke nan fòma a jeneral (Jeneral). Nan ka sa a, gen yon sèl karakteristik aparan - nimewo yo aliyen ak kwen gòch selil la, pandan y ap pa default yo aliyen ak kwen dwat la.
Desizyon: Si se yon sèl valè, jis klike sou icon erè a epi chwazi Konvèti nan nimewo (Konvèti an Nimewo) nan meni kontèks la.
Si sa a se sitiyasyon an ak anpil nimewo, chwazi yo ak dwa-klike sou zòn nan chwazi. Nan meni kontèks ki parèt, chwazi Fòma Selil yo (Fòma selil) > tab Nimewo (Nimewo) > fòma Nimewo (Nimerik) ak laprès OK.
6. Gen yon espas nan kòmansman oswa nan fen
Sa a se rezon ki pi piti evidan pou erè a. # N / A nan fonksyon VPR, Piske li vizyèlman difisil pou wè espas siplemantè sa yo, sitou lè w ap travay ak gwo tab, lè pifò done yo pa sou ekran an.
Solisyon 1: Espas siplemantè nan tablo prensipal la (kote fonksyon VLOOKUP la)
Si espas siplemantè parèt nan tablo prensipal la, ou ka asire fòmil yo fonksyone kòrèkteman lè w mete agiman an valè_chèch (lookup_value) nan yon fonksyon TRIM (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Solisyon 2: Espas siplemantè nan tablo rechèch la (nan kolòn rechèch la)
Si espas siplemantè yo nan kolòn rechèch la - fason ki senp # N / A nan fòmil la avèk VPR pa ka evite. Olye de VPR Ou ka itilize yon fòmil etalaj ak yon konbinezon de fonksyon Main Index (ENDEX), PLIS Ekspoze (MATCH) и 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))
Depi sa a se yon fòmil etalaj, pa bliye peze Ctrl + Shift + Mete olye pou yo nòmal la Antre nanpou antre fòmil la kòrèkteman.
Erè #VALUE! nan fòmil ak VLOOKUP
Nan pifò ka yo, Microsoft Excel rapòte yon erè #VALUE! (#VALÈ!) lè valè yo itilize nan fòmil la pa matche ak kalite done yo. Konsènan VPR, Lè sa a, anjeneral gen de rezon pou erè a #VALUE!.
1. Valè w ap chèche a pi long pase 255 karaktè
Fè atansyon: fonksyon VPR pa ka chèche valè ki gen plis pase 255 karaktè. Si valè w ap chèche a depase limit sa a, w ap resevwa yon mesaj erè. #VALUE!.
Desizyon: Sèvi ak yon pakèt karakteristik ENDEX+MATCH (ENDEX + MATCH). Anba a se yon fòmil ki pral fè jis byen pou travay sa a:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Chemen konplè nan liv travay rechèch la pa espesifye
Si w ap rekipere done ki soti nan yon lòt liv travay, ou dwe presize chemen konplè nan dosye sa a. Plis espesifikman, ou dwe mete non liv travay la (ki gen ladan ekstansyon an) nan parantèz kare [ ], ki te swiv pa non fèy la, ki te swiv pa yon pwen esklamasyon. Tout konstriksyon sa a dwe fèmen nan apòt, nan ka non liv la oswa fèy la gen espas.
Isit la se estrikti konplè fonksyon an VPR pou chèche nan yon lòt liv:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Fòmil reyèl la ta ka sanble tankou sa a:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Fòmil sa a pral gade valè selil la A2 nan yon kolòn B sou fèy la Sheet1 nan liv travay la Nouvo Pri epi ekstrè valè ki koresponn lan nan kolòn nan D.
Si nenpòt pati nan chemen tab la omisyon, fonksyon ou VPR pa pral travay epi yo pral rapòte yon erè #VALUE! (menm si liv travay la ak tab rechèch la louvri kounye a).
Pou plis enfòmasyon sou fonksyon an VPRreferans yon lòt dosye Excel, gade leson an: Chèche yon lòt liv travay lè l sèvi avèk VLOOKUP.
3. Agiman Column_num se mwens pase 1
Li difisil pou imajine yon sitiyasyon kote yon moun antre nan yon valè mwens pase 1pou endike kolòn nan kote yo ekstrè valè a. Malgre ke li posib si valè agiman sa a kalkile pa yon lòt fonksyon Excel enbrike nan VPR.
Se konsa, si li rive ke agiman an kol_index_num (column_number) mwens pase 1fonksyon VPR pral rapòte tou yon erè #VALUE!.
Si agiman an kol_index_num (column_number) pi gran pase kantite kolòn nan etalaj yo bay la, VPR pral rapòte yon erè #REF! (#SSYL!).
Erè #NAME? nan VLOOKUP
Ka ki pi senp la se yon erè #NAME? (#NOM?) - ap parèt si ou aksidantèlman ekri yon non fonksyon ak yon erè.
Solisyon an evidan - tcheke òtograf ou!
VLOOKUP pa travay (limitasyon, opozisyon ak desizyon)
Anplis sentaks la olye konplike, VPR gen plis limit pase nenpòt lòt fonksyon Excel. Paske nan limit sa yo, fòmil w pèdi senp ak VPR souvan mennen nan rezilta inatandi. Anba a ou pral jwenn solisyon pou plizyè senaryo komen kote VPR se sa ki mal.
1. VLOOKUP se pa ka sansib
fonksyon VPR pa fè distenksyon ant majiskil epi li aksepte karaktè miniskil ak majiskil kòm menm bagay la. Se poutèt sa, si gen plizyè eleman nan tablo a ki diferan sèlman nan ka, fonksyon VLOOKUP ap retounen premye eleman yo jwenn, kèlkeswa ka.
Desizyon: Sèvi ak yon lòt fonksyon Excel ki ka fè yon rechèch vètikal (LOOKUP, SUMPRODUCT, INDEX, ak MATCH) an konbinezon ak EXACTA ki distenge ka. Pou plis detay, ou ka aprann nan leson an - 4 fason yo fè VLOOKUP ka-sansib nan Excel.
2. VLOOKUP retounen premye valè yo jwenn
Kòm ou deja konnen, VPR retounen valè ki soti nan kolòn yo bay la ki koresponn ak premye match yo jwenn. Sepandan, ou ka fè li ekstrè 2yèm, 3yèm, 4yèm, oswa nenpòt lòt repetisyon nan valè ou vle a. Si ou bezwen ekstrè tout valè kopi, w ap bezwen yon konbinezon de fonksyon Main Index (ENDEX), PI BON (TI) ak LINE (RANJE).
3. Yo te ajoute oswa retire yon kolòn sou tab la
Malerezman, fòmil yo VPR sispann travay chak fwa yo ajoute oswa retire yon nouvo kolòn nan tablo rechèch la. Sa rive paske sentaks la VPR mande pou w presize seri konplè rechèch la ak nimewo kolòn espesifik pou fè ekstraksyon done. Natirèlman, tou de seri yo bay la ak nimewo kolòn nan chanje lè ou efase yon kolòn oswa mete yon nouvo.
Desizyon: Epi ankò fonksyon yo prese ede Main Index (ENDEX) ak PLIS Ekspoze (MATCH). Nan fòmil la ENDEX+MATCH Ou separe kolòn rechèch ak rekipere, epi kòm yon rezilta, ou ka efase oswa mete anpil kolòn jan ou vle san enkyete w pou w gen mete ajou tout fòmil rechèch ki gen rapò.
4. Referans selilè yo konfuze lè w ap kopye yon fòmil
Tit sa a eksplike sans nan pwoblèm nan konplè, dwa?
Desizyon: Toujou itilize referans selil absoli (ak senbòl la $) sou dosye seri a, pou egzanp $A$2:$C$100 or $A:$C. Nan ba fòmil la, ou ka byen vit chanje kalite lyen an lè w klike F4.
VLOOKUP - travay ak fonksyon yo IFERROR ak ISERROR
Si ou pa vle fè pè itilizatè yo ak mesaj erè # N / A, #VALUE! or #NAME?, ou ka montre yon selil vid oswa pwòp mesaj ou a. Ou ka fè sa pa mete VPR nan yon fonksyon SI ERREUR (IFERROR) nan Excel 2013, 2010 ak 2007 oswa itilize yon pakèt fonksyon SI+ÈRÈ (IF+ISERROR) nan vèsyon pi bonè.
VLOOKUP: travay ak fonksyon IFERROR
Fonksyon sentaks SI ERREUR (IFERROR) se senp epi li pale pou tèt li:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Sa vle di, pou premye agiman an ou mete valè a yo dwe tcheke pou yon erè, epi pou dezyèm agiman an ou presize sa yo retounen si yo jwenn yon erè.
Pou egzanp, fòmil sa a retounen yon selil vid si valè w ap chèche a pa jwenn:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Si ou vle montre pwòp mesaj ou olye de mesaj erè estanda fonksyon an VPR, mete l 'nan quotes, tankou sa a:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: travay ak fonksyon ISERROR
Depi fonksyon an SI ERREUR parèt nan Excel 2007, lè w ap travay nan vèsyon pi bonè w ap oblije itilize konbinezon an IF (SI) ak EOSHIBKA (ISERROR) tankou sa a:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Pou egzanp, fòmil la SI+ÈRÈ+VCHÈCHE, menm jan ak fòmil la SI ERREUR+VCHACHUPmontre pi wo a:
=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;ЛОЖЬ))
Se tout pou jodia. Mwen espere ti leson patikilye sa a pral ede w fè fas ak tout erè posib. VPR epi fè fòmil ou yo mache kòrèkteman.