Üldine valem
(=MAX(IF(rng1=criteria1,IF(rng2=criteria2,values))))
Kokkuvõte
Rohkem kui ühe kriteeriumi alusel andmehulga maksimaalse väärtuse saamiseks võite kasutada massiivi valemit, mis põhineb funktsioonidel MAX ja IF. Näidatud näites on valem punktis I6:
(=MAX(IF(color=G6,IF(item=H6,price))))
Värviga "punane" ja "müts" on tulemus 11,00 dollarit
Märkus. See on massiivivalem ja see tuleb sisestada, kasutades klahvikombinatsiooni Ctrl + Tõst + Sisestatud
Selgitus
Selles näites kasutatakse järgmisi nimetatud vahemikke: "color" = B6: B14, "item" = C6: C14 ja "price" = E6: E14. Eesmärk on leida antud värvi ja eseme maksimaalne hind.
Selles valemis kasutatakse kahte sisestatud IF-funktsiooni, mis on pakitud MAX-i sisse, et tagastada maksimaalne hind kahe kriteeriumiga. Alustades esimese IF-lause (color = G6) loogilise testiga, kontrollitakse nimega vahemikus "color" (B6: B14) olevaid väärtusi lahtris G6 "punane". Tulemuseks on selline massiiv:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Teise IF-lause, element = H6, loogilises testis kontrollitakse nimega vahemikuüksuse (C6: C14) väärtusi lahtris H6 "hat" oleva väärtuse suhtes. Tulemuseks on selline massiiv:
(TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE)
Teise IF-lause "väärtus, kui tõene" on nimega vahemik "hinnad" (E6: E14), mis on selline massiiv:
(11;8;9;12;9;10;9;8;7)
Selle vahemiku iga üksuse eest tagastatakse hind ainult siis, kui kahe ülaltoodud massiivi tulemus on TÕELINE vastavatel positsioonidel olevate üksuste jaoks. Näidatud näites näeb MAX-i viimane massiiv välja selline:
(11;8;9;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Pange tähele, et ainult hinnad, mis "ellu jäävad", on hinnad, mille värv on "punane" ja üksus on "müts".
Funktsioon MAX tagastab seejärel kõrgeima hinna, ignoreerides automaatselt FALSE väärtusi.
Boolean loogikat kasutav alternatiivne süntaks
Võite kasutada ka järgmist massiivivalemit, mis kasutab ainult üht IF-funktsiooni koos tõeväärtusega:
(=MAX(IF((color=G6)*(item=H6),price)))
Selle süntaksi eeliseks on see, et lisakriteeriume on lihtsam lisada ilma pesastatud IF-funktsioone lisamata. Kui vajate VÕI loogikat, kasutage tingimuste korrutamise asemel liitmist.
MAXIFS-iga
Excel 2016-s juurutatud funktsioon MAXIFS on loodud maksimumide arvutamiseks ühe või mitme kriteeriumi alusel, ilma et oleks vaja massiivivalemit. MAXIFS-i korral on valemi I6 valem järgmine:
=MAXIFS(price,color,G6,item,H6)
Märkus. MAXIFS ignoreerib automaatselt tühje lahtreid, mis vastavad kriteeriumidele. Teisisõnu, MAXIFS ei käsitle kriteeriumidele vastavaid tühje lahtreid nullina. Teisalt tagastab MAXIFS nulli (0), kui ükski lahter ei vasta kriteeriumidele.
