Минимум, если в Excel

Обновлено: 21.11.2024

Найти наименьшее значение для диапазона данных, удовлетворяющего определенному критерию

Бывший писатель Lifewire Тед Френч является сертифицированным специалистом Microsoft, который преподает и пишет о электронных таблицах и программах для работы с электронными таблицами.

Что нужно знать

Лучший способ понять, как сочетать функции МИН и ЕСЛИ в Excel, — это пример. В этом учебном примере показано время забега для двух видов соревнований по легкой атлетике — спринта на 100 и 200 метров, и он применим к Excel для Microsoft 365, Excel 2019, Excel 2016, Excel 2013 и Excel 2010.

Что такое массив MIN IF?

Использование формулы массива MIN IF позволяет найти самое быстрое время заезда для каждой гонки с помощью одной формулы.

Работа каждой части формулы заключается в следующем:

Функция MIN находит самое быстрое или минимальное время для выбранного события.
Функция ЕСЛИ позволяет нам выбрать расу, установив условие с использованием названий рас.
Формула массива позволяет функции ЕСЛИ проверять несколько условий в одной ячейке, а когда условие выполняется, формула массива определяет, какие данные (время гонки) проверяет функция МИН, чтобы найти самое быстрое время.

MIN IF синтаксис и аргументы вложенной формулы

Синтаксис формулы МИН. ЕСЛИ:

Поскольку функция ЕСЛИ вложена в функцию МИН, вся функция ЕСЛИ становится единственным аргументом функции МИН.

logical_test (обязательно) — значение или выражение, которое проверяется на истинность или ложность.
value_if_true (обязательно) — значение, которое отображается, если logical_test имеет значение true.
value_if_false (необязательно) — значение, которое отображается, если logical_test имеет значение false.

В этом примере логический тест пытается найти совпадение с названием расы, введенным в ячейку D10 рабочего листа. Аргумент value_if_true с помощью функции MIN определяет самое быстрое время для выбранной гонки. Аргумент значение_если_ложь опущен, так как он не нужен, а его отсутствие сокращает формулу. Если в ячейку D10 введено название забега, которого нет в таблице данных, например забега на 400 метров, формула возвращает ноль.

Пример формулы массива MIN IF в Excel

Введите следующие обучающие данные в ячейки с D1 по E9:

В ячейке D10 введите "100 метров" (без кавычек). Формула будет искать в этой ячейке, чтобы найти, для какой из гонок вы хотите найти самое быстрое время.

Ввод вложенной формулы MIN IF

Поскольку вы создаете и вложенную формулу, и формулу массива, вам нужно ввести всю формулу целиком в одну ячейку листа.

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

=МИН(ЕСЛИ(D3:D8=D10,E3:38))

Создание формулы массива

Теперь, когда вы ввели формулу МИН. ЕСЛИ, вам нужно преобразовать ее в массив. Для этого выполните следующие действия.

Нажмите и удерживайте клавиши Ctrl и Shift на клавиатуре.

Нажмите клавишу Enter на клавиатуре, чтобы создать формулу массива.

Ответ 11,77 появляется в ячейке F10, потому что это самое быстрое (наименьшее) время в трех забегах на 100 метров в спринте.

Полная формула массива отображается в строке формул над рабочим листом.

Поскольку клавиши Ctrl, Shift, и Enter на клавиатуре нажимаются одновременно после ввода формулы, полученные формулы иногда называют формулами CSE. .

Проверить формулу

Проверьте формулу, найдя лучшее время на дистанции 200 метров. Введите 200 метров в ячейку D10 и нажмите клавишу Enter на клавиатуре. Формула должна возвращать время 21,49 секунды в ячейке E10.

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

Рис. 1. Возврат минимального значения при нескольких критериях

Использование функции MIN с несколькими функциями ЕСЛИ

В этом методе мы вводим вложенные операторы IF для проверки нескольких условий внутри функции MIN, чтобы вернуть минимум, если несколько критериев. Предположим, мы хотим оценить минимальную себестоимость для указанного продукта с указанным цветом из таблицы данных. Продукты перечислены в диапазоне B2: B13, их цвета — в диапазоне C2: C13, а себестоимость — в диапазоне D2: D13. Мы применим следующую формулу в ячейке H4 как формулу массива, нажав клавиши клавиатуры Ctrl+Shift+Enter;

