In Excel, is minic a bhíonn sé riachtanach sonraí sonracha a aisghabháil go beacht. Cé go bhfuil a láidreachtaí féin ag gach ceann de na feidhmeanna INDEX agus MATCH, trí iad a chomhcheangal, díghlasáiltear tacar uirlisí cumhachtach chun sonraí a chuardach. Le chéile, éascaíonn siad raon cumas cuardaigh, ó chuardach cothrománach bunúsach agus ingearach go feidhmiúlachtaí níos airde mar chuardaigh débhealaigh, cás-íogair agus ilchritéar. Trí chumais fheabhsaithe a thairiscint i gcomparáid le VLOOKUP, ceadaíonn péireáil INDEX agus MATCH do raon níos leithne de roghanna cuardaigh sonraí. Sa rang teagaisc seo, déanaimis iniúchadh ar dhoimhneacht na bhféidearthachtaí is féidir leo a bhaint amach le chéile.

• Feidhm INDEX
• Feidhm MATCH
• Comhcheangail INDEX agus MATCH

• Cuardach déthreo
• Cuardach ar chlé
• Amharc-cás-íogair
• Cluiche is gaire
• Cuardaigh le critéir iolracha
• Cuardaigh thar cholúin iolracha
• Cuardach don chéad luach neamhbhán
• Cuardach don chéad luach uimhriúil
• Cuardaigh le haghaidh cumainn MAX nó MIN
• Leid: Cuir do chuid teachtaireachtaí earráide #N/A féin in oiriúint

Conas INDEX agus MATCH a úsáid in Excel

Sula n-úsáideann muid na feidhmeanna INDEX agus MATCH, déanaimis cinnte go bhfuil a fhios againn conas is féidir le INDEX agus MATCH cabhrú linn luachanna a chuardach ar dtús.

Conas feidhm INDEX a úsáid in Excel

An INNÉACS cuireann feidhm in Excel an luach ar ais in áit ar leith i raon ar leith. Seo a leanas comhréir na feidhme INDEX:

• eagar tagraíonn (riachtanach) don raon inar mian leat an luach a fháil ar ais.
• sraith_num (riachtanach, mura colún_num atá i láthair) tagairt d'uimhir rónna an eagar.
• colún_num (roghnach, ach ag teastáil más rud é sraith_num fágtha ar lár) tagairt d'uimhir cholúin an eagar.

Mar shampla, go mbeadh a fhios scór Jeff, an 6ú dalta ar an liosta, is féidir leat an fheidhm INDEX mar seo a úsáid:

=INDEX(C2:C11,6)

√ Nóta: An raon C2: C11 i gcás na scóir atá liostaithe, agus an uimhir 6 aimsíonn scór scrúdaithe an 6ú mac léinn.

Seo a ligean ar a dhéanamh tástáil beag. Don fhoirmle =INDEX(A1:C1,2), cén luach a thabharfaidh sé ar ais? --- Sea, beidh sé ar ais Dáta breithe, an 2nd luach sa tsraith a thugtar.

Anois ba chóir go mbeadh a fhios againn gur féidir le feidhm INDEX oibriú go foirfe le raonta cothrománacha nó ingearacha. Ach cad má theastaíonn sé uainn chun luach a thabhairt ar ais i raon níos mó le roinnt sraitheanna agus colúin? Bhuel, sa chás seo, ba cheart dúinn uimhir as a chéile agus uimhir cholúin a chur i bhfeidhm. Mar shampla, le fáil amach Scór Jeff laistigh den raon tábla seachas colún amháin, is féidir linn a scór a aimsiú le a uimhir a 6 agus a colún uimhir 3 sa cealla trí A2 go C11 mar seo:

=INDEX(A2:C11,6,3)

Mar sin féin, le haghaidh bunachar sonraí an-mhór le sraitheanna agus colúin iolracha, is cinnte nach bhfuil sé áisiúil dúinn an fhoirmle a chur i bhfeidhm le huimhir bheacht agus uimhir cholún. Agus seo é nuair ba cheart dúinn úsáid na feidhme MATCH a chur le chéile.

Iontach! Ag baint úsáide as Cluaisíní Éifeachtacha in Excel Cosúil le Chrome, Edge, Firefox agus Safari!
Sábháil 50% de do chuid ama, agus laghdaigh na mílte cad a tharlaíonn nuair luch duit gach lá!

