Cén fáth go bhfuil INDEX agus MATCH níos fearr ná VLOOKUP in Excel

Mhínigh muid cheana do thosaitheoirí conas bunfheidhmeanna VLOOKUP (Béarla VLOOKUP a úsáid, seasann an giorrúchán do “feidhm cuardaigh ingearach”). Agus taispeánadh roinnt foirmlí níos casta d'úsáideoirí a bhfuil taithí acu.

Agus san Airteagal seo déanfaimid iarracht faisnéis a thabhairt faoi mhodh oibre eile le cuardach ingearach.

B’fhéidir go bhfuil tú ag smaoineamh: “Cén fáth a bhfuil sé seo riachtanach?”. Agus tá sé seo riachtanach chun gach modh cuardaigh féideartha a thaispeáint. Ina theannta sin, is minic a chuireann srianta iomadúla VLOOKUP cosc ​​​​ar an toradh inmhianaithe a fháil. Maidir leis seo, tá INDEX( ) MATCH( ) i bhfad níos feidhmiúla agus níos éagsúla, agus tá níos lú srianta acu freisin.

Basics INDEX MATCH

Ós rud é gurb é cuspóir an treoir seo a thaispeáint cé chomh maith is atá an ghné seo, táimid Breathnaímid ar an eolas bunúsach maidir le prionsabail a oibríochta. Agus taispeánfaimid samplaí, agus déanfaimid machnamh freisin ar an bhfáth, go bhfuil sé níos fearr ná VLOOKUP ().

Feidhm INDEX Comhréir agus Úsáid

Cuidíonn an fheidhm seo le teacht ar an luach atá ag teastáil i measc na réimsí cuardaigh sonraithe bunaithe ar an uimhir colún nó líne. Comhréir:

= INDEX(eagar, uimhir ró, uimhir cholúin):

• eagar – an limistéar ina ndéanfar an cuardach;
• uimhir líne – uimhir na líne atá le cuardach san eagar sonraithe. Mura bhfuil an uimhir ró anaithnid, ní mór uimhir an cholúin a shonrú;
• uimhir an cholúin – uimhir an cholúin atá le fáil san eagar sonraithe. Mura bhfuil an luach anaithnid, beidh líneuimhir ag teastáil.

Sampla de fhoirmle shimplí:

=INDEX(A1:S10,2,3)

Déanfaidh an fheidhm cuardach sa raon ó A1 go C10. Léiríonn na huimhreacha cén ró (2) agus colún (3) as a léiríonn an luach inmhianaithe. Is é an toradh ná cill C2.

Simplí go leor, ceart? Ach nuair a bhíonn tú ag obair le fíordhoiciméid, ní dócha go mbeidh faisnéis agat maidir le huimhreacha nó cealla colún. Sin an fáth atá leis an bhfeidhm MATCH().

MATCH Feidhm Comhréir agus Úsáid

Déanann an fheidhm MATCH() cuardach don luach inmhianaithe agus taispeánann sí a neasuimhir sa limistéar cuardaigh sonraithe.

Breathnaíonn an chomhréir searchpos() mar seo:

=MATCH(luach le cuardach, eagar le cuardach, cineál meaitseála)

• luach cuardaigh – an uimhir nó an téacs atá le fáil;
• eagar cuardaigh – an limistéar ina ndéanfar an cuardach;
• cineál meaitseála – sonraítear cé acu ar cheart an luach beacht nó na luachanna is gaire dó a lorg:
  • 1 (nó gan aon luach sonraithe) – tugann sé ar ais an luach is mó atá comhionann leis an luach a sonraíodh nó atá níos lú ná an luach a sonraíodh;
  • 0 – léiríonn sé meaitseáil bheacht leis an luach cuardaigh. Sa chomhcheangal INDEX() MATCH() beidh meaitseáil beacht de dhíth ort beagnach i gcónaí, mar sin scríobhaimid 0;
  • -1 – Léiríonn sé an luach is lú atá níos mó ná nó cothrom leis an luach atá sonraithe san fhoirmle. Déantar an sórtáil in ord íslitheach.

Mar shampla, sa raon B1:B3 tá Nua-Eabhrac, Páras, Londain cláraithe. Taispeánfaidh an fhoirmle thíos an uimhir 3 toisc go bhfuil Londain sa tríú háit ar an liosta:

= EXPOSE(Londain, B1: B3,0)

Conas oibriú leis an bhfeidhm INDEX MATCH 

Is dócha go bhfuil tú tar éis tosú ag tuiscint cheana féin ar an bprionsabal trína dtógtar comhobair na bhfeidhmeanna seo. I mbeagán focal, ansin Déanann INDEX() cuardach don luach inmhianaithe i measc na sraitheanna agus na gcolún sonraithe. Agus taispeánann MATCH() uimhreacha na luachanna seo:

=INDEX(colún óna gcuirtear an luach ar ais, MATCH(luach le cuardach, colún le cuardach ann, 0))

Fós ag am deacair a thuiscint conas a oibríonn sé? B'fhéidir go míneoidh sampla níos fearr. Abair go bhfuil liosta de phríomhchathracha an domhain agus a ndaonra agat:

D'fhonn méid daonra caipitil áirithe a fháil amach, mar shampla, príomhchathair na Seapáine, bainimid úsáid as an bhfoirmle seo a leanas:

=INDEX(C2:C10, MATCH(An tSeapáin, A2:A10,0))

Míniú:

• Lorgaíonn an fheidhm MATCH() an luach – “An tSeapáin” san eagar A2:A10 agus filleann sí an uimhir 3, toisc gurb í an tSeapáin an tríú luach ar an liosta. 
• Téann an figiúr seo gouimhir líne” san fhoirmle INDEX() agus insíonn sé don fheidhm luach a phriontáil ón ró seo.

Mar sin éiríonn an fhoirmle thuas an fhoirmle chaighdeánach INDEX(C2:C10,3). Déanann an fhoirmle cuardach ó chealla C2 go C10 agus cuireann sé sonraí ar ais ón tríú cill sa raon seo, is é sin, C4, toisc go dtosaíonn an comhaireamh síos ón dara sraith.

Ná bí ag iarraidh ainm na cathrach a fhorordú san fhoirmle? Ansin scríobh i gcill ar bith é, abair F1, agus bain úsáid as mar thagairt san fhoirmle MATCH(). Agus beidh foirmle cuardaigh dinimiciúil agat ar deireadh:

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

Tábhachtach! Líon línte i eagar Caithfidh INDEX() a bheith mar an gcéanna le líon na sraitheanna i eagar measta in MATCH(), nó gheobhaidh tú an toradh mícheart.

Fan nóiméad, cén fáth nach n-úsáideann tú ach an fhoirmle VLOOKUP()?

=VLOOKUP(F1, A2:C10, 3, Bréagach)

 Cad é an pointe atá le cur amú ama ag iarraidh na castachtaí seo go léir a bhaineann le INDEX MATCH a dhéanamh amach?

Sa chás seo, is cuma cén fheidhm atá le húsáid. Níl anseo ach sampla chun tuiscint a fháil ar an gcaoi a n-oibríonn na feidhmeanna INDEX() agus MATCH() le chéile. Léireoidh samplaí eile cad is féidir leis na feidhmeanna seo a dhéanamh i gcásanna ina bhfuil an VLOOKUP gan chumhacht. 

INDEX MATCH nó VLOOKUP

Nuair a bhíonn cinneadh á dhéanamh maidir leis an bhfoirmle cuardaigh le húsáid, aontaíonn go leor go bhfuil INDEX() agus MATCH() i bhfad níos fearr ná VLOOKUP. Mar sin féin, úsáideann go leor daoine VLOOKUP(). Ar an gcéad dul síos, tá VLOOKUP() níos simplí, agus sa dara háit, ní thuigeann úsáideoirí go hiomlán na buntáistí a bhaineann le bheith ag obair le INDEX() agus MATCH(). Gan an t-eolas seo, ní bheidh aon duine sásta a gcuid ama a chaitheamh ag déanamh staidéir ar chóras casta.

Seo iad na príomhbhuntáistí a bhaineann le INDEX() agus MATCH() thar VLOOKUP():

• Cuardaigh ó dheas go clé. Ní féidir le VLOOKUP() cuardach a dhéanamh ó dheas go clé, mar sin caithfidh na luachanna atá uait a bheith sna colúin is clé den tábla i gcónaí. Ach is féidir le INDEX() agus MATCH() é seo a láimhseáil gan fadhb. Beidh an tAirteagal seo a insint duit cad tá sé cosúil go praiticiúil: conas a fháil ar an luach atá ag teastáil ar an taobh clé.

