Общая формула
(=MAX(IF(criteria_range=criteria,value_range)))
Резюме
Чтобы найти максимальное значение в диапазоне с определенными критериями, вы можете использовать базовую формулу массива, основанную на функциях ЕСЛИ и МАКС. В показанном примере формула в ячейке H8:
(=MAX(IF(B5:B9391=H7,E5:E9391)))
который возвращает максимальную температуру на дату в H7.
Примечание: это формула массива, которую необходимо вводить с помощью Control + Shift + Enter.
Объяснение
Показанный пример содержит почти 10 000 строк данных. Данные представляют собой показания температуры, снятые каждые 2 минуты в течение нескольких дней. Для любой заданной даты (указанной в ячейке H7) мы хотим получить максимальную температуру в эту дату.
Внутри функции ЕСЛИ логический тест вводится как B5: B9391 = H7. Поскольку мы сравниваем значение в H7 с диапазоном ячеек (массивом), результатом будет массив результатов, где каждый элемент в массиве имеет значение TRUE или FALSE. Значения ИСТИНА представляют даты, соответствующие H7.
Для значения, если оно истинно, мы предоставляем диапазон E5: E9391, который извлекает весь полный набор температур в градусах Фаренгейта. Это возвращает массив значений того же размера, что и первый массив.
Функция ЕСЛИ действует как фильтр. Поскольку мы предоставляем IF с массивом для логической проверки, IF возвращает массив результатов. Если дата совпадает с H7, массив содержит значение температуры. Во всех остальных случаях массив содержит ЛОЖЬ. Другими словами, только температуры, связанные с датой в H7, выдерживают переход через функцию IF.
Результат массива из функции ЕСЛИ доставляется непосредственно в функцию MAX, которая возвращает максимальное значение в массиве.
С MAXIFS
В Excel O365 и Excel 2019 новая функция MAXIFS может найти максимальное значение с одним или несколькими критериями без необходимости в формуле массива. С MAXIFS эквивалентная формула для этого примера:
=MAXIFS(E5:E9391,B5:B9391,H7)
