Clár ábhair
Míníonn an ceacht seo conas déileáil go tapa le cás ina bhfuil feidhm VPR (VLOOKUP) nach bhfuil ag iarraidh oibriú in Excel 2013, 2010, 2007 agus 2003, agus conas earráidí coitianta a aithint agus a shocrú agus teorainneacha a shárú VPR.
I roinnt alt roimhe seo, rinneamar iniúchadh ar ghnéithe éagsúla na feidhme VPR in Excel. Má tá siad léite agat go cúramach, ba cheart duit a bheith i do shaineolaí sa réimse seo anois. Mar sin féin, níl sé gan chúis a chreideann go leor saineolaithe Excel VPR ceann de na gnéithe is casta. Tá go leor teorainneacha agus gnéithe ann a thagann chun bheith ina fhoinse go leor fadhbanna agus earráidí.
San Airteagal seo gheobhaidh tú mínithe simplí ar earráidí #AT (#N / A), # AINM? (#NAME?) agus #VALUE! (#VALUE!) atá le feiceáil agus tú ag obair leis an bhfeidhm VPR, chomh maith le teicnící agus modhanna chun déileáil leo. Cuirfimid tús leis na cásanna is coitianta agus na cúiseanna is soiléire cén fáth. VPR nach n-oibríonn, mar sin tá sé níos fearr chun staidéar a dhéanamh ar na samplaí san ord ina bhfuil siad tugtha san alt.
Earráid #N/A a shocrú san fheidhm VLOOKUP in Excel
I bhfoirmlí le VPR teachtaireacht earráide #AT ciallaíonn (# N/A). nach bhfuil ar fáil (gan sonraí) - le feiceáil nuair nach féidir le Excel an luach atá á lorg agat a fháil. Is féidir leis seo tarlú ar chúiseanna éagsúla.
1. Tá an luach inmhianaithe mílitrithe
Smaoineamh maith an mhír seo a sheiceáil ar dtús! Is minic a tharlaíonn typos nuair a oibríonn tú le méideanna an-mhór sonraí, comhdhéanta de na mílte línte, nó nuair a scríobhtar an luach atá á lorg agat i bhfoirmle.
2. #N/A earráid agus neasmheaitseáil á cuardach le VLOOKUP
Má úsáideann tú foirmle le neaschoinníoll cuardaigh meaitseála, .i. argóint raon_lookup (range_lookup) FÍOR nó níl sé sonraithe, seans go dtuairisceodh d'fhoirmle earráid #N / A in dhá chás:
- Tá an luach le breathnú suas níos lú ná an luach is lú san eagar atá á lorg.
- Níl an colún cuardaigh curtha in ord ardaitheach.
3. #N/A earráid agus meaitseáil chruinn á lorg le VLOOKUP
Má tá meaitseáil chruinn á lorg agat, .i. argóint raon_lookup (range_lookup) FALSE agus níor aimsíodh an luach cruinn, tuairisceoidh an fhoirmle earráid freisin #N / A. Foghlaim tuilleadh faoi conas cuardach a dhéanamh ar mheaitseálacha cruinne agus neasa le feidhm VPR.
4. Níl an colún cuardaigh ar chlé
Mar is eol duit is dócha, ar cheann de na teorainneacha is suntasaí VPR is é nach féidir leis aghaidh a thabhairt ar chlé, mar sin ní mór an colún cuardaigh i do thábla a bheith ar chlé. Go praiticiúil, is minic a dhéanaimid dearmad faoi seo, rud a fhágann go bhfuil foirmle neamh-oibre agus earráid ann. #N / A.
Cinneadh: Murar féidir an struchtúr sonraí a athrú ionas go mbeidh an colún cuardaigh ar chlé, is féidir leat meascán feidhmeanna a úsáid INNÉACS (INDEX) agus NÍOS NÍOS MÓ (MATCH) mar rogha eile níos solúbtha le haghaidh VPR.
5. Déantar uimhreacha a fhormáidiú mar théacs
Foinse eile earráide #N / A i bhfoirmlí le VPR is uimhreacha iad i bhformáid téacs sa phríomhthábla nó sa tábla cuardaigh.
Tarlaíonn sé seo de ghnáth nuair a iompórtálann tú faisnéis ó bhunachair shonraí sheachtracha, nó nuair a chlóscríobhann tú astróf roimh uimhir chun an náid tosaigh a choinneáil.
Léirítear na comharthaí is soiléire d’uimhir i bhformáid téacs san fhíor thíos:
Ina theannta sin, is féidir uimhreacha a stóráil san fhormáid Go ginearálta (Ginearálta). Sa chás seo, níl ach gné amháin faoi deara - tá na huimhreacha ailínithe le imeall clé na cille, agus de réir réamhshocraithe tá siad ailínithe leis an imeall ceart.
Cinneadh: Más luach aonair é, níl le déanamh ach cliceáil ar an deilbhín earráide agus roghnaigh Tiontaigh go Uimhir (Tiontaigh go Uimhir) ón roghchlár comhthéacs.
Más é seo an cás le go leor uimhreacha, roghnaigh iad agus deaschliceáil ar an limistéar roghnaithe. Sa roghchlár comhthéacs atá le feiceáil, roghnaigh Cealla Formáid (Formáid Cealla) > tab Uimhir (Uimhir) > formáid Uimhir (Uimhriúil) agus brúigh OK.
6. Tá spás ag an tús nó ag an deireadh
Is é seo an chúis is lú soiléir leis an earráid. #N / A i bhfeidhm VPR, ós rud é go bhfuil sé deacair ó thaobh amhairc na spásanna breise seo a fheiceáil, go háirithe agus iad ag obair le táblaí móra, nuair a bhíonn an chuid is mó de na sonraí lasmuigh den scáileán.
Réiteach 1: Spásanna breise sa phríomhthábla (áit a bhfuil an fheidhm VLOOKUP)
Má tá spásanna breise le feiceáil sa phríomhthábla, is féidir leat a chinntiú go n-oibríonn na foirmlí i gceart tríd an argóint a chur faoi iamh lookup_value (lookup_value) isteach i bhfeidhm TRIM (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Réiteach 2: Spásanna breise sa tábla cuardaigh (sa cholún cuardaigh)
Má tá spásanna breise sa cholún cuardaigh - bealaí simplí #N / A san fhoirmle le VPR ní féidir a sheachaint. In ionad VPR Is féidir leat foirmle eagar a úsáid le meascán feidhmeanna INNÉACS (INDEX), NÍOS NÍOS MÓ (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))
Ós rud é gur foirmle eagar é seo, ná déan dearmad brúigh Ctrl + Shift + Iontráil in ionad an gnáth Iontráilchun an fhoirmle a chur isteach i gceart.
Earráid #VALUE! i bhfoirmlí le VLOOKUP
I bhformhór na gcásanna, tuairiscíonn Microsoft Excel earráid #VALUE! (#VALUE!) nuair nach ionann an luach a úsáidtear san fhoirmle agus an cineál sonraí. Maidir le VPR, ansin de ghnáth tá dhá chúis leis an earráid #VALUE!.
1. Tá an luach atá á lorg agat níos faide ná 255 carachtar
Bí cúramach: feidhm VPR ní féidir luachanna a bhfuil níos mó ná 255 carachtar iontu a chuardach. Má sháraíonn an luach atá uait an teorainn seo, gheobhaidh tú teachtaireacht earráide. #VALUE!.
Cinneadh: Bain úsáid as a bunch de ghnéithe INDEX+MATCH (INDEX + MATCH). Seo thíos foirmle a dhéanfaidh go breá don tasc seo:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Níl an cosán iomlán chuig an leabhar oibre cuardaigh sonraithe
Má tá tú ag aisghabháil sonraí ó leabhar oibre eile, ní mór duit an chonair iomlán chuig an gcomhad sin a shonrú. Go sonrach, ní mór duit ainm an leabhair oibre (lena n-áirítear an síneadh) a chur isteach idir lúibíní cearnacha [ ], agus ainm na leatháin ina dhiaidh sin, agus pointe exclamation ina dhiaidh sin. Ní mór an tógáil seo ar fad a chur faoi iamh i réanna, ar eagla go bhfuil spásanna in ainm an leabhair nó na leatháin.
Seo struchtúr iomlán na feidhme VPR chun cuardach a dhéanamh i leabhar eile:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Seans go bhféachfadh an fhoirmle fíor mar seo:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Breathnóidh an fhoirmle seo suas an luach cille A2 i gcolún B ar an mbileog Sheet1 sa leabhar oibre Praghsanna Nua agus bain an luach comhfhreagrach as an gcolún D.
Má fágtar aon chuid den chonair tábla ar lár, d’fheidhm VPR ní oibreoidh sé agus tuairisceoidh sé earráid #VALUE! (fiú má tá an leabhar oibre leis an tábla cuardaigh oscailte faoi láthair).
Chun tuilleadh eolais a fháil faoin bhfeidhm VPRag tagairt do chomhad Excel eile, féach an ceacht: Leabhar oibre eile a chuardach le VLOOKUP.
3. Argóint Column_num níos lú ná 1
Tá sé deacair cás a shamhlú ina n-iontrálann duine luach níos lú ná 1chun an colún as a mbainfear an luach a léiriú. Cé go bhfuil sé indéanta má ríomhtar luach an argóint seo le feidhm Excel eile neadaithe laistigh VPR.
Mar sin, má tharlaíonn sé go bhfuil an argóint col_index_num (column_number) níos lú ná 1fheidhm VPR tuairisceoidh sé freisin earráid #VALUE!.
Má tá an argóint col_index_num (uimhir_colún) níos mó ná líon na gcolún san eagar tugtha, VPR tuairisceoidh sé earráid #REF! (#SSYL!).
Earráid #NAME? i VLOOKUP
Is botún é an cás is simplí # AINM? (#NAME?) – le feiceáil má scríobhann tú de thimpiste ainm feidhme le hearráid.
Is léir an réiteach - seiceáil do litriú!
Ní oibríonn VLOOKUP (teorainneacha, caveatanna agus cinntí)
Chomh maith leis an chomhréir sách casta, VPR tá teorainneacha níos mó aige ná aon fheidhm Excel eile. Mar gheall ar na teorainneacha seo, foirmlí simplí cosúil le VPR go minic mar thoradh ar thorthaí gan choinne. Anseo thíos gheobhaidh tú réitigh le haghaidh cásanna coitianta éagsúla nuair VPR mícheart.
1. Níl VLOOKUP cásíogair
Feidhm VPR ní dhéanann sé idirdhealú idir cás agus glacann sé leis na carachtair sna litreacha beaga agus sna cásanna uachtair mar an gcéanna. Dá bhrí sin, má tá roinnt gnéithe sa tábla atá difriúil ach amháin i gcás, beidh feidhm VLOOKUP ar ais an chéad eilimint a fuarthas, beag beann ar chás.
Cinneadh: Úsáid feidhm Excel eile is féidir cuardach ingearach a dhéanamh (LOOKUP, SUMPRODUCT, INDEX, agus MATCH) i gcomhcheangal le EXACTA a dhéanann idirdhealú idir cás. Le haghaidh tuilleadh sonraí, is féidir leat foghlaim ón gceacht - 4 bhealach chun VLOOKUP a dhéanamh cás-íogair in Excel.
2. Tugann VLOOKUP an chéad luach a fuarthas ar ais
Mar atá a fhios agat cheana féin, VPR aischuireann sé an luach ón gcolún tugtha a fhreagraíonn don chéad mheaitseáil a aimsíodh. Mar sin féin, is féidir leat é a bhaint as an 2ú, 3ú, 4ú, nó aon athrá eile den luach atá uait. Más gá duit gach luach dúblach a bhaint as, beidh meascán feidhmeanna de dhíth ort INNÉACS (INDEX), LEAST (BEAG) agus LÍNE (ROW).
3. Cuireadh nó baineadh colún leis an tábla
Ar an drochuair, na foirmlí VPR stop a bheith ag obair gach uair a chuirtear nó a bhaintear colún nua leis an tábla cuardaigh. Tarlaíonn sé seo mar gheall ar an chomhréir VPR éilíonn tú raon iomlán an chuardaigh agus uimhir an cholúin shainiúil le haghaidh eastóscadh sonraí a shonrú. Ar ndóigh, athraíonn an raon tugtha agus uimhir an cholúin araon nuair a scriosann tú colún nó nuair a chuireann tú ceann nua isteach.
Cinneadh: Agus arís tá feidhmeanna i Hurry chun cabhrú leat INNÉACS (INDEX) agus NÍOS NÍOS MÓ (MATCH). San fhoirmle INDEX+MATCH Sainmhíníonn tú colúin chuardaigh agus aisghabhála ar leithligh, agus mar thoradh air sin, is féidir leat an oiread colún agus is mian leat a scriosadh nó a chur isteach gan a bheith buartha faoi na foirmlí cuardaigh gaolmhara go léir a nuashonrú.
4. Tá tagairtí cille garbled nuair a bhíonn foirmle á chóipeáil
Míníonn an ceannteideal seo croílár na faidhbe go hiomlán, ceart?
Cinneadh: Úsáid dearbhthagairtí cille i gcónaí (leis an tsiombail $) ar thaifid an raon, mar shampla $ A $ 2: $ C $ 100 or $ A: $ C.. Sa bharra foirmle, is féidir leat an cineál nasc a athrú go tapa trí chliceáil F4.
VLOOKUP – ag obair leis na feidhmeanna IFERROR agus ISERROR
Más rud é nach bhfuil tú ag iarraidh úsáideoirí a scanradh le teachtaireachtaí earráide #N / A, #VALUE! or # AINM?, is féidir leat ceall folamh nó do theachtaireacht féin a thaispeáint. Is féidir leat é seo a dhéanamh trí chur VPR isteach i bhfeidhm IFERROR (IFERROR) in Excel 2013, 2010 agus 2007 nó bain úsáid as sraith feidhmeanna IF+ISERROR (IF+ISERROR) i leaganacha níos luaithe.
VLOOKUP: ag obair leis an bhfeidhm IFERROR
Comhréir feidhme IFERROR (IFERROR) simplí agus labhraíonn sé ar a shon féin:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Is é sin, don chéad argóint cuireann tú isteach an luach atá le seiceáil le haghaidh earráide, agus don dara argóint sonraíonn tú cad ba cheart a thabhairt ar ais má aimsítear earráid.
Mar shampla, filleann an fhoirmle seo cill fholamh mura bhfaightear an luach atá á lorg agat:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Más mian leat do theachtaireacht féin a thaispeáint in ionad teachtaireacht earráide caighdeánach na feidhme VPR, cuir i Sleachta é, mar sin:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: ag obair leis an bhfeidhm ISERROR
Ós rud é an fheidhm IFERROR le feiceáil in Excel 2007, agus tú ag obair i leaganacha níos luaithe beidh ort an teaglaim a úsáid IF (MÁ) agus EOSHIBKA (ISERROR) mar seo:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Mar shampla, an fhoirmle IF+ISERROR+VLOOKUP, cosúil leis an bhfoirmle IFERROR+VLOOKUPléirithe thuas:
=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;ЛОЖЬ))
Sin go léir don lá atá inniu ann. Tá súil agam go gcabhróidh an teagasc gairid seo leat déileáil leis na botúin go léir is féidir. VPR agus cuir do fhoirmlí ag obair i gceart.