=МИН(ЕСЛИ(B2:B13=F4,ЕСЛИ(C2:C13=G4,D2:D13)))

Рисунок 2. Применение функции MIN с несколькими функциями ЕСЛИ

Каждый оператор IF возвращает массив логических значений TRUE и FALSE для каждого значения критерия. Когда диапазон значений в аргументе «Value_If_True» передается в последнем операторе IF, таком как D2: D13. Затем последний массив возвращает значения из диапазона D2: D13, где предыдущие массивы возвращают TRUE в соответствующей позиции вместе с логическими значениями FALSE, а функция MIN выбирает наименьшее числовое значение из этого массива.

Рисунок 3. Работа функции MIN с несколькими функциями ЕСЛИ

Использование функции MINIFS

Функция Excel MINIFS была введена в Excel для Office 365 и более поздних версиях, чтобы возвращать минимум, если несколько критериев без формулы массива. Используя эту функцию, мы можем вернуть минимальное значение из диапазона значений на основе одного или нескольких критериев в следующем синтаксисе формулы;

=MINIFS(диапазон, диапазон_критериев1, критерий1, [диапазон_критериев2, критерий2], . )

Формула в нашем примере будет выглядеть так:

=МИНИФН(D2:D13,B2:B13,F4,C2:C13,G4)

Рисунок 4. Использование функции MINIFS

Мгновенное подключение к эксперту через нашу службу Excelchat

В большинстве случаев проблема, которую вам нужно решить, будет более сложной, чем простое применение формулы или функции. Если вы хотите сэкономить часы исследований и разочарований, попробуйте наш онлайн-сервис Excelchat! Наши эксперты по Excel доступны круглосуточно и без выходных, чтобы ответить на любой вопрос, который у вас может возникнуть. Мы гарантируем подключение в течение 30 секунд и индивидуальное решение в течение 20 минут.

Чтобы получить минимальное значение в наборе чисел на основе более чем одного критерия (т. е. получить МИН. ЕСЛИ), можно использовать формулу массива и на основе функций МИН. и ЕСЛИ.

Примечание. Это формула массива, которую необходимо вводить с помощью клавиш Ctrl + Shift + Enter

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

В показанном примере формула в I6 выглядит следующим образом:

С цветом "зеленый" и элементом "футболка" результат составит 8,00 долл. США

Как работает эта формула

В этой формуле используются две вложенные функции ЕСЛИ, заключенные в MIN, для возврата минимальной цены по двум критериям. Начиная с логической проверки первого оператора IF, цвет = G6, значения в именованном диапазоне цветов (B6:B14) проверяются на соответствие значению в ячейке G6, "зеленый". В результате получается такой массив:

В логическом тесте для второго оператора IF элемент = H6 значения в именованном элементе диапазона (C6:C14) сравниваются со значением в ячейке H6, "футболка". В результате получается такой массив:

"Значение, если оно истинно" для второго оператора IF, именованный диапазон "prices" (E6:E14), который представляет собой такой массив:

Цена возвращается для каждого элемента в этом диапазоне только, когда результат первых двух приведенных выше массивов равен TRUE для элементов в соответствующих позициях. В показанном примере окончательный массив внутри MIN выглядит так:

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

Затем функция MIN возвращает самую низкую цену, автоматически игнорируя значения FALSE.

Альтернативный синтаксис с использованием логической логики

Вы также можете использовать следующую формулу массива, в которой используется только одна функция ЕСЛИ вместе с логической логикой:

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

MS Excel предоставляет простую функцию MIN, которая возвращает минимальное значение из любого диапазона.Но может быть случай, когда нам нужно найти или вычислить минимальное значение на основе одного или нескольких критериев. Есть много способов вычислить этот тип расчета в Excel. В этой статье я покажу несколько способов определения или поиска минимального значения на основе нескольких критериев в Excel.

