Hobaneng INDEX le MATCH li le betere ho feta VLOOKUP ho Excel

Re kile ra hlalosetsa ba qalang mokhoa oa ho sebelisa mesebetsi ea mantlha ea VLOOKUP (VLOOKUP ea Senyesemane, khutsufatso e emetse "vertical lookup function"). Mme basebelisi ba nang le boiphihlelo ba bonts'itsoe liforomo tse 'maloa tse rarahaneng.

'Me sehloohong sena re tla leka ho fana ka tlhahisoleseding mabapi le mokhoa o mong oa ho sebetsa ka lipatlisiso tse otlolohileng.

U ka 'na ua ipotsa: "Ke hobane'ng ha see se hlokahala?". 'Me sena sea hlokahala e le ho bontša mekhoa eohle ea ho batla e ka khonehang. Ntle le moo, lithibelo tse ngata tsa VLOOKUP hangata li thibela ho fumana sephetho se lakatsehang. Tabeng ena, INDEX() MATCH() e sebetsa haholo ebile e fapane, hape e na le lithibelo tse fokolang.

Lintho tsa motheo INDEX MATCH

Kaha sepheo sa tataiso ena ke ho bontša hore na tšobotsi ena e ntle hakae, re A re shebeng lintlha tsa motheo mabapi le melao-motheo ea ts'ebetso ea eona. 'Me re tla bontša mehlala, hape re nahane hore na ke hobane'ng ha e le molemo ho feta VLOOKUP ().

INDEX Mosebetsi oa Syntax le Tšebeliso

Ts'ebetso ena e thusa ho fumana boleng bo lakatsehang har'a libaka tsa ho batla tse boletsoeng ho latela kholomo kapa nomoro ea mohala. hokahanngoang:

=INDEX(sehlopha, nomoro ea mola, nomoro ea kholomo):

• array - sebaka seo patlo e tla etsahala ho sona;
• nomoro ea mohala - palo ea mohala o lokelang ho batlisisoa ka bongata bo boletsoeng. Haeba nomoro ea mola e sa tsejoe, nomoro ea kholomo e tlameha ho boleloa;
• nomoro ea kholumo - palo ea kholomo e tla fumanoa lethathamong le boletsoeng. Haeba boleng bo sa tsejoe, ho hlokahala nomoro ea mohala.

Mohlala oa foromo e bonolo:

=SEKANE(A1:S10,2,3)

Ts'ebetso e tla batla ho tloha ho A1 ho isa ho C10. Linomoro li bontša hore na ke mola ofe (2) le kholomo (3) ho bontša boleng bo lakatsehang ho tsoa ho. Sephetho e tla ba sele C2.

Ho bonolo haholo, ho nepahetse? Empa ha o sebetsa ka litokomane tsa 'nete, ha ho bonolo hore u be le tlhahisoleseling mabapi le linomoro tsa likholomo kapa lisele. Ke seo tšebetso ea MATCH() e leng sona.

MATCH Mosebetsi oa Syntax le Tšebeliso

MATCH() ts'ebetso e batla boleng bo lakatsehang mme e bonts'a palo ea eona e lekantsoeng sebakeng se boletsoeng.

The searchpos() syntax e shebahala tjena:

=MATCH(boleng ba ho sheba, tlhophiso ea ho batla, mofuta oa papali)

• boleng ba ho batla - nomoro kapa mongolo o tla fumanoa;
• sehlopha se batlisisitsoeng - sebaka seo ho tla batloa ho sona;
• mofuta oa papali - e hlalosa hore na u batla boleng bo nepahetseng kapa boleng bo haufi le eona:
  • 1 (kapa ha ho boleng bo boletsoeng) - e khutlisa boleng bo boholo bo lekanang kapa bo ka tlase ho boleng bo boletsoeng;
  • 0 - e bonts'a papali e tšoanang hantle le boleng bo batlisisitsoeng. Motsoakong oa INDEX() MATCH() o tla batla o hloka ho ts'oana hantle kamehla, kahoo re ngola 0;
  • -1 - E bonts'a boleng bo bonyenyane bo boholo ho feta kapa bo lekanang le boleng bo boletsoeng foromong. Ho hlopha ho etsoa ka tatellano e theohang.

