Пошук рішення – ЧАСТИНА 1

Пошук рішення – надбудова, що входить в комплект поставки Excel Її основним призначенням є вирішення лінійних і нелінійних задач оптимізації Для лінійних задач використовується симплекс-метод, для задач цілочисельного програмування – метод гілок, меж і для нелінійних задач – метод наведеного градієнта

Надбудова Пошук рішення є частиною блоку завдань, який іноді називають аналізом «що-якщо»

Якщо надбудова Пошук рішення підключена, на вкладці Дані зявляється група

Аналіз і в ній кнопка Пошук рішення (рис 249)

Рис 249 Застосування надбудови Пошук рішення

За допомогою цієї надбудови можна знайти максимальне або мінімальне значення для формули, що міститься в одній комірці, званої цільової При пошуку рішення робота проводиться з групою комірок, прямо або побічно повязаних з формулою в цільовій комірці Для отримання заданого результату за формулою в цільовій комірці надбудова Пошук рішення змінює значення в призначених осередках, званихзмінюваними осередками Для зменшення кількості використовуваних значень застосовуються обмеження, Які можуть посилатися на інші осередки, що впливають на формулу для цільової комірки

При пошуку рішення за допомогою цієї надбудови необхідно спочатку ввести в змінювані комірки деякі значення вихідних даних, а в цільову комірку – формулу для обчислень результату Потім натисканням кнопки Пошук рішення відкривається діалогове вікно, в якому проводиться налаштування параметрів задачі Приклад завдання таких параметрів показаний на рис 250

Після того як виконані всі необхідні налаштування параметрів, натисканням кнопки Виконати у вікні Пошук рішення запускається процес вирішення завдання Під час

обчислень в рядку стану відображається число виконаних ітерацій Залежно від різних умов процес обчислень може тривати досить довго

Рис 250 Вікно Пошук рішення

По закінченні розрахунків відкривається діалогове вікно Результати пошуку рішення, в якому повідомляється, чи було знайдено рішення

Якщо знайдено оптимальне рішення, то в діалоговому вікні Результати пошуку рішення виводяться два ключових повідомлення: Рішення знайдено і Всі обмеження і умови оптимальності виконані Відсутність хоча б одного з цих повідомлень свідок ствует про те, що програмі не вдалося знайти оптимальне рішення Причини невдачі можуть бути різні, необхідно перевірити встановлені обмеження, початкові значення змінюваних клітинок і постановку задачі в цілому Якщо рішення не було знайдено, повідомляються інші причини закінчення обчислень

У вікні Результати пошуку рішення можна прийняти знайдений варіант або відновити колишній, а також зберегти сценарій розрахунків з певним імям У цьому вікні є список Тип звіту Якщо виділити один або кілька варіантів, то при закритті вікна створюються додаткові аркуші робочої книги (Звіт за результатами 1, Звіт по межах 1, Звіт по стійкості 1) з запитаної додатковою інформацією При повторному запуску можуть створюватися додаткові аркуші з новими номерами звітів, таким чином, інформація про різних варіантах розрахунків не буде загублена

Для роботи з збереженими сценаріями можна використовувати Диспетчер сценаріїв, що викликається натисканням кнопки Аналіз «що-якщо» і вибором інструменту Диспетчер сценаріїв .. в групі Робота з даними вкладки Дані Тут можна додати, змінити, видалити сценарії, а також обєднати їх і вивести звіт на додатковий лист У цьому вікні наводиться список існуючих в даній книзі сценаріїв, для запуску одного з яких потрібно вибрати конкретне імя сценарію і натиснути кнопку Вивести При цьому відбувається перерахунок результатів, і в цільовій комірці зявляється нове значення

За допомогою надбудови Пошук рішення можна вирішувати такі завдання

✓&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Пошук оптимуму за наявності обмежень (умовна оптимізація) – найбільш

загальний тип задачі оптимізації

