Excel: рішення задач з підбором параметрів

РЕФЕРАТ

В« Excel : рішення задач з підбором параметрів В»

2010р.

Зміст

Введення

1 Теоретична частина

1.1 Підбір параметра

1.2 Пошук параметра

2 Практична частина

2.1 Приклад вирішення завдань з використанням функції "Підбір параметра"

2.2 Завдання: Аналіз суми виплат за вкладом

2.3 Завдання: Розрахунок розміру пенсійних накопичень

2.4 Застосування функції підбору параметра при роботі з діаграмами

2.5 Завдання: Обчислення радіусу описаного кола за трьома сторонами трикутника

Висновок

Список використовуваної літератури

Введення

Електронна таблиця, так само як і звичайна таблиця, являє собою набір числових і текстових даних, розміщених в осередках. Дані, що знаходяться в осередку електронної таблиці, можуть бути або введені користувачем, або визначені (обчислені) за даними з інших осередків. На основі вмісту електронних таблиць можуть створюватися діаграми, службовці ілюстрацією числової інформації.

За допомогою Ехсе1 можна створювати самі різні документи, виконувати самі різні завдання, наприклад:

* складати всілякі списки, звіти, відомості, бланки;

* оперативно виконувати обчислення різної складності;

* за даними таблиць будувати динамічно пов'язані з ними діаграми, графіки;

* вирішувати складні фінансові, економічні та математичні задачі і, в тому числі, завдання статистичного аналізу і т.д.

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

В даній роботі представлено опис функції підбору параметра і рішення задач за допомогою цієї функції.

1 Теоретична частина

1.1 Підбір параметра

Спеціальна функція підбір параметра дозволяє визначити параметр (Аргумент) функції, якщо відомо її значення. При підборі параметра значення впливаючою комірки (параметра) змінюється доти, поки формула, що залежить від цього осередку, не поверне задане значення.

Коли бажаний результат одиночної формули відомий, але невідомі значення, які потрібно ввести для отримання цього результату, можна скористатися засобом В«Підбір параметраВ» вибравши команду Підбір параметра в меню Сервіс. При підборі параметра Microsoft Excel змінює значення в одній конкретній комірці до тих пір, поки формула, залежна від цієї осередку, не повертає потрібний результат.

Рисунок 1 - Зміна процентної ставки

Наприклад, засіб В«Підбір параметраВ» використовується для зміни процентної ставки в комірці B3 в бік збільшення до тих пір, поки розмір платежу в комірці B4 не стане дорівнює 900,00 р. Малюнок 1

1.2 Пошук параметра

Можна розглянути процедуру пошуку параметра на простому прикладі: потрібно вирішити рівняння 10 * x - 10/x = 15. Тут параметр (аргумент) - x. Нехай це буде комірка A3. Потрібно ввести в цю комірку будь-яке число, лежаче в області визначення функції (в даному прикладі це число не може бути дорівнює нулю). Це значення буде використано в якості початкового. Нехай це буде 3. Потрібно ввести формулу = 10 * A3-10/A3, по якій повинно бути отримано необхідну значення, в яку комірку, наприклад, B3. Тепер можна запустити функцію пошуку параметра, вибравши команду Підбір параметра в меню Сервіс. Вводяться параметри пошуку:

В· В поле Встановити в комірці ввести посилання на клітинку, що містить необхідну формулу.

В· Ввести шуканий результат у полі Значення.

В· В полі Змінюючи значення комірки ввести посилання на клітинку, що містить підбирати значення.

В· Клікнути на клавіші OK.

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

Досить складно правильно визначити найбільш підходяще початкове значення. Частіше можна зробити які-небудь припущення про шуканий параметр, наприклад, параметр повинен бути цілим (тоді отримуємо перше рішення нашого рівняння) або непозитивно (друге рішення).

Завдання пошуку параметра при накладаються граничних умовах допоможе вирішити спеціальна надбудова Microsoft Excel Пошук рішення.

2 Практична частина

2.1 Приклад вирішення завдань з використанням функції "підбір параметра"

Як відомо, формули в Microsoft Excel дозволяють визначити значення функції з її аргументів. Однак може виникнути ситуація, коли значення функції відомо, а аргумент потрібно знайти (тобто розв'язати рівняння). Для вирішення подібних проблем призначена спеціальна функція Підбір параметра. Малюнок 2

Малюнок 2 - Підбір параметра

Якщо в якості початкового значення в даному прикладі вказати -3, тоді буде знайдено друге рішення рівняння: -0,5.

2.2 Завдання: Аналіз суми виплат за вкладом

Робота зі звичайними таблицями організована так: ввести дані, створити формули, отримати результат. Коли відомий результат, який потрібно отримати з допомогою обчислень за формулами, а вихідне значення, необхідне для отримання цього результату, невідомо, слід використовувати команду Підбір параметра, замість методу проб і помилок.

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

1. Потрібно створити нової лист з ім'ям Внесок.

2. В клітинку В4 ввести текст Розмір вкладу, а в С4 його значення 150000р.

3. До комірки В6 ввести текст Термін вкладу, а в С6 його значення 20.

4. До комірки В8 ввести текст Процентна ставка, а в С8 його значення 5%.

5. До комірки В10 ввести текст Коефіцієнт нарощення, а в С10 формулу його обчислення = (1 + С8) ^ С6.

6. До комірки В13 ввести текст Сума виплат, а в С13 формулу його обчислення = С10 * С4.

В результаті отримуємо модель аналізу суми виплат за вкладом, з допомогою якої можна встановити, як впливають вихідні значення на кінцевий результат. Малюнок 3

Малюнок 3 - Аналіз суми виплат за вкладом

Використовуючи Підбір параметра можна спростити процес отримання необхідного результату:

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

У полі Значення потрібно ввести цільове значення 500 000, а в полі Змінюючи значення комірки посилання на комірку С4 і натиснути ОК.

Малюнок 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)

Функція БС () повертає майбутнє значення вкладу, визначуване з урахуванням періодичних постійних платежів і постійної процентної ставки. Синтаксис цієї функції виглядає так:

БС (ставка; кпер: плата; нз: тип)

Аргументи функції: ставка - розмір процентної ставки за період; кпер - загальне число періодів виплат річної ренти; плата - виплата, вироблена в кожен період (це значення не може змінюватися на Протягом всього часу виплат), причому зазвичай плата складається з основного платежу і платежу по відсотках; нз - поточна вартість або загальна сума всіх майбутніх платежів, починаючи з теперішнього моменту (за замовчуванням - 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, місти...ть величину боку а (якщо клацнути мишею по цій комірці, то в поле введення виявиться адресу $ В). Після натискання кнопки "ОК" виводиться нове вікно "Результати підбору параметра". Якщо збільшити розрядність числа в осередку B9, то можна побачити, що R досягло значення 2.9999172. При цьому а = 1.515753171.

Результати виконання показані на малюнку 11

Малюнок 11 - Обчислення радіусу описаного кола за трьома сторонами трикутника з допомогою підбору параметра

Єдине Чи значення a відповідає R = 3? На це питання викладений метод підбору параметра не дає відповіді.

Висновок

Список використовуваної літератури

1. Довідка MS Excel

2. Microsoft Office Excel 2003. Навчальний курс/В. Кузьмін, - СПб.: Пітер: Видавнича група BHV, 2004. - 493 с.

3. Excel 2003. Ефективний самовчитель/В.В. Серогородскій, - СПб.: Наука і техніка, 2005. - 400 с.

4. Excel: Збірник прикладів і задач/С.М. Лавренов, - М.: Фінанси і статистика, 2003. - 336 с.