Реферат: Засоби «Пошук розв’язку» та «Підбір параметра» Пошук параметра

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

Пошук параметра.

Спеціальна функція 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 – Надстройки – відмітити прапорцем надбудову Поиск решений.