Підбір параметра (рішення рівняння)

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

Пошук рішення здійснюється в Excel методом ітерацій (від лат. Iteratio - повторення, послідовне наближення). Порядок роботи при цьому наступний:

1) в змінною осередку задати початкове (наближене) значення шуканого параметра;

2) в цільовій комірці ввести формулу для розрахунку результату;

3) виконати команду меню Сервіс → Підбір параметра. ввести у вікні необхідні дані і натиснути OK.

При цьому якщо початкове значення шуканого параметра не дає необхідного результату в цільовій комірці, то Excel автоматично робить наступний крок ітераційного процесу і знаходить нове значення параметра в змінною осередку. Цей процес триває до тих пір, поки не буде знайдено потрібне значення (за умови, що рішення задачі існує). За замовчуванням команда Подборпараметра припиняє обчислення після виконання 100 ітерацій або при отриманні результату, який знаходиться в межах 0,001 від заданого цільового значення. При необхідності змінити ці установки можна в меню Сервіс → Параметри. Для цього на вкладці Обчислення треба змінити значення полів Предельноечіслоітерацій і / або Относітельнаяпогрешность.

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

Приклад 1. Для перевезення вантажу вагою 400 т було подано склад з 5 критих вагонів і 7 платформ. Якою має бути вага вантажу в вагоні кожного типу, якщо на навантаження встановлені наступні обмеження: вага вантажу в вагонах одного типу повинен бути однаковим, а вага вантажу на платформі в 2 рази більше, ніж в критому вагоні?

Введемо на аркуші Excel вихідні дані і відповідні формули (рис. 1). Потім встановимо курсор в цільову комірку (комірку з результуючої формулою) C4 і виконаємо команду Сервіс → Підбір параметра. у вікні введемо необхідні дані (рис. 2). Після натискання на кнопку OK в осередках C2, C3 і C4 отримуємо результат (рис. 3).

Мал. 2. Форма вікна Підбір параметра для прикладу 1

Мал. 3. Вид вікна Результат підбору параметра для прикладу 1

Аналогічним чином Excel дозволяє легко вирішити рівняння з одним невідомим. Загальні рекомендації по знаходженню коренів рівнянь можна сформулювати наступним чином:

1) побудувати таблицю значень або графік функції і визначити, на якому інтервалі зміни аргументу функція змінює знак;

2) визначити початкове (наближене) значення шуканого кореня рівняння і задати його у вхідній (змінною) осередку;

3) ввести вирішуване рівняння (формулу) в цільову комірку (т. Е. Осередок результату);

4) ввести необхідні дані в меню Сервіс → Підбір параметра.

Приклад 2. Розглянемо приклад вирішення трансцендентного (т. Е. Містить неалгебраїчні функції) рівняння x 3 = e x.

Для зручності перетворимо це рівняння до виду x 3 - e x = 0.

Для визначення початкового наближення кореня протабуліруем функцію і побудуємо її графік, наприклад, на відрізку [0; 3] (рис. 4).

З таблиці значень функції та графіка випливає, що функція змінює знак на відрізку xЄ [1,5; 2], отже, на цьому відрізку знаходиться корінь функції. Для уточнення його значення виберемо в якості початкового наближення кореня будь-яку межу цього відрізка, наприклад, 1,5. Введемо це значення в клітинку A14. Для зручності дамо їй ім'я x. У осередок B14 введемо формулу - ліву частину решаемого рівняння, т. Е. = X ^ 3-EXP (x). Потім, перебуваючи в цьому осередку. виконаємо команду Сервіс → Підбір параметра і в діалоговому вікні задамо необхідні параметри (див. рис. 4). При цьому в віконці «Значення» вводиться права частина решаемого рівняння (в нашому випадку 0).

Мал. 4. Обчислення кореня рівняння

Установки у вікні «Підбір параметра» на рис. 4 доручають Excel перебирати значення в осередку x (A14) до тих пір, поки залежне від x вираз, записане в осередку B14, не досягне заданого значення 0.

Після закінчення процесу пошуку отримаємо значення кореня x = 1,85694 (рис. 5).

Мал. 5. Вид вікна Результат підбору параметра для прикладу 2