Тема:Пошукоптимальнихрозв'язків засобами Excel

Завдання

1) Використовуючи команди головного меню Сервис – Поискрешения, розв'язати задачу лінійного програмування.

Потрібно визначити, в якій кількості потрібно випускати продукцію чотирьох типів Продукція1, Продукція2, Продукція3, Продукція4, для виготовлення якої потрібні ресурси трьох видів: трудові, сировинні, фінансові. Кількість ресурсу кожного виду, яка є необхідною для випуску одиниці продукції даного типу, називається нормою витрат. Норми витрат, а також прибуток, який одержується при реалізації одиниці кожного типу продукції, наведені в таблиці. У цій же таблиці наведена кількість наявних ресурсів.

 

Ресурс Продук­ція1 Продук­ція2 Продук­ція3 Продук­ція4 Знак Наяв–ність
Прибуток max
Трудові <=
Сировинні <=
Фінансові <=

Складемо математичну модель, для цього введемо наступні позначення:

– кількість продукції j-го типу, яка випускається ( ).

Тодіматематична модель буде мативигляд:

 

 

Створиморобочу книгу Excel, в якій запишемо умови одержаної задачі лінійного програмування.При цьому дані до комірок вводяться за наступними формулами:

 

 

Комірка Формула у комірці
F6 =СУММПРОИЗВ($B$3:$E$3;B6:E6)
F9 =СУММПРОИЗВ($B$3:$E$3;B9:E9)
F10 =СУММПРОИЗВ($B$3:$E$3;B10:E10)
F11 =СУММПРОИЗВ($B$3:$E$3;B11:E11)

Викликаємо команди головного меню Сервис–Поискрешения.

 

Заповнюємо поля введення в діалоговомувікніПоискрешения:

Назва поля Значення
Установить целевую $F$6
Изменяя ячейки $B$3:$E$3
Ограничения $B$3>=$B$4 $C$3>=$C$4 $D$3>=$D$4 $E$3>=$E$4 $F$10<=$H$10 $F$11<=$H$11 $F$9<=$H$9

 

Післяцьогонатискаємо на кнопку Параметры і встановлюємо відповідніперемикачі в діалоговому вікні Параметры поиска решения.

 

Натискаємо на кнопки ОК і Найти решение. На екрані з'являється діалогове вікно Результаты поискарешения. При необхідностіможназберегтизнайденийрозв'язок в активному аркуші, відновити початкові значення або побудувати звіт за результатами розв'язання.

 

 

ВстановимоперемикачСохранить найденное решение і натиснемо на кнопку ОК. В активномуаркушіробочої книги з'являться результати роботи програми

 

 

Отже, знайденооптимальнийрозв'язок (Продукція1 = 0, Продукція2 = 0, Продукція3 = 11,25, Продукція4 = 0) . При цьому максимальний прибуток буде складати 1575, а кількість використаних ресурсів дорівнює: трудових – 11,25, сировинних – 45, фінансових – 190.

У загальному випадку розв'язок знаходиться не завжди. Якщо умови задачі несумісні, на екрані з'являється діалогове вікно Результаты поискарешенияз повідомленням Поиск не может найти подходящегорешения. Якщо цільова функція необмежена, то повідомлення буде мати вигляд Значенияцелевойячейки не сходятся.

Лабораторна робота №2