Функция MAXIFS в Excel — найти максимальное значение с несколькими критериями

В учебнике показано, как использовать функцию МАКСИМСЛИМН в Excel, чтобы получить максимальное значение с условиями.

Традиционно, когда вам нужно было найти наибольшее значение с условиями в Excel, вам приходилось создавать собственную формулу МАКС. ЕСЛИ. Если для опытных пользователей это не имеет большого значения, то для новичков могут возникнуть определенные трудности, поскольку, во-первых, вы должны помнить синтаксис формулы, а во-вторых, вам нужно знать, как работать с формулами массива. К счастью, Microsoft недавно представила новую функцию, которая позволяет нам легко выполнять условный максимум!

Функция Excel МАКСИФМН

Функция МАКСИСМ возвращает наибольшее числовое значение в указанном диапазоне на основе одного или нескольких критериев.

Синтаксис функции МАКСИФМ следующий:

MAXIFS(максимальный_диапазон, диапазон_критериев1, критерий1, [criteria_range2, criteria2]…)

Где:

• Max_range (обязательно) — диапазон ячеек, где вы хотите найти максимальное значение.
• Criteria_range1 (обязательно) — первый диапазон для оценки критерии1.
• Criteria1 – условие для использования в первом диапазоне. Он может быть представлен числом, текстом или выражением.
• Criteria_range2/criteria2, …(необязательно) — дополнительные диапазоны и связанные с ними критерии. Поддерживается до 126 пар диапазон/критерий.

Эта функция MAXIFS доступна в Excel 2019, Excel 2021 и Excel для Microsoft 365 в Windows и Mac.

В качестве примера найдем самого высокого футболиста в нашей местной школе. Предполагая, что рост учащихся находится в ячейках D2:D11 (max_range), а виды спорта — в B2:B11 (criteria_range1), используйте слово «футбол» в качестве критерия1, и вы получите следующую формулу:

=MAXIFS(D2:D11, B2:B11, “футбол”)

Чтобы сделать формулу более универсальной, вы можете ввести целевой вид спорта в какую-нибудь ячейку (скажем, G1) и включить ссылку на ячейку в поле критерии1 аргумент:

=МАКСЕСЛИ(D2:D11, B2:B11, G1)

Примечание. максимальный_диапазон а также критерии_диапазон аргументы должны быть одинакового размера и формы, т. е. содержать одинаковое количество строк и столбцов, иначе #ЗНАЧ! возвращается ошибка.

Как использовать функцию МАКСИМСЛИ в Excel – примеры формул

Как вы только что видели, Excel MAXIFS довольно прост и удобен в использовании. Тем не менее, у него есть несколько маленьких нюансов, которые имеют большое значение. В приведенных ниже примерах мы попытаемся максимально использовать условный максимум в Excel.

Найти максимальное значение на основе нескольких критериев

В первой части этого руководства мы создали формулу MAXIFS в ее простейшей форме, чтобы получить максимальное значение на основе одного условия. Теперь мы собираемся продолжить этот пример и оценить два разных критерия.

Предположим, вы хотите найти самого высокого баскетболиста в младшей школе. Для этого определите следующие аргументы:

• Макс_диапазон – диапазон ячеек, содержащих высоты – D2:D11.
• Criteria_range1 – диапазон ячеек, содержащих виды спорта – B2:B11.
• Критерии1 – «баскетбол», который вводится в ячейку G1.
• Criteria_range2 – диапазон ячеек, определяющий тип школы – C2:C11.
• Критерии2 – «младший», который вводится в ячейку G2.

Сложив аргументы вместе, мы получим следующие формулы:

С «жестко запрограммированными» критериями:

=MAXIFS(D2:D11, B2:B11, “баскетбол”, C2:C11, “юниор”)

С критериями в предопределенных ячейках:

=МАКСЕСЛИ(D2:D11, B2:B11, G1, C2:C11, G2)

Обратите внимание, что функция МАКСИМСЛИМН в Excel нечувствительна к регистру, поэтому вам не нужно беспокоиться о регистре букв в ваших критериях.

Если вы планируете использовать формулу в нескольких ячейках, обязательно заблокируйте все диапазоны абсолютными ссылками на ячейки, например:

=MAXIFS($D$2:$D$11, $B$2:$B$11, G1, $C$2:$C$11, G2)

