Рішення нелінійних рівнянь
Розглянемо, як на робочому аркуші за допомогою підбору параметра можна знаходити корені рівняння з одним аргументом. В якості базового прикладу розглянемо наступне рівняння:
x 3 - 0.01х 2 - 0.7044х + 0.139104 = 0.
Так як ми шукаємо корені полінома третього ступеня, то є не більше трьох речових коренів.
Для знаходження коренів їх спочатку треба локалізувати, т. Е. Знайти інтервали, на яких ці корені існують. Такими інтервалами локалізації коренів можуть служити проміжки, на кінцях яких функція має протилежний знак. З метою знаходження інтервалів, на кінцях яких функція змінює знак, необхідно побудувати її графік або її протабулювати. Наприклад, протабуліруем наш поліном на інтервалі [-1; 1] з кроком 0,2. З цією метою:
1. Введіть в клітинку A2 значення -1, а в клітинку A3- значення -0,8.
2. Виберіть діапазон A2: А3, розташуйте курсор миші на маркері заповнення цього діапазону і простягніть його на діапазон A4: А12. Аргумент протабулювати.
3. У осередок B2 введіть формулу:
= A2 ^ 3-0,01 * A2 ^ 2-0,7044 * A2 + 0,139104
4. Виберіть осередок B2. Розмістіть вказівник на маркері заповнення цього осередку і простягніть його на діапазон B3: B12. Функція також протабулювати.
На рис. 1 видно, що поліном змінює знак на інтервалах [-1; -0.8], [0.2; 0.4] і [0.6; 0.8], і тому на кожному з цих інтервалів є своє коріння. Так як поліном третього ступеня має не більше трьох коренів, то вони все локалізовані.
Перш ніж приступити до знаходження коренів за допомогою підбору параметра, необхідно виконати деяку підготовчу роботу:
- Встановіть точність, з якою знаходиться корінь. Корінь за допомогою підбору параметра знаходиться методом послідовних наближень. Для цього виберіть команду Сервіс → Параметри і на вкладці Обчислення діалогового вікна Параметри задайте відносну похибку і граничне число ітерацій рівними 0,00001 і 1000, відповідно
- Відведіть на робочому аркуші клітинку під шуканий корінь, наприклад, C2. Цей осередок грати подвійну роль. До застосування підбору параметра в ній знаходиться початкове наближення до кореня рівняння, а після застосування - знайдене наближене значення кореня.
Мал. 1. Локалізація коренів полінома і діалогове вікно Підбір параметра
Аналогічно треба вчинити з двома іншими шуканими корінням:
- Відвести осередок C3 під другий корінь, ввести в неї початкове наближення 0,3. а в клітинку D3 ввести наступну формулу
= C3 ^ 3-0,01 * C3 ^ 2-0,7044 * C3 + 0,139104 - Відвести осередок C4 під другий корінь, ввести в неї початкове наближення 0,7, а в клітинку D4 ввести наступну формулу
= C4 ^ 3-0,01 * C4 ^ 2 0,7044 * C4 + 0,139104
Тепер можна переходити до знаходження першого кореня рівняння:
1. Виберіть команду Сервіс → Підбір параметра. На екрані відобразиться діалогове вікно Підбір параметра.
3. У полі Значення введіть 0. Тут вказується значення з правої частини рівняння.
5. Натисніть кнопку ОК.
Мал. 2. Всі на корені рівняння і діалогове вікно Результат підбору параметра після успішного завершення пошуку третього кореня.
Примітка. Вводити посилання на комірки в поля діалогового вікна Підбір параметра зручніше не з клавіатури, а вибором відповідного осередку на робочому аркуші. При цьому MS Excel автоматично буде перетворювати їх в абсолютні посилання - в нашому випадку $ D $ 2 і $ C $ 2.