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

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

Задача 2. Ваш друг хоче взяти кредит у банку терміном на 30 років під 10% річних на придбання житла. Але в нього немає можливості повертати більше ніж 200 у.е. на місяць. Який максимальний кредит він може взяти, щоб укластися в цю суму?

Розв’язання. Розв’язуємо спочатку «пряму» задачу за допомогою функції ППЛАТ, починаючи з деякої початкової суми кредиту, наприклад, 50000. З рис. 13 видно, що виплати по цьому кредиту (комірка В4) перевищать $200.

Рис. 13.

Активізуємо комірку В4 і виконуємо команду «Сервис ® Подбор параметра». У вікні цієї команди (рис. 14) в першому полі за замовчуванням буде зазначена цільова адреса B4, яку залишаємо без зміни. В полі «Значение» вказуємо цільове значення 200, а в полі «Изменяя значение ячейки», вказуємо адресу B1, оскільки саме в цій комірці розташований шуканий аргумент.

Рис. 14. Вікно підбору параметра

Після натискання на кнопку «ОК» з'явиться вікно «Результат подбора параметра» з протоколом розв’язання задачі, а в комірці В1 з'явиться відповідь, яка дорівнює 22790. Задача розв’язана.

При розв’язанні задач методом підбору потрібно мати на увазі наступні обставини. Підбір параметра в Excel здійснюється методом послідовних наближень (ітерацій). В комірку з аргументом по певному алгоритму підставляються значення доти, поки не буде знайдене розв’язання. Розглянута нами задача про кредит розв’язується дуже швидко, але для розв’язання інших задач може знадобитися набагато більше часу, а деякі задачі цим методом взагалі можуть не розв’язуватися. Якщо розв’язання затягується, то можна клацнути по кнопці «Пауза» у вікні «Результат подбора параметра» і подивитися проміжні результати, які вдалося знайти до цього моменту. Після цього можна продовжити автоматичний пошук чи відмовитися від пошуку розв’язання.

При розв’язанні задач цим методом варто звертати увагу на точність одержуваних результатів і на вибір початкових наближень, які можуть вплинути на результат, якщо в задачі є кілька розв’язків. Наприклад, спробуємо добути квадратний корінь і запишемо в комірку А1 формулу =А2^2, а потім виконаємо команду «Подбор параметра» для пошуку значення в комірці А2, при якому вміст комірки А1 дорівнює 4. В результаті отримаємо «неточне» значення кореня, що дорівнює 2,000023. Якщо як початкове наближення взяти -1, то отримаємо в результаті –1,99992.

Дата добавления: 2015-05-24