Это обеспечит правильное копирование формулы в другие ячейки — ссылки на критерии изменяются в зависимости от относительного положения ячейки, в которую копируется формула, в то время как диапазоны остаются неизменными:

В качестве дополнительного бонуса я покажу вам быстрый способ извлечения значения из другой ячейки, связанной с максимальным значением. В нашем случае это будет имя самого высокого человека. Для этого мы будем использовать классическую формулу ИНДЕКС ПОИСКПОЗ и вставим МАКСИФМ в первый аргумент ПОИСКПОЗ в качестве значения поиска:

=ИНДЕКС($A$2:$A$11, MATCH(MAXIFS($D$2:$D$11, $B$2:$B$11, G1, $C$2:$C$11, G2), $D$2:$D $11, 0))

Формула говорит нам, что самого высокого баскетболиста в младшей школе зовут Лиам:

Excel MAXIFS с логическими операторами

В ситуации, когда вам нужно оценить числовые критерии, используйте логические операторы, такие как:

• больше, чем (>)
• меньше чем (<)
• больше или равно (>=)
• меньше или равно (<=)
• не равно (<>)

Оператор «равно» (=) в большинстве случаев можно опустить.

Обычно выбор оператора не представляет проблемы, самое сложное — построить критерии с правильным синтаксисом. Вот как:

• Логический оператор, за которым следует число или текст, должен быть заключен в двойные кавычки, например «>=14» или «<>running».
• В случае ссылки на ячейку или другой функции используйте кавычки, чтобы начать строку, и амперсанд, чтобы объединить ссылку и закончить строку, например, “>”&B1 или “<"&TODAY().

Чтобы увидеть, как это работает на практике, давайте добавим столбец «Возраст» (столбец C) в нашу тестовую таблицу и найдем максимальный рост среди мальчиков в возрасте от 13 до 14 лет. Это можно сделать по следующим критериям:

Критерий 1: “>=13”

Критерий 2: “<=14"

Поскольку мы сравниваем числа в одном и том же столбце, критерии_диапазона в обоих случаях одинаковы (C2:C11):