Ka mohlala, ka mefuta ea B1: B3 New York, Paris, London e ngolisitsoe. Foromo e ka tlase e tla bonts'a nomoro ea 3 hobane London ke ea boraro lenaneng:

=SEBAKA(London,B1:B3,0)

Mokhoa oa ho sebetsa ka ts'ebetso ea INDEX MATCH 

Mohlomong u se u qalile ho utloisisa molao-motheo oo mosebetsi o kopanetsoeng oa mesebetsi ena o hahiloeng ka oona. Ka bokhutšoanyane, he INDEX() e batla boleng bo lakatsehang har'a mela le mela e boletsoeng. Mme MATCH() e bonts'a lipalo tsa boleng bona:

=INDEX(kholomo eo boleng bo khutlisang ho tsoa ho eona, MATCH(boleng ba ho batlisisa, kholomo eo u lokelang ho e batla, 0))

U ntse u thatafalloa ho utloisisa hore na e sebetsa joang? Mohlomong mohlala o tla hlalosa hantle. Ha re re u na le lethathamo la lihlooho tsa lefatše le baahi ba tsona:

Ho fumana boholo ba baahi ba motse-moholo o itseng, mohlala, motse-moholo oa Japane, re sebelisa mokhoa o latelang:

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

tlhaloso:

• Mosebetsi oa MATCH () o batla boleng - "Japan" ka lihlopha tsa A2: A10 'me e khutlisa nomoro ea 3, hobane Japane ke boleng ba boraro lethathamong. 
• Palo ena e ea honomoro ea mohala” ho INDEX() foromo mme e bolella mosebetsi ho hatisa boleng ho tsoa moleng ona.

Kahoo foromo e ka holimo e fetoha mokhoa o tloaelehileng INDEX(C2:C10,3). Foromo e batlisisa ho tloha liseleng tsa C2 ho ea ho C10 'me e khutlisa data ho tsoa seleng ea boraro lethathamong lena, ke hore, C4, hobane ho bala ho qala ho tloha moleng oa bobeli.

Ha u batle ho fana ka lebitso la motse ka foromo? Ebe u e ngola seleng efe kapa efe, e re F1, 'me u e sebelise e le tšupiso ho MATCH() formula. 'Me u qetella u e-na le mokhoa o matla oa ho batla:

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

Bohlokoa! Nomoro ea mela ho drive INDEX() e tlameha ho lekana le palo ea mela ho e nkoa e le array ho MATCH(), ho seng joalo u tla fumana sephetho se fosahetseng.

Ema hanyane, hobaneng o sa sebelise foromo ea VLOOKUP() feela?

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

 Ho senya nako ke eng ho leka ho fumana mathata ana kaofela a INDEX MATCH?

Tabeng ena, ha ho na taba hore na u ka sebelisa ts'ebetso efe. Ona ke mohlala feela oa ho utloisisa hore na mesebetsi ea INDEX() le MATCH() e sebetsa 'moho joang. Mehlala e meng e tla bontša hore na mesebetsi ena e khona ho etsa eng maemong ao VLOOKUP e se nang matla. 

INDEX MATCH kapa VLOOKUP

Ha ba etsa qeto ea hore na ba sebelise foromo efe ea ho batla, ba bangata ba lumela hore INDEX() le MATCH() li phahametse VLOOKUP haholo. Leha ho le joalo, batho ba bangata ba ntse ba sebelisa VLOOKUP (). Taba ea pele, VLOOKUP() e bonolo haholoanyane, 'me ea bobeli, basebelisi ha ba utloisise ka botlalo melemo eohle ea ho sebetsa ka INDEX() le MATCH(). Ntle le tsebo ena, ha ho motho ea tla lumela ho qeta nako ea hae a ithuta tsamaiso e rarahaneng.

Melemo ea bohlokoa ea INDEX() le MATCH() ho feta VLOOKUP() ke ena:

• Batla ho tloha ho le letona ho ea ho le letšehali. VLOOKUP () ha e khone ho batla ho tloha ho le letona ho ea ho le letšehali, kahoo litekanyetso tseo u li batlang li tlameha ho lula li le ka ho le letšehali la tafole. Empa INDEX() le MATCH() li ka sebetsana le sena ntle le bothata. Sehlooho sena se tla u bolella hore na se shebahala joang ts'ebetsong: mokhoa oa ho fumana boleng bo lakatsehang ka lehlakoreng le letšehali.

