Как пользоваться функцией мин в excel

1 С итерируемым объектом

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

Посмотрите на пример, приведенный ниже.

# initialisation of list
list1 = 

# finding min element
print("Min value is : ", max(list1, default=0))

Выход:

Min value is : 23

Здесь передача списка list1 непосредственно методу дает нам минимум всех элементов, присутствующих в списке, то есть 23. Значение по установлено на 0, так что, если переданный итерабельный объект был пустым, метод был бы вернули это значение по умолчанию (0).

Для списка символов метод возвращает элемент с минимальным значением ASCII.

Функция МИН Excel

В самом заглавии функции МИН содержится ответ на вопросец, что эта функция делает. Ну естественно, функция МИН возвращает малое значение спектра либо введенных чисел. Если мы напишем, например, в ячейке А1 такую функцию =МИН(3; 4; -7; 23; 0; 73; 12), то получим в ячейке малое число посреди перечисленных, а конкретно -7.

Функция МИН Excel

Направьте внимание на ограничение — в скобках мы можем указать не наиболее 255 чисел. Таковым же образом мы можем указывать в качестве аргумента не определенные числа, а спектр ячеек в котором отыскиваем меньшее значение

Например, если мы желаем отыскать малое значение в спектре A1:A7 и поместить итог в ячейку B2, то должны в ячейке B2 написать =МИН(A1:A7)

Таковым же образом мы можем указывать в качестве аргумента не определенные числа, а спектр ячеек в котором отыскиваем меньшее значение. Например, если мы желаем отыскать малое значение в спектре A1:A7 и поместить итог в ячейку B2, то должны в ячейке B2 написать =МИН(A1:A7)

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

Пример 3. Некоторая компания ранее работала с одним поставщиком продукции. Однако стало известно, что некоторые другие поставщики предлагают более приемлемые цены. В таблице Excel содержатся коды товаров и скидки, предлагаемые другими поставщиками. Если скидки нет, отображено логическое значение ЛОЖЬ, если число положительное – товар стоит дороже. Найти наибольшую скидку для одних и тех же товаров только по разным ценам разных поставщиков.

Формула для расчета (формула массива):

1. Функция ЕСЛИ проверяет условие МИНА(ЕСЛИ(A3:A15=D1;B3:B15;»»))>=0, где МИНА возвращает минимальное значение скидки для товара, код которого указан в ячейке D1.
2. Функция МИНА учитывает логические значения. Возможен случай, когда для какого-либо товара скидки не существует (все значения – ЛОЖЬ), и будет возвращен результат 0 (нуль). В этом случае будет возвращена текстовая строка «Скидки нет». Аналогичное событие произойдет, если все скидки – только положительные числа.
3. Если условие не выполняется, будет возвращена максимальная скидка (наибольшее отрицательное значение) для указанного кода товара.

Вычислим наибольшие скидки для остальных товаров. В результате получим (Ctrl+Shift+Enter):

Использование метода Python max()

Ниже приведен синтаксис использования метода Python для поиска наибольшего значения в итерируемом объекте.

max(iterable, *)

• iterable — это объект, содержащий значения, для которых необходимо найти наибольшее,
• key определяет функцию упорядочивания с одним аргументом,
• И значение по умолчанию — это значение по умолчанию, возвращаемое методом, если переданная итерация пуста.

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

max(arg1, arg2, *args) 

Где,

arg1, arg2,…. argn — это n значений, среди которых метод max() вернет наибольшее значение.

Мы можем использовать метод по-разному, чтобы найти максимум или наибольшее значение данной итерации или для двух или более аргументов.

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

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

Пример 3. Некоторая компания ранее работала с одним поставщиком продукции. Однако стало известно, что некоторые другие поставщики предлагают более приемлемые цены. В таблице Excel содержатся коды товаров и скидки, предлагаемые другими поставщиками. Если скидки нет, отображено логическое значение ЛОЖЬ, если число положительное – товар стоит дороже. Найти наибольшую скидку для одних и тех же товаров только по разным ценам разных поставщиков.

