Поиск решения MS EXCEL. Экстремум функции с несколькими переменными. Граничные условия заданы уравнениями
Пусть дана функция с несколькими переменными F(x1, x2, . )=a1*x1+a2*x2+. Также даны граничные условия в виде b1*x1+b2*x2+. файл примера ).
Переменные (выделено зеленым) . В качестве переменных модели, очевидно, выступают x1, x2, x3, x4. Эта задача хороша тем, что переменные задаются однозначно, не требуется осмысливать житейскую задачу, например как с оптимизацией затрат . Хотя математически — это эквивалентные задачи, только количество переменных разное.
После запуска Поиск решения будет методично (последовательно) по своему алгоритму подставлять в зеленые ячейки числовые значения и вычислять функцию F (красная ячейка).
Ограничения (выделено серым) . Ограничения модели — это ограничения на область изменения переменных. Они могут задаваться как простыми выражениями для одной переменной, например х1>=0, так и для некой комбинации переменных 5*x1+4*x2-x3-2*x4 =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.
Как найти точку максимума функции в excel
Если функция F(x) непрерывна на отрезке [a, b] и имеет внутри этого отрезка локаль-ный экстремум, то его можно найти, используя надстройку Excel Поиск решения. Рассмотрим последовательность нахождения экстремума функции на примере.
Пример 12. Задана неразрывная функция Y= X 2 +X +2. Требуется найти ее экстремум (минимальное значение) на отрезке [-2, 2].
Решение:
1. В ячейку А3 рабочего листа введите любое число, принадлежащее заданному отрезку, в этой ячейке будет находиться значение Х.
2. В ячейку В3 введите формулу, определяющую заданную функциональную зависимость (рис. 18). Вместо переменной Х в этой формуле должна быть ссылка на ячейку А3: = A2^2 + A2 +2.
3. Выполните команду меню Сервис — Поиск решения.
4. В открывшемся окне диалога Поиск решения в поле Установить целевую ячейку укажите адрес ячейки, содержащей формулу (В3), установите пере-ключатель Минимальному значению, в поле Изменяя значение ячейки укажите адрес ячейки, в которой содержится переменная х.
5. Добавьте два ограничения в соответствующее поле: A3>= -2 и A3
6. Щелкните на кнопке Параметры и в от крывшемся диалоговом окне Пара-метры поиска решения установите относительную погрешность вычислений и предельное число итераций.
7. Щелкните на кнопке Выполнить.
В ячейке А3 будет помещено значение аргумента Х функции, при котором она принимает минимальное значение, а в ячейке В3 – минимальное значение функции. В результате выполнения вычислений в ячейке А3 будет получено значение независимой переменной, при котором функция принимает наименьшее значение, а в ячейке В3 – минимальное значение функции, равное 1,75. Постройте график заданной функции и убедитесь, что решение найдено верно.
Рисунок 18
XL: Поиск максимума функции
Господа!
Такой вопрос:
Требуется найти экстремум функции, заданной значениями в нескольких точках, которые записаны в массиве из двух векторов:агрумента X и функции Y.
С точки зрения математики, для решения этой задачи требуется аппроксимировать функцию (кривой второго порядка, например), найти производную функции и приравнять её нулю. С большой долей вероятности полученное решение уравнения будет экстремумом функции.
Но как это можно организовать в Excel?
Желательно, использовать только встроенные функции Excel, a-la ПРЕДСКАЗ().
За ранее благодарен.
16 ответов
10 декабря 2003 года
901 / / 23.10.2003
Originally posted by IKor
Господа!
Такой вопрос:
Требуется найти экстремум функции, заданной значениями в нескольких точках, которые записаны в массиве из двух векторов:агрумента X и функции Y.
С точки зрения математики, для решения этой задачи требуется аппроксимировать функцию (кривой второго порядка, например), найти производную функции и приравнять её нулю. С большой долей вероятности полученное решение уравнения будет экстремумом функции.
Но как это можно организовать в Excel?
Желательно, использовать только встроенные функции Excel, a-la ПРЕДСКАЗ().
За ранее благодарен.
Не совсем понял, что должен делать макрос — записать в ячейки значения экстремумов функции, либо построить график, либо ещё что?
В принципе, это-то ведь просто всё — нужно только мат. описание поиска экстремумов (я уже не помню все эти методы математичексих предствалений). А там уже надо их просто описать в коде. И всё. Блин, я в своё время в институте на ЕС такую фигню вообще сделал методом построения графика функции и поиска её макисмумов, ака в массиве ищутся максимумы -и всё.:)
10 декабря 2003 года
1.2K / / 17.10.2002
На мой взгляд, ты поставил задачу некорректно уже с точки зрения математики. Например, взяв просто максимум из всех игреков, ты получишь прекрасного кандидата на максимум всей функции. Если рассматривать в общем виде, то очевидно, все зависит от того, какой способ интерполяции ты выберешь. Параболу, например, ты всегда гарантированно проведешь только через три точки. Если брать в общем виде — многочлен n-1-й степени (n — количество точек), то получишь один результат. А можно, например, приближать сплайнами. Если кто не знает, сплайны — это склейки из кривых, как правило многочленов третьей степени. То есть у тебя между каждыми двумя данными точками кривая строится как многочлен 3-й степени. У многочлена 3-й степени 4 степени свободы (4 коэффициента). 2 из них, очевидно, затрачиваются на то, чтобы кривая проходила через данные точки. Еще одно обычно тратят на то, чтобы она была гладкой в точках склеек — то есть, чтобы производная при подходе к точке склейки слева равнялась производной справа. Последнюю сепень свободы тратят по-разному в зависимости от задачи. Иногда — чтобы и вторая производная в точках склейки существовала, иногда — чтобы первая производная в них равнялась 0. Да как угодно.
Сам понимаешь, что во всех вышеописанных случаях максимум/минимум получившейся функции будет попадать в разные места. А если у тебя данные — результат статистических измерений, то неразумно пытаться провести сложную криву точно через точки. Правильным будет взять характер кривой сообразно теоретическим представлениям (например, экспонента) и провести ее что-нибудь вроде методом наименьших квадратов. В общем, сформулируй задачу корректно с точки зрения математики.
10 декабря 2003 года
273 / / 12.02.2003
Originally posted by IKor
Господа!
Такой вопрос:
Требуется найти экстремум функции, заданной значениями в нескольких точках, которые записаны в массиве из двух векторов:агрумента X и функции
.
Но как это можно организовать в Excel?
Желательно, использовать только встроенные функции Excel, a-la ПРЕДСКАЗ().
За ранее благодарен.
Алгоритм реализуется с помощью кубического сплайна.
Кривая между соседними точками моделируется у-ем
Y = a0 + a1*X + a2*X^2 + a3*X^3
Четыре неизвестных коэф-та находим из 4-х уравнений. Два берутся по значениям X,Y в двух рассматриваемых точках. Еще два по производным в этих точках. Производная в точке (i) равна наклону
отрезка между точками (i-1) и (i+1).
Это можно реализовать и без программирования в принципе, но больно громоздко.
Если точек у тебя не больше шести удобно воспользоваться трендом на графике. (Там полином не боше 6-й степени)
Можно в принципе определить коэффициенты полинома N-й степени с помощью Анализа в Сервисе, но для этого надо подготовить колонки X^2 X^3 . X^N
Функция МАКС
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» является обязательным, последующие числа необязательные. От 1 до 255 чисел, среди которых требуется найти наибольшее.
Замечания
- Аргументы могут быть либо числами, либо содержащими числа именами, массивами или ссылками.
- Учитываются логические значения и текстовые представления чисел, которые непосредственно введены в список аргументов.
- Если аргумент является массивом или ссылкой, то учитываются только числа. Пустые ячейки, логические значения и текст в массиве или ссылке игнорируются.
- Если аргументы не содержат чисел, функция МАКС возвращает значение 0 (ноль).
- Аргументы, которые являются значениями ошибки или текстами, не преобразуемыми в числа, приводят в возникновению ошибок.
- Если в ссылку в качестве части вычислений необходимо добавить логические значения и текстовые представления, воспользуйтесь функцией МАКСА.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.