Обща формула
(=MAX(IF(TEXT(dates,"ddd")="Mon",values)))
Обобщение
За да намерите максималната стойност за даден делничен ден (т.е. понеделник, вторник, сряда и т.н.), можете да използвате проста формула за масив, базирана на функциите MAX, IF и TEXT. В показания пример формулата в клетка F5 е:
=MAX(IF(TEXT(dates,"ddd")=F4,values))
Където дати (B5: B15) и стойности (C5: C15) са наречени диапазони.
Забележка: това е формула на масив и трябва да се въведе с Control + Shift + Enter.
Обяснение
Работейки отвътре навън, функцията TEXT се използва за извличане на делнична стойност за всяка дата:
=TEXT(dates,"ddd")
Това води до масив като този:
("Mon";"Tue";"Wed";"Thu";"Fri";"Mon";"Tue";"Wed";"Thu";"Fri";"Mon")
което след това се сравнява с текста във F4, „Понеделник“. Резултатът е друг масив, който съдържа само TRUE и FALSE стойности:
(TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE)
Обърнете внимание, че всеки TRUE съответства на понеделник. Този масив се връща във функцията IF като логически тест. Той действа като филтър за отсяване на стойности в други дни от седмицата. Крайният резултат от IF, който се връща директно към функцията MAX, изглежда така:
=MAX((85;FALSE;FALSE;FALSE;FALSE;94;FALSE;FALSE;FALSE;FALSE;52))
MAX автоматично игнорира FALSE стойности и връща най-високата оставаща стойност, 94.
С АГРЕГАТ
За малко по-отвратителна формула, която не изисква управление + shift + enter, можете да използвате функцията AGGREGATE по следния начин:
=AGGREGATE(14,6,values/(TEXT(dates,"ddd")=F4),1)
Това е формулата, използвана в клетка F6 в показания пример. Тук даваме AGREGATE 14 за аргумента на функцията (LARGE) и 6 за аргумента за опция (игнориране на грешки). След това изграждаме логически израз, използвайки функцията TEXT, за да проверим всички дати за понеделник. Резултатът от тази операция е масив от TRUE / FALSE стойности, които стават знаменател на първоначалните стойности. Когато се използва в математическа операция, FALSE се оценява като нула и изхвърля # DIV / 0! грешка. TRUE се оценява като 1 и връща първоначалната стойност. Крайният масив от стойности и грешки действа като филтър. AGGREGATE игнорира всички грешки и връща най-голямата (максимална) от оцелелите стойности.
МАКСИФИ
Функцията MAXIFS, налична в Excel Office 365, може да върне максимална стойност, използвайки един или повече критерии, без да е необходима формула на масив. MAXIFS обаче е функция, базирана на диапазон, и няма да позволи на други функции като TEXT да обработват стойности в диапазони от критерии. Можете обаче да добавите помощна колона към данните, да генерирате делнични стойности с TEXT, след което да използвате MAXIFS с помощната колона като диапазон от критерии.
