Clár ábhair
Míníonn an teagasc beag seo conas feidhm a dhéanamh VPR (VLOOKUP) cás-íogair, taispeánann sé roinnt foirmlí eile ar féidir le Excel cuardach a dhéanamh orthu ar bhealach cás-íogair, agus léiríonn sé láidreachtaí agus laigí gach feidhme.
Is dóigh liom go bhfuil a fhios ag gach úsáideoir Excel cén fheidhm a dhéanann cuardach ingearach. Sin ceart, is feidhm é VPR. Mar sin féin, is beag duine a fhios sin VPR nach bhfuil cás íogair, .i. tá na carachtair íochtair agus uachtair comhionann leis.
Seo sampla tapa a léiríonn an neamhábaltacht VPR clár a aithint. Cuir i gcill A1 ina bhfuil an luach “bille” agus an chill A2 – “Bille”, foirmle:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
... stopfaidh sé a chuardach ar “bille” ós rud é go dtagann an luach sin ar dtús sa liosta, agus bainfidh sé an luach as an gcill B1.
Níos déanaí san Airteagal seo, beidh mé léiríonn tú conas a dhéanamh VPR cás-íogair. Ina theannta sin, foghlaimfimid cúpla feidhmeanna eile ar féidir leo cuardaigh cás-íogair a dhéanamh in Excel.
Tosóimid leis an gceann is simplí - FÉACH (LOOKUP) agus SUMPRODUCT (SUMPRODUCT), a bhfuil, ar an drochuair, roinnt teorainneacha suntasacha. Ansin, déanfaimid breathnú níos dlúithe ar an bhfoirmle beagán níos casta INDEX+MATCH (INDEX+MATCH), a oibríonn go flawless in aon chás agus le haon tacar sonraí.
Tá feidhm VLOOKUP cásíogair
Mar is eol duit cheana féin, an fheidhm is gnách VPR is cás neamhíogair. Mar sin féin, tá bealach ann chun é a dhéanamh íogair. Chun seo a dhéanamh, ní mór duit colún cúnta a chur leis an tábla, mar a thaispeántar sa sampla seo a leanas.
Cuir i gcolún B tá aitheantóirí táirge (Mír) ann agus ba mhaith leat praghas an táirge agus an trácht comhfhreagrach a bhaint as na colúin C и D. Is í an fhadhb atá ann go bhfuil idir litreacha beaga agus litreacha móra sna haitheantóirí. Mar shampla, luachanna cille B4 (001Tvci3u) agus B5 (001Tvci3U) difriúil ach amháin i gcás an charachtair deiridh, u и U faoi seach.
Mar is féidir leat a shamhlú, an fhoirmle cuardaigh is gnách
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
fillfidh $ 90, ós rud é an luach 001Tvci3u atá sa raon cuardaigh níos luaithe ná 001Tvci3U. Ach ní hé sin a theastaíonn uainn, an ea?
Chun cuardach le feidhm VPR i gcás Excel íogair, beidh ort colún cúntóir a chur leis agus a chealla a líonadh leis an bhfoirmle seo a leanas (áit a bhfuil B mar cholún cuardaigh):
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1)) & КОДСИМВ(ПСТР(B2;5;1)) & КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1)) & КОДСИМВ(ПСТР(B2;8;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;9;1));"")
Briseann an fhoirmle seo an luach inmhianaithe ina charachtair ar leith, cuirtear a chód in ionad gach carachtar (mar shampla, in ionad A ag 65, ina ionad a cód 97) agus ansin nascann sé na cóid seo i sraith uathúil uimhreacha.
Tar éis sin, úsáidimid feidhm shimplí VPR do chuardach cásíogair:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Feidhmiú ceart na feidhme VPR braitheann cás-íogair ar dhá fhachtóir:
- Caithfidh colún an chúntóra a bheith mar an gcolún is faide ar chlé sa raon infheicthe.
- Caithfidh cód carachtair a bheith sa luach atá á lorg agat in ionad an fhíorluacha.
Conas an fheidhm CÓD a úsáid i gceart
Glacann an fhoirmle a cuireadh isteach i gcealla an cholúin chúnta leis go bhfuil an líon céanna carachtair ag gach ceann de do luachanna cuardaigh. Mura bhfuil, ní mór duit fios a bheith agat ar na huimhreacha is lú agus is mó agus cuir leis an oiread gnéithe IFERROR (IFERROR) cé mhéad carachtar atá sa difríocht idir an luach is giorra agus is faide a cuardaíodh.
Mar shampla, más é 3 charachtar an luach cuardaigh is giorra agus 5 charachtar an ceann is faide, úsáid an fhoirmle seo:
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;3;1));"") & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;4;1));"")
Chun feidhm PSTR (MID) Cuireann tú na hargóintí seo a leanas ar fáil:
- 1ú argóint - téacs (téacs) an tagairt téacs nó cille ina bhfuil na carachtair atá le baint (is é B2 inár gcás)
- 2ú argóint - tús_num (start_position) suíomh an chéad cheann de na carachtair sin a bhaintear. tú isteach 1 sa chéad fheidhm PSTR, 2 – sa dara feidhm PSTR etc.
- 3ú argóint - líon_chars (number_of_characters) – Sonraítear líon na gcarachtar atá le baint as an téacs. Ós rud é nach bhfuil de dhíth orainn ach 1 charachtar an t-am ar fad, scríobhaimid i ngach feidhm 1.
TEORAINNEACHA: Feidhm VPR nach é an réiteach is fearr le haghaidh cuardaigh cás-íogair in Excel. Ar dtús, tá gá le colún cúnta a chur leis. Ar an dara dul síos, ní dhéanann an fhoirmle post maith ach amháin má tá na sonraí aonchineálach, nó má tá líon beacht na gcarachtar sna luachanna cuardaigh ar eolas. Murab é seo do chás, is fearr ceann de na réitigh a léirímid thíos a úsáid.
Feidhm LOOKUP do chuardach cásíogair
Feidhm FÉACH (LOOKUP) gaolmhar VPR, mar sin féin ceadaíonn a chomhréir cuardaigh cás-íogair gan colún cúnta a chur leis. Chun seo a dhéanamh, bain úsáid as FÉACH in éineacht leis an bhfeidhm EXACT (EXACT).
Má thógaimid na sonraí ón sampla roimhe seo (gan colún cúnta), rachaidh an fhoirmle seo a leanas i ngleic leis an tasc:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Cuardach foirmle i raon A2: A7 mheaitseáil beacht le luach cille F2 cás-íogair agus cuireann sé ar ais an luach ó cholún B den tsraith chéanna.
cosúil VPRfheidhm FÉACH oibríonn sé go cothrom le luachanna téacs agus uimhriúla, mar a fheiceann tú sa screenshot thíos:
Tábhachtach! D'fhonn an fheidhm FÉACH oibrigh sé i gceart, ba cheart na luachanna sa cholún cuardaigh a shórtáil in ord ardaitheach, ie ón gceann is lú go dtí an ceann is mó.
Lig dom a mhíniú go hachomair conas a oibríonn an fheidhm EXACT san fhoirmle a thaispeántar thuas, toisc gurb é seo an príomhphointe.
Feidhm EXACT déanann sé comparáid idir an dá luach téacs sa 1ú agus sa 2ú argóint agus filleann sé TRUE má tá siad díreach mar a chéile, nó BRÉAGACH mura bhfuil siad. Tá sé tábhachtach dúinn go bhfuil an fheidhm EXACT cás-íogair.
A ligean ar a fheiceáil conas a oibríonn ár bhfoirmle VIEW+EXACT:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- Feidhm EXACT comparáid luach cille F2 le gach eilimint i gcolún A (A2:A7). Filleann sé TRUE má aimsítear meaitseáil chruinn, nó BRÉAGACH.
- Ós rud é go dtugann tú an chéad argóint feidhm FÉACH luach TRUE, ní bhaineann sé an luach comhfhreagrach as an gcolún sonraithe (inár gcás, colún B) ach amháin má aimsítear meaitseáil chruinn, cás íogair.
Tá súil agam go raibh an míniú seo soiléir agus anois tuigeann tú an príomh-smaoineamh. Más amhlaidh, ansin ní bheidh aon deacrachtaí agat le feidhmeanna eile a ndéanfaimid anailís bhreise orthu, mar gheall ar. oibríonn siad go léir ar an bprionsabal céanna.
TEORAINNEACHA: Ní mór na sonraí sa cholún cuardaigh a shórtáil in ord ardaitheach.
SUMPRODUCT - aimsíonn sé luachanna téacs, cás-íogair, ach ní thugann sé ach uimhreacha ar ais
Mar a thuig tú cheana ón teideal, SUMPRODUCT (SUMPRODUCT) feidhm Excel eile a chabhróidh leat cuardach cás-íogair a dhéanamh, ach ní thabharfaidh sé ach luachanna uimhriúla ar ais. Mura n-oireann an rogha seo duit, is féidir leat dul ar aghaidh láithreach chuig an bundle INDEX+MATCH, a thugann réiteach d'aon chás agus d'aon chineál sonraí.
Ar dtús, lig dom comhréir na feidhme seo a mhíniú go hachomair, cabhróidh sé seo leat tuiscint níos fearr a fháil ar an bhfoirmle cás-íogair a leanas.
Feidhm SUMPRODUCT iolraíonn sé gnéithe na n-eagair tugtha agus cuireann sé suim na dtorthaí ar ais. Breathnaíonn an chomhréir mar seo:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Ós rud é go dteastaíonn cuardach cás-íogair, úsáidimid an fheidhm EXACT (EXACT) ón sampla roimhe seo mar cheann de na hiolraitheoirí:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Mar is cuimhin leat, EXACT comparáid luach cille F2 le gach eilimint i gcolún A. Filleann sé TRUE má aimsítear meaitseáil chruinn, nó BRÉAGACH. In oibríochtaí matamaitice, glacann Excel TRUE mar 1, agus BRÉAGACH do 0tuilleadh SUMPRODUCT iolraíonn sé na huimhreacha seo agus suimíonn sé na torthaí.
Ní dhéantar nialais a chomhaireamh mar nuair a iolraítear iad tugann siad i gcónaí 0. A ligean ar ghlacadh le breathnú níos dlúithe ar cad a tharlaíonn nuair a mheaitseáil cruinn i gcolún A aimsithe agus ar ais 1… Feidhm SUMPRODUCT iolraíonn sé an uimhir sa cholún B on 1 agus cuireann sé an toradh ar ais – díreach an uimhir chéanna! Tá sé seo toisc go bhfuil torthaí na dtáirgí eile nialas, agus ní dhéanann siad difear don tsuim mar thoradh air.
Ar an drochuair an fheidhm SUMPRODUCT ní féidir oibriú le luachanna téacs agus dátaí mar ní féidir iad a iolrú. Sa chás seo, gheobhaidh tú teachtaireacht earráide #VALUE! (#VALUE!) mar atá i gcill F4 sa phictiúr thíos:
TEORAINNEACHA: Seoltar ar ais luachanna uimhriúla amháin.
INDEX + MATCH – cuardach cás-íogair d'aon chineál sonraí
Ar deireadh, táimid gar do fhoirmle cuardaigh neamhtheoranta agus cás-íogair a oibríonn le haon tacar sonraí.
Tagann an sampla seo go deireanach, ní toisc go bhfuil an chuid is fearr fágtha le haghaidh milseog, ach toisc go gcuideoidh an t-eolas a fuarthas ó na samplaí roimhe seo leat an fhoirmle cás-íogair a thuiscint níos fearr agus níos tapúla. INDEX+MATCH (INDEX+MATCH).
Mar a cheap tú is dócha, an meascán de fheidhmeanna NÍOS NÍOS MÓ и INNÉACS in Excel mar mhalairt níos solúbtha agus níos cumhachtaí do VPR. Míneoidh an t-alt Ag baint úsáide as INDEX agus MATCH in ionad VLOOKUP go foirfe conas a oibríonn na feidhmeanna seo le chéile.
Ní dhéanfaidh mé ach na príomhphointí a athchoimriú:
- Feidhm NÍOS NÍOS MÓ (MATCH) cuardach do luach i raon ar leith agus cuireann sé a shuíomh coibhneasta ar ais, is é sin, uimhir na ró agus/nó na colúin;
- Next, an fheidhm INNÉACS Tugann (INDEX) luach ar ais ó cholún sonraithe agus/nó ó ró.
Go foirmle INDEX+MATCH in ann cás-íogair a chuardach, ní gá duit ach feidhm amháin a chur leis. Níl sé deacair a buille faoi thuairim a bhfuil ann arís EXACT (EXACT):
=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))
=ИНДЕКС($B$2:$B$7;ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);0))
Sa fhoirmle seo EXACT oibríonn ar an mbealach céanna agus i gcomhar leis an bhfeidhm FÉACH, agus tugann sé an toradh céanna:
Tabhair faoi deara go bhfuil an fhoirmle INDEX+MATCH tá foirmle eagair iniata i braces curly agus ní mór duit é a chomhlánú trí bhrú Ctrl + Shift + Iontráil.
Cén fáth gurb é INDEX+MATCH an réiteach is fearr do chuardach cás-íogair?
Na príomhbhuntáistí a bhaineann leis an bundle INNÉACS и NÍOS NÍOS MÓ:
- Ní gá colún cúnta a chur leis, murab ionann agus VPR.
- Ní éilíonn sé an colún cuardaigh a shórtáil, murab ionann agus FÉACH.
- Oibríonn sé le gach cineál sonraí – uimhreacha, téacs agus dátaí.
Is cosúil go bhfuil an fhoirmle seo foirfe, nach ea? I ndáiríre, nach bhfuil sé. Agus sin an fáth.
Glac leis go bhfuil an chill sa cholún luach fillte a bhaineann leis an luach cuardaigh folamh. Cén toradh a bheidh ar an bhfoirmle? Níl? Feicfimid cad a thugann an fhoirmle ar ais i ndáiríre:
Úps, filleann an fhoirmle nialas! B'fhéidir nach fadhb mhór é seo má tá tú ag obair le luachanna téacs íon. Mar sin féin, má tá uimhreacha sa tábla, lena n-áirítear nialais “cearta”, is fadhb é seo.
Go deimhin, déanann na foirmlí cuardaigh eile go léir (VLOOKUP, LOOKUP, agus SUMPRODUCT) a phléamar níos luaithe iad féin a iompar ar an mbealach céanna. Ach ba mhaith leat an fhoirmle foirfe, ceart?
Chun cás foirmle a dhéanamh íogair INDEX+MATCH foirfe, é a chur i bhfeidhm IF (IF) a thástáilfidh cill a bhfuil luach aischuir aici agus a thabharfaidh toradh folamh ar ais má tá sé folamh:
=IF(INDIRECT("B"&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>"",INDEX($B$2:$B$7, MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),"")
=ЕСЛИ(ДВССЫЛ("B"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0)))<>"";ИНДЕКС($B$2:$B$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0));"")
Sa fhoirmle seo:
- B is colún é le luachanna tuairisceáin
- 1+ is uimhir í a chasann suíomh coibhneasta na cille ar ais ag an bhfeidhm NÍOS NÍOS MÓ, chuig fíorsheoladh na cille. Mar shampla, inár bhfeidhm NÍOS NÍOS MÓ eagar cuardaigh tugtha A2: A7, is é sin, suíomh coibhneasta na cille A2 Beidh 1, toisc gurb é an chéad cheann san eagar é. Ach suíomh iarbhír na cille A2 sa cholún tá 2, mar sin cuirimid 1an difríocht a dhéanamh suas agus an fheidhm a bheith aige INDIRECT (INDIRECT) an luach a aisghabháil ón gcill inmhianaithe.
Taispeánann na pictiúir thíos an fhoirmle cheartaithe cás-íogair INDEX+MATCH I ngníomh. Filleann sé toradh folamh má tá an chill ar ais folamh.
Athscríobh mé an fhoirmle ina colúin B:Da d'oirfeadh an barra foirmle ar an screenshot.
Filleann an fhoirmle 0má tá nialas sa chill ar ais.
Más mian leat an nasc INNÉACS и NÍOS NÍOS MÓ teachtaireacht éigin a thaispeáint nuair a bhíonn an luach fillte folamh, is féidir leat é a scríobh sna comharthaí athfhriotail deiridh (“”) den fhoirmle, mar shampla, mar seo:
=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7, MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),"There is nothing to return, sorry.")
=ЕСЛИ(ДВССЫЛ("D"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0)))<>"";ИНДЕКС($D$2:$D$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0));"There is nothing to return, sorry.")
