Решение задачи линейного программирования с помощью Excel — КиберПедия 

Архитектура электронного правительства: Единая архитектура – это методологический подход при создании системы управления государства, который строится...

Биохимия спиртового брожения: Основу технологии получения пива составляет спиртовое брожение, - при котором сахар превращается...

Решение задачи линейного программирования с помощью Excel

2018-01-07 206
Решение задачи линейного программирования с помощью Excel 0.00 из 5.00 0 оценок
Заказать работу

 

Рассмотрим, решение предыдущей задачи, с помощью программы 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] Алексеев Е.Р., Павловская Е.В., Чеснокова О.В., Ломовцева О., Решение задач оптимизации и линейного программирования с помощью современных программных средств.

 


Поделиться с друзьями:

Механическое удерживание земляных масс: Механическое удерживание земляных масс на склоне обеспечивают контрфорсными сооружениями различных конструкций...

Архитектура электронного правительства: Единая архитектура – это методологический подход при создании системы управления государства, который строится...

Своеобразие русской архитектуры: Основной материал – дерево – быстрота постройки, но недолговечность и необходимость деления...

Таксономические единицы (категории) растений: Каждая система классификации состоит из определённых соподчиненных друг другу...



© cyberpedia.su 2017-2024 - Не является автором материалов. Исключительное право сохранено за автором текста.
Если вы не хотите, чтобы данный материал был у нас на сайте, перейдите по ссылке: Нарушение авторских прав. Мы поможем в написании вашей работы!

0.021 с.