Dvosmerno približno ujemanje z več merili
V tej vadnici bomo govorili o tem, kako poiskati približno ujemanje na podlagi več meril, navedenih v stolpcih in vrsticah v Excelovi preglednici, s pomočjo of INDEX, MATCHin IF funkcije.
Kako izvesti dvosmerno približno ujemanje z več merili v Excelu?
Če želite izvedeti, velikost oblačil za ženska kdo je 165.5cm visok in tehta 55kg v skladu s zgornjo tabelo, lahko formuli dodate dve funkciji MATCH: ena s funkcijo IF dobi številko vrstice (spol in višina), druga pa številko stolpca (teža). Nato INDEX poišče ustrezno velikost glede na koordinate. (Upoštevajte, da je številka vrstice pred številko stolpca v formuli INDEX.)
Splošna skladnja
=INDEX(return_range,MATCH(lookup_value1,IF(lookup_array2=lookup_value2,lookup_array1),match_type),MATCH(lookup_value3,lookup_array3,match_type))
√ Opomba: To je matrična formula, ki zahteva, da vnesete Ctrl + Shift + Vnesite.
- obseg_vrnitve: Obseg, iz katerega želite, da kombinacija kombinacije vrne velikost. Tu se nanaša na obseg velikosti.
- iskalna_vrednost: Vrednost, ki jo je formula uporabila za iskanje položaja ustrezne velikosti. Tu se nanašajo na podatke o danem spolu, višini in teži.
- iskalni_array: Obseg celic z vrednostmi za primerjavo z lookup_value. Tu se nanaša na spol, višino in težo.
- match_type: 1 ali -1.
1 ali izpuščeno (privzeto) bo MATCH našel največjo vrednost, ki je manjša ali enaka lookup_value. Vrednosti v iskalni_array mora biti postavljen v naraščajočem vrstnem redu.
-1, MATCH bo našel najmanjšo vrednost, ki je večja ali enaka lookup_value. Vrednosti v iskalni_array morajo biti postavljene v padajočem vrstnem redu.
Če želite izvedeti, velikost oblačil za ženska kdo je 165.5cm visok in tehta 55kg, kopirajte ali vnesite spodnjo formulo v celico M9 in pritisnite Ctrl + Shift + Vnesite da dobite rezultat:
= INDEX (D5: J16, MATCH (M6, IF (B5: B16=M5,C5: C16), 1), MATCH (M7,D4: J4, 1))
Pojasnilo formule
=INDEX(D5:J16,MATCH(M6,IF(B5:B16=M5,C5:C16),1),MATCH(M7,D4:J4,1))
- IF(B5:B16=M5,C5:C16): Funkcija IF preveri vsako vrednost v B5: B16 če se ujemajo z vrednostjo v celici M5, Moški. V tem primeru bo funkcija IF ohranila ustrezne višine, navedene v C5: C16; V nasprotnem primeru se IF vrne LAŽNE. Formula bo vrnila matriko, kot je ta: {157;160;162.5;165;167.5;170;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}.
- UJEMA (M6,IF(B5:B16=M5,C5:C16), 1) = UJEMA (M6,{157;160;162.5;165;167.5;170;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}, 1): O match_type 1 prisili funkcijo MATCH, da vrne položaj največje vrednosti, ki je manjša ali enaka 165.5 (vrednost v celici M6) v matriki. Torej se MATCH vrne 4, položaj vrednosti 165.
- UJEMA (M7, D4: J4,1): Vrne se funkcija MATCH 3, saj je največja vrednost manjša ali enaka vrednosti v M7, 55, je pri 3rd položaj območja D4: J4.
- KAZALO (D5: J16,UJEMA (M6,IF(B5:B16=M5,C5:C16), 1),UJEMA (M7, D4: J4,1)) = INDEX (D5: J16,4,3): Funkcija INDEX pridobi vrednost na presečišču 4th vrstici in 3rd stolpec v obsegu velikosti D5: J16, ki je vrednost v celici F8, M.
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.
Funkcija IF je ena najpreprostejših in najbolj uporabnih funkcij v Excelovem delovnem zvezku. Izvede preprost logični test, ki glede na rezultat primerjave vrne eno vrednost, če je rezultat TRUE, ali drugo vrednost, če je rezultat FALSE.
Sorodne formule
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.
Če želite poiskati najbližje ujemanje iskalne vrednosti v številčnem nizu podatkov v Excelu, lahko skupaj uporabite funkcije INDEX, MATCH, ABS in MIN.
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.
Iskanje po več kriterijih z indeksom in ujemanjem
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.
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.
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.