Conas feidhm MATCH a úsáid in Excel

Filleann feidhm MATCH in Excel luach uimhriúil, suíomh earra ar leith sa raon ar leith. Seo a leanas comhréir na feidhme MATCH:

• lookup_value (riachtanach) tagairt don luach a mheaitseáil sa cuardaigh_eagar.
• cuardaigh_eagar tagraíonn (riachtanach) don raon cealla inar mian leat MATCH a chuardach.
• cineál_meaitseála (roghnach): 1, 0 or -1.
  • 1 (réamhshocraithe), gheobhaidh MATCH an luach is mó atá níos lú ná nó cothrom leis an lookup_value. Na luachanna sa cuardaigh_eagar a chur in ord ardaitheach.
  • 0, Gheobhaidh MATCH an chéad luach atá comhionann go díreach leis an lookup_value. Na luachanna sa cuardaigh_eagar féidir a bheith in aon ord. (I gcás na gcásanna go bhfuil an cineál meaitseála socraithe go 0, is féidir leat carachtair an chárta fiáin a úsáid.)
  • -1, Gheobhaidh MATCH an luach is lú atá níos mó ná nó cothrom leis an lookup_value. Na luachanna sa cuardaigh_eagar a chur in ord íslitheach.

Mar shampla, go mbeadh a fhios seasamh Vera ar liosta na n-ainmneacha, is féidir leat an fhoirmle MATCH mar seo a úsáid:

=MATCH("Vera",A2:A11,0)

√ Nóta: Léiríonn an toradh “4” go bhfuil an t-ainm “Vera” sa 4ú háit ar an liosta.

Anois go bhfuil eolas againn faoi bhunúsáidí na bhfeidhmeanna INDEX agus MATCH in Excel, déanaimis ár muinchillí a rolladh suas agus ullmhóimid an dá fheidhm le chéile.

Conas INDEX agus MATCH a chomhcheangal in Excel

Féach an sampla thíos le do thoil chun a fháil amach conas is féidir linn feidhmeanna INDEX agus MATCH a chur le chéile:

A fháil Scór Evelyn, agus é ar an eolas go bhfuil scóir na scrúduithe sa 3rd colún, is féidir linn úsáid an fheidhm MATCH chun suíomh an ró a chinneadh go huathoibríoch gan an gá é a chomhaireamh de láimh. Ina dhiaidh sin, is féidir linn an fheidhm INDEX a úsáid chun an luach ag a dtrasnaíonn an ró aitheanta agus an 3ú colún:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),3)

Bhuel, ós rud é go bhféadfadh an fhoirmle breathnú beagán casta, déanaimis dul trí gach cuid de.

An INNÉACS Tá trí argóint sa fhoirmle:

• sraith_num: MATCH("Evelyn",A2:A11,0) soláthraíonn INDEX suíomh ró an luacha "Evelyn" sa raon A2: A11Is é sin, a 5.
• colún_num: 3 sonraítear an 3rd colún don INDEX chun an scór a aimsiú laistigh den eagar.
• eagar: A2: C11 tugann sé treoir do INDEX an luach meaitseála a thabhairt ar ais ag crosbhealach na ró agus na colún sonraithe, laistigh den raon ó A2 go C11. Ar deireadh, faigheann muid an toradh 90.

Sa fhoirmle thuas, d'úsáideamar luach cruachód, "Evelyn". Mar sin féin, i ndáiríre, tá luachanna crua-chóid neamhphraiticiúil, toisc go mbeadh gá le mionathrú orthu gach uair a dhéanaimid iarracht sonraí éagsúla a chuardach, mar shampla scór mac léinn eile. I gcásanna den sórt sin, is féidir linn tagairtí cille a úsáid chun foirmlí dinimiciúla a chruthú. Mar shampla, sa chás seo, déanfaidh mé athraigh "Evelyn" go F2:

=INDEX(A2:C11,MATCH(F2,A2:A11,0),3)

Samplaí de fhoirmle INDEX agus MATCH

Sa chuid seo, labhróidh muid faoi chúinsí éagsúla chun feidhmeanna INDEX agus MATCH a úsáid chun freastal ar riachtanais éagsúla.

