Задача 1.2
Цех хлібозаводу по виробництву борошна уклав контракт з міні-пекарнею про поставку щодня 300 кг житнього та пшеничного борошна, причому пшеничного - не менше 50%. Зерно, яке надходить у цех, проходить в ньому обмолот, помел та пакування борошна. Трудовитрати (в людино-годинах) на вказані операції представлені в таблиці:
Операція
Житня мука
Пшеничне борошно
Наявний ресурс
Обмолот
0,1
0,1
30
Помел
0,1
0,08
27
Упаковка
0,05
0,05
200
Собівартість одного кілограма житнього борошна складає 14 рублів, а пшеничного - 18 рублів.
Потрібно знайти оптимальний план виробництва продукції, що дозволяє цеху виконати умови контракту з найменшими витратами.
Рішення.
Нехай x 1 - обсяг виробництва житнього борошна, x 2 - обсяг виробництва пшеничного борошна. Тоді задача може бути описана у вигляді наступної моделі лінійного програмування:
Заповнимо дані в середовищі Microsoft Excel
Додамо в таблицю ще один рядок (Наприклад, рядок 9 План), в яку вноситимуться одержувані в ході вирішення результати. Обсяг виробництва житнього борошна будемо припускати отримати в комірці В9, пшеничного борошна - в комірці С9, а витрати на собівартість - в комірці D7.
Для вирішення завдання необхідно також виділити стовпець (в даному прикладі - стовпець D), в який будуть введені формули для розрахунку значень лівої частини обмежень. Формула вводиться також і в клітинку, в якій буде розраховано витрати на собівартість підприємства (комірка D7). Потрібно звернути увагу на те, що в формулах використовуються не числа або символьні позначення, а координати осередків таблиці, в яких зберігаються ці числа і змінні.
D3 = СУММПРОИЗВ (B3: C3; $ B: $ C)
D4 = СУММПРОИЗВ (B4: C4; $ B: $ C)
D5 = СУММПРОИЗВ (B5: C5; $ B: $ C)
D7 = СУММПРОИЗВ (B7: C7; $ B: $ C)
D9 = B9 + C9
Для запуску процедури оптимізації в меню Сервіс слід вибрати пункт Пошук рішення. У полі Встановити цільову осередок вводяться координати комірки (D7), яку необхідно мінімізувати.
У полі Змінюючи осередки вводяться координати осередків (розділені комами або об'єднані в інтервал двокрапкою), в яких містяться значення змінних - обсягів виробництва житнього та пшеничного борошна. Змінні осередки повинні бути прямо або побічно пов'язані з цільової осередком. У полі Обмеження необхідно ввести всі граничні умови завдання:
При цьому обмеження на позитивність змінних можна ввести шляхом установки прапорця ненегативне значення у вікні Параметри. У разі, якщо розв'язувана задача є задачею лінійного програмування, краще відзначити це у вікні Параметри відповідним прапорцем:
Запуск процесу оптимізації виробляється натисканням кнопки Виконати. По закінченні рахунку з'являється діалогове вікно Результати пошуку рішення. Натиснувши в ньому відповідну кнопку, можна зберегти знайдене рішення або відновити вихідні значення. Зміни при цьому торкнуться як цільової комірки, так і впливають осередків. У цьому ж діалоговому вікні можна вказати необхідні типи звітів, які будуть розміщені на окремих аркушах книги.
Звіт Результати відображає вихідні та результуючі значення цільової і впливають осередків, а також відомості про накладені обмеженнях.
Таким чином підприємству необхідно випустити 150 кг житнього борошна і 150 кг пшеничного борошна, тоді мінімальна собівартість випуску складе 4800 людино-годин, при цьому ресурси на обмолот і помел буде використано повністю, а ресурси на упаковку залишаться в надлишку в кількості 185 людино-годин.
Задача 2.2
Керівництво птахофабрики має можливість закуповувати корми трьох видів. З цих кормів птиці повинні отримувати поживні речовини (П1, П2, П3 і П4), необхідну кількість яких і вміст в кормах (в од.) наведені в таблиці:
Живильні
речовини
Корми
Норми споживання
К1
К2
К3
П1
3
5
0
дорівнює 16
П2
2
2
4
не менше 24
П3
8
1
2
не менше 25
П4
4
3
5
не менше 33, але не більше 40
Ціни за 1 т кормів складають відповідно 1000, 900 і 800 рублів.
Які корми і в якій кількості слід закуповувати, щоб витрати птахофабрики виявилися мінімальними?
Рішення.
Рішення: позначимо за x 1 кількість корму К1, за x 2 - кількість корму К2, за x 3 - кількість корму К3. Тоді:
Підготуємо в Microsoft Excel таблицю для вирішення завдання:
Оскільки Microsoft Excel дозволяє шляхом встановлення відповідного прапорця автоматично визначати позитивність змінних, додавання обмежень на позитивність у модель при вирішенні задачі в Microsoft Excel не є обов'язковим.
Введемо в таблицю формули для розрахунку лівих частин обмежень і цільової комірки:
Е4 = СУММПРОИЗВ (B4: D4; $ B: $ D)
Е5 = СУММПРОИЗВ (B5: D5; $ B: $ D)
Е6 = СУММПРОИЗВ (B6: D6; $ B: $ D)
Е7 = СУММПРОИЗВ (B7: D7; $ B: $ D)
Е8 = СУММПРОИЗВ (B8: D8; $ B: $ D)
Цільова осередок Е10 = СУММПРОИЗВ (B10: D10; $ B: $ D)
Обмеження запишуться так
Запустимо модуль Пошук рішення і зробимо розрахунки
В ході розрахунків отримаємо наступні результати: оптимальний раціон повинен містити 3,125 од. корму К1, 4,6875 од. корму К2 і 0,1875 корму К3. При цьому вартість раціону становитиме 7493,75 руб.
Задача 3.1
Один з цехів фабрики з пошиття виробів зі шкіри розкроює надходять заготовки для отримання 5 видів деталей одним з трьох можливих способів. З однієї заготовки отримують:
Спосіб розкрою
Деталі
A
B
C
D
E
I
10
5
|
