Všeobecný vzorec
(=MAX(IF(TEXT(dates,"ddd")="Mon",values)))
Zhrnutie
Ak chcete zistiť maximálnu hodnotu v daný pracovný deň (tj. Pondelok, utorok, streda atď.), Môžete použiť jednoduchý vzorec poľa založený na funkciách MAX, IF a TEXT. V zobrazenom príklade je vzorec v bunke F5:
=MAX(IF(TEXT(dates,"ddd")=F4,values))
Kde sú dátumy (B5: B15) a hodnoty (C5: C15) pomenované rozsahy.
Poznámka: toto je vzorec poľa a musí byť zadaný pomocou klávesov Control + Shift + Enter.
Vysvetlenie
Pri práci zvnútra sa funkcia TEXT používa na extrahovanie hodnoty v týždni pre každý dátum:
=TEXT(dates,"ddd")
Výsledkom bude pole ako je toto:
("Mon";"Tue";"Wed";"Thu";"Fri";"Mon";"Tue";"Wed";"Thu";"Fri";"Mon")
ktorý sa potom porovnáva s textom vo F4 „Mon“. Výsledkom je ďalšie pole, ktoré obsahuje iba TRUE a FALSE hodnoty:
(TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE)
Všimnite si, že každá PRAVDA zodpovedá pondelku. Toto pole sa vracia do funkcie IF ako logický test. Funguje ako filter na skríning hodnôt v ostatných dňoch týždňa. Konečný výsledok z IF, ktorý sa vráti priamo do funkcie MAX, vyzerá takto:
=MAX((85;FALSE;FALSE;FALSE;FALSE;94;FALSE;FALSE;FALSE;FALSE;52))
MAX automaticky ignoruje FALSE hodnoty a vráti najvyššiu zostávajúcu hodnotu 94.
S AGREGÁTOM
Pre mierne formulovaný vzorec, ktorý nevyžaduje ovládanie + shift + enter, môžete použiť funkciu AGGREGATE takto:
=AGGREGATE(14,6,values/(TEXT(dates,"ddd")=F4),1)
Toto je vzorec použitý v bunke F6 v zobrazenom príklade. Tu dáme AGGREGATE 14 pre argument funkcie (LARGE) a 6 pre argument voľby (chyby ignorovať). Potom pomocou funkcie TEXT zostavíme logický výraz, aby sme skontrolovali všetky dátumy pre pondelok. Výsledkom tejto operácie je pole hodnôt TRUE / FALSE, ktoré sa stanú menovateľom pôvodných hodnôt. Keď sa použije v matematickej operácii, FALSE sa vyhodnotí ako nula a hodí # DIV / 0! chyba. Hodnota TRUE sa vyhodnotí ako 1 a vráti pôvodnú hodnotu. Výsledné pole hodnôt a chýb funguje ako filter. AGGREGATE ignoruje všetky chyby a vráti najväčšiu (maximálnu) z prežívajúcich hodnôt.
MAXIFY
Funkcia MAXIFS, ktorá je k dispozícii v balíku Excel Office 365, dokáže vrátiť maximálnu hodnotu pomocou jedného alebo viacerých kritérií bez potreby maticového vzorca. MAXIFS je však funkcia na diaľku a neumožňuje iným funkciám, ako je TEXT, spracovávať hodnoty v rozsahu kritérií. Môžete však k údajom pridať pomocný stĺpec, vygenerovať hodnoty v týždni pomocou TEXTU a potom použiť MAXIFS so pomocným stĺpcom ako rozsah kritérií.
