Carson a tha INDEX agus MATCH nas fheàrr na VLOOKUP ann an Excel

Tha sinn air mìneachadh roimhe seo do luchd-tòiseachaidh mar a chleachdas iad gnìomhan bunaiteach VLOOKUP (Beurla VLOOKUP, tha an giorrachadh a’ seasamh airson “gnìomh lorg dìreach”). Agus chaidh grunn fhoirmlean nas iom-fhillte a shealltainn do luchd-cleachdaidh eòlach.

Agus san artaigil seo feuchaidh sinn ri fiosrachadh a thoirt seachad mu dhòigh eile air a bhith ag obair le sgrùdadh dìreach.

Is dòcha gu bheil thu a’ faighneachd: “Carson a tha seo riatanach?”. Agus tha seo riatanach gus sealltainn a h-uile dòigh sgrùdaidh comasach. A bharrachd air an sin, bidh grunn chuingealachaidhean VLOOKUP gu tric a’ cur casg air an toradh a tha thu ag iarraidh fhaighinn. A thaobh seo, tha INDEX ( ) MATCH ( ) tòrr nas gnìomhaiche agus nas eadar-mheasgte, agus tha nas lugha de chuingealachaidhean aca cuideachd.

Basics INDEX MATCH

Leis gur e adhbhar an iùil seo sealltainn cho math sa tha am feart seo, tha sinn Bheir sinn sùil air an fhiosrachadh bunaiteach a thaobh prionnsapalan a h-obrachaidh. Agus seallaidh sinn eisimpleirean, agus beachdaichidh sinn cuideachd air carson, tha e nas fheàrr na VLOOKUP ().

Co-chòrdadh gnìomh INDEX agus Cleachdadh

Bidh an gnìomh seo a’ cuideachadh le bhith a’ lorg an luach a tha thu ag iarraidh am measg nan raointean sgrùdaidh ainmichte stèidhichte air a’ cholbh no àireamh loidhne. Syntax:

= INDEX (eagrachadh, àireamh sreath, àireamh colbh):

• raon - an raon anns an tèid an rannsachadh a dhèanamh;
• àireamh loidhne - àireamh na loidhne a thèid a sgrùdadh anns an raon ainmichte. Mura h-eil fios air àireamh na loidhne, feumar àireamh a’ cholbh a shònrachadh;
• àireamh colbh - àireamh a’ cholbh a lorgar san raon ainmichte. Mura h-eil an luach neo-aithnichte, tha feum air àireamh loidhne.

Eisimpleir de fhoirmle sìmplidh:

=INDEX(A1:S10,2,3)

Nì an gnìomh sgrùdadh anns an raon bho A1 gu C10. Tha na h-àireamhan a’ sealltainn dè an sreath (2) agus an colbh (3) a sheallas an luach a tha thu ag iarraidh. Is e an toradh cealla C2.

Gu math sìmplidh, ceart? Ach nuair a bhios tu ag obair le fìor sgrìobhainnean, chan eil e coltach gum bi fiosrachadh agad a thaobh àireamhan colbh no ceallan. Is ann airson sin a tha an gnìomh MATCH().

Co-chòrdadh gnìomh MATCH agus Cleachdadh

Bidh an gnìomh MATCH () a’ lorg an luach a tha thu ag iarraidh agus a’ sealltainn an àireamh tuairmseach aige san raon sgrùdaidh ainmichte.

Tha an co-chàradh searchpos() a’ coimhead mar seo:

=MATCH(luach ri lorg, sreath ri lorg, seòrsa maids)