INDEX agus MATCH chun amharc dhá bhealach a chur i bhfeidhm

Sa sampla roimhe seo, bhí uimhir an cholúin ar eolas againn agus d’úsáideamar foirmle MATCH chun uimhir an rónna a fháil. Ach cad a tharlóidh mura bhfuilimid cinnte faoi uimhir an cholúin freisin?

I gcásanna den sórt sin, is féidir linn cuardach dhá bhealach a dhéanamh, ar a dtugtar cuardach maitrís freisin, trí dhá fheidhm MATCH a úsáid: ceann amháin chun uimhir an ró a aimsiú agus an ceann eile chun uimhir an cholúin a chinneadh. Mar shampla, go mbeadh a fhios Scór Evelyn, ba cheart dúinn an fhoirmle a úsáid:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),MATCH("Score",A1:C1,0))

INDEX agus MATCH chun amharc ar chlé a chur i bhfeidhm

Anois, déanaimis machnamh ar chás ina gcaithfidh tú rang Evelyn a chinneadh. B'fhéidir gur thug tú faoi deara go bhfuil colún an ranga suite ar thaobh na láimhe clé den cholún ainm, cás a sháraíonn cumais feidhm chuardaigh chumhachtach Excel eile, VLOOKUP.

Go deimhin, tá an cumas chun cuardaigh ar an taobh clé a dhéanamh ar cheann de na gnéithe ina sáraíonn an meascán de INDEX agus MATCH VLOOKUP.

A fháil Rang Evelyn, an fhoirmle seo a leanas a úsáid chun cuardaigh Evelyn in B2:B11 agus faigh an luach comhfhreagrach ó A2:A11.

=INDEX(A2:A11,MATCH("Evelyn",B2:B11,0))

INDEX agus MATCH chun cuardach cás-íogair a chur i bhfeidhm

Go bunúsach tá feidhmeanna MATCH cás-íogair. Ach, nuair a theastaíonn uait d’fhoirmle chun idirdhealú a dhéanamh idir carachtair chás uachtair agus cás íochtair, is féidir leat é a fheabhsú tríd an bhfoirmle a ionchorprú. EXACT feidhm. Trí fheidhm MATCH a chomhcheangal le EXACT i bhfoirmle INDEX, is féidir leat cuardach cás-íogair a dhéanamh go héifeachtach, mar a thaispeántar thíos:

• eagar a thagraíonn don raon óna dteastaíonn uait an luach a thabhairt ar ais.
• lookup_value tagairt don luach a mheaitseáil, ag cur san áireamh cás na gcarachtar, sa cuardaigh_eagar.
• cuardaigh_eagar a thagraíonn do raon na gceall ar mhaith leat MATCH a chur i gcomparáid leis lookup_value.

Mar shampla, go mbeadh a fhios Scór scrúdaithe JIMMY, bain úsáid as an bhfoirmle seo a leanas:

=INDEX(C2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0))

√ Nóta: Is foirmle eagar é seo a éilíonn ort dul isteach leis Ctrl + Shift + Iontráil, ach amháin in Excel 365 agus Excel 2021.

INDEX agus MATCH chun teacht ar an gcluiche is gaire

In Excel, d’fhéadfá teacht ar chásanna ina gcaithfidh tú an meaitseáil is gaire nó is gaire do luach ar leith laistigh de thacar sonraí a aimsiú. I gcásanna dá leithéid, d’fhéadfadh sé a bheith thar a bheith cabhrach úsáid a bhaint as meascán d’fheidhmeanna INDEX agus MATCH, mar aon leis na feidhmeanna ABS agus MIN.

• eagar a thagraíonn don raon óna dteastaíonn uait an luach a thabhairt ar ais.
• cuardaigh_eagar a thagraíonn don raon luachanna a bhfuil tú ag iarraidh an meaitseáil is gaire dó a fháil lookup_value.
• lookup_value tagraíonn sé don luach chun a mheaitseáil is gaire a fháil.

Mar shampla, le fáil amach a bhfuil a scór is gaire do 85, bain úsáid as an bhfoirmle seo a leanas chun cuardach a dhéanamh don scór is gaire do 85 in C2:C11 agus faigh an luach comhfhreagrach ó A2:A11.

