Obecný vzorec
(=MAX(IF(TEXT(dates,"ddd")="Mon",values)))
souhrn
Chcete-li zjistit maximální hodnotu v daný den v týdnu (tj. Pondělí, úterý, středa atd.), Můžete použít jednoduchý maticový vzorec založený na funkcích MAX, IF a TEXT. V zobrazeném příkladu je vzorec v buňce F5:
=MAX(IF(TEXT(dates,"ddd")=F4,values))
Kde jsou data (B5: B15) a hodnoty (C5: C15) pojmenovány rozsahy.
Poznámka: toto je maticový vzorec a musí být zadán pomocí kláves Control + Shift + Enter.
Vysvětlení
Při práci zevnitř ven se funkce TEXT používá k extrakci hodnoty dne v týdnu pro každé datum:
=TEXT(dates,"ddd")
Výsledkem je pole jako toto:
("Mon";"Tue";"Wed";"Thu";"Fri";"Mon";"Tue";"Wed";"Thu";"Fri";"Mon")
který je poté porovnán s textem v F4, „Mon“. Výsledkem je další pole, které obsahuje pouze TRUE a FALSE hodnoty:
(TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE)
Všimněte si, že každá PRAVDA odpovídá pondělí. Toto pole je vráceno uvnitř funkce IF jako logický test. Funguje jako filtr k odfiltrování hodnot v jiné dny v týdnu. Konečný výsledek z IF, který se vrací přímo do funkce MAX, vypadá takto:
=MAX((85;FALSE;FALSE;FALSE;FALSE;94;FALSE;FALSE;FALSE;FALSE;52))
MAX automaticky ignoruje FALSE hodnoty a vrátí nejvyšší zbývající hodnotu, 94.
S AGREGÁTEM
Pro úhledně více podivný vzorec, který nevyžaduje ovládání + Shift + Enter, můžete použít funkci AGGREGATE takto:
=AGGREGATE(14,6,values/(TEXT(dates,"ddd")=F4),1)
Toto je vzorec použitý v buňce F6 v zobrazeném příkladu. Zde dáme AGGREGATE 14 pro argument funkce (LARGE) a 6 pro argument volby (ignorovat chyby). Potom vytvoříme logický výraz pomocí funkce TEXT, abychom zkontrolovali všechna data pro pondělí. Výsledkem této operace je pole TRUE / FALSE hodnot, které se stanou jmenovatelem původních hodnot. Při použití v matematické operaci se FALSE vyhodnotí jako nula a hodí # DIV / 0! chyba. TRUE vyhodnotí jako 1 a vrátí původní hodnotu. Konečné pole hodnot a chyb funguje jako filtr. AGGREGATE ignoruje všechny chyby a vrátí největší (maximální) z přežívajících hodnot.
MAXIFY
Funkce MAXIFS, která je k dispozici v aplikaci Excel Office 365, může vrátit maximální hodnotu pomocí jednoho nebo více kritérií bez nutnosti maticového vzorce. MAXIFS je však funkce založená na dálku a neumožňuje jiným funkcím, jako je TEXT, zpracovávat hodnoty v rozsahu kritérií. Můžete však přidat k datům sloupec pomocníka, generovat hodnoty v týdnu pomocí TEXTU a poté použít MAXIFS se sloupcem pomocníka jako rozsah kritérií.
