Kako uporabljati funkciji INDEX in MATCH v Excelu

2024 Avtor: Abigail Brown | [email protected]. Nazadnje spremenjeno: 2023-12-17 07:00

Kaj morate vedeti

• Funkcijo INDEX lahko uporabljate samostojno, vendar z ugnezdenjem funkcije MATCH znotraj nje ustvarite napredno iskanje.
• Ta ugnezdena funkcija je bolj prilagodljiva kot VLOOKUP in lahko hitreje daje rezultate.

Ta članek pojasnjuje, kako skupaj uporabljati funkciji INDEX in MATCH v vseh različicah Excela, vključno z Excelom 2019 in Microsoft 365.

Kaj sta funkciji INDEX in MATCH?

INDEX in MATCH sta Excelovi funkciji za iskanje. Čeprav gre za dve popolnoma ločeni funkciji, ki ju je mogoče uporabljati samostojno, ju je mogoče tudi združiti za ustvarjanje naprednih formul.

Funkcija INDEX vrne vrednost ali sklic na vrednost znotraj določenega izbora. Uporabite ga lahko na primer za iskanje vrednosti v drugi vrstici nabora podatkov ali v peti vrstici in tretjem stolpcu.

Medtem ko bi lahko INDEX uporabili samostojno, je z ugnezdenjem MATCH v formuli nekoliko bolj uporaben. Funkcija MATCH išče določen element v obsegu celic in nato vrne relativni položaj elementa v obsegu. Lahko bi ga na primer uporabili za določitev, da je določeno ime tretji element na seznamu imen.

INDEX in MATCH Sintaksa in argumenti

Tako morata biti obe funkciji zapisani, da ju Excel razume:

=INDEX(matrika, številka_vrstice, [števila_stolpca])

• array je obseg celic, ki jih bo uporabljala formula. Lahko je ena ali več vrstic in stolpcev, na primer A1:D5. Obvezno je.
• row_num je vrstica v matriki, iz katere naj se vrne vrednost, na primer 2 ali 18. Zahtevana je, razen če je prisotna column_num.
• column_num je stolpec v matriki, iz katere je treba vrniti vrednost, na primer 1 ali 9. Ni obvezno.

=MATCH(lookup_value, lookup_array, [match_type])

• lookup_value je vrednost, ki jo želite ujemati v lookup_array. Lahko je številka, besedilo ali logična vrednost, ki je vnesena ročno ali na katero se sklicujete prek sklica na celico. To je obvezno.
• lookup_array je obseg celic, ki jih je treba pregledati. Lahko je ena vrstica ali en stolpec, na primer A2:D2 ali G1:G45. To je obvezno.
• match_type je lahko -1, 0 ali 1. Določa, kako se lookup_value ujema z vrednostmi v lookup_array (glejte spodaj). 1 je privzeta vrednost, če je ta argument izpuščen.

