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)
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)
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)
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)
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.
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)
MATCH najde 'Frantz' in vrne 5 v INDEX za vrstico.
Povzeti:
- INDEX potrebuje številčne položaje.
- MATCH najde te položaje.
- 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:
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:
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:
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.
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:
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:
- Osnovni INDEX in MATCH natančno (vsebuje zgodbo igrač)
- Osnovni indeks in ujemanje sta približna (ocene)
- Dvosmerno iskanje z INDEX in MATCH (približno ujemanje)