- •Российский заочный институт текстильной и легкой промышленности
- •123423 Москва ул. Демьяна Бедного, 7.
- •1. Решение нелинейных уравнений и оптимизация в Excel
- •1.1 Надстройки в Excel
- •2.Надстройка «Подбор параметра»
- •2.1. Введение
- •2.2. Пример использования надстройки «Подбор параметра»
- •2.3. Подбор процентной ставки
- •2.4. Поиск корней нелинейного уравнения
- •2.5. Задача о портфеле ценных бумаг
- •3. Надстройка «Поиск решения»
- •3.1. Оптимизация нелинейной функции
- •3.2 Решение задачи линейного программирования
- •3.3 Решение нелинейных уравнений
- •4. Лабораторные работы
3. Надстройка «Поиск решения»
3.1. Оптимизация нелинейной функции
3.1.1 Оптимизация нелинейной функции без учета ограничений
Пусть требуется определить минимальное значение функции . Функция достаточно простая, и мы используем ее для ознакомления с правилами применения надстройки. Эта функция имеет единственный минимум в точке, поэтому в качестве начального приближения можно выбрать произвольное значение, например, ноль. Выберем две любые ячейки рабочего листаExcel. Пусть это будут А2 и В2. В первую запишем начальное значение, равное нулю, во вторую – формулу, по которой вычисляется, и нажмем клавишуEnter.Excelвычислит функцию.
В меню Сервис выберем командуПоиск решения.Появится окно «Поиск решения» (рис.3.1). Если перед вызовом командыПоиск решения выделить ячейку, в которой вычислено значение функции, то адрес этой ячейки автоматически запишется в поле «Установить целевую ячейку». В противном случае придется записывать его самостоятельно. В поле «Изменяя ячейки» поместите адрес аргумента (А2). Обратите внимание на то, что адреса в полях записаны со знаком доллара (используются абсолютные адреса). Устанавливайте адреса ячеек с помощью мыши, а не вводите их вручную. Это поможет избежать ошибок ввода.
Рис.3.1. Вычисление функции и окно «Поиск решения»
Для того, чтобы сообщить программе о необходимости вычислить минимум функции, щелкните мышью переключатель рядом с надписью «минимальному значению». Затем щелкните на кнопке Выполнить. После выполнения некоторого количества итераций программа выведет на экран окно «Результаты поиска решения» (рис.3.2), а в ячейках В2 и А2 появятся минимальное значение функции и соответствующее значение аргумента.
В окне «Результаты поиска решения» (рис.3.2) выберите переключатель «Сохранить найденное значение», затем в списке «Тип отчета» - строку «Результаты» и щелкните на кнопке ОК. На рабочем листе «Отчет по результатам 1» будет создан отчет (рис.3.3). Он выдает следующие сообщения.
Адрес ячейки, в которой записано максимальное (или минимальное) значение целевой функции, имя функции, ее исходное значение и результат.
Адрес или адреса аргументов (изменяемых ячеек), их имена, исходные значения и результаты.
Сведения об ограничениях.
Пусть требуется найти минимум функции нескольких аргументов, например . В этом случае разделяйте символом «;» (точка с запятой) адреса отведенных для аргументов ячеек, когда записываете их в поле «изменяя ячейки» окна «Поиск решения».
Рис.3.2 Окно «Результаты поиска решения»
Рис.3.3 Отчет по результатам
3.1.2 Оптимизация нелинейной функции с учетом ограничений
При попытке найти максимум функции будет выдано сообщение об ошибке (рис.3.4). Появление на экране этого диалогового окна является признаком неограниченности целевой функции. Действительно, с ростом абсолютного значения аргументацелевая функциятолько растет. Однако, если ввести ограничения на, то максимальное значениебудет найдено. Максимум будет иметь место приили.
Рис.3.4 Сообщение об ошибке
Ограничения записываются в поле «Ограничения» окна «Поиск решения». Щелкните на кнопке Добавить. Откроется окно «Добавление ограничения» (рис.3.5). В поле «Ссылка на ячейку» укажите адрес ячейки, в которой вычисляется левая часть ограничения. Записывать формулы в этом поле не следует. В рассматриваемом примере в левом поле записан адрес аргумента. В следующем поле следует выбрать нужный знак логической операции ( >=, <=, =) и в поле «Ограничение» надо записать константу или адрес ячейки, в которой она размещена. Затем щелкните на кнопкеДобавить,если надо добавить следующее ограничение или на кнопкеОК. В зависимости от того, каким было выбрано начальное значение, будут получены разные результаты. Это объясняется тем, что у функциина отрезке [0,1] имеются максимумы (два) в граничных точках отрезка.
Рис.3.5 Окно «Добавление ограничения»
В диалоговом окне «Поиск решения» имеется кнопка Параметры. Щелчок по этой кнопке вызывает появление диалогового окна «Параметры поиска решения». Большинство принятых по умолчанию параметров, перечисленных в этом окне, обычно обеспечивают получение решения задачи об оптимуме нелинейной функции с приемлемой точностью. Но значения некоторых параметров зависят от типа решаемой задачи. Так переключатель «Линейная модель» лучше отключить, при решении задачи нелинейного программирования и включить, если решается задача с линейной целевой функцией и линейными ограничениями. Автоматическое масштабирование используется в тех случаях, когда аргументы целевой функции различаются по величине. Методы оптимизации хорошо работают, когда аргументы - величины одного порядка. Если аргументы целевой функции могут принимать только положительные значения, следует отметить флажок «Неотрицательные значения» вместо добавления ограничений типа.
Рис.3.6 Окно «Параметры поиска решения»
Рассмотрим приведенный в разделе 1 пример определения таких размеров бака объемом 20м3, которые соответствуют минимуму площади его поверхности (рис.1.2). Чем меньше поверхность бака, тем меньше материала уйдет на его изготовление. В ячейки В1÷В3 рабочего листаExcelзапишем значения переменныхкоторые могут быть выбраны произвольно, в пределах разумного конечно. В ячейку В4 запишем формулу вычисления объема бака
=В1*В2*В3
В ячейке В5 разместим формулу вычисления площади поверхности бака.
=2*(В1*В2+(В1+В2)*В3)
В поле «Ограничения» окна «Поиск решения» надо записать ограничения
В4=20
В1<=0
B2<=0
B3<=0
Вместо трех последних неравенств можно отметить флажок «Неотрицательные значения» в окне «Параметры поиска решения». После щелчка на кнопке Выполнить в ячейках В1÷В3 программа разместит оптимальные значения переменныходинаковых по величине, равные 2,7144. Площадь поверхности бака, представляющего собой куб, равна 44,208м2.
Если по условию задачи переменные должны принимать целочисленные значения, следует в поле «Ограничения» окна «Поиск решения» записать ограничения (рис.3.5):
a= целое
b= целое
h= целое
В этом случае получим следующий результат:
a=5,b=2,h=2,V=20,S=48