• luach rannsachaidh - an àireamh no an teacsa a gheibhear;
• raon rannsachaidh - an raon far an tèid an rannsachadh a dhèanamh;
• seòrsa maids - a 'sònrachadh am bu chòir dhut coimhead airson an dearbh luach no na luachan as fhaisge air:
  • 1 (no gun luach air a shònrachadh) - a 'tilleadh an luach as motha a tha co-ionann no nas lugha na an luach a chaidh a shònrachadh;
  • 0 - a’ sealltainn maids ceart leis an luach a chaidh a sgrùdadh. Anns a’ chothlamadh INDEX() MATCH() cha mhòr an-còmhnaidh feumaidh tu maids ceart, mar sin bidh sinn a’ sgrìobhadh 0;
  • -1 - A’ sealltainn an luach as lugha a tha nas motha na no co-ionann ris an luach a tha air a shònrachadh san fhoirmle. Tha an rèiteachadh air a dhèanamh ann an òrdugh teàrnaidh.

Mar eisimpleir, anns an raon B1: B3 tha New York, Paris, Lunnainn clàraichte. Seallaidh am foirmle gu h-ìosal an àireamh 3 leis gu bheil Lunnainn san treas àite air an liosta:

= EXPOSE (Lunnainn, B1: B3,0)

Mar a dh’ obraicheas tu leis a’ ghnìomh INDEX MATCH 

Is dòcha gu bheil thu mar-thà air tòiseachadh a 'tuigsinn a' phrionnsapail leis a bheil co-obrachadh nan gnìomhan sin air a thogail. Ann an ùine ghoirid, an uairsin Bidh INDEX () a’ lorg an luach a tha thu ag iarraidh am measg nan sreathan agus na colbhan ainmichte. Agus tha MATCH() a’ sealltainn àireamhan nan luachan sin:

= INDEX (colbh às an tèid an luach a thilleadh, MATCH (luach ri rannsachadh, colbh airson rannsachadh a-steach, 0))

Fhathast ùine dhoirbh a bhith a’ tuigsinn mar a tha e ag obair? Is dòcha gu bheil eisimpleir a 'mìneachadh nas fheàrr. Seach gu bheil liosta agad de phrìomh-oifisean an t-saoghail agus an àireamh-sluaigh:

Gus faighinn a-mach meud an t-sluaigh ann an calpa sònraichte, mar eisimpleir, prìomh-bhaile Iapan, bidh sinn a’ cleachdadh na foirmle a leanas:

=INDEX(C2:C10, MATCH(Iapan, A2:A10,0))

Mìneachadh:

• Bidh an gnìomh MATCH () a’ coimhead airson an luach - “Iapan” anns an raon A2: A10 agus a’ tilleadh an àireamh 3, oir is e Iapan an treas luach air an liosta. 
• Tha am figear seo a’ dol guàireamh loidhne” anns an fhoirmle INDEX () agus ag innse don ghnìomh luach a chlò-bhualadh bhon t-sreath seo.

Mar sin thig am foirmle gu h-àrd gu bhith na fhoirmle àbhaisteach Clàr-innse(C2:C10,3). Bidh am foirmle a’ lorg bho cheallan C2 gu C10 agus a’ tilleadh dàta bhon treas cealla san raon seo, is e sin, C4, leis gu bheil an cunntadh sìos a’ tòiseachadh bhon dàrna sreath.

Nach eil thu airson ainm a’ bhaile òrdachadh san fhoirmle? An uairsin sgrìobh e ann an cealla sam bith, can F1, agus cleachd e mar iomradh anns an fhoirmle MATCH(). Agus bidh foirmle sgrùdaidh fiùghantach agad:

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

Cudromach! An àireamh de loidhnichean a-staigh sreath Feumaidh INDEX () a bhith co-ionann ris an àireamh de shreathan a-staigh air a mheas mar raon ann am MATCH(), air neo gheibh thu an toradh ceàrr.

Fuirich mionaid, carson nach cleachd thu ach am foirmle VLOOKUP()?

= VLOOKUP(F1, A2: C10, 3, meallta)

 Dè an adhbhar a bhith a’ caitheamh ùine a’ feuchainn ris na iom-fhillteachd sin de INDEX MATCH obrachadh a-mach?

