Werrej
Din il-lezzjoni tispjega kif malajr tittratta sitwazzjoni fejn funzjoni VPR (VLOOKUP) ma tridx taħdem f'Excel 2013, 2010, 2007 u 2003, u kif tidentifika u tiffissa żbalji komuni u tegħleb il-limitazzjonijiet VPR.
F'diversi artikli preċedenti, esplorajna l-aspetti varji tal-funzjoni VPR f'Excel. Jekk qrajthom bir-reqqa, issa għandek tkun espert f'dan il-qasam. Madankollu, mhuwiex bla raġuni li ħafna esperti Excel jemmnu VPR waħda mill-aktar karatteristiċi kumplessi. Għandu mazz ta 'limitazzjonijiet u karatteristiċi li jsiru s-sors ta' ħafna problemi u żbalji.
F'dan l-artikolu se ssib spjegazzjonijiet sempliċi ta 'żbalji #AT (#N/A), #NAME? (#ISEM?) u #VALUE! (#VALUR!) li jidhru meta taħdem mal-funzjoni VPR, kif ukoll tekniki u metodi kif jiġu ttrattati. Nibdew bl-aktar każijiet komuni u r-raġunijiet l-aktar ovvji għaliex. VPR ma taħdimx, għalhekk huwa aħjar li tistudja l-eżempji fl-ordni li fiha jingħataw fl-artiklu.
Iffissar ta 'żball #N/A fil-funzjoni VLOOKUP f'Excel
Fil-formuli ma VPR messaġġ ta ’żball #AT (#N/A) tfisser mhux disponibbli (l-ebda data) – jidher meta Excel ma jkunx jista’ jsib il-valur li qed tfittex. Dan jista' jiġri għal diversi raġunijiet.
1. Il-valur mixtieq huwa miktub ħażin
Idea tajba li tiċċekkja dan l-oġġett l-ewwel! Ħafna drabi jseħħu typos meta taħdem b'ammonti kbar ħafna ta' dejta, li jikkonsistu f'eluf ta' linji, jew meta l-valur li qed tfittex ikun miktub f'formula.
2. #N/A żball meta tkun qed tfittex taqbila approssimattiva ma' VLOOKUP
Jekk tuża formula b'kundizzjoni approssimattiva ta' tfittxija ta' tqabbil, jiġifieri argument range_lookup (range_lookup) huwa VERU jew mhux speċifikat, il-formula tiegħek tista' tirrapporta żball # N / A f'żewġ każijiet:
- Il-valur li wieħed ifittex huwa inqas mill-iżgħar valur fil-firxa li tkun qed tfittex.
- Il-kolonna tat-tfittxija mhix magħżula f'ordni axxendenti.
3. #N/A żball meta tfittex taqbila eżatta mal-VLOOKUP
Jekk qed tfittex taqbila eżatta, jiġifieri argument range_lookup (range_lookup) huwa FALZ u l-valur eżatt ma nstabx, il-formula se tirrapporta wkoll żball # N / A. Tgħallem aktar dwar kif tfittex logħbiet eżatti u approssimattivi b'funzjoni VPR.
4. Il-kolonna tat-tfittxija mhix l-aktar fuq ix-xellug
Kif probabilment taf, waħda mill-limitazzjonijiet l-aktar sinifikanti VPR huwa li ma tistax tiffaċċja lejn ix-xellug, għalhekk il-kolonna tal-ħarsa fit-tabella tiegħek trid tkun l-aktar fuq ix-xellug. Fil-prattika, ħafna drabi ninsew dwar dan, li jwassal għal formula li ma taħdimx u żball. # N / A.
Deċiżjoni: Jekk ma jkunx possibbli li tinbidel l-istruttura tad-dejta sabiex il-kolonna tat-tfittxija tkun l-aktar fuq ix-xellug, tista’ tuża taħlita ta’ funzjonijiet INDIĊI (INDIĊI) u AKTAR ESPOSTI (MATCH) bħala alternattiva aktar flessibbli għal VPR.
5. In-numri huma fformattjati bħala test
Sors ieħor ta 'żball # N / A fil-formuli ma VPR huma numri f'format ta' test fit-tabella prinċipali jew fit-tabella ta' tfittxija.
Dan normalment jiġri meta timporta informazzjoni minn databases esterni, jew meta ttajpja appostrofu qabel numru biex iżżomm iż-żero ewlieni.
L-aktar sinjali ovvji ta 'numru f'format ta' test huma murija fil-figura hawn taħt:
Barra minn hekk, in-numri jistgħu jinħażnu fil-format ġenerali (Ġenerali). F'dan il-każ, hemm karatteristika waħda biss notevoli - in-numri huma allinjati mat-tarf tax-xellug taċ-ċellula, filwaqt li awtomatikament huma allinjati mat-tarf tal-lemin.
Deċiżjoni: Jekk huwa valur wieħed, ikklikkja fuq l-ikona tal-iżball u agħżel Ikkonverti għal Numru (Aqleb għal Numru) mill-menu tal-kuntest.
Jekk din hija s-sitwazzjoni b'ħafna numri, agħżelhom u kklikkja bil-lemin fuq iż-żona magħżula. Fil-menu tal-kuntest li jidher, agħżel Format Ċelloli (Format Ċelloli) > tab Numru (Numru) > format Numru (Numeriku) u agħfas OK.
6. Hemm spazju fil-bidu jew fl-aħħar
Din hija l-inqas raġuni ovvja għall-iżball. # N / A fil-funzjoni VPR, peress li huwa viżwalment diffiċli li tara dawn l-ispazji żejda, speċjalment meta taħdem ma 'tabelli kbar, meta l-biċċa l-kbira tad-data hija barra mill-iskrin.
Soluzzjoni 1: Spazji żejda fit-tabella prinċipali (fejn hemm il-funzjoni VLOOKUP)
Jekk jidhru spazji żejda fit-tabella prinċipali, tista' tiżgura li l-formuli jaħdmu b'mod korrett billi tehmeż l-argument lookup_value (lookup_value) f'funzjoni TRIM (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Soluzzjoni 2: Spazji żejda fit-tabella ta' tfittxija (fil-kolonna ta' tfittxija)
Jekk hemm spazji żejda fil-kolonna tat-tfittxija - modi sempliċi # N / A fil-formula bil VPR ma jistax jiġi evitat. Minflok VPR Tista 'tuża formula ta' firxa b'kombinazzjoni ta 'funzjonijiet INDIĊI (INDIĊI), AKTAR ESPOSTI (TAQBIL) и 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))
Peress li din hija formula array, tinsiex tagħfas Ctrl + Shift + Daħħal minflok is-soltu Iktebbiex iddaħħal il-formula b'mod korrett.
Żball #VALUR! fil-formuli ma VLOOKUP
Fil-biċċa l-kbira tal-każijiet, Microsoft Excel jirrapporta żball #VALUE! (#VALUR!) meta l-valur użat fil-formula ma jaqbilx mat-tip tad-dejta. Dwar VPR, allura ġeneralment ikun hemm żewġ raġunijiet għall-iżball #VALUE!.
1. Il-valur li qed tfittex huwa itwal minn 255 karattru
Oqgħod attent: funzjoni VPR ma tistax tfittex valuri li fihom aktar minn 255 karattru. Jekk il-valur li qed tfittex jaqbeż dan il-limitu, tirċievi messaġġ ta 'żball. #VALUE!.
Deċiżjoni: Uża mazz ta 'karatteristiċi INDIĊI+TAQBIL (INDIĊI + TAQBIL). Hawn taħt hawn formula li se tagħmel tajjeb għal dan il-kompitu:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Il-mogħdija sħiħa għall-ktieb tax-xogħol tat-tfittxija mhix speċifikata
Jekk qed tirkupra data minn ktieb tax-xogħol ieħor, trid tispeċifika t-triq sħiħa għal dak il-fajl. B'mod aktar speċifiku, trid tinkludi l-isem tal-ktieb tax-xogħol (inkluża l-estensjoni) fil-parentesi kwadri [ ], segwit mill-isem tal-folja, segwit minn punt ta 'esklamazzjoni. Din il-kostruzzjoni kollha trid tkun magħluqa f'apostrofi, f'każ li l-isem tal-ktieb jew tal-folja jkun fih spazji.
Hawnhekk hawn l-istruttura sħiħa tal-funzjoni VPR biex tfittex fi ktieb ieħor:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Il-formula reali tista' tidher bħal din:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Din il-formula se tfittex il-valur taċ-ċellula A2 f’kolonna B fuq il-folja Sheet1 fil-ktieb tax-xogħol Prezzijiet ġodda u estratt il-valur korrispondenti mill-kolonna D.
Jekk xi parti mill-mogħdija tat-tabella titħalla barra, il-funzjoni tiegħek VPR mhux se jaħdem u se jirrapporta żball #VALUE! (anki jekk il-ktieb tax-xogħol bit-tabella ta 'tfittxija bħalissa huwa miftuħ).
Għal aktar informazzjoni dwar il-funzjoni VPRb'referenza għal fajl Excel ieħor, ara l-lezzjoni: Tiftix ktieb tax-xogħol ieħor billi tuża VLOOKUP.
3. Argument Column_num huwa inqas minn 1
Huwa diffiċli li wieħed jimmaġina sitwazzjoni fejn xi ħadd jidħol valur inqas minn 1biex tindika l-kolonna minn fejn jiġi estratt il-valur. Għalkemm huwa possibbli jekk il-valur ta 'dan l-argument jiġi kkalkulat minn funzjoni oħra ta' Excel imnaqqsa fi ħdan VPR.
Allura, jekk jiġri li l-argument col_index_num (column_number) inqas minn 1funzjoni VPR se tirrapporta wkoll żball #VALUE!.
Jekk l-argument col_index_num (numru_kolonna) huwa akbar min-numru ta' kolonni fil-firxa mogħtija, VPR se tirrapporta żball # REF! (#SSIL!).
Żball #ISEM? fil-LOOKUP
L-aktar każ sempliċi huwa żball #NAME? (#ISEM?) – se jidher jekk aċċidentalment tikteb isem tal-funzjoni bi żball.
Is-soluzzjoni hija ovvja - iċċekkja l-ortografija tiegħek!
VLOOKUP ma taħdimx (limitazzjonijiet, twissijiet u deċiżjonijiet)
Minbarra s-sintassi pjuttost ikkumplikata, VPR għandha aktar limitazzjonijiet minn kwalunkwe funzjoni oħra ta' Excel. Minħabba dawn il-limitazzjonijiet, formoli apparentement sempliċi bil VPR ħafna drabi jwasslu għal riżultati mhux mistennija. Hawn taħt għandek issib soluzzjonijiet għal diversi xenarji komuni fejn VPR huwa żbaljat.
1. VLOOKUP mhijiex sensittiva għall-każ
funzjoni VPR ma jiddistingwix bejn il-każ u jaċċetta karattri żgħar u kbar bħala l-istess. Għalhekk, jekk hemm diversi elementi fit-tabella li jvarjaw biss fil-każ, il-funzjoni VLOOKUP tirritorna l-ewwel element misjub, irrispettivament mill-każ.
Deċiżjoni: Uża funzjoni oħra ta' Excel li tista' twettaq tfittxija vertikali (LOOKUP, SUMPRODUCT, INDEX, u MATCH) flimkien ma' EXACTA li jiddistingwi każ. Għal aktar dettalji, tista 'titgħallem mil-lezzjoni - 4 modi kif tagħmel VLOOKUP sensittiv għall-każi f'Excel.
2. VLOOKUP jirritorna l-ewwel valur misjub
Kif diġà taf, VPR jirritorna l-valur mill-kolonna mogħtija li tikkorrispondi għall-ewwel tqabbil misjub. Madankollu, jista 'jkollok estratt it-2, 3, 4, jew kwalunkwe ripetizzjoni oħra tal-valur li trid. Jekk għandek bżonn tiġbed il-valuri duplikati kollha, ser ikollok bżonn taħlita ta 'funzjonijiet INDIĊI (INDIĊI), INQAS (ŻGĦAR) u LINJA (ROW).
3. Ġiet miżjuda jew imneħħija kolonna mat-tabella
Sfortunatament, il-formuli VPR tieqaf taħdem kull darba li tiżdied jew titneħħa kolonna ġdida fit-tabella ta' tfittxija. Dan jiġri minħabba li s-sintassi VPR teħtieġ li tispeċifika l-firxa sħiħa tat-tfittxija u n-numru speċifiku tal-kolonna għall-estrazzjoni tad-dejta. Naturalment, kemm il-firxa mogħtija kif ukoll in-numru tal-kolonna jinbidlu meta tħassar kolonna jew daħħal waħda ġdida.
Deċiżjoni: U għal darb'oħra l-funzjonijiet huma mgħaġġla biex jgħinu INDIĊI (INDIĊI) u AKTAR ESPOSTI (TAQBIL). Fil-formula INDIĊI+TAQBIL Inti tiddefinixxi separatament il-kolonni tat-tiftix u l-irkupru, u bħala riżultat, tista 'tħassar jew daħħal kolonni kemm trid mingħajr ma tinkwieta dwar li jkollok taġġorna l-formuli ta' tfittxija kollha relatati.
4. Ir-referenzi taċ-ċelluli huma mħawda meta tikkopja formula
Din l-intestatura tispjega l-essenza tal-problema b'mod eżawrjenti, hux?
Deċiżjoni: Dejjem uża referenzi assoluti taċ-ċelluli (bis-simbolu $) fuq ir-rekords tal-firxa, pereżempju $A$2:$C$100 or $A:$C. Fil-bar tal-formula, tista 'malajr taqleb it-tip ta' link billi tikklikkja F4.
VLOOKUP – taħdem mal-funzjonijiet IFERROR u ISERROR
Jekk ma tridx tbeżża' lill-utenti b'messaġġi ta' żball # N / A, #VALUE! or #NAME?, tista' turi ċellola vojta jew il-messaġġ tiegħek. Tista 'tagħmel dan billi tpoġġi VPR f'funzjoni JEKK Żball (IFERROR) f'Excel 2013, 2010 u 2007 jew uża għadd ta' funzjonijiet JEKK + ISERROR (IF+ISERROR) f'verżjonijiet preċedenti.
VLOOKUP: taħdem mal-funzjoni IFERROR
Sintassi tal-funzjoni JEKK Żball (IFERROR) huwa sempliċi u jitkellem waħdu:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Jiġifieri, għall-ewwel argument daħħal il-valur li jrid jiġi ċċekkjat għal żball, u għat-tieni argument inti tispeċifika x'għandek tirritorna jekk jinstab żball.
Pereżempju, din il-formula tirritorna ċellula vojta jekk il-valur li qed tfittex ma jinstabx:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Jekk trid turi l-messaġġ tiegħek minflok il-messaġġ ta 'żball standard tal-funzjoni VPR, poġġiha bil-kwotazzjonijiet, bħal hekk:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: taħdem mal-funzjoni ISERROR
Peress li l-funzjoni JEKK Żball deher f'Excel 2007, meta taħdem f'verżjonijiet preċedenti ser ikollok tuża l-kombinazzjoni IF (IF) u EOSHIBKA (ISERROR) bħal dan:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Per eżempju, il-formula JEKK+HIJAŻERROR+LOOKUP, simili għall-formula JEKK Żball+LOOKUPmuri hawn fuq:
=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;ЛОЖЬ))
Dak kollu għal-lum. Nispera li dan it-tutorja qasira jgħinek tittratta l-iżbalji kollha possibbli. VPR u tagħmel il-formuli tiegħek jaħdmu b'mod korrett.