1 of 28

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

2-ге видання, оновлене

Урок 14

Інформатика

teach-inf.com.ua

за підручником

Бондаренко О.О. та ін.

Рівень стандарту 10(11)

2 of 28

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

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

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

Розділ 2

§ 14

3 of 28

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

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

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

1

2

3

Розділ 2

§ 14

4 of 28

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

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

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

х² + Зх - 8 = 0

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

Розділ 2

§ 14

5 of 28

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

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

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

Розділ 2

§ 14

6 of 28

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

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

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

1

2

3

Розділ 2

§ 14

7 of 28

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

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

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

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

Розділ 2

§ 14

8 of 28

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

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

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

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

Розділ 2

§ 14

9 of 28

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

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

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

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

Розділ 2

§ 14

10 of 28

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

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

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

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

(х = 1,701562) вже знайдено.

Розділ 2

§ 14

11 of 28

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

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

Для того щоб знайти корінь на інтервалі [-5;-4,5], виконаємо такі дії:

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

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

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

Розділ 2

§ 14

12 of 28

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

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

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

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

Розділ 2

§ 14

13 of 28

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

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

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

Розділ 2

§ 14

14 of 28

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

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

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

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

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

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

Розділ 2

§ 14

15 of 28

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

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

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

Розділ 2

§ 14

16 of 28

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

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

Розділ 2

§ 14

17 of 28

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

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

Розділ 2

§ 14

18 of 28

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

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

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

Розділ 2

§ 14

19 of 28

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

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

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

Розділ 2

§ 14

20 of 28

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

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

Розділ 2

§ 14

21 of 28

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

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

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

Розділ 2

§ 14

22 of 28

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

Отже, Підбір параметра дозволяє швидко і з достатньою точністю

розв’язувати рівняння; знаходити значення аргумента, при якому буде

отримане потрібне значення функції.

Доходимо висновку: потрібно встановити роздрібну ціну 28,11 грн.

Розділ 2

§ 14

23 of 28

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

Параметр

Розділ 2

§ 14

24 of 28

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

Депозит

Розділ 2

§ 14

25 of 28

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

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

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

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

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

Розділ 2

§ 14

26 of 28

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

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

§ 14, с. 78-82

Розділ 2

§ 14

27 of 28

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

Сторінка

82

Розділ 2

§ 14

28 of 28

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

2-ге видання, оновлене

Урок 14

Інформатика

teach-inf.com.ua

за підручником

Бондаренко О.О. та ін.

Рівень стандарту 10(11)