Anns a 'chùis seo, chan eil e gu diofar dè an gnìomh a chleachdadh. Is e seo dìreach eisimpleir airson tuigsinn mar a tha gnìomhan INDEX () agus MATCH () ag obair còmhla. Seallaidh eisimpleirean eile na tha na gnìomhan sin comasach ann an suidheachaidhean far a bheil an VLOOKUP gun chumhachd. 

INDEX MATCH no VLOOKUP

Nuair a bhios iad a’ co-dhùnadh dè am foirmle sgrùdaidh a chleachdas iad, tha mòran ag aontachadh gu bheil INDEX() agus MATCH() gu math nas fheàrr na VLOOKUP. Ach, tha mòran dhaoine fhathast a’ cleachdadh VLOOKUP(). An toiseach, tha VLOOKUP () nas sìmplidh, agus san dàrna h-àite, chan eil luchd-cleachdaidh gu tur a’ tuigsinn na buannachdan uile bho bhith ag obair le INDEX () agus MATCH (). Às aonais an eòlais seo, chan aontaich duine an ùine a chaitheamh a 'sgrùdadh siostam iom-fhillte.

Seo na prìomh bhuannachdan a tha aig INDEX() agus MATCH() thairis air VLOOKUP():

• Lorg bho dheas gu clì. Chan urrainn do VLOOKUP() sgrùdadh a dhèanamh bho dheas gu clì, agus mar sin feumaidh na luachan a tha thu a’ sireadh a bhith an-còmhnaidh anns na colbhan as fhaide air chlì den chlàr. Ach faodaidh INDEX () agus MATCH () seo a làimhseachadh gun duilgheadas. Innsidh an artaigil seo dhut cò ris a tha e coltach ann an cleachdadh: mar a lorgas tu an luach a tha thu ag iarraidh air an taobh chlì.

1. Cur-ris no toirt air falbh colbhan gu sàbhailte. Tha am foirmle VLOOKUP() a’ sealltainn toraidhean ceàrr nuair a bheir thu air falbh no nuair a chuireas tu colbhan ris oir feumaidh VLOOKUP() an dearbh àireamh colbh a bhith soirbheachail. Gu nàdarra, nuair a thèid colbhan a chur ris no a thoirt air falbh, bidh na h-àireamhan aca ag atharrachadh cuideachd. 

Agus anns na foirmlean INDEX () agus MATCH (), tha raon de cholbhan air a shònrachadh, chan e colbhan fa leth. Mar thoradh air an sin, faodaidh tu colbhan a chuir ris agus a thoirt air falbh gu sàbhailte gun a bhith agad ri am foirmle ùrachadh gach turas.

1. Chan eil crìochan air meudan sgrùdaidh. Nuair a bhios tu a’ cleachdadh VLOOKUP(), chan fhaod an àireamh iomlan de shlatan-tomhais sgrùdaidh a bhith nas àirde na 255 caractar no gheibh thu #VALUE! Mar sin ma tha àireamh mhòr de charactaran anns an dàta agad, is e INDEX () agus MATCH () an roghainn as fheàrr.
2. Àrd-astar giollachd. Ma tha na bùird agad an ìre mhath beag, chan eil e coltach gum mothaich thu eadar-dhealachadh sam bith. Ach, ma tha na ceudan no na mìltean de shreathan anns a 'chlàr, agus, a rèir sin, tha na ceudan agus na mìltean de fhoirmlean ann, bidh INDEX () agus MATCH () a' dèiligeadh fada nas luaithe na VLOOKUP (). Is e an fhìrinn nach bi Excel a’ làimhseachadh ach na colbhan a tha air an sònrachadh san fhoirmle, an àite a bhith a’ giullachd a’ bhùird gu lèir. 

