Vispārēja formula
(=MAX(IF(rng1=criteria1,IF(rng2=criteria2,values))))
Kopsavilkums
Lai iegūtu maksimālo vērtību datu kopā, pamatojoties uz vairākiem kritērijiem, varat izmantot masīva formulu, kuras pamatā ir funkcijas MAX un IF. Parādītajā piemērā formula I6 ir:
(=MAX(IF(color=G6,IF(item=H6,price))))
Ar krāsu "sarkana" un "cepures" priekšmetu rezultāts ir 11,00 ASV dolāri
Piezīme: Šī ir masīva formula, un tā jāievada, izmantojot Ctrl + Shift + ievadīts
Paskaidrojums
Šajā piemērā tiek izmantoti šādi nosauktie diapazoni: "color" = B6: B14, "item" = C6: C14 un "price" = E6: E14. Mērķis ir atrast maksimālo cenu noteiktai krāsai un priekšmetam.
Šajā formulā tiek izmantotas divas ligzdotas IF funkcijas, kas ietītas MAX iekšpusē, lai atgrieztu maksimālo cenu ar diviem kritērijiem. Sākot ar pirmā IF paziņojuma loģisko pārbaudi color = G6, vērtības nosauktajā diapazonā "color" (B6: B14) tiek pārbaudītas pret vērtību šūnā G6 "red". Rezultāts ir šāds masīvs:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Otrā IF paziņojuma, vienums = H6, loģiskajā pārbaudē nosauktā diapazona vienuma (C6: C14) vērtības tiek salīdzinātas ar vērtību šūnā H6 "hat". Rezultāts ir šāds masīvs:
(TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE)
2. IF paziņojuma vērtība “ja taisnība” ir nosauktais diapazons “cenas” (E6: E14), kas ir šāds masīvs:
(11;8;9;12;9;10;9;8;7)
Cena tiek atgriezta par katru šī diapazona priekšmetu tikai tad, ja pirmo divu iepriekš minēto masīvu rezultāts ir PATIESA precēm attiecīgajās pozīcijās. Parādītajā piemērā pēdējais masīvs MAX iekšpusē izskatās šādi:
(11;8;9;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Ņemiet vērā, ka cenas, kas “izdzīvo”, ir tikai tām, kuras krāsa ir “sarkana” un prece “cepure”.
Tad funkcija MAX atgriež augstāko cenu, automātiski ignorējot FALSE vērtības.
Alternatīva sintakse, izmantojot loģisko loģiku
Varat arī izmantot šādu masīva formulu, kurā kopā ar Būla loģiku tiek izmantota tikai viena IF funkcija:
(=MAX(IF((color=G6)*(item=H6),price)))
Šīs sintakses priekšrocība ir tā, ka ir vieglāk pievienot papildu kritērijus, nepievienojot papildu ligzdotas IF funkcijas. Ja jums nepieciešama loģika VAI, nosacījumu reizināšanas vietā izmantojiet papildinājumu.
Ar MAXIFS
Funkcija MAXIFS, kas ieviesta programmā Excel 2016, ir paredzēta maksimumu aprēķināšanai, pamatojoties uz vienu vai vairākiem kritērijiem, bez masīva formulas nepieciešamības. Izmantojot MAXIFS, I6 formulā ir:
=MAXIFS(price,color,G6,item,H6)
Piezīme: MAXIFS automātiski ignorēs tukšas šūnas, kas atbilst kritērijiem. Citiem vārdiem sakot, MAXIFS tukšas šūnas, kas atbilst kritērijiem, neuzskatīs par nulli. No otras puses, MAXIFS atgriezīs nulli (0), ja neviena šūna neatbilst kritērijiem.