1. Colúin a shuimiú nó a bhaint go sábháilte. Taispeánann an fhoirmle VLOOKUP() torthaí míchearta nuair a bhíonn colúin á mbaint nó á gcur leis toisc go bhfuil uimhir bheacht na gcolún ag teastáil ó VLOOKUP() chun go n-éireoidh leis. Ar ndóigh, nuair a chuirtear nó nuair a bhaintear colúin, athraíonn a n-uimhreacha freisin. 

Agus sna foirmlí INDEX() agus MATCH(), sonraítear raon colún seachas colúin aonair. Mar thoradh air sin, is féidir leat colúin a chur leis agus a bhaint go sábháilte gan an fhoirmle a nuashonrú gach uair.

1. Níl aon teorainneacha ar líon na gcuardach. Agus VLOOKUP() á úsáid agat, ní féidir le líon iomlán na gcritéar cuardaigh a bheith níos mó ná 255 carachtar nó gheobhaidh tú #VALUE! Mar sin má tá líon mór carachtar i do shonraí, is iad INDEX() agus MATCH() an rogha is fearr.
2. Luas próiseála ard. Má tá do táblaí sách beag, ansin ní dócha go dtabharfaidh tú faoi deara aon difríocht. Ach, má tá na céadta nó na mílte sraitheanna sa tábla, agus, dá réir sin, tá na céadta agus na mílte foirmlí ann, déileálfaidh INDEX () agus MATCH () i bhfad níos tapúla ná VLOOKUP (). Is é an fírinne nach ndéanfaidh Excel ach na colúin a shonraítear san fhoirmle a phróiseáil, in ionad an tábla iomlán a phróiseáil. 

Beidh tionchar feidhmíochta VLOOKUP() go háirithe faoi deara má tá líon mór foirmlí ar nós VLOOKUP() agus SUM() ar do bhileog oibre. Tá gá le seiceálacha ar leith ar fheidhmeanna VLOOKUP() chun gach luach a pharsáil in eagar. Mar sin caithfidh Excel méid ollmhór faisnéise a phróiseáil, agus cuireann sé seo moill mhór ar an obair.

Samplaí Foirmle 

Táimid tar éis a dhéanamh amach cheana féin úsáideacht na bhfeidhmeanna seo, ionas gur féidir linn bogadh ar aghaidh go dtí an chuid is suimiúla: cur i bhfeidhm an eolais go praiticiúil.

Foirmle chun cuardach a dhéanamh ó dheis go clé

Mar a luadh cheana, ní féidir le VLOOKUP an cineál cuardaigh seo a dhéanamh. Mar sin, mura bhfuil na luachanna inmhianaithe sa cholún is clé, ní thabharfaidh VLOOKUP() toradh. Tá na feidhmeanna INDEX() agus MATCH() níos ilúsáidí, agus ní bhíonn ról mór ag suíomh na luachanna dóibh a bheith ag obair.

Mar shampla, cuirfimid colún céim ar an taobh clé den tábla agus déanfaimid iarracht a dhéanamh amach cén chéim i dtéarmaí daonra atá i gcaipiteal Ár dTír.

I gcill G1, scríobhaimid an luach atá le fáil, agus ansin úsáidimid an fhoirmle seo a leanas chun cuardach a dhéanamh sa raon C1:C10 agus cuirimid an luach comhfhreagrach ar ais ó A2:A10:

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

Pras. Má tá sé ar intinn agat an fhoirmle seo a úsáid le haghaidh ilcheall, cinntigh go socróidh tú na raonta trí úsáid a bhaint as seoladh iomlán (mar shampla, $A$2: $A$10 agus $C$2: 4C$10).

INDEX NÍOS MÓ NÍOS TUILLEADH  chun cuardach a dhéanamh i gcolúin agus i sraitheanna

Sna samplaí thuas, d'úsáideamar na feidhmeanna seo in ionad VLOOKUP() chun luachanna a thabhairt ar ais ó raon sraitheanna réamhshainithe. Ach cad más gá duit cuardach maitrís nó dhá thaobh a dhéanamh?

