Задачи на нахождение экстремального значения функции
Пример 1.
Пусть требуется определить оптимальный срок эксплуатации оборудования, если известны постоянные (а) и переменные (b) издержки на обслуживание оборудования, параметр изменения издержек во времени (n) и первоначальная стоимость оборудования (А). В зависимости от срока эксплуатации (t) издержки (Е) можно приближенно описать функцией:
Е = a+ btn+A/t.
Решение:
Возьмем конкретные значения параметров:
Е = 20,1+ 3t2+1000/t.
Для этих значений построим в Excel диаграмму
На диаграмме видно, что функция имеет
минимум при t
5
. Уточним это значение. Для этого
вычислим производную функции и приравняем
ее к нулю:
6t +1000/t2=0
В ячейку D3 введем произвольное значение для независимой переменной t (например, 4), в ячейку ЕЗ – формулу для вычисления производной, а в ячейку F3 — формулу для вычисления функции Е.
Далее выполним следующие действия:
1. Вызовем процедуру Подбор параметра (команда Сервис — Подбор параметра).
2. В поле Установить в ячейке мышью укажем ЕЗ, в поле Значение с клавиатуры зададим 0 (правая часть уравнения), в поле Изменяя значение ячейки мышью укажем D3.
3. Щелкнем на кнопке ОК и получим результат подбора, отображаемый в диалоговом окне Результат подбора параметра. Щелкнем на кнопке ОК, чтобы сохранить полученные значения ячеек, участвовавших в операции. В ячейке D3 получим приближенное значение t 5,5 (рис.), а в ячейке F3 отобразится соответствующее минимальное значение затрат Е=292,67. Если уравнение имеет более одного корня, то процедуру поиска корней повторяют несколько раз.
Упражнения
1. Сезонная динамика спроса на товар описывается зависимостью:
S = 28Ln(t)-0,5t2+10/t2, где t' - номер месяца.
Определить, в каком месяце следует ожидать минимальный и максимальный спрос на товар и величину спроса.
Модели линейной оптимизации в ms Excel
Инструментом для решений задач оптимизации в MS Excel служит надстройка Поиск решения. Процедура поиска решения позволяет найти оптимальное значение формулы, содержащейся в ячейке, которая называется целевой. Эта процедура работает с группой ячеек, прямо или косвенно связанных с формулой в целевой ячейке. Чтобы получить по формуле, содержащейся в целевой ячейке, заданный результат, процедура изменяет значения во влияющих ячейках.
Если данная надстройка установлена, то Поиск решения запускается из меню Сервис. Если такого пункта нет, следует выполнить команду Сервис Надстройки и выставить флажок против надстройки Поиск решения.
Решение задачи оптимизации состоит из нескольких этапов.
A. Создание модели задачи оптимизации.
B. Поиск решения задачи оптимизации.
C. Анализ найденного решения задачи оптимизации.
Рассмотрим подробнее эти этапы.
Этап А.
На этапе создания модели вводятся обозначения неизвестных, на рабочем листе заполняются диапазоны исходными данными задачи, вводится формула целевой функции.
Этап В.
Команда Сервис Поиск решения открывает диалоговое окно Поиск решения.
В окне Поиск решения имеются следующие поля:
Установить целевую ячейку — служит для указания целевой ячейки, значение которой необходимо максимизировать, минимизировать или установить равным заданному числу. Эта ячейка должна содержать формулу.
Равной - служит для выбора варианта оптимизации значения целевой ячейки максимизация, минимизация или подбор заданного числа. Чтобы установить число, введите его в поле.
Изменяя ячейки — служит для указания ячеек, значения которых изменяются в процессе поиска решения до тех пор, пока не будут выполнены наложенные ограничения и условие оптимизации значения ячейки, указанной в поле Установить целевую ячейку.
Предположить - используется для автоматического поиска ячеек, влияющих на формулу, ссылка на которую дана в поле Установить целевую ячейку. Результат поиска отображается в поле Изменяя ячейки.
Ограничения - служит для отображения списка граничных условий поставленной задачи.
Добавить — служит для отображения диалогового окна Добавить ограничение.
Изменить — служит для отображения диалоговое окна Изменить ограничение.
Удалить — служит для снятия указанного ограничения.
Выполнить - служит для запуска поиска решения поставленной задачи.
Закрыть — служит для выхода из окна диалога без запуска поиска решения поставленной задачи. При этом сохраняются установки сделанные в окнах диалога, появлявшихся после нажатий на кнопки Параметры, Добавить, Изменить или Удалить.
Параметры - служит для отображения диалогового окна Параметры поиска решения, в котором можно загрузить или сохранить оптимизируемую модель и указать предусмотренные варианты поиска решения. Восстановить — Служит для очистки полей окна диалога и восстановления значений параметров поиска решения, используемых по умолчанию.
Этап C.
Для вывода итогового сообщения о результате решения используется диалоговое окно Результаты поиска решения.
Диалоговое окно Результаты поиска решения содержит следующие поля:
Сохранить найденное решение — служит для сохранения найденного решения во влияющих ячейках модели.
Восстановить исходные значения — служит для восстановления исходных значений влияющих ячеек модели.
Отчеты — служит для указания типа отчета, размещаемого на отдельном листе книги.
Результаты. Используется для создания отчета, состоящего из целевой ячейки и списка влияющих ячеек модели, их исходных и конечных значений, а также формул ограничений и дополнительных сведений о наложенных ограничениях.
Устойчивость. Используется для создания отчета, содержащего сведения о чувствительности решения к малым изменениям в формуле (поле Установить целевую ячейку, диалоговое окно Поиск решения) или в формулах ограничений.
Ограничения. Используется для создания отчета, состоящего из целевой ячейки и списка влияющих ячеек модели, их значений, а также нижних и верхних границ. Такой отчет не создается для моделей, значения в которых ограничены множеством целых чисел. Нижним пределом является наименьшее значение, которое может содержать влияющая ячейка, в то время как значения остальных влияющих ячеек фиксированы и удовлетворяют наложенным ограничениям. Соответственно, верхним пределом называется наибольшее значение.
Сохранить сценарий — служит для отображения диалогового окна Сохранение сценария, в котором можно сохранить сценарий решения задачи, чтобы использовать его в дальнейшем с помощью диспетчера сценариев MS Excel.