Загрузить рабочую тетрадь

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

1. Найдите минимальное значение на основе нескольких критериев с помощью функций МИН и ЕСЛИ

Предположим, у нас есть набор данных о заказах на еду с именами, количеством, ценой, датой доставки и статусом. Теперь мы узнаем минимальную цену на еду, если количество больше 7, а цена больше 700 долларов.

Шаг 1. Введите формулу в ячейку F15 и нажмите клавишу ВВОД

Объяснение формулы

Здесь IF(C4:C12>7 — наше первое условие, а IF(D4:D12>700) — второе условие.
После проверки условий функция MIN получит минимальную цену из D4:D12 этого диапазона. Если вы хотите узнать больше об этой функции MIN, вы можете перейти по этой ссылке

2. Найдите минимальное значение на основе нескольких критериев с помощью функции MINIFS

Сочетая функции МИН и ЕСЛИ, мы получаем еще одну функцию в Excel с именем МИНИМС. Здесь мы найдем минимальное значение на основе нескольких критериев, используя функцию MINIFS для вышеуказанного набора данных.

Шаг 1. Введите следующую формулу в ячейку F15 и нажмите клавишу ВВОД

Объяснение формулы

В функции MINIFS D4:D12 — это первый минимальный диапазон. C4:C12 — это первый диапазон критериев, а «>7» — это первый критерий.
Точно так же D4:D12 — это второй диапазон критериев, а «>700» — второй критерий.
Если вы хотите узнать больше о функции MINIFS, перейдите по этой ссылке.

3. Определение N-го наименьшего значения с помощью функций НАИМЕНЬШИЙ и ЕСЛИ

Теперь в этом разделе мы увидим, как мы можем узнать N-е наименьшее значение из любого набора данных. Наша задача состоит в том, чтобы найти три наименьшие цены, где количество больше 7 и статус "Ожидание".

Шаг 1. Теперь введите следующую формулу в ячейку C20 и нажмите Enter. В Excel 365 это будет работать правильно, но для другой версии вам нужно поместить его как формулу массива, поэтому вам нужно нажать CTRL + SHIFT + Enter

Объяснение формулы

IF($C$4:$C$12>$C$15 проверяет наше первое условие. Мы проверяем, превышает ли количество введенное вами число или нет. Чтобы узнать больше о функции ЕСЛИ, вы можете перейти по этой ссылке.
IF($E$4:$E$12=$C$16,$F$4:$F$12)) это внутренний оператор IF, который проверяет и возвращает совпадающие строки состояния. Затем из этого диапазона $F$4:$F$12 мы получим соответствующую цену строки.
Наконец, B20 определяет ранг цены. Если вы хотите узнать больше об этой МАЛЕНЬКОЙ функции, вы можете перейти по этой ссылке

Шаг 3. Теперь введите 7 в поле Количество и Ожидание в качестве статуса

[Примечание. Убедитесь, что все ячейки с ценами имеют правильный формат валюты]

4. МАЛЕНЬКАЯ формула ЕСЛИ с несколькими критериями ИЛИ

Используя формулу функций МАЛЕНЬКИЙ ЕСЛИ, мы можем выполнять несколько операций или в Excel. Здесь наша цель — найти 3 самые низкие цены, для которых статус «Ожидание» ИЛИ «В обработке».

Шаг 1. Введите следующую формулу в ячейку C20 и скопируйте ее до ячейки C22. И опять же, в Excel 365 это будет работать правильно, но для другой версии вам нужно поставить его как формулу массива, поэтому вы должны нажать CTRL + SHIFT + Enter

Объяснение формулы

Эта формула работает так же, как и предыдущая. Но здесь между двумя условиями мы используем оператор плюс (+), чтобы сделать это операцией ИЛИ.

[Примечание. Убедитесь, что все ячейки с ценами имеют правильный формат валюты]

Похожие чтения:

5. Найдите N-е наименьшее значение с помощью критерия ИЛИ, используя функции НАИМЕНЬШИЙ и ФИЛЬТР

До сих пор мы видели, как найти N-е наименьшее значение и как найти наименьшее значение с помощью операции ИЛИ. Теперь здесь мы увидим способ нахождения 4-го наименьшего значения с критериями ИЛИ с использованием функций МАЛЕНЬКИЙ и ФИЛЬТР. И наше условие ИЛИ состоит в том, что статус должен быть либо в ожидании, либо в обработке.

Шаг 1. Введите следующую формулу в ячейку C20 и скопируйте ее до ячейки C23.

Объяснение формулы

В функции ФИЛЬТР $F$4:$F$12 — это диапазон массива, $E$4:$E$12=$B$16) + ($E$4:$E$12=$C$16)) — это два условия, которые действуют как операция ИЛИ. Если вы хотите узнать больше об этой функции ФИЛЬТР, вы можете перейти по этой ссылке
Наконец, $B20 определяет ранг наименьшего значения с помощью функции НАИМЕНЬШИЙ.

