Общая формула
(=MAX(IF(rng1=criteria1,IF(rng2=criteria2,values))))
Резюме
Чтобы получить максимальное значение в наборе данных на основе более чем одного критерия, вы можете использовать формулу массива, основанную на функциях МАКС и ЕСЛИ. В показанном примере формула I6 имеет следующий вид:
(=MAX(IF(color=G6,IF(item=H6,price))))
С цветом «красный» и предметом «шляпа» результат 11,00 $.
Примечание. Это формула массива, и ее необходимо вводить с помощью Ctrl + Shift + Enter.
Объяснение
В этом примере используются следующие именованные диапазоны: «color» = B6: B14, «item» = C6: C14 и «price» = E6: E14. Цель состоит в том, чтобы найти максимальную цену для данного цвета и предмета.
В этой формуле используются две вложенные функции ЕСЛИ, заключенные в MAX, чтобы вернуть максимальную цену с двумя критериями. Начиная с логической проверки первого оператора IF, color = G6, значения в названном диапазоне «цвет» (B6: B14) сравниваются со значением в ячейке G6, «красный». Результатом является такой массив:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
В логическом тесте для второго оператора IF, item = H6, значения в названном элементе диапазона (C6: C14) сравниваются со значением в ячейке H6, «hat». Результатом является такой массив:
(TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE)
«Значение, если истинно» для второго оператора IF - это именованный диапазон «цен» (E6: E14), который представляет собой массив, подобный этому:
(11;8;9;12;9;10;9;8;7)
Цена возвращается для каждого элемента в этом диапазоне, только если результат первых двух массивов выше ИСТИНА для элементов в соответствующих позициях. В показанном примере последний массив внутри MAX выглядит так:
(11;8;9;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Обратите внимание, что «выживают» только цены, находящиеся в позиции, где цвет «красный», а предмет - «шляпа».
Затем функция MAX возвращает самую высокую цену, автоматически игнорируя значения FALSE.
Альтернативный синтаксис с использованием логической логики
Вы также можете использовать следующую формулу массива, в которой используется только одна функция ЕСЛИ вместе с логической логикой:
(=MAX(IF((color=G6)*(item=H6),price)))
Преимущество этого синтаксиса заключается в том, что легче добавлять дополнительные критерии без добавления дополнительных вложенных функций IF. Если вам нужна логика ИЛИ, используйте сложение вместо умножения между условиями.
С MAXIFS
Функция MAXIFS, представленная в Excel 2016, предназначена для вычисления максимумов на основе одного или нескольких критериев без необходимости в формуле массива. С MAXIFS формула в I6:
=MAXIFS(price,color,G6,item,H6)
Примечание. MAXIFS автоматически игнорирует пустые ячейки, соответствующие критериям. Другими словами, MAXIFS не будет обрабатывать пустые ячейки, соответствующие критериям, как ноль. С другой стороны, MAXIFS вернет ноль (0), если ни одна ячейка не соответствует критериям.
