Лабораторная работа №5. Excel имеет встроенные средства для решения оптимизационных задач

Поиск решения

 

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

Модели математического программирования чрезвычайно популярны и входят в состав многих современных программных продуктов. Оптимизация достигается методом последовательного улучшения начального варианта в несколько шагов.

 

 

Задание. Н айти максимальное значение функции

4*x1 + 5* x2 + 9*x3+ 11* x4 à MAX

при следующих ограничениях

1*x1 + 1*x2 + 1*x3 + 1*x4 < = 15

7*x1 + 5*x2 + 3*x3 + 2*x4 < = 120

3*x1 + 5*x2 + 10*x3 + 15*x4 < = 100

x1> =0; x2> =0; x3> =0; x4 > =0

 

Порядок выполнения работы.

1. Сформируйте шапку таблицы (строка 1)

2. Введите коэффициенты при неизвестных в ограничениях (строки 2, 3, 4)

3. Задайте начальные значения неизвестных, равные нулю (строка 7)

4. Введите в ячейку Е2 формулу ограничения
Е2 = A2*$A$7+B2*$B$7+C2*$C$7+D2*$D$7 и затем скопируйте ее в ячейки Е3: Е4

5. В ячейку Е7 введите функцию цели
Е7 = 4*A7+5*B7+9*C7+11*D7

 

Рис. 6

6. Активизируйте программу «Поиск решения» («Solver») командой
Сервис / Поиск решения или
Сервис / Надстройки / þ Поиск решения

7. В диалоговое окно «Поиск решения» введите оставшиеся данные

Рис. 7

8. Выполните (решите) задачу по данным табл. 2

Таблица 2

Варианты заданий

Вариант	Цель, ограничения
	x1 + 6 * x2 - 7*x3+ x4 + 5*х5 à мин 5 * x1 – 4 * x2 + 13 * x3 – 2 * x4 + х5 = 20 x1 - x2 + 5 * x3 - x4 + х5 = 8 x1> =0; x2> =0; x3> =0; x4 > =0; х5> =0
	2 * x1 - 3* x2 + 6 * x3 + x4 – 2 * х5 à мин 2*x1 – 3 * x2 + x3 + 3 * x4 – х5 = 3 x1 + x2 – 2 * x3 + 9 * x4 = 4 x1> =0; x2> =0; x3> =0; x4 > =0; х5> =0
	60 * x1 + 70 * x2 + 120 * x3 + 130 * x4 à мах x1 + x2 + x3 + x4 < = 16 4 * x1 + 6 * x2 + 10 * x3 + 13 * x 4 < = 100 6 * x1 + 5 * x2 + 4 * x3 + 3 * x 4 < = 110 x1> =0; x2> =0; x3> =0; x4 > =0
	12 * x1 + 20 * x2 + 18 * x3+ 40 * x4 à мах 4 * x1 + 9 * x2 + 7 * x3 + 10 * x4 < = 6000 x1 + x2 + 3 * x3 + 40 * x4 < = 4000 x1> =0; x2> =0; x3> =0; x4 > =0
	x1 – x2 + 3 * x3 – 2 * x4 + х5 à мин 3 * x1 + x2 + 2 * x3 + x4 + х5 = 2 2 * x1 - x2 + x3 + x4 +4 * х5 = 3 x1> =0; x2> =0; x3> =0; x4 > =0
	- x1 + 2 * x2 + x3 + 3 * x4 – х5 à мин x1 + 2 * x2 + 3 * x3 + 2 * x4 – х5 = 6 2 * x2 + 4 * x3 – 4 * x4 + 2*x5 = 6 x2 + x3 + x4 +х5 = 5 x1> =0; x2> =0; x3> =0; x4 > =0; х5> =0
	x1 + 2 * x2 + 3 * x3 + 4 * x4 à мин x1 + x2 - x3 + x4 = 4 x1 + 2 * x2 – 3 * x3 + x4 = 2 x1> =0; x2> =0; x3> =0; x4 > =0
	x1 + 2 * x2 + 3 * x3 + 4 * x4 à мин x1 + x2 + x3 + x4 = 2 x1 – 2 * x2 + 3 * x3 – 4 * x4 = - 2 x1> =0; x2> =0; x3> =0; x4 > =0
	- x1 – 2 * x2 – 3 *x3 à мин 2 * x1 + x2 - x3 < = 2 2 *x1 - x2 + 5 * x3 < = 6 4 * x1 + x2 + x3 < = 5 x1> =0; x2> =0; x3> =0
	2 * x1 + 2 * x2 + x3 + 3* x4 +х5 + 2 * х6 à мин x1 + x2 + x3 < = 350 x4 + x5 + x6 < = 650 х1 + х4 = 300 х2 + х5 = 300 х3 + х6 = 300 x1> =0; x2> =0; x3> =0; x4 > =0; х5> =0; x > =0

 

1. Выполните постановку и решите следующую задачу:

Предприятие выпускает 5 видов продукции П1, П2, П3, П4, П5. Для их изготовления используются 4 вида ресурсов Р1, Р2, Р3, Р4. Известны:

· прибыль, получаемая при реализации единицы продукции каждого вида (1, 2, 3, 4, 3 у.е.);

· граничные значения (верхняя и нижняя) количества выпуска каждого вида продукции (5, 10), (5, 100), (3, 15), (0, 20), (0, 100) единиц;

· ограничения на ресурсы (170, 520, 200, 500 единиц);

· потребности в ресурсах для выпуска единицы каждого вида продукции:

для производства продукции П1 требуется 1, 3, 7, 0 единиц первого вида ресурса,

для производства продукции П2 требуется 12, 1, 5, 7 единиц первого вида ресурса,

для производства продукции П3 требуется 0, 15, 1, 18 единиц первого вида ресурса,

для производства продукции П4 требуется 6, 7, 1, 4 единиц первого вида ресурса,

для производства продукции П5 требуется 1, 12, 4, 10 единиц первого вида ресурса.

Требуется определить оптимальное количество выпуска каждого вида продукции, при котором будет получена максимальная прибыль.