Bidh buaidh dèanadais VLOOKUP () gu sònraichte follaiseach ma tha àireamh mhòr de fhoirmlean mar VLOOKUP () agus SUM () air an duilleag-obrach agad. Feumar sgrùdaidhean fa leth de na gnìomhan VLOOKUP () gus gach luach a pharsadh ann an sreath. Mar sin feumaidh Excel tòrr fiosrachaidh a phròiseasadh, agus tha seo a’ slaodadh sìos an obair gu mòr.

Eisimpleirean Foirmle 

Tha sinn mu thràth air faighinn a-mach dè cho feumail ‘s a tha na gnìomhan sin, gus an urrainn dhuinn gluasad air adhart chun phàirt as inntinniche: cleachdadh eòlais ann an cleachdadh.

Formula airson rannsachadh bho dheas gu clì

Mar a chaidh ainmeachadh roimhe, chan urrainn do VLOOKUP an seòrsa sgrùdaidh seo a dhèanamh. Mar sin, mura h-eil na luachan a thathar ag iarraidh anns a’ cholbh as fhaide air chlì, cha toir VLOOKUP() toradh a-mach. Tha na gnìomhan INDEX () agus MATCH () nas ioma-chruthach, agus chan eil àite mòr aig suidheachadh nan luachan airson a bhith ag obair.

Mar eisimpleir, cuiridh sinn colbh inbhe ri taobh clì ar clàr agus feuchaidh sinn ri faighinn a-mach dè an ìre a thaobh àireamh-sluaigh a tha prìomh-bhaile Ar Dùthaich a’ fuireach.

Ann an ceal G1, bidh sinn a’ sgrìobhadh an luach a tha ri lorg, agus an uairsin a’ cleachdadh na foirmle a leanas gus sgrùdadh a dhèanamh anns an raon C1: C10 agus an luach co-fhreagarrach a thilleadh bho A2: A10:

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

Pras. Ma tha thu an dùil am foirmle seo a chleachdadh airson ioma-cheallan, dèan cinnteach gun socraich thu na raointean le bhith a’ cleachdadh seòladh iomlan (mar eisimpleir, $A$2: $A$10 agus $C$2:4C$10).

INDEX MORE EXPOSED MORE EXPOSED  airson rannsachadh ann an colbhan is sreathan

Anns na h-eisimpleirean gu h-àrd, tha sinn air na gnìomhan sin a chleachdadh an àite VLOOKUP() gus luachan a thilleadh bho raon ro-mhìnichte de shreathan. Ach dè ma dh’ fheumas tu sgrùdadh matrix no dà-thaobh a dhèanamh?

Tha e toinnte, ach tha am foirmle airson àireamhachadh mar seo coltach ris an fhoirmle àbhaisteach INDEX () MATCH (), le dìreach aon eadar-dhealachadh: feumar am foirmle MATCH() a chleachdadh dà uair. A’ chiad uair a gheibh thu àireamh na loidhne, agus an dàrna turas a gheibh thu àireamh a’ cholbh:

= INDEX (eagrachadh, MATCH (luach sgrùdaidh dìreach, colbh sgrùdaidh, 0), MATCH (luach sgrùdaidh còmhnard, sreath sgrùdaidh, 0))

Bheir sinn sùil air a’ chlàr gu h-ìosal agus feuchaidh sinn ri foirmle a dhèanamh INDEX() EXPRESS() EXPRESS() gus sluagh-sluaigh a thaisbeanadh ann an dùthaich shònraichte airson bliadhna taghte.

Tha an dùthaich air a bheilear ag amas ann an cealla G1 (lorg dìreach) agus tha a’ bhliadhna targaid ann an cealla G2 (lorg còmhnard). Bidh am foirmle a’ coimhead mar seo:

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

Mar a tha am foirmle seo ag obair

Coltach ri foirmlean iom-fhillte sam bith eile, tha iad nas fhasa an tuigsinn le bhith gan briseadh sìos gu co-aontaran fa leth. Agus an uairsin tuigidh tu dè a bhios gach gnìomh fa leth a’ dèanamh:

