Сегодня мы продолжим изучать функцию Excel МИН и узнаем еще несколько способов поиска наименьшего числа на основе одного или нескольких условий. Я покажу вам комбинацию функций МИН и ЕСЛИ, а затем расскажу о новой функции МИНЕСЛИ (MINIFS в английской версии), чтобы доказать, что она определенно заслуживает вашего внимания.
Я уже рассказывал о функции МИН и ее возможностях. Но если вы давно работаете с Excel, то, думаю, знаете, что функции можно комбинировать друг с другом самыми разными способами, чтобы решить столько различных задач, сколько вы только можете придумать.
В этой статье я хочу продолжить наше знакомство с функцией МИН, показать вам еще несколько способов ее использования и предложить хорошую альтернативу.
Функция МИН с несколькими условиями
Некоторое время назад я показывал вам использование функций МИН и ЕСЛИ, чтобы вы могли найти наименьшее число на основе некоторого критерия. Но что, если одного условия недостаточно? Что если вам нужно провести более сложный поиск и найти наименьшее значение на основе нескольких условий?
Когда вы знаете, как найти минимум по одному условию с помощью МИН и ЕСЛИ, вам может быть интересно, как найти его по двум или более критериям. Как это можно сделать? Решение будет таким же очевидным, как вы думаете – с помощью функций МИН и двух или более ЕСЛИ.
Рассмотрим небольшой пример. Итак, если вам нужно найти наименьшее количество яблок, проданных в определенном регионе, вот вам формула:
=МИН(ЕСЛИ(A2:A15=F2;ЕСЛИ(C2:C15=F3;D2:D15)))
Кроме того, вы можете избежать нескольких вложенных ЕСЛИ в одной формуле, используя символ умножения (*). Поскольку вы применяете формулу массива, оператор И заменяется знаком умножения (звездочкой).
Таким образом, вторая формула для получения наименьшего количества яблок, проданных на юге, будет такой:
=МИН(ЕСЛИ((A2:A15=F2)*(C2:C15=F3);D2:D15))
Примечание! Помните, что комбинация функций MIN и ЕСЛИ – это формула массива, которую следует в Excel2003-2019 вводить с помощью Ctrl + Shift + Enter.
Функция Excel МИНЕСЛИ или как легко найти минимум на основе одного или нескольких условий
Функция МИНЕСЛИ (MINIFS по-английски) возвращает минимальное значение на основе одного или нескольких заданных вами критериев. Как видно из названия, это объединение в одной функции МИН и ЕСЛИ.
Примечание! Эта функция доступна только в Microsoft Excel 2019 и в новейших версиях Office 365 и Excel 2021.
Синтаксис функции Excel МИНЕСЛИ
Excel МИНЕСЛИ просматривает диапазон данных и находит наименьшее значение, исходя из заданных условий.
Синтаксис МИНЕСЛИ приведен ниже:
=МИНЕСЛИ(минимальный_диапазон, диапазон_условий1, условие1, [диапазон_условий2], [условие2], ...)
- минимальный_диапазон (обязательно) - диапазон, в котором нужно найти минимум
- диапазон_условий1 (обязательно) - набор данных для проверки по первому критерию
- условие1 (обязательно) - условие, на которое нужно проверить диапазон_условий1
- [диапазон_условий2], [условие2], ... (необязательно) - дополнительный диапазон(ы) данных и соответствующие им условия.
Помните, как мы искали минимум с помощью МИН и ЕСЛИ и нажимали Ctrl + Shift + Enter, чтобы превратить выражение в формулу массива? Так вот, для пользователей Office 2019 и новее есть другое решение. Предупреждение под спойлером – оно проще :)
Давайте вернемся к нашим примерам и посмотрим, как работает МИНЕСЛИ.
МИНЕСЛИ для получения минимума по условию
Основное достоинство МИНЕСЛИ заключается в ее простоте. Смотрите, вы указываете в формуле диапазон с числами, набор ячеек для проверки условия и само условие. На самом деле это проще сделать, чем сказать :)
Вот новая формула для нашего предыдущего случая:
=МИНЕСЛИ(B2:B15;A2:A15;D2)
Логика проста:
А – Определите записи в таблице, которые соответствуют первому условию.
Б – Повторите с оставшимися записями предыдущее действие столько раз, сколько условий содержится в вашей формуле.
В – Среди значений, которые соответствуют всем условиям, определите наименьшее.
Как найти минимум по нескольким условиям с помощью функции МИНЕСЛИ
Я показал вам, как найти наименьшее число по одному условию с помощью МИНЕСЛИ. Это было довольно просто, правда? И я думаю, что к тому времени, как вы дочитаете это предложение до конца, вы поймете, что уже знаете, как найти минимальное число по нескольким критериям :)
Вот обновленная формула для этого случая:
=МИНЕСЛИ(D2:D15,A2:A15,F2,C2:C15,F3)
Примечание! Для корректной работы формулы размер минимальный_диапазон и всех диапазон_условий должен быть одинаковым. В противном случае вместо минимального значения вы получите ошибку #ЗНАЧ! (#VALUE!).
Как найти наименьшее значение без учета нулевых и пустых ячеек с помощью МИНЕСЛИ
Условиями, которые вы задаете для функции МИНЕСЛИ, могут быть не только некоторые слова и значения, но и выражения с логическими операторами (>,<,<>,=). Я хочу сказать, что вы можете найти минимальное значение, которое больше нуля, используя всего одну формулу:
=МИНЕСЛИ(B2:B15;B2:B15;">0")
Использование МИНЕСЛИ для поиска минимума по частичному совпадению
При поиске самого маленького значения может оказаться, что ваш поиск был не совсем точен. После ключевого слова могут быть другие слова, символы или случайный пробел, которые могут помешать вам получить правильный результат. Ведь по умолчанию вы ищете полное совпадение ячейки с заданным условием.
К счастью, в функции МИНЕСЛИ можно использовать подстановочные знаки, которые станут вашим маленьким спасением в этой ситуации.
Так, если вы точно знаете, что в вашей таблице есть несколько различных вхождений, скажем, яблок, и вам нужно найти наименьшее значение из всех этих яблок, просто поставьте звездочку сразу после слова поиска, чтобы формула выглядела следующим образом:
=МИНЕСЛИ(C2:C15;A2:A15;"Яблоки*")
Или более универсальный вариант, когда мы указываем условие в отдельной ячейке и просто добавляем к нему звездочку:
=МИНЕСЛИ(C2:C15;A2:A15;E2&"*")
Теперь нет необходимости корректировать формулу при смене условия поиска.
В этом случае формула проверит все вхождения слова «яблоки» в начале ячейки, проигнорирует все последующие символы, и вернет вам наименьшее число из столбца "Продано".
Этот трюк может стать настоящей экономией времени и нервов, когда дело доходит до частичных совпадений.
Говорят, что лучше всего – это старое и проверенное. Но, как вы видите, что-то новое (например, функция МИНЕСЛИ Excel) может быть даже лучше. Она проста, эффективна, и нет необходимости постоянно помнить о комбинации Ctrl+Shift+Enter.
С помощью МИНЕСЛИ вы можете легко найти наименьшее значение на основе одного, двух, трех и более условий.
Но если вы предпочитаете "старое доброе проверенное", то пара МИН и ЕСЛИ подойдет вам как нельзя лучше. Потребуется еще несколько лишних нажатий на кнопки, и более сложная формула, но это работает.
Надеюсь, вам была полезна эта информация. Если у вас есть вопросы или другие примеры на примете, пожалуйста, оставляйте их в разделе комментариев.
