Пошук рішення - надбудова, що входить в комплект поставки Excel. Її основним призначенням є рішення лінійних і нелінійних задач оптимізації. Для лінійних задач використовується симплекс-метод, для задач цілочисельного програмування - метод гілок, кордонів і для нелінійних задач - метод приведеного градієнта.
Надбудова Пошук рішення є частиною блоку завдань, який іноді називають аналізом «що-якщо».
Якщо надбудова Пошук рішення підключена, на вкладці Дані з'являється група
Аналіз і в ній кнопка Пошук рішення (рис. 2.49).
Мал. 2.49. Застосування надбудови Пошук рішення
За допомогою цієї надбудови можна знайти максимальне або мінімальне значення для формули, що міститься в одній клітинці, званої цільової. При пошуку рішення робота проводиться з групою осередків, прямо або побічно пов'язаних з формулою в цільовій комірці. Для отримання заданого результату за формулою в цільовій комірці надбудова Пошук рішення змінює значення в призначених осередках, званих змінними комірками. Для зменшення кількості використовуваних значень застосовуються обмеження, які можуть посилатися на інші осередки, що впливають на формулу для цільової осередки.
При пошуку рішення за допомогою цієї надбудови необхідно спочатку ввести в змінювані осередки деякі значення вихідних даних, а в цільову комірку - формулу для обчислень результату. Потім натисканням кнопки Пошук рішення відкривається діалогове вікно, в якому проводиться настройка параметрів завдання. Приклад завдання таких параметрів показаний на рис. 2.50.
Після того як виконані всі необхідні налаштування параметрів, натисканням кнопки Виконати у вікні Пошук рішення запускається процес вирішення завдання. Під час
обчислень в рядку стану відображається число виконаних ітерацій. Залежно від різних умов процес обчислень може тривати досить довго.
Мал. 2.50. Вікно Пошук рішення
Після закінчення розрахунків відкривається діалогове вікно Результати пошуку рішення, в якому повідомляється, чи було знайдено рішення.
Якщо знайдено оптимальне рішення, то в діалоговому вікні Результати пошуку рішення виводяться два ключових повідомлення: Рішення знайдено і Всі обмеження і умови оптимальності виконані. Відсутність хоча б одного з цих повідомлень свідок ствует про те, що програмі не вдалося знайти оптимальне рішення. Причини невдачі можуть бути різні, необхідно перевірити встановлені обмеження, початкові значення змінних осередків і постановку задачі в цілому. Якщо рішення не було знайдено, повідомляються інші причини закінчення обчислень.
У вікні Результати пошуку рішення можна прийняти знайдений варіант або відновити колишній, а також зберегти сценарій розрахунків з певним ім'ям. У цьому вікні є список Тип звіту. Якщо виділити один або кілька варіантів, то при закритті вікна створюються додаткові аркуші робочої книги (Звіт за результатами 1, Звіт по межах 1, Звіт по стійкості 1) з запитаної додатковою інформацією. При повторному запуску можуть створюватися додаткові аркуші з новими номерами звітів, таким чином, інформація про різні варіанти розрахунків не буде загублена.
Для роботи з збереженими сценаріями можна використовувати Диспетчер сценаріїв, що викликається натисканням кнопки Аналіз «що-якщо» і вирішили купити продукт Диспетчер сценаріїв ... в групі Робота з даними вкладки Дані. Тут можна додати, змінити, видалити сценарії, а також об'єднати їх і вивести звіт на додатковий лист. У цьому вікні наводиться список існуючих в даній книзі сценаріїв, для запуску одного з яких потрібно вибрати конкретне ім'я сценарію і натиснути кнопку Вивести. При цьому відбувається перерахунок результатів, і в цільовій комірці з'являється нове значення.
За допомогою надбудови Пошук рішення можна вирішувати такі завдання.
✓ Пошук оптимуму при наявності обмежень (умовна оптимізація) - найбільш
загальний тип завдання оптимізації.
В цьому випадку задані обмеження і адреса комірки цільової функції, яку необхідно максимізувати або мінімізувати. Якщо цільова функція і всі обмеження лінійні, то має місце задача лінійної оптимізації. В цьому випадку в діалоговому вікні Параметри пошуку рішення можна встановити прапорець Лінійна модель. При цьому рішення задачі буде знайдено швидше, надійніше, і буде видана більш детальна додаткова інформація. В іншому випадку надбудова Пошук рішення використовує метод приведеного градієнта. Якщо цільова функція має кілька оптимумів, удовлетво ряющий обмеженням, то надбудова Пошук рішення знайде один з них (тобто локальний оптимум). Знайдений оптимум може не бути глобальним (тобто найкращим з усіх оптимумів). При наявності декількох локальних оптимумів на результат пошуку рішення може вплинути зміна початкових значень змінних осередків.
✓ Пошук безумовного оптимуму - завдання знаходження максимуму або мінімуму
цільової функції при відсутності обмежень.
Подібна постановка задачі має сенс тільки в тому випадку, якщо цільова функція є нелінійною. При пошуку оптимуму лінійної цільової функції без завдання обмежень буде виводитися повідомлення про необмежене вирішенні. Як і в попередньому випадку, якщо цільова функція має декілька максимумів або мінімумів, то надбудова Пошук рішення знаходить один з них (локальний оптимум), який може не збігатися з глобальним оптимумом. Початкові значення змінних осередків можуть істотно впливати на пошук оптимуму.
✓ Пошук допустимого рішення.
Якщо не задана цільова осередок (поле Встановити цільову осередок в діалоговому вікні Пошук рішення), то надбудова Пошук рішення припинить роботу, знайшовши допустиме рішення задачі, тобто набір значень для змінних осередків, які задовольняють всім обмеженням. У разі лінійності функцій обмежень можна встановити прапорець Лінійна модель в діалоговому вікні Параметри пошуку рішення. Дана настройка параметрів завдання може прискорити пошук допустимого рішення.
✓ Рішення систем рівнянь.
В цьому випадку цільова осередок не ставить, обмеження вказуються тільки у вигляді рівності. При вирішенні завдання виконується пошук значень змінюється мих осередків, які відповідають заданій системі обмежень. Таким чином, вирішується система рівнянь, в якій невідомими є значення змінних осередків.
✓ Підбір параметрів.
Для вирішення такого завдання необхідно поставити конкретне значення для цільової осередки, не визначаючи будь-які обмеження. При вирішенні завдання може проводитися підбір декількох параметрів, що доставляють задане значення цільової функції.
Приклад підбору параметрів, в якості одного з варіантів застосування надстрой ки Пошук рішення, наводиться далі.
На рис. 2.51 показано вікно Пошук рішення з настройками для підбору параметрів.
Мал. 2.51. Завдання значення цільової осередки для підбору параметрів
На рис. 2.52 показаний результат підбору параметрів.
Мал. 2.52. Результати підбору параметрів
Далі наводиться більш докладний опис параметрів надбудови Пошук рішення, які задаються в окремих діалогових вікнах.
Діалогове вікно Пошук рішення
Діалогове вікно Пошук рішення містить елементи управління, що дозволяють налаштувати основні параметри розв'язуваної задачі.
Для установки цільової осередки в поле Встановити цільову осередок вводиться адреса комірки, що містить значення цільової функції. Адреса комірки можна ввести вручну або клацнувши мишею на потрібній комірці (в цьому випадку адреса вводиться автоматично).
В області Рівної діалогового вікна Пошук рішення задається тип оптимізації. Можна вказати, чи потрібно досягти максимального або мінімального значення цільової осередки або деякого конкретного значення, яке вводиться у відповідному полі даного діалогового вікна.
В поле Змінюючи осередки необхідно вказати осередки, в яких містяться змінні значення завдання.
Список Обмеження містить перелік всіх обмежень, встановлених для даного завдання. При натисканні на кнопку Додати відкривається діалогове вікно Додавання обмеження. За умовчанням передбачається, що обмеження має вид нерівності <=. Замість цього нерівності зі списку можуть бути обрані інші варіанти, а також обмеження виду цілий і двоіче, які вказують, що значення змінних осередків можуть приймати тільки цілі значення або тільки двійкові значення (0 і 1).
В Excel 2007 максимальну кількість змінюваних осередків, з якими одновремен але може працювати надбудова Пошук рішення, дорівнює 200. При вирішенні нелінійних задач на кожну з змінюються осередків можна накласти до 100 обмежень (на додаток до цілочисельним обмеженням на змінні).
Джерело: Трусов А. Ф. Excel 2007 для менеджерів та економістів: логістичні, виробничі та оптимізаційні розрахунки (+ CD). - СПб .: Пітер, 2009. - 256 с .: іл.
на Ваш сайт.
