Завдання № 3
Ціль: Вивчити застосування команди Пошук рішення, для завдань оптимізації.
Відкрийте наступний Аркуш і перейменуйте його в Пошук рішення.
Вирішимо наступне завдання. Автомобільний завод провадить автомобілі трьох моделей (Модель1, Модель2, Модель3). Видаток комплектуючих запасних частин до моделей задається таблицею 1 (реальна таблиця значно більше):
У стовпці А даний денний запас комплектуючих. У стовпцях З, D, E зазначена кількість комплектуючих використовуваних для кожної моделі автомобіля. Необхідно визначити оптимальне співвідношення між виробництвом автомобілів різних моделей і денним запасом комплектуючих, при повнім їхньому використанні. Для рішення завдання введіть дані таблиці 1.
Таблиця 1 - Рішення завдань оптимізації
В осередки C2, D2, E2 занесіть нулі - надалі значення цих осередків будуть підібрані автоматично, у цих осередках буде відбита оптимальна кількість випущених моделей автомобілів.
В осередках В3:В14 потрібно вказати формули для розрахунку видатку комплектуючих запасних частин по моделях. Для цього в осередок В3 уведіть формулу =$C$2*C3+$D$2*D3+$E$2*E3, а інші формули можна одержати методом автозаполнения.
В осередок F2 занесіть формулу, що обчислює загальне число зроблених автомобілів. Для цього встановите курсор в осередок F2 і клацніть на кнопці Автосумма (на вкладці Головна, група Редагування) і натисніть клавішу Enter.
Установите надбудову Офіс
,
Параметри Excel, Надбудови,натисніть
кнопку Перейти…і
встановите прапорець біля Пошук
рішення й
натисніть
ОК. Виконаєте команду Пошук рішення (вкладка Дані, група Аналіз) - відкриється діалогове вікно Пошук рішення.
У поле Встановити цільовий осередок укажіть осередок, що містить оптимизируемое значення (F2). Установите перемикач Рівної максимальному значенню, тому що потрібен максимальний обсяг виробництва автомобілів.
У поле Змінюючи осередку задайте діапазон З2:Е2, для параметрів, що підбираються.
Щоб установити набір обмежень клацніть на кнопці Додати. У діалоговому вікні, що відкрилося, Додавання обмеження в поле Посилання на осередок укажіть діапазон В3:В14. Як умова задайте <=. У поле Обмеження задайте діапазон А3:А14. Ця умова вказує, що денний видаток комплектуюч не повинен перевершувати запасів. Клацніть на кнопці ОК.
Знову клацніть на кнопці Додати, у діалоговому вікні Пошук рішення. У поле Посилання на осередок укажіть діапазон З2:Е2. Як умова задайте >=. У поле Обмеження задайте число 0. Ця умова вказує, що число вироблених моделей ненегативно. Клацніть на кнопці ОК.
Знову клацніть на кнопці Додати, у діалоговому вікні Пошук рішення. У поле Посилання на осередок укажіть діапазон З2:Е2. Як умова виберіть пункт цілий. Це умова не дозволяє провадити частки моделей. Клацніть на кнопці ОК.
Клацніть на кнопці Виконати. По завершенні оптимізації відкриється діалогове вікно Результати пошуку рішення.
Установите перемикач Зберегти знайдене рішення, потім клацніть на кнопці ОК.
Перевірте оптимальність рішення, експериментуючи зі значеннями осередків З2:Е2.
Збережете завдання й доповісте викладачеві.