Решение уравнений и систем уравнений с помощью приближенных вычислений

Відкрийте робочу книгу "Решение уравнений" і завантажте собі копію.

Розглянемо знаходження коренів рівняння:

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

З метою знаходження інтервалів, на кінцях яких функція змінює знак, необхідно побудувати її графік або протабулювати її. Складемо таблицю значень функції на інтервалі [-1; 1] з кроком 0,2. Для цього необхідно:

• Ввести в клітинку A2 значення -1, а в клітинку A3 значення -0,8. 
• Вибрати діапазон A2: A3, розташувати покажчик миші на маркері заповнення цього діапазону і протягнути його на діапазон A4: A12, аргумент протабулювати. У осередок B2 ввести формулу: = A2 ^ 3-0,01 * A2 ^ 2-0,7044 * A2 + 0,139104 
• Вибрати клітинку B2. 
• Розташувати курсор миші на маркері заповнення цього осередку і протягнути його на діапазон B3: B12. Функція також протабулювати.

Значення аргументу х	Значення функції у
-1,00	-0,1665
-0,8	0,1842
-0,60	0,3421
-0,4	0,3553
-0,20	0,2716
0	0,1391
0,20	0,0058
0,4	-0,0803
0,60	-0,0711
0,8	0,0812
1,00	0,4247

З таблиці видно, що поліном змінює знак на інтервалах [-1; -0,8], [0,2; 0,4] і [0,6; 0,8], і тому на кожному з цих інтервалів є своє коріння. Так як поліном третього ступеня має не більше трьох коренів, то вони все локалізовані.

Перш ніж приступити до знаходження коренів за допомогою підбору параметра, необхідно виконати деяку підготовчу роботу:

• Встановити точність, з якою знаходиться корінь. Корінь за допомогою підбору параметра знаходиться методом послідовних наближень. Для цього в Настройці панелі швидкого доступу / Інші команди, і на вкладці Формули діалогового вікна Параметри Excel задайте в Параметрах обчислень відносну похибку і граничне число ітерацій рівними 0,00001 і 1000, відповідно. 
• Відвести на робочому аркуші клітинку, наприклад С2, під шуканий корінь. Цей осередок грати подвійну роль. До застосування підбору параметра в ній знаходиться початкове наближення до кореня рівняння, а після застосування - знайдене наближене значення кореня.

Корінь за допомогою підбору параметра знаходимо методом послідовних наближень. Тому в клітинку C2 треба ввести значення, що є наближенням до шуканого кореня. У нашому випадку, першим відрізком локалізації кореня є [-1; -0,8]. Отже, за початкове наближення до кореня розумно взяти середню точку цього відрізка -0,9.

• Відвести осередок, наприклад D2, під функцію, для якої ведеться пошук кореня, причому замість невідомої у цій функції повинна вказуватися посилання на осередок, відведену під шуканий корінь. Таким чином, в осередок D2 введіть формулу: = C2 ^ 3-0,01 * C2 ^ 2-0,7044 * C2 + 0,139104

Аналогічно треба вчинити з двома іншими шуканими корінням:

• Відвести осередок C8 під другий корінь, ввести в неї початкове наближення 0,3, а в клітинку D8 ввести наступну формулу: = C8 ^ 3-0,01 * C8 ^ 2-0,7044 * C8 + 0,139104 
• Відвести осередок C10 під другий корінь, ввести в неї початкове наближення 0,7, а в клітинку D10 ввести наступну формулу: = C10 ^ 3-0,01 * C10 ^ 2-0,7044 * C10 + 0,139104

Результати виконаних дій наведені в таблиці:

значення х	значення у	Початкове наближення до застосування методу	значення функції
-1,00	-0,1665	-0,9	0,0360
-0,8	0,1842
-0,60	0,3421
-0,4	0,3553
-0,20	0,2716
0	0,1391
0,20	0,0058	0,3	-0,0461
0,4	-0,0803
0,60	-0,0711	0,7	-0,0159
0,8	0,0812
1,00	0,4247

Тепер можна переходити до знаходження першого кореня рівняння:

• Виберіть команду Підбір параметра. На екрані відобразиться діалогове вікно Підбір параметра. 
• В поле Встановити в осередку введіть посилання на комірку D2. У цьому полі дається посилання на осередок, в якій введена формула, що обчислює значення лівої частини рівняння. 
• Для знаходження кореня за допомогою підбору параметра рівняння треба уявити в такому вигляді, щоб його права частина не містила змінну. 
• У полі Значення введіть 0. Тут вказується значення з правої частини рівняння. 
• У полі Змінюючи значення осередків введіть C2. В даному полі наводиться посилання на осередок, відведену під змінну. Натисніть кнопку OK.

На екрані відображається вікно Результат підбору параметра. Крім того, розглядається засіб поміщає знайдене наближене значення кореня в клітинку C2. В даному випадку воно дорівнює -0,920. Аналогічно в осередках C8 і C10 знаходяться два залишилися кореня. Вони рівні 0,210 і 0,721.

Значення х	Значення у	Корень рівняння	Значення функції
-1,00	-0,1665	-0,920	0,00
-0,8	0,1842
-0,60	0,3421
-0,4	0,3553
-0,20	0,2716
0	0,1391
0,20	0,0058	0,210	0,00
0,4	-0,0803
0,60	-0,0711	0,721	0,00
0,8	0,0812
1,00	0,4247

Подбор параметра и поиск решения

Відкрийте робочу книгу "Поиск решения" і завантажте собі копію.

Результати практичної роботи:

Прізвище, ім'я	Оцінка
Похилова, Прошинская, Вересова	7
Познанский, Кильчанов	10
Цыганов, Федоренко, Иваненко	11
Ханишин, Скляр, Китаев	7
Кемарский	12
Натарова	?
Борисевич, Крячун	6
Кузнецова, Полухова, Лоик, Дударь	8
Брилевич, Ясманович	9
Петренко	11

Що таке метод перебору?

 За великим рахунком, можна виділити дві групи методів вирішення будь-яких завдань:

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

Існує клас задач, які легше вирішити методом перебору, ніж методом «наукового тику». Інструмент Підбір параметра допомагає вирішити задачу, коли відомо, що повинно вийти у відповіді, але не відомо, яке значення має мати одна з змінних. Говорячи простою математичною мовою, ми шукаємо рішення рівняння з одним невідомим.

Підбір параметра в Excel і приклади його використання

«Підбір параметра» - обмежений по функціоналу варіант надбудови «Пошук рішення». Це частина блоку завдань інструменту «Аналіз« Що-Якщо ".

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

Де знаходиться «Підбір параметра»?

Відомий результат якоїсь формули. Є також вхідні дані. Крім одного. Невідоме вхідний значення ми і будемо шукати. Розглянемо функцію «Підбір параметра» в Excel на прикладі.

Необхідно підібрати процентну ставку за позикою, якщо відома сума і термін. Заповнюємо таблицю вхідними даними.

Відсоткова ставка невідома, тому осередок порожня. Для розрахунку щомісячних платежів використовуємо функцію ПЛТ.

Коли умови задачі записані, переходимо на вкладку «Дані». «Робота з даними» - «Аналіз« Що-Якщо "-" Підбір параметра ».

В полі «Встановити в осередку» задаємо посилання на клітинку з розрахункової формули (B4). Поле «Значення» призначене для введення бажаного результату формули. У нашому прикладі це сума щомісячних платежів. Припустимо, -5 000 (щоб формула працювала правильно, ставимо знак «мінус», адже ці гроші будуть віддаватися). В поле «Змінюючи значення осередки» - абсолютне посилання на комірку з потрібним параметром ($ B $ 3).

Після натискання ОК на екрані з'явиться вікно результату.

Щоб зберегти, натискаємо ОК або Enter.

Функція «Підбір параметра» змінює значення в осередку В3 до тих пір, поки не отримає заданий користувачем результат формули, записаної в осередку В4. Команда видає тільки одне рішення задачі.

Рішення рівнянь методом підбору параметрів

Функція «Підбір параметра» ідеально підходить для вирішення рівнянь з одним невідомим.

Візьмемо для прикладу вираз: 

20 * х – 20 / х = 25. 

Аргумент х - шуканий параметр. Нехай функція допоможе вирішити рівняння підбором параметра і відобразить знайдене значення в осередку Е2.

У комірку Е3 введемо формулу:

