Главная страница | Контакты | Случайная страница Автомобили Астрономия Биология География Дом и сад Другие языки Другое Информатика История Культура Литература Логика Математика Медицина Металлургия Механика Образование Охрана труда Педагогика Политика Право Психология Религия Риторика Социология Спорт Строительство Технология Туризм Физика Философия Финансы Химия Черчение Экология Экономика Электроника

Задача про використання сировини

Невелика фабрика виготовляє два види фарб: для зовнішніх (Е) і внутрішніх (I) робіт. Продукція обох видів надходить в оптовий продаж. Для виробництва фарб використаються два вихідних продукти А и В. Максимально можливі добові запаси цих продуктів становлять 6 і 8 тонн відповідно. Витрати продуктів А и В на 1 тонну відповідних фарб наведені в таблиці:

Вивчення ринку збуту показало, що добовий попит на фарбу I ніколи не перевищує величину попиту на фарбу Е більш ніж на 1 тонну. Крім того, встановлено, що попит на фарбу I ніколи не перевищує 2 тонн на добу. Оптові ціни однієї тонни фарби рівні: 3 тис. грн. для фарби Е, 2 тис. грн. для фарби I.

Яку кількість фарби кожного виду повинна робити фабрика, щоб доход від реалізації продукції був максимальним?

Рішення:

Нехай Х₁, Х₂ – планований добовий обсяг виробництва фарби Е и I відповідно (у тоннах). Тоді цільова функція математичної моделі буде виражати сумарний прибуток від реалізації фарби обох видів, а система обмежень - виробничі й маркетингові обмеження, що накладають на змінні моделі.

Таким чином, математична модель даної задачі буде мати вигляд:

Підготуємо аркуш EXCEL до використання процедури “Пошуку рішення”:

1) в комірках C2:D2 записуються найменування змінних моделі (у загальному випадку кількість комірок у даному діапазоні дорівнює кількості змінних у відповідній математичній моделі);

2) комірки C3:D3 резервуються для значень змінних моделі, які будуть знайдені після виконання процедури “Пошуку рішення”;

3) в комірках C4:D4 записують коефіцієнти при змінних моделі в цільовій функції моделі F(X₁, Х₂);

4) в комірки C6:D9 (число рядків діапазону дорівнює кількості обмежень у системі обмежень математичної моделі, число стовпців – числу змінних) заносимо матрицю коефіцієнтів при змінних X₁ і Х₂ у системі обмежень моделі;

5) в комірках G6:G9 записані праві частини системи обмежень моделі;

6) комірка Е4 (цільова комірка) резервується для обчислення оптимального значення цільової функції моделі.

Для розглянутого прикладу аркуш EXCEL буде мати вигляд (мал. 1):

Після занесення вихідних даних на аркуш EXCEL у цільовій комірці Е4 записують формулу: СУММПРОИЗВ($C$3:$D$3;C4:D4), яку потім копіюють із модифікацією в комірки Е6:Е9 (результат представлений на мал. 2):

Примітка: для виклику вбудованої функції СУММПРОИЗВ необхідно виконати послідовність дій:

1) встановити курсор у потрібну комірку (у нашому прикладі - в комірку Е4);

2) викликати “Майстер функцій” (кнопка f_x), далі “Математичні” і вибрати “СУММПРОИЗВ”;

3) в екранній формі, що з'явилася (див. мал. 3) установити курсор в “Масив 1” і виділити на аркуші EXCEL діапазон зарезервованих для значень змінних комірок, поставивши їм абсолютні адреси ($C$3:$D$3) натисканням функціональної клавіші F4; перевести курсор в “Масив 2” і виділити діапазон осередків, у яких записані коефіцієнти при змінних у цільовій функції (C4:D4), після чого нажати “ОК”.

Таким чином, після завершення всіх підготовчих операцій вибираємо в “Сервіс” процедуру “Пошук рішення” (див. мал. 4). У вікні процедури „Пошуку рішення”(див. рис 5) установлюємо цільову комірку - $Е$4, потім відзначаємо прапорцем тип оптимізації (виходячи з умов завдання) - максимізація; переводимо курсор в “Змінюючи комірки” і виділяємо на аркуші EXCEL діапазон зарезервованих для значень змінних комірок($C$3:$D$3); після чого, установивши курсор в “Обмеження”, натискаємо “Додати” і в екранній формі, що з'явилася (див. мал. 6) відзначаємо діапазон комірок:

a) “Посилання на комірки” - $Е$6:$Е$9 (тут записані результати підсумовування лівих частин нерівностей у системі обмежень);

b) знак вибирається відповідно до побудованої математичної моделі, причому, якщо не всі обмеження мають однакові знаки, то кожна група таких обмежень програмується окремо, для чого зручно обмеження з однаковими знаками розташовувати поруч один з одним;

c) “Обмеження:” - $G$6:$G$9 (тут записані праві частини нерівностей у системі обмежень моделі).

По натисканню “ОК” повертаємося до екранної форми “Пошук рішення”. Вибираємо кнопку “Параметри”, де відзначаємо прапорцем “Лінійна модель” і ”Невід’ємні значення” (див. мал. 7), потім по натисканню “ОК” повертаємося до екранної форми “Пошук рішення”.

Після вибору опції “Виконати” EXCEL проводить розрахунки й результати обчислень заносяться в комірки C3:D3 і Е4, які були зарезервовані для значень шуканих змінних і оптимального значення цільової функції.

Остаточно аркуш EXCEL буде мати вигляд (див. мал. 8):