Обща формула
(=MAX(IF(rng1=criteria1,IF(rng2=criteria2,values))))
Обобщение
За да получите максималната стойност в набор от данни въз основа на повече от един критерий, можете да използвате формула на масив, базирана на функциите MAX и IF. В показания пример формулата в I6 е:
(=MAX(IF(color=G6,IF(item=H6,price))))
С цвят на "червено" и елемент на "шапка" резултатът е $ 11,00
Забележка: Това е формула на масив и трябва да се въведе с помощта на Ctrl + Shift + въведен
Обяснение
Този пример използва следните именувани диапазони: "color" = B6: B14, "item" = C6: C14 и "price" = E6: E14. Целта е да се намери максималната цена за даден цвят и артикул.
Тази формула използва две вложени IF функции, увити в MAX, за да върне максималната цена с два критерия. Започвайки с логически тест на първия оператор IF, color = G6, стойностите в посочения диапазон "цвят" (B6: B14) се проверяват спрямо стойността в клетка G6, "червено". Резултатът е масив като този:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
В логическия тест за втория оператор IF, item = H6, стойностите в посочения елемент на диапазона (C6: C14) се проверяват спрямо стойността в клетка H6, "шапка". Резултатът е масив като този:
(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 стойности.
Алтернативен синтаксис, използващ логическа логика
Можете също да използвате следната формула на масив, която използва само една IF функция заедно с булева логика:
(=MAX(IF((color=G6)*(item=H6),price)))
Предимството на този синтаксис е, че е по-лесно да се добавят допълнителни критерии, без да се добавят допълнителни вложени IF функции. Ако имате нужда ИЛИ логика, използвайте добавяне вместо умножение между условията.
С МАКСИФИ
Функцията MAXIFS, въведена в Excel 2016, е предназначена да изчислява максимуми въз основа на един или повече критерии, без да е необходима формула на масив. С MAXIFS формулата в I6 е:
=MAXIFS(price,color,G6,item,H6)
Забележка: MAXIFS автоматично ще игнорира празни клетки, които отговарят на критериите. С други думи, MAXIFS няма да третира празните клетки, които отговарят на критериите, като нула. От друга страна, MAXIFS ще върне нула (0), ако нито една клетка не отговаря на критериите.