= 20 * Е2 – 20 / Е2.

А в клітинку Е2 поставимо будь-яке число, яке знаходиться в області визначення функції. Нехай це буде 2.

Запускаємо інструмент і заповнюємо поля:

• «Встановити в осередку» - Е3 (осередок з формулою);
• «Значення» - 25 (результат рівняння);
• «Змінюючи значення осередки» - $ Е $ 2 (осередок, призначена для аргументу х).

Результат функції:

Знайдений аргумент відобразиться в зарезервованої для нього осередку.

Рішення рівняння: х = 1,80.

Функція «Підбір параметра» повертає в якості результату пошуку перше знайдене значення. Незалежно від того, скільки рівняння має рішень. Якщо, наприклад, в клітинку Е2 ми поставимо початкове число -2, то рішення буде іншим.

Функція «Підбір параметра» працює правильно, якщо:

• Значення бажаного результату виражено формулою;
• Всі формули написані повністю і без помилок.

Надбудова "Пошук рішення" і підбір декількох параметрів в Excel

Надбудова Excel «Пошук рішення» - це аналітичний інструмент, який дозволяє нам швидко і легко визначити, коли і який результат ми отримаємо при певних умовах. Можливості інструмента пошуку рішення набагато вище, ніж може надати «підбір параметра» в Excel.

Основні відмінності між пошуком рішення і підбором параметра:

• Підбір декількох параметрів в Excel.
• Накладення умов обмежують зміни в осередках, які містять змінні значення.
• Можливість використання в тих випадках, коли може бути багато рішень однієї задачі.

 Підключення надбудови «Пошук рішення»

В меню Office вибрати «Параметри Excel» і перейти на вкладку «Надбудови». Тут буде видно активні і неактивні, але доступні надбудови.

Якщо потрібна надбудова неактивна, то потрібно перейти за посиланням «Управління» (внизу таблички) та встановити надбудову. З'явиться діалогове вікно, в якому потрібно відзначити галочкою «Пошук рішення» і натиснути ОК.

Тепер на простенькій задачі розглянемо, як користуватися розширеними можливостями Excel.

Для нормальної роботи невеликого підприємства вистачить 4-6 робочих, 7-9 продавців, 2 менеджера, завідувач складом, бухгалтер, директор. Потрібно визначити їх оклади.

Обмеження: місячний фонд зарплати мінімальний; оклад робітника - не нижче прожиткового мінімуму в 100 доларів. Коефіцієнт А показує: у скільки разів оклад спеціаліста більше окладу робітника.

Таблиця з відомими параметрами:

• менеджер отримує на 30 доларів більше продавця (см. коефіцієнт В);
• завідувач складом - на 20 доларів більше робочого;
• директор - на 40 доларів більше менеджера;
• бухгалтер - на 10 доларів більше менеджера.

Знайдемо зарплату для кожного фахівця (на малюнку все зрозуміло).

Переходимо на вкладку «Дані» - «Аналіз» - «Пошук рішення» (так як ми додали надбудову, тепер вона доступна).

Заповнюємо меню. Щоб вводити обмеження, використовуємо кнопку «Додати». Рядок «Змінюючи осередки» повинна містити посилання на ті осередки, для яких програма буде шукати рішення. Заповнений варіант буде виглядати так:

Натискаємо кнопку «Виконати» і отримуємо результат:

Тепер ми знайдемо зарплату для всіх категорій працівників і порахуємо ФОП (Фонд Оплати Праці).

Анализ линий тренда. Коэффициент регрессии

Завантажте робочу книгу "Регрессионные модели" і скачайте собі копію.

Що таке апроксимація?

На практиці при моделюванні різних процесів - зокрема, економічних, фізичних, технічних, соціальних - широко використовуються ті чи інші способи обчислення наближених значень функцій за відомими їх значенням в деяких фіксованих точках.

  Такого роду завдання наближення функцій часто виникають:

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

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

Що таке лінія тренда?

Лінії тренда дозволяють наочно показати тенденції зміни даних і допомагають аналізувати завдання прогнозу. Такого типу аналіз також називається регресійний аналіз.

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

Вибір вірного типу лінії тренда для даних.

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

