MAXIFS и MINIFS в Excel: полное руководство с практическими примерами

Что вы получите из этой статьи

• Понимание синтаксиса MAXIFS и MINIFS и аргументов функций.
• Пошаговые примеры: одно условие, несколько условий, сравнения по датам и тексту.
• Подсказки по логическим операторам и их использованию в критериях.
• Альтернативы для старых версий Excel и методика создания надёжных формул.
• Чеклист, ошибки, случаи, когда функции не подходят, и небольшая методология тестирования.

Введение и требования

MAXIFS возвращает максимальное значение из указанного диапазона, которое соответствует одному или нескольким условиям. MINIFS — аналогично для минимального значения.

Важно: эти функции появились в Office 365 и Office 2019. Если у вас более старая версия Excel, используйте комбинации с массивными формулами (Ctrl+Shift+Enter в старых версиях), агрегатные функции с ПОМОЩЬЮ helper-столбцов или комбинации INDEX/AGGREGATE/IF.

Кратко:

• Типы поддерживаемых данных: числа, текст, даты.
• Условия можно комбинировать (AND — перечисление пар диапазон/критерий; OR решается внешними приёмами).

Практическая таблица данных (пример)

Ниже — образец данных, который будем использовать в примерах. Даты приведены в привычном для России формате, а формулы в примерах сохранены в оригинальном виде, чтобы соответствовать ожидаемому формату в исходной статье.

Важно: в таблице даты и числа отформатированы для чтения; фактические значения в вашей книге Excel должны быть типами «Дата» и «Число», чтобы формулы работали корректно.

Синтаксис функций

MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Обязательные аргументы:

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

Дополнительные пары диапазон/критерий можно добавлять по необходимости. Все пары критериев объединяются логикой AND: значение выбирается только если удовлетворяются все пары.

Ключевые замечания по аргументам

• Диапазоны критериев должны иметь ту же форму (количество строк/столбцов), что и диапазон результата, иначе формула вернёт ошибку.
• Если критерий ссылается на текст, Excel сравнивает строки без учёта регистра (регистрозависимость не применяется в обычных критериях).
• Для логики OR используйте несколько формул или вспомогательные столбцы/массивы.

Примеры: работа с числами

Одно условие

Задача: найти максимальную и минимальную прибыль для подкатегории Tables.

Формулы (в соответствии с исходными диапазонами в примере):

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

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

Объяснение:

• F2:F14 — диапазон прибыли, из которого берём результат.
• D2:D14 — столбец «Подкатегория», где ищем значение “Tables”.

Результаты в примере: максимальная прибыль = 85.3092, минимальная прибыль = -383.031.

Несколько условий (логика AND)

Задача: найти максимальные и минимальные продажи для категории Furniture в штате California.

Формулы:

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

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

Объяснение:

• E2:E14 — диапазон «Продажи», откуда берём максимальное/минимальное значение.
• B2:B14 — столбец «Город», условие “California”.
• C2:C14 — столбец «Категория», условие “Furniture”.

Результаты: max = 1706.184, min = 48.86.

Логические операторы в числовых критериях

Вы можете использовать операторы сравнения в строковых критериях, например “<50” или “>=100”. Примеры:

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

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

Оба примера применяют условие к самому столбцу продаж: первое возвращает максимальное значение меньше 50 (в примере 48.86), второе — минимальное значение меньше 50 (в примере 7.28).

Таблица операторов (для справки):

Важно: строковый критерий должен быть заключён в кавычки, если вы пишете оператор и число вместе, например “>02-01-2016”. Для составных критериев и ссылок на ячейки используйте конкатенацию:

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

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

Примеры: работа с датами

MAXIFS и MINIFS хорошо подходят для фильтрации по диапазону дат.

Пример: найти максимальные и минимальные продажи после 02-01-2016 (в представленных формулах дата записана как в оригинале):

=MAXIFS(E2:E14, A2:A14, ">02-01-2016")

=MINIFS(E2:E14, A2:A14, ">02-01-2016")

В примере первое выражение возвращает 731.94 (максимум после указанной даты), второе — 14.62 (минимум).

Чтобы задать диапазон дат (между двумя датами), используйте две пары критериев для столбца дат:

=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")

В этом диапазоне максимальное значение = 957.5775, минимальное = 14.62.

Примечание: при использовании критериев с датами лучше ссылаться на ячейки с типом “Дата” и использовать конкатенацию: “>” & G1, где G1 — дата. Это убирает неоднозначности форматов дат.

Примеры: работа с текстом

MAXIFS и MINIFS принимают текстовые критерии. Пример: найти максимум и минимум продаж для города California:

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

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

Результаты: max = 1706.184, min = 7.28.

Совет: Excel выполняет сравнение текста без учёта регистра. Но пробелы и невидимые символы повлияют на результат — используйте TRIM и CLEAN в предварительной обработке, если данные не чистые.

Частые ошибки и причины неверных результатов

• Несоответствие размеров диапазонов (ошибка VALUE/#REF). Все диапазоны должны иметь одинаковую форму.
• Неправильный тип данных: даты хранятся как текст — сравнения не работают как ожидалось.
• Пробелы и невидимые символы в текстовых полях — критерий не совпадает.
• Неучтённая логика OR: многие пытаются просто перечислить варианты через запятую, но MAXIFS/MINIFS по умолчанию работают как AND. Для OR используйте SUMPRODUCT/AGGREGATE/массивы или объединяйте результаты нескольких формул.

Примеры неправильного применения

• Использование диапазона результата, который шире, чем диапазоны критериев (вернёт ошибку).
• Ожидание, что MINIFS вернёт «самую раннюю дату», если столбец результата содержит текст дат — даты должны быть в формате даты.

Когда MAXIFS и MINIFS не подходят — альтернативы

1. Список требований OR (альтернатива): если требуется найти максимум по OR-условию (например, город = A или B), вы можете использовать:

• Несколько формул и затем MAX из них: =MAX( MAXIFS(…условие A…), MAXIFS(…условие B…))
• AGGREGATE/ARRAY/INDEX+MATCH при продвинутых сценариях.
• В старых версиях Excel (без MAXIFS/MINIFS) используйте массивные формулы: =MAX(IF(range_crit=значение, range_result)) — вводить с Ctrl+Shift+Enter.

2. Большие таблицы и производительность: при огромных объёмах данных вычисления с большим количеством условий могут быть медленными. Решение — вспомогательные столбцы, Power Query или сводные таблицы.

3. Сложные условия с текстовыми шаблонами (LIKE): используйте комбинирование функций SEARCH/ISNUMBER вместе с AGGREGATE/INDEX.

Ментальные модели и эвристики (как думать о задачах)

• Модель “фильтр → агрегат”: представьте MAXIFS/MINIFS как операцию «отфильтровать строки по условиям, затем выбрать максимум/минимум по столбцу X». Это упрощает построение формул и отладку.
• Всегда проверяйте, что столбец, из которого берётся результат, действительно содержит ожидаемый тип данных.
• Начинайте с одного условия; если результат верный — добавляйте условия по одному, проверяя промежуточный результат.

Мини-методология для создания надёжной формулы

1. Убедитесь, что источники данных отформатированы правильно (дата/число/текст).
2. Проверьте диапазоны: выделите их и посчитайте количество строк/столбцов.
3. Постройте формулу с одним условием и проверьте результат вручную на 3–5 строках.
4. Добавляйте условия по одному.
5. Если нужны OR-условия — используйте вспомогательные формулы или массивы и документируйте логику.
6. Напишите короткий тестовый набор (см. раздел «Тесты» ниже) и прогоняйте после изменений.

Чеклист для ролей (краткие инструкции)

Аналитик данных:

• Проверьте чистоту данных (TRIM, VALUE для чисел, DATEVALUE для текста-даты).
• Оформите диапазоны как таблицу Excel (Ctrl+T), используйте структурированные ссылки.
• Документируйте все допущения (например, что пустые значения — это 0 или пропуск).

BI-разработчик / администратор:

• Для больших массивов данных предпочтите Power Query / Power Pivot.
• Рассмотрите использование агрегированных колонок или предварительных фильтров на уровне источника.

Конечный пользователь / менеджер:

• Просите аналитика предоставить проверку границ (максимум/минимум) и пример строк, обеспечивающих результат.

Тесты и критерии приёмки

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

• Формула возвращает ожидаемый максимум/минимум при тестовых данных (перечислить 5 тестовых строк вручную).
• При добавлении строки с крайним значением формула корректно изменяет результат.
• Для диапазона дат формула корректно учитывает границы (границы включены/исключены — проверить).

Примеры тестов:

• Добавьте строку с продажами 9999 для подкатегории Tables — ожидаемый максимум должен стать 9999.
• Преобразуйте дату в текст в одной из строк — убедитесь, что эта строка не влияет на результаты по датам.

Отладка и восстановление (runbook)

1. Если формула возвращает ошибку #VALUE или #REF — проверьте размеры диапазонов.
2. Если результат кажется неверным — отфильтруйте исходную таблицу по условиям и посмотрите на значения вручную.
3. Для неочевидных результатов вставляйте вспомогательные колонки, которые показывают булевы значения для каждого условия (TRUE/FALSE).
4. Для проблем с датами используйте =ISNUMBER(A2) и =TYPE(A2) для определения типа данных.

Альтернативы и миграция для старых версий Excel

• Старые версии без MAXIFS/MINIFS: используйте массивы =MAX(IF(…)) / =MIN(IF(…)). Помните о вводе Ctrl+Shift+Enter.
• Power Query: выгрузите данные, примените фильтрацию по условиям и вычислите статистику в шаге “Группировать по”.
• Сводные таблицы: добавьте фильтры по полям и посмотрите сводные метрики (максимум/минимум через настройку полей значений).

Примеры сложных сценариев

1. OR-условие по двум городам:

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

2. Максимум продаж для категории Furniture, но только если прибыль положительная:

=MAXIFS(E2:E14, C2:C14, "Furniture", F2:F14, ">0")

3. Максимальная прибыль за последние 365 дней (предполагая, что TODAY() — текущая дата):

=MAXIFS(F2:F14, A2:A14, ">" & (TODAY()-365))

1‑строчный глоссарий

• Диапазон результата: столбец с числами, из которого берём max/min.
• Диапазон критериев: столбец, где проверяем условие.
• Критерий: строка/выражение, задающее условие (например, “>100” или “California”).

Контроль качества и производительность

• При большом объёме данных используйте таблицы Excel (Structured Tables) — диапазоны автоматически расширяются при добавлении строк.
• Если формула тормозит, попробуйте:
  • Создать вспомогательные булевы столбцы с предварительно вычисленными условиями.
  • Перенести обработку в Power Query/Power Pivot.

Резюме

• MAXIFS и MINIFS — удобный и читаемый способ получать максимум/минимум при множестве условий.
• Они работают с числами, датами и текстом, но требуют аккуратности с типами данных и размерами диапазонов.
• Для OR-логики, больших наборов данных или старых версий Excel существуют надёжные альтернативы.

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

Ключевые шаги при внедрении:

1. Убедитесь в чистоте и типах данных; 2) Начинайте с одного условия; 3) Добавляйте условия по одному; 4) Тестируйте крайние случаи.

Если хотите, могу подготовить готовые формулы под вашу конкретную таблицу или преобразовать примеры под русские локальные форматы дат/чисел и региональные настройки Excel.