1 of 28

Розв’язування задач на підбір параметра

За навчальною програмою 2018 року

Урок 14

10

2 of 28

Розв'язування задач�на підбір параметра

Розділ 2 § 14

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

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

10

3 of 28

Розв'язування задач�на підбір параметра

Розділ 2 § 14

Команда Підбір параметра (група Аналіз «якщо»,) дозволяє отримати необхідне значення в певній комірці, яку називають цільовою, шляхом зміни значення (параметра) іншої комірки, яку називають впливаючою.

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

1

2

3

10

4 of 28

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

Розділ 2 § 14

Цей інструмент застосовують для аналізу даних з одною невідомою умовою.

Розглянемо рівняння

х² + Зх — 8 = 0

Відомо значення функції (у = 0); треба знайти значення аргумента х, при якому ми отримаємо цей результат. З математики нам відомий алгоритм пошуку коренів квадратного рівняння, але засіб Пошук параметра діє методом підбору: підставляє замість х різні значення і аналізує, наскільки результат обчислень відрізняється від умов, зазначених в параметрах інструмента. Щойно буде досягнуто результат обчислення з потрібною точністю, процес підбору припиняється.

10

5 of 28

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

Розділ 2 § 14

Розв'яжемо рівняння за допомогою засобу Підбір параметра.

Заповнимо комірки аркуша так, як показано на рисунку. Клітинка В2 — цільова. У комірці параметра А2 потрібно підібрати таке значення, при якому в цільовій комірці отримаємо значення 0.

10

6 of 28

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

Розділ 2 § 14

(Продовження…) Розв'яжемо рівняння за допомогою засобу Підбір параметра:

Виберемо комірку В2 і виберемо команду Дані ⇒ Аналіз «якщо» ⇒ Підбір параметра.

1

2

3

10

7 of 28

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

Розділ 2 § 14

(Продовження…) Розв'яжемо рівняння за допомогою засобу Підбір параметра:

Заповнимо поля вікна Підбір параметра значеннями згідно з рисунком і натиснемо ОК.

У комірці А2 з'явиться значення х = 1,701562. Вікно Результат підбору параметра повідомляє, що рішення знайдено.

10

8 of 28

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

Розділ 2 § 14

(Продовження…) Розв'яжемо рівняння за допомогою засобу Підбір параметра:

Зверніть увагу на те, як змінилося значення в цільовій комірці: 7,0359 * 10^-19 — це максимально наближений до 0 результат.

Але ми знаємо, що при D > 0 квадратне рівняння має 2 корені. Для того щоб знайти другий корінь, слід у комірку А2 ввести його наближене значення і повторити підбір.

10

9 of 28

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

Розділ 2 § 14

(Продовження…) Розв'яжемо рівняння за допомогою засобу Підбір параметра:

Але як вибрати наближене значення?

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

10

10 of 28

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

Розділ 2 § 14

(Продовження…) Розв'яжемо рівняння за допомогою засобу Підбір параметра:

Складемо таблицю значень функції f(x) = х² = Зх — 8 на інтервалі [—5;5] з кроком 0,5:

Із таблиці видно, що функція змінює знак на інтервалах [—5;—4,5] і [1,5:2], що означає, що в кожному з цих інтервалів є корінь. Корінь з інтервалу [1,5:2] (х = 1,701562) вже знайдено.

10

11 of 28

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

Розділ 2 § 14

(Продовження…) Розв'яжемо рівняння за допомогою засобу Підбір параметра:

Щоб знайти корінь на інтервалі [—5;—4,5], виконаємо такі дії:

введемо в комірку A3 значення, що є наближенням до шуканого кореня. За початкове наближення до кореня доцільно взяти середню точку відрізка -4,75;

скопіюємо формулу з комірки В2 в комірку ВЗ;

за допомогою Підбору параметра встановимо в цільовій комірці ВЗ нульове значення, зміняючи значення комірки A3.

10

12 of 28

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

