Розв’язування типових математичних задач засобами Microsoft Excel. Задача підбору параметра
Мета: освоїти методи та засоби розв'язування задачі підбору параметра засобами Microsoft Excel.
Завдання:
1. Ознайомитися з завданням та рекомендаціями по його виконанню.
2. Виконати на комп'ютері задачу підбору параметра засобами MS Excel відповідно до послідовності виконання роботи, приведеної нижче.
3. Виконати індивідуальне завдання за своїм варіантом. Варіант обирається відповідно до номеру своєї залікової книжки. Для цього беруться дві останні цифри номеру залікової книжки. Якщо дві останні цифри номеру залікової книжки більше 40, то береться їх сума. Збірник завдань знаходиться у викладача або дивись розділ 2 методичного посібника.
4. Результати роботи представити викладачу у вигляді звіту.
Послідовність виконання роботи:
Постановка задачі
Задачею підбору параметра називається знаходження такого значення аргумента даної функції, при якому ця функція набуває заданого значення.
Зокрема, це може бути пошук коренів заданого рівняння f(x)=0, коли необхідно знайти такі значення аргумента, за яких функція набуває нульового значення.
Розглянемо задачу знаходження коренів рівняння 
. Якщо 
має простий вигляд, то корені можна знайти аналітичним методом, тобто за формулами:
q при 
маємо лінійне рівняння 
, корінь якого 
;
q при 
маємо квадратне рівняння 
, дійсні корені якого 
.
Але у більш складних випадках формул для обчислення коренів рівняння не існує. Тому застосовують наступну чисельну методику:
|
|
|
q спочатку здійснюють відокремлення всіх коренів, тобто на заданому широкому проміжку 
для кожного кореня знаходять досить вузький проміжок 
, на якому відсутні інші корені;
q уточнюють корені, тобто на кожному з вузьких проміжків знаходять значення кореня із заданою точністю.
Розглянемо відокремлення коренів. Основні методи відокремлення коренів: графічний та табличний.
У відповідності з графічним методом необхідно побудувати графік заданої функції 
на всьому заданому проміжку , після чого візуально локалізувати точки його перетину з віссю Ox. Далі в околі точок перетину треба довільним способом вибрати вузькі проміжки так, щоб на кожному з них знаходилась лише одна точка перетину графіка з віссю. Один із недоліків даного методу – його громіздкість. Другий недолік – ненадійність – пов’язаний з можливістю втратити корені при неякісній побудові графіка.
Більш зручним слід вважати табличний метод, оскільки він досить формальний і зводиться до послідовності простих обчислювальних операцій. У відповідності з цим методом, на всьому широкому проміжку визначають знаки функції з певним кроком h. З одержаної таблиці знаків вибирають вузькі проміжки , на кінцях яких функція має протилежні знаки. Чим дрібніший крок h, тим надійніше будуть відокремлені корені, тим менша ймовірність їх втратити.
|
|
|
Приклад розв’язання рівняння
Відокремимо корені рівняння 
на широкому проміжку 
. Виберемо крок 
. При цьому ми припускаємо, що відстань між найближчими коренями даного рівняння перевищує вибраний крок, і тому корені втрачені не будуть. Складемо таблицю знаків функції 
на проміжку .
| x | 1.0 | 1.5 | 2.0 | 2.5 | 3.0 | 3.5 | 4.0 | 4.5 | 5.0 |
| Знак f(x) | + | + | + | – | – | – | 0 | + | + |
Таким чином, таблиця показує, що на широкому проміжку знаходяться два корені: перший – в межах вузького проміжка 
, другий – в межах вузького проміжка 
.
Подібну таблицю для заданої функції можна скласти засобами MS Excel. Для цього достатньо знати широкий проміжок , а також правильно вибрати крок побудови таблиці h.
І. Виконуємо побудову таблиці значень заданої функції на широкому проміжку [A,B] з кроком h=0,5.
ІІ. Визначаємо вузькі проміжки, де знаходяться корені (на кінцях цього проміжку функція f(x) має протилежні знаки). Виділяємо кінці таких проміжків шрифтом – полужирний курсив.
ІІІ. Для уточнення коренів в MS Excel використовують підбір параметрів. При цьому порядок дій для кожного вузького проміжка може бути наступним:
|
|
|
¨ Вибрати комірки для розміщення значення аргумента x і функції y (наприклад, F4 і F5 відповідно).
¨ В комірку F4 ввести орієнтовне значення кореня. Це має бути один із кінців вузького проміжка .
¨ В комірку F5 ввести формулу для обчислення значення функції , вважаючи, що аргумент x знаходиться в комірці F4.
¨ Виконати команду Сервис-Подбор параметра.... Внаслідок цього на екрані з’являється діалогове вікно Подбор параметра.
¨ В поле Установить в ячейке: ввести адресу комірки значення функції, тобто F5.
¨ В поле Значение: ввести нульове значення.
¨ В поле Изменяя значение ячейки: ввести адресу комірки значення аргумента, тобто F4.
¨ Натиснути кнопку OK діалогового вікна. З’являється нове вікно Результат подбора параметра, в якому повідомляється, вдалося чи ні розв’язати рівняння.
¨ Значення кореня рівняння з’являється у комірці F4.
Таким чином, ми отримуємо наступні результати:
¨ на проміжку [2;2,5] корінь знаходиться в точці x=2,2;
¨ на проміжку [3,5;4,5] корінь знаходиться в точці x=4.
Якщо користувача не задовольняє точність знайденого значення, то він може її змінити. Для цього треба виконати команду Сервис-Параметры..., у діалоговому вікні Параметры вибрати вкладинку Вычисления, а полі Относительная погрешность: цієї вкладинки ввести інше значення (наприклад, 0.00001 замість 0.001).
|
|
|
Приклад оформлення
| Завдання №3. Розв'язування рівняння | |||||
|
|
|
|
| ||
| Рівняння: |
|
| |||
|
|
|
| |||
| Таблиця значень: |
|
| Корені: |
| |
| i | x | y |
|
|
|
| 1 | -10 | 304,7000 |
| 1) x = | 1,9347 |
| 2 | -9,5 | 280,5000 |
| y = | 0,0000 |
| 3 | -9 | 257,3000 |
|
|
|
| 4 | -8,5 | 235,1000 |
| 2) x = | 2,7653 |
| 5 | -8 | 213,9000 |
| y = | 0,0000 |
| 6 | -7,5 | 193,7000 |
|
|
|
| 7 | -7 | 174,5000 |
|
|
|
| 8 | -6,5 | 156,3000 |
|
|
|
| 9 | -6 | 139,1000 |
|
|
|
| 10 | -5,5 | 122,9000 |
|
|
|
| 11 | -5 | 107,7000 |
|
|
|
| 12 | -4,5 | 93,5000 |
|
|
|
| 13 | -4 | 80,3000 |
|
|
|
| 14 | -3,5 | 68,1000 |
|
|
|
| 15 | -3 | 56,9000 |
|
|
|
| 16 | -2,5 | 46,7000 |
|
|
|
| 17 | -2 | 37,5000 |
|
|
|
| 18 | -1,5 | 29,3000 |
|
|
|
| 19 | -1 | 22,1000 |
|
|
|
| 20 | -0,5 | 15,9000 |
|
|
|
| 21 | 0 | 10,7000 |
|
|
|
| 22 | 0,5 | 6,5000 |
|
|
|
| 23 | 1 | 3,3000 |
|
|
|
| 24 | 1,5 | 1,1000 |
|
|
|
| 25 | 2 | -0,1000 |
|
|
|
| 26 | 2,5 | -0,3000 |
|
|
|
| 27 | 3 | 0,5000 |
|
|
|
| 28 | 3,5 | 2,3000 |
|
|
|
| 29 | 4 | 5,1000 |
|
|
|
| 30 | 4,5 | 8,9000 |
|
|
|
| 31 | 5 | 13,7000 |
|
|
|
| 32 | 5,5 | 19,5000 |
|
|
|
| 33 | 6 | 26,3000 |
|
|
|
| 34 | 6,5 | 34,1000 |
|
|
|
| 35 | 7 | 42,9000 |
|
|
|
| 36 | 7,5 | 52,7000 |
|
|
|
| 37 | 8 | 63,5000 |
|
|
|
| 38 | 8,5 | 75,3000 |
|
|
|
| 39 | 9 | 88,1000 |
|
|
|
| 40 | 9,5 | 101,9000 |
|
|
|
| 41 | 10 | 116,7000 |
|
|
|
| Таблиця значень у вигляді формул: |
| Корені: |
| ||
| i | x | y |
|
|
|
| 1 | -10 | =2*B6^2-9,4*B6+10,7 |
| 1) x = | 1,93490499648135 |
| 2 | -9,5 | =2*B7^2-9,4*B7+10,7 |
| y = | =2*F6^2-9,4*F6+10,7 |
| 3 | -9 | =2*B8^2-9,4*B8+10,7 |
|
|
|
| 4 | -8,5 | =2*B9^2-9,4*B9+10,7 |
| 2) x = | 2,76564268442471 |
| 5 | -8 | =2*B10^2-9,4*B10+10,7 |
| y = | =2*F9^2-9,4*F9+10,7 |
| 6 | -7,5 | =2*B11^2-9,4*B11+10,7 |
|
|
|
| 7 | -7 | =2*B12^2-9,4*B12+10,7 |
|
|
|
| 8 | -6,5 | =2*B13^2-9,4*B13+10,7 |
|
|
|
| 9 | -6 | =2*B14^2-9,4*B14+10,7 |
|
|
|
| 10 | -5,5 | =2*B15^2-9,4*B15+10,7 |
|
|
|
| 11 | -5 | =2*B16^2-9,4*B16+10,7 |
|
|
|
| 12 | -4,5 | =2*B17^2-9,4*B17+10,7 |
|
|
|
| 13 | -4 | =2*B18^2-9,4*B18+10,7 |
|
|
|
| 14 | -3,5 | =2*B19^2-9,4*B19+10,7 |
|
|
|
| 15 | -3 | =2*B20^2-9,4*B20+10,7 |
|
|
|
| 16 | -2,5 | =2*B21^2-9,4*B21+10,7 |
|
|
|
| 17 | -2 | =2*B22^2-9,4*B22+10,7 |
|
|
|
| 18 | -1,5 | =2*B23^2-9,4*B23+10,7 |
|
|
|
| 19 | -1 | =2*B24^2-9,4*B24+10,7 |
|
|
|
| 20 | -0,5 | =2*B25^2-9,4*B25+10,7 |
|
|
|
| 21 | 0 | =2*B26^2-9,4*B26+10,7 |
|
|
|
| 22 | 0,5 | =2*B27^2-9,4*B27+10,7 |
|
|
|
| 23 | 1 | =2*B28^2-9,4*B28+10,7 |
|
|
|
| 24 | 1,5 | =2*B29^2-9,4*B29+10,7 |
|
|
|
| 25 | 2 | =2*B30^2-9,4*B30+10,7 |
|
|
|
| 26 | 2,5 | =2*B31^2-9,4*B31+10,7 |
|
|
|
| 27 | 3 | =2*B32^2-9,4*B32+10,7 |
|
|
|
| 28 | 3,5 | =2*B33^2-9,4*B33+10,7 |
|
|
|
| 29 | 4 | =2*B34^2-9,4*B34+10,7 |
|
|
|
| 30 | 4,5 | =2*B35^2-9,4*B35+10,7 |
|
|
|
| 31 | 5 | =2*B36^2-9,4*B36+10,7 |
|
|
|
| 32 | 5,5 | =2*B37^2-9,4*B37+10,7 |
|
|
|
| 33 | 6 | =2*B38^2-9,4*B38+10,7 |
|
|
|
| 34 | 6,5 | =2*B39^2-9,4*B39+10,7 |
|
|
|
| 35 | 7 | =2*B40^2-9,4*B40+10,7 |
|
|
|
| 36 | 7,5 | =2*B41^2-9,4*B41+10,7 |
|
|
|
| 37 | 8 | =2*B42^2-9,4*B42+10,7 |
|
|
|
| 38 | 8,5 | =2*B43^2-9,4*B43+10,7 |
|
|
|
| 39 | 9 | =2*B44^2-9,4*B44+10,7 |
|
|
|
| 40 | 9,5 | =2*B45^2-9,4*B45+10,7 |
|
|
|
| 41 | 10 | =2*B46^2-9,4*B46+10,7 |
|
|
|
Примітка.Таблиця значень у вигляді формул отримується в табличному процесорі MS Excel за допомогою командиСервис-Параметры,а у вікні Параметры вибрати вкладку Вид, де задати прапорець Формулы.
Питання до захисту практичної роботи № 5:
1. В чому суть задачі підбору параметра ?
2. Які способи знаходження коренів рівняння ?
3. Які є основні методи відокремлення коренів ?
4. Назвіть основні кроки розв'язання рівняння при відокремленні коренів табличним методом.
Практична робота № 6
Дата добавления: 2018-02-18; просмотров: 1925; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!