6. Поиск наименьших значений с игнорированием нулей по нескольким критериям

Во время расчета минимальных значений иногда нам нужно игнорировать нули. До сих пор все процессы, которые мы использовали, считали нуль во время определения минимальных значений. Теперь мы увидим, как найти наименьшие значения без учета нулей в Excel. Сначала мы увидим функции МАЛЕНЬКИЙ и ЕСЛИ для не нуля, затем мы увидим ту же формулу для больше нуля с условием.

Здесь наша задача — найти 3 минимальные цены, где цена не должна быть равна нулю, а статус находится на рассмотрении.

[ Примечание. Здесь пустые ячейки с одним знаком тире (-) представляют собой ячейки с нулевыми значениями. В Excel, если используется формат «Учет», вы не можете напрямую вводить нулевые значения в эту ячейку.]

Шаг 1. Введите формулу в ячейку C18 и скопируйте ее до ячейки C21. Нам нужно нажать CTRL + SHIFT + ENTER, так как это формула массива

Объяснение формулы

Эта формула похожа на предыдущую. Просто в первом условии IF($F$4:$F$12<>0 проверяется, равна ли цена нулю или нет. В Excel символ <> является операцией НЕ.

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

7. Найдите минимальное значение на основе нескольких критериев с помощью функции агрегирования

В Excel есть мощная функция АГРЕГАТ, которая может выполнять различные агрегатные операции, такие как МАКС, МИН, СУММ и т. д. Подробности об этой функции можно найти по этой ссылке

Мы можем использовать эту функцию, чтобы действовать как функция MINIFS, чтобы найти минимальные значения с условиями. Опять же, мы будем использовать тот же набор данных.

Шаг 1. Введите формулу в ячейку C20 и скопируйте ее вниз до ячейки C22

Объяснение формулы

В функции используется 15, так как вычисляется наименьшее число, затем используется 3, поскольку мы игнорируем скрытые строки, значения ошибок, вложенные промежуточные итоги и агрегатные функции.
1/( ($C$4:$C$12>$C$15) * ($E$4:$E$12=$C$16)) эта часть вернет 0 или 1, например True или False.Если оба условия выполнены, то возвращается 1, иначе 0.
Это 1 или True будет умножено на столбцы цен и вернет выбранную цену.
Наконец, 1 определяет первое наименьшее значение из выбранных строк.

Что нужно помнить

Заключение

Вот несколько способов найти минимальное значение на основе нескольких критериев в Excel. Я показал все методы с соответствующими примерами, но может быть много других итераций. Я также обсудил основы используемых функций. Если у вас есть какой-либо другой способ добиться этого, поделитесь им с нами.

Дополнительная литература

Мд. Абдулла Аль Мурад

Здравствуйте! Добро пожаловать в мой профиль. В настоящее время я работаю и изучаю Microsoft Excel, и здесь я буду публиковать статьи, связанные с этим. Моя последняя образовательная степень — бакалавр наук, а моей программой были компьютерные науки и инженерия Американского международного университета в Бангладеш. Я выпускник компьютерных наук с большим интересом к исследованиям и разработкам. Всегда старайтесь собирать знания из разных источников и старайтесь находить инновационные решения.

Читайте также: