Задачу пошуку параметра при накладаються граничних умовах допоможе вирішити спеціальна надбудова Microsoft Excel Пошук рішення.
2 Практична частина
2.1 Приклад рішення задач з використанням функції В«підбір параметра"
Як відомо, формули в Microsoft Excel дозволяють визначити значення функції з її аргументів. Проте може виникнути ситуація, коли значення функції відомо, а аргумент потрібно знайти (тобто вирішити рівняння). Для вирішення подібних проблем призначена спеціальна функція Підбір параметра. Малюнок 2
В
Малюнок 2 - Підбір параметра
Якщо в якості початкового значення в даному прикладі вказати -3, тоді буде знайдено друге рішення рівняння: -0,5.
2.2 Завдання: Аналіз суми виплат за вкладом
Робота із звичайними таблицями організована так: ввести дані, створити формули, отримати результат. Коли відомий результат, який потрібно одержати за допомогою обчислень за формулами, а вихідне значення, необхідне для отримання цього результату, невідомо, слід використовувати команду Підбір параметра, замість методу проб і помилок.
При виконанні процедури підбору параметра значення зазначеної осередку варіюються доти, поки залежна формулу не поверне шуканий результат. Процедуру підбору параметра слід використовувати для пошуку особливого значення окремої комірки, при якому інша осередок приймає відоме значення. Якщо формула осередку залежить від декількох величин, для пошуку оптимального набору значень при зміні декількох впливають осередків або при накладенні обмежень на одну або кілька клітинок, потрібно застосовувати для пошуку рішення команду Пошук рішення.
1. Потрібно створити нової лист з ім'ям Вклад. p> 2. У осередок В4 ввести текст Розмір вкладу, а в С4 його значення 150000р.
3. У осередок В6 ввести текст Термін вкладу, а в С6 його значення 20.
4. У осередок В8 ввести текст Процентна ставка, а в С8 його значення 5%. p> 5. У осередок В10 ввести текст Коефіцієнт нарощення, а в С10 формулу його обчислення = (1 + С8) ^ С6. p> 6. У осередок В13 ввести текст Сума виплат, а в С13 формулу його обчислення = С10 * С4.
В результаті отримуємо модель аналізу суми виплат за вкладом, за допомогою якої можна встановити, як впливають вихідні значення на кінцевий результат. Малюнок 3
В
Малюнок 3 - Аналіз суми виплат за вкладом
Використовуючи Підбір параметра можна спростити процес отримання необхідного результату:
Потрібно виділити осередок C13, яка містить формулу обчислення результату, і вибрати команду Підбір параметра меню Сервіс.
У полі Значення потрібно ввести цільове значення 500 000, а в полі Змінюючи значення комірки посилання на клітинку С4 і натиснути ОК. br/>В
Малюнок 4 - Вікно з результатами розрахунку
З'явиться вікно з результатами розрахунку, які після натискання кнопки ОК будуть внесені в таблицю. Малюнок 4. Як видно для отримання суми виплат до 500 000 руб. при 5% річних за 20 років потрібно покласти 188445 руб. Результат виконання завдання показаний на малюнку 5
В
Малюнок 5 - Результат виконання завдання
2.3 Завдання: Розрахунок розміру пенсійних накопичень
При обробці табличних даних часто виникає необхідність у прогнозуванні результату на основі відомих вихідних значень або, навпаки, у визначенні того, якими мають бути вихідні значення, що дозволяють отримати потрібний результат.
Використання засобу підбору параметра
Розглянемо, як працює засіб підбору параметра, що дозволяє визначити початкове значення, яке забезпечує заданий результат функції. Як приклад візьмемо таблицю, за допомогою якої розраховується розмір пенсійних накопичень (рис. 6).
В
Рис. 6 - Таблиця для розрахунку розміру пенсійних накопичень
У цій таблиці вказані вік, починаючи з якого в пенсійний фонд вносяться платежі (А2), величина щомісячного внеску (В2), період відрахувань, розрахований за формулою
= 60-А2
тобто передбачається, що мова йде про чоловікові, який вийде на пенсію в 60 років (С2), а також величина процентної ставки (D2).
Сума накопичень розраховується за допомогою функції за такою формулою:
= БС (D2; C2;-B2 * 12; 0; 1)
Функція БС () повертає майбутнє значення вкладу, яке визначається з урахуванням періодичних постійних платежів і постійної процентної ставки. Синтаксис цієї функції виглядає так:
БС (ставка; кпер: плата; нз: тип)
Аргументи функції: ставка - розмір процентної ставки за період; кпер - загальна кількість періодів виплат річний ренти; плата - виплата, вироблена в кожен період (це значення не може змінюватися протягом усього часу виплат), причому зазвичай плата складається з основного платежу і пл...
