видно для отримання суми виплат у 500 000 руб. при 5% річних за 20 років потрібно покласти 188445 крб. Результат виконання завдання показаний на малюнку 5
Малюнок 5 - Результат виконання завдання
2.3 Завдання: Розрахунок розміру пенсійних накопичень
При обробці табличних даних часто виникає необхідність у прогнозуванні результату на основі відомих вихідних значень або, навпаки, у визначенні того, якими мають бути вихідні значення, що дозволяють отримати потрібний результат.
Використання засобу підбору параметра
Розглянемо, як працює засіб підбору параметра, що дозволяє визначити початкове значення, яке забезпечує заданий результат функції. Як приклад візьмемо таблицю, за допомогою якої розраховується розмір пенсійних накопичень (рис. 6).
Рис. 6 - Таблиця для розрахунку розміру пенсійних накопичень
У цій таблиці вказані вік, починаючи з якого в пенсійний фонд вносяться платежі (А2), величина щомісячного внеску (В2), період відрахувань, розрахований за формулою
= 60-А2
тобто передбачається, що мова йде про чоловіка, який вийде на пенсію в 60 років (С2), а також величина відсоткової ставки (D2).
Сума накопичень розраховується за допомогою функції за наступною формулою:
= БС (D2; C2;-B2 * 12; 0; 1)
Функція БС () повертає майбутнє значення вкладу, визначуване з урахуванням періодичних постійних платежів і постійної процентної ставки. Синтаксис цієї функції виглядає так:
БС (ставка; кпер: плата; нз: тип)
Аргументи функції: ставка - розмір процентної ставки за період; кпер - загальне число періодів виплат річної ренти; плата - виплата, вироблена в кожен період (це значення не може змінюватися на Протягом всього часу виплат), причому зазвичай плата складається з основного платежу і платежу по відсотках; нз - поточна вартість або загальна сума всіх майбутніх платежів, починаючи з теперішнього моменту (за замовчуванням - 0); тип - число, яке визначає, коли повинна проводитися виплата (0 - в кінці періоду, задається за замовчуванням, 1 - на початку періоду).
Формула має такий вигляд, оскільки передбачається, що відсотки нараховуються не щомісячно, а на початку, наступного року за попередній рік. Припустимо, необхідно визначити, в якому віці майбутньому пенсіонерові треба починати виплати, щоб потім отримати надбавку до пенсії в розмірі 1000 руб. Для цього слід виділити вічко, відведену для представлення результату (у даному випадку F2), і викликати команду "Сервіс В»Підбір параметра". Коли з'явиться діалогове вікно Підбір параметра (рис. 7), адресу виділеної комірки буде автоматично вставлений в полі "Установить в осередку ". Потрібно вказати в полі "Значення" цільове значення - 1000. Потрібно помістити курсор введення в поле "значение ячейки" і виділити комірку А2, після чого її адресу відобразиться у вказаному полі.
Рис. 7 Діалогове вікно "Підбір параметра" з заданими параметрами
Примітка: При використанні функції підбору параметра необхідно, щоб осередок з цільовим значенням містила посилання на комірку із змінним значенням.
Після виконання всіх установок потрібно натиснути кнопку К, і пошук потрібного значення буде розпочато. Результат обчислення відобразиться в діалоговому вікні "Результат підбору параметра", а також у вихідній таблиці (рис, 8). Після натискання кнопки 0К отримані значення будуть вставлені в таблицю.
Рис. 8 - Результати підбору параметра
Якщо пошук потрібного значення триває занадто довго, перервати його на час можна за допомогою кнопки "Пауза". Кнопка "Крок" дозволяє переглянути проміжні результати обчислення.
Підбір параметра
1. Вибрати цільову комірку, тобто осередок з формулою, результат якої потрібно підібрати.
2. Викликати команду "СервісВ» Підбір параметра ". В поле "Встановити в комірці" діалогового вікна буде відображатися адресу цільової комірки.
3. Задати у полі "Значення" значення, яке повинна містити цільова осередок
4. Вказати в полі "Змінюючи значення комірки" адресу осередку, значення якої необхідно встановити таким, щоб у цільової осередку отримати задане значення.
5. Натиснути кнопку ОК, і потрібний параметр буде підібраний в діалоговому вікні "Результат підбору параметра". По закінченні цього процесу в ньому відобразяться результати.
6. Натиснути кнопку ОК, якщо ви хочете замінити значення осередках на робочому листі новими, або кнопку "Скасувати" в противному випадку.
2.4 Застосування функції підбору параметра при роботі з діаграмами
Засіб підбору параметра застосовується і при роботі з діаграмами. Як це робиться, показано в наступному прикладі.
На підставі даних про суми виручки від продажу виробів в три регіони потрібно побудувати за допомогою майстра діаграм гістограму (Рис. 8) В інтерактивному режимі з використанням миші потрібно налаштувати висоту смуги В«СереднєВ», і подивитися, як Ехсе1 змінить висоту смуги «ѳверськВ» для отримання цільового значення. Далі необхідно двічі клацнути на останній смузі (один раз - для вибору ряду, а другий - для вибору смуги з ряду), яка представляє середні значення, і збільшити її висоту шляхом перетягування маркерів розмірів.
Коли кнопка миші буде відпущена, відкриється діалогове вікно "Підбір параметра. У полі "Встановити в комірці" з'явиться ім'я комірки В5, а в поле "Значення" - число, відповідне останньому значенню, яке відображалося в поле підказки. Курсор уведення буде знаходитися в полі "Змінюючи значення комірки ", тому залишається лише ввести в дане поле значення В2. Потрібно клацнути на кнопці ОК, після чого з'явиться діалогове вікно "Результат підбору параметра ", в якому буде міститися потрібна інформація. Клацнути на кнопці ОК для повернення на робочий лист. В осередках вже містяться нові значення і що відповідно до них налаштована висота смуг гістограми (рис. 9).
Малюнок 8 - Гістограма "В иручка від продажу виробів в трьох регіонах"
Малюнок 9 - Гістограма "Виручка від продажу виробів в трьох регіонах" c застосуванням підбору параметрів
2.5 Завдання: Обчислення радіусу описаного кола за трьома сторонами трикутника з допомогою підбору параметра
Обчислюємо радіус описаного кола R за формулою:
= a * b * c_/(4 * S)
де а = 2, b = 4, з_ = 5 - сторони трикутника
S - площа трикутника
Площа трикутника S обчислюється за формулою Герона:
= КОРІНЬ (p * (p-a) * (p-b) * (p-c_))
де а = 2, b = 4, з_ = 5 - сторони трикутника
р - напівпериметр трикутника
напівпериметр трикутника p обчислюємо за формулою:
= (a + b + c_)/2
Приклад обчислення радіусу описаного кола в програмі Excel показаний на малюнку 10
Малюнок 10 - Обчислення радіусу описаного кола за трьома сторонами трикутника
Отже, радіус описаного кола R по трьом сторонам трикутника а, b, с обчислений. Якщо зафіксувати довжини сторін b і с, то можна вважати, що R обчислюється як функція а. Але Ехсе1 дає можливість вирішити зворотну задачу: по заданому R обчислити а. При цьому не треба вирішувати вручну громіздку задачу відшукання а як функції R. Формул на робочому листі для цієї мети цілком достатньо. Наприклад, треба визначити величину а при R = 3. Для це потрібно виділити осередок В9, в якій обчислюється R. У меню потрібно вибрати "Сервіс/Підбір параметра". Виводиться діалогове вікно "Підбір параметра ". Поле "Встановити в комірці:" вже містить адресу виділеної комірки B9. У полі "Значення:" потрібно ввести 3. У полі "Змінюючи значення комірки:" потрібно ввести адресу комірки В2, місти...
