 |
Контрольное задание 1. Определение оптимальных значений целевой функции средствами MS Excel
|
|
|
|
Освоить методику выбора оптимальных значений влияющих параметров целевой функции с помощью надстройки «Поиск решения» MS Excel.
Краткие теоретические сведения
Процессор электронных таблиц MS Excel предоставляет достаточно функциональные средства анализа данных. Одним из них является надстройка «Поиск решения», которая позволяет достаточно быстро подобрать оптимальные параметры для целевой функции. Данная опция не устанавливается по умолчанию при первоначальной установке пакета MS Office. Порядок ее установки зависит от используемой версии пакета. Для уточнения наличия данной опции необходимо обратиться к системному администратору.
Поиск оптимальных значений параметров целевой функции состоит из трех этапов:
– подготовки данных на листе MS Excel: ввода целевой функции и ограничений;
– указания целевой ячейки, цели оптимизации, ячеек с изменяемыми значениями параметров и ввода списка ограничений в диалоговом окне «Поиск решения»;
– ввода дополнительных параметров поиска решения и запуска процесса.
Если задача поставлена корректно и все данные введен правильно, то решение обычно находится достаточно быстро. Если задача не имеет приемлемого решения или при вводе данных допущена ошибка, то MS Excel выведет сообщение о неудачной попытке поиска оптимальных значений параметров.
Методические указания
Рассмотрим порядок действий по определению значений параметров целевой функции на примере. Пусть целевая функция имеет вид:
F = 13 x 1 – 8 x 2 + 10 x 4 ®max.
Значения параметров должны удовлетворять следующим ограничениям:
2 x 1 – x 2 – 2 x 4 + x 5 = 3,
3 x 1 + 2 x 2 + x 3 – 3 x 4 = 8,
- x 1 + 3 x 2 + x 4 ≤ 6,
x 1, x 2, x 3, x 4, x 5 ≥ 0.
Подготовим данные на лиcте MS Excel. Для этого введем в ячейки B3:F3 обозначения всех используемых параметров: x 1, x 2, x 3, x 4, x 5. Введем коэффициент целевой функции в ячейки B4, C4, и E4. Зададим начальные значения параметров x 1, x 2, x 4,в ячейках 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 равной максимальному значению. В секции «Изменяя ячейки» укажем ячейки, в которых содержатся значения параметров x 1, x 2, x 4 : $B$5:$C$5:$E$5 соответственно. В список «Ограничения» внесем имеющиеся ограничения, используя кнопку «Добавить». После ввода всех перечисленных данных окно «Поиск решения» будет иметь вид, изображенный на рисунке 2.
Рис.2 Вид окна поиска решения после ввода параметров поиска
Нажав кнопку «Параметры», введем дополнительные параметры поиска. В появившемся окне следует поставить галочки перед надписями «Линейная модель» и «Неотрицательные значения», остальные значения следует оставить по умолчанию и нажать кнопку «ОК». Окно дополнительных параметров исчезнет. После нажатия на кнопку «Выполнить» MS Excel подберет оптимальные значения параметров целевой функции в ячейках B5, C5 и E5. Оптимизационная задача решена.
|
|
|
Варианты заданий
| Номер варианта | Условия задачи | Номер варианта | Условия задачи | |
| F = -11x1-10x2+120x3 ® max, 7x1-4x2+48x3 ≤ 13, -7x1-8x2+104x3 ≤ 23, -10x1-6x2+72x3 ≤ 3, x1, x2,x3 ≥ 0. | F=-21x1-20x2+45x3 ® max, 11x1-8x2+18x3 ≤ 13, -15x1-16x2+36x3 ≤ 23, -16x1-126x2+27x3 ≤ 3 x1, x2,x3 ≥ 0 | |||
F=-11x1-10x2+90x3  max
7x1-4x2+36x3 ≤ 13,
-7x1-8x2+72x3 ≤ 23,
-10x1-6x2+54x3 ≤ 3,
x1, x2,x3 ≥ 0.
| F=2x1+2x2-x3-x4 ® max, x1-x2+2x3-x4 ≤ 2, 2x1+x2-3x3+x4 ≤ 6, x1+x2+x3+x4 ≤ 7 x1, x2,x3, x4 ≥ 0 | |||
F=-11x1+20x2+45x3  max
7x1-4x2+6x3 ≤ 13,
-7x1+8x2-2x3 ≤ 23,
-10x1-6x2+23x3 ≤ 66,
x1, x2,x3 ≥ 0.
| F=1,5x1-2x2-3x3-2x4 ® min, 3x1+4x2+3x3-x4 ≤ 3, -2x1-x2+x3-x4 ≤ 1, x1, x2,x3, x4 ≥ 0 | |||
| F=-11x1+10x2+45x3 max
7x1-4x2+18x3 ≤ 13,
-7x1-8x2+36x3 ≤ 23,
-10x1-6x2+27x3 ≤ 3,
x1, x2,x3 ≥ 0.
| F=x1+2x2-x3+x4 ® max, 2x1-3x2+x3+x4 ≤ 3, x1+2x2-x4 ≤ 3, 3x1+x2+x3 ≤ 8, x1, x2,x3, x4 ≥ 0 | |||
| F=-9x1+10x2+120x3 max
-x1+4x2+48x3 ≤ 13,
9x1+8x2+104x3 ≤ 23,
2x1+6x2+72x3 ≤ 3,
x1, x2,x3 ≥ 0.
| F=3x1-5x2-2x3+4x4 ® max, 2x1+x3+3x4 ≤ 17, 4x1+x2+x4 ≤ 12, x1+2x2+8x3-x4 ≤ 6, x1, x2,x3, x4 ≥ 0 | |||
Контрольное задание 2.
|
|
|