• прямі,
• логарифмічні,
• поліноміальні,
• статечні
• експоненціальні
• з лінійною фільтрацією.

Тип лінії тренду, який слід вибирати, визначається типом наявних даних.

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

Прямі лінії тренду

Пряма лінія тренда найкращим чином описує простий лінійний набір даних. Вона застосовується у випадках, коли точки даних розташовані близько до прямої. Інакше кажучи, пряма лінія тренда добре підходить для величини, яка зростає або убуває з постійною швидкістю.

У цьому прикладі пряма лінія тренда описує стабільне зростання продажів холодильників протягом 13 років. Зверніть увагу, що величина достовірності апроксимації дорівнює 0,979, що свідчить про хорошому збігу розрахункової лінії з даними.

Логарифмічні лінії тренду

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

У наступному прикладі логарифмічна лінія тренду описує прогнозоване зростання популяції тварин, що мешкають в ареалі з фіксованими межами. Швидкість зростання популяції падає через обмеженість життєвого простору для тварин. Крива досить добре описує дані: величина достовірності апроксимації дорівнює 0,933.

Поліноміальні лінії тренду

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

У наступному прикладі поліноміальна лінія тренда другого ступеня (один максимум) описує залежність витрати палива від швидкості руху. Близька до одиниці величина достовірності апроксимації (0,979) свідчить про хороше збігу кривої з даними.

Статечні лінії тренду

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

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

Експонентні лінії тренду

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

У наступному прикладі експонентна пряма тренду описує вміст радіоактивного вуглецю-14 в залежності від віку органічного об'єкта. Величина достовірності апроксимації дорівнює 0,990, що означає дуже гарне відповідність кривої даними.

Лінії тренда з лінійної фільтрацією

Використання лінії тренда з лінійної фільтрацією дозволяє згладити коливання даних і таким чином більш наочно показати характер залежності. Лінійний фільтр будується по певній кількості точок даних (воно задається параметром Точки). Елементи даних усереднюються, і отриманий результат використовується в якості точки лінії тренду. Так, якщо параметр Точки дорівнює 2, перша точка лінії тренда з лінійної фільтрацією визначається як середнє значення перших двох елементів даних, друга точка - як середнє другого і третього елементів, і так далі.

У наступному прикладі показана залежність числа продажів протягом 26 тижнів, отримана шляхом розрахунку змінного середнього.

Додавання лінії тренду

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

1. Клацніть діаграму.
2. Це показує кошти Робота з діаграмами, що включають вкладки Конструктор, Макет і Формат.
3. На вкладці Макет в групі Аналіз виберіть пункт Лінія тренда.
   

Виконайте одну з таких дій.

1. Виберіть відповідний зумовлений параметр лінії тренда.
2. ПРИМІТКА: Лінія тренда буде застосована без можливості вибору конкретних параметрів.
3. Натисніть Додаткові параметри лінії тренда, а потім в категорії Параметри лінії тренду в розділі Побудова лінії тренду (апроксимація і згладжування) виберіть потрібний тип лінії тренда.

Якщо обраний тип Поліноміальна, введіть в поле Ступінь найбільший ступінь для незалежної змінної.

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

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

Якщо лінія тренда додається до графіка, гистограмме, діаграмі з областями або лінійчатої діаграмі, вона обчислюється згідно допущенню, що значення X рівні 1, 2, 3, 4, 5, 6 і т. Д. Таке припущення робиться незалежно від того, чи є значення по осі X числовими або текстовими. Щоб обчислити лінію тренда на основі числових значень X, необхідно використовувати точкову діаграму.

Завдання додаткових параметрів лінії тренду

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

1. Клацніть діаграму.
2. Це показує кошти Робота з діаграмами, що включають вкладки Конструктор, Макет і Формат.
3. На вкладці Макет в групі Аналіз виберіть пункт Лінія тренда, а потім натисніть Додаткові параметри лінії тренда.
4. Щоб вказати число періодів для включення в прогноз, в розділі Прогноз вкажіть число періодів в поле вперед на або назад на.
5. Щоб показати на діаграмі рівняння лінії тренду, встановіть прапорець Показувати рівняння на діаграмі.
6. На вкладці Параметри лінії тренда встановіть прапорець Помістити на діаграму величину достовірності апроксимації (R ^ 2).