Формула для расчета (формула массива):

1. Функция ЕСЛИ проверяет условие МИНА(ЕСЛИ(A3:A15=D1;B3:B15;»»))>=0, где МИНА возвращает минимальное значение скидки для товара, код которого указан в ячейке D1.
2. Функция МИНА учитывает логические значения. Возможен случай, когда для какого-либо товара скидки не существует (все значения – ЛОЖЬ), и будет возвращен результат 0 (нуль). В этом случае будет возвращена текстовая строка «Скидки нет». Аналогичное событие произойдет, если все скидки – только положительные числа.
3. Если условие не выполняется, будет возвращена максимальная скидка (наибольшее отрицательное значение) для указанного кода товара.

Вычислим наибольшие скидки для остальных товаров. В результате получим (Ctrl+Shift+Enter):

Как получить первое минимальное значение?

Для демонстрации решения задачи создадим простую таблицу:

В этом примере данные таблицы где нужно найти значение находятся в диапазоне A6:B18. Как видно на рисунке в столбце A содержаться значения, а в столбце B соответствующие им суммы. Кроме того, видно, что среди сумм встречается несколько минимальных значений разбросанных по разным строкам.

Чтобы получить значение из столбца A которому соответствует первая наименьшая сумма в столбце B выполним 2 простых шага действий:

1. В ячейке B3 введите следующую формулу:
2. Подтвердите ввод формулы комбинацией горячих клавиш CTRL+SHIFT+Enter, так как она должна выполнится в массиве. Если все сделано правильно в строке формул появятся фигурные скобки .

В результате мы получили значение, соответствующее первой наименьшей сумме.

Детальный разбор формулы для первого наименьшего значения

Функция ИНДЕКС является самой главной в этой формуле. Ее номинальная задача – это поиск значения в указанной таблице (диапазон A6:A18 указанный в первом аргументе функции) на основе координат, указанных в ее втором (номер строки таблицы) и третьем (номер столбца) аргументах. Так как таблица, которую указано для работы функции ИНДЕКС имеет только один столбец A, то третий аргумент в параметрах – пропущен (необязателен). В тоже время во втором аргументе сразу используется несколько функций, работающих с диапазоном B6:B18.

В первом аргументе функции ЕСЛИ тестирует: является ли значение в каждой ячейке диапазона B6:B18 наименьшим числом. Таким образом в памяти создается массив логических значений ИСТИНА и ЛОЖЬ. В данном примере массив содержит только три элемента с положительным результатом теста, так как столько же содержит одинаковых минимальных значений исходная таблица. Если результат положительный формула переходит к следующему вычислительному этапу, а если отрицательный функция возвращает в массив памяти пустое текстовое значение.

Следующий вычислительный этап формулы – это определение, какие номера строк содержат эти минимальные суммы. Данный этап нам необходим для того, чтобы определить первое минимальное значение в диапазоне B6:B18. Реализовывается такая задача с помощью функции СТРОКА, которая создает в памяти программы следующий массив, состоящий из номеров строк. От этих номеров следует вычесть номер первой строки с которой начинается диапазон исходной таблицы

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

Таким образом в памяти программы сформируется массив, состоящий из номеров строк. Далее функция МИН возвращает наименьший номер строки таблицы, которая содержит минимальное значение. Этот номер использует в качестве второго аргумента функция ИНДЕКС, которая поэтому же номеру строки возвращает нам значение из диапазона A6:B18.

Функции Excel 2016 МАКСЕСЛИ (MAXIFS) и МИНЕСЛИ (MINIFS)

В MS Excel добавили новые статистические функции — МАКСЕСЛИ и МИНЕСЛИ. Обе функции имеют возможность учитывать несколько условий и некоторое время в их названиях в конце были буквы -МН. Потом убрали, хотя в скриншотах ниже используется вариант названий с -МН.

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