У цьому випадку задані обмеження і адреса комірки цільової функції, яку необхідно максимізувати або мінімізувати Якщо цільова функція і всі обмеження лінійні, то має місце задача лінійної оптимізації У цьому випадку в діалоговому вікні Параметри пошуку рішення можна встановити прапорець Лінійна модель При цьому рішення задачі буде знайдено швидше, надійніше, і буде видана більш детальна додаткова інформація В іншому випадку надбудова Пошук рішення використовує метод наведеного градієнта Якщо цільова функція має декілька оптимумів, удовлетво ряющіх обмеженням, то надбудова Пошук рішення знайде один з них (тобто локальний оптимум) Знайдений оптимум може не бути глобальним (тобто найкращим з усіх оптимумів) За наявності декількох локальних оптимумів на результат пошуку рішення може вплинути зміна початкових значень змінюваних осередків

✓&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Пошук безумовного оптимуму – задача знаходження максимуму або мінімуму

цільової функції при відсутності обмежень

Подібна постановка задачі має сенс тільки в тому випадку, якщо цільова функція є нелінійною При пошуку оптимуму лінійної цільової функції без завдання обмежень буде виводитися повідомлення про необмежене рішенні Як і в попередньому випадку, якщо цільова функція має декілька максимумів або мінімумів, то надбудова Пошук рішення знаходить один з них (локальний оптимум), який може не збігатися з глобальним оптимумом Початкові значення змінюваних клітинок можуть істотно впливати на пошук оптимуму

✓&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Пошук допустимого рішення

Якщо не задана цільова комірка (полі Встановити цільову комірку в діалоговому вікні Пошук рішення), то надбудова Пошук рішення припинить роботу, знайшовши допустиме рішення задачі, тобто набір значень для змінюваних осередків, які задовольняють всім обмеженням У разі лінійності функцій обмежень можна встановити прапорець Лінійна модель в діалоговому вікні Параметри пошуку рішення Дана настройка параметрів задачі може прискорити пошук допустимого рішення

✓&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Рішення систем рівнянь

У цьому випадку цільова осередок не запитує, обмеження вказуються тільки у вигляді рівності При вирішенні завдання виконується пошук значення змінюється екпортувати осередків, що задовольняють заданій системі обмежень Таким чином, вирішується система рівнянь, в якій невідомими є значення змінюваних клітинок

✓&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Підбір параметрів

Для вирішення такого завдання необхідно задати конкретне значення для цільової комірки, не визначаючи будь-які обмеження При вирішенні завдання може проводитися підбір декількох параметрів, доставляють задане значення цільової функції

Приклад підбору параметрів, в якості одного з варіантів застосування надстрой ки Пошук рішення, наводиться далі

На рис 251 показано вікно Пошук рішення з налаштуваннями для підбору параметрів

Рис 251 Завдання значення цільової комірки для підбору параметрів

На рис 252 показаний результат підбору параметрів

Рис 252 Результати підбору параметрів

Далі наводиться більш докладний опис параметрів надбудови Пошук рішення, які задаються в окремих діалогових вікнах

Діалогове вікно Пошук рішення містить елементи керування, що дозволяють налаштувати основні параметри розвязуваної задачі

Для установки цільової комірки в полі Встановити цільову комірку вводиться адреса комірки, що містить значення цільової функції Адреса комірки можна ввести вручну або клацнувши мишею на потрібній комірці (у цьому випадку адреса вводиться автоматично)

В області Рівної діалогового вікна Пошук рішення задається тип оптимізації Можна вказати, чи досягти максимального або мінімального значення цільової комірки чи деякого конкретного значення, яке вводиться у відповідному полі даного діалогового вікна

У полі Змінюючи осередки необхідно вказати комірки, в яких містяться змінні значення завдання

Список Обмеження містить перелік всіх обмежень, встановлених для даної задачі При натисканні на кнопку Додати відкривається діалогове вікно Додавання обмеження За умовчанням передбачається, що обмеження має вигляд нерівності <=. Замість даної нерівності зі списку можуть бути обрані інші варіанти, а також обмеження виду цілий і двоич, які вказують, що значення змінюваних клітинок можуть приймати тільки цілі значення або тільки двійкові значення (0 і 1).

У Excel 2007 максимальна кількість змінюваних осередків, з якими одновремен але може працювати надбудова Пошук рішення, дорівнює 200 При вирішенні нелінійних задач на кожну з змінюються комірок можна накласти до 100 обмежень (на додаток до цілочисловим обмеженням на змінні)

Джерело: Трусов А Ф Excel 2007 для менеджерів та економістів: логістичні, виробничі та оптимізаційні розрахунки (+ CD) – СПб: Питер, 2009 – 256 с: Ил

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*