Задача 8 «Розв'язування нелінійного рівняння»

Дано нелінійне рівняння 2пх-п - sinnx, де п — номер варіанта. Розв'язати рівняння методом простих ітерацій (див. рис. 45) і методом добирання параметра.

План

1. Абсолютна і змішана адреси.

2. Логічні функції.

3. Дати.

4. Метод добирання параметра.

5. Метод простих ітерацій.

Теоретичні відомості

1. Абсолютна і змішана адреси. Розглянемо поняття абсолют­
ної і змішаної адрес клітинки у формулі. Абсолютною називають
адресу, в якій є два символи $: один перед назвою стовпця, другий
— перед номером рядка, наприклад $Е$3. Змішана адреса містить
лише один символ $. Правило: частина адреси після символу $ не
модифікується під час копіювання формули.

Абсолютні адреси слугують, зокрема, для посилання на клітинки, які містять константи, що є у формулах. Такою кон­стантою є, наприклад, відсотки (12% =0,12) річних у задачі 2. Якщо для задачі 2 число 0,12 занести в клітинку ЕЗ, то в клітинку СЗ можна ввести формулу = ВЗ*$Е$3.

2. Логічні функції. Розгалуження в ЕТ реалізовують за
допомогою функції ЯКЩО (ЕСЛИ, IF), яка використовується у
формулах і має таку структуру:

ЯКЩО(<лопчний вираз>; <вираз 1>; <вираз 2>).

Логічний вираз — це форма запису умови: простої або складеної.

Якщо умова істинна, то функція набуває значення першого виразу, інакше — другого.

Вираз 1 чи вираз 2 також може бути функцією ЯКЩО — так утворюють вкладені розгалуження. Часто виразом 1 чи виразом 2 є лише адреса клітинки, яка містить деяке значення або конкретне число.

Прості умови записують так, як в алгоритмічних мовах — за допомогою операцій порівняння =, >, <, <=, > =, <>, визначених над виразами, наприклад, 7>5, А5<=20 тощо.

Складені умови записують за допомогою логічних функцій

І(< умова"! >;<умова2>;...) та АБО(<умова1>;<умова2>;...).

Функція І (И, AND) істинна, якщо всі умови в списку істинні.

Функція АБО (ИЛИ, OR) істинна, якщо хоч би одна умова в списку її аргументів істинна.

Наприклад, функція ЯКЩО(АБО(5>7; 5<7); 5; 7) набуває значення 5, а функція ЯКЩО(І (5>7; 5<7); 5; 7) — значення 7.

Якщо користувач не пам'ятає вигляду функції, він може вставити її у вираз за допомогою майстра функцій, який викли­кають командою Вставити ■=> Функція. У цьому разі потрібно ви­брати назву функції із запропонованого списку (крок 1) і запов­нити поля значеннями параметрів (крок 2).

Працюючи в Excel, потрібно користуватися російськими (ЕС­ЛИ, И, ИЛИ) або англійськими (IF, AND, OR) назвами функцій.

3. Дати. В економічних чи бухгалтерських задачах дати тра* пляються доволі часто. Дати в ЕТ чисто умовно зачисляють до даних типу дата. Наспраді дати зберігаються в ЕТ як цілі числа і лише відображаються на екрані в тому чи іншому форматі, наприклад: 12 січня 2006 р. чи 12.01.2006, чи 2006-01-12, чи 38729. За точку відліку дат взято 1 січня 1900 року. Будь-яка дата еквівалентна цілому числу, що дорівнює кількості днів, які минули від точки відліку (38729 для 12 січня 2006 p.). Це дає змогу виконувати над датами такі операції: віднімати дати для визначення проміжку днів між двома датами, додавати чи від­німати від дат ціле число.

Можна застосовувати функції опрацювання дат з метою визначення окремих компонент дати: поточного номера дня в тижні (WEEKDAY), в місяці (DAY), номера місяця в році (MONTH), року (YEAR), поточної дати (TODAY) тощо. Ці функції потрібно вставляти у вирази командами Вставити ■=> Функція ^ Дати і час. Деякі з цих функцій мають параметри, дія яких описується у відповідному діалоговому вікні.

Час задається годиною, хвилиною, секундою. Йому від­повідає десяткове число від 0 до 1, що відображає частину доби.

 

162

163

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

4. Метод добирання параметра. Метод добирання параметра призначений для розв'язування нелінійного рівняння f(x) = с. Суть методу полягає в тому, щоб автоматично визначити (з деякою точністю) таке значення параметра х, для якого функція f(x) одержує потрібне значення с.

Цей засіб має важливе значення для розв'язування задач зворотного економічного аналізу. Наприклад такої: скільки треба купити одиниць деякого товару (це є параметр), щоб вклас­тися в заплановану суму (це функція).

Розглянемо задачу: встановити тарифну ставку (це пара­метр) дванадцятьом працівникам, щоб вкластися в запланований обсяг зарплатні (це функція) 1000 грн.

Модель задачі. Нехай А1 — адреса клітинки, що міститиме відповідь — значення параметра-ставки, a f(Al) — задана функ­ція (мета дослідження), наприклад, зарплатня(АІ) = 12*А1. Складаємо рівняння, яке є математичною Моделлю задачі: 12*А1 = 1000.

Хоча задача дуже проста, застосуємо для її розв'язування метод підбору параметра, який полягає в тому, що програма сама має підібрати значення А1, щоб задовольнити будь-яке рівняння.

Алгоритм дій користувача такий.

1. У будь-яку клітинку (але не в А1) треба занести формулу ■■ f(Al). У нашому випадку формула така: *■ 12*А1.

2. Вибрати цю клітинку і виконати команду Сервіс ■=> Підбір параметра. Отримаємо діалогове вікно Підбір параметра.

3. Заповнити три поля: а) зазначити адресу формули (вона буде вказана автоматично, якщо клітинка з формулою була вибрана перед цим); б) бажане значення формули, тобто с (у нашому випадку 1000); в) адресу параметра — А1.

