Iskanje po več merilih z INDEX in MATCH
Pri obravnavi velike zbirke podatkov v Excelovi preglednici z več stolpci in napisi vrstic je vedno težko najti nekaj, kar ustreza več kriterijem. V tem primeru lahko uporabite formulo matrike s the INDEX in MATCH fdejanja.
Kako opraviti iskanje po več merilih?
Če želite izvedeti, izdelek da bele in srednje-velikosti s ceno $18 kot je prikazano na zgornji sliki, lahko uporabite logično logiko, da ustvarite matriko 1s in 0s za prikaz vrstic, ki izpolnjujejo merila. Funkcija MATCH bo nato našla položaj prve vrstice, ki izpolnjuje vsa merila. Po tem bo INDEX v isti vrstici našel ustrezen ID izdelka.
Splošna skladnja
=INDEX(return_range,MATCH(1,(criteria_value1=criteria_range1*criteria_value2=criteria_range2*(…),0))
√ Opomba: To je matrična formula, ki zahteva, da vnesete Ctrl + Shift + Vnesite.
- obseg_vrnitve: Obseg, iz katerega želite, da kombinirana formula vrne ID izdelka. Tu se nanaša na obseg ID -jev izdelka.
- vrednost_kriterijev: Merila, ki se uporabljajo za iskanje položaja ID -ja izdelka. Tu se nanaša na vrednosti v celicah H4, H5 in H6.
- obseg_kriterijev: Ustrezna območja, kjer je merila_vrednosti so navedeni. Tu se nanaša na barvo, velikost in cenovni razred.
- match_type 0: Prisili MATCH, da poišče prvo vrednost, ki je popolnoma enaka lookup_value.
Če želite najti izdelek, ki je bele in srednje-velikosti s ceno $18, kopirajte ali vnesite spodnjo formulo v celico H8 in pritisnite Ctrl + Shift + Vnesite da dobite rezultat:
= INDEX (B5: B10, MATCH (1, ("Bela"=C5: C10) * ("Srednje"=D5: D10) * (18=E5: E10), 0))
Ali pa uporabite sklic na celico, da naredite formulo dinamično:
= INDEX (B5: B10, MATCH (1, (H4=C5: C10) * (H5=D5: D10) * (H6=E5: E10), 0))
Pojasnilo formule
=INDEX(B5:B10,MATCH(1,(H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0))
- (H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10): Formula primerja barvo v celici H4 proti vsem barvam v paleti C5: C10; primerja velikost v H5 proti vsem velikostim v D5: D10; primerja ceno v H6 proti vsem cenam v E5: E10. Začetni rezultat je takšen:
{TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}*{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE}*{FALSE;FALSE;FALSE;TRUE;TRUE;FALSE}.
Množenje bo spremenilo vrednosti TRUE in FALSE v 1s in 0s:
{1;0;1;0;1;0}*{0;0;1;1;1;0}*{0;0;0;1;1;0}.
Po množenju bomo imeli eno samo matriko, kot je ta:
{0;0;0;0;1;0}. - UJEMA (1,(H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10), 0) = UJEMA (1,{0;0;0;0;1;0}, 0): Vrsta ujemanja 0 zahteva, da funkcija MATCH najde natančno ujemanje. Funkcija nato vrne položaj 1 v matriki {0;0;0;0;1;0}, Ki je 5.
- KAZALO (B5: B10,UJEMA (1,(H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10), 0)) = INDEX (B5: B10,5): Funkcija INDEX vrne 5th vrednost v območju ID izdelka B5: B10, Ki je 30005.
Sorodne funkcije
Funkcija Excel INDEX vrne prikazano vrednost na podlagi danega položaja iz obsega ali matrike.
Funkcija Excel MATCH išče določeno vrednost v obsegu celic in vrne relativni položaj vrednosti.
Sorodne formule
Poiščite vrednost najbližjega ujemanja z več merili
V nekaterih primerih boste morda morali poiskati najbližjo ali približno vrednost ujemanja na podlagi več meril. S kombinacijo funkcij INDEX, MATCH in IF lahko to hitro opravite v Excelu.
Približno ujemanje z INDEX in MATCH
Včasih moramo v Excelu najti približna ujemanja, da ocenimo uspešnost zaposlenih, ocenimo ocene učencev, izračunamo poštnino glede na težo itd. V tej vadnici se bomo pogovarjali o tem, kako s funkcijami INDEX in MATCH pridobiti rezultate, ki jih potrebujemo.
Iskanje vrednosti iz drugega delovnega lista ali delovnega zvezka
Če veste, kako s funkcijo VLOOKUP iskati vrednosti na delovnem listu, vam vrednosti vlookup z drugega delovnega lista ali delovnega zvezka ne bodo predstavljale težav. Vadnica vam bo pokazala, kako v Excelu pregledati vrednosti z drugega delovnega lista.
Najboljša orodja za pisarniško produktivnost
Kutools za Excel - vam pomaga izstopati iz množice
Kutools za Excel se ponaša z več kot 300 funkcijami, Zagotavljanje, da je vse, kar potrebujete, le en klik stran ...
Kartica Office - omogočite branje in urejanje z zavihki v programu Microsoft Office (vključite Excel)
- Eno sekundo za preklop med desetinami odprtih dokumentov!
- Vsak dan zmanjšajte na stotine klikov z miško, poslovite se od roke miške.
- Poveča vašo produktivnost za 50% pri ogledu in urejanju več dokumentov.
- Prinaša učinkovite zavihke v Office (vključno z Excelom), tako kot Chrome, Edge in Firefox.
