V tem članku se bomo naučili, kako uporabiti funkcijo INDEX in MATCH za iskanje vrednosti v Excelu.
INDEX & MATCH funkcija za zamenjavo funkcije vlookup
Običajno uporabljamo funkcijo VLOOKUP za iskanje vrednosti, ki pozna indeks vrstice in številko stolpca. Za funkcijo VLOOKUP moramo imeti merila za ujemanje števila stolpcev in vrstic ter najpomembnejšo vrednost, ki jo je treba ugotoviti, na desni strani ujemajoče se vrednosti. Toda podatki tega ne podpirajo vedno. Velikokrat moramo samo poiskati vrednosti, ne da bi poznali indeks vrstice ali stolpca, samo da se vrednosti ujemajo. Na primer: iskanje plače zaposlenega z osebnim dokumentom. V tem bomo iskali ID zaposlenega za indeks vrstice in Plačo za indeks stolpca. Naučimo se, kako lahko to iskanje izvedemo z uporabo INDEX & MATCH.
Kako rešiti problem?
Da bi formula najprej razumela, moramo nekoliko pregledati naslednje funkcije
- INDEX funkcija
- Funkcija MATCH
Zdaj bomo naredili formulo z uporabo zgornjih funkcij. Funkcija ujemanja bo vrnila indeks iskalne vrednosti1 v polju glave vrstice. Druga funkcija MATCH bo vrnila indeks iskalne vrednosti 2 iz polja glave stolpca. Številke indeksov bodo zdaj vnesene v funkcijo INDEX, da dobimo vrednosti pod iskalno vrednostjo iz podatkov tabele.
Splošna formula:
| = INDEX (podatki, MATCH (iskalna_vrednost1, vrstice_glavja, 0, MATCH (iskalna_vrednost2, stolpci_glavja, 0))) |
podatkov : niz vrednosti znotraj tabele brez glav
lookup_value1 : vrednost za iskanje v naslovu row_header.
row_headers : Matrika vrstice indeksa za iskanje.
lookup_value1 : vrednost za iskanje v glavi stolpca.
stolpci_glavja : stolpec Indeksni niz za iskanje.
Primer:
Zgornje izjave je lahko težko razumeti. Zato razumejmo to z uporabo formule v primeru
Tu imamo seznam rezultatov, ki so jih učenci pridobili s svojim seznamom predmetov. Moramo najti oceno za določenega študenta (Gary) in predmet (družbene študije), kot je prikazano na spodnjem posnetku.
Vrednost študenta1 se mora ujemati z matriko Row_header in vrednost Subject2 se mora ujemati z matriko Column_header.
Uporabite formulo v celici J6:
| = INDEX (tabela, MATCH (J5, vrstica, 0, MATCH (J4, stolpec, 0))) |
Pojasnilo:
- Funkcija MATCH se ujema z vrednostjo Student v celici J4 z matriko glave vrstice in vrne njen položaj 3 kot številko.
- Funkcija MATCH se ujema z vrednostjo Subject v celici J5 z matriko glave stolpca in vrne njen položaj 4 kot številko.
- Funkcija INDEX vzame številko indeksa vrstice in stolpca ter poišče podatke v tabeli in vrne ujemajočo se vrednost.
- Argument tipa MATCH je pritrjen na 0. Ker bo formula izvlekla natančno ujemanje.
Tu so vrednosti formule podane kot sklice na celice, vrstice_glavja, tabele in glave stolpcev pa kot poimenovani obsegi.
Kot lahko vidite na zgornjem posnetku, smo dobili oceno, ki jo je pridobil učenec Gary v Zadeva Družboslovja kot 36. To dokazuje, da formula dobro deluje in za dvome si oglejte spodnje opombe za razumevanje.
Zdaj bomo kot številke uporabili približno ujemanje z glavami vrstic in stolpcev. Približno ujemanje sprejme samo številske vrednosti, saj se ne more uporabiti za besedilne vrednosti
Tu imamo ceno vrednosti glede na višino in širino izdelka. Najti moramo ceno za določeno višino (34) in širino (21), kot je prikazano na spodnjem posnetku.
Vrednost Height1 se mora ujemati z matriko Row_header, vrednost Width2 pa se mora ujemati z nizom Column_header.
Uporabite formulo v celici K6:
| = INDEX (podatki, MATCH (K4, Višina, 1, MATCH (K5, Širina, 1))) |
Pojasnilo:
- Funkcija MATCH ujema vrednost Height v celici K4 z matriko glave vrstice in vrne njen položaj 3 kot številko.
- Funkcija MATCH ujema vrednost Width v celici K5 z matriko glave stolpca in vrne njen položaj 2 kot številko.
- Funkcija INDEX vzame številko indeksa vrstice in stolpca ter poišče podatke v tabeli in vrne ujemajočo se vrednost.
- Argument tipa MATCH je pritrjen na 1. Ker bo formula izvlekla približno ujemanje.
Tu so vrednosti formule podane kot sklice na celice, glava vrstice, podatkov in glave stolpca pa kot poimenovana območja, kot je omenjeno v zgornjem posnetku.
Poiščete lahko tudi natančna ujemanja s funkcijami INDEX in MATCH v Excelu. Preberite več o tem, kako v Excelu izvedete iskanje velikih in malih črk s funkcijo INDEX & MATCH. Delna ujemanja lahko poiščete tudi z nadomestnimi znaki v Excelu.
Kot lahko vidite na zgornjem posnetku, smo dobili ceno, pridobljeno po višini (34) & Premer (21) kot 53.10. To dokazuje, da formula dobro deluje, in za dvome si oglejte spodnje opombe za boljše razumevanje.
Opombe:
- Funkcija vrne napako #NA, če je argument matrike iskanja funkciji MATCH 2D matrika, ki je polje glave podatkov …
- Funkcija se ujema z natančno vrednostjo, saj je argument vrste ujemanja s funkcijo MATCH 0.
- Iskalne vrednosti so lahko podane kot referenca celice ali pa neposredno uporabljeni navednik (") v formuli kot argumenti.
Upam, da je ta članek o tem, kako uporabljati funkcijo INDEX in MATCH za iskanje vrednosti v Excelu, razložljiv. Več člankov o izračunu vrednosti in sorodnih formulah Excel najdete tukaj. Če so vam bili naši blogi všeč, jih delite s prijatelji na Facebooku. Prav tako nas lahko spremljate na Twitterju in Facebooku. Radi bi slišali od vas, nam sporočite, kako lahko izboljšamo, dopolnimo ali inoviramo svoje delo in ga izboljšamo. Pišite nam na spletnem mestu e -pošte.
Za iskanje vrednosti uporabite INDEX in MATCH : Formula INDEX-MATCH se uporablja za dinamično in natančno iskanje vrednosti v dani tabeli. To je alternativa funkciji VLOOKUP in premaga pomanjkljivosti funkcije VLOOKUP.
Uporabite VLOOKUP iz dveh ali več iskalnih tabel : Za iskanje iz več tabel lahko uporabimo pristop IFERROR. Za iskanje iz več tabel napaka jemlje kot stikalo za naslednjo tabelo. Druga metoda je lahko pristop If.
Kako v Excelu poiskati velike in male črke : Excelova funkcija VLOOKUP ne razlikuje velikih in malih črk in bo vrnila prvo ujemajočo se vrednost s seznama. INDEX-MATCH ni izjema, vendar ga je mogoče spremeniti tako, da je občutljiv na velike in male črke. Poglejmo, kako…
Iskanje pogosto pojavljajočega se besedila z merili v Excelu : Iskanje se najpogosteje pojavi v besedilu v obsegu, ki ga uporabljamo INDEX-MATCH with MODE. Tukaj je metoda.
Priljubljeni članki:
Kako uporabljati funkcijo IF v Excelu : Stavek IF v Excelu preveri pogoj in vrne določeno vrednost, če je pogoj TRUE, ali vrne drugo posebno vrednost, če je FALSE.
Kako uporabljati funkcijo VLOOKUP v Excelu : To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih obsegov in listov.
Kako uporabljati funkcijo SUMIF v Excelu : To je še ena bistvena funkcija nadzorne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.
Kako uporabljati funkcijo COUNTIF v Excelu : Štejte vrednosti s pogoji s to neverjetno funkcijo. Za štetje določenih vrednosti vam ni treba filtrirati podatkov. Funkcija Countif je bistvena za pripravo vaše armaturne plošče.