Загальна формула
(=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, значення в названому діапазоні "color" (B6: B14) перевіряються зі значенням у комірці G6, "red". Результат - такий масив:
(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)
Повертається ціна на кожен товар у цьому діапазоні лише тоді, коли результат перших двох масивів, наведених вище, становить TRUE для предметів у відповідних позиціях. У наведеному прикладі остаточний масив всередині MAX виглядає так:
(11;8;9;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Зверніть увагу, що єдиними цінами, які "виживають", є ціни, коли колір "червоний", а товар - "капелюх".
Потім функція MAX повертає найвищу ціну, автоматично ігноруючи FALSE значення.
Альтернативний синтаксис із використанням логічної логіки
Ви також можете використовувати наступну формулу масиву, яка використовує лише одну функцію IF разом із логічною логікою:
(=MAX(IF((color=G6)*(item=H6),price)))
Перевага цього синтаксису полягає в тому, що простіше додавати додаткові критерії, не додаючи додаткові вкладені функції IF. Якщо вам потрібна логіка АБО, використовуйте додавання замість множення між умовами.
З MAXIFS
Функція MAXIFS, представлена в Excel 2016, призначена для обчислення максимумів на основі одного або декількох критеріїв без необхідності формули масиву. Для MAXIFS формула в I6 така:
=MAXIFS(price,color,G6,item,H6)
Примітка: MAXIFS автоматично ігнорує порожні клітинки, які відповідають критеріям. Іншими словами, MAXIFS не буде розглядати порожні комірки, які відповідають критеріям, як нуль. З іншого боку, MAXIFS поверне нуль (0), якщо жодна комірка не відповідає критеріям.
