Решение задачи в MS Excel. Отчет по лабораторной работе №1

Отчет по лабораторной работе №1

по дисциплине Информационные технологии и платформы разработки информационных систем

тема Изучение метода решения задач линейной оптимизации в Excel

Выполнил(а)
студент(ка) гр. 124-ИС ______________ Баранов А.А.

_{(подпись) (ФИО)}

Руководитель _______________ Суслова С. А.

_{(подпись) (ФИО)}

Оценка _______________

Дата 20.01.2015

Липецк 2015

Цель работы - изучение метода решения задач линейной оптимизации в Excel. Приобретение и развитие навыков использования команды Поиск решения.

Постановка задачи

Предприятие специализируется на производстве трех видов продукции А, В, С. Известный нормы расхода ресурсов R1,R2,R3 (сырья, электроэнергии, материалов) на каждый вид продукции и ограничения на ресурсы (в условных единицах). Кроме того, имеются ограничения на объемы выпуска продукции разных видов. Эти данные и данные о прибыли приведены в таблице 1.13. Кроме того имеется ограничение – объем выпуска продукции вида А не более 50.

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

таблице 1.13

Виды ресурсов	Нормы расходов ресурсов на продукцию вида	Ограничение на ресурсы
A	B	C
R1				<=400
R2				<=350
R3				<=450
Прибыль (млн.руб)				-

Математическая модель

Для составления модели обозначим количество продукции вида A, В, С соответственно через Х1, Х2, ХЗ.

Тогда ограничения по ресурсам можно записать в виде:

6*X1+4*X2+3*X3<=400 - сырье	(1)
5*X1+8*X2+4*X3<=350 - электроэнергия	(2)
3*X1+5*X2+4*X3<=450 - материалы	(3)
Граничные условия для ресурсов Х1, Х2, ХЗ 0>=X1<=50	(4)
X2>=0	(5)
X3>=0	(6)
Целевая функция Z=7*X1+5*X2+3*X3 =>max.	(7)

Решение задачи в MS Excel

3.1 Исходные данные для решения задачи представлены на рис. 1.

Рис.1. Исходная таблица

3.2. Для решения задачи введены следующие формулы:

· функция цели:

D8 =G4*A3+G5*B3+G6*C3;

· левые части ограничений:

A6=6*A3+4*B3+3*C3;

A7=5*A3+8*B3+3*C3;

A8=3*A3+5*B3+4*C3;

A9=AЗ;

A10=BЗ;

A11=C3;

· Массив правых частей ограничений (400, 350, 450) введён в диапазон B6:B11, условие не отрицательности переменных будет введено в окно “Добавление ограничений”.

3.3. Выбрал команду Сервис | Поиск решения. Откроется окно диалога “Поиск решения” (рис.2).

· В поле “Установить целевую ячейку” ввел абсолютную ссылку на ячейку с целевой функцией $D$8.

· В группе переключателей “Равной” выбрал тип взаимосвязи.

3.4 Окно поиска решений (рис.2).

Рисунок 2. Рабочий лист с решениями в окне Поиска решений

3.5 Рабочий лист с результатами решения задачи ЛО (рис.3).

Рисунок 3. рабочий лист с результатами решения задачи ЛО

4. Очёты

Рис.4. Отчёт Excel по результатам

Отчёт по результатам состоит из трёх таблиц (рис.4). В таблице “Целевая ячейка” приводятся сведения о целевой функции: ячейка, исходное значение и найденный оптимальный результат. В таблице “Изменяемые ячейки” приве­дены исходные и найденные значения искомых переменных. Таблица “ Огра­ничения” содержит результаты оптимального решения для ограничений. В столбце “Формула” приведена система ограничений решаемой задачи, в столбце ”Значение” приведены величины использованного ресурса, в столбце ”Разница” – количество неиспользованного ресурса. Если ресурс используется полностью, то в столбце “Статус” указывается “связанное”; если не полностью, то указывается ”не связан”.

4.1 На рисунке 5 показан отчёт по устойчивости

Рис.5. Отчёт Excel по устойчивости

Отчет по устойчивости состоит из двух таблиц (рис.5). В таблице “Изменяемые ячейки” приводятся результаты решения задачи; нормированные стоимости, т.е. дополнительные переменные, которые показывают, насколько изменяется целевая функция при принудительном включении единицы искомой переменной в оптимальном решении; коэффициенты целевой функции; предельные значения приращений коэффициентов целевой функции, при которых сохраняется набор переменных, входящих в оптимальное решение, в столбцах “Допустимое увеличение” и ”Допустимое уменьшение”.

В таблице “Ограничения” приводятся аналогичные данные для ограничений: величина использованных ресурсов; теневая цена, т.е. оценки, которые показывают, насколько изменится целевая функция при изменении ресурса на единицу; предельные значения приращения ресурсов, при которых сохраняется набор переменных, входящих в оптимальное решение.

4.2. На рисунке 6 предоставлен отчёт по пределам

Рисунок 6.Отчёт по пределам

Отчёт по пределам (рис.6) показывает, в каких пределах могут изменяться искомые переменные, вошедшие в оптимальное решение, при сохранении неизменной структуры оптимального решения. В этом отчёте приведены данные по целевой функции и по искомым переменным в оптимальном решении, значения целевой функции при значениях искомых переменных на нижнем пределе (столбце “Нижний предел” и “Целевой результат”) и при их значениях на верхнем пределе (столбце “Верхний предел” и “ Целевой результат").

Краткий анализ результатов и заключение (вывод).

В ходе решения линейной оптимизационной задачи получили следующие результаты:

Кол-во продукции вида A - 50 единиц;

Кол-во продукции вида B - не производилось;

Кол-во продукции вида C - 33 единицы.

Ограничения по ресурсам расходуются следующим образом:

Сырье расходуется полностью 400 (у.е) из 400 (у.е).

Электроэнергия расходуется полностью 350 (у.е) из 350 (у.е).

Материалы расходуются не полностью 283 (у.е) из 450 (у.е), 167 (у.е) не используются.

Все условия по ограничениям на ресурсы выполнены.

Максимальная прибыль составила 450 (у.е).

Дата добавления: 2015-09-06