Nei INDEX uye MATCH zviri nani pane VLOOKUP muExcel

Isu takambotsanangurira kune vanotanga mashandisirwo ekutanga mabasa eVLOOKUP (Chirungu VLOOKUP, chidimbu chinomiririra "vertical lookup function"). Uye vashandisi vane ruzivo vakaratidzwa akati wandei akaomesesa mafomula.

Uye munyaya ino tichaedza kupa ruzivo pamusoro peimwe nzira yekushanda nekutsvaga vertical.

Iwe unogona kunge uchinetseka: "Nei izvi zvichidikanwa?". Uye izvi zvakakosha kuitira kuratidza nzira dzese dzinogona kutsvaga. Pamusoro pezvo, zvirambidzo zvakawanda zveVLOOKUP zvinowanzo dzivirira kuwana mhedzisiro yaunoda. Panyaya iyi, INDEX() MATCH() inonyanya kushanda uye yakasiyana, uye vanewo zvirambidzo zvishoma.

Basics INDEX MATCH

Sezvo chinangwa chegwaro iri kuratidza kunaka kwakaita chimiro ichi, isu Ngatitarisei ruzivo rwekutanga maererano nemisimboti yekushanda kwayo. Uye isu ticharatidza mienzaniso, uye zvakare funga kuti sei, iri nani pane VLOOKUP ().

INDEX Basa Syntax uye Kushandisa

Iri basa rinobatsira mukutsvaga kukosha kwaunoda pakati penzvimbo dzakatsanangurwa dzekutsvaga zvichienderana nekoramu kana nhamba yemutsara. Syntax:

= INDEX(hurongwa, nhamba yemutsara, nhamba yembiru):

• array - nzvimbo iyo kutsvaga kuchaitika;
• nhamba yemutsara - nhamba yemutsara wekutsvaga mumutsara wakatsanangurwa. Kana nhamba yemutsara isingazivikanwe, nhamba yekoramu inofanira kutsanangurwa;
• column number - nhamba yekoramu inowanikwa muhurongwa hwakatarwa. Kana kukosha kusingazivikanwe, nhamba yemutsara inodiwa.

Muenzaniso weformula iri nyore:

=INDEX(A1:S10,2,3)

Basa racho richatsvaga mumhando kubva kuA1 kusvika kuC10. Nhamba dzinoratidza kuti ndeupi mutsara (2) uye koramu (3) kuratidza kukosha kwaunoda kubva. Mhedzisiro ichava sero C2.

Zviri nyore, handiti? Asi kana ukashanda nemagwaro chaiwo, haungaite kuti uve neruzivo maererano nenhamba dzekoramu kana maseru. Ndizvo zvinoitirwa MATCH() basa.

MATCH Basa Syntax uye Kushandisa

Iyo MATCH () basa rinotsvaga kukosha kwaunoda uye rinoratidza nhamba yaro yekufungidzira munzvimbo yakatarwa yekutsvaga.

Iyo searchpos() syntax inotaridzika seizvi:

= MATCH(kukosha kwekutarisa, kuronga kutarisa, rudzi rwemachisi)

• kutsvaga kukosha - nhamba kana zvinyorwa zvinowanikwa;
• yakatsvaga - nzvimbo iyo kutsvaga kuchaitika;
• match type - inotsanangura kana uchitsvaga kukosha chaiko kana hunhu huri padyo nayo:
  • 1 (kana kuti hapana kukosha kwataurwa) - inodzosera kukosha kukuru kwakaenzana kana kushoma pane kukosha kwakataurwa;
  • 0 - inoratidza chaiyo mechi ine kukosha kwakatsvaga. Mumusanganiswa INDEX() MATCH() unenge uchigara uchida mutambo chaiwo, saka tinonyora 0;
  • -1 - Inoratidza diki kukosha iyo yakakura kupfuura kana yakaenzana neiyo kukosha kwakataurwa mufomula. Kuronga kunoitwa mukudzika.

