MAXIFS и MINIFS в Excel: руководство, примеры и советы

О чём эта статья

В этой статье вы найдёте:

• объяснение синтаксиса MAXIFS и MINIFS;
• практические примеры с числами, датами и текстом;
• варианты с логическими операторами и несколькими условиями;
• альтернативные подходы и типичные ошибки;
• чек‑листы для аналитика и разработчика отчётов;
• критерии приёмки и тест‑кейсы;
• справочный FAQ и decision‑tree, который поможет выбрать подход.

Требования к версии Excel

MAXIFS и MINIFS поддерживаются в Office 365 и Office 2019 и новее. В Excel 2016 и более старых версиях этих функций нет — используйте массивные формулы (Ctrl+Shift+Enter в старых версиях), AGGREGATE, сводные таблицы или Power Query.

Important: при локализации дат учитывайте региональные форматы (в примерах ниже даты приведены в формате ДД.ММ.ГГГГ для российского региона).

Набор данных для практики

Ниже — примерный набор данных, который мы используем в примерах. Обратите внимание на формат дат (ДД.ММ.ГГГГ).

Факт‑бокс — ключевые значения из набора данных:

• Максимальный Sales: 1706.184
• Минимальный Sales: 7.28
• Максимальная прибыль (Profit): 219.582
• Минимальная прибыль (Profit): -383.031

Синтаксис и базовая логика

MAXIFS и MINIFS имеют очень похожие синтаксисы:

MAXIFS(max_range, criteria_range1, criteria1, ...)

MINIFS(min_range, criteria_range1, criteria1, ...)

Кратко о аргументах:

• max_range / min_range: диапазон, из которого возвращается значение (числа).
• criteria_range1: диапазон, который проверяется на соответствие условию.
• criteria1: условие, которое применяется к criteria_range1.
• Допускается несколько пар criteria_rangeN/criteriaN.

Правило: все criteria_rangeN должны иметь одинаковую длину с max_range/min_range.

Работа с числами

MAXIFS и MINIFS отлично подходят для поиска экстремумов среди числовых значений при учёте условий.

Один критерий

Найдём максимальную прибыль для подкатегории “Tables”:

=MAXIFS(F2:F14, D2:D14, "Tables")

Аналогично, минимальная прибыль:

=MINIFS(F2:F14, D2:D14, "Tables")

По данным примера: максимальная прибыль в Tables = 85.3092, минимальная = -383.031.

Несколько критериев

Максимальные и минимальные продажи для Furniture в штате California:

=MAXIFS(E2:E14, B2:B14, "California", C2:C14, "Furniture")

=MINIFS(E2:E14, B2:B14, "California", C2:C14, "Furniture")

Смысл: Excel фильтрует строки, где B = “California” и C = “Furniture”, затем берёт максимум/минимум по E.

Логические операторы с числами

Операторы включаются как строковые критерии:

=MAXIFS(E2:E14, E2:E14, "<50")

=MINIFS(E2:E14, E2:E14, "<50")

Первый вернёт максимальные продажи меньше 50 (48.86), второй — минимальные продажи меньше 50 (7.28).

Совет: при использовании числовых сравнений внутри критериев CONCATENATE или & пригодятся для ссылок на ячейки:

=MAXIFS(E2:E14, A2:A14, ">" & G1)

где G1 содержит дату или число.

Работа с датами

MAXIFS и MINIFS работают с датами как с числами (датой является числовое значение в Excel). Примеры:

=MAXIFS(E2:E14, A2:A14, ">02.01.2016")

=MINIFS(E2:E14, A2:A14, ">02.01.2016")

В этих формулах Excel вернёт максимальное/минимальное значение Sales для заказов после 02.01.2016. Чтобы задать диапазон дат:

=MAXIFS(E2:E14, A2:A14, ">02.01.2015", A2:A14, "<02.01.2017")

=MINIFS(E2:E14, A2:A14, ">02.01.2015", A2:A14, "<02.01.2017")