Розділ 2 § 14

(Продовження…) Розв'яжемо рівняння за допомогою засобу Підбір параметра:

Отримаємо другий корінь і значення для оцінювання результату:

X	F(x)
-4,70157	4,51E-05

10

13 of 28

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

Розділ 2 § 14

(Продовження…) Розв'яжемо рівняння за допомогою засобу Підбір параметра:

За замовчуванням інструмент виконує 100 повторень (ітерацій) з точністю 0.001. Якщо потрібно збільшити кількість повторень або підвищити точність обчислення, змініть налаштування, виконавiи команду Файл ⇒ Параметри ⇒ Формули ⇒ Параметри обчислень.

10

14 of 28

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

Розділ 2 § 14

Таким чином, якщо нас не влаштовує результат обчислень, можна:

збільшити в налаштуваннях параметр граничного числа ітерацій;

змінити відносну похибку;

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

Використовуючи ці способи налаштувань, можна істотно полегшити і прискорити процес пошуку максимально точного розв'язку.

10

15 of 28

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

Розділ 2 § 14

Підбір параметра для банківських депозитів.

Клієнт банку планує вносити гроші на банківський депозитний рахунок щорічно й однаковими частинами. Яким має бути розмір щорічного внеску, щоб за 10 років зібрати 20 000 грн при 5% річних? Скористаємося інструментом Підбір параметра.

10

16 of 28

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

Розділ 2 § 14

Складемо таблицю. У комірку В4 введемо формулу: =FV(B1;B2;B3).

10

17 of 28

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

Розділ 2 § 14

Виберемо комірку В4 і виконаємо команду Дані ⇒ Аналіз «якщо» ⇒ Підбір параметра, задавши значення 20 000, і ввівши в поле Змінюючи значення комірки посилання на комірку ВЗ.

10

18 of 28

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

Розділ 2 § 14

У комірці ВЗ отримано від'ємний результат, що відповідає стандарту фінансових функцій Excel:

регулярні внески задаються від'ємними значеннями, оскільки належать до категорії видаткових операцій. А по закінченню 10 років ми отримаємо на рахунку +20 000 грн.

10

19 of 28

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

Розділ 2 § 14

Підбір параметра для встановлення роздрібної ціни.

Виробник планує продати продукт у кількості 10 000 шт. Собівартість 1 одиниці продукту 20,5 грн. Витрати на реалізацію продукту: 20 000 грн. Яку потрібно встановити роздрібну ціну, щоб рентабельність виробництва зберігалася на рівні 20%?

10

20 of 28

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

Розділ 2 § 14

Заповнимо таблицю даними і формулами.

10

21 of 28

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

Розділ 2 § 14

Відформатуємо клітинки В2, ВЗ:В7 грошовим форматом, В8 — відсотковим.

Виберемо комірку В8 і виконаємо команду Дані ⇒ Аналіз «якщо» ⇒ Підбір параметра, задавши значення 20%, і ввівши в поле Змінюючи значення комірки посилання на комірку В1.

10

22 of 28

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

Розділ 2 § 14

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

10

23 of 28

Розгадайте ребус

Параметр

Розділ 2 § 14

10

24 of 28

Розгадайте ребус

Депозит

Розділ 2 § 14

10

25 of 28

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

Розділ 2 § 14

У якому випадку застосовується надбудова Підбір параметра?

Поясніть схему розв'язування лінійного рівняння за допомогою засобу Підбір параметра.

Поясніть схему розв'язування квадратного рівняння за допомогою засобу Підбір параметра.

Як підвищити точність обчислення невідомої величини за допомогою засобу Підбір параметра?

10

26 of 28

Домашнє завдання

Проаналізувати

§ 14, ст. 78-82

Розділ 2 § 14

10

27 of 28

Працюємо за комп’ютером

Розділ 2 § 14

Сторінка

82

10

28 of 28

Дякую за увагу!

За навчальною програмою 2018 року

Урок 14

10