Kungani i-INDEX kanye ne-MATCH zingcono kune-VLOOKUP ku-Excel

Sike sachazela abasaqalayo ukuthi isetshenziswa kanjani imisebenzi eyisisekelo ye-VLOOKUP (isiNgisi VLOOKUP, isifinyezo simelela “umsebenzi wokubheka oqondile”). Futhi abasebenzisi abanolwazi baboniswe amafomula ambalwa ayinkimbinkimbi.

Futhi kulesi sihloko sizozama ukunikeza ulwazi mayelana nenye indlela yokusebenza ngokusesha okuqondile.

Ungase uzibuze: "Kungani lokhu kudingekile?". Futhi lokhu kuyadingeka ukuze ubonise zonke izindlela zokusesha okungenzeka. Ngaphezu kwalokho, imikhawulo eminingi ye-VLOOKUP ivamise ukuvimbela ukuthola umphumela ofunekayo. Mayelana nalokhu, INDEX( ) MATCH( ) iyasebenza kakhulu futhi iyahlukahluka, futhi inemikhawulo embalwa.

Okuyisisekelo INDEX MATCH

Njengoba inhloso yalo mhlahlandlela kuwukukhombisa ukuthi lesi sici sisihle kangakanani, thina Ake sibheke ulwazi oluyisisekelo mayelana nemigomo yokusebenza kwayo. Futhi sizobonisa izibonelo, futhi sicabangele nokuthi kungani, kungcono kune-VLOOKUP ().

I-INDEX Function Syntax kanye nokusetshenziswa

Lo msebenzi usiza ekutholeni inani elifunekayo phakathi kwezindawo zokusesha ezishiwo ngokusekelwe kukholomu noma inombolo yomugqa. I-Syntax:

=INDEX(uhlu, inombolo yomugqa, inombolo yekholomu):

• array – indawo okuzoseshwa kuyo;
• inombolo yomugqa - inombolo yomugqa ozoseshwa ohlwini olucacisiwe. Uma inombolo yomugqa ingaziwa, inombolo yekholomu kufanele icaciswe;
• inombolo yekholomu - inombolo yekholomu ezotholakala kuhlelo olushiwo. Uma inani lingaziwa, inombolo yomugqa iyadingeka.

Isibonelo sefomula elula:

=INDEX(A1:S10,2,3)

Umsebenzi uzosesha ebangeni ukusuka ku-A1 kuye ku-C10. Izinombolo zibonisa ukuthi yimuphi umugqa (2) kanye nekholomu (3) okumele kuboniswe inani elifiswayo kulo. Umphumela uzoba iseli C2.

Ilula kakhulu, akunjalo? Kodwa uma usebenza ngamadokhumenti angempela, cishe ngeke ube nolwazi mayelana nezinombolo zekholomu noma amaseli. Yilokho umsebenzi we-MATCH() owenzelwa.

I-Syntax Yomsebenzi Wokufanisa nokusetshenziswa

Umsebenzi MATCH() usesha inani elifiswayo futhi ubonise inombolo yawo ehlawumbiselwe endaweni ethile yokusesha.

I-searchpos() syntax ibukeka kanje:

=MATCH(inani elizobhekwa, uhlu oluzobhekwa, uhlobo lokufanisa)

• inani lokusesha - inombolo noma umbhalo ozotholwa;
• uhlu oluseshiwe - indawo lapho kuzoseshwa khona;
• uhlobo lokufanisa - icacisa ukuthi kufanele ubheke inani eliqondile noma amanani aseduze kakhulu nalo:
  • 1 (noma alikho inani elishiwo) – ibuyisela inani elikhulu elilingana noma elingaphansi kwenani elishiwo;
  • 0 – ikhombisa ukufana ncamashi nevelu eliseshiwe. Kunhlanganisela INDEX() MATCH() cishe uzodinga ukufana ncamashi, ngakho-ke sibhala u-0;
  • -1 – Ibonisa inani elincane kakhulu elikhulu noma elilingana nenani elishiwo kufomula. Ukuhlunga kwenziwa ngohlelo olwehlayo.

Isibonelo, kububanzi B1:B3 New York, Paris, London abhalisiwe. Ifomula engezansi izokhombisa inombolo 3 ngoba iLondon ingeyesithathu ohlwini:

=EXPOSE(London,B1:B3,0)

Ungasebenza kanjani ngomsebenzi we-INDEX MATCH 

Cishe usuvele uqalile ukuqonda isimiso okwakhiwe ngaso umsebenzi ohlangene wale misebenzi. Ngamafuphi-ke INDEX() isesha inani elifiswayo phakathi kwemigqa namakholomu ashiwo. Futhi MATCH() ikhombisa izinombolo zalawa manani:

=INDEX(ikholomu okubuyiswa kuyo inani, MATCH(inani lokusesha, ikholomu okumele kuseshwe kuyo, 0))

Usakuthola kunzima ukuqonda ukuthi kusebenza kanjani? Mhlawumbe isibonelo sizochaza kangcono. Ake sithi unohlu lwezihloko zomhlaba kanye nenani labantu:

Ukuze sithole ubukhulu besibalo sabantu benhloko-dolobha ethile, isibonelo, inhloko-dolobha yaseJapane, sisebenzisa le fomula elandelayo:

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

Incazelo:

• Umsebenzi MATCH() ubheka inani – “Japan” ohlwini A2:A10 futhi ubuyisela inombolo 3, ngoba i-Japan iyinani lesithathu ohlwini. 
• Lesi sibalo siya kuinombolo yomugqa” kufomula INDEX() futhi itshela umsebenzi ukuthi uphrinte inani elisuka kulo mugqa.

Ngakho le fomula engenhla iba ifomula evamile INHLOKO(C2:C10,3). Ifomula isesha kusuka kumaseli C2 kuye ku-C10 futhi ibuyisela idatha kusuka kuseli lesithathu kulobu bubanzi, okungukuthi, u-C4, ngenxa yokuthi ukubala phansi kuqala kusukela kumugqa wesibili.

Awufuni ukunikeza igama lomuzi kufomula? Bese uyibhala kunoma iyiphi iseli, ithi F1, bese uyisebenzisa njengereferensi kufomula MATCH(). Futhi ugcina unefomula yosesho enamandla:

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

Kubalulekile! Inombolo yemigqa ku i-array INDEX() kumelwe ifane nenani lemigqa phakathi kucatshangelwe uhlu ku-MATCH(), kungenjalo uzothola umphumela ongalungile.

Ima kancane, kungani ungavele usebenzise ifomula ye-VLOOKUP()?

=I-VLOOKUP(F1, A2:C10, 3, Amanga)

 Kuyini iphuzu lokumosha isikhathi uzama ukuthola zonke lezi zingqinamba ze-INDEX MATCH?

Kulokhu, akunandaba ukuthi yimuphi umsebenzi okufanele usetshenziswe. Lesi isibonelo nje sokuqonda ukuthi i-INDEX() kanye ne-MATCH() imisebenzi isebenza ndawonye. Ezinye izibonelo zizobonisa ukuthi le misebenzi ikwazi ukwenzani ezimeni lapho i-VLOOKUP ingenawo amandla. 

INDEX MATCH noma i-VLOOKUP

Lapho benquma ukuthi iyiphi ifomula yosesho abazoyisebenzisa, abaningi bayavuma ukuthi INDEX() kanye ne-MATCH() ziphakeme kakhulu kune-VLOOKUP. Nokho, abantu abaningi basasebenzisa i-VLOOKUP(). Okokuqala, i-VLOOKUP() ilula, futhi okwesibili, abasebenzisi abaziqondi ngokugcwele zonke izinzuzo zokusebenza nge-INDEX() kanye ne-MATCH(). Ngaphandle kwalolu lwazi, akekho ozovuma ukuchitha isikhathi sakhe efunda uhlelo oluyinkimbinkimbi.

Nazi izinzuzo ezibalulekile ze-INDEX() kanye ne-MATCH() ngaphezu kwe-VLOOKUP():

• Sesha kusukela kwesokudla kuye kwesobunxele. I-VLOOKUP() ayikwazi ukusesha ukusuka kwesokudla ukuya kwesokunxele, ngakho-ke amanani owafunayo kufanele ahlale ekholomu yesokunxele yethebula. Kodwa I-INDEX() kanye ne-MATCH() ingakusingatha lokhu ngaphandle kwenkinga. Lesi sihloko sizokutshela ukuthi kubukeka kanjani ekusebenzeni: indlela yokuthola inani elifunayo ngakwesokunxele.

1. Ukwengeza okuphephile noma ukususwa kwamakholomu. Ifomula ye-VLOOKUP() ibonisa imiphumela engalungile lapho ukhipha noma wengeza amakholomu ngoba i-VLOOKUP() idinga inombolo yekholomu enembile ukuze iphumelele. Ngokwemvelo, lapho amakholomu engezwa noma esuswa, izinombolo zawo nazo ziyashintsha. 

Futhi kumafomula INDEX() kanye ne-MATCH(), kucacisiwe ububanzi bamakholomu, hhayi amakholomu angawodwana. Ngenxa yalokho, ungakwazi ukwengeza nokukhipha amakholomu ngokuphephile ngaphandle kokubuyekeza ifomula isikhathi ngasinye.

1. Ayikho imikhawulo emiqulwini yosesho. Uma usebenzisa i-VLOOKUP(), inani lenombolo yokusesha akumele lidlule izinhlamvu ezingu-255 noma uzothola #VALUE! Ngakho-ke uma idatha yakho iqukethe inani elikhulu lezinhlamvu, i-INDEX() kanye ne-MATCH() iyindlela engcono kakhulu.
2. Isivinini sokucubungula esiphezulu. Uma amatafula akho emancane uma kuqhathaniswa, cishe ngeke uphawule noma yimuphi umehluko. Kodwa, uma ithebula liqukethe amakhulu noma izinkulungwane zemigqa, futhi, ngokufanelekile, kukhona amakhulu nezinkulungwane zamafomula, i-INDEX () kanye ne-MATCH () zizobhekana ngokushesha kakhulu kune-VLOOKUP (). Iqiniso liwukuthi i-Excel izocubungula kuphela amakholomu ashiwo kufomula, esikhundleni sokucubungula lonke ithebula. 

Umthelela wokusebenza we-VLOOKUP() uzobonakala ikakhulukazi uma ishidi lakho lokusebenza liqukethe inani elikhulu lamafomula afana ne-VLOOKUP() kanye ne-SUM(). Ukuhlola okuhlukene kwemisebenzi ye-VLOOKUP() kuyadingeka ukuze kuncozululwe inani ngalinye kumalungu afanayo. Ngakho-ke i-Excel kufanele icubungule inani elikhulu lolwazi, futhi lokhu kubambezela umsebenzi kakhulu.

Izibonelo Zefomula 

Sesikutholile kakade usizo lwale misebenzi, ngakho-ke singadlulela engxenyeni ethakazelisa kakhulu: ukusetshenziswa kolwazi ekusebenzeni.

Ifomula yokusesha ukusuka kwesokudla kuye kwesokunxele

Njengoba sekushiwo, i-VLOOKUP ayikwazi ukwenza lolu hlobo lokusesha. Ngakho-ke, uma amanani afunekayo engekho kukholamu kwesokunxele, i-VLOOKUP() ngeke ikhiqize umphumela. Imisebenzi ye-INDEX() kanye ne-MATCH() iguquguquka kakhulu, futhi indawo yamanani ayidlali indima enkulu ukuthi isebenze.

Isibonelo, sizokwengeza ikholomu yezinga kwesokunxele setafula lethu futhi sizame ukuthola ukuthi inhlokodolobha Yezwe Lethu ikuliphi izinga ngokwesibalo sabantu.

Kuseli G1, sibhala inani elizotholwa, bese sisebenzisa ifomula elandelayo ukusesha kububanzi C1:C10 futhi sibuyisela inani elihambisanayo ukusuka ku-A2:A10:

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

Shesha. Uma uhlela ukusebenzisa le fomula kumaseli amaningi, qiniseka ukuthi ulungisa ububanzi usebenzisa ikheli eliphelele (isibonelo, $A$2: $A$10 kanye ne-$C$2: 4C$10).

INHLOKO OKUNINGI ADAMBWE KAKHULU  ukusesha kumakholomu nemigqa

Kulezi zibonelo ezingenhla, sisebenzise le misebenzi njengokumiselela i-VLOOKUP() ukuze sibuyisele amanani kusuka kububanzi obuchazwe ngaphambilini bemigqa. Kodwa kuthiwani uma udinga ukwenza i-matrix noma usesho olunezinhlangothi ezimbili?

