Nilalaman
Ipinapaliwanag ng araling ito kung paano mabilis na haharapin ang isang sitwasyon kung saan ang isang function VPR (VLOOKUP) ay hindi gustong gumana sa Excel 2013, 2010, 2007 at 2003, at kung paano tukuyin at ayusin ang mga karaniwang error at malampasan ang mga limitasyon VPR.
Sa ilang mga nakaraang artikulo, na-explore namin ang iba't ibang facet ng function VPR sa Excel. Kung nabasa mo nang mabuti ang mga ito, dapat ka na ngayong maging eksperto sa larangang ito. Gayunpaman, hindi walang dahilan na naniniwala ang maraming eksperto sa Excel VPR isa sa mga mas kumplikadong tampok. Mayroon itong isang grupo ng mga limitasyon at mga tampok na nagiging pinagmulan ng maraming mga problema at mga error.
Sa artikulong ito makikita mo ang mga simpleng paliwanag ng mga error #AT (#N/A), #NAME? (#NAME?) at #VALUE! (#VALUE!) na lumalabas kapag nagtatrabaho sa function VPR, pati na rin ang mga pamamaraan at pamamaraan ng pagharap sa kanila. Magsisimula tayo sa mga pinakakaraniwang kaso at sa mga pinaka-halatang dahilan kung bakit. VPR ay hindi gumagana, kaya mas mahusay na pag-aralan ang mga halimbawa sa pagkakasunud-sunod kung saan ibinigay ang mga ito sa artikulo.
Pag-aayos ng #N/A error sa VLOOKUP function sa Excel
Sa mga formula na may VPR maling mensahe #AT (#N/A) ibig sabihin Hindi available ang (walang data) – lalabas kapag hindi mahanap ng Excel ang value na hinahanap mo. Ito ay maaaring mangyari sa ilang kadahilanan.
1. Mali ang spelling ng gustong value
Magandang ideya na suriin muna ang item na ito! Kadalasang nangyayari ang mga typo kapag nagtatrabaho ka sa napakalaking dami ng data, na binubuo ng libu-libong linya, o kapag ang halagang hinahanap mo ay nakasulat sa isang formula.
2. #N/A error kapag naghahanap ng tinatayang tugma sa VLOOKUP
Kung gumamit ka ng formula na may tinatayang kondisyon ng paghahanap ng tugma, ibig sabihin, argumento range_lookup (range_lookup) ay TAMA o hindi tinukoy, ang iyong formula ay maaaring mag-ulat ng error # N / A sa dalawang kaso:
- Ang halaga na hahanapin ay mas mababa kaysa sa pinakamaliit na halaga sa hanay na tinitingnan.
- Ang hanay ng paghahanap ay hindi pinagsunod-sunod sa pataas na pagkakasunud-sunod.
3. #N/A error kapag naghahanap ng eksaktong tugma sa VLOOKUP
Kung naghahanap ka ng eksaktong tugma, ibig sabihin, argumento range_lookup (range_lookup) ay FALSE at ang eksaktong halaga ay hindi natagpuan, ang formula ay mag-uulat din ng isang error # N / A. Matuto nang higit pa tungkol sa kung paano maghanap ng eksakto at tinatayang mga tugma sa isang function VPR.
4. Ang hanay ng paghahanap ay hindi pinakakaliwa
Tulad ng malamang na alam mo, isa sa mga pinakamahalagang limitasyon VPR ito ay hindi ito maaaring humarap sa kaliwa, kaya ang hanay ng paghahanap sa iyong talahanayan ay dapat na nasa pinakakaliwa. Sa pagsasagawa, madalas nating nakakalimutan ang tungkol dito, na humahantong sa isang hindi gumaganang formula at isang error. # N / A.
Desisyon: Kung hindi posible na baguhin ang istraktura ng data upang ang hanay ng paghahanap ay nasa kaliwa, maaari kang gumamit ng kumbinasyon ng mga function INDEX (INDEX) at MAS EXPOSED (MATCH) bilang isang mas nababaluktot na alternatibo para sa VPR.
5. Ang mga numero ay naka-format bilang text
Isa pang pinagmulan ng error # N / A sa mga formula na may VPR ay mga numero sa text format sa pangunahing talahanayan o lookup table.
Karaniwan itong nangyayari kapag nag-import ka ng impormasyon mula sa mga panlabas na database, o kapag nag-type ka ng apostrophe bago ang isang numero upang mapanatili ang nangungunang zero.
Ang pinaka-halatang mga palatandaan ng isang numero sa format ng teksto ay ipinapakita sa figure sa ibaba:
Bilang karagdagan, ang mga numero ay maaaring maimbak sa format Pangkalahatan (General). Sa kasong ito, mayroon lamang isang kapansin-pansing tampok - ang mga numero ay nakahanay sa kaliwang gilid ng cell, habang bilang default ay nakahanay ang mga ito sa kanang gilid.
Desisyon: Kung ito ay isang solong halaga, i-click lamang ang icon ng error at piliin I-convert sa Numero (I-convert sa Numero) mula sa menu ng konteksto.
Kung ito ang sitwasyon na may maraming mga numero, piliin ang mga ito at i-right-click sa napiling lugar. Sa lalabas na menu ng konteksto, piliin Mga Format ng Cell (Format Cells) > tab Numero (Numero) > format Numero (Numeric) at pindutin OK.
6. May puwang sa simula o sa dulo
Ito ang hindi gaanong malinaw na dahilan para sa pagkakamali. # N / A sa pag-andar VPR, dahil mahirap makita ang mga karagdagang espasyong ito, lalo na kapag nagtatrabaho sa malalaking talahanayan, kapag ang karamihan sa data ay nasa labas ng screen.
Solusyon 1: Mga karagdagang puwang sa pangunahing talahanayan (kung saan ang VLOOKUP function ay)
Kung lalabas ang mga karagdagang puwang sa pangunahing talahanayan, masisiguro mong gumagana nang tama ang mga formula sa pamamagitan ng paglalagay ng argumento lookup_value (lookup_value) sa isang function TRIM (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Solusyon 2: Mga karagdagang espasyo sa lookup table (sa lookup column)
Kung ang mga karagdagang espasyo ay nasa hanay ng paghahanap – mga simpleng paraan # N / A sa formula na may VPR hindi maiiwasan. sa halip na VPR Maaari kang gumamit ng array formula na may kumbinasyon ng mga function INDEX (INDEX), MAS EXPOSED (MATCH) at 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))
Dahil ito ay isang array formula, huwag kalimutang pindutin Ctrl + Shift + Enter sa halip na karaniwan Magpasokupang maipasok nang tama ang formula.
Error #VALUE! sa mga formula na may VLOOKUP
Sa karamihan ng mga kaso, ang Microsoft Excel ay nag-uulat ng isang error #VALUE! (#VALUE!) kapag ang value na ginamit sa formula ay hindi tumutugma sa uri ng data. Tungkol sa VPR, pagkatapos ay karaniwang may dalawang dahilan para sa error #VALUE!.
1. Ang halaga na iyong hinahanap ay mas mahaba sa 255 character
Mag-ingat: function VPR hindi maaaring maghanap ng mga value na naglalaman ng higit sa 255 character. Kung ang halaga na iyong hinahanap ay lumampas sa limitasyong ito, makakatanggap ka ng mensahe ng error. #VALUE!.
Desisyon: Gumamit ng isang grupo ng mga tampok INDEX+MATCH (INDEX + MATCH). Nasa ibaba ang isang formula na magiging maayos para sa gawaing ito:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Ang buong landas patungo sa workbook ng paghahanap ay hindi tinukoy
Kung kumukuha ka ng data mula sa isa pang workbook, dapat mong tukuyin ang buong path sa file na iyon. Higit na partikular, dapat mong isama ang pangalan ng workbook (kabilang ang extension) sa mga square bracket [ ], na sinusundan ng pangalan ng sheet, na sinusundan ng tandang padamdam. Ang lahat ng konstruksiyon na ito ay dapat na nakapaloob sa mga kudlit, kung sakaling ang pangalan ng aklat o sheet ay naglalaman ng mga puwang.
Narito ang kumpletong istraktura ng function VPR para maghanap sa ibang libro:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Ang totoong formula ay maaaring magmukhang ganito:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Hahanapin ng formula na ito ang halaga ng cell A2 sa isang kolum B sa sheet Sheet1 sa workbook Mga Bagong Presyo at kunin ang katumbas na halaga mula sa column D.
Kung ang anumang bahagi ng path ng talahanayan ay tinanggal, ang iyong function VPR hindi gagana at mag-uulat ng error #VALUE! (kahit na kasalukuyang bukas ang workbook na may lookup table).
Para sa karagdagang impormasyon tungkol sa function VPRsa pagtukoy ng isa pang Excel file, tingnan ang aralin: Paghahanap ng isa pang workbook gamit ang VLOOKUP.
3. Ang Argument Column_num ay mas mababa sa 1
Mahirap isipin ang isang sitwasyon kung saan ang isang tao ay nagpasok ng isang halaga na mas mababa sa 1upang ipahiwatig ang column kung saan kukunin ang halaga. Bagama't posible kung ang halaga ng argumentong ito ay kinakalkula ng isa pang function ng Excel na naka-nest sa loob VPR.
Kaya, kung ito ang mangyayari na ang argumento col_index_num (column_number) mas mababa sa 1tungkulin VPR mag-uulat din ng error #VALUE!.
Kung ang argumento col_index_num (column_number) ay mas malaki kaysa sa bilang ng mga column sa ibinigay na array, VPR mag-uulat ng error #REF! (#SSYL!).
Error sa #NAME? sa VLOOKUP
Ang pinakasimpleng kaso ay isang pagkakamali #NAME? (#NAME?) – lalabas kung hindi mo sinasadyang magsulat ng function name na may error.
Ang solusyon ay malinaw - suriin ang iyong spelling!
Hindi gumagana ang VLOOKUP (mga limitasyon, caveat at desisyon)
Bilang karagdagan sa medyo kumplikadong syntax, VPR ay may higit pang mga limitasyon kaysa sa anumang iba pang function ng Excel. Dahil sa mga limitasyong ito, tila simpleng mga formula na may VPR madalas na humahantong sa hindi inaasahang resulta. Sa ibaba makikita mo ang mga solusyon para sa ilang karaniwang mga sitwasyon kung saan VPR ay mali.
1. Ang VLOOKUP ay hindi case sensitive
tungkulin VPR ay hindi nakikilala sa pagitan ng case at tumatanggap ng lowercase at uppercase na character bilang pareho. Samakatuwid, kung mayroong ilang mga elemento sa talahanayan na naiiba lamang sa kaso, ibabalik ng VLOOKUP function ang unang elemento na natagpuan, anuman ang kaso.
Desisyon: Gumamit ng isa pang Excel function na maaaring magsagawa ng patayong paghahanap (LOOKUP, SUMPRODUCT, INDEX, at MATCH) kasama ng EksaktoA na nagpapakilala sa kaso. Para sa higit pang mga detalye, maaari kang matuto mula sa aralin - 4 na paraan upang gawing case-sensitive ang VLOOKUP sa Excel.
2. Ibinabalik ng VLOOKUP ang unang value na natagpuan
Sa pagkaka-alam mo, VPR ibinabalik ang halaga mula sa ibinigay na column na tumutugma sa unang tugma na natagpuan. Gayunpaman, maaari mo itong i-extract ang ika-2, ika-3, ika-4, o anumang iba pang pag-uulit ng halaga na gusto mo. Kung kailangan mong i-extract ang lahat ng mga duplicate na halaga, kakailanganin mo ng kumbinasyon ng mga function INDEX (INDEX), LEAST (MALIIT) at LINE (ROW).
3. Isang column ang idinagdag o inalis sa talahanayan
Sa kasamaang palad, ang mga formula VPR huminto sa pagtatrabaho sa tuwing may idaragdag o aalis na bagong column sa lookup table. Nangyayari ito dahil ang syntax VPR kailangan mong tukuyin ang buong hanay ng paghahanap at ang partikular na numero ng column para sa pagkuha ng data. Naturally, parehong nagbabago ang ibinigay na hanay at ang numero ng column kapag nagtanggal ka ng column o nagpasok ng bago.
Desisyon: At muli ang mga function ay nagmamadaling tumulong INDEX (INDEX) at MAS EXPOSED (MATCH). Sa formula INDEX+MATCH Hiwalay mong tinutukoy ang mga hanay ng paghahanap at pagkuha, at bilang resulta, maaari mong tanggalin o ipasok ang maraming column hangga't gusto mo nang hindi nababahala tungkol sa kinakailangang i-update ang lahat ng nauugnay na formula sa paghahanap.
4. Magulo ang mga cell reference kapag kumukopya ng formula
Ang heading na ito ay nagpapaliwanag ng esensya ng problema nang lubusan, tama ba?
Desisyon: Palaging gumamit ng ganap na mga sanggunian sa cell (na may simbolo $) sa mga talaan ng hanay, halimbawa $A$2:$C$100 or $A:$C. Sa formula bar, mabilis mong mailipat ang uri ng link sa pamamagitan ng pag-click F4.
VLOOKUP – gumagana sa mga function na IFERROR at ISERROR
Kung ayaw mong takutin ang mga user gamit ang mga mensahe ng error # N / A, #VALUE! or #NAME?, maaari kang magpakita ng walang laman na cell o ng iyong sariling mensahe. Magagawa mo ito sa pamamagitan ng paglalagay VPR sa isang function IFERROR (IFERROR) sa Excel 2013, 2010 at 2007 o gumamit ng isang grupo ng mga function KUNG+ISERROR (IF+ISERROR) sa mga naunang bersyon.
VLOOKUP: gumagana sa IFERROR function
Syntax ng function IFERROR (IFERROR) ay simple at nagsasalita para sa sarili nito:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Iyon ay, para sa unang argumento ay ipinasok mo ang halaga na susuriin para sa isang error, at para sa pangalawang argumento ay tinukoy mo kung ano ang ibabalik kung may nakitang error.
Halimbawa, ang formula na ito ay nagbabalik ng isang walang laman na cell kung ang halaga na iyong hinahanap ay hindi natagpuan:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Kung gusto mong ipakita ang iyong sariling mensahe sa halip na ang karaniwang mensahe ng error ng function VPR, ilagay ito sa mga quote, tulad nito:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: nagtatrabaho sa function ng ISERROR
Dahil ang function IFERROR ay lumitaw sa Excel 2007, kapag nagtatrabaho sa mga naunang bersyon kailangan mong gamitin ang kumbinasyon IF (KUNG) at EOSHIBKA (ISERROR) tulad nito:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Halimbawa, ang formula KUNG+ISERROR+VLOOKUP, katulad ng formula IFERROR+VLOOKUPipinakita sa itaas:
=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;ЛОЖЬ))
Yan lamang para sa araw na ito. Umaasa ako na ang maikling tutorial na ito ay makakatulong sa iyo na harapin ang lahat ng posibleng pagkakamali. VPR at gawin nang tama ang iyong mga formula.