Команда Підбір параметра

За допомогою команди Підбір параметра (Goal Seek) меню Сервіс можна обчислити невідоме значення певного параметра, який задовольняє підсумкового бажаного результату Припустимо, потрібно дізнатися, яку максимальну позику вам за коштами взяти в банку, якщо термін погашення дорівнює 30 рокам, процентна ставка становить 6,5%, а розмір щомісячних виплат не перевищує $ 2000 Щоб відповісти на це питання, перш за все необхідно скласти формулу розрахунку і вирішити поставлене завдання при якому-небудь початковому значенні параметра Наприклад, на аркуші, зображеному на рис 1819, ми розрахували, що розмір позики в $ 500 000 не забезпечує дотримання умов, оскільки платити щомісяця доведеться більше ніж $ 2000

Рис 1819 Знайдемо максимальну величину позики при фіксованому розмірі щомісячних виплат за допомогою команди Підбір параметра

Тепер розглянемо процес роботи з командою Підбір параметра:

1 Перш за все слід присвоїти імена осередкам В1: В4 Для цього виділіть весь діапазон А1: В4, в меню Вставка виберіть команду Імя ► Створити Встановіть прапорець У стовпці Зліва (Left Column) і натисніть кнопку ОК

2 Виділіть клітинку з формулою, в даному випадку осередок В4

3 У меню Сервіс виберіть команду Підбір параметра (Goal Seek), щоб вивести на екран однойменне діалогове вікно, показане на рис 1820

Рис 1820 Щоб знайти рішення простого рівняння, заповніть поля діалогового вікна Підбір параметра

4 Залиште без зміни вміст поля Встановити в осередку (Set Cell) (Тут повинна знаходитися посилання на осередок, що містить формулу) У полі Значення (То Value) введіть очікувану величину результату, в даному випадку це -2000 (Знак мінус перед числом означає, що ви будете платити, а не отримувати)

5 У полі Змінюючи значення комірки (By Changing Cell) введіть посилання або клацніть на тій комірці робочого аркуша, в якій потрібно визначити значення для виконання умов завдання У нашому прикладі це комірка В1 Якщо осередкам призначені імена, то замість абсолютної посилання тут краще вказати імя комірки (в даному випадку позичка)

6 Натисніть кнопку ОК або клавішу Enter Відкриється діалогове вікно Результат підбору параметра (Goal Seek Status), показане на рис 1821 Шукана величина буде видна в комірці, визначеної раніше в поле Змінюючи значення клітинки Що нас цікавить відповідь на задачу – $ 316 422,64 – зявиться в комірці В1

7 Щоб прийняти відповідь і зберегти його в комірці В1, клацніть на кнопці 0ДО в діалоговому вікні Результат підбору параметра Якщо потрібно змінити умови завдання, натисніть кнопку Скасування (Cancel) і введіть нові опорні значення

Рис 1821 У діалоговому вікні Результат підбору параметра відображається інформація про результат пошуку рішення

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

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

Зауваження про точність і множинності рішень

Припустимо, на чистому аркуші ви ввели в комірку А1 формулу = А2 ^ 2, а потім скористалися командою Підбір параметра для знаходження значення в комірці А1, при якому значення в комірці А2 стане рівним 4 (Іншими словами, в діалоговому вікні Підбір параметра в полі Встановити в осередку знаходиться посилання $ А $ 1, в полі Значення – число 4 і посилання $ А $ 2 – в полі Змінюючи значення клітинки) Результат, продемонстрований на рис 1822, може вас кілька здивувати – найближче рішення, яке пропонує Excel для обчислення квадратного кореня з 4, одно 2,000023

Рис 1822 Результат, що повертається командою Підбір параметра, при пошуку квадратного кореня з чотирьох

Але це цілком природно виходячи з природи ітераційного процесу За замовчуванням виконувані обчислення припиняються, як тільки кількість ітерацій доходить до 100 або при отриманні результату з відносною похибкою 0,001 Якщо потрібна велика точність, потрібно змінити ці параметри, відкривши вкладку Обчислення (Calculation) діалогового вікна Параметри і встановивши нові значення в поля Граничне число ітерацій (Maximum Iteration) і Відносна похибка (Maximum Change)

ДИВИСЬ ТАКОЖ

Більш детальна інформація про параметри обчислень знаходиться в главі 12 «Створення формул»

І друге попередження Будьте уважні при роботі з командою Підбір параметра Справа в тому, що програма знаходить тільки одне рішення задачі і зупиняє обчислення, як тільки його виявляє Адже в розглянутому вище прикладі для числа 4 існує два рішення, два квадратних кореня: +2 і -2 У ситуаціях, подібних до цієї, доманда Підбір параметра пропонує рішення з тим же знаком, що і початкове значення Наприклад, якщо почати обчислення з числа -1, ми отримаємо в якості відповіді значення -1,999917, а не +2,000023

Джерело: Ефективна робота: Microsoft Office Excel 2003 / М Додж, К Стінсон – СПб: Питер, 2005 – 1088 с: ил

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*