Методичні вказівки з виконання завдання 1. Математична постановка. Позначимо xj – кількість страв j-го типу (j=1,2,3,4), що планується випускати

Математична постановка. Позначимо x_j – кількість страв j -го типу (j =1, 2, 3, 4), що планується випускати. При цьому будуть використані 1, 3x₁+1, 3x₂+1, 7x₃+0, 9х₄ одиниць першої сировини; 2, 1x₁+1, 1x₂+3x₃+1, 7х₄ одиниць другої сировини; 0, 9х₁+2х₂+1, 4х₃+2, 1х₄ одиниць третьої сировини. Виходячи із запасів сировини різного виду отримаємо систему обмежень:

(1)

Кількість страв не може бути від’ємною, тому

x₁≥ 0; x₂≥ 0; x₃≥ 0; x₄≥ 0. (2)

Вартість реалізації страви 1 складе 12, 5 x₁ грн, вартість реалізації страви 2 – 21, 1 x₂ грн., вартість реалізації страви 3 – 9, 56 x₃ грн., вартість реалізації страви 4 – 11, 34 х4. Тоді цільова функція буде мати вигляд:

(3)

Отже, математична модель задачі планування оптимального випуска страв: знайти такий план випуска страв, що задовольняє системі (1) та умові невід’ємності (2), при якому функція (3) приймає максимальне значення.

Реалізація задачі в середовищі Microsoft Excel. Побудуємо на листі Microsoft Excel початкову таблицю, як показано на рис. 11.

Рис. 11 – Зразок заповнення початкової таблиці

В клітинках G3: G6 сформуємо задані обмеження за допомогою математичної функції СУММПРОИЗВ, аргументами якої виступають масиви відповідних даних про кількість використаної сировини кожного виду.

В клітинці В8 побудуємо цільову функцію за допомогою СУММПРОИЗВ, аргументами якої виступають масиви відповідних даних про вартість реалізації.

Для вирішення поставленої задачі скористаємось надбудовою «Поиск решения». Для цього слід виконати команду Сервис - Поиск решения і заповнити діалогове вікно як показано на рис. 12 і натиснути на кнопку Выполнить.

Рис. 12 – Зразок заповнення діалогового вікна «Пошук рішення»

В результаті виконання визначеної кількості ітерацій отримуємо такі результати (рис. 13): для отримання максимальної вартості реалізації страв – 3188 грн. та використання сировини в рамках обмежень необхідно випускати 21 порцію страви 1, 79 порцій страви 2 і 111 порцій страви 4.

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

Завдання 2 (Транспортна задача)

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

Таблиця 2 – Дані для вирішення завдання 2

Математична постановка. Позначимо х_ij – кількість продукції, яку необхідно доставити і -му постачальнику j -ому ресторану. План перевезення задаеться матрицею .

Сумарна кількість продукції, яку можуть доставити постачальники, дорівнює

Сумарна кількість продукції, яка необхідна ресторану, дорівнює

Як бачимо, обидві суми співпадають, отже, задача закрита, тобто, попит ресторанів дорівнює можливостям постачальників:

(4)

(5)

Кількість продукції не може бути від‘ємною, тому

(i=1, 2, 3; j=1, 2, 3, 4) (6)

Сумарні витрати на перевезення продукції розраховуються таким чином:

(7)

Отже, математична модель задачі планування оптимального превезення: знайти такий план перевезення, що задовольняє системі (4), (5) та умові (6), при якому цільова функція (7) приймає мінімальне значення:

Реалізація задачі в середовищі Microsoft Excel. Побудуємо на листі Microsoft Excel початкову таблицю, як показано на рис. 14.

Рис. 14 – Зразок заповнення початкової таблиці

У клітинках G11: G13 та С14: F14 сформуємо задані обмеження за допомогою математичної функції СУММ, аргументами якої виступають масиви відповідних даних о кількості продукції, яку необхідно перевезти.

У клітинці В16 побудуємо цільову функцію за допомогою СУММПРОИЗВ, аргументами якої виступають масиви відповідних даних про вартість перевезення, що відповідають плану перевезення.

Для вирішення поставленої задачі скористуємося надбудовою «Поиск решения». Для цього слід виконати команду Сервис - Поиск решения і заповнити діалогове вікно як показано на рис. 15 та натиснути на кнопку Выполнить.

Рис. 15 – Зразок заповнення діалогового вікна «Пошук рішення»

В результаті виконання певної кількості ітерацій отримаємо такі результати (рис. 16): для отримання мінімальних витрат на перевезення – 760 грош. од. та забезпечити необхідною кількістю продукції ресторани, необхідно виконати план перевезення, що отриманий в діапазоні С11: F13.

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