=INDEX(A2:A11,MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0))

√ Nóta: Is foirmle eagar é seo a éilíonn ort dul isteach leis Ctrl + Shift + Iontráil, ach amháin in Excel 365 agus Excel 2021.

Iontach! Ag baint úsáide as Cluaisíní Éifeachtacha in Excel Cosúil le Chrome, Edge, Firefox agus Safari!
Sábháil 50% de do chuid ama, agus laghdaigh na mílte cad a tharlaíonn nuair luch duit gach lá!

INDEX agus MATCH chun lookup le ilchritéir a chur i bhfeidhm

Chun luach a fháil a chomhlíonann coinníollacha iolracha, a éilíonn ort cuardach a dhéanamh thar dhá cholún nó níos mó, bain úsáid as an bhfoirmle seo a leanas. Ligeann an fhoirmle duit cuardach ilchritéar a dhéanamh trí choinníollacha éagsúla a shonrú thar cholúin éagsúla, ag cabhrú leat teacht ar an luach inmhianaithe a chomhlíonann na critéir shonraithe go léir.

√ Nóta: Is foirmle eagar é seo a éilíonn ort dul isteach leis Ctrl + Shift + Iontráil. Taispeánfar péire lúibíní cuartha sa bharra foirmle ansin.

• eagar a thagraíonn don raon óna dteastaíonn uait an luach a thabhairt ar ais.
• (lookup_value=lookup_array) is ionann coinníoll amháin. Seiceálacha an coinníoll seo má tá ar leith lookup_value a mheaitseálann na luachanna sa cuardaigh_eagar.

Mar shampla, chun an scór Coco Aicme A, arb é 7/2/2008 a dháta breithe, is féidir leat an fhoirmle seo a leanas a úsáid:

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0))

INDEX agus MATCH chun amharc a chur i bhfeidhm ar fud ilcholún

Samhlaigh cás ina bhfuil tú ag déileáil le colúin sonraí iolracha. Feidhmíonn an chéad cholún mar eochair chun na sonraí sna colúin eile a rangú. Chun catagóir nó aicmiú iontráil ar leith a chinneadh, beidh ort cuardach a dhéanamh ar fud na gcolún sonraí agus é a nascadh leis an eochair ábhartha sa cholún tagartha.

Mar shampla, sa tábla thíos, conas is féidir linn an mac léinn Shawn a mheaitseáil lena rang comhfhreagrach ag baint úsáide as INDEX agus MATCH? Bhuel, is féidir leat é a bhaint amach le foirmle, ach tá an fhoirmle sách fairsing agus is féidir a bheith dúshlánach a thuiscint, gan trácht ar mheabhrú agus clóscríobh.

=IFERROR(INDEX($A$2:$A$4,MATCH(IF(SUM(MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0)))>0,1,-1),MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0))^0,0)), "")

Sin an áit Kutools le haghaidh Excel's Innéacs agus Meaitseáil ar Cholúin Il gné thagann i handy. Déanann sé an próiseas a shimpliú, rud a fhágann go bhfuil sé tapa agus éasca iontrálacha sonracha a mheaitseáil lena gcatagóirí comhfhreagracha. Chun an uirlis chumhachtach seo a dhíghlasáil agus Shawn a mheaitseáil go simplí lena rang, go simplí íoslódáil agus suiteáil an breiseán Kutools for Excel, agus ansin déan mar a leanas:

1. Roghnaigh an chill sprice inar mian leat an rang meaitseála a thaispeáint.
2. Ar an Kutools tab, cliceáil Cúntóir Foirmle > Cuardach & Tagairt > Innéacs agus Meaitseáil ar Cholúin Il.
3. 
3. Sa bhosca dialóige aníos, déan mar a leanas:
   1. Cliceáil ar an 1ú cnaipe in aice leis Cuardaigh_col chun an colún a roghnú ina bhfuil an fhaisnéis thábhachtach is mian leat a thabhairt ar ais, ie, ainmneacha na ranganna. (Ní féidir leat ach colún amháin a roghnú anseo.)
   2. Cliceáil ar an 2ú cnaipe in aice leis Tábla_rng chun na cealla a roghnú chun na luachanna sa roghnaithe a mheaitseáil Cuardaigh_col, ie, ainmneacha na mac léinn.
   3. Cliceáil ar an 3ú cnaipe in aice leis Cuardach_luach chun an chill a roghnú ina bhfuil ainm an dalta is mian leat a mheaitseáil lena rang, sa chás seo, Shawn.
   4. cliceáil OK.
   5. 