Важно: при сравнении дат надёжнее использовать ссылки на ячейки с датой и объединение (&), чтобы избежать проблем с локалью:

=MAXIFS(E2:E14, A2:A14, ">" & DATE(2016,1,2))

или

=MAXIFS(E2:E14, A2:A14, ">" & G1)

где G1 содержит дату как значение.

Работа с текстом

Текстовые критерии — прямое совпадение или подстановочные знаки (*, ?). Пример — макс и мин Sales для города California:

=MAXIFS(E2:E14, B2:B14, "California")

=MINIFS(E2:E14, B2:B14, "California")

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

=MAXIFS(E2:E14, D2:D14, "Book*")  // любая подкатегория, начинающаяся с Book

Важно: критерии по тексту не учитывают регистр — “California” и “california” будут эквивалентны.

Когда MAXIFS и MINIFS дают неожиданные результаты

Common pitfalls:

• Несовпадение размеров диапазонов: max_range и все criteria_range должны быть одной длины. Иначе — #VALUE!.
• Пустые ячейки и текст вместо числа: если max_range содержит текст, результат будет игнорировать эти строки; если нет подходящих значений — возвращается 0 или ошибка в зависимости от контекста.
• Локаль дат: прямое указание дат в критериях (“<02-01-2016”) может интерпретироваться по локали. Надёжнее использовать DATE() или ссылки на ячейки.
• Пустые строки в критериях: критерий пустой строки (“”) и критерий “<>” ведут себя по-разному.
• Неявные преобразования типов: например, числа, записанные как текст, не будут учитываться в numeric max_range.

Counterexample — когда функция не подходит:

• При необходимости вернуть максимально возможную дату/значение после сложной фильтрации по выражению, которое нельзя легко выразить в парах диапазон/критерий — лучше использовать FILTER (Office 365) и затем MAX.
• Когда нужно вернуть не значение, а строку с контекстом (например, строку с максимальной продажей) — используйте INDEX+MATCH или XLOOKUP/INDEX with MATCH on MAX(IF()).

Альтернативные подходы

1. Массивная формула (для старых версий Excel):

=MAX(IF(D2:D14="Tables", F2:F14))

(в старых версиях — ввод с Ctrl+Shift+Enter). Эта формула выполняет фильтрацию через IF и берёт максимум.

2. AGGREGATE: можно использовать для игнорирования ошибок и скрытых строк.

3. FILTER + MAX (Office 365):

=MAX(FILTER(F2:F14, D2:D14="Tables"))

4. Сводная таблица: быстрый визуальный способ получить максимум/минимум по группам.

5. Power Query: удобен для больших наборов данных и повторяемой очистки/агрегации.

Советы по отладке

• Проверьте размеры диапазонов: используйте NAME MANAGER или выделение диапазонов мышью.
• Для проверки условий вставляйте вспомогательный столбец с логикой (например, =D2=”Tables”), чтобы увидеть, какие строки попадают в фильтр.
• При проблемах с датами заменяйте строковые условия на “>” & DATE(…).
• Используйте TRACE PRECEDENTS/ERROR CHECKING в Excel для поиска источника ошибки.

Чек‑лист ролей

Аналитик:

• Убедиться, что диапазоны одинаковой длины.
• Проверить типы данных (даты как даты, числа как числа).
• Протестировать формулу на небольшой выборке.

Разработчик отчетов / BI:

• Предусмотреть обработку пустых и некорректных значений.
• Автоматизировать проверочные тесты (см. Критерии приёмки).
• Рассмотреть FILTER/Power Query для масштабируемости.

Менеджер данных:

• Настроить стандарт форматов (даты, валюта).
• Задокументировать используемые формулы и допущения.

Критерии приёмки

Минимальные тесты, которые должна проходить формула:

