Рішення математичних задач у середовищі Excel

1 Чисельне диференціювання

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

Для обчислення похідної в Excel будемо використовувати наведенузалежність.

Розглянемо методику обчислення похідної на прикладі вправи.

Припустимо потрібно знайти похідну функції Y = 2x3 + x2 в точці x = 3. < br>Похідна, обчислена аналітичним методом, дорівнює 60.

Для обчислення похідної виконайте наступні дії:
. табуліруйте задану функцію в околі точки х = 3 з досить малим кроком, наприклад 0,001 (див рис.)

. в комірку С2 введіть формулу обчислення похідної. Тут осередок В2 містить значення хк 1, комірка А2 - хк.
. буксируванням скопіювати формулу до рядка 7, отримаємо значення похідних в точках табуляції аргументу.
Для значення х = 3 похідна функції дорівнює значенню 60,019, що близько дозначенню, вирахуваній аналітично.

2 Чисельне обчислення визначених інтегралів

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

Методику обчислення певного інтеграла в Excel з використаннямприведеної формули розглянемо на прикладі.

Нехай потрібно обчислити визначений інтеграл
Величина інтеграла, обчислена аналітично дорівнює 9. Для чисельногообчислення величини інтеграла з використанням наведеної формуливиконайте наступні дії:

. табуліруйте підінтегральною функцією в діапазоні зміни значень аргументу 0 - 3 (див. рис .).

. в комірку С3 введіть формулу = (A3-A2) * B2 + (A3-A2) * (B3-B2)/2 + C2, яка реалізує підінтегральною функцією.

. Скопіюйте буксируванням формулу, записану в комірці С3 до значення аргументу х = 3. Обчислення значення в комірці С17 і буде величиною заданого інтеграла - 9.

3 Знаходження екстремумів функцій за допомогою інструменту Пошук рішення

Якщо функція F (x) неперервна на відрізку [a, b] і має всередині цьоговідрізка локальний екстремум, то його можна знайти використовуючи надбудову Excel
Пошук рішення.

Розглянемо послідовність знаходження екстремуму функції на прикладінаступного вправи.

Нехай задана нерозривний функція Y = X2 + X 2. Потрібно знайти їїекстремум (мінімальне значення).

Для вирішення завдання виконайте дії:
. У комірку А2 робочого аркуша введіть будь-яке число належить області визначення функції, у цій клітинці буде знаходитися значення Х;
. У комірку В2 введіть формулу, що визначає задану функцію. Замість змінної Х в цій формулі має бути посилання на комірку А2: = A2 ^ 2 + A2

2
. Виконайте команду меню Сервіс/Пошук рішення;
. Налаштуйте параметри інструменту Пошук рішення: число ітерацій - 1000, відносна похибка 0,00001.
. в полі Встановити цільову клітинку вкажіть адресу комірки, що містить формулу

(А2), виберіть варіант мінімального значення, в полі Змінюючи клітинки введіть адресу комірки, що містить Х (А2);
. Клацніть на кнопці Виконати. У комірці А2 буде поміщено значення Х функції, при якому вона має мінімальне значення, а в комірці В2 - мінімальне значення функції.

Зверніть увагу, що у вікні Пошук рішення можна встановлювати обмеження. Їх доцільно використовувати, якщо функція многоекстремальна, а потрібно знайти екстремум в заданому діапазоні зміни аргументу.

4 Рішення систем лінійних рівнянь

1 Вбудовані функції для роботи з матрицями

У бібліотеці Excel у роздiлi математичних функцій є функції длявиконання операцій над матрицями (табл.1.1).

Таблиця 1.1

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

МОБР (А1: B2) або МОДР (матріца_1).

2 Рішення систем лінійних рівнянь

Відомо, що система лінійних рівнянь в матричному поданнізаписується у вигляді:

AX = B.
Рішення такої системи записується у вигляді

X = A-1B,
Де A-1-матриця, обернена по відношенню до А.

3 Приклад рішення системи лінійних рівнянь:

Нехай система рівнянь задана матрицями:

