V tem članku se bomo naučili, kako poiskati vrednosti v 2d tabeli s funkcijo INDEX-MATCH-MATCH v Excelu.
Scenarij:
Predpostavimo, da morate iz tabele, ki ima na stotine stolpcev, opraviti več iskanj. V takih primerih bo uporaba različnih formul za vsako iskanje vzela preveč časa. Kaj pa ustvarjanje formule za dinamično iskanje, ki jo lahko poiščete po podani glavi. Da, to lahko storimo. Ta formula se imenuje INDEX MATCH MATCH formula ali recimo 2d iskalna formula.
Kako rešiti problem?
Da bi formula najprej razumela, moramo nekoliko pregledati naslednje funkcije
- INDEX funkcija
- Funkcija MATCH
Funkcija INDEX vrne vrednost pri danem indeksu v matriki.
Funkcija MATCH vrne indeks prvega prikaza vrednosti v matriki (enorazsežna matrika).
Zdaj bomo naredili formulo z uporabo zgornjih funkcij. Funkcija ujemanja bo vrnila indeks iskalne vrednosti1 v polju glave vrstice. Druga funkcija MATCH pa vrne indeks iskalne vrednosti2 v polju glave stolpca. Številke indeksov bodo zdaj vnesene v funkcijo INDEX, da dobimo vrednosti pod iskalno vrednostjo iz podatkov 2D tabele.
Splošna formula:
= INDEX (podatki, MATCH (iskalna_vrednost1, vrstice_glavja, 0, MATCH (iskalna_vrednost2, stolpci_glavja, 0)))
Podatki: niz vrednosti znotraj tabele brez glav
lookup_value1 : vrednost za iskanje v naslovu row_header.
row_headers : Matrika indeksnih vrstic 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, glava vrstice, glave tabele in stolpca pa kot poimenovana območja.
Kot lahko vidite na zgornjem posnetku, smo dobili oceno, ki jo je pridobil učenec Gary v Zadeva Družboslovja kot 36.
Dokazuje, da formula dobro deluje, za dvome pa 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 za besedilne vrednosti ne more uporabiti
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, vrstica_glavja, podatki in glava stolpca pa kot poimenovani obsegi, kot je omenjeno v zgornjem posnetku.
Kot lahko vidite na zgornjem posnetku, imamo ceno, pridobljeno po višini (34) & Premer (21) kot 53.10. Dokazuje, da formula dobro deluje, in za dvome glejte 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 ste razumeli, kako uporabljati iskanje v 2D tabeli s funkcijo INDEX & MATCH v Excelu. Raziščite več člankov v iskalni vrednosti Excel tukaj. Prosimo, da svoja vprašanja navedete spodaj v polju za komentarje. Zagotovo vam bomo pomagali.
Za iskanje vrednosti uporabite INDEX in MATCH : Funkcija INDEX & MATCH za iskanje vrednosti po potrebi.
Obseg SUM z INDEX v Excelu : S funkcijo INDEX poiščite SUM vrednosti, kot je potrebno.
Kako uporabljati funkcijo SUM v Excelu : Poiščite SUM številk s funkcijo SUM, razloženo s primerom.
Kako uporabljati funkcijo INDEX v Excelu : Poiščite INDEX matrike s funkcijo INDEX, razloženo s primerom.
Kako uporabljati funkcijo MATCH v Excelu : Poiščite MATCH v matriki z uporabo vrednosti INDEX znotraj funkcije MATCH, razložene s primerom.
Kako uporabljati funkcijo LOOKUP v Excelu : Poiščite iskalno vrednost v matriki s funkcijo LOOKUP, razloženo s primerom.
Kako uporabljati funkcijo VLOOKUP v Excelu : Poiščite iskalno vrednost v matriki s funkcijo VLOOKUP, razloženo s primerom.
Kako uporabljati funkcijo HLOOKUP v Excelu : Poiščite iskalno vrednost v matriki s funkcijo HLOOKUP, razloženo s primerom.
Priljubljeni članki
50 Bližnjica v Excelu za večjo produktivnost
Uredite spustni seznam
Absolutna referenca v Excelu
Če s pogojnim oblikovanjem
Če z nadomestnimi znaki
Vpogled po datumu
Pridružite se imenu in priimku v Excelu