Kuzwakala kuyinkimbinkimbi, kodwa ifomula yezibalo ezinjalo iyafana nefomula evamile INDEX() MATCH(), inomehluko owodwa kuphela: i-MATCH() ifomula kufanele isetshenziswe kabili. Isikhathi sokuqala sokuthola inombolo yomugqa, futhi okwesibili ukuthola inombolo yekholomu:

=INDEX(uhlu, OKUFANISAYO(inani lokusesha elimile, ikholomu yosesho, 0), OKUFANELEKILE(inani lokusesha elivundlile, umugqa wosesho, 0))

Ake sibheke ithebula elingezansi bese sizame ukwenza ifomula INDEX() EXPRESS() EXPRESS() ukuze ubonise izibalo zabantu ezweni elithile ngonyaka okhethiwe.

Izwe okuhlosiwe likuseli G1 (ukubheka okuqondile) futhi unyaka okuhlosiwe ukuseli G2 (ukubheka okuvundlile). Ifomula izobukeka kanje:

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

Isebenza kanjani le fomula

Njenganoma yimaphi amanye amafomula ayinkimbinkimbi, kulula ukuwaqonda ngokuwahlukanisa abe izibalo ngazinye. Futhi-ke ungaqonda ukuthi umsebenzi ngamunye wenzani:

• I-MATCH(G1,A2:A11,0) – ibheka inani (G1) kububanzi A2:A11 futhi ibonisa inombolo yaleli nani, esimweni sethu ingu-2;
• SESHA(G2,B1:D1,0) – ibheka inani (G2) kububanzi B1:D1. Kulokhu, umphumela waba ngu-3.

Izinombolo zomugqa nekholomu ezitholiwe zithunyelwa enanini elihambisanayo kufomula INDEX():

=INDEX(B2:D11,2,3)

Ngenxa yalokho, sinevelu eliseselini empambana imigqa emi-2 namakholomu angu-3 kububanzi B2:D11. Futhi ifomula ikhombisa inani elifunekayo, elikuseli D3.

Sesha ngezimo eziningi nge-INDEX kanye ne-MATCH

Uma ufunde umhlahlandlela wethu ku-VLOOKUP(), cishe uzame amafomula okusesha amaningi. Kodwa le ndlela yokusesha inomkhawulo owodwa obalulekile - isidingo sokwengeza ikholomu eyisizayo.

Kodwa izindaba ezinhle ukuthi Nge-INDEX() kanye ne-MATCH() ungasesha izimo eziningi ngaphandle kokuhlela noma ukushintsha ishidi lakho lokusebenza.

Nali ifomula evamile yosesho lwemibandela eminingi ye-INDEX() MATCH():

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

Inothi: le fomula kufanele isetshenziswe kanye nesinqamuleli sekhibhodi CTRL+SHIFT+ENTER.

Ake sithi udinga ukuthola inani olifunayo ngokusekelwe ezimweni ezi-2: Umthengi и Umkhiqizo.

Lokhu kudinga ifomula elandelayo:

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

Kule fomula, i-C2:C10 yibanga lapho ukusesha okuzokwenzeka khona, F1 - lesi simo, A2:A10 - ububanzi bokuqhathanisa isimo, F2 - isimo 2, iv2:v10 - ububanzi bokuqhathanisa umbandela 2.

Ungakhohlwa ukucindezela inhlanganisela ekupheleni komsebenzi ngefomula CTRL+SHIFT+ENTER - I-Excel izovala ngokuzenzakalelayo ifomula ngama-curly braces, njengoba kukhonjisiwe esibonelweni:

Uma ungafuni ukusebenzisa ifomula yamalungu afanayo ngomsebenzi wakho, bese wengeza enye INDEX() kufomula bese ucindezela okuthi ENTER, izobukeka njengesibonelo:

Asebenza kanjani la mafomula

Le fomula isebenza ngendlela efanayo nefomula INDEX() MATCH() ejwayelekile. Ukuze useshele izimo eziningi, umane udale izimo eziningi ezingamanga nezeQiniso ezimele izimo zomuntu ngamunye ezilungile nezingalungile. Bese-ke le mibandela isebenza kuzo zonke izakhi ezihambisanayo zamalungu afanayo. Ifomula iguqula ama-agumenti Amanga nokuthi Iqiniso abe ngu-0 no-1, ngokulandelanayo, futhi akhiphe amalungu afanayo lapho u-1 enamanani afanayo atholwe kuyunithi yezinhlamvu. MATCH() izothola inani lokuqala elifana no-1 futhi ilidlulisele ku-INDEX() ifomula. Futhi, yona, izobuyisela inani elivele lifiswa emugqeni oshiwo kusukela kukholomu oyifunayo.

Ifomula engenamalungu afanayo incike ekhonweni le-INDEX() lokuzisingatha ngokwayo. I-INDEX() yesibili kufomula ifana namanga (0), ngakho idlulisa wonke amalungu nalawo manani kufomula MATCH(). 

Lena incazelo ende yomqondo ongemuva kwale fomula. Ukuze uthole ulwazi olwengeziwe funda isihloko esithi “I-INDEX MATCH enemibandela eminingi".

I-AVERAGE, MAX kanye ne-MIN ku-INDEX naku-MATCH

I-Excel inemisebenzi yayo ekhethekile yokuthola ama-avareji, ama-maximus, kanye nokuncane. Kodwa kuthiwani uma ufuna ukuthola idatha kuseli elihlotshaniswa nalawo manani? Esimweni esinjalo I-AVERAGE, MAX kanye ne-MIN kufanele kusetshenziswe ngokuhambisana ne-INDEX kanye ne-MATCH.

INDEX MATCH kanye no-MAX

Ukuze uthole inani elikhulu kunawo wonke kukholamu D futhi ulibonise kukholamu C, sebenzisa ifomula: 

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

INDEX MATCH kanye MIN

Ukuze uthole inani elincane kakhulu kukholomu D futhi ulibonise kukholamu C, sebenzisa ifomula elandelayo:

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

SESHA I-INDEX kanye ne-SERPENT

Ukuze uthole inani elimaphakathi kukholomu D futhi ubonise leli nani kokuthi C:

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

Kuye ngokuthi idatha yakho ibhalwa kanjani, impikiswano yesithathu kokuthi MATCH() ingu-1, 0, noma -1:

• uma amakholomu ehlungwa ngendlela ekhuphukayo, setha u-1 (bese ifomula izobala inani eliphakeme, elingaphansi noma elilingana nenani elijwayelekile);
• uma uhlobo lwehla, khona-ke -1 (ifomula izokhipha inani elincane elikhulu noma elilingana nesilinganiso);
• uma uhlu lokubheka luqukethe inani elilingana ncamashi nesilinganiso, lisethe ku-0. 

 Esibonelweni sethu, inani labantu lihlelwa ngendlela eyehlayo, ngakho-ke sibeka -1. Futhi umphumela uba yi-Tokyo, njengoba inani labantu (13,189) liseduze kakhulu nenani elijwayelekile (000).

I-VLOOKUP() ingakwazi futhi ukwenza izibalo ezinjalo, kodwa kuphela njengefomula yamalungu afanayo: I-VLOOKUP ene-AVERAGE, MIN kanye ne-MAX.

I-INDEX MATCH kanye ne-ESND/IFERROR

Cishe usubonile ukuthi uma ifomula ingakwazi ukuthola inani olifunayo, iphonsa iphutha # N / A. Ungakwazi esikhundleni somlayezo wephutha ojwayelekile ngokuthile okwaziyo kakhulu. Isibonelo, setha impikiswano kufomula Kwe-XNUMX:

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

Ngale fomula, uma ufaka idatha engekho etafuleni, ifomu lizokunikeza umlayezo oshiwo.

Uma ufuna ukubamba wonke amaphutha, ke ngaphandle Kwe-XNUMX ingasetshenziswa IFERROR:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), “Kukhona okungahambanga kahle!”)

Kodwa khumbula ukuthi amaphutha okufihla ngale ndlela akuwona umqondo omuhle, ngoba amaphutha ajwayelekile abika ukwephulwa kwefomula.

Sithemba ukuthi uthole umhlahlandlela wethu wokusebenzisa umsebenzi we-INDEX MATCH() ulusizo.