Все очень просто. Как и у СУММЕСЛИМН вначале указываем диапазон, где находится искомое максимальное значение (колонка В), затем диапазон с критериями (колонка А) и далее сам критерий (в ячейке D2). Можно указать сразу несколько условий. Таким же способом легко рассчитать минимальное значение по условию. Найдем, к примеру, минимум внутри группы Б.

Ниже показан ролик, как рассчитать максимальное и минимальное значение по условию.

Как вычислить сумму минимальных неотрицательных значений в Excel

Пример 2. В таблице Excel содержится несколько столбцов числовых данных. Найти суммарное значение минимальных неотрицательных значений, содержащихся в этих столбцах. Дополнительно найти наименьшее число из имеющихся в таблице.

Для определения минимальных неотрицательных чисел используем формулу массива:

Единственным аргументом является функция ЕСЛИ, выполняющая проверку массива данных на вхождения положительных чисел. Если условие выполняется, функция МИН принимает в качестве аргумента массив, содержащий только положительные числа. Аналогично найдем минимальные значения для остальных столбцов. Полученный результат (Ctrl+Shift+Enter):

Найдем наименьшее число в таблице с помощью формулы:

В качестве аргументов функции являются значения, возвращаемые каждой из функций МИН для указанного вектора данных. Использовать как формулу массива. Результат (Ctrl+Shift+Enter):

Почему моя функция MIN не работает?

В идеальном мире все формулы будут работать как часы и возвращать правильные результаты, как только вы нажмете Enter. Но в мире, в котором мы живем, бывает, что функции вместо нужного нам результата возвращают ошибку. Не беспокойтесь, ошибка сама по себе всегда намекает на ее возможную причину. Вам просто нужно более внимательно изучить свои функции.

Исправление ошибки #VALUE в MIN

Как правило, вы получаете #ЗНАЧ! сообщение об ошибке, когда хотя бы один из аргументов, используемых в формуле, неверен. Что касается MIN, это может произойти, когда один из них поврежден, например, что-то не так с данными, на которые ссылается формула.

Например, #ЗНАЧ! может появиться, если один из ее аргументов является ячейкой с ошибкой или в ее ссылке есть опечатка.

Что может вызвать ошибку #ЧИСЛО! ошибка?

Excel показывает #ЧИСЛО! ошибка, когда невозможно рассчитать вашу формулу. Обычно это происходит, когда числовое значение слишком велико или мало для отображения. Допустимые номера находятся между -2.2251E-308 и 2.2251E-308. Если один из ваших аргументов выходит за пределы этой области, вы увидите #ЧИСЛО! ошибка.

Я получаю #DIV/0! ошибка, что делать?

Исправление #DIV/0! это легко. Не делите на ноль! Без шуток, это единственное решение этой проблемы. Проверьте, есть ли ячейка с #DIV/0! в вашем диапазоне данных, исправьте его, и формула сразу вернет результат.

Ищете самую маленькую цифру, но получаете #NAME? ошибка?

#ИМЯ? означает, что Excel не может распознать формулу или ее аргументы. Наиболее возможная причина такого результата — опечатка. Вы можете либо написать функцию с ошибкой, либо указать неправильные аргументы. Более того, текстовые представления чисел также вызовут эту ошибку.

Другая возможная причина этой проблемы лежит в именованном диапазоне. Итак, если вы сошлетесь на несуществующий диапазон или в нем есть опечатка, вы увидите #ИМЯ? в том месте, где вы ожидаете появления результата.

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

Это все на сегодня. Спасибо, что прочитали этот урок! Пожалуйста, не стесняйтесь делиться своими мыслями и вопросами в разделе комментариев, я буду рад получить обратную связь от вас!

