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

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

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

Під час підбору параметра потрібно визначити три складові:

1) місце розміщення цільової комірки (вміст якої обов’язково має бути формулою);

2) значення, яке має бути досягнуте в цільовій комірці після зміни параметра;

3) комірку, вміст якої (параметр) зміниться для досягнення цільовою коміркою шуканого значення.

Всі три складові об’єднані в діалоговому вікні Подбор параметра, яке викликається з меню Сервис.

Розв’язання з допомогою підбора параметра є наближеними і лише в деяких випадках дають точні значення.

Підбір параметра — це ітераційний процес. Для комірки, яку задано в полі Изменяя значения ячейки, Excel по черзі перевіряє різні значення доти, поки не відшукає найбільш придатне. За замовчуванням програма робить до 100 ітерацій або продовжує обчислювати значення, доки не буде досягнена відносна похибка 0, 001.

Приклад 1

Дана функція y = 2x + 89. З допомогою підбору параметра знайти значення х, при яких y = 0; 0, 1; 101; 1001.

Послідовність дій.

1) Внести дані в таблицю.

2) В комірку В3 ввести формулу “=2*A3+89”.

3) Виконати команду Сервис/Подбор параметра. У діалоговому вікні Подбор параметра вказати наступні дані:

• Установить в ячейке - комірку В3;

• Значение – число 0 (виконуючи команду Сервис/Подбор параметра для пошуку інших значень змінної y, потрібно вводити значення 0, 1; 101; 1001);

• Изменяя значение ячейки – комірку А3.

В наступному діалоговому вікні буде показано результат обчислення, який автоматично заноситься в комірку А3.

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

Приклад 2

Дана функція y = 10 – x2. З допомогою підбору параметра знайти значення х, при яких y = 6.

Внести дані в таблицю.

В комірку В3 ввести формулу “=10-A3^2”.

Виконати команду Сервис/Подбор параметра. У діалоговому вікні Подбор параметра вказати необхідні дані.

Шуканому значенню y = 6 відповідають два значення параметра х

(х=2 і х= - 2). Вирішальну роль у тому, яке значення буде знайдено внаслідок підбору, відіграє початкове значення параметра, що вводиться в комірку В3.

При від’ємних початкових значеннях х буде знайдене значення х= -2, що дає у = 6. а при нульовому або додатних початкових значення х буде отримано х=2, що також дає у=6.

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

Приклад 3

Використовуючи засіб Подбор параметра, визначити, яку максимальну позику можна взяти на 10 років, щоб придбати квартиру при відсотковій ставці 7% і за умови щомісячної виплати не більш $400.

Послідовність дій

1) Внести потрібні дані в таблицю. Встановити табличний курсор у клітинці В5.

2) Вибрати команду Сервис/Подбор параметра.

3) В отриманому діалоговому вікні Подбор параметра задати посилання на відповідні комірки. У полі Установить в ячейке автоматично з'явиться посилання на комірку В5.

4) В поле Значение ввести бажане значення щомісячних виплат, а саме 400 (число вводиться зі знаком " мінус", оскільки гроші будуть виплачуватися).

5) В поле Изменяя значения ячейки ввести посилання на комірку В2.

6) Клацнути на кнопці ОК у вікні Подбор параметра, після чого з'явиться вікно Результат подбора параметра, де Excel повідомить, що розв’язок знайдено. В клітинці В5 відобразиться значення $400, а в клітинці В2 робочої таблиці – шукане значення позики.