Toradh

Tá an fhoirmle ginte ag Kutools go huathoibríoch, agus feicfidh tú ainm ranga Shawn ar taispeáint sa chill scríbe láithreach.

INDEX agus MATCH chun cuardach a dhéanamh don chéad luach neamhbhán

Chun an chéad luach neamhbhán a fháil, gan aird a thabhairt ar earráidí, ó cholún nó as a chéile, is féidir leat foirmle a úsáid bunaithe ar fheidhmeanna INDEX agus MATCH. Mar sin féin, mura bhfuil tú ag iarraidh neamhaird a dhéanamh de na hearráidí ó do raon, cuir an fheidhm ISBLANK leis.

• Faigh an chéad luach neamhbhán i gcolún nó i ndiaidh ag déanamh neamhairde ar earráidí:

• =INDEX(B4:B15,MATCH(TRUE,INDEX((B4:B15<>0),0),0))

• Faigh an chéad luach neamhbhán i gcolún nó as a chéile lena n-áirítear earráidí:

• =INDEX(B4:B15,MATCH(FALSE,ISBLANK(B4:B15),0))

  

INDEX agus MATCH chun cuardach a dhéanamh don chéad luach uimhriúil

Chun an chéad luach uimhriúil a fháil ó cholún nó as as a chéile, úsáid an fhoirmle atá bunaithe ar na feidhmeanna INDEX, MATCH agus ISNUMBER.

=INDEX(B4:B15,MATCH(TRUE,ISNUMBER(B4:B15),0))

INDEX agus MATCH chun cumainn MAX nó MIN a chuardach

Más gá duit luach a bhaineann leis an uasluach nó leis an íosluach laistigh de raon a aisghabháil, is féidir leat an fheidhm MAX nó MIN a úsáid in éineacht leis na feidhmeanna INDEX agus MATCH.

• INDEX agus MATCH chun luach a bhaineann leis an uasluach a fháil:
• =INDEX(array, MATCH(MAX(lookup_array), lookup_array, 0))
• INDEX agus MATCH chun luach a bhaineann leis an íosluach a fháil:
• =INDEX(array, MATCH(MIN(lookup_array), lookup_array, 0))
• Tá dhá argóint sna foirmlí thuas:
• • eagar a thagraíonn don raon óna dteastaíonn uait an fhaisnéis ghaolmhar a thabhairt ar ais.
  • cuardaigh_eagar is ionann é agus an tacar luachanna atá le scrúdú nó le cuardach le haghaidh sainchritéir, .i. uasluachanna nó íosluachanna.

Mar shampla, más mian leat a chinneadh cé aige a bhfuil an scór is airde, bain úsáid as an bhfoirmle seo a leanas:

=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11,0))

Leid: Cuir do chuid teachtaireachtaí earráide #N/A féin in oiriúint

Agus tú ag obair le feidhmeanna INDEX agus MATCH Excel, seans go dtiocfaidh tú ar an earráid #N/A nuair nach bhfuil aon toradh meaitseála agat. Mar shampla, sa tábla thíos, nuair a dhéantar iarracht scór dalta darb ainm Samantha a fháil, tagann earráid #N/A toisc nach bhfuil sí i láthair sa tacar sonraí.

Chun do scarbhileoga a dhéanamh níos so-úsáidte, is féidir leat an teachtaireacht earráide seo a shaincheapadh trí d’fhoirmle INDEX MATCH a fhilleadh san fheidhm IFNA:

=IFNA(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

Thuas tá an t-ábhar ábhartha go léir a bhaineann leis na feidhmeanna INDEX agus MATCH in Excel. Tá súil agam go mbeidh an rang teagaisc cabhrach. Má tá tú ag iarraidh tuilleadh leideanna agus cleasanna Excel a iniúchadh, cliceáil anseo chun rochtain a fháil ar ár mbailiúchán fairsing de bhreis is mílte ranganna teagaisc.