Практична робота №4, Технологія розв’язання оптимізаційних задач

« Назад

Мета: Ознайомлення зі спеціальним інструментарієм (оптимізатором) електронної таблиці Excel для рішення оптимізаційних задач.

Програмне забезпечення: Табличний процесор MS Excel

Завдання: Використання табличного процесора MS Excel для пошуку оптимального рішення розв’язання оптимізаційних задач

Хід виконання роботи

І. Підготовчий етап

Підприємство випускає продукцію 4-х видів: А, В, С, D. Щомісяця воно може випускати не більш 1000 екземплярів (при цьому кожного типу - не менше 150). Протягом місяця 500 працівників працюють по 150 годин кожний. Підприємство може витратити за місяць не більш 1000 тон сировини.

Ціль полягає в тому, щоб зробити такий асортиментний набір видів продукції і таку кількість одиниць кожного виду, що принесуть підприємству максимальний прибуток. Одночасно необхідно дотримувати обмежень з боку ресурсів, тобто часу, потужності і сировини.

В основі розрахунку лежать нормативи, розроблені службою техніко-економічного планування виробництва. Ці нормативні дані зведені в таблицю:

Вид продукції	Витрати сировини, тон	Витрати часу,  год	Прибуток з одиниці продукції, грн
A	0.5	70	500
B	0.9	100	720
C	1.0	90	550
D	1.3	130	1000

Уточнимо набір наявних даних і їхній зміст у контексті оптимізаційної задачі:

кожен вид продукції - це перемінна (A, B, C, D);

ліміт по ресурсах - основне обмеження;

потужність підприємства і дилерське замовлення - це нижня і верхня границі виробництва продукції (додаткове обмеження);

максимум прибутку - це цільова функція;    

виробництво такого набору моделей і кількості виробів, що дасть вам максимальний прибуток - це рішення.

Обговоримо і злегка формалізуємо обмеження. Перше обмеження має вид: A+B+C+D=< 1000.

Однак відомо, що підприємство повинно випускати не менш 600 одиниць, тому друге обмеження має вид: A+B+C+D => 600.

Витрати сировини не можуть перевищувати 1000 тонн на місяць, тому третє обмеження умовне має вид:

(A сировина) + (B сировина) + (C сировина) + (D сировина) =< 1000.

Кількість годин, що щомісяця відпрацьовують усі працівники підприємства, не може перевищувати 75000, тобто четверте обмеження умовне має вид:

(A годин) + (B годин) + (C годин) + (D годин) =< 75000.

Таким чином, ми маємо систему лінійних нерівностей:

A+B+C+D=< 1000

0.5 A + 0.9 B + 1.0 C + 1.3 D =< 1000

70 A + 100 B + 90 C + 130 D =< 75000

A+B+C+D=>600

Додаткові обмеження на кожен вид продукції зв'язані з попитом, вираженим через сумарне дилерське замовлення: дилери замовляють не менш 150 і не більш 600 одиниць продукції кожного виду. (У реальних умовах замовлення на різні види продукції будуть різними. Нижня межа замовлення може бути зв'язаний із крапкою беззбитковості для дилерських пунктів, а верхній - з максимально можливим попитом.)

Порядок дій з електронною таблицею Excel на підготовчому етапі

Ввести дані про витрати ресурсів на одиницю потужності (блок В2..Е4), тобто на одиницю продукції, а також дані про прибуток з одиниці продукції (блок В5..Е5).

Ввести в окрему групу комірок (F2..F4)  константи обмежень, що будуть потрібні нам на етапі основного діалогу при формулюванні обмежень.

Ввести рядок комірок (В8..Е8) для майбутнього оптимального результату, - ці комірки є порожніми на початку рішення, але ми будемо посилатися на них, уводячи формули в комірки G2..G4 для підрахунку витрат ресурсів на програму виробництва, а також при уведенні формули цільової функції (прибуток):

в G2=B2*B8+C2*C8+D2*D8+E2*E8;

в G3=B3*B8+C3*C8+D3*D8+E3*E8;

в G4=B4*B8+C4*C8+D4*D8+E4*E8.

Ввести формулу для підрахунку прибутку в комірку G5, - повний вид цієї формули відображений у рядку формул (угорі таблиці) =B5*$B$8+C5*$C$8+D5*$D$8+E5*$E$8

Ввести верхні і нижні обмеження даних  в комірки В6..Е7.

Основний етап

Активізувати \Сервис, Поиск решения\ і в діалоговому вікні \Поиск решения\ для нашої задачі виконати дії:

визначите адресу цільової комірки G5;

активізуйте один з перемикачів бажаного екстремума (Мах);

задайте, які осередки програма повинна змінювати доти, поки не буде досягнутий максимальний прибуток, тобто В8:Е8;

для початку введення обмежень виберіть опцію \Добавить\. Відкриється нове вікно \Добавление ограничения\ для визначення трьох частин окремого обмеження. Послідовність уведення декількох обмежень несуттєва;

укажемо верхні границі перемінних $В$8:$Е$8 <= $В$7:$Е$7, і для введення нового обмеження виберемо кнопку \Добавить\, що очистить вікно для визначення наступного обмеження;

покажемо нижні границі перемінних $В$8:$Е$8 >= $В$6:$Е$6 і знову активізуємо кнопку \Добавить\ для визначення обмежень по ресурсах;

задамо обмеження по ресурсах $G$2:$G$4 <= $F$2:$F$4;

задати інші обмеження ($B$8:$E$8=целое);

після введення останнього обмеження активізуємо кнопку ОК, вона поверне нас у вікно \Поиск решения\;

після перевірки всіх установок у діалоговому вікні \Поиск решения\ активізуємо кнопку /Выполнить/.

Рис. 1 – Поиск решения в MS Excel

Заключний етап

Для одержання звіту за результатами, у вікні /Результати поиска решения/ визначите /Отчет по результатам/ чи інший звіт і активізуйте ОК. Звіт автоматично запишеться в новий лист робочої книги.

Рис. 2 - Отчет по результатам в MS Excel

Якщо необхідно зберегти опис моделі поточної задачі для наступного використання, то збережіть модель командою \Параметры поиска решения\Сохранить модель\. Збережена модель при необхідності викликається командою \Параметры поиска решения\Загрузить модель\.

Детальніше дивіться методичні рекомендації:

http://ua.kursoviks.com.ua/metodychni_vkazivky/article_post/355-praktichna-robota-4-tehnolog-ya-rozv-yazannya-optim-zac-ynih-zadach