history 14 января 2021 г.
- Группы статей
- Надстройка “Поиск решения”
Пусть дана функция с несколькими переменными F(x1, x2, …)=a1*x1+a2*x2+… Также даны граничные условия в виде b1*x1+b2*x2+…<=c (несколько условий). Нужно найти экстремум функции F (минимум или максимум). Это классическая задача для Поиска решения MS EXCEL, кроме того это линейная модель. Сделаем удобную форму для таких задач и покажем как настроить Поиск решения.
Задача
Пусть дана явная функция с 4 переменными:
Также даны несколько (семь) граничных условий (англ: restrictions, constraints):
Требуется найти максимум функции F.
Создание модели
Решим задачу с помощью инструмента MS EXCEL
Поиск решения
, хотя можно ее решить и другим способом, например аналитически.
На рисунке ниже приведена модель, созданная для решения задачи (см.
файл примера
).
Переменные (выделено зеленым)
. В качестве переменных модели, очевидно, выступают x1, x2, x3, x4. Эта задача хороша тем, что переменные задаются однозначно, не требуется осмысливать житейскую задачу, например как с
оптимизацией затрат
. Хотя математически – это эквивалентные задачи, только количество переменных разное.
После запуска Поиск решения будет методично (последовательно) по своему алгоритму подставлять в зеленые ячейки числовые значения и вычислять функцию F (красная ячейка).
Ограничения (выделено серым)
. Ограничения модели – это ограничения на область изменения переменных. Они могут задаваться как простыми выражениями для одной переменной, например х1>=0, так и для некой комбинации переменных 5*x1+4*x2-x3-2*x4<=-3. В первом случае х1>=0 ограничения можно ввести прямо в окне Поиска решения (будет показано ниже), для более сложных зависимостей удобно подготовить вспомогательную таблицу (С26:Е29).
Составить модель, особенно первую, непросто. Может помочь такой подход: считать, что переменные (зеленые ячейки) уже содержат некие значения, пусть даже не оптимальные. Так легче составлять огграничения. В нашем случае ограниечение 5*x1+4*x2-x3-2*x4 можно записать с помощью формулы =
СУММПРОИЗВ($D$19:$D$22;C26:C29)
. В диапазоне D19:D22 содержатся коэффициенты 5; 4; -1; -2. Кроме того, если значения переменных заданы, то и значение целевой функции также автоматически рассчитано (тоже не оптимальное пока, до запуска Поиска решения).
Целевая функция (выделено красным)
.
Целевая функция – это то, что требуется оптимизировать, т.е. F. Формула для ее вычисления задана в явном виде – не нужно догадываться из условий обычной задачи как ее подсчитать. Это не всегда очевидно (см., например, статью про
пропускную способность трубопровода
).
Ниже приведено окно Поиска решения с заполненными полями: целевая функция, переменные и ограничения.
После запуска Поиска решения ответ будет вычислен за доли секунды: F=3.
Microsoft Excel поддерживает множество функций, предназначенных для упрощения математических расчетов в электронной таблице. МИН – одна из них, и по названию уже можно понять, что она как-то связана с минимальным значением. Все об этой функции и ее применении в Excel вы узнаете далее.
Объявление и написание функции МИН
Начать стоит с самого важного – синтаксиса функции, поскольку при неправильной записи на экране просто появится ошибка. МИН (MIN) состоит всего из одного аргумента, поэтому запутаться будет тяжело. Задача этой функции – возвращение наименьшего значения из указанного перечня данных. То есть вы выбираете несколько ячеек, а функция покажет, какое число в них минимальное. Кстати, точно так же работает и МАКС, поэтому сегодняшнее руководство можете применить при вычислении максимального значения, объявив подходящую функцию.
Выше вы видите скриншот с окном «Аргументы функции», которое можно использовать для создания функции. В поле «Число» для МИН вы вписываете тот самый диапазон, который и нужно обработать. Если таких диапазонов несколько, запишите второй в «Число2», после чего на экране появится поле «Число3», и так может продолжаться долго, пока вы не впишите все требуемые диапазоны. Если хотите написать функцию в ячейке самостоятельно, используйте для этого =МИН(), а в скобках указывайте диапазоны чисел, отделяя их знаком «;», как об этом сказано в подсказке от разработчиков.
Во время объявления диапазона вы можете выделить его прямо в таблице, и запись появится автоматически. Это общее правило, относящееся ко всем функциям в Excel, поэтому вы, скорее всего, знаете о его существовании. В случае с МИН использование подобного выделения как никогда окажется кстати.
Ниже вы видите, что после расчетов, то есть когда вы нажимаете клавишу Enter, в ячейке с функцией отображается всего одно число, которое и является минимальным в указанных диапазонах. Не получится перечислить все стороны применения подобных расчетов, но обычно пользователь уже сам знает, зачем ему нужно возвращать минимальное число в диапазоне.
При указании в одной функции МИН нескольких диапазонов с данными в результате все равно будет одно число, то есть то, которое является среди всех значений самым маленьким. Это удобно, когда в подсчеты берется сразу несколько столбцов значений, находящихся в одной или разных таблицах.
Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостей
Подписаться
Математические операции и функция МИН
Кратко расскажу о том, как вы можете использовать функцию МИН, выполняя различные математические расчеты. Самый простой пример – сложение двух диапазонов данных. Вам нужно объявить несколько функций МИН в одной ячейке, поставив между ними «+», в результате чего два минимальных значения сложатся, а на экране отобразится их сумма. Конечно, вместо плюса вы можете подставить любой знак, а в качестве второй функции при расчетах необязательно использовать МИН.
Запись МИН в других функциях
Некоторые пользователи, только начинающие свой путь в Microsoft Excel, не знают, что в рамках одной функции может находиться и другая. Конечно, непосредственно в МИН нельзя засунуть еще какое-то выражение, поскольку функция применяет только аргумент, а вот саму МИН можно поместить в другую функцию. Например, объявите логическую операцию =ЕСЛИ(МИН(A1:A10)). Вы видите, что первое выражение для ЕСЛИ – это возвращение минимального значения.
Теперь нужно решить, какой знак поставить, а затем записать значения лжи и истины. Полностью формула выглядит следующим образом: =ЕСЛИ(МИН(A1:A10)>X;”+”;”-”). То есть если МИН будет больше указанного числа, значение ЕСЛИ станет «+», в противном случае «-». Вместо X можно подставить любую другую функцию, если в этом есть надобность.
Если описание выше вам показалось сложным, объявите ЕСЛИ самостоятельно и откройте окно «Аргументы функции». В нем вы увидите разделение на поля и поймете, что в логическом выражении нужно написать саму функцию МИН и число либо другую функцию после знака «больше», «меньше» или «равно». Дополнительно сами разработчики представляют подсказки, поэтому понять принцип действий будет нетрудно.
Как видно, МИН – простая функция, которая прекрасно сочетается с другими и может помочь при обработке массивов данных, когда вручную найти требуемое значение не получается. Соблюдайте синтаксис, следите за тем, с какими диапазонами данных работаете, и все у вас получится.
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще…Меньше
В этой статье описаны синтаксис формулы и использование функции МИН в Microsoft Excel.
Описание
Возвращает наименьшее значение в списке аргументов.
Синтаксис
МИН(число1;[число2];…)
Аргументы функции МИН описаны ниже.
-
Число1, число2,… Аргумент “число1” является обязательным, последующие числа необязательные. От 1 до 255 чисел, среди которых требуется найти наименьшее.
Замечания
-
Аргументы могут быть либо числами, либо содержащими числа именами, массивами или ссылками.
-
Учитываются логические значения и текстовые представления чисел, которые непосредственно введены в список аргументов.
-
Если аргумент является массивом или ссылкой, то учитываются только числа. Пустые ячейки, логические значения и текст в массиве или ссылке игнорируются.
-
Если аргументы не содержат чисел, функция МИН возвращает значение 0.
-
Аргументы, которые являются значениями ошибки или текстами, не преобразуемыми в числа, приводят к возникновению ошибок.
-
Если в ссылку в качестве части вычислений необходимо добавить логические значения и текстовые представления, воспользуйтесь функцией МИНА.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
|
Данные |
||
|
10 |
||
|
7 |
||
|
9 |
||
|
27 |
||
|
2 |
||
|
Формула |
Описание |
Результат |
|
=МИН(A2:A6) |
Наименьшее из чисел в диапазоне A2:A6. |
2 |
|
=МИН(A2:A6;0) |
Наименьшее из чисел в диапазоне A2:A6 и 0. |
0 |
Нужна дополнительная помощь?
Нужны дополнительные параметры?
Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.
В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.
Skip to content
В этой статье вы узнаете, как использовать функцию МИН в Excel 2007-2019, найти минимальное значение, наименьшее значение по условию и выделить самое маленькое число в вашем диапазоне.
Сегодня мы расскажем вам, как использовать базовую, но очень важную функцию МИН в Excel. Вы увидите способы найти минимальное значение, в том числе исключая нули, абсолютный минимум и наименьшее значение с учётом условий. Кроме того, я покажу вам пошагово, как выделить цветом наименьшее число, а также подскажу, что делать, если ваша функция МИН возвращает ошибку вместо нужного результата.
- Функция Excel МИН – синтаксис
- 1. Нахождение наименьшего значения
- 2. Как найти самую раннюю дату
- 3. Поиск абсолютного минимума в Excel
- Как найти самое маленькое значение в Excel, исключая нули
- Нахождение минимума на основе условия
- Как подсветить наименьшее число в Excel
- Почему не работает функция МИН?
Итак, начнем 🙂
Функция Excel МИН – синтаксис
Функция МИН проверяет ваш диапазон данных и возвращает минимальное значение в этом наборе.
Синтаксис для Excel МИН следующий:
МИН (число 1; [число 2]; …)
число1, [число2],… – это серия чисел, откуда вы хотите получить минимум. Число1 – обязательный аргумент, в то время как [число 2] и следующие являются необязательными. В одной формуле МИН допускается до 255 аргументов. Аргументы могут быть числами, ссылками на ячейки и диапазонами.
Функция МИН является одной из самых простых в применении. Вот пару примеров, доказывающих это:
1. Нахождение наименьшего значения
Допустим, у вас есть фрукты на складе. Ваша задача – проверить, какие из них заканчиваются. Есть несколько способов это сделать:
1: Ввести каждое значение из столбца «Количество на складе» в формулу:
= МИН(366;476;398;982;354;534;408)
2: Сделать ссылки на каждую ячейку столбца Количество:
= МИН(В2; В3; В4; В5; В6; В7; В8)
3: Или просто сослаться на весь диапазон ячеек:
= МИН(В2:В8)
4: в качестве альтернативы вы можете создать именованный диапазон и использовать его вместо этого, чтобы избежать любых прямых ссылок:
2. Как найти самую раннюю дату
Представьте, что вы ждете несколько доставок и хотели бы быть готовыми к самой ближайшей. Как узнать самую раннюю дату в Excel? Легко! Используйте функцию МИН, следуя логике из примера 1:
Примените МИН и выберите даты, ссылаясь непосредственно на ячейки:
= МИН(В2:В8)
или на именованный диапазон:
= МИН(дата_поставки)
3. Поиск абсолютного минимума в Excel
Предположим, что у вас есть диапазон чисел, и вам нужно определить не просто самое маленькое из них, но найти абсолютный минимум или минимум по абсолютному значению (по модулю). Одна только МИН функция не сможет справиться с этим, поскольку она просто будет искать минимальное число. Здесь вам нужна вспомогательная функция, которая может конвертировать все отрицательные числа в положительные.
Есть ли готовое решение для этого случая? Вопрос был риторическим, в Excel есть решение любой задачи. Если у вас есть какие-либо сомнения, просто просмотрите наш блог. 🙂
Но вернемся к нашей задаче. Готовое решение для этого конкретного случая называется функцией ABS, которая возвращает абсолютное значение заданных вами чисел. Таким образом, комбинация функций МИН и ABS сделает свое дело. Просто введите следующую формулу в любую пустую ячейку:
{= МИН(ABS(А1:Е12))}
Важно! Вы заметили фигурные скобки вокруг формулы? Это признак того, что это формула массива, и ее нужно вводить с помощью комбинации клавиш Ctrl + Shift + Enter, а не просто Enter.
Как найти самое маленькое значение в Excel, исключая нули
Вам кажется, что вы уже знаете все о поиске минимальных значений в Excel? Не спешите с выводами, всегда есть чему поучиться. Например, как бы вы определили наименьшее ненулевое значение? Есть идеи? Не обманывай и не гугли, просто продолжай читать;)
Дело в том, что Excel МИН работает не только с положительными и отрицательными числами, но и с нулями. Если вы не хотите, чтобы нули были таким минимумом, вам нужна помощь функции ЕСЛИ (IF). Как только вы добавите ограничение, что ваш диапазон должен быть больше нуля, ожидаемый результат не заставит себя ждать. Вот пример формулы для использования:
{= МИН(ЕСЛИ(В2:В15>0;В2:В15))}
Вы, наверное, заметили фигурные скобки вокруг формулы массива. Просто помните, что вы не вводите их вручную. Они появляются в тот момент, когда вы нажимаете Ctrl + Shift + Enter на клавиатуре.
Нахождение минимума на основе условия
Предположим, вам нужно найти наименьшее количество продаж определенного фрукта в списке. Другими словами, ваша задача – определить минимальное значение на основе некоторого критерия. В Excel условия обычно требуют использования функции ЕСЛИ. Все, что вам нужно сделать, это создать идеальную комбинацию МИН и ЕСЛИ для решения этой задачи:
Нажмите Ctrl + Shift + Enter, чтобы эта функция массива работала, и наслаждайтесь результатом.
Выглядит довольно легко, правда? И как вы определите минимум на основе 2 или более условий? Может быть, есть более простая формула для этой задачи, доступная в Excel? Читайте дальше, чтобы узнать ответ. 😉
Как подсветить наименьшее число в Excel
А что, если вам не нужно возвращать минимальное значение, а вы просто хотите найти его в своей таблице? Самый простой способ найти ячейку – выделить ее. И самый простой способ сделать это – применить условное форматирование. Это даже проще, чем создать формулу:
1 Создайте новое правило условного форматирования, щелкнув Условное форматирование -> Создать правило
2 Когда откроется диалоговое окно «Создание правила форматирования», выберите тип правила «Форматировать только первые или последние значения».
3 Поскольку задача состоит в том, чтобы найти одно-единственное наименьшее значение, выберите опцию «последним» в раскрывающемся списке и укажите 1 в качестве количества ячеек, которые нужно выделить.
Но что делать, если в вашей таблице снова есть ноль? Как игнорировать нули при выделении минимального значения? Не беспокойтесь, для этого случая тоже есть маленькая хитрость:
1. Создайте новое правило условного форматирования, выбрав опцию «Использовать формулу для определения форматируемых ячеек».
2. Введите это выражение в поле «Значения формата» для проверки условия:
=B2=МИН(ЕСЛИ($B$2:$B$15>0;$B$2:$B$15))
Где B2 – первая ячейка диапазона, в которой будет найдено самое маленькое значение
3. Выберите цвет ( Формат… -> Заливка) и нажмите ОК.
4. Наслаждайтесь 🙂
Почему не работает функция МИН?
В идеальном мире все формулы будут работать как часы и возвращать правильные результаты, когда вы нажмете Enter. Но в мире, в котором мы живем, формулы иногда возвращают ошибку, а не результат, который нам нужен. Не беспокойтесь, сама ошибка всегда намекает на ее возможную причину. Вам просто нужно всмотреться в вашу формулу.
Исправление ошибки #ЗНАЧ! в формуле МИН
Как правило, вы получаете #ЗНАЧ! сообщение об ошибке, когда хотя бы одно из значений, использованных в формуле, неверно. Что касается МИН, это может произойти, когда один из аргументов поврежден, например, что-то не так с ячейками, которые использует формула.
Например, #ЗНАЧ! может появиться, если один из аргументов является ячейкой с ошибкой или в ссылке на ячейку есть опечатка.
Что может вызвать ошибку #ЧИСЛО! ?
Excel показывает ошибку #ЧИСЛО!, когда невозможно рассчитать вашу формулу. Обычно это происходит, когда числовое значение слишком велико или мало для отображения в Excel. В Excel допустимы номера от -2,2251E-308 до 2,2251E-308. Если один из ваших аргументов находится вне этого диапазона, функция вернет #ЧИСЛО!
Я получаю сообщение #ДЕЛ/0!, что делать?
Исправление #ДЕЛ/0! – это легко. Не делите на ноль! 🙂 Не шучу, это единственное решение этой проблемы. Проверьте диапазон ваших данных, исправьте ошибку #ДЕЛ/0!, и формула незамедлительно вернет правильный результат.
Ищете минимум, но получаете #ИМЯ?
Сообщение #ИМЯ? означает, что Excel не может распознать формулу или ее аргументы. Наиболее вероятная причина этого результата – опечатка. Вы можете либо неправильно написать формулу, либо указать неверные аргументы для получения такого результата.
Другая возможная причина этой проблемы лежит в именованном диапазоне. Итак, если вы ссылаетесь на несуществующий диапазон или в нем есть опечатка, вы увидите #ИМЯ? в ячейке с результатом.
Это были некоторые из способов найти минимум в Microsoft Excel с помощью функции МИН. Для вас я рассмотрел различные подходы, чтобы найти самое маленькое значение в Excel и найти абсолютный минимум. Вы можете считать эту статью своей шпаргалкой и использовать всякий раз, когда вам нужно найти минимальное число в зависимости от условия, чтобы предотвратить и исправить возможные ошибки.
Вот и все на сегодня. Пожалуйста, не стесняйтесь делиться своими мыслями и вопросами в разделе комментариев, я буду рад получить от вас обратную связь! 🙂
Еще полезная дополнительная информация:
Функция МАКС в Excel: как найти максимальное значение – В этой статье объясняется функция МАКС с множеством примеров формул, которые показывают, как найти наибольшее значение в Excel и выделить наибольшее число на листе. МАКС – одна из самых простых и простых в использовании функций Excel. Тем не менее, у нее… 
Как сделать зависимый выпадающий список в Excel? – Одной из наиболее полезных функций проверки данных является возможность создания выпадающего списка, который позволяет выбирать значение из предварительно определенного перечня. Но как только вы начнете применять это в своих таблицах, то неизбежно столкнетесь с проблемой: нужно сделать один выпадающий список… 
Создаем выпадающий список в Excel при помощи формул – Задача: Создать выпадающий список в Excel таким образом, чтобы в него автоматически попадали все новые значения. Сделаем это при помощи формул, чтобы этот способ можно было использовать не только в Excel 2007 и старше, но и в Excel 2003. Как… 
5 способов создать выпадающий список в ячейке Excel – Одной из наиболее полезных функций при вводе данных является возможность использовать выпадающий список в Excel. Он позволяет выбирать значение из предварительно определенного перечня и разрешает вводить только те данные, которые соответствуют вашим требованиям. Мы предложим вам несколько простых способов, как…
