Как найти максимальное или минимальное значение по заданным критериям в Excel?
Предположим, у вас есть диапазон данных: названия товаров указаны в столбце A, а количество заказов — в столбце B. Теперь вы хотите быстро найти максимальное или минимальное количество заказов для конкретного товара, например «KTE», как показано на скриншоте ниже. Excel предлагает несколько способов получить максимальное или минимальное значение по одному или нескольким критериям.
Поиск максимального или Минимальное значение значения на основе только одного критерия
Поиск максимального или Минимальное значение значения на основе нескольких критериев
Функции Excel MAXIFS и MINIFS (Excel 2019+)
Поиск максимального или Минимальное значение значения на основе критерия с помощью Сводная таблица
Поиск максимального или Минимальное значение значения на основе только одного критерия
Чтобы найти максимальное или минимальное значение по заданному критерию, используйте функцию МАКС или МИН в сочетании с формулой массива.
Этот метод особенно полезен, когда нужно быстро найти максимальное или минимальное значение при выполнении одного чётко заданного условия — например, определить самый продаваемый товар или наименьший объём продаж отдельного товара.
1. Введите следующую формулу в ячейку, где должен отобразиться результат (например, в E2):
=MAX((A2:A13=D2)*B2:B13)
Советы: в этой формуле: A2:A13 задаёт диапазон ячеек, содержащий ваш критерий, D2 — это сам критерий (например, название товара «KTE»), а B2:B13 — диапазон значений, из которого будет возвращён результат.
Обязательно используйте абсолютные или относительные ссылки на ячейки в соответствии с задачей, если собираетесь копировать формулу в другое место.
2. После ввода формулы нажмите Ctrl + Shift + Enter (а не просто Enter). Excel автоматически заключит вашу формулу в фигурные скобки { }, что означает использование формулы массива, и отобразит максимальное значение, соответствующее вашему критерию. См. скриншот:
Примечание: чтобы получить Минимальное значение по определенному критерию, введите эту формулу:
=MIN(IF(A2:A13=D2,B2:B13))Не забудьте также подтвердить нажатием Ctrl + Shift + Enter. В результате отобразится наименьшее количество для указанного товара.
Если вы столкнулись с ошибкой #ЗНАЧ! или похожей, убедитесь, что размеры ваших диапазонов совпадают, ячейка с критерием содержит корректное значение, а формула подтверждена комбинацией Ctrl + Shift + Enter, а не обычным нажатием Enter.
Раскройте магию Excel с помощью KUTOOLS AI
- Интеллектуальное выполнение: Выполняйте операции с ячейками, анализируйте данные и создавайте диаграммы — всё это доступно через простые команды.
- Пользовательские формулы: создавайте индивидуальные формулы для оптимизации рабочих процессов.
- Программирование на VBA: Пишите и внедряйте код VBA легко и без усилий.
- Анализ формул: Легко разбирайтесь даже в самых сложных формулах.
- Перевод текста: Ломайте языковые барьеры прямо в ваших таблицах.
Поиск максимального или Минимальное значение значения на основе нескольких критериев
Когда нужно найти максимальное или минимальное значение, удовлетворяющее нескольким условиям — например, конкретный товар за определённый месяц или в определённом регионе, — используйте формулу массива с несколькими критериями внутри вложенных функций ЕСЛИ.
Например, возьмём следующие данные: нужно найти максимальное (или минимальное) количество заказов товара KTE в январе.
1. Введите эту формулу в ячейку, где должен отобразиться результат (например, в H2):
=MAX(IF(A2:A13=F1,IF(B2:B13=F2,C2:C13)))
Советы: в этой формуле: A2:A13 содержит первый критерий, B2:B13 задаёт второй критерий, F1 и F2 — это значения для сопоставления с критериями, а C2:C13 — диапазон, из которого будет возвращено максимальное значение. При необходимости скорректируйте диапазоны и критерии в соответствии с вашим набором данных.
Используйте эту структуру, если у вас два или более условия фильтрации. Убедитесь, что все диапазоны имеют одинаковую длину, чтобы избежать ошибок.
2. После ввода формулы нажмите Ctrl + Shift + Enter, и результат покажет максимальное значение, найденное для товара и месяца, соответствующих вашим критериям. Этот метод особенно полезен при фильтрации данных по комбинациям полей, таким как категория, регион или период времени.
Примечание: для получения Минимальное значение при тех же условиях используйте эту формулу:
=MIN(IF(A2:A13=F1,IF(B2:B13=F2,C2:C13)))Обязательно используйте сочетание клавиш Ctrl + Shift + Enter. Если формула не даёт ожидаемого результата, дважды проверьте значения в ячейках с критериями и убедитесь, что опция «Сортировать все столбцы» отключена, особенно если в вашем диапазоне есть пустые ячейки или текст.
Формулы массива требуют особого внимания к ссылкам на ячейки и точному выбору диапазонов. Если результаты не оправдывают ожиданий, убедитесь, что диапазоны содержат одинаковое количество строк, а ячейки с критериями — корректные значения.
Использование функций MAXIFS и MINIFS (Excel 2019+)
Если вы используете Excel 2019 или более позднюю версию, можно использовать встроенные функции MAXIFS и MINIFS для поиска максимального или минимального значения по одному или нескольким условиям — без необходимости применять формулы массива или нажимать Ctrl + Shift + Enter. Этот подход проще, особенно при работе с несколькими критериями или если вы хотите избежать синтаксиса массивов.
Применимые сценарии: Используйте этот метод, если у вас установлен Excel 2019 или новее — он поможет упростить формулы и снизить вероятность ошибок.
1. Чтобы получить максимальное значение для «KTE», введите эту формулу в целевую ячейку (например, в E2):
=MAXIFS(B2:B13,A2:A13,D2)Эта формула находит наибольшее значение в диапазоне B2:B13 (количество заказов), где товар из диапазона A2:A13 совпадает со значением в ячейке D2 (например, «KTE»).
2. Нажмите Enter, чтобы подтвердить формулу — использовать Ctrl + Shift + Enter не нужно. Чтобы применить эту формулу к другим строкам или товарам, просто измените ячейку с критерием или скопируйте формулу вниз по мере необходимости.
Чтобы найти Минимальное значение по одному критерию, используйте:
=MINIFS(B2:B13,A2:A13,D2)Аналогично, формулу можно расширить для нескольких критериев. Например, чтобы найти максимальный заказ товара KTE в январе (где месяцы указаны в диапазоне)B2:B13, а количества — в диапазоне C2:C13):
=MAXIFS(C2:C13,A2:A13,F1,B2:B13,F2)В этом примере F1 — это критерий по товару, а F2 — критерий по месяцу. Настройте поля в соответствии с вашим набором данных.
Сводная таблица Решение
Сводная таблица предоставляет интерактивный способ быстрого анализа больших объемов данных и может мгновенно отображать максимальное или минимальное значение на основе выбранных критериев. Это решение идеально подходит, если необходимо сводить данные по различным категориям — например, по товару, региону или дате — и получить динамический обзор наибольших или наименьших значений.
Применимые сценарии: Используйте этот метод, когда вам нужны гибкие интерактивные отчёты или когда вы совместно работаете и делитесь выводами с другими пользователями, предпочитающими сводные таблицы формулам.
Чтобы настроить Сводная таблица для извлечения максимального или минимального значения по критериям:
1. Выделите диапазон данных, включая заголовки (например, A1:B13).
2. Перейдите на вкладку Вставка на ленте и нажмите Сводная таблица.
3. В диалоговом окне выберите место для размещения сводной таблицы (рекомендуется новый лист) и нажмите ОК.
4. В области «Поля сводной таблицы» перетащите поле критерия (например, Товар) в область Строки, а поле значений (например, Количество заказов) — в область Значения.
5. По умолчанию значения суммируются с помощью функции Сумма. Нажмите стрелку раскрывающегося списка рядом с полем в области Значения, выберите Параметры полей / Настройки полей, затем укажите Максимум или Минимум — в зависимости от ваших задач. Нажмите ОК, чтобы подтвердить.
Теперь сводная таблица будет отображать максимальное или минимальное значение для каждого критерия (например, наибольшее или наименьшее количество заказов по каждому товару). При необходимости вы можете дополнительно фильтровать и группировать данные, добавляя дополнительные поля в области строк, столбцов или фильтров.
Примечание: Если ваши данные обновляются, щелкните правой кнопкой мыши по сводной таблице и выберите Обновить, чтобы ваши вычисления оставались актуальными.
Преимущества: Сводные таблицы позволяют интуитивно и легко изменять критерии, детализировать данные и создавать динамические сводки — всё это без изменения исходных данных или формул. Они особенно эффективны при работе с большими и сложными наборами данных.
См. также:
Как выбрать максимальное значение с помощью функции МАКС в Excel?
Как выбрать максимальное и минимальное значения в Excel?
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек