Vispārēja formula
(=MAX(IF(TEXT(dates,"ddd")="Mon",values)))
Kopsavilkums
Lai atrastu maksimālo vērtību noteiktā nedēļas dienā (ti, pirmdienā, otrdienā, trešdienā utt.), Varat izmantot vienkāršu masīva formulu, kuras pamatā ir funkcijas MAX, IF un TEXT. Parādītajā piemērā formula šūnā F5 ir:
=MAX(IF(TEXT(dates,"ddd")=F4,values))
Kur datumi (B5: B15) un vērtības (C5: C15) ir nosaukti diapazoni.
Piezīme: šī ir masīva formula, un tā jāievada ar Control + Shift + Enter.
Paskaidrojums
Strādājot no iekšpuses, funkcija TEXT tiek izmantota, lai katram datumam iegūtu nedēļas dienas vērtību:
=TEXT(dates,"ddd")
Rezultātā rodas šāds masīvs:
("Mon";"Tue";"Wed";"Thu";"Fri";"Mon";"Tue";"Wed";"Thu";"Fri";"Mon")
kuru pēc tam salīdzina ar tekstu F4 "Mon". Rezultāts ir vēl viens masīvs, kurā ir tikai TRUE un FALSE vērtības:
(TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE)
Ņemiet vērā, ka katra TRUE atbilst pirmdienai. Šis masīvs tiek atgriezts IF funkcijas iekšpusē kā loģiskais tests. Tas darbojas kā filtrs, lai ekrānā parādītu vērtības citās nedēļas dienās. IF gala rezultāts, kas tiek atgriezts tieši MAX funkcijā, izskatās šādi:
=MAX((85;FALSE;FALSE;FALSE;FALSE;94;FALSE;FALSE;FALSE;FALSE;52))
MAX automātiski ignorē FALSE vērtības un atgriež augstāko atlikušo vērtību 94.
Ar AGGREGATE
Nedaudz izteiksmīgākai formulai, kurai nav nepieciešama vadība + Shift + Enter, varat izmantot funkciju AGGREGATE šādi:
=AGGREGATE(14,6,values/(TEXT(dates,"ddd")=F4),1)
Šī ir formula, kas parādītajā piemērā izmantota šūnā F6. Šeit mēs piešķiram AGGREGATE 14 funkcijas argumentam (LARGE) un 6 opcijas argumentam (ignorēt kļūdas). Pēc tam mēs izveidojam loģisku izteiksmi, izmantojot funkciju TEXT, lai pārbaudītu visus pirmdienu datumus. Šīs operācijas rezultāts ir TRUE / FALSE vērtību masīvs, kas kļūst par sākotnējo vērtību saucēju. Lietojot matemātikas operācijā, FALSE tiek novērtēts kā nulle un iemet # DIV / 0! kļūda. Patiesība TRUE tiek novērtēta kā 1 un atgriež sākotnējo vērtību. Pēdējais vērtību un kļūdu masīvs darbojas kā filtrs. AGGREGATE ignorē visas kļūdas un atgriež lielāko (maksimālo) no saglabātajām vērtībām.
MAXIFS
Funkcija MAXIFS, kas pieejama programmā Excel Office 365, var atgriezt maksimālo vērtību, izmantojot vienu vai vairākus kritērijus, bez masīva formulas. Tomēr funkcija MAXIFS ir diapazona funkcija un neļaus citām funkcijām, piemēram, TEXT, apstrādāt vērtības kritēriju diapazonos. Tomēr datiem varat pievienot palīga kolonnu, ģenerēt nedēļas dienas vērtības, izmantojot tekstu TEXT, pēc tam kā kritēriju diapazonu izmantot MAXIFS ar palīga kolonnu.
