Решете проблем с помощта на диспечера на сценарии

Решение на проблема за функцията Избор на параметър

Ако стойността на функция може да бъде променена само чрез промяна на стойността на един параметър, тогава необходимата стойност на този параметър може да бъде намерена с помощта на функциятаИзбор на параметър.

Задача. Клиентът иска да открие сметка в търговска банка при 10% годишно и да натрупа по тази сметка за пет години сумата от 55 000 рубли. Клиентът се съгласява да прави годишни удръжки по банковата си сметка. Трябва да знаете размера на тези годишни удръжки.

1. Създайте таблица според образеца (фиг. 28)

Ориз. 28. Начална таблица

2. Поставете курсора в клеткаC6 и изпълнете командатаВмъкване - Функция.

4. Попълнете параметрите на функцията по образец (фиг. 29).

Ориз. 29. Функционални параметри на BS

5. НатиснетеОК.

6. Сега можете да използвате командата за избор на параметър, за да разрешите проблема. Изпълняваме командатаУслуга - Избор на параметри. На екрана се появява прозорец, който попълваме по следния начин:

Ориз. 30. Параметри на функцията Избор на параметри

7. След натискане на бутонаОК ще получите диалогов прозорецРезултат от избора на параметър, съдържащ кратък отчет за резултатите от извършените итеративни изчисления. С щракване върху бутонаOK записвате резултатите от изчислението в клетките на работния лист.

Отговорът трябва да се окаже отрицателен, което означава, че клиентът трябва периодично да дава пари на банката в размер на 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 предоставяScript Manager за този случай. Тя ви позволява да показвате всички получени резултати. Всеки набор от променливи се записва като скрипт и резултатът може да се види, като изберете желания скрипт от списъка. Освен това можете да създадете отчет, който комбинира всички сценарии.

Задача. Фирмата иска да вземе заем от банка в размер на 1 500 000 рубли. при 9,2% годишно за 10 години. Определете размера на месечните плащания и общата сума на плащанията. След това анализирайте как промените в лихвения процент и срока на заема влияят върху месечните плащания.

1. Създайте таблица като тази:

Ориз. 35. Таблица за проблема на заема

2. Поставете курсора в клеткаС6.

3. Въведете функциятаPMT в клеткаС6 и попълнете нейните параметри (фиг. 36). Тъй като плащанията трябва да са месечни, клеткаС4 трябва да се раздели на 12, а клеткаС5 да се умножи по 12. В полетоPS клеткаС3 беше обозначена с минус, така че резултатът от функцията да не е отрицателен.

Ориз. 36. Параметри на функцията PMT

4. В клеткаC8 въведете формулата=C5*C6*12.

Нека анализираме какво се случва, ако лихвеният процент намалее, или кредитът е теглен за по-малък брой години или е на по-малка сума? Как това ще се отрази на месечните плащания и общата сума?

1. Нека създадем три сценария. За да създадете скрипт, изберете командатаИнструменти - Скриптове.

2. За да създадете първия скрипт, щракнете върху бутонаДобавяне на скрипт. Ще видите диалоговия прозорецДобавяне на скрипт. Тук трябва да въведете името на бъдещия сценарий и да посочите клетките, които да промените. Посочете име, за да обясните целта на сценария -Минимум, тъй като първият сценарий предполага минимален кредит.

3. В полетоChangeable Cells въведете диапазона$C$3:$C$5. Тези. всички въведени данни подлежат на промяна.

4. Защитете скрипта от промени. КвадратчетоЗабрани промените означава, че никой освен вас не може да промени скрипта.Скриване означава, че няма да се появи в списъка със скриптове. В резултат на това ще имате всички параметри, необходими за изчислението (Фиг. 37).

Ориз. 37. Сценарий минимум

• клеткаC3 - 1 000 000
• клеткаС4 – 0.05
• клеткаС5 - 20.

Тези. прозорецът ще изглежда така:

Ориз. 38. Минимални стойности на клетката на сценария

6. Натиснете бутонаOK. Ще се върнете към оригиналния прозорец.

7. За да създадете следващия скрипт, щракнете върху бутонаДобави. Ще се появи диалоговият прозорецДобавяне на скрипт.

8. Въведете иметоЖелани стойности и щракнете върхуОК. В диалоговия прозорец на сценарияCell Values трябва да въведете нови стойности:

• клеткаC3 - 1 500 000
• клеткаС4 – 0.1
• клеткаC5 - 15.

9. Създайте трети сценарий с име -Максимум. Предоставете следната информация за него:

• клеткаC3 - 2 000 000
• клеткаС4 – 0.05
• клеткаС5 - 10.

10. След последния набор от числа щракнете върху бутонаOK, за да върнете прозореца на диспечера на сценарии, този път изброявайки трите създадени от вас скрипта.

11. Създадените скриптове се записват с работния лист, за да видите как въведените стойности влияят на резултата, в прозореца на Script Managerщракнете върху необходимия скрипт и след това върху бутонаOutput. Направете това за всеки сценарий.

13. Чрез създаване на отчети за сценарии можете да сравнявате променливи и резултати на един и същи работен лист. За да направите това, извикайте диспечера на сценариите (Услуга - Сценарии ).

14. Щракнете върху бутонаДокладвай. В появилия се прозорец поставете отметка в полетоСтруктура и щракнете върхуОК (фиг. 39).

Ориз. 39. Създайте отчет за сценарий

15. Когато диалоговият прозорец е завършен, EXCEL ще създаде нов работен листScript Structure и ще представи отчет, който може да бъде отпечатан и анализиран (фиг. 40).

---