4. Натиснути на ОК і у клітинці А1 отримати результат.

Другий спосіб полягає у використанні можливостей програ­ми Solver («Пошук розв'язку»), що додається до Excel. Вона дає змогу розв'язувати задачі з багатьма параметрами і з обмежен­нями. Наприклад, такі: скільки треба купити одиниць двох чи трьох найменувань товарів (це параметри), щоб вкластися в заплановану суму (це функція) і щоб кількості товарів не пере­вищували деяких величин (це обмеження у вигляді нерівностей, див. роботу № 24).

5. Метод простих ітерацій. Продовжимо вивчати застосу­
вання електронних таблиць для розв'язування типових матема­
тичних задач. Розглянемо ще два способи розв'язування неліній-

КИ

ного рівняння: 1) метод простих ітерацій з побудовою таблиці; 2) метод простих ітерацій з використанням двох клітинок.

Розглянемо метод простих ітерацій. Щоб нелінійне рівняння f (x) = с можна було розв'язати методом простих ітерацій, його зводять до вигляду х — z (x) так, щоб виконувалась нерівність: \ z '(x)\ < 1. За цієї умови метод простих ітерацій збігається, тобто дає правильний розв'язок. Наприклад, рівняння 2пх-п = sinnx спочатку треба звести до такого вигляду:

х = (sinnx + п)/2п.

Метод простої ітерації реалізують за допомогою рекурентної формули так:

X = (sinnx₍+ n)/2n, де х_о - будь-яке початкове наближення, і=0, 1, 2,..., а замість п треба підставити значення свого варіанта. Домовимося, що коли і=8, то значення x_t (тобто x_g) вважатимемо розв'язком рівняння. Розглянемо реалізацію рекурентної формули в ЕТ. Нехай л=1, а в клітинку А6 введено будь-яке початкове наближення, наприклад 2. Тоді наступне наближення отримаємо в клітинці В6, ввівши туди формулу =(sin(A6)+1)/2. Це значення приймаємо за початкове для наступної ітерації: в А7 заносимо значення В6. У клітинці В7 отримуємо наступне наближення і т.д. У клітинці ВІЗ буде останнє (восьме) наближення, яке і прий­маємо за розв'язок.

Другий спосіб полягає у використанні властивості ЕТ авто­матичного багаторазового переобчислення, якщо ввімкнений режим ітерацій у діалоговому вікні Параметри. Тут для розв'я­зування задачі достатньо двох клітинок (див. рис. 45, рядок 17). Цей спосіб розглянемо під час виконання роботи.

Словник

 

Підбір параметра	Подбор параметра	Goal Seak
Пошук розв'язку	Поиск решения	Solver
Дата	Дата	Date
Якщо/і/або	Если/и/или	If/And/Or
Захист	Защита	Protection
Функція	Функция	Function
Ітерації	Итерации	Iterations
Клітинка-ціль	Целевая ячейка	Targer Cell
Обмеження	Ограничения	Constraints
Сторінка/Книжка	Лист/Книга	Sheet/Book
СЬОГОДНІ	СЕГОДНЯ	TODAY

Хід роботи