Kaundan
Kini nga leksyon nagpatin-aw kon unsaon pag-atubang dayon sa usa ka sitwasyon diin ang usa ka function VPR (VLOOKUP) dili gusto nga magtrabaho sa Excel 2013, 2010, 2007 ug 2003, ug unsaon pag-ila ug pag-ayo sa kasagarang mga sayop ug pagbuntog sa mga limitasyon VPR.
Sa ubay-ubay nga nangaging mga artikulo, among gisusi ang lainlaing mga bahin sa function VPR sa Excel. Kung gibasa nimo kini pag-ayo, kinahanglan ka karon nga usa ka eksperto sa kini nga natad. Bisan pa, dili kini walay rason nga daghang mga eksperto sa Excel ang nagtuo VPR usa sa mas komplikado nga mga bahin. Kini adunay daghang mga limitasyon ug mga bahin nga mahimong gigikanan sa daghang mga problema ug mga sayup.
Niini nga artikulo makit-an nimo ang yano nga mga pagpasabut sa mga sayup #AT (#N/A), #NAME? (#NAME?) ug #BILI! (#VALUE!) nga makita kung nagtrabaho kauban ang function VPR, ingon man mga teknik ug pamaagi sa pag-atubang niini. Magsugod kita sa labing kasagaran nga mga kaso ug ang labing klaro nga mga hinungdan ngano. VPR dili molihok, mao nga mas maayo nga tun-an ang mga pananglitan sa pagkasunod-sunod diin kini gihatag sa artikulo.
Pag-ayo sa #N/A error sa VLOOKUP function sa Excel
Sa mga pormula nga adunay VPR mensahe sa sayup #AT (#N/A) nagpasabot dili mahimo (walay data) – makita kung dili makit-an sa Excel ang kantidad nga imong gipangita. Kini mahitabo tungod sa daghang mga hinungdan.
1. Ang gitinguha nga bili sayop nga spelling
Maayong ideya nga susihon una kini nga butang! Ang typos kasagaran mahitabo kung nagtrabaho ka nga adunay daghan kaayo nga datos, nga naglangkob sa liboan ka mga linya, o kung ang kantidad nga imong gipangita gisulat sa usa ka pormula.
2. #N/A kasaypanan sa dihang nangita ug gibanabanang tugma sa VLOOKUP
Kung mogamit ka usa ka pormula nga adunay gibanabana nga kondisyon sa pagpangita sa posporo, ie argumento range_lookup (range_lookup) TINUOD o wala gipiho, ang imong pormula mahimong magreport sa usa ka sayup # N / A sa duha ka kaso:
- Ang bili nga tan-awon mas ubos kay sa pinakagamay nga bili sa array nga gitan-aw.
- Ang kolum sa pagpangita wala gihan-ay sa pataas nga han-ay.
3. #N/A kasaypanan kung nangitag eksaktong tugma sa VLOOKUP
Kung nangita ka usa ka eksaktong tugma, ie argumento range_lookup (range_lookup) mao ang FALSE ug ang eksaktong kantidad wala makit-an, ang pormula usab magreport sa usa ka sayup # N / A. Pagkat-on og dugang kon unsaon pagpangita sa eksakto ug gibanabana nga mga tugma nga adunay function VPR.
4. Ang kolum sa pagpangita dili pinakawala
Sama sa imong nahibal-an, usa sa labing hinungdanon nga mga limitasyon VPR kay dili kini makaatubang sa wala, busa ang kolum sa pagpangita sa imong lamesa kinahanglan nga labing wala. Sa praktis, kanunay natong kalimtan ang bahin niini, nga mosangpot sa usa ka dili nagtrabaho nga pormula ug usa ka sayup. # N / A.
Desisyon: Kung dili posible nga usbon ang istruktura sa datos aron ang kolum sa pagpangita mao ang labing wala, mahimo nimong gamiton ang kombinasyon sa mga gimbuhaton INDEX (INDEX) ug MAS EXPOSED (MATCH) isip mas flexible nga alternatibo para sa VPR.
5. Ang mga numero giporma ingong teksto
Laing tinubdan sa sayop # N / A sa mga pormula nga adunay VPR mao ang mga numero sa text format sa main table o lookup table.
Kini kasagarang mahitabo kung mag-import ka og impormasyon gikan sa mga external nga database, o kung mag-type ka og apostrophe sa wala pa ang usa ka numero aron magpabilin ang nag-unang zero.
Ang labing klaro nga mga timailhan sa usa ka numero sa format sa teksto gipakita sa numero sa ubos:
Dugang pa, ang mga numero mahimong tipigan sa format Kinatibuk-ang (General). Sa kini nga kaso, adunay usa lamang ka mamatikdan nga bahin - ang mga numero gipahiangay sa wala nga ngilit sa cell, samtang pinaagi sa default sila gilinya sa tuo nga ngilit.
Desisyon: Kung kini usa ka kantidad, pag-klik lang sa icon nga sayup ug pilia I-convert sa Numero (Convert sa Numero) gikan sa menu sa konteksto.
Kung kini ang sitwasyon nga adunay daghang mga numero, pilia kini ug pag-right-click sa gipili nga lugar. Sa menu sa konteksto nga makita, pilia Mga Format sa Cell (Format Cells) > tab Numero (Numero) > pormat Numero (Numeric) ug press OK.
6. Adunay luna sa sinugdanan o sa kataposan
Kini ang labing gamay nga klaro nga hinungdan sa sayup. # N / A sa function VPR, tungod kay lisud tan-awon ang pagtan-aw niining mga dugang nga mga wanang, labi na kung nagtrabaho uban ang dagkong mga lamesa, kung kadaghanan sa mga datos wala sa screen.
Solusyon 1: Dugang nga mga luna sa main table (diin ang VLOOKUP function)
Kung ang dugang nga mga luna makita sa main table, mahimo nimong masiguro nga ang mga pormula molihok sa husto pinaagi sa paglakip sa argumento lookup_value (lookup_value) ngadto sa usa ka function TRIM (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Solusyon 2: Dugang nga mga luna sa lookup table (sa lookup column)
Kung ang dugang nga mga luna naa sa kolum sa pagpangita - yano nga mga paagi # N / A sa pormula nga adunay VPR dili malikayan. Imbes nga VPR Mahimo nimong gamiton ang usa ka pormula sa array nga adunay kombinasyon sa mga gimbuhaton INDEX (INDEX), MAS EXPOSED (MATCH) ug 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))
Tungod kay kini usa ka array nga pormula, ayaw kalimot sa pagpindot Ctrl + Shift + Enter imbes sa naandan Pagsulodsa pagsulod sa pormula sa husto nga paagi.
Sayop #VALUE! sa mga pormula nga adunay VLOOKUP
Sa kadaghanan nga mga kaso, ang Microsoft Excel nagreport sa usa ka sayup #BILI! (#VALUE!) kung ang kantidad nga gigamit sa pormula dili motakdo sa tipo sa datos. Mahitungod VPR, unya kasagaran adunay duha ka rason sa sayop #BILI!.
1. Ang bili nga imong gipangita kay mas taas pa sa 255 ka karakter
Pag-amping: function VPR dili makapangita alang sa mga kantidad nga adunay labaw sa 255 nga mga karakter. Kung ang kantidad nga imong gipangita molapas sa kini nga limitasyon, makadawat ka usa ka mensahe sa sayup. #BILI!.
Desisyon: Paggamit usa ka hugpong sa mga bahin INDEX+MATCH (INDEX + MATCH). Sa ubos mao ang usa ka pormula nga maayo alang niini nga buluhaton:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Ang bug-os nga dalan sa search workbook wala gipiho
Kung nagkuha ka ug data gikan sa lain nga workbook, kinahanglan nimong ipiho ang tibuuk nga agianan sa kana nga file. Sa mas espesipiko, kinahanglan nimong iapil ang ngalan sa workbook (lakip ang extension) sa mga square bracket [ ], gisundan sa ngalan sa sheet, gisundan sa usa ka exclamation point. Ang tanan nga kini nga pagtukod kinahanglan nga gilakip sa mga apostrophe, kung ang ngalan sa libro o sheet adunay mga espasyo.
Ania ang kompleto nga istruktura sa function VPR sa pagpangita sa laing libro:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Ang tinuod nga pormula mahimong tan-awon sama niini:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Kini nga pormula mangita sa kantidad sa cell A2 sa usa ka kolum B sa palid Palid 1 sa workbook Bag-ong mga Presyo ug kuhaa ang katumbas nga kantidad gikan sa kolum D.
Kung ang bisan unsang bahin sa agianan sa lamesa wala iapil, ang imong function VPR dili molihok ug magreport sa usa ka sayup #BILI! (bisan kung ang workbook nga adunay lamesa sa pagpangita karon bukas).
Alang sa dugang nga kasayuran bahin sa function VPRpaghisgot sa laing Excel file, tan-awa ang leksyon: Pagpangita sa laing workbook gamit ang VLOOKUP.
3. Ang Argument Column_num kay kubos sa 1
Lisud mahanduraw ang usa ka sitwasyon diin ang usa ka tawo mosulod sa usa ka kantidad nga mas ubos kaysa 1aron ipakita ang kolum diin makuha ang kantidad. Bisan kung posible kung ang kantidad sa kini nga argumento kalkulado sa laing function sa Excel nga nagsalag sa sulod VPR.
Busa, kon kini mahitabo nga ang argumento col_index_num (column_number) ubos pa kay sa 1function VPR magreport usab og sayop #BILI!.
Kung ang argumento col_index_num (column_number) mas dako kay sa gidaghanon sa mga column sa gihatag nga array, VPR magreport ug sayop #REF! (#SSYL!).
Sayop si #NAME? sa VLOOKUP
Ang pinakasimple nga kaso mao ang usa ka sayop #NAME? (#NAME?) – makita kung wala ka tuyoa nagsulat ug function name nga naay error.
Ang solusyon klaro - susiha ang imong spelling!
Dili mugana ang VLOOKUP (limitasyon, kweba ug desisyon)
Dugang pa sa medyo komplikado nga syntax, VPR adunay daghang mga limitasyon kaysa sa bisan unsang ubang function sa Excel. Tungod niini nga mga limitasyon, daw yano nga mga pormula nga adunay VPR kasagaran mosangpot sa wala damhang resulta. Sa ubos makit-an nimo ang mga solusyon alang sa daghang sagad nga mga senaryo kung diin VPR sayop.
1. Ang VLOOKUP dili case sensitive
function VPR wala mag-ila tali sa letra ug modawat sa gagmay ug dagkong letra nga pareho ra. Busa, kung adunay daghang mga elemento sa lamesa nga magkalainlain sa kaso, ang VLOOKUP function ibalik ang una nga elemento nga nakit-an, bisan unsa pa ang kaso.
Desisyon: Paggamit og laing Excel function nga makahimo og vertical nga pagpangita (LOOKUP, SUMPRODUCT, INDEX, ug MATCH) inubanan sa LABAWA nga nagpalahi sa kaso. Alang sa dugang nga mga detalye, makakat-on ka gikan sa leksyon - 4 nga mga paagi aron mahimo ang VLOOKUP case-sensitive sa Excel.
2. Gibalik sa VLOOKUP ang unang balor nga nakit-an
Nahibal-an na nimo, VPR ibalik ang kantidad gikan sa gihatag nga kolum nga katumbas sa una nga tugma nga nakit-an. Bisan pa, mahimo nimo kini makuha ang 2nd, 3rd, 4th, o bisan unsa nga pagbalik-balik sa kantidad nga gusto nimo. Kung kinahanglan nimo nga makuha ang tanan nga mga doble nga kantidad, kinahanglan nimo ang kombinasyon sa mga gimbuhaton INDEX (INDEX), KINAHANGLAN (GAMAY) ug PAGTULUN (ROW).
3. Usa ka kolum ang gidugang o gitangtang sa lamesa
Ikasubo, ang mga pormula VPR hunong sa pagtrabaho sa matag higayon nga ang usa ka bag-ong kolum idugang o kuhaon sa lamesa sa pagpangita. Kini mahitabo tungod kay ang syntax VPR nanginahanglan kanimo nga ipiho ang tibuuk nga hanay sa pagpangita ug ang piho nga numero sa kolum alang sa pagkuha sa datos. Natural, ang gihatag nga hanay ug ang numero sa kolum mausab kung imong gitangtang ang usa ka kolum o gisulud ang usa ka bag-o.
Desisyon: Ug usab ang mga gimbuhaton nagdali sa pagtabang INDEX (INDEX) ug MAS EXPOSED (PAGTABANG). Sa pormula INDEX+MATCH Gilain nimo nga gihubit ang mga kolum sa pagpangita ug pagkuha, ug ingon usa ka sangputanan, mahimo nimong tangtangon o isulod ang daghang mga kolum nga gusto nimo nga wala’y kabalaka bahin sa kinahanglan nga i-update ang tanan nga mga pormula sa pagpangita.
4. Ang mga reperensiya sa cell magubot kon magkopya sa usa ka pormula
Kini nga ulohan nagpatin-aw sa esensya sa problema sa hingpit, dili ba?
Desisyon: Kanunay gamita ang hingpit nga mga reference sa cell (uban ang simbolo $) sa mga rekord sa range, pananglitan $A$2:$C$100 or $A:$C. Sa pormula bar, dali nimong mabalhin ang tipo sa link pinaagi sa pag-klik F4.
VLOOKUP – nagtrabaho uban sa mga function IFERROR ug ISERROR
Kung dili nimo gusto nga mahadlok ang mga tiggamit sa mga mensahe sa sayup # N / A, #BILI! or #NAME?, mahimo nimong ipakita ang usa ka walay sulod nga cell o imong kaugalingon nga mensahe. Mahimo nimo kini pinaagi sa pagbutang VPR ngadto sa usa ka function IFERROR (IFERROR) sa Excel 2013, 2010 ug 2007 o gamita ang daghang mga gimbuhaton KUNG+ISERROR (IF+ISERROR) sa naunang mga bersyon.
VLOOKUP: nagtrabaho kauban ang function sa IFERROR
Syntax sa function IFERROR (IFERROR) yano ug nagsulti alang sa iyang kaugalingon:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Kana mao, alang sa una nga argumento imong gisukip ang bili nga susihon alang sa usa ka sayup, ug alang sa ikaduha nga argumento imong itakda kung unsa ang ibalik kung adunay makit-an nga sayup.
Pananglitan, kini nga pormula nagbalik sa usa ka walay sulod nga cell kung ang kantidad nga imong gipangita wala makit-an:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Kung gusto nimo ipakita ang imong kaugalingon nga mensahe imbes nga ang standard nga mensahe sa sayup sa function VPR, ibutang kini sa mga kinutlo, sama niini:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: nagtrabaho kauban ang function sa ISERROR
Sukad sa function IFERROR nagpakita sa Excel 2007, kung nagtrabaho sa naunang mga bersyon kinahanglan nimo nga gamiton ang kombinasyon IF (KUNG) ug EOSHIBKA (ISERROR) sama niini:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Pananglitan, ang pormula KUNG+ISERROR+VLOOKUP, susama sa pormula IFERROR+VLOOKUPgipakita sa ibabaw:
=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;ЛОЖЬ))
Kana lang para karong adlawa. Nanghinaut ko nga kini nga mubo nga panudlo makatabang kanimo sa pag-atubang sa tanan nga posible nga mga sayup. VPR ug himoa ang imong mga pormula nga molihok sa husto.