Fuaimeann sé casta, ach tá an fhoirmle le haghaidh ríomhanna dá leithéid cosúil leis an bhfoirmle chaighdeánach INDEX() MATCH(), gan ach difríocht amháin: ní mór an fhoirmle MATCH() a úsáid faoi dhó. An chéad uair chun uimhir an ró a fháil, agus an dara huair chun uimhir an cholúin a fháil:

=INDEX(eagar, MATCH(luach cuardaigh ingearach, colún cuardaigh, 0), MATCH (luach cuardaigh cothrománach, ró chuardaigh, 0))

Breathnaímid ar an tábla thíos agus déanaimis iarracht foirmle a dhéanamh INDEX() EXPRESS() EXPRESS() chun déimeagrafaic a thaispeáint i dtír ar leith do bhliain roghnaithe.

Tá an spriocthír i gcill G1 (lorg ingearach) agus tá an spriocbhliain i gcill G2 (lorg cothrománach). Breathnóidh an fhoirmle mar seo:

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

Conas a oibríonn an fhoirmle seo

Mar aon le foirmlí casta ar bith eile, is fusa iad a thuiscint trí iad a bhriseadh síos ina gcothromóidí aonair. Agus ansin is féidir leat a thuiscint cad a dhéanann gach feidhm aonair:

• MATCH(G1,A2:A11,0) – lorgaíonn sé luach (G1) sa raon A2:A11 agus taispeánann sé uimhir an luacha seo, is é 2 inár gcás;
• CUARDACH(G2,B1:D1,0) – lorgaíonn luach (G2) sa raon B1:D1. Sa chás seo, ba é 3 an toradh.

Seoltar uimhreacha na ró agus na gcolún a fuarthas chuig an luach comhfhreagrach san fhoirmle INDEX():

=INDEX(B2:D11,2,3)

Mar thoradh air sin, tá luach againn atá i gcill ag crosbhealach 2 shraith agus 3 cholún sa raon B2:D11. Agus léiríonn an fhoirmle an luach atá ag teastáil, atá i gcill D3.

Cuardaigh de réir coinníollacha iolracha le INDEX agus MATCH

Má tá ár dtreoir ar VLOOKUP() léite agat, is dócha gur bhain tú triail as ilfhoirmlí cuardaigh. Ach tá teorainn shuntasach amháin ag baint leis an modh cuardaigh seo - an gá le colún cúnta a chur leis.

Ach is é an dea-scéal ná Le INDEX() agus MATCH() is féidir leat coinníollacha iolracha a chuardach gan a bheith ort do bhileog oibre a chur in eagar nó a athrú.

Seo an fhoirmle chuardaigh ilchoinníollacha ghinearálta le haghaidh INDEX() MATCH():

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

An nóta: ní mór an fhoirmle seo a úsáid in éineacht leis an aicearra méarchláir CTRL+SHIFT+ENTER.

Cuir i gcás go gcaithfidh tú an luach atá uait a fháil bunaithe ar 2 choinníoll: Ceannaitheora и Táirge.

Éilíonn sé seo an fhoirmle seo a leanas:

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

Sa fhoirmle seo, is é C2:C10 an raon ina ndéanfar an cuardach, F1 - an coinníoll seo, A2: A10 - is é an raon chun an riocht a chur i gcomparáid, F2 – coinníoll 2, V2: V10 – raon chun riocht 2 a chur i gcomparáid.

Ná déan dearmad brúigh an teaglaim ag deireadh na hoibre leis an bhfoirmle CTRL+SHIFT+ENTER - Dúnfaidh Excel an fhoirmle go huathoibríoch le braces curly, mar a thaispeántar sa sampla:

Mura dteastaíonn uait foirmle eagar a úsáid le haghaidh do chuid oibre, cuir INDEX() eile leis an bhfoirmle agus brúigh ENTER, beidh sé cuma sa sampla:

Conas a oibríonn na foirmlí seo

