Решение задачи линейного программирования с помощью Excel
Рассмотрим, решение предыдущей задачи, с помощью программы Excel.Ввод условия задачи.
Сделаем форму и введем исходные данные:
Введём зависимости
Чтобы получить значение целевой функции в ячейке F5, воспользуемся функцией СУУММПРОИЗВ. Для этого поместим курсор в ячейку F5 и с помощью команды МАСТЕР ФУНКЦИЙ вызовем математическую функцию СУММПРОИЗВ. На экране появится ее диалоговое окно. В массив 1 ввести строку со значениями переменных, т.е. $B$3:$E$3. В массив 2 ввести адрес строки коэффициентов целевой функции, т.е. B5:E5. Копируем формулу из ячейки E5 в ячейки E7, E8, E9,E10.
Решение задачи осуществляем с помощью средства Поиск решения из меню Сервис:
Вводим ограничения. Далее командой Параметры вызываем диалоговое окно Параметры и устанавливаем флажок Линейная модель. Возвращаемся в диалоговое окно Поиск решения и, щелкнув по кнопке Выполнить, находим оптимальное решение задачи. В диалоговом окне Результаты поиска решения выбираем Тип отчета Устойчивость.
Представим план реализации товаров в виде диаграммы.
Получим отчет по устойчивости:
| Результ. | Нормир. | Целевой | Допустимое | Допустимое | ||
| Ячейка | Имя | значение | стоимость | Коэффициент | Увеличение | Уменьшение |
| $B$3 | Значение x1 | 2,666666667 | ||||
| $C$3 | Значение x2 | -3,3 | 3,3 | 1E+30 | ||
| $D$3 | Значение х3 | 1E+30 | 0,8 | |||
| $E$3 | Значение х4 | -3,033333333 | 3,033333333 | 1E+30 | ||
| Результ. | Теневая | Ограничение | Допустимое | Допустимое | ||
| Ячейка | Имя | значение | Цена | Правая часть | Увеличение | Уменьшение |
| $F$7 | По 1 ресурсу ЦФ | 101,6666667 | 1E+30 | 148,3333333 | ||
| $F$8 | По 2 ресурсу ЦФ | 0,1 | 6,376811594 | |||
| $F$9 | По 3 ресурсу ЦФ | 0,266666667 | 6,875 | |||
| $F$10 | По 4 ресурсу ЦФ | 76,33333333 | 1E+30 | 3,666666667 |
Используя отчёт по устойчивости, мы можем найти решение двойственной задачи. Теневая цена в отчете по устойчивости – это оценка соответствующего ресурса, а нормированная стоимость – это оценка вида продукции. Сравнивая результаты отчета по устойчивости и результаты, полученные нами в пунктах 2.1, 2.3 и 2.5, можем сказать, что данные расчетов совпадают. Значит, задача решена верно.
3.2. Отчет по результатам:
| Ячейка | Имя | Исходное значение | Результат |
| $F$5 | Коэф. Целевой функции ЦФ | 30,66666667 | 30,66666667 |
| Ячейка | Имя | Исходное значение | Результат |
| $B$3 | Значение x1 | 2,666666667 | 2,666666667 |
| $C$3 | Значение x2 | ||
| $D$3 | Значение х3 | ||
| $E$3 | Значение х4 |
| Ячейка | Имя | Значение | Формула | Статус | Разница |
| $F$7 | По 1 ресурсу ЦФ | 101,6666667 | $F$7<=$G$7 | не связан. | 148,3333333 |
| $F$8 | По 2 ресурсу ЦФ | $F$8<=$G$8 | связанное | ||
| $F$9 | По 3 ресурсу ЦФ | $F$9<=$G$9 | связанное | ||
| $F$10 | По 4 ресурсу ЦФ | 76,33333333 | $F$10<=$G$10 | не связан. | 3,666666667 |
| $B$3 | Значение x1 | 2,666666667 | $B$3>=0 | не связан. | 2,666666667 |
| $C$3 | Значение x2 | $C$3>=0 | связанное | ||
| $D$3 | Значение х3 | $D$3>=0 | не связан. | ||
| $E$3 | Значение х4 | $E$3>=0 | связанное |
Отчет по пределам:
| Целевое | ||||||
| Ячейка | Имя | Значение | ||||
| $F$5 | Коэф. Целевой функции ЦФ | 30,66666667 | ||||
| Изменяемое | ||||||
| Ячейка | Имя | Значение | ||||
| $B$3 | Значение x1 | 2,666666667 | ||||
| $C$3 | Значение x2 | |||||
| $D$3 | Значение х3 | |||||
| $E$3 | Значение х4 | |||||
| Нижний | Целевой | Верхний | Целевой | |||
| предел | результат | предел | результат | |||
| 2,666666667 | 30,66666667 | |||||
| 30,66666667 | 30,66666667 | |||||
| 10,66666667 | 30,66666667 | |||||
| 30,66666667 | 30,66666667 | |||||
3.3.Оценим влияние изменений ресурса 1 на -20 единиц, 2 — на 40 единиц, 3 — на -10 единиц, 4 — на 30 единиц.
Представим оптимальные планы реализации товарных групп на диаграмме:
Как видно из диаграммы планы производства при первом и четвертом изменении одинаковы, следовательно данные изменения не повлияли на оптимальный план производства.
Представим значения целевых функций данных планов:
Из графика видно, что первое и четвертое изменения ресурсов не повлияли на величину целевой функции, второе изменение привело к возрастанию целевой функции, третье изменение – к снижению целевой функции.
3.4.Решим исходную задачу при условии, что решение должно быть целочисленным. Для этого в меню «поиск решения» добавляем целочисленное ограничение, для искомых переменных. Получаем целочисленное решение:
3.5.
Оптимальное значение целевой функции при целочисленном решении будет 28 ден.ед., что на 2,667 ден. ед. меньше, чем при нецелочисленном решении.
Заключение
По произведенным нами расчетам можем сказать следующее. Согласно оптимальному плану рассматриваемой нами задачи следует производить 2,667 ед. изделий А; 0 ед. изделий Б; 5 ед. изделий В; 0 ед. изделий С. При этом предприятие получит максимальную прибыль в размере 30,667 ден. ед. Останутся неиспользованными 148,3 ед. ресурса P1 (сталь, кг.), и 3,667 ед. ресурса Р4 (фрезерные станки, станко-час), а ресурсы P2 и Р3 будут израсходованы полностью.
Основываясь на решении двойственной задачи, можем отметить, оценки ресурсов Р2 и Р3являются положительными, следовательно эти виды сырья используется постоянно и является дефицитным. Наиболее дефицитным ресурсом будет ресурс Р3, так он имеет наибольшую оценку. Избыточным ресурсами является ресурсы Р1и Р4, так как их оценки равны нулю.
Величина двойственной оценки численно равна изменению целевой функции при изменении соответствующего ресурса на одну единицу. При увеличении ресурса Р2 на одну весовую единицу значение целевой функции оптимального плана увеличится на 0,1 ден. ед. При увеличении ресурса Р3 на одну весовую единицу значение целевой функции оптимального плана увеличится на 0,2667 ден. ед. Оценки ресурсов Р1 и Р4 равны нулю, следовательно данные ресурсы не является дефицитным, при их увеличении значение целевой функции не изменится. Оценка изделий Б и С больше нуля, следовательно производство данных изделий не будет рентабельным и при производстве одной единицы изделия Б значение целевой функции оптимального плана уменьшится на 3,3 ден. ед., а при производстве одной единицы изделия С значение целевой функции оптимального плана уменьшится на 3,033 ден. ед.
Реализовывать новую товарную группу, на единицу которой ресурсы Р1, Р2 и Р3, Р4 расходуются в количествах 14; 20; 25; 90 единиц, а цена реализации составляет 10 ден. ед. не целесообразно, так как затраты на её реализацию не превышают прибыль при её продаже.
Увеличение прибыли при закупке 1 единицы ресурса Р3 меньше цены данного ресурса, следовательно не имеет смысла закупать данный ресурс.
Изменение второго вида ресурса не находится в пределах устойчивости оценок, следовательно мы не можем оценить влияние изменения на целевую функцию. Изменения ресурсов 1, 3, 4 находятся в пределах устойчивости оценок, то их раздельное влияние на величину прибыли, Dfimax определяется произведением оценки yi и величины изменения Dbi.
Df1max0ден. ед.; Df3max= -2,667 ден. ед.; Df4max= 0 ден. ед.
Суммарное влияние Dfmax=Df1max+Df3max +Df4max= 0 – 2,667 + 0= -2,667 ден. ед.
При введении в производство нового вида продукции прибыль не превышает затраты, следовательно нет смысла внедрять новый вид продукции.
СПИСОК ИСПОЛЬЗОВАННОЙ ЛИТЕРАТУРЫ
[1] Taha, Н. Operationsresearch
[2] Орлов А.И., Основы теории принятия решений. Учебное пособие. Москва, 2002.
[3] Новикова, Н.А. Исследование Операций, Минск.2010
[4] Лемешко Б.Ю., Теория игр и исследование операций. Новосибирский государственный технический университет, 2013
[5] Алексеев Е.Р., Павловская Е.В., Чеснокова О.В., Ломовцева О., Решение задач оптимизации и линейного программирования с помощью современных программных средств.