1. Keketso e bolokehileng kapa ho tlosoa ha litšiea. Foromo ea VLOOKUP() e bonts'a liphetho tse fosahetseng ha o tlosa kapa o kenya likholomo hobane VLOOKUP() e hloka nomoro ea kholomo e nepahetseng hore e atlehe. Ka tlhaho, ha litšiea li eketsoa kapa li tlosoa, lipalo tsa tsona le tsona lia fetoha. 

'Me ka liforomo tsa INDEX() le MATCH(), ho boletsoe mefuta e mengata ea likholomo, eseng likholomo ka bomong. Ka lebaka leo, o ka eketsa le ho tlosa likholomo ka mokhoa o sireletsehileng ntle le ho ntlafatsa foromo nako le nako.

1. Ha ho na meeli ho palo ea lipatlisiso. Ha u sebelisa VLOOKUP(), kakaretso ea lintlha tsa ho batla ha ea lokela ho feta litlhaku tse 255 kapa u tla fumana #VALUE! Kahoo haeba data ea hau e na le litlhaku tse ngata, INDEX() le MATCH() ke khetho e ntle ka ho fetisisa.
2. Lebelo le phahameng la ho sebetsa. Haeba litafole tsa hau li batla li le nyane, ha ho na monyetla oa hore u bone phapang. Empa, haeba tafole e na le mela e makholo kapa likete, 'me, ka hona, ho na le liforomo tse makholo le likete, INDEX () le MATCH () li tla sebetsana ka katleho kapele ho feta VLOOKUP (). 'Nete ke hore Excel e tla sebetsana feela le litšiea tse boletsoeng ka foromo, ho e-na le ho sebetsana le tafole eohle. 

Tšusumetso ea ts'ebetso ea VLOOKUP() e tla bonahala haholo haeba leqephe la hau le na le liforomo tse ngata joalo ka VLOOKUP() le SUM(). Litlhahlobo tse arohaneng tsa mesebetsi ea VLOOKUP() lia hlokahala ho hlalosa boleng bo bong le bo bong ka mokoloko. Kahoo Excel e tlameha ho sebetsana le tlhaiso-leseling e ngata, 'me sena se liehisa mosebetsi haholo.

Mehlala ea Foromo 

Re se re bone molemo oa mesebetsi ena, kahoo re ka fetela karolong e thahasellisang ka ho fetisisa: ts'ebeliso ea tsebo ka ts'ebetso.

Foromo ea ho batla ho tloha ho le letona ho ea ho le letšehali

Joalokaha ho se ho boletsoe, VLOOKUP e ke ke ea etsa mokhoa ona oa ho batla. Kahoo, haeba boleng bo lakatsehang bo se kholomong e ka ho le letšehali, VLOOKUP() e ke ke ea hlahisa sephetho. Mesebetsi ea INDEX () le MATCH () e na le mekhoa e mengata, 'me sebaka sa litekanyetso ha se na karolo e kholo ea hore se sebetse.

Mohlala, re tla eketsa kholomo ea boemo ka lehlakoreng le letšehali la tafole ea rona mme re leke ho fumana hore na motse-moholo oa Naha ea Rona o boemong bofe ho ea ka baahi.

Seleng ea G1, re ngola boleng bo tla fumanoa, ebe re sebelisa foromo e latelang ho batla mofuteng oa C1: C10 le ho khutlisa boleng bo lumellanang le A2:A10:

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

Potlakisa. Haeba u rera ho sebelisa foromo ena bakeng sa lisele tse ngata, etsa bonnete ba hore u lokisa mekhahlelo u sebelisa aterese e felletseng (mohlala, $A$2: $A$10 le $C$2: 4C$10).

INDEX HO EKETSEHILENG E HLAHALA HO EKETSEHILENG  ho batla ka mela le mela

Mehlaleng e ka holimo, re sebelisitse mesebetsi ena e le sebaka sa VLOOKUP() ho khutlisa boleng ho tsoa methating e boletsoeng esale pele. Empa ho thoe'ng haeba u hloka ho etsa lipatlisiso tsa matrix kapa tse peli?