Oibríonn an fhoirmle seo ar an mbealach céanna leis an bhfoirmle chaighdeánach INDEX() MATCH(). Chun coinníollacha iolracha a chuardach, ní chruthaíonn tú ach coinníollacha iolracha Bréagach agus Fíor a léiríonn na coinníollacha aonair cearta agus míchearta. Agus ansin baineann na coinníollacha seo le gach eilimint chomhfhreagrach den eagar. Tiontaíonn an fhoirmle na hargóintí Bréagach agus Fíor go 0 agus 1, faoi seach, agus aschuireann sí eagar ina bhfuil 1 na luachanna meaitseála a fuarthas sa teaghrán. Gheobhaidh MATCH() an chéad luach a mheaitseálann 1 agus cuirfidh sé ar aghaidh chuig an bhfoirmle INDEX() é. Agus tabharfaidh sé, ina dhiaidh sin, an luach atá ag teastáil cheana féin ar ais sa líne sonraithe ón gcolún atá ag teastáil.

Braitheann foirmle gan eagar ar chumas INDEX() chun iad a láimhseáil ina haonar. Meaitseálann an dara INDEX() san fhoirmle bréagach (0), mar sin cuireann sé an t-eagar iomlán leis na luachanna sin ar aghaidh chuig an bhfoirmle MATCH(). 

Is míniú sách fada é seo ar an loighic taobh thiar den fhoirmle seo. Le haghaidh tuilleadh eolais léigh an t-alt “INDEX MATCH le coinníollacha iolracha'.

MEÁN, MAX agus MIN in INDEX agus MATCH

Tá a fheidhmeanna speisialta féin ag Excel chun meán, uasmhéid agus íosmhéid a aimsiú. Ach cad más mian leat sonraí a fháil ón gcill a bhaineann leis na luachanna sin? Sa chás seo Ní mór AVERAGE, MAX agus MIN a úsáid in éineacht le INDEX agus MATCH.

INDEX MATCH agus MAX

Chun an luach is mó i gcolún D a fháil agus é a thaispeáint i gcolún C, úsáid an fhoirmle: 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

MATCH INDEX agus MIN

Chun an luach is lú i gcolún D a fháil agus é a thaispeáint i gcolún C, úsáid an fhoirmle seo a leanas:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

CUARDACH INDEX agus SERPENT

Chun an meánluach i gcolún D a fháil agus an luach seo a thaispeáint in C:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

Ag brath ar an gcaoi a scríobhtar do shonraí, is é 1, 0, nó -1 an tríú argóint a bhaineann le MATCH():

• má dhéantar na colúin a shórtáil in ord ardaitheach, socraigh 1 (ansin ríomhfaidh an fhoirmle an t-uasluach, atá níos lú ná nó cothrom leis an meánluach);
• má tá an saghas íslitheach, ansin -1 (aschuirfidh an fhoirmle an luach íosta atá níos mó ná nó cothrom leis an meán);
• má tá luach atá díreach comhionann leis an meán san eagar cuardaigh, socraigh go 0 é. 

 In ár sampla, tá an daonra curtha in ord íslitheach, mar sin cuirimid -1. Agus is é an toradh ná Tóiceo, toisc gurb é an luach daonra (13,189) an ceann is gaire don mheánluach (000).

Is féidir le VLOOKUP() ríomhanna dá leithéid a dhéanamh freisin, ach mar fhoirmle eagair amháin: VLOOKUP le AVERAGE, MIN agus MAX.

MEABHRÁN INDEX agus ESND/IFERROR

Is dócha gur thug tú faoi deara cheana féin mura bhfuil an fhoirmle in ann an luach atá ag teastáil a fháil, go gcaitheann sé earráid #N / A. Is féidir leat rud éigin níos faisnéiseach a chur in ionad na teachtaireachta earráide caighdeánach. Mar shampla, socraigh an argóint san fhoirmle Sa XNUMXú:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

Leis an bhfoirmle seo, má chuireann tú isteach sonraí nach bhfuil sa tábla, tabharfaidh an fhoirm an teachtaireacht sonraithe duit.

Más mian leat a ghabháil go léir earráidí, ansin ach amháin i gcás Sa XNUMXú Is féidir é a úsáid IFERROR:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), "Tharla earráid!")

Ach cuimhnigh nach smaoineamh maith é earráidí a chumhdach ar an mbealach seo, toisc go dtuairiscíonn earráidí caighdeánacha sáruithe san fhoirmle.

Tá súil againn gur chuidigh tú lenár dtreoir maidir leis an bhfeidhm INDEX MATCH() a úsáid.