• MATCH(G1,A2:A11,0) - a’ coimhead airson luach (G1) anns an raon A2: A11 agus a’ sealltainn àireamh an luach seo, sa chùis againn is e 2 a th’ ann;
• SEARCH(G2,B1:D1,0) - a’ coimhead airson luach (G2) anns an raon B1: D1. Anns a 'chùis seo, b' e an toradh 3.

Thèid na h-àireamhan sreath agus colbh a chaidh a lorg a chuir chun luach co-fhreagarrach san fhoirmle INDEX ():

=INDEX(B2:D11,2,3)

Mar thoradh air an sin, tha luach againn a tha ann an cealla aig crois-rathaid 2 shreath agus 3 colbhan anns an raon B2: D11. Agus tha am foirmle a’ sealltainn an luach a tha thu ag iarraidh, a tha ann an cealla D3.

Rannsaich a rèir grunn shuidheachaidhean le INDEX agus MATCH

Ma leugh thu an stiùireadh againn air VLOOKUP(), is dòcha gu bheil thu air iomadh foirmle sgrùdaidh fheuchainn. Ach tha aon chuingealachadh mòr aig an dòigh sgrùdaidh seo - an fheum air colbh taiceil a chuir ris.

Ach is e an deagh naidheachd sin Le INDEX() agus MATCH() faodaidh tu iomadh suidheachadh a lorg gun a bhith agad ri do dhuilleag-obrach a dheasachadh no atharrachadh.

Seo am foirmle sgrùdaidh ioma-chumhachd coitcheann airson INDEX() MATCH():

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

An nota: feumar am foirmle seo a chleachdadh còmhla ri ath-ghoirid a’ mheur-chlàir CTRL+SHIFT+ENTER.

Seach gum feum thu an luach a tha thu a’ sireadh a lorg stèidhichte air 2 chumha: ceannaiche и Bathar.

Feumaidh seo am foirmle a leanas:

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

Anns an fhoirmle seo, is e C2:C10 an raon anns an tèid an rannsachadh a dhèanamh, F1 — an staid so, A2: A10 - tha an raon coimeas a dhèanamh eadar an suidheachadh, F2 - suidheachadh 2, V2: V10 - raon airson coimeas a dhèanamh eadar suidheachadh 2.

Na dì-chuimhnich am measgachadh a bhrùthadh aig deireadh na h-obrach leis an fhoirmle CTRL+SHIFT+ENTER - Dùinidh Excel am foirmle gu fèin-ghluasadach le braces lùbach, mar a chithear san eisimpleir:

Mura h-eil thu airson foirmle rèite a chleachdadh airson do chuid obrach, cuir INDEX () eile ris an fhoirmle agus brùth ENTER, seallaidh e san eisimpleir:

Mar a tha na foirmlean seo ag obair

Bidh am foirmle seo ag obair san aon dòigh ris an fhoirmle àbhaisteach INDEX() MATCH(). Gus iomadh suidheachadh a lorg, bidh thu dìreach a’ cruthachadh grunn shuidheachaidhean meallta agus fìor a tha a’ riochdachadh nan suidheachaidhean fa leth ceart agus ceàrr. Agus an uairsin tha na cumhaichean sin a’ buntainn ris a h-uile eileamaid co-fhreagarrach den raon. Bidh am foirmle ag atharrachadh na h-argamaidean Meallta is Fìor gu 0 agus 1, fa leth, agus a’ toirt a-mach sreath far a bheil 1 na luachan maidsidh a chaidh a lorg anns an t-sreang. Lorgaidh MATCH() a’ chiad luach a tha a’ maidseadh 1 agus bheir e dhan fhoirmle INDEX(). Agus tillidh e, an uair sin, an luach a tha thu ag iarraidh mar-thà anns an loidhne ainmichte bhon cholbh a tha thu ag iarraidh.

