Функция MAXIFS в Excel

Функция MAXIFS в Excel возвращает наибольшее числовое значение в ячейках, соответствующих нескольким условиям, называемым критериями. Для определения критериев MAXIFS поддерживает логические операторы (>,<,<>,=) и подстановочные знаки (*,?,~) и может применять условия к ячейкам, содержащим даты, числа и текст.

Что делает Функция MAXIFS в Excel?

Функция MAXIFS в Excel возвращает наибольшее значение в ячейках, соответствующих нескольким условиям.

Возвращаемое значение

Максимальное значение.

Аргументы

  • max_range — диапазон значений, используемый для определения максимума.
  • range1 — первый диапазон для оценки.
  • criteria1 — критерии для использования в диапазоне 1.
  • range2 — [необязательно] Второй диапазон для оценки.
  • criteria2 – [необязательно] Критерии, используемые в диапазоне2.

Синтаксис

=MAXIFS(max_range, range1, criteria1, [range2], [criteria2], …)

Примечания по использованию

Функция MAXIFS возвращает наибольшее числовое значение в ячейках, соответствующих нескольким условиям, называемым критериями. Для каждого условия предусмотрен отдельный диапазон и критерии. Для определения критериев MAXIFS поддерживает различные логические операторы (>,<,<>,=) и подстановочные знаки (*,?,~). Синтаксис, используемый для применения критериев, немного сложен, поскольку он необычен для Excel.

Синтаксис

Синтаксис функции MAXIFS зависит от оцениваемых критериев. Для каждого условия предусмотрен отдельный диапазон и критерии. Общий синтаксис выглядит следующим образом:

=MAXIFS(max_range,range1,criteria1) // 1 условие
=MAXIFS(max_range,range1,criteria1,range2,criteria2) // 2 условия

Функция MAXIFS принимает три обязательных аргумента: max_range, range1 и Criteria1. С этими тремя аргументами MAXIFS возвращает максимальное число в  max_range, где соответствующие ячейки в диапазоне1 соответствуют условию, установленному критерием1.

Функция MAXIFS в Excel

Дополнительные условия применяются с использованием пар диапазон/критерий. Второе условие определяется  диапазоном2 и критериями2, третье условие —  диапазоном3 и критериями3 и так далее. MAXIFS может обрабатывать до 126 пар диапазон/критерий.

При использовании MAXIFS имейте в виду следующее:

  • Каждое новое условие требует отдельного диапазона и критериев.
  • Чтобы включиться в окончательный результат, все условия должны быть TRUE.
  • Если ни одна ячейка не соответствует заданным критериям, функция вернет ноль (0).
  • Функция автоматически игнорирует пустые ячейки, соответствующие заданным критериям.
  • MAXIFS требует диапазона ячеек для аргументов диапазона; вы не можете использовать массив.
  • Вернет #VALUE! ошибка, если размер критерия_диапазона не совпадает с размером max_range.

Критерии

Функция MAXIFS поддерживает логические операторы (>,<,<>,=) и подстановочные знаки (*,?) для частичного сопоставления. Поскольку MAXIFS входит в группу из восьми функций, которые разделяют логические критерии на две части, синтаксис немного сложен. Для каждого условия требуется отдельный диапазон и критерии, а операторы необходимо заключать в двойные кавычки («»). В таблице ниже показаны некоторые распространенные примеры:

Цель Критерии
Клетки больше 75 «>75»
Ячейки равны 100 100 или «100»
Ячейки меньше или равны 100 «<=100»
Ячейки, равные «красному» «красный»
Ячейки, не равные «красному» «<>красный»
Ячейки пустые «» «»
Ячейки, которые не пусты «<>»
Ячейки, начинающиеся с «X» «X*»
Ячейки меньше A1 «<«&A1
Клеток меньше, чем сегодня «<«&СЕГОДНЯ()

Обратите внимание, что в последних двух примерах используется конкатенация с символом амперсанда (&). Если аргумент критерия включает значение из другой ячейки или результат формулы, логические операторы, такие как «<», должны быть объединены конкатенацией. Это связано с тем, что Excel необходимо сначала оценить ссылки на ячейки и формулы, чтобы получить значение, прежде чем это значение можно будет присоединить к оператору.

Базовый пример

На листе, показанном выше, формулы в G5 и G6:

=MAXIFS(D5:D16,C5:C16,"F") // возвращает 93
=MAXIFS(D5:D16,C5:C16,"M") // возвращает 83

В первой формуле MAXIFS возвращает максимальное значение в D5:D16, где C5:C16 равно «F» (93). Во второй формуле возвращается максимальное значение в D5:D16, где C5:C16 равно «M» (83).

Два критерия

В приведенном ниже примере функция MAXIFS используется с двумя критериями: одним для пола и одним для группы. Условия примечания добавляются в парах диапазон/критерий. Диапазон E5:E16 сочетается с условием «B».

Формулы в H5:I6:

H5=MAXIFS(D5:D16,C5:C16,"F",E5:E16,"A") // возвращает 93
I5=MAXIFS(D5:D16,C5:C16,"F",E5:E16,"B") // возвращает 85
H6=MAXIFS(D5:D16,C5:C16,"M",E5:E16,"A") // возвращает 83
I6=MAXIFS(D5:D16,C5:C16,"M",E5:E16,"B") // возвращает 79

Другие критерии

Чтобы вернуть максимальное значение в A1:A100, когда ячейки в B1:B100 больше 50:

=MAXIFS(A1:A100,B1:B100,">50")

Чтобы получить максимальное значение в A1:A100, когда ячейки в B1:B100 меньше или равны 100, а ячейки в C1:C100 больше нуля:

=MAXIFS(A1:A100,B1:B100,"<=100",C1:C100,">0")

Не равно

Чтобы создать критерий «не равно», используйте оператор «<>», заключенный в двойные кавычки («»). Например, чтобы вернуть максимальное значение в A1:A100, когда ячейки в B1:B100 не равны «красному»:

=MAXIFS(A1:A100,B1:B100,"<>red")

Значение из другой ячейки

При использовании значения из другой ячейки в условии ссылка на ячейку должна быть объединена с оператором. Например, чтобы вернуть максимальное значение в A1:A100, когда ячейки в B1:B100 больше значения в C1:

=MAXIFS(A1:A100,B1:B100,">"&C1)

Обратите внимание, что оператор «больше» (>) заключен в кавычки («»), а ссылка на ячейку (C1) — нет.

Подстановочные знаки

В критериях можно использовать подстановочные знаки: вопросительный знак (?), звездочку (*) или тильду ( ~ ) . Вопросительный знак (?) соответствует любому одному символу, а звездочка (*) соответствует нулю или более символам. Например, чтобы вернуть максимальное значение в A1:A100, если ячейки в B1:B100 начинаются с «a»:

=MAXIFS(A1:A100,B1:B100,"a*")

Тильда (~) — это escape-символ, позволяющий находить буквальные подстановочные знаки. Например, чтобы соответствовать буквальному вопросительному знаку (?), звездочке (*) или тильде (~), добавьте тильду перед подстановочным знаком (т. е. ~?, ~*, ~~).

Примечания

  • Условия применяются с использованием пар диапазон/критерий.
  • MAXIFS в эксель вернет ошибку #VALUE, если размер какого-либо диапазона критериев не совпадает с размером  max_range.
  • Если ни один критерий не соответствует, функция вернет ноль (0).
  • Функция игнорирует пустые ячейки, соответствующие критериям.
Автор А. Днепров

Обучаю основам работы с офисными программами.

Функция MAXIFS в Excel
Как добавить подписи к изображениям в PowerPoint