Главная | Обратная связь

Засоби «Пошук рішення» та «Підбір параметра»

Підбір параметра.

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

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

Розглянемо процедуру пошуку параметра на простому прикладі: розв’яжемо рівняння

10 * x - 10 / x = 15. Тут параметр (аргумент) - x. Хай це буде комірка A3. Введемо в цю комірку будь-яке число, що лежить в області визначення функції (в нашому прикладі це число не може бути рівний нулю). Це значення буде використано як початкове. Хай це буде 3. Введемо формулу =10*A3-10/A3, по якій повинне бути отримано необхідне значення, в яку-небудь комірку, наприклад, B3. Тепер можна запустити функцію пошуку параметра, вибравши команду Goal Seek (Підбір параметра) в меню Tools (Сервіс). Введіть параметри пошуку:

ü В полі Set сеll (Встановити в комірці) введіть необхідну формулу, що містить посилання на комірку.

ü Введіть шуканий результат в полі To value (Значення). У нашому випадку 15.

ü В полі changing сеll (Змінюючи значення комірки) введіть посилання на комірку, що буде містити підбиране значення.

ü Кликніть на клавіші OK.

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

Достатньо складно правильно визначити найбільш відповідне початкове значення. Частіше ми можемо зробити які-небудь припущення про шуканий параметр, наприклад, параметр повинен бути цілим (тоді ми отримаємо перше рішення нашого рівняння) або непозитивним (друге рішення).

Задачу пошуку параметра за граничних умов, що накладаються, допоможе вирішити спеціальна надбудова Microsoft Excel Solver (Пошук рішення).

 

Пошук рішення.

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

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

Функції Пошук рішень передбачає можливість обмеження кількості ітерація та часу проведення розв'язку, а також відносну похибку для визначення точності виконуваних обмежень, допустиме відхилення. також можна передбачити можливість показу результатів ітерацій.

Надбудова Microsoft Excel Solver (Пошук рішення) не встановлюється автоматично при звичайній установці:

ü В меню Tools (Сервіс) виберіть команду Add-Ins (Надбудови). Якщо діалогове вікно Add-Ins (Надбудови) не містить команди Solver (Пошук рішення), натискуйте кнопку Browse (Огляд) і вкажіть диск і теку, в якій міститься файл надбудови Solver.xla (як правило, це директорій Library\Solver) або запустите програму установки Microsoft Office, якщо знайти файл не вдається.

ü В діалоговому вікні Add-Ins (Надбудови) встановите прапорець Solver (Пошук рішення).

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

В діалоговому вікні Solver (Пошук рішення) так само, як і в діалоговому вікні Goal Seek (Підбір параметра), необхідно вказати цільову комірку, її значення і комірки, які слід змінювати для досягнення мети. Для вирішення задач оптимізації цільову комірку слід вказати рівній максимальному або мінімальному значенню.

Якщо Ви клацните на кнопці Guess (Припустити), Excel сам спробує знайти всі комірки, що впливають на формулу.

Ви можете додати граничні умови, кликнувши на клавіші Add (Додати).

Натиснувши кнопку Options (Параметри), можна змінити умови пошуку рішення: максимальний час пошуку рішення, кількість ітерацій, точність рішення, допуск на відхилення від оптимального рішення, метод екстраполяції (лінійна або квадратична), алгоритм оптимізації і т.д.

Повернемося до попереднього прикладу: для того, щоб отримати друге (непозитивне) рішення, достатньо додати граничну умову A3<=-0.01. Так само як і при підборі параметра, на екрані з'явиться вікно, в якому буде відображений звіт про результати пошуку необхідного рішення. Саме рішення буде показано в призначених для нього комірках (в комірці A3 відобразиться значення -0.50).

 

Надбудова Microsoft Excel Solver (Пошук рішення) дозволяє, також, вирішувати системи рівнянь або нерівностей. Розглянемо простий приклад: спробуємо вирішити систему рівнянь

x + у = 2

x - у = 0

Введемо в комірки, призначені для вирішення (A1:A2) довільні величини, що лежать в області визначення (початкові значення).

ü В комірки B1 і B2 внесемо формули, по яких повинні обчислюватися праві частини рівнянь (= A1 + A2 і = A1 - A2).

ü Запустимо Solver (Пошук рішення) з меню Tools (Сервіс).

ü Виберемо одну з комірок, що містять формули, як цільову комірку (наприклад, B1), зробимо її рівною 2.

ü Кликнемо на кнопці Guess (Припустити) для того, щоб Excel визначив впливаючі комірки (A1:A2).

ü Додамо обмеження B2 = 0.

ü Кликнемо на клавіші Solve (Виконати).

Результати пошуку відобразяться в призначених для розв’язку комірках (A1:A2), звіт про результати з'явиться на екрані.

 

 

!!!! Доступ до даних засобів у версії 2007 відбувається через кнопки закладинки Данные. Якщо інструмент Пошук ріщення не відображається, виконайте команди Системна кнопка Ms Office - Параметры Excel – Надстройки – відмітити прапорцем надбудову Поиск решений.