INDEX in MATCH v več stolpcih
Če želite poiskati vrednost z ujemanjem v več stolpcih, uporabite formulo matrike, ki temelji na INDEX in MATCH funkcije, ki vključuje MMULT, TRANSPOSE in STOLPEC vam bo naredil uslugo.
Kako poiskati vrednost z ujemanjem v več stolpcih?
Če želite izpolniti ustrezen razred vsakega učenca kot je prikazano v zgornji tabeli, kjer je informacije so navedene v več stolpcih, lahko najprej uporabite trik funkcij MMULT, TRANSPOSE in COLUMN, da ustvarite matrično matriko. Potem vam bo funkcija MATCH dala položaj vaše iskalne vrednosti, ki bo napajana v INDEX, da pridobi vrednost, ki jo iščete v matriki.
Splošna skladnja
=INDEX(return_range,(MATCH(1,MMULT(--(lookup_array=lookup_value),TRANSPOSE(COLUMN(lookup_array)^0)),0)))
√ Opomba: To je matrična formula, ki zahteva, da vnesete Ctrl + Shift + Vnesite.
- obseg_vrnitve: Obseg, iz katerega želite, da formula vrne informacije o razredu. Tukaj se nanaša na obseg razreda.
- iskalna_vrednost: Vrednost, ki jo je formula uporabila za iskanje ustreznih informacij o razredu. Tukaj se nanaša na dano ime.
- iskalni_array: Obseg celic, kjer je lookup_value je na seznamu; Obseg z vrednostmi za primerjavo z lookup_value. Tu se nanaša na obseg imen.
- match_type 0: Prisili MATCH, da poišče prvo vrednost, ki je popolnoma enaka lookup_value.
Da bi našli tisto razred Jimmyja, kopirajte ali vnesite spodnjo formulo v celico H5 in pritisnite Ctrl + Shift + Vnesite da dobite rezultat:
= INDEX ($ 5 $: $ 7 $,(MACH(1,MMULT(--($C$5:$E$7=G5),PRENOSI (STOPEC($C$5:$E$7)^0)),0)))
√ Opomba: Zgornji znaki za dolar ($) označujejo absolutne reference, kar pomeni, da se ime in obsegi razredov v formuli ne bodo spremenili, ko formulo premaknete ali kopirate v druge celice. Upoštevajte, da referenci celice, ki predstavlja iskalno vrednost, ne smete dodajati znakov za dolar, saj želite, da je relativna, ko jo kopirate v druge celice. Ko vnesete formulo, povlecite ročico za polnjenje navzdol, da uporabite formulo v spodnjih celicah.
Pojasnilo formule
=INDEX($B$5:$B$7,(MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSE(COLUMN($C$5:$E$7)^0)),0)))
- ---($C$5:$E$7=G5): Ta segment preveri vsako vrednost v obsegu $C$5:$E$7 če sta enaka vrednosti v celici G5, in ustvari matriko TRUE in FALSE, kot je ta:
{TRUE, FALSE, FALSE; FALSE, FALSE, FALSE; FALSE, FALSE, FALSE}.
Dvojni negativni bo nato pretvoril vrednosti TRUE in FALSE v 1s in 0s, da bo dal niz, kot je ta:
{1,0,0; 0,0,0; 0,0,0}. - STOLPEC($C$5:$E$7): Funkcija COLUMN vrne številke stolpcev za obseg $C$5:$E$7 v nizu, kot je ta: 3,4,5 {}.
- PRENOS (STOLPEC($C$5:$E$7)^0) = PRENOS (3,4,5 {}^0): Po dvigu moči na 0 se bodo vsa števila v matriki {3,4,5} pretvorila v 1: {1,1,1}. Funkcija TRANSPOSE nato pretvori matriko stolpcev v niz vrstic, kot je ta: {1; 1; 1}.
- MMULT(---($C$5:$E$7=G5),PRENOS (STOLPEC($C$5:$E$7)^0)) = MMULT({1,0,0; 0,0,0; 0,0,0},{1; 1; 1}): Funkcija MMULT vrne matrični produkt dveh nizov takole: {1; 0; 0}.
- UJEMA (1,MMULT(---($C$5:$E$7=G5),PRENOS (STOLPEC($C$5:$E$7)^0)), 0) = UJEMA (1,{1; 0; 0}, 0): Vrsta ujemanja 0 prisili funkcijo MATCH, da vrne položaj prvega ujemanja 1 v matriki {1; 0; 0}, Ki je 1.
- KAZALO ($ 5 $: $ 7 $,(UJEMA(1,MMULT(---($C$5:$E$7=G5),PRENOS (STOLPEC($C$5:$E$7)^0)), 0))) = INDEX ($ 5 $: $ 7 $,1): Funkcija INDEX vrne 1st vrednost v razrednem območju $ 5 $: $ 7 $, Ki je A.
Če želite preprosto poiskati vrednost z ujemanjem v več stolpcih, lahko uporabite tudi naš profesionalni dodatek za Excel Kutools za Excel. Oglejte si navodila tukaj za izpolnitev misije.
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.
Excelova funkcija MMULT vrne matrični produkt dveh nizov. Rezultat matrike ima enako število vrstic kot matrika1 in enako število stolpcev kot matrika2.
Excelova funkcija TRANSPOSE zasuka orientacijo obsega ali matrike. Na primer, lahko zasuka tabelo, ki je vodoravno razporejena v vrsticah, navpično v stolpcih ali obratno.
Funkcija COLUMN vrne število stolpcev, v katerih se pojavi formula, ali vrne številko stolpca dane reference. Na primer, formula =COLUMN(BD) vrne 56.
Sorodne formule
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 matrično formulo s funkcijami INDEX in MATCH.
Dvosmerno iskanje z INDEX in MATCH
Za iskanje nečesa v vrsticah in stolpcih v Excelu ali pa rečemo, da poiščemo vrednost na presečišču določene vrstice in stolpca, lahko uporabimo pomoč funkcij INDEX in MATCH.
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.
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.
