Excel

Kako uporabljati INDEX in MATCH

How Use Index Match

INDEX in MATCH sta najbolj priljubljeno orodje v Excelu za izvajanje naprednejših iskanj. To je zato, ker sta INDEX in MATCH neverjetno prilagodljiva-lahko izvajate vodoravne in navpične poizvedbe, dvosmerne poizvedbe, leve poizvedbe, iskanja glede na velike in male črke ter celo poizvedbe na podlagi več meril. Če želite izboljšati svoje znanje Excela, bi morali biti na vašem seznamu INDEX in MATCH.



Ta članek preprosto pove, kako skupaj uporabiti INDEX in MATCH za iskanje. Potreben je postopek po korakih, najprej razložite INDEX, nato MATCH, nato pa vam pokažemo, kako združiti obe funkciji skupaj, da ustvarite dinamično dvosmerno iskanje. Spodaj na strani so še naprednejši primeri.

INDEX funkcija | Funkcija MATCH | INDEX in MATCH | Dvosmerno iskanje | Iskanje levo | Velike in male črke | Najbližje ujemanje | Več meril | Več primerov





Funkcija INDEX

Funkcija INDEX v Excelu je fantastično prilagodljiva in zmogljiva, našli pa jo boste v ogromnem številu Excelovih formul, zlasti v naprednih formulah. Toda kaj dejansko počne INDEX? Na kratko INDEX pridobi vrednost na določeni lokaciji v območju. Na primer, recimo, da imate tabelo planetov v našem sončnem sistemu (glejte spodaj) in želite dobiti ime 4. planeta, Mars, s formulo. INDEX lahko uporabite tako:

 
= INDEX (B3:B11,4)

S pomočjo INDEX -a dobite ime 4. planeta
INDEX vrne vrednost v 4. vrstico obsega.



Video: Kako iskati stvari z INDEX -om

Kaj pa, če želite doseči premer Marsa z INDEX -om? V tem primeru lahko ponudimo številko vrstice in številko stolpca ter zagotovimo večji razpon. Spodnja formula INDEX uporablja celoten obseg podatkov v B3: D11 s številko vrstice 4 in številko stolpca 2:

 
= INDEX (B3:D11,4,2)

S pomočjo INDEX dobite premer 4. planeta
INDEX pridobi vrednost v vrstici 4, stolpcu 2.

Če povzamemo, INDEX dobi vrednost na določeni lokaciji v obsegu celic na podlagi številskega položaja. Ko je obseg enodimenzionalen, morate vnesti samo številko vrstice. Ko je obseg dvodimenzionalen, morate vnesti številko vrstice in stolpca.

Na tej točki morda razmišljate: 'Kaj pa? Kako pogosto dejansko poznate položaj nečesa v preglednici? '

Popolnoma prav. Potrebujemo način, da ugotovimo položaj stvari, ki jih iščemo.

Vnesite funkcijo MATCH.

Funkcija MATCH

Funkcija MATCH je zasnovana za en namen: poiščite položaj predmeta v obsegu. Na primer, lahko uporabimo MATCH, da dobimo položaj besede 'breskev' na tem seznamu sadja, kot je ta:

 
= MATCH ('peach',B3:B9,0)

Z MATCH poiščite položaj v navpičnem območju
MATCH vrne 3, saj je 'Peach' tretji element. MATCH ne razlikuje velikih in malih črk.

MATCH ne zanima, ali je obseg vodoravni ali navpični, kot lahko vidite spodaj:

 
= MATCH ('peach',C4:I4,0)

S pomočjo MATCH poiščite položaj v vodoravnem območju
Isti rezultat z vodoravnim razponom, MATCH vrne 3.

Video: Kako uporabiti MATCH za natančna ujemanja

Pomembno: Zadnji argument v funkciji MATCH je vrsta ujemanja. Vrsta ujemanja je pomembna in nadzoruje, ali je ujemanje natančno ali približno. V mnogih primerih boste želeli uporabiti nič (0) za vsiljevanje natančnega ujemanja. Privzeta vrsta ujemanja je 1, kar pomeni približno ujemanje, zato je pomembno, da navedete vrednost. Glej MATCH stran za več podrobnosti.

INDEX in MATCH skupaj

Zdaj, ko smo obravnavali osnove INDEX in MATCH, kako združimo obe funkciji v eno samo formulo? Upoštevajte spodnje podatke, tabelo, ki prikazuje seznam prodajalcev in mesečne prodajne številke za tri mesece: januar, februar in marec.

Prodaja po prodajalcih po mesecih

Recimo, da želimo napisati formulo, ki za določenega prodajalca vrne prodajno številko za februar. Iz zgornje razprave vemo, da lahko INDEX -u podarimo številko vrstice in stolpca za pridobitev vrednosti. Na primer, za vrnitev februarske prodajne številke za Frantz podamo obseg C3: E11 z vrstico 5 in stolpcem 2:

 
= INDEX (C3:E11,5,2) // returns 94

Očitno pa ne želimo trdo kodirati številk. Namesto tega želimo a dinamično Poglej gor.

Kako bomo to storili? Seveda funkcija MATCH. MATCH bo odlično deloval pri iskanju delovnih mest, ki jih potrebujemo. Če delamo korak za korakom, pustimo stolpec trdo kodiran kot 2 in naredimo številko vrstice dinamično. Tukaj je spremenjena formula, pri kateri je funkcija MATCH vstavljena v INDEX namesto 5:

 
= INDEX (C3:E11, MATCH ('Frantz',B3:B11,0),2)

Če gremo še korak dlje, bomo uporabili vrednost iz H2 v MATCH:

 
= INDEX (C3:E11, MATCH (H2,B3:B11,0),2)

INDEX in MATCH poiščite februarsko prodajo za katero koli ime
MATCH najde 'Frantz' in vrne 5 v INDEX za vrstico.

Povzeti:

  1. INDEX potrebuje številčne položaje.
  2. MATCH najde te položaje.
  3. MATCH je ugnezden znotraj INDEX -a.

Zdaj pa se lotimo številke stolpca.

Dvosmerno iskanje z INDEX in MATCH

Zgoraj smo uporabili funkcijo MATCH za dinamično iskanje številke vrstice, vendar smo trdo kodirali številko stolpca. Kako lahko formulo naredimo popolnoma dinamično, da lahko v vsakem mesecu vrnemo prodajo za katerega koli prodajalca? Trik je v tem, da dvakrat uporabite MATCH - enkrat za položaj vrstice in enkrat za položaj stolpca.

Iz zgornjih primerov vemo, da MATCH dobro deluje tako z vodoravnimi kot navpičnimi matrikami. To pomeni, da lahko z MATCH -om zlahka najdemo položaj določenega meseca. Ta formula na primer vrne položaj marca, ki je 3:

 
= MATCH ('Mar',C2:E2,0) // returns 3

Seveda pa ne želimo trde kode kaj vrednosti, zato posodobimo delovni list, da omogočimo vnos imena meseca, in uporabimo MATCH, da poiščemo številko stolpca, ki jo potrebujemo. Spodnji zaslon prikazuje rezultat:

Dinamično iskanje z INDEX in MATCH
Popolnoma dinamično, dvosmerno iskanje z INDEX in MATCH.

 
= INDEX (C3:E11, MATCH (H2,B3:B11,0), MATCH (H3,C2:E2,0))

Prva formula MATCH vrne 5 v INDEX kot številko vrstice, druga formula MATCH vrne 3 v INDEX kot številko stolpca. Ko se MATCH zažene, se formula poenostavi na:

 
= INDEX (C3:E11,5,3)

in INDEX pravilno vrne 10.525 USD, prodajno številko za Frantz marca.

Opomba: lahko uporabite Validacija podatkov za ustvarjanje spustnih menijev za izbiro prodajalca in meseca.

Video: Kako narediti dvosmerno iskanje z INDEX in MATCH

Video: Kako odpraviti napake v formuli s F9 (za ogled povratnih vrednosti MATCH)

Iskanje levo

Ena ključnih prednosti INDEX -a in MATCH -a pred funkcijo VLOOKUP je možnost izvajanja 'levega iskanja'. Preprosto povedano, to pomeni samo iskanje, kjer je ID stolpec v prav vrednosti, ki jih želite pridobiti, kot je prikazano v spodnjem primeru:

Levo iskanje z INDEX in MATCH

Podrobno razlago preberite tukaj .

Iskanje glede na velike in male črke

Funkcija MATCH sama po sebi ne razlikuje velikih in malih črk. Vendar pa uporabljate TOČNA funkcija z INDEX in MATCH za iskanje, ki upošteva velike in male črke, kot je prikazano spodaj:

Iskanje z velikimi in malimi črkami z INDEX in MATCH

Podrobno razlago preberite tukaj .

Opomba: to je formula matrike in ga je treba vnesti s tipko control + shift + enter, razen v Excel 365 .

Najbližje ujemanje

Drug primer, ki kaže na prilagodljivost INDEX in MATCH, je problem iskanja najbližje ujemanje . V spodnjem primeru uporabljamo Funkcija MIN skupaj z Funkcija ABS do ustvarite iskalno vrednost in iskalno matriko notri funkcijo MATCH. V bistvu uporabljamo MATCH za iskanje najmanjše razlike. Nato uporabimo INDEX za pridobivanje povezanega potovanja iz stolpca B.

Poiščite najbližje ujemanje z INDEX in MATCH

Podrobno razlago preberite tukaj .

Opomba: to je formula matrike in ga je treba vnesti s tipko control + shift + enter, razen v Excel 365 .

Iskanje po več merilih

Ena najtežjih težav v Excelu je iskanje po več merilih. Z drugimi besedami, iskanje, ki se ujema z več stolpci hkrati. V spodnjem primeru uporabljamo INDEX in MATCH ter logična logika za ujemanje v treh stolpcih: postavka, barva in velikost:

INDEX in MATCH z več merili

Podrobno razlago preberite tukaj .

Opomba: to je formula matrike in ga je treba vnesti s tipko control + shift + enter, razen v Excel 365 .

več stavkov if v eni celici

Več primerov INDEX + MATCH

Tu je še nekaj osnovnih primerov INDEX in MATCH v akciji, vsak s podrobno razlago:

Avtor Dave Bruns Priloge mapa indeks exceljet in match.xlsx


^