отчет по оптимизации
.docМинистерство образования Республики Беларусь
Учреждение образования
Белорусский государственный университет информатики и радиоэлектроники
Инженерно-экономический факультет
Кафедра экономической информатики
Отчёт по лабораторной работе №3
Тема « РЕШЕНИЕ ОПТИМИЗАЦИОННЫХ ЗАДАЧ СРЕДСТВАМИ ЭЛЕКТРОННОЙ ТАБЛИЦЫ EXCEL »
Выполнил:
студент гр. XXX
Проверила:
Верняховская В.В.
Минск, 2009
Цель работы:
Научиться находить решения оптимизационных задач средствами электронной таблицы EXCEL.
Задание 1.
Собственные средства банка составляют 100 единиц. Банк получает прибыль, выдавая кредиты и покупая ценные бумаги. Доходность кредитов составляет 15%, ценных бумаг – 10%. Таким образом, годовая прибыль банка составляет:
F(x, y)=0,15x+0,1y
где x объем средств, выданных в виде кредитов, а y средства, затраченные на покупку ценных бумаг. Используя "Поиск решения" найдём максимальную прибыль банка при условии ограничений:
Выполнение:
Создадим таблицу, как указано на рис. 1.
рис. 1
Для ячейки E4 введём формулу: =СУММПРОИЗВ($B$3:$C$3,B4:C4) и скопируем
ее в ячейки E7, E8, E9. Выполним команду Сервис/Поиск решения… Заполним вызванное окно в соответствии с рис. 2.
рис. 2
Ограничения записываются через использование кнопки Добавить, где вносятся обозначения ограничений задачи. рис. 3
рис. 3
После заполнения окна Поиска решений… перейдём по кнопке Параметры в окно Параметры поиска решений (рис. 4) и установим там флажки напротив полей: Линейная модель и Неотрицательные значения.Далее выполним команду ОК, и нажмем клавишу Выполнить в окне Поиска решений.
рис. 4
Т.к. все выполнено правильно, на экране появилось сообщение о найденном
решении. рис. 5
рис. 5
В результате выполнения задания появилось следующее решение данной
оптимизационной задачи. рис. 6
рис. 6
Задание 2.
Процесс изготовления двух видов (А и В) изделий заводом требует, во-первых последовательной обработки на токарных и фрезерных станках, и, во-вторых затрат двух видов сырья: стали и цветных металлов. Данные о потребности каждого ресурса на единицу выпускаемой продукции и общие запасы ресурсов приведены в таблице. Прибыль от реализации единицы изделия А – 3 тыс. руб., а единицы Б – 8 тыс. руб. Определить такой план выпуска продукции, который обеспечивает максимальную прибыль при условии, что время работы фрезерных станков должно быть использовано полностью.
Выполнение: производиться аналогично заданию 1.
Результат на рис.7
рис. 7
Задание 3.
Предприятие выпускает три вида продукции А, В и С. Реализация единицы
продукции А даёт прибыль 9 руб., В – 10 руб. а С – 16 руб. Сбыт продукции обеспечен, т.е. её можно производить в любых количествах, но запасы сырья ограничены. В таблице приведены нормы расхода сырья на производство единицы продукции и запасы трёх видов необходимого сырья. Найти план выпуска продукции, при котором прибыль будет максимальна.
Выполнение: производиться аналогично заданию 1.
Результат на рис.8
рис. 8
Задание 4.
Предприятие располагает ресурсами сырья трёх видов: С1, С2 и С3. Используя это сырьё, оно выпускает четыре вида продукции: П1, П2, П3 и П4. В таблице указаны затраты каждого вида сырья на изготовление 1 тонны продукции каждого вида и объём ресурсов сырья. Прибыль, получаемая от реализации 1 тонны продукции равна: П1 – 48, П2 – 25, П3 – 56, П4 – 30. Определить ассортимент выпускаемой продукции, при котором прибыль будет максимальной, при условии, что продукции П2 необходимо выпустить не менее 8 т, продукции П4 не более 5 т, а продукции П1 и П3 в отношении 3:1.
Выполнение: производиться аналогично заданию 1.
Результат на рис.9
рис. 9
Задание 5.
Месячный фонд зарплаты сотрудников больницы составляет 50000 руб. Штат
больницы и коэффициенты в формуле для расчёта окладов сотрудников приведены в таблице. Оклады определяются по формуле: оклад=А*x+В, где x – оклад санитара. Определить оклады всех сотрудников.
Выполнение: производиться аналогично заданию 1, только здесь ЦФ стремится к опред. значению и изменяем ячейку С3. Все остальные рассчитываюстя по формуле Ax+B.
Результат на рис.10
рис. 10
Задание 6.
С трех баз надо перевезти грузы в два магазина. Количество груза (в штуках) на базах – в таблице:
|
База 1 |
База 2 |
База 3 |
|
18 |
75 |
31 |
Потребность магазинов в этих грузах в таблице:
|
|
Магазин 1 |
Магазин 2 |
|
База 1 |
17 |
6 |
|
База 2 |
12 |
13 |
|
База 3 |
9 |
8 |
Определить, сколько груза надо перевезти с каждой базы в каждый магазин, чтобы
стоимость перевозки была минимальной. Примечание. Груз измеряется в штуках, следовательно, решение должно быть получено в целых числах. Для этого необходимо добавить ограничение на проектные параметры – установить, что они должны быть целыми числами (и, естественно, неотрицательными).
Выполнение: Создадим таблицу, как указано на рис. 11. Для ячеек введем следующие формулы:
рис. 11
Выполним команду Сервис/Поиск решения… Заполним вызванное окно в соответствии с
рис. 12
рис. 12
После заполнения окна Поиска решений… перейдём по кнопке Параметры в окно Параметры поиска решений (рис. 4) и установим там флажки напротив полей: Линейная модель и Неотрицательные значения. Далее выполним команду ОК, и нажмём клавишу Выполнить в окне Поиска решений. В результате выполнения задания появитлось следующее решение данной оптимизационной задачи рис. 13.
рис. 13
Задание 7.
Три автобазы А1, А2 и А3 предоставляют бульдозеры для работы на четырёх объектах О1, О2, О3 и О4. Число бульдозеров на автобазах представлено в таблице:
|
А1 |
А2 |
А3 |
|
11 |
11 |
8 |
Количество бульдозеров, необходимых на объектах – в таблице:
|
О1 |
О2 |
О3 |
О4 |
|
5 |
9 |
9 |
7 |
Время, затрачиваемое бульдозером на переезд с автобаз на объекты, приведено в таблице:
|
|
О1 |
О2 |
О3 |
О4 |
|
А1 |
7 |
8 |
5 |
3 |
|
А2 |
2 |
4 |
5 |
9 |
|
А3 |
6 |
3 |
1 |
2 |
Рассчитать, какое количество бульдозеров с каждой автобазы должно быть направлено на каждый объект, чтобы суммарное время, затрачиваемое на переезд, было минимальным. (Число бульдозеров – целое неотрицательное).
Выполнение: производиться аналогично заданию 6, найденное решение на рис. 14.
рис. 14
Задание 8.
Известно, что в разных странах цены трудовых ресурсов, сырья и т.п. могут существенно различаться. Фирме необходимо разместить четыре заказа. Любой из этих заказов может быть размещен в любой из четырёх стран, но только целиком (т.е. нельзя один заказ разместить в двух странах). Нельзя размещать два заказа в одной стране. Стоимости выполнения заказов в каждой из четырёх стран приведены в таблице:
|
|
Заказ 1 |
Заказ 2 |
Заказ 3 |
Заказ 4 |
|
Страна 1 |
1 |
4 |
6 |
3 |
|
Страна 2 |
9 |
10 |
7 |
9 |
|
Страна 3 |
4 |
5 |
11 |
7 |
|
Страна 4 |
8 |
7 |
8 |
5 |
Найти оптимальный план размещения заказов.
Выполнение: производиться аналогично заданию 6, найденное решение на рис. 15.
рис. 15
Выполнение дополнительного задания:
Обозначения:
n – Количество новых салфеток (ценой 25 центов);
k – Количество салфеток, отправленных в прачечную короля (2 дня стирки + цена 15 центов за штуку);
q – Количество салфеток, отправленных в прачечную королевы (3 дня стирки + цена 10 центов за штуку);
d – Количество грязных салфеток, не отправляемых в стирку.
Для решения данной задачи была создана таблица 1, где в ячейки G4:G10, J4:J10, K4:K10 введены формулы (ограничения), так же в ячейку L4 формула целевой функции:
=25*(B4+B5+B6+B7+B8+B9+B10)+15*(C4+C5+C6+C7+C8)+10*(D4+D5+D6+D7)
таблица 1
Заполнение диалогового окна поиск решения:
Решение задачи:
Вывод:
Научились находить решения оптимизационных задач средствами электронной таблицы EXCEL. Познакомились с функцией ПОИСК РЕШЕНИЯ.