Функции Excel 2016 МАКСЕСЛИ (MAXIFS) и МИНЕСЛИ (MINIFS)

В MS Excel добавили новейшие статистические функции — МАКСЕСЛИ и МИНЕСЛИ. Обе функции имеют возможность учесть несколько критерий и некое время в их заглавиях в конце были буковкы -МН. Позже убрали, хотя в снимках экрана ниже употребляется вариант заглавий с -МН.

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

Все весьма просто. Как и у СУММЕСЛИМН сначала указываем спектр, где находится разыскиваемое наибольшее значение (колонка В), потом спектр с аспектами (колонка А) и дальше сам аспект (в ячейке D2). Можно указать сходу несколько критерий. Таковым же методом просто высчитать малое значение по условию. Найдем, например, минимум снутри группы Б.

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

Функция Excel МИНА

Наблюдения и советы этой статьи мы подготовили на основании опыта команды Функция МИНА возвращает наименьшее числовое значение из предоставленного набора значений. Он оценивает текст в ячейке как 0 и оценивает логические значения ИСТИНА и ЛОЖЬ как 1 и 0 соответственно.

аргументы

• Value1 (обязательно): первое значение, ссылка на ячейку или диапазон.
• Значение2, … (необязательно): последующие значения, ссылки на ячейки или диапазоны.

Примечания к функциям

1. аргументы в функции MINA можно указать как числа или имена, массивы или ссылки, содержащие значения. До 255 допускаются аргументы.
2. Функция MINA может считать Логические значения и текстовые представления чисел (числа, заключенные в кавычки, означают «10») которые вы вводите непосредственно в список аргументов.
3. Функция МИНА игнорирует пустые ячейки.
4. В отличие от функции MIN, ячейки, содержащие текст или логические значения, могут быть подсчитаны в функции МИНА.
5. Функция МИНА оценивает ИСТИНА как 1 и ЛОЖЬ как 0 (ноль). Ячейки с текстом, включая пустой текст «», текстовые представления чисел и другой текст, оцениваются как 0 (ноль).
6. Если аргументы в функции МИНА не содержат значений, 0 (ноль) будет возвращен.
7. Наблюдения и советы этой статьи мы подготовили на основании опыта команды #СТОИМОСТЬ! ошибка значение возникает, если любой из предоставленных аргументы, которые вы ввели напрямую в функции текстовые строки, которые нельзя интерпретировать как числа в Excel.

Примеры

Чтобы рассчитать наименьшее значение из значений, представленных в таблице ниже, скопируйте приведенную ниже формулу в ячейку. E5 и нажмите Enter ключ для получения результата.

=МИНА (C5: C10)

Заметки:

1. Аргумент в приведенной выше формуле предоставляется как диапазон который содержит несколько значений.
2. Мы также можем использовать ссылки на ячейки в качестве аргументов формулы. Приведенную выше формулу можно изменить на:

   =МИНА (C5, C6, C7, C8, C9, C10)

3. Непосредственно вводить вышеуказанные значения в аргументы формулы не рекомендуется. То #СТОИМОСТЬ! Ошибка будет происходить, когда текст, который нельзя интерпретировать как число, вводится непосредственно в список аргументов.
4. Потому что Функция МИНА оценивает текст «ЯБЛОКО» в ячейке C5 как 0, а логические значения FALSE и TRUE в ячейках C5 и C9 как 0 и 1, формула выше Возвращает 0 как наименьшее числовое значение среди диапазона C5:C10.

5. Наблюдения и советы этой статьи мы подготовили на основании опыта команды Функция MIN вернется 61 как наименьшее число в диапазоне C5:C10, поскольку функция MIN игнорирует текст и логические значения в ячейках.

Относительные функции:

Excel DEVSQ ФункцияФункция DEVSQ вычисляет сумму квадратов отклонений от выборочного среднего.

Excel DSTDEV ФункцияФункция DSTDEV возвращает оценочное значение стандартного отклонения генеральной совокупности на основе выборки.