Katero vrsto ujemanja uporabiti
Vrsta ujemanja	Kaj počne	Pravilo	Primer
1	Poišče največjo vrednost, ki je manjša ali enaka lookup_value.	Vrednosti lookup_array morajo biti postavljene v naraščajočem vrstnem redu (npr. -2, -1, 0, 1, 2; ali A-Z; ali FALSE, TRUE.	lookup_value je 25, vendar manjka v lookup_array, zato se namesto tega vrne položaj naslednjega najmanjšega števila, na primer 22.
0	Najde prvo vrednost, ki je popolnoma enaka lookup_value.	Vrednosti lookup_array so lahko v poljubnem vrstnem redu.	lookup_value je 25, zato vrne položaj 25.
-1	Poišče najmanjšo vrednost, ki je večja ali enaka lookup_value.	Vrednosti lookup_array morajo biti postavljene v padajočem vrstnem redu (npr. 2, 1, 0, -1, -2).	lookup_value je 25, vendar manjka v lookup_array, zato je namesto tega vrnjen položaj naslednjega največjega števila, na primer 34.

Uporabite 1 ali -1 v primerih, ko morate zagnati približno iskanje po lestvici, na primer, ko imate opravka s številkami in ko so približki v redu. Vendar ne pozabite, da če ne podate match_type, bo privzeta vrednost 1, kar lahko popači rezultate, če resnično želite natančno ujemanje.

Primeri formul INDEX in MATCH

Preden pogledamo, kako združiti INDEX in MATCH v eno formulo, moramo razumeti, kako ti funkciji delujeta sami.

INDEX Primeri

=INDEX(A1:B2, 2, 2)
=INDEX(A1:B1, 1)
=INDEX(2:2, 1)=INDEKS(B1:B2, 1)





V tem prvem primeru so štiri formule INDEKS, ki jih lahko uporabimo za pridobivanje različnih vrednosti:

=INDEX(A1:B2, 2, 2) pregleda A1:B2, da bi našel vrednost v drugem stolpcu in drugi vrstici, ki je Stacy.
=INDEX(A1:B1, 1) poišče A1:B1, da najde vrednost v prvem stolpcu, ki je Jon.
=INDEX(2:2, 1) pregleda vse v drugi vrstici, da poišče vrednost v prvem stolpcu, ki je Tim.
=INDEX(B1:B2, 1) poišče B1:B2, da poišče vrednost v prvi vrstici, ki je Amy.

Primeri UJEMANJA
=MATCH("Stacy", A2:D2, 0)
=MATCH(14, D1:D2)
=MATCH(14, D1:D2, -1)=MATCH(13, A1:D1, 0)





Tu so štirje preprosti primeri funkcije MATCH:

=MATCH("Stacy", A2:D2, 0) išče Stacy v obsegu A2:D2 in kot rezultat vrne 3.
=MATCH(14, D1:D2) išče 14 v obsegu D1:D2, a ker ga ni mogoče najti v tabeli, MATCH najde naslednjo največjo vrednost to je manjše ali enako 14, kar je v tem primeru 13, ki je na položaju 1 lookup_array.
=MATCH(14, D1:D2, -1) je enaka formuli nad njim, a ker matrika ni v padajočem vrstnem redu, kot zahteva -1, dobimo napako.
=MATCH(13, A1:D1, 0) išče 13 v prvi vrstici lista, ki vrne 4, ker je to četrti element v tej matriki.


Primeri INDEX-MATCH
Tu sta dva primera, kjer lahko združimo INDEX in MATCH v eni formuli:
Poišči sklic na celico v tabeli
=INDEX(B2:B5, MATCH(F1, A2:A5))






Ta primer ugnezdi formulo MATCH znotraj formule INDEX. Cilj je prepoznati barvo artikla s pomočjo številke artikla.
Če pogledate sliko, lahko v vrsticah »Ločeno« vidite, kako bi bile formule zapisane same, a ker jih ugnezdimo, se dogaja tole:

MATCH(F1, A2:A5) išče vrednost F1 (8795) v naboru podatkov A2:A5. Če odštevamo stolpec, lahko vidimo, da je 2, torej je to pravkar ugotovila funkcija MATCH.
Matrika INDEX je B2:B5, ker na koncu iščemo vrednost v tem stolpcu.
Funkcijo INDEX bi zdaj lahko prepisali takole, saj je 2 tisto, kar je našel MATCH: INDEX(B2:B5, 2, [številka_stolpca]).
Ker column_num ni obvezen, ga lahko odstranimo in ostane tole: INDEX(B2:B5, 2).
Torej, to je kot običajna formula INDEX, kjer najdemo vrednost drugega elementa v B2:B5, ki je rdeče barve.

Iskanje po naslovih vrstic in stolpcev
=INDEX(B2:E13, MATCH(G1, A2:A13, 0), MATCH(G2, B1:E1, 0))






V tem primeru MATCH in INDEX izvajamo dvosmerno iskanje. Ideja je videti, koliko denarja smo maja zaslužili z zelenimi predmeti. To je resnično podobno zgornjemu primeru, vendar je dodatna formula MATCH ugnezdena v INDEX.

MATCH(G1, A2:A13, 0) je prvi rešen element v tej formuli. Išče G1 (beseda "maj") v A2:A13, da dobi določeno vrednost. Tukaj ga ne vidimo, vendar je 5.
MATCH(G2, B1:E1, 0) je druga formula MATCH in je zelo podobna prvi, vendar namesto tega išče G2 (beseda "Green") v naslovih stolpcev pri B1:E1. Ta se razreši na 3.
Sedaj lahko prepišemo formulo INDEKS takole, da vizualiziramo, kaj se dogaja: =INDEX(B2:E13, 5, 3). To išče v celotni tabeli, B2:E13, za peto vrstico in tretji stolpec, ki vrne 180 $.


Pravila UJEMANJA in INDEKS
Pri pisanju formul s temi funkcijami morate upoštevati več stvari:

MATCH ne razlikuje med velikimi in malimi črkami, zato se pri ujemanju besedilnih vrednosti velike in male črke obravnavajo enako.
MATCH vrne N/V iz več razlogov: če je match_type 0 in lookup_value ni najden, če je match_type enak -1 in lookup_array ni v padajočem vrstnem redu, če je match_type 1 in lookup_array ni v naraščajočem vrstnem redu in če lookup_array ni ena vrstica ali stolpec.
V argumentu lookup_value lahko uporabite nadomestni znak, če je match_type enak 0 in je lookup_value besedilni niz. Vprašaj se ujema s katerim koli posameznim znakom, zvezdica pa s katerim koli zaporedjem znakov (npr.npr., =MATCH("Jo", 1:1, 0)). Če želite uporabiti MATCH za iskanje dejanskega vprašaja ali zvezdice, najprej vnesite ~.
INDEX vrne REF! če row_num in column_num ne kažeta na celico v matriki.


Sorodne Excelove funkcije
Funkcija MATCH je podobna funkciji LOOKUP, vendar MATCH vrne položaj elementa namesto samega elementa.
VLOOKUP je še ena funkcija iskanja, ki jo lahko uporabite v Excelu, vendar za razliko od MATCH, ki zahteva INDEX za napredna iskanja, formule VLOOKUP potrebujejo samo to eno funkcijo.