1. Формула возвращает ожидаемый максимум/минимум на эталонном наборе данных (см. факт‑бокс).
2. Если критерий не соответствует ни одной строке — поведение задокументировано (ожидаемое: 0, пусто или #N/A).
3. Для диапазонов разных размеров возвращается #VALUE! — это проверяемый тест.
4. При использовании ссылок на даты формула должна корректно работать при смене региональных настроек (тест с DATE()).

Примеры тест‑кейсов:

• Тест 1: =MAXIFS(E2:E14, B2:B14, “California”) → 1706.184
• Тест 2: =MINIFS(E2:E14, D2:D14, “Art”) → 7.28
• Тест 3: =MAXIFS(E2:E14, E2:E14, “<50”) → 48.86

Шаблоны и сниппеты формул

Ниже — набор полезных шаблонов, которые можно копировать:

• Максимум по одному критерию:

=MAXIFS(value_range, criteria_range, criteria)

• Минимум по двум критериям:

=MINIFS(value_range, criteria_range1, criteria1, criteria_range2, criteria2)

• Максимум для диапазона дат (с ячейками F1 и G1 как даты):

=MAXIFS(E2:E14, A2:A14, ">" & F1, A2:A14, "<" & G1)

• Альтернатива через FILTER (Office 365):

=MAX(FILTER(E2:E14, (B2:B14="California")*(C2:C14="Furniture")))

• Вернуть строку с максимальным значением (INDEX+MATCH):

=INDEX(A2:F14, MATCH(MAXIFS(E2:E14, B2:B14, "California"), E2:E14, 0), 1)

(эта формула возвращает дату заказа, соответствующую максимальным продажам в California)

Decision‑tree: какую функцию выбрать

flowchart TD
  A[Нужен максимум/минимум с условиями?] -->|Да| B{Office 365 / 2019+}
  B -->|Да| C[MAXIFS / MINIFS]
  B -->|Нет| D{Можно ли использовать массивные формулы?}
  D -->|Да| E[MAX'IF'...'']
  D -->|Нет| F[Поверить в AGGREGATE или сводную таблицу]
  C --> G{Нужна сложная фильтрация?}
  G -->|Да| H[FILTER + MAX]
  G -->|Нет| I[ОК — MAXIFS достаточно]

Безопасность и приватность

При работе с реальными данными соблюдайте политики конфиденциальности вашей организации. Формулы не передают данные за пределы файла Excel, но подключаемые надстройки и макросы могут это делать — проверяйте разрешения.

Часто задаваемые вопросы

Поддерживаются ли эти функции в Excel Online?

Да, MAXIFS и MINIFS поддерживаются в современных версиях Excel Online, если ваш рабочий аккаунт использует актуальную облачную версию Office.

Можно ли использовать несколько критериев для одной колонки?

Да, можно добавить повторяющиеся criteria_range с разными условиями. Если нужно логическое И — перечисляйте условия в отдельных парах. Для логического ИЛИ используйте несколько вызовов функций или FILTER.

Что возвращает функция, если нет совпадений?

Если нет совпадений, поведение зависит от типа аргументов: обычно возвращается 0 или пустая строка в зависимости от контекста; лучше проверять результат через IFERROR или предусматривать правило обработки отсутствия данных.

Итог и рекомендации

MAXIFS и MINIFS — простые и надёжные средства для поиска экстремальных значений с учётом условий. Используйте их, когда: диапазоны одинаковой длины, условия выражаются через простые критерии, и у вас современная версия Excel. Для более сложных задач рассмотрите FILTER, Power Query или массивные формулы.

Короткий чек‑лист перед публикацией отчёта:

• Проверить размеры диапазонов.
• Проверить типы данных (числа/даты/текст).
• Написать тесты для граничных случаев.
• Задокументировать поведение при отсутствии данных.

Дополнительные ресурсы: используйте встроенный мастер формул, окно “Evaluate Formula” (Оценить формулу) и тестовые наборы для проверки логики.

Краткое резюме:

MAXIFS/MINIFS — быстрый путь к агрегации с условиями. Знайте ограничения, тестируйте на краевых случаях и выбирайте альтернативы при необходимости.