Excel DSTDEVP ФункцияФункция Excel DSTDEVP возвращает стандартное отклонение совокупности, используя числа из всей базы данных, соответствующие заданным вами критериям.

Функция МИН() в MS EXCEL

​ ячеек в диапазоне​

​ диапазонов и условий.​​ (на английском языке).​

​Альтернативный и более наглядный​​ необходимости измените ширину​ текстовые представления чисел,​ СУММ() и СЧЁТ()​ записав формулу =ИСТИНА+0).​Найдем с помощью функции​=МИН (ЕСЛИ (A1​ находящимся на листе,​

​​ вертикальный массив А1:А5​200​ значение, поэтому результат​Оценка​10​ макс_диапазон ячейка A6​Размер и форма аргументов​

​Функция МАКСЕСЛИ возвращает максимальное​ вариант (без использования​ столбцов, чтобы видеть​ логические значения (например,​ у МИН() нет​ Если логическое значение​ МИН(), английский вариант​ Для массивного объёма​ но и для​

​, после чего применяя​Для решения этой​1​ равен 12.​Класс​b​ имеет максимальное значение,​ макс_диапазон и диапазон_условияN​ значение из заданных​ формул массива) -​ все данные.​ ИСТИНА или ЛОЖЬ)​ аналога СУММЕСЛИ() и​ введено в аргументе​ MIN(), минимальное значение​ данных придётся напротив​ виртуальных массивов​ функцию ЕСЛИ() мы​ задачи можно использовать​b​Пример 5​Уровень​Бизнес​ поэтому результат равен​ должны быть одинаковыми.​ определенными условиями или​ создать соседний столбец.​Данные​ в ссылке.​

​ СЧЁТЕСЛИ(), позволяющих выполнять​ не через ссылку,​ в списке аргументов.​ каждой ячейки написать​В файле -примере приведены​ преобразуем массив в​ как дополнительный столбец​

​Техника​Вес​10​100​ 91.​ В противном случае​ критериями ячеек.​Если критерий относится к​ЛОЖЬ​Если аргументом является массив​

​ вычисления с учетом​ а напрямую (=МИН(ИСТИНА;55)),​ Предполагаем, что диапазон​ ЕСЛИ (A1 2)​ все решения, включая​ следующий:​ с промежуточной формулой,​300​Оценка​b​1​Пример 2​

​ эти функции вернут​Примечание:​ текстовому столбцу, то​0,2​ или ссылка, учитываются​

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

Функция МИН Excel

В самом названии функции МИН содержится ответ на вопрос, что эта функция делает. Ну конечно, функция МИН возвращает минимальное значение диапазона или введенных чисел. Если мы напишем, к примеру, в ячейке А1 такую функцию =МИН(3; 4; -7; 23; 0; 73; 12), то получим в ячейке минимальное число среди перечисленных, а именно -7.

Функция МИН Excel

Обратите внимание на ограничение — в скобках мы можем указать не более 255 чисел. Таким же образом мы можем указывать в качестве аргумента не конкретные числа, а диапазон ячеек в котором ищем наименьшее значение

К примеру, если мы хотим найти минимальное значение в диапазоне A1:A7 и поместить результат в ячейку B2, то должны в ячейке B2 написать =МИН(A1:A7)

Таким же образом мы можем указывать в качестве аргумента не конкретные числа, а диапазон ячеек в котором ищем наименьшее значение. К примеру, если мы хотим найти минимальное значение в диапазоне A1:A7 и поместить результат в ячейку B2, то должны в ячейке B2 написать =МИН(A1:A7)

Функции Excel 2016 МАКСЕСЛИ (MAXIFS) и МИНЕСЛИ (MINIFS)

В MS Excel добавили новые статистические функции — МАКСЕСЛИ и МИНЕСЛИ. Обе функции имеют возможность учитывать несколько условий и некоторое время в их названиях в конце были буквы -МН. Потом убрали, хотя в скриншотах ниже используется вариант названий с -МН.

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

Все очень просто. Как и у СУММЕСЛИМН вначале указываем диапазон, где находится искомое максимальное значение (колонка В), затем диапазон с критериями (колонка А) и далее сам критерий (в ячейке D2). Можно указать сразу несколько условий. Таким же способом легко рассчитать минимальное значение по условию. Найдем, к примеру, минимум внутри группы Б.

Ниже показан ролик, как рассчитать максимальное и минимальное значение по условию.

Использование функции МИН в Microsoft Excel

Синтаксис любой функции в Excel — это правила ее записи, которые необходимо соблюдать для нормальных расчетов. Рассматриваемая функция «МИН» имеет всего один аргумент, который можно растянуть на неограниченное количество. Для его записи в строке «Число» или при ручной записи массивов указывается диапазон чисел или выбирается группа ячеек в строке либо столбце.

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

Пример записи функции МИН

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

1. Запись функции «МИН», как обычно, начинается с ее объявления. Для этого в выбранной ячейке напишите =МИН() . Переместите курсор между круглыми скобками, чтобы начать запись аргументов.

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

Использование функции «МИН» в других функциях

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

1. Объявите функцию «ЕСЛИ» или любую другую, в которой будет использоваться «МИН», а затем нажмите по кнопке «Аргументы функции» для запуска этого самого окна.

Добавление математических операций

Последним примером разберем объединение несколько массивов «МИН» в один результат при помощи математических операций. Это требуется, например, когда нужно взять два числа из разных выборок и суммировать их. В этом случае синтаксис остается стандартным.

1. Объявите первую функцию «МИН» и запишите ее точно так же, как это было показано в предыдущих разделах статьи.

Мы рады, что смогли помочь Вам в решении проблемы. Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Множественные условия

Вышерассмотренный подход можно распространить на случаи когда необходимо найти максимальный или минимальный для нескольких текстовых условий.

В этом случае придется записать более сложную формулу массива :

= НАИМЕНЬШИЙ(ЕСЛИ(($A$6:$A$16=E6)*($B$6:$B$16=F6);$C$6:$C$16;””);1)

В файле примера для наглядности настроено Условное форматирование для выделения строк, удовлетворяющим критериям . Кроме того, для выбора критериев использован Выпадающий список (см. желтые ячейки).

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

Как получить второе наименьшее значение?

Чтобы исправить это, введите новую измененную формулу в ячейку F3:

Чтобы подтвердить ввод формулы, нажмите CTRL + SHIFT + Enter еще раз, чтобы выполнить ее в массиве и сразу получить результат:

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

Поиск абсолютного минимума в Excel

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

Есть ли готовое решение для этого случая? Вопрос был риторическим, в Excel есть решение любой задачи. Если у вас есть какие-либо сомнения, просто просмотрите наш блог.

Но вернемся к нашей задаче. Готовое решение для этого конкретного случая называется функцией ABS, которая возвращает абсолютное значение заданных вами чисел. Таким образом, комбинация функций МИН и ABS сделает свое дело. Просто введите следующую формулу в любую пустую ячейку:

Важно! Вы заметили фигурные скобки вокруг формулы? Это признак того, что это формула массива, и ее нужно вводить с помощью комбинации клавиш Ctrl + Shift + Enter, а не просто Enter

Поиск минимального или максимального значения по условию

В Microsoft Excel давно есть в стандартном наборе функции СЧЁТЕСЛИ (COUNTIF) , СУММЕСЛИ (SUMIF) и СРЗНАЧЕСЛИ (AVERAGEIF) и их аналоги, позволяющие искать количество, сумму и среднее в таблице по одному или нескольким условиям. Но что если нужно найти не сумму или среднее, а минимум или максимум по условию(ям)?

Предположим, нам нужно найти минимальную цену для каждого товара в базе данных по поставщикам:

Таким образом, условием будет наименование товара (бумага, карандаши, ручки), а диапазоном для выборки — столбец с ценами.

Для будущего удобства, конвертируем исходный диапазон с ценами в «умную таблицу». Для этого выделите его и выберите на вкладке Главная — Форматировать как таблицу (Home — Format as Table) или нажмите Ctrl+T. Наша «поумневшая» таблица автоматически получит имя Таблица1, а к столбцам можно будет, соответственно, обращаться по их именам, используя выражения типа Таблица1 или Таблица1. При желании, стандартное имя Таблица1 можно подкорректировать на вкладке Конструктор (Design) , которая появляется, если щелкнуть в любую ячейку нашей «умной» таблицы. Подробнее о таких таблицах и их скрытых возможностях можно почитать здесь.

Способ 1. Функции МИНЕСЛИ и МАКСЕСЛИ в Excel 2016

Начиная с версии Excel 2016 в наборе функции Microsoft Excel наконец появились функции, которые легко решают нашу задачу — это функции МИНЕСЛИ (MINIFS) и МАКСЕСЛИ (MAXIFS) . Синтаксис этих функции очень похож на СУММЕСЛИМН (SUMIFS) :

=МИНЕСЛИ( Диапазон_чисел ; Диапазон_проверки1 ; Условие1 ; Диапазон_проверки2 ; Условие2 . )

• Диапазон_чисел — диапазон с числами, из которых выбирается минимальное или максимальное
• Диапазон_проверки — диапазон, который проверяется на выполнение условия
• Условие — критерий отбора

Например, в нашем случае:

Просто, красиво, изящно. Одна проблема — функции МИНЕСЛИ и МАКСЕСЛИ появились только начиная с 2016 версии Excel. Если у вас (или тех, кто будет потом работать с вашим файлом) более старые версии, то придется шаманить другими способами.

Способ 2. Формула массива

В английской версии это будет, соответственно =MIN(IF(Table1=F4;Table1))

Не забудьте после ввода этой формулы в первую зеленую ячейку G4 нажать не Enter , а Ctrl + Shift + Enter , чтобы ввести ее как формулу массива. Затем формулу можно скопировать на остальные товары в ячейки G5:G6.

Давайте разберем логику работы этой формулы поподробнее. Функция ЕСЛИ проверяет каждую ячейку массива из столбца Товар на предмет равенства текущему товару (Бумага). Если это так, то выдается соответствующее ему значение из столбца Цена. В противном случае – логическое значение ЛОЖЬ (FALSE) .

Таким образом внешняя функция МИН (MIN) выбирает минимальное не из всех значений цен, а только из тех, где товар был Бумага, т.к. ЛОЖЬ функцией МИН игнорируется. При желании, можно выделить мышью всю функцию ЕСЛИ(…) в строке формул

. и нажать на клавиатуре F9, чтобы наглядно увидеть тот самый результирующий массив, из которого потом функция МИН и выбирает минимальное значение:

Способ 3. Функция баз данных ДМИН

Этот вариант использует малоизвестную (и многими, к сожалению, недооцененную) функцию ДМИН (DMIN) из категории Работа с базой данных (Database) и требует небольшого изменения результирующей таблицы:

Как видите, зеленые ячейки с результатами транспонированы из столбца в строку и над ними добавлена мини-таблица (F4:H5) с условиями. Логика работы этой функции следующая:

• База_данных — вся наша таблица вместе с заголовками.
• Поле — название столбца из шапки таблицы, из которого выбирается минимальное значение.
• Критерий — таблица с условиями отбора, состоящая (минимально) из двух ячеек: названия столбца, по которому идет проверка (Товар) и критерия (Бумага, Карандаши, Ручки).

Это обычная формула (не формула массива), т.е. можно вводить и использовать ее привычным образом. Кроме того, в той же категории можно найти функции БДСУММ (DSUM) , ДМАКС (DMAX) , БСЧЁТ (DCOUNT) , которые используются совершенно аналогично, но умеют находить не только минимум, но и сумму, максимум и количество значений по условию.

Способ 4. Сводная таблица

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

Установите активную ячейку в любое место нашей умной таблицы и выберите на вкладке Вставка — Сводная таблица (Insert — Pivot Table) . В появившемся окне нажмите ОК:

В конструкторе сводной таблицы перетащите поле Товар в область строк, а Цену в область значений. Чтобы заставить сводную вычислять не сумму (или количество), а минимум щелкните правой кнопкой мыши по любому числу и выберите в контекстном меню команду Итоги по — Минимум:

голоса

Рейтинг статьи

Примеры формул с использованием функций МИН и МИНА в Excel

Функция МИН используется для нахождения минимального числа в исследуемом диапазоне и возвращает соответствующее число.

Функция МИНА предназначена для поиска минимального значения в исследуемом диапазоне данных и возвращает соответствующий результат.

Примеры использования функций МИН и МИНА в Excel

Пример 1. В таблице Excel содержатся данные о дате рождения офисных работников. Определить минимальный возраст работника.

Для расчетов используем формулу массива (корректный результат только при нажатии комбинации Ctrl+Shift+Enter):

Единственным аргументом является выражение ГОД(СЕГОДНЯ())-ГОД(B3:B10), возвращающее массив числовых значений, равных разнице текущего года и года рождения каждого сотрудника. В результате вычислений получим:

Формула автоматически вычислила, что самому младшему сотруднику 27 лет.

Как вычислить сумму минимальных неотрицательных значений в Excel

Пример 2. В таблице Excel содержится несколько столбцов числовых данных. Найти суммарное значение минимальных неотрицательных значений, содержащихся в этих столбцах. Дополнительно найти наименьшее число из имеющихся в таблице.

Для определения минимальных неотрицательных чисел используем формулу массива:

Единственным аргументом является функция ЕСЛИ, выполняющая проверку массива данных на вхождения положительных чисел. Если условие выполняется, функция МИН принимает в качестве аргумента массив, содержащий только положительные числа. Аналогично найдем минимальные значения для остальных столбцов. Полученный результат (Ctrl+Shift+Enter):

Найдем наименьшее число в таблице с помощью формулы:

В качестве аргументов функции являются значения, возвращаемые каждой из функций МИН для указанного вектора данных. Использовать как формулу массива. Результат (Ctrl+Shift+Enter):

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

Пример 3. Некоторая компания ранее работала с одним поставщиком продукции. Однако стало известно, что некоторые другие поставщики предлагают более приемлемые цены. В таблице Excel содержатся коды товаров и скидки, предлагаемые другими поставщиками. Если скидки нет, отображено логическое значение ЛОЖЬ, если число положительное – товар стоит дороже. Найти наибольшую скидку для одних и тех же товаров только по разным ценам разных поставщиков.

Формула для расчета (формула массива):

1. Функция ЕСЛИ проверяет условие МИНА(ЕСЛИ(A3:A15=D1;B3:B15;»»))>=0, где МИНА возвращает минимальное значение скидки для товара, код которого указан в ячейке D1.
2. Функция МИНА учитывает логические значения. Возможен случай, когда для какого-либо товара скидки не существует (все значения – ЛОЖЬ), и будет возвращен результат 0 (нуль). В этом случае будет возвращена текстовая строка «Скидки нет». Аналогичное событие произойдет, если все скидки – только положительные числа.
3. Если условие не выполняется, будет возвращена максимальная скидка (наибольшее отрицательное значение) для указанного кода товара.

Вычислим наибольшие скидки для остальных товаров. В результате получим (Ctrl+Shift+Enter):

Формула MAX IF с несколькими критериями