Semuenzaniso, mumhando yeB1: B3 New York, Paris, London yakanyoreswa. Iyo fomula pazasi inoratidza iyo nhamba 3 nekuti London ndeyechitatu pane iyo rondedzero:

=EXPOSE(London,B1:B3,0)

Maitiro ekushanda neiyo INDEX MATCH basa 

Iwe wakatotanga kunzwisisa nheyo iyo basa rekubatana remabasa aya rinovakwa. Muchidimbu, ipapo INDEX() inotsvaga kukosha kwaunoda pakati pemitsara nemakoramu ataurwa. Uye MATCH () inoratidza huwandu hweizvi zvakakosha:

=INDEX(column panodzoserwa kukosha, MATCH(kukosha kwekutsvaga, column yekutsvaga, 0))

Uchiri kunetsekana kunzwisisa kuti zvinoshanda sei? Zvichida muenzaniso uchatsanangura zviri nani. Ngatiti iwe une runyorwa rwemazita makuru enyika nehuwandu hwavo:

Kuti tizive saizi yehuwandu hweimwe capital, semuenzaniso, guta guru reJapan, tinoshandisa inotevera fomula:

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

Tsanangudzo:

• Iyo MATCH () basa rinotarisa kukosha - "Japan" muhurongwa A2: A10 uye inodzosera nhamba 3, nekuti Japan ndiyo yechitatu kukosha mune rondedzero. 
• Mufananidzo uyu unoendamutsara nhamba” mu INDEX() formula uye inoudza basa kuti ridhinde kukosha kubva mumutsara uyu.

Saka iyo fomula iri pamusoro inova iyo yakajairwa fomula INDEX(C2:C10,3). Iyo formula inotsvaga kubva kumasero C2 kuenda kuC10 uye inodzosa data kubva kune yechitatu sero mune iyi renji, kureva, C4, nekuti kuverenga kunotanga kubva pamutsara wechipiri.

Haudi kunyora zita reguta mune formula? Wobva wanyora mune chero sero, taura F1, uye shandisa sereferensi muMATCH() formula. Uye iwe unopedzisira uine ine simba yekutsvaga fomula:

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

Zvinokosha! Nhamba yemitsara mukati array INDEX() inofanira kufanana nehuwandu hwemitsara mukati akatariswa array muMATCH(), zvikasadaro uchawana mhedzisiro isiriyo.

Mirira kweminiti, wadii kungoshandisa iyo VLOOKUP() formula?

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

 Zvine basa rei kupedza nguva kuedza kufunga zvese izvi zvakaomarara zveINDEX MATCH?

Muchiitiko ichi, hazvina basa kuti ndeipi basa rekushandisa. Uyu ungori muenzaniso wekunzwisisa kuti INDEX() uye MATCH() mabasa anoshanda pamwechete. Mimwe mienzaniso inoratidza izvo mabasa aya anokwanisa kuita mumamiriro ezvinhu apo VLOOKUP isina simba. 

INDEX MATCH kana VLOOKUP

Pakusarudza nzira yekutsvaga yekushandisa, vazhinji vanobvuma kuti INDEX() uye MATCH() akanyanya kukunda VLOOKUP. Nekudaro, vanhu vazhinji vachiri kushandisa VLOOKUP (). Chekutanga, VLOOKUP () iri nyore, uye chechipiri, vashandisi havanzwisise zvizere zvakanakira kushanda neINDEX () uye MATCH (). Pasina ruzivo urwu, hapana anobvuma kushandisa nguva yake achidzidza yakaoma system.

Heano mabhenefiti akakosha eINDEX() uye MATCH() pamusoro peVLOOKUP():

• Tsvaga kubva kurudyi kuenda kuruboshwe. VLOOKUP () haigone kutsvaga kubva kurudyi kuenda kuruboshwe, saka kukosha kwauri kutsvaga kunofanirwa kugara kuri kuruboshwe kwetafura. Asi INDEX() uye MATCH() inogona kubata izvi pasina dambudziko. Ichi chinyorwa chichakuudza kuti chinoratidzika sei mukuita: nzira yekuwana kukosha kwaunoda kuruboshwe.

1. Kuwedzera kwakachengeteka kana kubviswa kwembiru. Iyo VLOOKUP () fomula inoratidza mhedzisiro isiriyo pakubvisa kana kuwedzera makoramu nekuti VLOOKUP () inoda iyo chaiyo nhamba nhamba kuti ibudirire. Nomuzvarirwo, kana makoramu awedzerwa kana kubviswa, nhamba dzawo dzinochinjawo. 

Uye mune INDEX () uye MATCH () mafomula, huwandu hwemakoramu hunotsanangurwa, kwete makoramu ega. Nekuda kweizvozvo, unogona kuwedzera nekubvisa makoramu zvakachengeteka pasina kugadzirisa fomura nguva yega yega.

1. Hapana miganhu pamavhoriyamu ekutsvaga. Paunenge uchishandisa VLOOKUP(), huwandu hwese hwekutsvaga haufanire kudarika mavara mazana maviri nemakumi mashanu kana kuti uchawana #VALUE! Saka kana data rako riine nhamba huru yemavara, INDEX() uye MATCH() ndiyo yakanakisa sarudzo.
2. High processing speed. Kana matafura ako ari madiki, saka haugone kuona chero musiyano. Asi, kana tafura iine mazana kana zviuru zvemitsara, uye, saizvozvo, kune mazana nezviuru zvemafomula, INDEX () uye MATCH () ichaita nekukurumidza kupfuura VLOOKUP (). Icho chokwadi ndechekuti Excel inongogadzirisa makoramu anotsanangurwa mufomula, pane kugadzirisa tafura yese. 

Kuita kweVLOOKUP () kuchanyanya kuoneka kana pepa rako rebasa riine nhamba huru yemafomati seVLOOKUP () uye SUM (). Akaparadzana cheki yeVLOOKUP () mabasa anodiwa kuti utarise kukosha kwega kwega muhurongwa. Saka Excel inofanirwa kugadzirisa huwandu hukuru hweruzivo, uye izvi zvinodzikisira basa zvakanyanya.

Formula Mienzaniso 

Isu takatoona kukosha kweaya mabasa, saka tinogona kuenderera kune chikamu chinonakidza: kushandiswa kwezivo mukuita.

Formula yekutsvaga kubva kurudyi kuenda kuruboshwe

Sezvatotaurwa, VLOOKUP haikwanise kuita iyi nzira yekutsvaga. Saka, kana hunhu hunodiwa usiri mukoramu yekuruboshwe, VLOOKUP () haiburitse mhedzisiro. Iyo INDEX () uye MATCH () mabasa anochinja-chinja, uye nzvimbo yemakoshero haite basa rakakura kuti vashande.

Semuyenzaniso, isu tichawedzera renji chikamu kuruboshwe rwetafura yedu uye kuyedza kufunga kuti ndechipi chinzvimbo maererano nehuwandu hwevanhu guta guru reNyika Yedu rinogara.

Musero G1, tinonyora kukosha kunowanikwa, uye toshandisa fomula inotevera kutsvaga mumutsara C1: C10 uye kudzorera kukosha kunoenderana kubva kuA2: A10:

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

Prompt. Kana iwe ukaronga kushandisa iyi fomula kune akawanda maseru, ita shuwa kuti unogadzirisa marenji uchishandisa asolute kero (semuenzaniso, $A$2:$A$10 uye $C$2:4C$10).

INDEX ZVIMWE ZVAKAFUNDURWA ZVAKAWANDA ZVAKAFUNDURWA  kutsvaga mumakoroni nemitsara

Mumienzaniso iri pamusoro, tashandisa mabasa aya sechinotsiva VLOOKUP () kudzorera kukosha kubva kune yakafanotaurwa yemitsara. Asi zvakadini kana iwe uchida kuita matrix kana maviri-mativi ekutsvaga?

Zvinonzwika zvakaoma, asi fomula yekuverenga kwakadaro yakafanana neyakajairwa INDEX() MATCH() formula, ine musiyano mumwe chete: iyo MATCH () fomula inofanira kushandiswa kaviri. Kekutanga kuwana nhamba yemutsara, uye kechipiri kuwana nhamba yekoramu:

=INDEX(kurongana, MATCH(yakatwasuka kutsvaga kukosha, koramu yekutsvaga, 0), MATCH(yakachinjika kutsvaga kukosha, mutsara wekutsvaga, 0))

Ngatitarisei tafura iri pazasi uye edza kugadzira formula INDEX() EXPRESS() EXPRESS() kuitira kuratidza huwandu hwevanhu mune imwe nyika kwegore rakasarudzwa.

Nyika inotariswa iri muchitokisi G1 (vertical lookup) uye gore rinotangwa riri muchitokisi G2 (horizontal lookup). Iyo formula ichaita seizvi:

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

Kuti fomula iyi inoshanda sei

Sezvimwe nemamwe mafomula akaomesesa, ari nyore kunzwisisa nekuapatsanura kuita equation yega. Uye ipapo iwe unogona kunzwisisa izvo basa rega rega rinoita:

• MATCH(G1,A2:A11,0) - inotarisa kukosha (G1) muhuwandu hweA2: A11 uye inoratidza nhamba yeiyi kukosha, kwatiri ndeye 2;
• TSVAGA(G2,B1:D1,0) - inotarisa kukosha (G2) muhuwandu B1: D1. Muchiitiko ichi, mhedzisiro yaive 3.

Iyo yakawanikwa mutsara uye nhamba dzekoramu dzinotumirwa kune inowirirana kukosha muINDEX () formula:

=INDEX(B2:D11,2,3)

Nekuda kweizvozvo, tine kukosha kuri musero pamharadzano yemitsara miviri nemakoramu matatu muchikamu B2:D3. Uye iyo formula inoratidza kukosha kwaunoda, iyo iri muchitokisi D2.

Tsvaga nemaitiro akawanda neINDEX uye MATCH

Kana iwe wakaverenga gwara redu kuVLOOKUP (), iwe unogona kunge wakaedza akawanda ekutsvaga mafomula. Asi iyi nzira yekutsvaga ine chinhu chimwe chete chakakosha - kudiwa kwekuwedzera koramu yekubatsira.

Asi nhau dzakanaka ndedzekuti NeINDEX() uye MATCH() unogona kutsvaga akawanda mamiriro pasina kugadzirisa kana kushandura pepa rako rebasa.

Heino yakajairika yakawanda-mamiriro ekutsvaga fomula yeINDEX() MATCH():

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

Chinyorwa: iyi fomula inofanirwa kushandiswa pamwe chete neiyo keyboard shortcut CTRL+SHIFT+ENTER.

Ngatiti iwe unoda kuwana kukosha kwauri kutsvaga zvichienderana ne2 mamiriro: mutengi и Chigadzirwa.

Izvi zvinoda formula inotevera:

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

Mune iyi fomula, C2: C10 ndiyo nhanho umo kutsvaga kuchaitika, F1 - condition iyi, A2:A10 — ndiyo chiyero chekuenzanisa mamiriro, F2 - mamiriro 2, V2:V10 - chiyero chekuenzanisa mamiriro 2.

Usakanganwa kudzvanya musanganiswa pakupera kwebasa neformula CTRL+SHIFT+ENTER -Excel inozovhara otomatiki fomula ine curly braces, sezvinoratidzwa mumuenzaniso:

Kana iwe usingade kushandisa array formula yebasa rako, wobva wawedzera imwe INDEX() kune fomula uye wodzvanya ENTER, ichaita senge mumuenzaniso:

Mashandiro aya mafomula