Для вирішення завдання виконайте дії:
. Виділіть діапазон розмірністю 2 х 2 і надайте йому ім'я А;
. Виділіть діапазон розмірністю 1 х 2 і надайте йому ім'я В;
. Виділіть діапазон розмірністю 1 х 2 і надайте йому ім'я Х;
. Використовуючи список імен виділіть діапазон А і введіть у нього значення елементів матриці А;
. Використовуючи список імен виділіть діапазон В і введіть у нього значення елементів вектора В;
. Використовуючи список імен виділіть діапазон Х для приміщення результату рішення системи;
. У виділений діапазон Х введіть формулу

= МУМНОЖ (МОБР (А); В);
. Вкажіть Excel, що виконується операція над масивами, для цього натисніть комбінацію клавіш + +, в комірках діапазону Х буде отриманий результат: х1 = 2,16667, х2 = - 1,33333

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

Розв'яжіть систему рівнянь виду AX = B і виконайте перевірку рішення

5 Рішення нелінійних рівнянь методом підбору параметра

Використовуючи можливості Excel можна знаходити коріння нелінійного рівнянняв допустимої області визначення змінної. Послідовність операційзнаходження коренів наступна:

1. Рівняння представляється у вигляді функції однієї змінної;

2. Проводиться табулювання функції в діапазоні ймовірного існування коренів;

3. По таблиці фіксуються найближчі наближення до значень коренів;

4. Використовуючи засіб Excel Підбір параметра, обчислюються корені рівняння з заданою точністю.

Розглянемо послідовність відшукання коренів нелінійного рівнянняна прикладі.

Потрібно знайти всі корені рівняння X3-0, 01X2-0, 7044X +0,139104 = 0 навідрізку [-1; 1]. Права частина рівняння представлена поліномом третійступеня, отже, рівняння може мати не більше трьох коренів.
1. представимо рівняння у вигляді функції

Y = X3-0, 01X2-0, 7044X 0,139104

Відомо, що коріння вихідного рівняння знаходяться в точках перетинуграфіка функції з віссю Х.
2. Для локалізації початкових наближень необхідно визначити інтервали значень Х, усередині яких значення функції перетинає вісь абсцис, тобто функція змінює знак. З цією метою табуліруем функцію на відрізку [-1; 1] з кроком 0,2, отримаємо табличні значення функції. З отриманої таблиці знаходимо, що значення функції тричі перетинає вісь Х, отже, вихідне рівняння має на заданому відрізку всі три корені.
3. Аналіз таблиці показує, що функція змінює знак в наступних інтервалах значень аргументу Х: (-1; -0,8), (-0,2; 0,4) і (0,6; 0,8).

Тому в якості початкових наближень візьмемо значення Х: -0,8; -0,2 і

0,6.
4. На вільній ділянці робочого аркуша, як показано на малюнку, в комірки

А15: A17 введіть початкові наближення, а відповідні клітинки стовпця

У скопіювати формулу.

5 . Виконайте команду меню Сервіс/Параметри, у вкладці Обчислення встановити відносну похибку обчислень E = 0,00001, а число ітерацій N = 1000, встановіть прапорець Ітерації.
6. Виконайте команду меню Сервіс/Підбір параметра. У діалоговому вікні заповніть наступні поля:

Встановити в клітинці: у полі вказується адреса комірки, в якій записанаформула правій частині функції;

Значення: у полі вказується значення, яке має отримати поліном врезультаті обчислень, тобто права частина рівняння (у нашому випадку 0);

Змінюючи значення: у полі вказується адреса комірки (де записано початковунаближення), в якій буде обчислюватися корінь рівняння і на якупосилається формула.

Якщо клацнути на ОК отримаємо значення першого кореня: -0,92.

Виконуючи послідовно операції аналогічні попереднім, обчислимозначення інших коренів: -0,209991 і 0,720002.

6 Рішення систем нелінійних рівнянь

Застосовуючи надбудову Excel Пошук рішення можна вирішувати системинелінійних рівнянь. Попередньо система рівнянь повинна бутиприведена до одного рівняння. Розглянемо послідовність рішення наприкладі вправи.

Дана система двох рівнянь:

Потрібно знайти всі корені наведеного рівняння для діапазону значень х іy [-3; 3].
Крок 1. Наведемо систему до одного рівняння. Пара (x, y) є рішенням системи тоді і тільки тоді, коли вона є рішенням наступного рівняння з двома невідомими:

(x2 + y2 - 3) 2 + (2x + 3y - 1) 2 = 0
Крок 2. Для вирішення останнього рівняння необхідно знайти початковінаближення, для цього табуліруем вираз, що стоїть в лівій частині якфункцію по двох змінних x та y. Для табуляції функції виконайте наступнідії:
. У стовпець А введіть послідовність значень Х із кроком 0,5, а рядок 3

- послідовність значень У також з кроком 0,5.
. Дайте діапазонами значень Х та У імена Х і У, відповідно.
. Виділіть діапазон комірок, в якому будуть обчислюватися значення функції

(B4: N16).
. У виділений діапазон введіть формулу

= (Х ^ 2 + Y ^ 2-3) ^ 2 + (2 * Х 3 * Y-1) ^ 2.
. Натиснувши комбінацію клавіш [Ctrl] + [Shift] + [Enter] виконайте операцію над виділеним масивом. У виділеному діапазоні з'являться обчислені значення функції.
Крок 3. Знайдемо початкові наближення. Оскільки табуліруемая функція задаєповерхню, то початкові наближення слід шукати в западинах, тобто вточках, де функція приймає найменші значення. На малюнку ці точкизатемнені. Початковими наближеннями є пари (-1; 1) та (1,5; -0,5).
Введіть значення знайдених наближень у суміжні комірки робочого листа (див. рис.). Над стовпцями зробіть написи XX і YY, які виконуватимутьу формулах роль міток. Зверніть увагу, що ми вже використали імена Х і
Y, тому імена нових міток повинні відрізнятися.
Крок 4. До комірки рядка, в якій записана перша пара Х і У введітьформулу, яка обчислює значення функції:

= (XX ^ 2 + YY ^ 2-3) ^ 2 + (2 * XX 3 * YY-1) ^ 2і скопіюйте її в наступний рядок.
Крок 4. Встановіть курсор на комірку, в якій записана формула і виконайтекоманду меню Сервіс/Пошук рішення. Виконайте налаштування параметрівінструменту Пошук рішення: Граничне число ітерацій - 1000, відноснапохибка 0,000001.
У вікні Поиск решения в якості цільової комірки встановіть адресу комірки,що містить формулу, Зведіть перемикач мінімального значення, в полі
Змінюючи клітинки вкажіть адресу діапазону, що містить початкові наближення іклацніть на ОК. В осередках, де зберігалися початкові наближення будеотримана перша пара коренів.
Повторіть такі ж операції для другої пари наближень.
Рішенням системи є пари (-1,269; 1,1791) і (1,5764; -0,718).

Завдання для самостійної роботи

1. Знайти корені рівняння:

| Варіант | Рівняння | Відповідь |
| 1 | Sin (x) e-2x = 0 для значень х [-2; 2] | Х = 0 |
| 2 | X3-2, 56x2-1, 3251x +4,395006 = 0 | X =- 0,94644 |
| 3 | X3-2, 92x2 1,4355 x +0,791136 = 0 для х | -0,32; 1,229997; |
| | [-3; 3] | 2,010001 |
| 4 | x3-2, 84x2-5, 6064x-1476336 = 0 | 4,700766 |
| 5 | X3 +1,41 x2-5, 4724x-7, 380384 = 0 | 3,542723 |

2. Знайти корені лінійного рівняння виду Ах = В і виконати перевірку:

Варіант 1 Варіант2

Варіант 3 Варіант 4

3. Знайти похідну функції:a) Y = 2x2 при х = 3b) Y = Sin (x) для х = 0c) Y = Cos (x) для х = 0d) Y = Sin (x) для х = Пі/2e) Y = Cos (x) для х = Пі/2f) Y = Tg (x) для х = 0
4. Обчислити визначений інтеграл:
А) В)

С) D)

5. Знайти екстремум функції:a) Y = (2 - x) 2b) Y = x2 + y2 - 3c) Y = (x-2) 2 + (y +3) 2-6d) Y = sin (2x) для х [0; Пі/2]

-----------------------< br>EMBED Equation.3

< p>