2015 Великий
800 800 1300 300 600
–
–
1100
Середній
600 1000 1100 800 600 1200
–
1800
Дрібний
250 850 1150 400 1100
–
1100 1250 2020 Великий
800 800 1300 900 1050
–
–
1700
Середній
1000 1000 1500 800 1000 1650
–
3200
Дрібний
700 1200 1600 1300 1100
–
1600 2500
Приведені
інтегральні витрати, млн.грн.
450 510 836 531 630 302 288
Оптимізація плану полягає у виборі з відомих варіантів розвитку кожного підприємства таких, реалізація яких дозволяє забезпечити задану потребу в сортовому прокаті пороках планового періоду з мінімальними сукупними приведеними витратами. Для кожного підприємства може бути обрано не більше одного варіанта реконструкції і розвитку. Для розв’язку задачі позначимо змінну ij x , значення якої дорівнює
1, якщо те підприємство вибирає й варіант розвитку, і дорівнює 0 в протилежному випадку. Тоді умова вибору кожним підприємством тільки одного варіанту розвитку визначається наступним записом:
3
ij j 1
x
1,i
1,3




;



 

ij
1,
якщо і те підприємство вибирає j й варіант розвитку;
x
0,
в протилежному випадку.
Позначимо задану потребу в сортовому прокаті в динаміці як змінну k
d ,k
1,9

Індексація для цієї змінної для спрощення математичної постановки задачі вибрана від 1 до 9, тобто ми розглядаємо 9 заданих потреб в сортову прокаті незалежно одна від одної (3 сорти x 3 роки).
Тоді v ijk
– обсяг виробництва для го підприємства при вибраному j-му варіанту розвитку для k-ої потребив сортовому прокаті. Отже, умова забезпечення заданих потреб в сортовому прокаті набуває наступного вигляду:





3 3
ij ijk k
i 1 j 1
x v d ,k
1,9

Таким чином, задача полягає в визначенні таких значень невідомих ij x , що задовольняють систему обмежень і умову цілочисловості, при яких досягається мінімальне значення цільової функції (сукупні приведені витрати):











 
















3 3
ij ij i 1 j 1
ij
3
ij j 1 3
3
ij ijk k
i 1 j 1
F
c x min
1,
якщо і те підприємство вибирає j й варіант розвитку;
x
0,
в протилежному випадку.
x
1,i
1,3
x v d В даній постановці задачі є одна умовність, оскільки у нас немає змінних з індексами і
33
, тобто потрібно було використовувати різні
індекси для варіантів розвитку кожного підприємства, але цього не було зроблено, щоб не ускладнювати надмірно математичну модель задачу
2. Пошук оптимального плану розвитку підприємств. Введіть в комірки робочого листа вихідну інформацію задачі (рис. 24). Додамо до таблиці з умовами задачі рядок для вибраних варіантів розвитку для кожного підприємства. Цей рядок буде містити 1 для вибраних варіантів розвитку, 0 – в протилежному випадку. Позначимо з початку, що кожне підприємство вибрало для себе й варіант розвитку. В комірках стовпця "Объем производства" використано формули, що обчислюють обсяг виробництва для кожної заданої потребив сортовому прокаті, який буду забезпечено при певному виборі варіантів розвитку підприємствами. Для цього можна скористаємося наступною формулою =СУММПРОИЗВ(C4:I4;$C$14:$I$14) для комірки К. Завдяки використанню абсолютного посилання на блок комірок з вибраними варіантами розвитку цю формулу можна скопіювати на інші комірки стовпця К для розрахунку обсягу виробництва. Останній рядок в таблиці Контрольная сумма" призначений для реалізації лівої частини умови вибору кожним підприємством тільки одного варіанту розвитку системи обмежень задачі. Для цього достатньо розрахувати суму вибраних варіантів розвитку для кожного підприємства. Наприклад, в комірці E15 використано наступну формулу =C14+D14+E14.

Рис. 24. Таблиця для розрахунків цілочислової задачі Для цільової функції в комірці К, що мінімізує приведені загальні витрати на проведення реконструкції і розвитку трьох металургійних підприємств, використовуємо наступну формулу
=СУММПРОИЗВ(C13:I13;$C$14:$I$14). Аргументами цієї функції є масиви приведених затрат і вибраних варіантів розвитку. Цю формулу також можна скопіювати з комірки вище.
Виділимо комірку, у якій обчислюється цільова функція, і виберемо на стрічці інструментів Данные / Поиск решения. У діалоговому вікні в полі введення "Оптимизировать целевую функцию" вже міститься адреса комірки з цільовою функцією $K$13. Установимо перемикач: Минимум. Перейдемо до поля введення "Изменяя ячейки переменных" потрібно занести адрес блоку з вибраними варіантами розвитку –
$C$14:$I$14. Систему обмежень задачі формують наступні умови (рис. 25): обсяги виробництва задовольняють задані потреби у сортовому прокаті в динаміці порокам контрольні суми повинні дорівнювати 1 для кожного підприємства – $E$15=1, $G$15=1, $I$15=1; вибрані варіанти розвитку не можуть бути менше нуля – $C$14:$I$14>=0.

Рис. 25. Вікно Поиск решения з умовами задачі Для реалізації умови цілочисловості задачі в діалогову вікні Добавление ограничения" для останньої умови задачі указуємо, що
$C$14:$I$14
– цілі за допомогою елементу "цел" списку, що випадає для вибору операторів для порівняння. Таким чином, отримуємо наступне обмеження: целое (рис. 26). Рис. 26. Умова цілочислової задачі Задача оптимізації повністю підготована. Натискаємо кнопку "Найти решение" і отримуємо оптимальний план реконструкції і розвитку металургійних підприємств:

*
*
*
11 12 13
*
*
21 22
*
*
31 32
x
0; x
1; x
0;
x
1; x
0;
x
1; Таким чином, для го підприємства буде обраний й варіант розвитку, а для II і III – й варіант розвитку. Дане рішення забезпечує задану потребу в прокаті для кожного сорту прокату для трьох років при мінімальних сумарних витратах на реалізацію обраних варіантів розвитку.