Формула Excel MAX IF, чтобы найти наибольшее значение с условиями
В статье показано несколько различных способов получить максимальное значение в Excel на основе одного или нескольких указанных вами условий.
В нашем предыдущем руководстве мы рассмотрели распространенное использование функции MAX, которая предназначена для возврата наибольшего числа в наборе данных. Однако в некоторых ситуациях вам может потребоваться углубиться в свои данные, чтобы найти максимальное значение на основе определенных критериев. Это можно сделать с помощью нескольких различных формул, и в этой статье объясняются все возможные способы.
Формула Excel МАКС ЕСЛИ
До недавнего времени в Microsoft Excel не было встроенной функции МАКС. ЕСЛИ для получения максимального значения в зависимости от условий. С введением MAXIFS в Excel 2019 мы можем легко выполнять условное максимальное значение.
В Excel 2016 и более ранних версиях вам все равно придется создавать собственную формулу массива, комбинируя функцию MAX с оператором IF:
{=МАКС(ЕСЛИ(критерии_диапазонзнак равнокритерии, максимальный_диапазон))}
Чтобы увидеть, как эта общая формула MAX IF работает с реальными данными, рассмотрим следующий пример. Предположим, у вас есть таблица с результатами прыжков в длину нескольких учеников. В таблицу включены данные по трем раундам, и вы ищете лучший результат конкретного спортсмена, скажем Якова. С именами учащихся в A2:A10 и расстояниями в C2:C10 формула принимает следующий вид:
=МАКС(ЕСЛИ(A2:A10=”Иаков”, C2:C10))
Помните, что формулу массива всегда нужно вводить, одновременно нажимая клавиши Ctrl + Shift + Enter. В результате он автоматически обрамляется фигурными скобками, как показано на скриншоте ниже (набор фигурных скобок вручную не работает!).
В реальных рабочих листах критерий удобнее вводить в какую-то ячейку, чтобы можно было легко изменить условие, не меняя формулу. Итак, набираем нужное имя в F1 и получаем следующий результат:
=МАКС(ЕСЛИ(A2:A10=F1, C2:C10))
Как работает эта формула
В логическом тесте функции ЕСЛИ мы сравниваем список имен (A2:A10) с целевым именем (F1). Результатом этой операции является массив значений ИСТИНА и ЛОЖЬ, где значения ИСТИНА представляют имена, совпадающие с целевым именем (Джейкоб):
{ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ИСТИНА; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ}
Для значение_ если_истина аргумент, мы предоставляем результаты длинного перехода (C2:C10), поэтому, если логический тест оценивается как TRUE, возвращается соответствующее число из столбца C. значение_ если_ложь аргумент опущен, то есть будет иметь значение FALSE, если условие не выполняется:
{ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;5.48;5.42;5.57;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ}
Этот массив передается функции MAX, которая возвращает максимальное число, игнорируя значения FALSE.
Кончик. Чтобы просмотреть внутренние массивы, описанные выше, выберите соответствующую часть формулы на листе и нажмите клавишу F9. Чтобы выйти из режима оценки формулы, нажмите клавишу Esc.
Формула MAX IF с несколькими критериями
В ситуации, когда вам нужно найти максимальное значение на основе более чем одного условия, вы можете:
Используйте вложенные операторы IF, чтобы включить дополнительные критерии:
{=МАКС(ЕСЛИ(критерии_диапазон1знак равнокритерии1ЕСЛИ(критерии_диапазон2знак равнокритерии2, максимальный_диапазон)))}
Или обработайте несколько критериев, используя операцию умножения:
{=МАКС(ЕСЛИ((критерии_диапазон1знак равнокритерии1) * (критерии_диапазон2знак равнокритерии2), максимальный_диапазон))}
Допустим, у вас есть результаты юношей и девушек в одной таблице и вы хотите найти самый длинный прыжок среди девушек в 3 туре. Для этого вводим первый критерий (женский) в G1, второй критерий (3) в G2 и используйте следующие формулы для определения максимального значения:
=МАКС(ЕСЛИ(B2:B16=G1, ЕСЛИ(C2:C16=G2, D2:D16)))
=МАКС(ЕСЛИ((B2:B16=G1)*(C2:C16=G2), D2:D16))
Поскольку обе формулы являются формулами массива, не забудьте нажать Ctrl + Shift + Enter, чтобы заполнить их правильно.
Как показано на снимке экрана ниже, формулы дают одинаковый результат, поэтому какую из них использовать, зависит от ваших личных предпочтений. Для меня формулу с булевой логикой легче читать и строить — она позволяет добавлять сколько угодно условий без вложения дополнительных функций ЕСЛИ.
Как работают эти формулы
Первая формула использует две вложенные функции ЕСЛИ для оценки двух критериев. В логической проверке первого оператора IF мы сравниваем значения в столбце «Пол» (B2:B16) с критерием в G1 («Женский»). Результатом является массив значений TRUE и FALSE, где TRUE представляет данные, соответствующие критерию:
{ ЛОЖНЫЙ; ЛОЖНЫЙ; ЛОЖНЫЙ; УГРОЖАТЬ; УГРОЖАТЬ; УГРОЖАТЬ; ЛОЖНЫЙ; ЛОЖНЫЙ; ЛОЖНЫЙ; ЛОЖНЫЙ; ЛОЖНЫЙ; ЛОЖНЫЙ; УГРОЖАТЬ; УГРОЖАТЬ; УГРОЖАТЬ}
Аналогичным образом вторая функция ЕСЛИ проверяет значения в столбце округления (C2:C16) на соответствие критерию в G2.
Для значение_если_истина аргумент во втором операторе IF, мы предоставляем результаты прыжка в длину (D2:D16), и таким образом мы получаем элементы, которые имеют TRUE в первых двух массивах в соответствующих позициях (т. е. элементы, где пол «женский» и круглые равно 3):
{ЛОЖНЫЙ; ЛОЖНЫЙ; ЛОЖНЫЙ; ЛОЖНЫЙ; ЛОЖНЫЙ; 4,63; ЛОЖНЫЙ; ЛОЖНЫЙ; ЛОЖНЫЙ; ЛОЖНЫЙ; ЛОЖНЫЙ; ЛОЖНЫЙ; ЛОЖНЫЙ; ЛОЖНЫЙ; 4.52}
Этот последний массив передается функции MAX, и она возвращает наибольшее число.
Вторая формула оценивает одни и те же условия в рамках одного логического теста, а операция умножения работает как оператор И:
Когда значения TRUE и FALSE используются в любой арифметической операции, они преобразуются в 1 и 0 соответственно. А поскольку умножение на 0 всегда дает ноль, результирующий массив имеет 1 только тогда, когда все условия ИСТИННЫ. Этот массив оценивается в логической проверке функции ЕСЛИ, которая возвращает расстояния, соответствующие элементам 1 (ИСТИНА).
МАКС. ЕСЛИ без массива
Многие пользователи Excel, в том числе и я, предвзято относятся к формулам массивов и стараются по возможности избавиться от них. К счастью, в Microsoft Excel есть несколько функций, которые изначально обрабатывают массивы, и мы можем использовать одну из таких функций, а именно СУММПРОИЗВ, как своего рода «оболочку» вокруг MAX.
Общая формула MAX IF без массива выглядит следующим образом:
=СУММПРОИЗВ(МАКС((критерии_диапазон1знак равнокритерии1) * (критерии_диапазон2знак равнокритерии2) * максимальный_диапазон))
Естественно, при необходимости вы можете добавить больше пар диапазон/критерий.
Чтобы увидеть формулу в действии, мы будем использовать данные из предыдущего примера. Цель состоит в том, чтобы получить максимальный прыжок спортсменки в раунде 3:
=СУММПРОИЗВ(МАКС(((B2:B16=G1) * (C2:C16=G2) * (D2:D16))))
Эта формула заменяется обычным нажатием клавиши Enter и возвращает тот же результат, что и формула массива MAX IF:
Присмотревшись к приведенному выше снимку экрана, вы можете заметить, что недопустимые переходы, отмеченные знаком «x» в предыдущих примерах, теперь имеют 0 значений в строках 3, 11 и 15, и в следующем разделе объясняется, почему.
Как работает эта формула
Как и в случае с формулой МАКС. ЕСЛИ, мы оцениваем два критерия, сравнивая каждое значение в столбцах «Пол» (B2:B16) и «Округление» (C2:C16) с критериями в ячейках G1 и G2. Результатом являются два массива значений TRUE и FALSE. Умножение элементов массивов в одинаковых позициях преобразует ИСТИНА и ЛОЖЬ в 1 и 0 соответственно, где 1 представляет элементы, соответствующие обоим критериям. Третий умноженный массив содержит результаты прыжков в длину (D2:D16). И поскольку умножение на 0 дает ноль, выживают только элементы, имеющие 1 (ИСТИНА) в соответствующих позициях:
{0; 0; 0; 0; 0; 4,63; 0; 0; 0; 0; 0; 0; 0; 0; 4.52}
В случае максимальный_диапазон содержит любое текстовое значение, операция умножения возвращает ошибку #ЗНАЧ, из-за которой вся формула не работает.
Функция MAX берет его отсюда и возвращает наибольшее число, удовлетворяющее заданным условиям. Результирующий массив, состоящий из одного элемента {4.63}, поступает в функцию СУММПРОИЗВ и выводит максимальное число в ячейке.
Примечание. Из-за своей специфической логики формула работает со следующими оговорками:
- Диапазон, в котором вы ищете наибольшее значение, должен содержать только числа. Если есть какие-либо текстовые значения, #VALUE! возвращается ошибка.
- Формула не может оценить условие «не равно нулю» в отрицательном наборе данных. Чтобы найти максимальное значение без учета нулей, используйте либо формулу МАКС. ЕСЛИ, либо функцию МАКС.
Формула Excel MAX IF с логикой ИЛИ
Чтобы найти максимальное значение при выполнении любого из указанных условий, используйте уже знакомую формулу массива МАКС ЕСЛИ с булевой логикой, но сложите условия, а не перемножайте их.
{=МАКС(ЕСЛИ((критерии_диапазон1знак равнокритерии1) + (критерии_диапазон2знак равнокритерии2), максимальный_диапазон))}
Кроме того, вы можете использовать следующую формулу без массива:
=СУММПРОИЗВ(МАКС(((критерии_диапазон1знак равнокритерии1) + (критерии_диапазон2знак равнокритерии2)) * максимальный_диапазон))
Для примера вычислим лучший результат в раундах 2 и 3. Обратите внимание, что в языке Excel задача формулируется иначе: вернуть максимальное значение, если раунд либо 2, либо 3.
С раундами, перечисленными в B2:B10, результатами в C2:C10 и критериями в F1 и H1, формула выглядит следующим образом:
=МАКС(ЕСЛИ((B2:B10=F1) + (B2:B10=H1), C2:C10))
Введите формулу, нажав комбинацию клавиш Ctrl + Shift + Enter, и вы получите такой результат:
Максимальное значение с теми же условиями также можно найти с помощью этой формулы без массива:
=СУММПРОИЗВ(МАКС(((B2:B10=F1) + (B2:B10=H1)) * C2:C10))
Однако в этом случае нам нужно заменить все значения «x» в столбце C нулями, потому что СУММПРОИЗВ МАКС работает только с числовыми данными:
Как работают эти формулы
Формула массива работает точно так же, как МАКС. ЕСЛИ с логикой И за исключением того, что вы соединяете критерии, используя операцию сложения вместо умножения. В формулах массива сложение работает как оператор ИЛИ:
Сложение двух массивов ИСТИНА и ЛОЖЬ (которые получаются в результате проверки значений в B2:B10 по критериям в F1 и H1) дает массив из 1 и 0, где 1 представляет элементы, для которых любое условие является ИСТИННЫМ, а 0 представляет элементы. для которого оба условия ЛОЖНЫ. В результате функция ЕСЛИ “сохраняет” все элементы в C2:C10 (значение_если_истина), для которого любое условие ИСТИННО (1); остальные элементы заменяются на FALSE, потому что значение_если_ложь аргумент не указан.
Формула без массива работает аналогичным образом. Разница в том, что вместо логического теста IF вы умножаете элементы массива 1 и 0 на элементы массива результатов прыжка в длину (C2:C10) в соответствующих позициях. Это аннулирует элементы, которые не соответствуют ни одному условию (имеют 0 в первом массиве), и сохраняет элементы, которые соответствуют одному из условий (имеют 1 в первом массиве).
MAXIFS — простой способ найти максимальное значение с условиями
Пользователи Excel 2019, 2021 и Excel 365 избавлены от необходимости приручать массивы для создания собственной формулы MAX IF. Эти версии Excel предоставляют долгожданную функцию MAXIFS, которая упрощает поиск наибольшего значения в условиях детской игры.
В первом аргументе MAXIFS вы вводите диапазон, в котором должно быть найдено максимальное значение (в нашем случае D2:D16), а в последующих аргументах вы можете ввести до 126 пар диапазон/критерий. Например:
=МАКСЕСЛИ(D2:D16, B2:B16, G1, C2:C16, G2)
Как показано на снимке экрана ниже, у этой простой формулы нет проблем с обработкой диапазона, содержащего как числовые, так и текстовые значения:
Подробную информацию об этой функции см. в разделе Функция MAXIFS в Excel с примерами формул.
Вот как вы можете найти максимальное значение с условиями в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Практическая рабочая тетрадь для скачивания
Примеры формулы Excel MAX IF (файл .xlsx)