E utloahala e rarahane, empa mokhoa oa lipalo o tšoana le o tloaelehileng INDEX() MATCH() formula, e nang le phapang e le 'ngoe feela: MATCH() foromo e tlameha ho sebelisoa habeli. Lekhetlo la pele la ho fumana nomoro ea mola, le lekhetlo la bobeli ho fumana nomoro ea kholomo:

= TLHAKISO(sehlopha, MATCH(boleng ba patlo e otlolohileng, kholomo ea ho batla, 0), MATCH(boleng bo tšekaletseng, mothalo oa ho batla, 0))

Ha re shebeng tafole e ka tlase mme re leke ho etsa foromo INDEX() EXPRESS() TLHALOSO() molemong oa ho bonts'a palo ea batho naheng e itseng bakeng sa selemo se khethiloeng.

Naha eo ho shebiloeng ho eona e ka seleng ea G1 (ho sheba ka holimo) 'me selemo se lebeletsoeng se seleng ea G2 (ho sheba ka holimo). Foromo e tla shebahala tjena:

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

Mokhoa ona o sebetsa joang

Joalo ka liforomo tse ling tse rarahaneng, li bonolo ho li utloisisa ka ho li arola ka li-equations ka bomong. Ebe u ka utloisisa hore na mosebetsi o mong le o mong o etsa eng:

• MATCH(G1,A2:A11,0) - e sheba boleng (G1) sebakeng sa A2: A11 mme e bonts'a palo ea boleng bona, molemong oa rona ke 2;
• BATLA(G2,B1:D1,0) - e sheba boleng (G2) sebakeng sa B1:D1. Tabeng ena, sephetho e bile 3.

Linomoro tsa mola le kholomo li romelloa boleng bo tsamaellanang ho INDEX() formula:

=INELA(B2:D11,2,3)

Ka lebaka leo, re na le boleng bo ka har'a sele mateanong a mela e 2 le likholomo tse 3 mokolokong oa B2:D11. 'Me foromo e bontša boleng bo lakatsehang, bo ka seleng ea D3.

Batla ka maemo a mangata ka INDEX le MATCH

Haeba u balile tataiso ea rona ho VLOOKUP(), mohlomong u lekile mekhoa e mengata ea ho batla. Empa mokhoa ona oa ho batla o na le moeli o le mong oa bohlokoa - tlhokahalo ea ho eketsa kholomo e thusang.

Empa litaba tse monate ke hore Ka INDEX() le MATCH() u ka batla maemo a mangata ntle le ho hlophisa kapa ho fetola leqephe la mosebetsi.

Mona ke mokhoa o akaretsang oa ho batla maemo a mangata bakeng sa INDEX() MATCH():

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

Molaetsa: foromo ena e tlameha ho sebelisoa hammoho le sekhechana sa keyboard CTRL+SHIFT+ENTER.

A re re u hloka ho fumana boleng boo u bo batlang ho latela maemo a 2: moreki и Sehlahisoa.

Sena se hloka foromo e latelang:

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

Ka mokhoa ona, C2:C10 ke sebaka seo patlisiso e tla etsahala ho sona, F1 - boemo bona, A2:A10 ke sebaka sa ho bapisa boemo, F2 - boemo 2, V2:V10 - sebaka sa ho bapisa maemo a 2.

U se ke ua lebala ho hatella motsoako qetellong ea mosebetsi ka foromo CTRL+SHIFT+ENTER - Excel e tla koala foromo ka bo eona ka li-curly braces, joalo ka ha ho bonts'itsoe mohlaleng:

Haeba u sa batle ho sebelisa mokhoa o hlophisitsoeng bakeng sa mosebetsi oa hau, joale eketsa INDEX() e 'ngoe ho foromo ebe o tobetsa ENTER, e tla shebahala joalo ka mohlala:

Kamoo liforomo tsena li sebetsang kateng

Foromo ena e sebetsa ka tsela e ts'oanang le e tloaelehileng INDEX() MATCH() formula. Ho batla maemo a mangata, o theha maemo a mangata a Bohata le 'Nete a emelang maemo a nepahetseng le a fosahetseng a motho ka mong. 'Me joale maemo ana a sebetsa ho likarolo tsohle tse lumellanang tsa sehlopha. Foromo e fetolela likhang tsa Bohata le 'Nete hore e be 0 le 1, ka ho latellana,' me e hlahise lethathamo moo 1 e leng boleng bo tšoanang bo fumanoeng khoeleng. MATCH() e tla fumana boleng ba pele bo lumellanang le 1 ebe e e fetisetsa ho INDEX() foromo. 'Me eona, ka lehlakoreng le leng, e tla khutlisetsa boleng bo seng bo lakatseha moleng o boletsoeng ho tloha kholomong e lakatsehang.

Foromo e se nang lethathamo e itšetlehile ka bokhoni ba INDEX() ho e sebetsana ka bobona. INDEX () ea bobeli ka foromo e tsamaisana le bohata (0), kahoo e fetisa lethathamo lohle le litekanyetso tseo ho MATCH() foromo. 

Ena ke tlhaloso e telele haholo ea moelelo oa mokhoa ona. Ho fumana lintlha tse ling bala sehlooho se reng “INDEX MATCH e nang le maemo a mangata".

AVERAGE, MAX le MIN ho INDEX le MATCH

Excel e na le mesebetsi ea eona e khethehileng ea ho fumana li-average, maximums, le bonyane. Empa ho thoe'ng haeba u batla ho fumana data ho tsoa seleng e amanang le litekanyetso tseo? Boemong bona AVERAGE, MAX le MIN li tlameha ho sebelisoa mmoho le INDEX le MATCH.

INDEX MATCH le MAX

Ho fumana boleng bo boholo kholomong ea D le ho e bonts'a kholomong ea C, sebelisa foromo: 

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

INDEX MATCH le MIN

Ho fumana boleng bo bonyenyane ka ho fetisisa kholomong ea D le ho e bontša kholomong ea C, sebelisa foromo e latelang:

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

BATLA INDEX le SERPENT

Ho fumana boleng ba karolelano kholomong ea D le ho bonts'a boleng bona ho C:

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

Ho latela hore na data ea hau e ngotsoe joang, khang ea boraro ho MATCH() ke 1, 0, kapa -1:

• haeba litšiea li hlophisoa ka tatellano e nyolohang, beha 1 (joale foromo e tla bala palo e kholo, e ka tlaase ho kapa e lekanang le boleng bo tloaelehileng);
• haeba mofuta o theoha, joale -1 (formula e tla hlahisa boleng bo fokolang bo boholo ho feta kapa bo lekanang le karolelano);
• haeba lethathamo la ho sheba le na le boleng bo lekanang hantle le karolelano, joale e behe ho 0. 

 Mohlala oa rona, palo ea batho e hlophisoa ka tatellano e theohang, kahoo re beha -1. 'Me phello ke Tokyo, kaha palo ea baahi (13,189) ke eona e haufi haholo le boleng ba karolelano (000).

VLOOKUP() e ka boela ea etsa lipalo tse joalo, empa feela e le mokhoa oa ho fapanyetsana: VLOOKUP e nang le AVERAGE, MIN le MAX..

INDEX MATCH le ESND/IFERROR

Mohlomong u se u hlokometse hore haeba foromo e sitoa ho fumana boleng bo lakatsehang, e etsa phoso # N / A. U ka nkela molaetsa o tloaelehileng oa phoso sebaka ka ho hong ho rutang haholoanyane. Ka mohlala, beha khang ka foromo Lilemong tsa bo-XNUMX:

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

Ka foromo ena, haeba u kenya data e seng tafoleng, foromo e tla u fa molaetsa o boletsoeng.

Haeba u batla ho tšoara liphoso tsohle, ka nako eo ntle bakeng sa Lilemong tsa bo-XNUMX e ka sebelisoa IFEREKE:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), “Ho na le phoso e etsahetseng!”)

Empa hopola hore liphoso tsa masking ka tsela ena ha se mohopolo o motle, hobane liphoso tse tloaelehileng li tlaleha litlolo ka foromo.

Re tšepa hore u fumane tataiso ea rona ea ho sebelisa tšebetso ea INDEX MATCH() e thusa.