СКЛАДАННЯ ОПТИМАЛЬНОГО ПЛАНУ ВИПУСКА ПРОДУКЦІЇ ЗА ДОПОМОГОЮ MS EXCEL

Heобходімо скласти оптимальний план випуску продукції різного виду, при якому забезпечується максимальний прибуток (Р) підприємства.

Вартість одиниці продукції і витрати ресурсів наведені в таблиці 1.

Таблиця 1. Вартість одиниці продукції і витрати ресурсів

Алгоритм складання оптимального  плану випуску продукції

На основі даних в таблиці для застосування програми «Пошук рішення» потрібно побудувати модель аналізованого процесу у вигляді таблиці з формулами, які відображають задані обмеження і цільову функцію (рис.1).

Рисунок.1. Таблиця з даними для застосування програми «Пошук рішення»

В даному випадку цільовою функцією є прибуток Р, значення якого залежить від кількості виробленої продукції. Комірка ВЗ області цільової функції є цільовою. У ній відображається значення показника, який змінюється. Цільова комірка може зберігати формулу (рис. 2) і результат, що змінюється в залежності від вмісту комірок В11:B13 області змінних: 

=СУММПРОИЗB($B$11:$B$13;$E$11:$E$13).

Рисунок 2. Формула для комірки В3 

В комірці В18, В19 області обмежень вводяться формули (рис 3; рис..4) для обчислення значень витрат на електроенергію і метал, а в комірці С18, С19-задані обмеження цих значень:

B18:= СУММПРОИЗB($B$11:$B$13;$C$11:$C$13)

B19:= СУММПРОИЗB($B$11:$B$13;$D$11:$D$13)

Рисунок 3. Формула для комірки В18

Рисунок 4. Формула для комірки В19

Для запуску програми «Пошук рішення» виконується команда Сервіс-Пошук рішення, яка відкриває однойменне діалогове вікно (рис. 5).

Рисунок 5. Діалогове вікно «Пошук Рішення»

В полі «Встановити цільову комірку» слід вказати адресу комірки, значення якої буде використовуватися як критерій оптимізації – В3. За допомогою перемикачів вікна виробляється вибір значення критерію оптимальності, яке може дорівнювати максимальному, мінімальному або заданому значенню.

В полі «Змінюючи комірки» вказується діапазон, що містить комірки, значення яких програма повинна змінити для отримання оптимального значення (діапазон В11:B13 області змінних). Після активізації цього поля за допомогою миші слід виділити ці осередки або з клавіатури ввести адресу діапазону. При натисканні кнопки «Припустити» програма виділяє осередки, на які прямо або опосередковано посилається цільова комірка.

Для того щоб задати обмеження (як прямі, так і функціональні), слідкує натиснути кнопку «Додати». У діалоговому вікні (рис. 6) в полі «Посилання на комірку» вказується адреса комірки або діапазону комірок, вміст яких має відповідати  одному з заданих типів обмежень.

Рисунок 6. Додавання обмежень

Поле «Обмеження» цього вікна призначене для введення значення обмеження у вигляді константи або у вигляді адреси комірки, що містить це значення. Кнопка «Додати» використовується для переходу до введення наступного обмеження. В даному випадку до обмеження додаються значення комірок В11:B13, які повинні бути цілими і перевищувати одиницю, а також області обмежень значень витрат на електроенергію і метал не повинні перевищувати заданих лімітів. 

Запуск програми на виконання обчислень здійснюється натисканням кнопки «Виконати». Якщо оптимальне рішення буде знайдено, отримані значення будуть вставлені в таблицю, і на екрані з'явиться вікно «Результати пошуку рішення» з інформацією про закінчення цього процесу (рис. 7).

Рисунок 7. Результати пошуку рішення

Якщо рішення знайдено (рис.8), то користувач повинен вибрати один з режимів (збереження отриманого результату або відновлення вихідних значень).

Рисунок 8. Результати