Загальна формула
(=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)
Зверніть увагу, кожна ІСТИНА відповідає понеділку. Цей масив повертається всередину функції 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 у наведеному прикладі. Тут ми даємо AGGREGATE 14 для аргументу функції (LARGE) і 6 для аргументу параметра (ігнорувати помилки). Потім ми будуємо логічний вираз за допомогою функції TEXT для перевірки всіх дат понеділка. Результатом цієї операції є масив значень TRUE / FALSE, які стають знаменником вихідних значень. При використанні в математичній операції FALSE вважає нулем і видає # DIV / 0! помилка. ІСТИНА обчислюється як 1 і повертає початкове значення. Остаточний масив значень та помилок діє як фільтр. AGGREGATE ігнорує всі помилки і повертає найбільше (максимум) із збережених значень.
МАКСИФИ
Функція MAXIFS, доступна в Excel Office 365, може повертати максимальне значення за одним або кількома критеріями без необхідності формули масиву. Однак MAXIFS є функцією на основі діапазону і не дозволяє іншим функціям, як TEXT, обробляти значення в діапазоні критеріїв. Однак ви можете додати допоміжний стовпець до даних, генерувати значення днів тижня за допомогою TEXT, а потім використовувати MAXIFS із допоміжним стовпцем як діапазон критеріїв.
