Splošna formula
(=MAX(IF(TEXT(dates,"ddd")="Mon",values)))
Povzetek
Če želite najti največjo vrednost na dani delovni dan (tj. V ponedeljek, torek, sredo itd.), Lahko uporabite preprosto matrično formulo, ki temelji na funkcijah MAX, IF in TEXT. V prikazanem primeru je formula v celici F5:
=MAX(IF(TEXT(dates,"ddd")=F4,values))
Kjer so datumi (B5: B15) in vrednosti (C5: C15) imenovani obsegi.
Opomba: to je matrična formula, ki jo je treba vnesti s tipkama Control + Shift + Enter.
Pojasnilo
Funkcija TEXT, ki deluje od znotraj navzven, se uporablja za pridobivanje vrednosti v tednu za vsak datum:
=TEXT(dates,"ddd")
Rezultat je taka matrika:
("Mon";"Tue";"Wed";"Thu";"Fri";"Mon";"Tue";"Wed";"Thu";"Fri";"Mon")
ki se nato primerja z besedilom v F4, "pon". Rezultat je še ena matrika, ki vsebuje samo TRUE in FALSE vrednosti:
(TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE)
Upoštevajte, da vsaka TRUE ustreza ponedeljku. Ta matrika se vrne znotraj funkcije IF kot logični test. Deluje kot filter za prikaz vrednosti v drugih dneh v tednu. Končni rezultat iz IF, ki se vrne neposredno v funkcijo MAX, je videti takole:
=MAX((85;FALSE;FALSE;FALSE;FALSE;94;FALSE;FALSE;FALSE;FALSE;52))
MAX samodejno prezre FALSE vrednosti in vrne najvišjo preostalo vrednost, 94.
Z AGREGATE
Za nekoliko bolj gejevsko formulo, ki ne zahteva nadzora + shift + enter, lahko uporabite funkcijo AGGREGATE, kot je ta:
=AGGREGATE(14,6,values/(TEXT(dates,"ddd")=F4),1)
To je formula, uporabljena v celici F6 v prikazanem primeru. Tu podajamo AGREGATE 14 za argument funkcije (LARGE) in 6 za argument možnosti (prezre napake). Nato z uporabo funkcije TEXT zgradimo logični izraz, da preverimo vse datume za ponedeljek. Rezultat te operacije je niz TRUE / FALSE vrednosti, ki postanejo imenovalec prvotnih vrednosti. Ko se uporablja v matematični operaciji, FALSE oceni kot nič in vrže # DIV / 0! napaka. TRUE oceni kot 1 in vrne prvotno vrednost. Končno polje vrednosti in napak deluje kot filter. AGGREGATE prezre vse napake in vrne največjo (največjo) vrednost od preživetih.
MAXIFI
Funkcija MAXIFS, ki je na voljo v Excelu Office 365, lahko vrne največjo vrednost z uporabo enega ali več meril, ne da bi bila potrebna matrična formula. Vendar je MAXIFS funkcija, ki temelji na obsegu, in drugim funkcijam, kot je TEXT, ne dovoljuje obdelave vrednosti v obsegih meril. Podatkom pa lahko dodate stolpec pomožnika, ustvarite vrednosti dneva v tednu s TEXT in nato uporabite MAXIFS s pomožnim stolpcem kot obseg meril.
