Как найти локальный минимум функции в excel

Как найти локальный минимум функции в 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

Поиск решения 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

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

Лабы по информатике / Laboratornaya_rabota_10

Если функция f(x) непрерывна на отрезке [a, b] и имеет внутри этого отрезка локальный экстремум, то его можно найти, используя надстройку MS Excel Поиск решения.

Рассмотрим последовательность нахождения экстремума функции на следующих примерах.

Пусть задана функция Z = X 4 + Y 4 – X 2 – 2XY – Y 2 . Требуется найти ее экстремум (минимальное значение).

Для решения задачи выполните действия (рис. 1):

• В ячейку В4 рабочего листа введите любое число принадлежащее области определения функции, в этой ячейке будет находиться значение Х (например 5);
• В ячейку В5 введите любое число принадлежащее области определения функции, в этой ячейке будет находиться значение У (например 2);
• В ячейку В6 введите формулу определяющую заданную функцию. Вместо переменной Х в этой формуле должна быть ссылка на ячейку А4, а вместо переменной У ссылка на ячейку В5.

Р ис. 1.

• Выполните команду меню Сервис | Поиск решения (рис.2);

Рис. 2.

• Н ажав на кнопку Параметры в окне инструмента Поиск решения, откройте окно Параметры поиска решения (рис. 3) и установите: число итераций – 1000, относительная погрешность 0,00001.

Рис. 3.

• В поле Установить целевую ячейку укажите адрес ячейки, содержащей формулу ( B6), установите переключатель Минимальному значению, в поле Изменяя ячейки введите адрес ячеек, содержащих Х и У (А4:А5);
• Щелкните на кнопке Выполнить. В ячейках В4 и В5 будут помещены значения Х и У при которых функция Z имеет минимальное значение, а в ячейке В6 – минимальное значение функции.

Ответ: X = 0,99999; Y = 1; Z = −2. Пример 2. Пусть задана функция Z= X2+Y2. Требуется найти ее экстремум (минимальное значение) при условии: X+ 2Y= 1. Для решения задачи выполните действия (рис. 4):

• В ячейку В4 рабочего листа введите любое число принадлежащее области определения функции, в этой ячейке будет находиться значение Х;
• В ячейку В5 введите любое число принадлежащее области определения функции, в этой ячейке будет находиться значение У;
• В ячейку В6 введите формулу, определяющую заданную функцию. Вместо переменной Х в этой формуле должна быть ссылка на ячейку В4, а вместо переменной У ссылка на ячейку В5.
• В ячейку В6 введите формулу функции =В4^2 + В5^2.
• В ячейку B7 введите формулу условия = В4 + 2*В5.
• В ячейки В4 и В5 введите начальные значения (например 1 и 3 соответственно).

Рис. 4.

• Выполните команду меню Сервис | Поиск решения;
• Настройте параметры инструмента Поиск решения: число итераций – 1000, относительная погрешность 0,00001.
• В поле Установить целевую ячейку укажите адрес ячейки, содержащей формулу ( B6), установите переключатель Минимальному значению, в поле Изменяя ячейки введите адрес ячеек содержащих Х и У (В4:В5);
• Д обавьте ограничение, заполнив форму (рис. 5) и щелкните по кнопке

Рис. 5. Выполнить. В ячейке В6 будет помещено минимальное значение функции. В ячейках В4 и В5 величины Х и У при которых функция Z достигает минимума. Ответ: X = 0,2; Y = 0,4; min Z = 0,2. 2. Решение систем нелинейных уравнений Применяя надстройку MS Excel Поиск решения можно решать системы нелинейных уравнений. Рассмотрим последовательность решения на примере. Пример Дана система двух уравнений: Требуется найти все корни приведенных уравнений, если такие существуют. Шаг 1.Для определения существования корней построим графики уравнений, представив их в виде y=f(x). Первое уравнение: Границы изменения аргумента [‑1,7; 1,7]. Второе уравнение: Границы изменения аргумента [-; +]. Ш аг 2.Построим графики уравнений Y1, Y2 и Y3 в границах изменения аргумента [-1,7; 1,7]. В этих границах находится решение первого уравнения. В ячейки А4:А38 вводим аргумент Х. В ячейку В4 вводим формулу =КОРЕНЬ(3−А4^2). В ячейку С4 вводим формулу =−КОРЕНЬ(3−А4^2). В ячейку D4 вводим формулу =(1−2*A4)/3. Введенные формулы копируем до 38 строки. Выделяем диапазоны: В4:В38;С4:С38 и D4:D38. Строим график (рис. 6). Рис. 6. Как видно из рисунка решением системы уравнений являются точки пересечения окружности и прямой. Приблизительное значение корней: [X₁; Y₁] ~ [-1,3; 1,2], [X₂; Y₂] ~ [1,7; -0,8]. Шаг 3. Для дальнейшего поиска корней необходимо привести систему к одному равносильному уравнению. Пара (х, у) является решением системы тогда и только тогда, когда она является решением следующего уравнения с двумя неизвестными: (х 2 + у 2 − 3) 2 + (2х + 3у − 1) 2 = 0. Шаг 4.Для уточнения значения 1-го корня выполняем следующие действия (адреса ячеек могут быть другие): В ячейки B1:B2 заносим приблизительные значения 1-го корня -1,3 и 1,2 соответственно. В ячейку В3 записываем формулу равносильного уравнения. Вызываем надстройку Поиск решения, и вносит в ее форму наши данные. Находим решение Первый корень: [X₁; Y₁] = [-1,26859; 1,179031] Аналогично уточняем 2-й корень, занося в ячейки B1:B2 приблизительные значения 2-го корня 1,7 и -0,8 соответственно. Находим решение Второй корень: [X₂; Y₂] = [1,576208; -0,7175]. Шаг 5.Табуляция равносильного уравнения по двум переменным X и Y. Для табуляции уравнения из рисунка 6 определяем отрезки табуляции по переменной X [−1,7; 1,7], по переменной Y [−1; 1,6] и выполняем следующие действия:

• На новом рабочем листе в столбец А начиная с ячейки А2 введите последовательность значений Х с шагом 0,1, а в строку 1, начиная с ячейки В1 введите последовательность значений Y с шагом 0,2.
• Присвойте диапазонам значений Х и У имена Х и У, соответственно (команда Вставка | Имя | Присвоить …)
• В ячейку В2 введите формулу

=(Х^2 +Y^2 −3)^2+(2*X+3*Y−1)^2 и скопируйте ее по всем переменным, заполнив ею диапазон В2:О36. П о полученным данным строим график равносильного уравнения представляющего собой поверхность. Корни системы уравнений находятся во впадинах, где уравнение принимает наименьшие значения. Контрольные вопросы

1. Для решения, каких задач применяется надстройка MS Excel Поиск решения?
2. Объясните процесс подготовки задачи к решению с помощью надстройки Поиск решения.
3. Объясните назначение элементов диалоговых окон: Поиск решения и Параметры поиска решения.

9