Iyi fomula inoshanda zvakafanana neyakajairwa INDEX() MATCH() formula. Kutsvaga akawanda mamiriro, iwe unongogadzira akawanda Nhema uye Chokwadi mamiriro anomiririra echokwadi uye asina kunaka mamiriro emunhu. Uye zvino aya mamiriro anoshanda kune ese anoenderana zvinhu zveiyo array. Formula inoshandura Nhema neChokwadi nharo kuenda ku0 na 1, zvichiteerana, uye inoburitsa dhairekitori apo 1 ndiyo inowirirana kukosha kwakawanikwa mutambo. MATCH() ichawana kukosha kwekutanga kunoenderana ne1 uye kuipfuudza kune INDEX() formula. Uye iyo, zvakare, ichadzosa iyo yatodiwa kukosha mumutsara wakatarwa kubva kune yaunoda koramu.

Formula isina array zvinoenderana nekugona kwe INDEX() kuzvibata wega. Yechipiri INDEX() mufomula inoenderana nemanyepo (0), saka inopfuudza hurongwa hwese neaya maitiro kune MATCH() formula. 

Iyi itsananguro yakati rebei yezvine musoro zviri kuseri kweformula iyi. Kuti uwane rumwe ruzivo verenga chinyorwa "INDEX MATCH ine akawanda mamiriro".

AVERAGE, MAX uye MIN muINDEX neMATCH

Excel ine yayo yakakosha mabasa ekutsvaga mavhareji, maximum, uye mashoma. Asi ko kana iwe uchida kuwana data kubva muchitokisi chakabatana neaya maitiro? Panyaya iyi AVERAGE, MAX uye MIN inofanira kushandiswa pamwe chete neINDEX uye MATCH.

INDEX MATCH uye MAX

Kuti uwane kukosha kukuru mukoramu D uye woiratidza mumutsara C, shandisa fomula: 

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

INDEX MATCH uye MIN

Kuti uwane diki kukosha mukoramu D uye woiratidza mumutsara C, shandisa inotevera fomula:

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

TSVAGA INDEX uye SERPENT

Kuti uwane avhareji kukosha mukoramu D uye kuratidza kukosha uku muC:

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

Zvichienderana nemanyorerwo ako data, nharo yechitatu kuMATCH() ingangove 1, 0, kana -1:

• kana makoramu akarongedzerwa mukukwira kurongeka, seta 1 (ipapo iyo formula ichaverenga iyo yakanyanya kukosha, iyo isingasviki kana yakaenzana neavhareji kukosha);
• kana rudzi rwuri kudzika, zvino -1 (formula ichaburitsa kukosha kwediki kunopfuura kana kuenzana neavhareji);
• kana iyo lookup array iine kukosha kwakanyatso kuenzana neavhareji, woisa ku0. 

 Mumuenzaniso wedu, huwandu hwevanhu hwakarongwa mukudzika, saka tinoisa -1. Uye mhedzisiro iTokyo, sezvo kukosha kwehuwandu (13,189) ndiyo iri padyo neavhareji kukosha (000).

VLOOKUP () inogona zvakare kuita maverengero akadai, asi chete seyakarongwa fomula: VLOOKUP ine AVERAGE, MIN uye MAX..

INDEX MATCH uye ESND/IFERROR

Iwe unogona kunge watoona kuti kana fomula isingawane kukosha kwaunoda, inokanda mhosho # N / A. Iwe unogona kutsiva iyo yakajairwa mhosho meseji nechimwe chinhu chine ruzivo. Semuenzaniso, isa nharo mufomula Muna XNUMX:

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

Neiyi fomula, kana iwe ukaisa data risiri patafura, fomu rinokupa iyo yakataurwa meseji.

Kana iwe uchida kubata zvikanganiso zvese, saka kunze kwe Muna XNUMX inogona kushandiswa IFERROR:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), “Pane chakaipa!”)

Asi yeuka kuti masking zvikanganiso nenzira iyi haisi zano rakanaka, nekuti zvikanganiso zvakajairwa zvinoshuma kutyorwa mufomula.

Tinovimba wakawana gwara redu rekushandisa iyo INDEX MATCH() basa rinobatsira.