х1*0,4+х2*0,4+х3*0,3<=600 ограничения на запасы сырья (сахарный
х1*0.1+х2*0,2+х3*0,2<=120 песок, патока, фруктовое пюре)
х1>=0; x2>=0; x3>=0;
x1, x2, x3- целые числа.
Для решения задачи в Excel запишем ее в виде, представленном на таблице 1.
Таблица 1 – Таблица для решения задачи
Кг.
ограничение
х1
0
800
>=
0
х2
0
600
>=
0
х3
0
120
>=
0
Mах прибыль:
0
В соответствии с условием прибыль должна быть максимальной, поэтому в таблице 1 добавлена строка «Mах прибыль». В ней буду суммировать прибыль от реализации продукции.
Вызываю Поиск решения из меню Сервис.
Определяю целевую ячейку – $D$8, устанавливаю переключатель в максимальное значение. Ввожу диапазон изменяемых ячеек ($B$11:$В$13) и вношу ограничения. Прежде всего, количество продукта не может быть отрицательным ($B$11:$В$13>=0), далее добавляю ограничения на запасы сырья, которое должно быть не более нормативного (800>=G$5; 600>=G$6; 120>=G$7). Нажимаю кнопку Выполнить.
В появившемся окне Результаты поиска решения нажимаю кнопку ОК и получаю решение задачи (приложение Д).Из полученных данных видно, что максимальная прибыль при производстве карамели составила 1296 рублей, причем такая прибыль будет получена при производстве 1200кг. Карамели вида А.
Для проверки правильности решения введем дополнительные ограничения.
В первом варианте я ввела ограничение на карамель вида В и получила результат приведенный в таблице 1.
Таблица 1
Вариант 1
Запасы сырья (кг.)
Ограничение
Х1
1170
800
>=
709,5
Х2
15
600
>=
474
Х3
0
120
>=
120
Целевая функция
1280,4
Дополнительное ограничение
Х2>=15
Из таблицы видно, что прибыль по сравнению с данными полученными в приложении Д уменьшилась на 15,6 рублей, при этом уменьшилось и производство карамели вида А на 30кг.
Во втором варианте я ввела ограничение на карамель вида С и получила следующий результат
Вариант 2
Запасы сырья (кг.)
Ограничение
Х1
1180
800
>=
714
Х2
0
600
>=
475
Х3
10
120
>=
120
Целевая функция
1287,2
Дополнительное ограничение
Х3>=10
Из полученных данных видно, что прибыль, так же как и в первом варианте, уменьшилась относительно данных из приложения Д на 8,8 рубля, а производство карамели вида А уменьшилось на 20кг.
По полученным данным можно сделать вывод, что исходное решение задачи было верным.
ЗАКЛЮЧЕНИЕ
В данной курсовой работе были решены оптимизационные задачи с использованием программных средств Microsoft Excel.
В процессе решения первой задачи были построены: график функции F(x) с учетом параметра А=0 в заданном диапазоне значений переменной Х, которые изменяются в диапазоне от 0 до 400. Были найдены интервалы значений переменной Х в пределах, которых функция принимает значение параметра А. При использовании метода половинного деления были найдены значения переменной Х, при которых функция принимает значение параметра А, в соответствии с заданной точностью, равной 0,001. Проверка правильности вычислений была осуществлена с помощью «Подбора параметра».
Решение второй задачи осуществлялось с помощью «Поиска решений» средствами Microsoft Excel. Была составлена целевая функция и ограничения (соответствующие условию задачи). В результате был выбран оптимальный вариант решения задачи. Для проверки этого варианта были внесены дополнительные ограничения, которые показали, что исходно оптимальный вариант решения был верен.
ПРИЛОЖЕНИЕ А
Начало
Описание F(х), А, ∆
Ввод а, b
с=(a+b)/2
F(a), F(b), F(c)
нет да
(F(a)-A)+(F(c)-A)<=0
a: = c b: = c
нет да
(b-a)<=∆
Выво с,F(c), F(c)-A
Конец
ПРИЛОЖЕНИЕ Б
ПРИЛОЖЕНИЕ В
ПРИЛОЖЕНИЕ Г
А В С D E
X
