Рішення задач оптимального управління в excel
Розділ 4. Рішення задач оптимального управління в Excel
Excel дозволяє вирішувати завдання оптимального управління, загальний вигляд яких представлений формулами 1-5.
де xj може приймати або дійсні, або цілочисельні, або булеві значення.
Формула 1 визначає цільову функцію, формула 2 визначає обмеження, а формула 3 визначає граничні умови. Залежно від виду функції f у формулі 1 задача може ставитися або до класу задач лінійного або нелінійного програмування. Залежно від того, чи є xj цілими і булеві або дійсними величинами завдання може ставитися до завдань цілочисельного програмування чи ні.
Для вирішення таких завдань в Excel передбачена надбудова «Пошук рішення», яку можна викликати з меню «Сервис». Якщо пункту меню «Пошук рішення» немає, то потрібно повернутися до встановлення Excel і встановити цю надбудову.
Після вибору пункту меню з'явиться діалог надбудови «Пошук рішення». Детальну довідку з цього діалогу можна отримати в довідковій системі Excel за ключовими словами «надбудова пошуку рішення, переривання» (см.ріс.2). З'явиться наступне вікно довідки (див. Рис. 3).
Рис.2. Пошук інформації в Excel за ключовими словами
Постановка завдання та оптимізація моделі за допомогою процедури пошуку рішення
1 У меню Сервіс виберіть команду Пошук рішення. Якщо команда Пошук рішення відсутня в меню Сервіс, встановіть відповідну надбудову. інструкції
3 Щоб максимізувати значення цільової осередки шляхом зміни значень впливають осередків, встановіть перемикач в положення максимального значення. Щоб мінімізувати значення цільової осередки шляхом зміни значень впливають осередків, встановіть перемикач в положення мінімального значення. Щоб встановити значення в цільовій комірці рівним деякому числу шляхом зміни значень впливають осередків, виберіть пункт значенням і введіть у відповідне поле необхідне число.
5 У полі Обмеження введіть всі обмеження, що накладаються на пошук рішення.
6 Натисніть кнопку Виконати.
7 Щоб зберегти знайдене рішення, встановіть перемикач в діалоговому вікні Результати пошуку рішення в положення Зберегти знайдене рішення. Щоб відновити вихідні дані, виберіть пункт Відновити вихідні значення.
· Щоб перервати пошук рішення, натисніть клавішу ESC. Microsoft Excel перерахує лист з урахуванням знайдених значень впливають осередків.
· Щоб отримати докладніші відомості про елементи управління діалогового вікна Пошук рішення, натисніть кнопку.
· Щоб отримати докладніші відомості про елементи управління діалогового вікна Результати пошуку рішення, натисніть кнопку.
· Щоб отримати докладніші відомості про методи пошуку, натисніть кнопку.
Ріс.3.Окно довідки Excel
Представлена завдання відноситься до класу задач лінійного програмування. В цьому випадку цільова функція і обмеження приймають вид, показаний у формулі (6).
У нашій задачі цільовою функцією є максимум прибутку, обсяг виробництва кожного виду продукції - xj, вагові коефіцієнти кожного виду продукції в прибутку - cj ,. питомі витрати по кожному виду ресурсів для виробництва кожного виду продукції - aij. наявність ресурсів -bi. Граничні умовах а ия трансформуються в вимога xj> 0. так як випустити негативне кількість продукції неможливо.
При вирішенні даного завдання доцільно до наявної таблиці (див. Завдання) додати чотири осередки, в яких записати формули для обчислення значень, показаних у формулі (6) і чотири осередки для значень обсягу випуску кожного виду продукції. Обчислення сум зручно проводити за спеціальною формулою Excel «СУММПРОИЗВ». Після цього можна вибрати пункт меню «Пошук рішення».
Для завдання обмежень потрібно натиснути на кнопку «Додати» у вікні діалогу «Пошук рішення» і в діалозі «Додавання обмежень» ввести обмеження за ресурсами. Далі, потрібно натиснути на кнопку параметри і діалоговому вікні (див. Рис. 3) відзначити потрібні опції, обов'язково вибравши пункт «позитивне значення».
Після чого потрібно натиснути на кнопку «Виконати» і з'явиться діалогове вікно «Результати пошуку рішення» (див. Рис. 4).
Для побудови звіту по різним значенням наявності фінансів необхідно зберегти сценарії вирішення, натиснувши на кнопку «Зберегти сценарій».
Побудова звіту за сценаріями
Для отримання звіту за отриманими сценаріями потрібно вибрати пункт «Сценарії» в меню «Сервис». З'явиться «Диспетчер сценаріїв» (див. Рис. 5).
Рис.5. Діалогове вікно «Диспетчер сценаріїв»
Натиснувши на кнопку «Звіт» та вибравши в подальшому діалозі «Звіт за сценарієм» пункт «структура, отримаємо таблицю результатів експериментів.
У версії Excel 97 може виконувати функції кнопки «Зберегти сценарій» в надбудові «Пошук рішення» через що дані про результати експериментів можуть бути виведені в повному обсязі.