Определение оптимальных значений параметров целевой функции средствами ms Excel Цель работы
Освоить методику выбора оптимальных значений влияющих параметров целевой функции с помощью надстройки «Поиск решения» MS Excel.
Краткие теоретические сведения
Процессор электронных таблиц MS Excel предоставляет достаточно функциональные средства анализа данных. Одним из них является надстройка1 «Поиск решения», которая позволяет достаточно быстро подобрать оптимальные параметры для целевой функции. Данная опция не устанавливается по умолчанию при первоначальной установке пакета MS Office. Порядок её установки зависит от используемой версии пакета. Для уточнения наличия данной опции необходимо обратиться к системному администратору.
Поиск оптимальных значений параметров целевой функции состоит из трёх этапов:
подготовка данных на листе MS Excel: ввод целевой функции и ограничений;
указание целевой ячейки, цели оптимизации, ячеек с изменяемыми значениями параметров и ввод списка ограничений в диалоговом окне «Поиск решения»;
ввод дополнительных параметров поиска решения и запуск процесса.
Если задача поставлена корректно и все данные введены правильно, то решение обычно находится достаточно быстро. Если задача не имеет приемлемого решения или при вводе данных допущена ошибка, то MS Excel выведет сообщение о неудачной попытки поиска оптимальных значений параметров.
Методические указания
Рассмотрим порядок действий по определению значений параметров целевой функции на примере. Пусть целевая функция имеет вид:
Значения параметров должны удовлетворять следующим ограничениям:
Подготовим данные
на листе MS
Excel.
Для этого введём в ячейки B3:F3
обозначения всех используемых параметров:
.
Введём коэффициенты целевой функции в
ячейки B4,
C4
и E4.
Зададим начальные значения параметров
в ячейках B5,
C5
и E5
равными нулю. Введём в ячейку G4
формулу, соответствующую целевой
функции: =B4*B5+C4*C5+E4*E5. Введём в соответствующие
ячейки диапазона B10:F12
коэффициенты при неизвестных параметрах
в ограничениях. Если какой-либо параметр
в ограничении отсутствует, то
соответствующую ячейку следует оставлять
пустой. В ячейку G10
ввести универсальную для всех ограничений
формулу =B10*$B$5+C10*$C$5+D10*$D$5+E10*$E$5+ +F10*$F$5 и
протянуть её в ячейки G11
и G12.
В ячейки H10:H12
введём пороговые значения, у казанные
в правой части знаков равенства или
неравенства первых трёх ограничений.
После ввода дополнительных поясняющих
надписей и форматирования ячеек этап
подготовки данных будет закончен. Вид
таблицы на данном этапе показан на
рисунке 1.
Рисунок 1 – Вид таблицы после подготовки данных
Зададим параметры
поиска решения. Для этого откроем
диалоговое окно «Поиск решения» так,
как описано в предыдущем разделе.
Установим целевую ячейку $G$4,
равной максимальному значению. В секции
«Изменяя ячейки» укажем ячейки, в которых
содержатся значения параметров
:
$B$5:$C$5;$E$5 соответственно. В список
«Ограничения» внесём имеющиеся
ограничения, используя кнопку «Добавить».
После ввода всех перечисленных данных
окно «Поиск решения» будет иметь вид,
изображённый на рисунке 2.
Рисунок 2 – Вид окна поиска решения после ввода параметров поиска.
Нажав кнопку «Параметры», введём дополнительные параметры поиска. В появившемся окне следует поставить галочки перед надписями «Линейная модель» и «Неотрицательные значения», остальные значения следует оставить по умолчанию2 и нажать кнопку «ОК». Окно дополнительных параметров исчезнет. После нажатия на кнопку «Выполнить» MS Excel подберёт оптимальные значения параметров целевой функции в ячейках B5, C5 и E5. Оптимизационная задача решена.