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