=MAXIFS(D2:D11, C2:C11, “>=13”, C2:C11, “<=14")

Если вы не хотите жестко задавать критерии в формуле, введите их в отдельные ячейки (например, G1 и H1) и используйте следующий синтаксис:

=MAXIFS(D2:D11, C2:C11, “>=”&G1, C2:C11, “<="&H1)

На скриншоте ниже показан результат:

Помимо чисел, логические операторы также могут работать с текстовыми критериями. В частности, оператор «не равно» пригодится, когда вы хотите что-то исключить из своих вычислений. Например, чтобы найти самого высокого ученика во всех видах спорта, кроме волейбола, используйте следующую формулу:

=MAXIFS(D2:D11, B2:B11, “<>волейбол”)

Или этот, где G1 — исключенный вид спорта:

=МАКСЕСЛИ(D2:D11, B2:B11, “<>“&G1)

Формулы MAXIFS с подстановочными знаками (частичное совпадение)

Чтобы оценить условие, содержащее определенный текст или символ, включите в критерий один из следующих подстановочных знаков:

• Вопросительный знак (?) для соответствия любому одиночному символу.
• Звездочка

для соответствия любой последовательности символов.

Для этого примера давайте найдем самого высокого парня в игровых видах спорта. Поскольку названия всех игровых видов спорта в нашем наборе данных заканчиваются словом «мяч», мы включаем это слово в критерии и используем звездочку для соответствия любым предыдущим символам:

=МАКСИФС(D2:D11, B2:B11, “*мяч”)

Вы также можете ввести «мяч» в какую-либо ячейку, например, G1, и соединить подстановочный знак со ссылкой на ячейку:

=МАКСЕСЛИ(D2:D11, B2:B11, “*”&G1)

Формула MAXIFS с подстановочным знаком

Получить максимальное значение в диапазоне дат

Поскольку во внутренней системе Excel даты хранятся в виде порядковых номеров, вы работаете с критериями дат так же, как и с числами. Чтобы проиллюстрировать это, мы заменим Возраст колонка с Дата рождения

и попытайтесь определить максимальный рост среди мальчиков, родившихся в определенный год, скажем, в 2004 году. Для выполнения этой задачи нам нужно «отфильтровать» даты рождения, которые больше или равны 1 января 2004 года и меньше или равно 31 декабря 2004 г.

При построении критериев важно указать даты в формате, понятном Excel:

=MAXIFS(D2:D11, C2:C11, “>=1 января 2004 г.”, C2:C11, “<=31 декабря 2004 г.")

Или же

=MAXIFS(D2:D11, C2:C11, “>=01.01.2004”, C2:C11, “<=31.12.2004")

Чтобы предотвратить неправильное толкование, имеет смысл использовать функцию DATE:

=MAXIFS(D2:D11, C2:C11, “>=”&ДАТА(2004,1,1), C2:C11, “<="&ДАТА(2004,12,31))

В этом примере мы введем целевой год в G1, а затем воспользуемся функцией DATE для указания дат:
=”&ДАТА(G1,1,1), C2:C11, “<="&ДАТА(G1,12,31))" title="Условный максимум для дат"/>

Условный максимум для дат

Примечание. В отличие от чисел даты следует заключать в кавычки, если они используются в критериях сами по себе. Например:

=МАКСИФС(D2:D11, C2:C11, “10.05.2005”)

Найдите максимальное значение на основе нескольких критериев с логикой ИЛИ Функция Excel МАКСИМС предназначена для проверки условий с помощью логики И, т. е. она обрабатывает только те числа, максимальный_диапазон

для которого все критерии ИСТИННЫ. Однако в некоторых ситуациях вам может потребоваться оценить условия с помощью логики ИЛИ, т. е. обработать все числа, для которых любой из указанных критериев является ИСТИННЫМ.

Чтобы упростить понимание, рассмотрим следующий пример. Предположим, вы хотите найти максимальный рост парней, которые играют в баскетбол или футбол. Как бы Вы это сделали? Использование «баскетбола» в качестве критерия1 и в качестве критерия «футбола»2 не будет работать, поскольку Excel предполагает, что оба критерия должны оцениваться как ИСТИНА.

Решение состоит в том, чтобы создать две отдельные формулы MAXIFS, по одной для каждого вида спорта, а затем использовать старую добрую функцию MAX, чтобы вернуть большее число:

=МАКС(МАКСЕСЛИМН(C2:C11, B2:B11, “баскетбол”), МАКСЕСЛИМН(C2:C11, B2:B11, “футбол”))

Найдите максимальное значение на основе нескольких критериев с логикой ИЛИ

Другой способ — использовать формулу МАКС. ЕСЛИ с логикой ИЛИ.

7 вещей, которые нужно помнить об Excel MAXIFS

1. Ниже вы найдете несколько замечаний, которые помогут улучшить ваши формулы и избежать распространенных ошибок. Некоторые из этих наблюдений уже обсуждались в качестве советов и примечаний в наших примерах, но может быть полезно получить краткое изложение того, что вы уже узнали:
2. Функция MAXIFS в Excel может получить наибольшее значение на основе одного или нескольких критериев.
3. По умолчанию Excel MAXIFS работает с логикой И, т. е. возвращает максимальное число, удовлетворяющее всем заданным условиям.
4. Чтобы функция работала, максимальный диапазон и диапазоны критериев должны иметь одинаковый размер и форму.
5. Функция СУММЕСЛИ нечувствительна к регистру, т.е. она не распознает регистр букв в текстовых критериях.
6. При написании формулы MAXIFS для нескольких ячеек не забудьте заблокировать диапазоны абсолютными ссылками на ячейки, чтобы формула копировалась правильно.
   • Обратите внимание на синтаксис ваших критериев! Вот основные правила:
   • При использовании отдельно текст и даты должны быть заключены в кавычки, а числа и ссылки на ячейки – нет.
7. Когда число, дата или текст используются с логическим оператором, все выражение должно быть заключено в двойные кавычки, например «>=10»; ссылки на ячейки и другие функции должны быть объединены с помощью амперсанда, такого как “>”&G1.

MAXIFS доступен только в Excel 2019 и Excel для Office 365. В более ранних версиях эта функция недоступна.

Вот как вы можете найти максимальное значение в Excel с условиями. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге в ближайшее время!

Скачать рабочую тетрадь: Примеры формул Excel MAXIFS

(файл .xlsx)

Вас также могут заинтересовать