Tha foirmle gun raon an urra ri comas INDEX () gus an làimhseachadh leis fhèin. Tha an dàrna INDEX () anns an fhoirmle a 'maidseadh meallta (0), agus mar sin bidh e a' dol seachad air an raon gu lèir leis na luachan sin gu foirmle MATCH (). 

Is e mìneachadh caran fada a tha seo air an loidsig air cùl na foirmle seo. Airson tuilleadh fiosrachaidh leugh an artaigil “MATCH INDEX le iomadh suidheachadh".

Cuibheasach, MAX agus MIN ann an INDEX agus MATCH

Tha na gnìomhan sònraichte aige fhèin aig Excel airson a bhith a’ lorg cuibheasachd, as àirde agus as ìsle. Ach dè ma tha thu airson dàta fhaighinn bhon chill co-cheangailte ris na luachan sin? Anns a 'chùis seo Feumar AVERAGE, MAX agus MIN a chleachdadh an co-bhonn ri INDEX agus MATCH.

MATCH INDEX agus MAX

Gus an luach as motha a lorg ann an colbh D agus a thaisbeanadh ann an colbh C, cleachd am foirmle: 

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

MATCH INDEX agus MIN

Gus an luach as lugha a lorg ann an colbh D agus a thaisbeanadh ann an colbh C, cleachd am foirmle a leanas:

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

SEARCH INDEX and SERPENT

Gus an luach cuibheasach ann an colbh D a lorg agus an luach seo a thaisbeanadh ann an C:

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

A rèir mar a tha an dàta agad air a sgrìobhadh, is e an treas argamaid airson MATCH() 1, 0, no -1:

• ma tha na colbhan air an rèiteachadh ann an òrdugh dìreadh, suidhich 1 (an uairsin obraichidh am foirmle an luach as àirde, a tha nas lugha na no co-ionann ris an luach cuibheasach);
• ma tha an seòrsa a 'teàrnadh, an uairsin -1 (bidh am foirmle a' toirt a-mach an luach as ìsle a tha nas motha na no co-ionnan ris a 'chuibheasachd);
• ma tha luach anns an raon lorg a tha dìreach co-ionann ris a’ chuibheasachd, cuir gu 0 e. 

 Anns an eisimpleir againn, tha an sluagh air a rèiteachadh ann an òrdugh teàrnaidh, agus mar sin chuir sinn -1. Agus is e an toradh Tokyo, leis gur e luach an t-sluaigh (13,189) an ìre as fhaisge air an luach cuibheasach (000).

Faodaidh VLOOKUP () àireamhachadh mar sin a dhèanamh cuideachd, ach dìreach mar fhoirmle rèite: VLOOKUP le AVERAGE, MIN agus MAX.

MATCH INDEX agus ESND/IFERROR

Is dòcha gu bheil thu air mothachadh mu thràth mura h-urrainn don fhoirmle an luach a tha thu ag iarraidh a lorg, bidh e a’ tilgeil mearachd # N / A.. Faodaidh tu rudeigin nas fiosrachail a chuir an àite an teachdaireachd mearachd àbhaisteach. Mar eisimpleir, suidhich an argamaid anns an fhoirmle Anns an XNUMXth:

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

Leis an fhoirmle seo, ma chuireas tu a-steach dàta nach eil sa chlàr, bheir am foirm dhut an teachdaireachd ainmichte.

Ma tha thu airson a h-uile mearachd a ghlacadh, an uairsin ach a-mhàin Anns an XNUMXth a chleachdadh IFERROR:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), "Chaidh rudeigin ceàrr!")

Ach cuimhnich nach e deagh bheachd a th’ ann a bhith a’ falach mhearachdan san dòigh seo, leis gu bheil mearachdan àbhaisteach ag aithris brisidhean san fhoirmle.

Tha sinn an dòchas gun d’fhuair thu feum air an stiùireadh againn mu bhith a’ cleachdadh an gnìomh INDEX MATCH() .