Bendroji formulė
(=MAX(IF(TEXT(dates,"ddd")="Mon",values)))
Santrauka
Norėdami rasti maksimalią vertę nurodytą darbo dieną (ty pirmadienį, antradienį, trečiadienį ir kt.), Galite naudoti paprastą masyvo formulę, pagrįstą MAX, IF ir TEXT funkcijomis. Parodytame pavyzdyje formulė langelyje F5 yra:
=MAX(IF(TEXT(dates,"ddd")=F4,values))
Kur datos (B5: B15) ir reikšmės (C5: C15) įvardijamos kaip diapazonai.
Pastaba: tai yra masyvo formulė, kurią reikia įvesti paspaudus „Control“ + „Shift“ + „Enter“.
Paaiškinimas
Dirbant iš vidaus, TEXT funkcija naudojama kiekvienos datos savaitės dienos vertei išgauti:
=TEXT(dates,"ddd")
Dėl to gaunamas toks masyvas:
("Mon";"Tue";"Wed";"Thu";"Fri";"Mon";"Tue";"Wed";"Thu";"Fri";"Mon")
kuris tada lyginamas su tekstu F4 „Pirmadienis“. Rezultatas yra kitas masyvas, kuriame yra tik TRUE ir FALSE reikšmės:
(TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE)
Atkreipkite dėmesį, kad kiekviena TRUE atitinka pirmadienį. Šis masyvas grąžinamas IF funkcijos viduje kaip loginis testas. Jis veikia kaip filtras, rodantis reikšmes kitomis savaitės dienomis. Galutinis IF rezultatas, kuris grąžinamas tiesiai į MAX funkciją, atrodo taip:
=MAX((85;FALSE;FALSE;FALSE;FALSE;94;FALSE;FALSE;FALSE;FALSE;52))
MAX automatiškai ignoruoja FALSE reikšmes ir grąžina didžiausią likusią vertę - 94.
Su AGGREGATE
Jei norite šiek tiek geeky formulės, kuriai nereikia valdymo + Shift + Enter, galite naudoti tokią funkciją AGGREGATE:
=AGGREGATE(14,6,values/(TEXT(dates,"ddd")=F4),1)
Tai formulė, naudojama pavyzdžio F6 langelyje. Čia nurodome AGGREGATE 14 funkcijos argumentui (LARGE) ir 6 - argumento argumentui (ignoruoti klaidas). Tada mes sukuriame loginę išraišką naudodami TEXT funkciją, kad patikrintume visas pirmadienių datas. Šios operacijos rezultatas yra TRUE / FALSE reikšmių masyvas, kuris tampa pradinių verčių vardikliu. Naudojant matematikos operacijoje, FALSE vertinamas kaip nulis ir metamas # DIV / 0! klaida. TRUE vertina kaip 1 ir grąžina pradinę vertę. Galutinis reikšmių ir klaidų masyvas veikia kaip filtras. AGGREGATE nepaiso visų klaidų ir pateikia didžiausią (maksimalią) iš išlikusių verčių.
MAKSIFAI
„MAXIFS“ funkcija, pasiekiama „Excel Office 365“, gali grąžinti maksimalią vertę pagal vieną ar daugiau kriterijų, nereikia masyvo formulės. Tačiau „MAXIFS“ yra diapazono funkcija ir neleis kitoms funkcijoms, pvz., TEXT, apdoroti reikšmes kriterijų diapazonuose. Tačiau prie duomenų galite pridėti pagalbinį stulpelį, sugeneruoti savaitės dienų reikšmes naudodami TEXT, tada naudoti kriterijų diapazoną MAXIFS su pagalbiniu stulpeliu.
