Загальна формула
(=MAX(IF(criteria_range=criteria,value_range)))
Резюме
Щоб знайти максимальне значення в діапазоні з певними критеріями, ви можете використовувати основну формулу масиву на основі функції IF та MAX. У наведеному прикладі формула в комірці H8 має вигляд:
(=MAX(IF(B5:B9391=H7,E5:E9391)))
який повертає максимальну температуру на дату в H7.
Примітка: це формула масиву, і її потрібно вводити за допомогою Control + Shift + Enter
Пояснення
Наведений приклад містить майже 10 000 рядків даних. Дані представляють показники температури, зроблені кожні 2 хвилини протягом періоду днів. Для будь-якої заданої дати (вказана в комірці H7) ми хочемо отримати максимальну температуру на цю дату.
Усередині функції IF логічний тест вводиться як B5: B9391 = H7. Оскільки ми порівнюємо значення в H7 з діапазоном комірок (масив), результатом буде масив результатів, де кожен елемент масиву має значення TRUE або FALSE. Значення TRUE представляють дати, які відповідають H7.
Якщо значення true, ми надаємо діапазон E5: E9391, який визначає весь повний набір температур у Фаренгейті. Це повертає масив значень того самого розміру, що і перший масив.
Функція IF діє як фільтр. Оскільки ми надаємо IF для масиву логічного тесту, IF повертає масив результатів. Там, де дата відповідає H7, масив містить значення температури. У всіх інших випадках масив містить FALSE. Іншими словами, лише температура, пов’язана з датою в H7, виживає в поїздці через функцію IF.
Результат масиву від функції IF доставляється безпосередньо до функції MAX, яка повертає максимальне значення в масиві.
З MAXIFS
В Excel O365 та Excel 2019 нова функція MAXIFS може знайти максимальне значення за одним або кількома критеріями без необхідності формули масиву. Для MAXIFS еквівалентна формула для цього прикладу:
=MAXIFS(E5:E9391,B5:B9391,H7)
