Розв’язання задачі за допомогою Диспетчера сценаріїв

Розв'язання задачі на функцію Підбір параметра

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

Завдання. Клієнт бажає відкрити рахунок у комерційному банку під 10% відсотків річних і накопичити на цьому рахунку за п'ять років суму 55 000 руб. Клієнт згоден проводити щорічні відрахування на свій рахунок у банку. Необхідно дізнатися про розмір цих щорічних відрахувань.

1. Створіть таблицю за зразком (Мал. 28)

Мал. 28. Вихідна таблиця

2. Встановіть курсор у коміркуС6 та виконайте командуВставка – Функція.

4. Заповніть параметри зразка (Мал. 29).

Мал. 29. Параметри функції БС

5. НатиснітьОК.

6. Тепер можна скористатись командою Підбір параметра для вирішення задачі. Виконайте командуСервіс - Підбір параметра. На екрані з'являється вікно, яке заповнюємо наступним чином:

Мал. 30. Параметри функції Вибір параметра

7. Після натискання кнопкиОК ви отримаєте діалогове вікноРезультат підбору параметра, що містить короткий звіт про результати виконаних ітераційних обчислень. Натиснувши кнопкуОК, ви збережете результати обчислень у комірках робочого листа.

Відповідь повинна вийти негативною, що говорить про те, що клієнт повинен періодично віддавати гроші банку в розмірі 9008 руб.

Розглянемо другий приклад. Клієнт відкрив рахунок у банку та вніс початкову суму 1000 руб. Потрібно визначити, який час розмір суми збільшиться до 1500 крб., якщо відсоткову ставку на місяцьскладає 5%.

1. Запустіть EXCEL та створіть таблицю за зразком (Мал. 31).

Мал. 31. Вихідна таблиця

2. У коміркуD1 помістіть розмір початкової суми - 1000 руб. Спочатку визначимо розмір накопиченої суми за 1 місяць. У коміркуD4 введіть формулу, за якою визначається накопичена сума:=D1*(1+D2/100)^D3.

Після введення формули в комірці буде видно результат, а у рядку формул буде відображено формулу (Рис. 32).

Мал. 32. Виконаний розрахунок

3. Виконайте командуСервіс - Вибір параметра. Заповніть параметри функції Підбір параметра таким чином:

Мал. 33. Параметри функції Вибір параметра

4. НатиснітьОК та отримайте остаточний результат (Мал. 34).

Мал. 34. Отриманий результат

Отже, через 8,3 місяці на рахунку накопичиться потрібна сума.

Розв'язання задачі за допомогою Диспетчера сценаріїв.

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

Завдання. Фірма хоче взяти позику у банку у вигляді 1 500 000 крб. під 92% річних на 10 років. Визначити розмір щомісячних виплат та загальну суму виплат. Після цього проаналізувати, як впливає зміна процентної ставки та строку кредиту на щомісячні виплати.

1. Створіть таблицю такого виду:

Мал. 35. Таблиця до завдання про позику

2. Встановіть курсор у коміркуС6.

3. Вставте в коміркуС6 функціюПЛТ та заповніть її параметри (Мал. 36). Так як платежі повинні бути щомісячними, то коміркуС4 треба розділити на 12, а коміркуС5 помножити на 12. У поліПС коміркуС3 > вказали з мінусом, щоб результат функції був негативним.

Мал. 36. Параметри функції ПЛТ

4. У коміркуС8 введіть формулу=C5*C6*12.

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

1. Створимо три сценарії. Щоб створити сценарій, виберітьСервіс - Сценарії.

2. Щоб створити перший сценарій, натисніть кнопкуДодати. Ви побачите вікно діалогуДодати сценарій. Тут потрібно ввести назву майбутнього сценарію і вказати комірки, що змінюються. Введіть ім'я, що пояснює призначення сценарію -Мінімум, оскільки перший сценарій передбачає мінімальний кредит.

3. У поліЗмінні комірки вкажіть діапазон$C$3:$C$5. Тобто. усі вихідні дані можуть змінюватися.

4. Захистимо сценарій від змін. ПрапорецьЗаборонити зміни означає, що ніхто, крім вас, не зможе змінити сценарій.Приховати означає, що він не з'явиться у списку сценаріїв. У результаті у вас будуть задані всі параметри, потрібні для розрахунку (Мал. 37).

Мал. 37. Сценарій Мінімум

• осередокС3 - 1 000 000
• осередокС4 - 0,05
• осередокС5 - 20.

Тобто. вікно матиме такий вигляд:

Мал. 38. Значення осередків сценарію Мінімум

6. Натисніть кнопкуОК. Ви повернетеся до вихідного вікна.

7. Щоб створити наступний сценарій, натиснітьДодати. З'явиться діалогове вікноДодавання сценарію.

8. Введіть назвуБажані значення і натиснітьОК. У вікні діалогуЗначення осередків сценарію потрібно ввести нові значення:

• осередокС3 - 1 500 000
• осередокС4 - 0,1
• осередокС5 - 15.

9. Створіть третій сценарій з ім'ямМаксимум. Для нього вкажіть такі дані:

• осередокС3 - 2 000 000
• осередокС4 - 0,05
• осередокС5 - 10.

10. Після останнього набору чисел потрібно клацнути на кнопціОК, щоб повернути на екран вікно Диспетчер сценаріїв, що на цей раз містить список із трьох створених сценаріїв.

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

13. Створивши звіти за сценаріями, можна порівняти змінні та результати на одному робочому аркуші. Для цього викличте Менеджер сценаріїв (Сервіс – Сценарії ).

14. НатиснітьЗвіт. У вікні встановіть прапорецьСтруктура і клацнітьОК (Мал. 39).

Мал. 39. Створення звіту за сценаріями

15. Після завершення діалогу EXCEL створить новий робочий листСтруктура сценарію і надасть звіт, який можна роздрукувати та проаналізувати (Рис. 40).

---