Excel10УКР

51

правилам роботи із такими функціями (розділ 3), тобто вставши на першу клітинку, в якій потрібно набути прогнозного значення, виділити всі клітинки очікуваного прогнозу, викликати функцію ТЕНДЕНЦИЯ() і заповнити її аргументи:

известные_значения_y – клітинки, що містять чисельність населення з 1950 року по 2012 роки;

известные_значения_x – клітинки, що містять роки з 1950 по 2012;

новые_значения_x – клітинки, що містять роки з 1920 по 2040;

конст=истина (будь–яке число, відмінне від нуля, у даному випадку конст=ложь припускає, що чисельність населення Землі у рік народження Христа була нульовою ).

На кінцевому кроці замість звичної кнопки «ОК», натискаємо комбі-

націю CTRL+SHIFT+ENTER!

Функція ТЕНДЕНЦИЯ() дозволяє отримати також значення параметрів згладжуючої прямої.

Дійсно

y(0) k 0 b b, y(1) k 1 b k b або

b y(0), k y(1) y(0) , тобто досить набути розрахункових значень функцією ТЕНДЕНЦИЯ() для 0 і 1 значення аргументу. Корисно виконати ці обчислення і переконатися, що параметри прямої співпадуть з отриманими при побудові лінії тренда. Для наших даних отримані результати приведені у таблиці 6.3.

Таблиця 6.3

Обчислення параметрів згладжуючої прямої

	Роки		1950		1960		1970		1980		1990		2000		2012		0		1		k
	Чисе-		2527		3060		3727		4430		5294		6091		7122		–143854,73		–143779,76		74,97
	льність

Аналогічну апроксимацію виконує функція РОСТ(), тільки у цьому випадку пошук згладжуючої кривої виконується у класі експоненціальних

функцій y ceax . Аргументи функції РОСТ() такі ж, як у функції ТЕНДЕНЦИЯ(), якщо конст=истина, то шукаються обидва параметри C і а, при конст=ложь параметр C вважається рівним одиниці.

На рис. 6.4 показаний графік і параметри апроксимуючої експоненціальної функції для тих же даних про кількість населення Землі.

52

Рис. 6.4. Графік і параметри апроксимуючої експоненціальної функції по кількості населення Землі

При побудові лінії тренда вибраний тип апроксимації ―Экспоненциальная‖. Прогноз народонаселення Землі за допомогою функції РОСТ() приведений в таблиці 6.4.

Таблиця 6.4

Прогноз народонаселення Землі, млн осіб

	Роки		1950		1960		1970		1980		1990		2000		2012		2020		2030		2040
	Чисель-		2527		3060		3727		4430		5294		6091		7122		8498		10061		11911
	ність

Як видно, згладжування експоненціальною функцією приводить до значно більшої величини прогнозу, чим при лінійній апроксимації. Рекомендовано самостійно вивести формулу для обчислення коефіцієнтів с та а за допомогою функції РОСТ().

Цікаво зазначити, що одержані результати прогнозу лінійною апроксимацією непогано співпадають з прогнозами вчених. Так за прогнозами ООН поріг у 8 млрд. буде перевищений у 2028 році, за нашою лінійною апроксимацією отримуємо 2025,5, що співпадає з прогнозом Бюро перепису населення США –

2026 рік.

53

7. ЛАБОРАТОРНА РОБОТА № 6. ВИКОРИСТАННЯ ДЕЯКИХ ФІНАНСОВИХ ФУНКЦІЙ

Розглянемо проблему взяття кредиту в банку, що має для багатьох практичне значення. Кредит в основному визначається сумою, яка береться, відсотковою ставкою, терміном кредиту, комісією банку і первинним внеском (для цільових кредитів). Виплати по кредиту практично завжди щомісячні і складаються з двох частин – виплати частини суми, яку взяли в кредит (основні виплати або виплати по тілу кредиту) і оплати відсотків за поточний період, які нараховуються на залишок боргу. Необхідно розрахувати по місяцях суму основних виплат і суму виплат по відсотках.

Трохи спростимо завдання. Вважатимемо, що немає первинного внеску і комісії банку. Первинний внесок в даний час може складати до 25% від суми кредиту і є дуже істотною складовою кредиту, оскільки цю суму необхідно буде знайти з інших джерел, і служить він для додаткового підтвердження платоспроможності клієнта. У розрахунках її легко буде врахувати, зменшивши суму кредиту. Комісія банку – це інструмент, який придумали банкіри для введення в оману клієнтів щодо майбутніх виплат. Відсоткова ставка завжди вказується щодо річних виплат, а комісію при- в'язують до місячних виплат. Наприклад, видається кредит під 25% річних при щомісячній комісії 1%. Два числа сильно відрізняються і клієнт може не прийняти його до уваги, а це додаткові 12% до річної ставки! У наших розрахунках врахувати комісію досить просто, незалежно від того нараховується вона на залишок суми, або носить постійний характер.

Ще однією важливою характеристикою кредиту є схема виплат. У першому варіанті сума виплат по тілу кредиту і відсоткам постійна впродовж усього терміну кредиту (аннуитет), в другому випадку постійні виплати по тілу кредиту і відсотки нараховуються на залишок. Розглянемо детально випадок аннуитета, складніший для розрахунків, залишивши другий випадок для самостійної роботи.

Для розрахунків по аннуитету в Excel служить функція ПЛТ() з розділу фінансові функції. Синтаксис функції наступний:

ПЛТ(ставка; кпер; плт; [бс]; [тип])

Повертає суму періодичного платежу для аннуитета на основі постійних сум платежів і постійної процентної ставки.

Ставка – відсоткова ставка по кредиту (обов'язковий аргумент). Кпер – загальне число виплат по кредиту (обов'язковий аргумент).

54

Пс – приведена до теперішнього моменту вартість або загальна сума, яка на даний момент рівноцінна ряду майбутніх платежів, що також має назву основна сума. У разі боргу задається із знаком мінус! (обов'язковий аргумент).

Бс – необхідне значення майбутньої вартості, тобто бажаного залишку коштів після останнього платежу. Якщо цей аргумент опущений, передбачається, що він рівний 0 (необов'язковий аргумент).

Тип – ознака часу виплат, 0 або опущений – виплати в кінці періоду, 1 – виплати на початку періоду.

Зауваження.

Переконайтеся, що ви послідовні у виборі одиниць вимірювання для завдання аргументів "Ставка" і "Кпер". Якщо ви робите щомісячні виплати по чотирирічному кредиту з розрахунку 12 відсотків річних, то використовуйте значення 12%/12 для завдання аргументу "Ставка" і 4*12 для завдання аргументу "Кпер".

Розглянемо наступне практичне завдання.

Узята в кредит сума 120000 грн. на термін 7 років під 18% річних. Необхідно розрахувати щомісячну основну плату і плату по відсотках.

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

Оформимо рішення задачі на аркуші Excel наступним чином

(рис. 7.1).

55

Рис. 7.1. Рішення задачі

Зауваження.

При роботі з грошовими величинами рекомендується використовувати фінансовий формат чисел з вказівкою назви валюти. Для цього необхідно виконати Главная => Формат => Формат ячеек => Число => Финансовый і в рядку «Обозначение» вибрати потрібну валюту. Нажаль, найбільш поширене позначення української гривни «грн.» у даній версії Excel відсутнє (є позначення «UAH» і « »). Його можна створити самостійно. Оберіть замість формату Финансовый «все форматы» і один з фінансових форматів замініть на наступний: «_-* # ##0,00\

[$грн.-422]_-;».

На рис. 7.1 клітинці B2 привласнено ім'я «Размер_кредита», в клітинці D3 обчислюється термін кредиту в місяцях і їй привласнено ім'я «Срок», в клітинці D4 обчислюється щомісячна процентна ставка і їй привласнено ім'я «Месячная ставка», у клітинці B6 обчислюється сумарна щомісячна плата по формулі =ПЛТ(Месячная___ставка;Срок;-

Размер_кредита) і їй привласнено ім'я «Ежемесячная_плата» (при при-

власненні імені Excel автоматично пропонує ім'я клітинки, відповідне найближчому напису, замінюючи пропуски та інші неприпустимі символи на знак підкреслення).

Для створення таблиці виплат спочатку в двох рядках впишемо прості формули (рис. 7.2):

56

Рис. 7.2. Формули для розрахунків

Формули в 10-му рядку повністю готові до копіювання. Їх треба протягувати одночасно оскільки вони взаємозв'язані. Після отримання всієї таблиці необхідно виконати додаткові обчислення. Перш за все, перевірити, що сума щомісячних основних платежів повністю співпадає з сумою узятого кредиту. Обчислити суму платежів по відсотках і нарешті, суму грошей, яку Вам доведеться заплатити за весь кредит (банки дуже не люблять показувати цю суму клієнтам). Додатково обчисліть, скільки відсотків складе ця сума від суми узятого кредиту (рис. 7.3):

Рис 7.3. Результат розрахунків

57

Самостійно отримайте таку ж таблицю використовуючи функції ПРПЛТ() і ОСПЛТ(). Синтаксис функцій узяти з довідки. Розрахуйте також щомісячні виплати по другій схемі (тіло кредиту погашається рівними долями – див. вище), використовуючи необхідні формули (стандартні функції відсутні, і необхідності в них немає).

Варіанти лабораторної роботи наведені в табл. 7.1.

Таблиця 7.1

Варіанти лабораторної роботи

		№
						Сума кредиту				Річна відсоткова				Термін,
										ставка
		варіанту												роки
	1				14000				27				6
	2				25000				25				8
	3				160000				20				12
	4				1170000				15				18
	5				80000				22				24
	6				190000				21				12
	7				200000				18				18
	8				210000				22				9
	9				220000				25				10
	10				20000				26				12
	11				27000				23				18
	12				250000				18				6
	13				260000				21				15
	14				270000				15				8
	15				280000				21				10
	16				250000				8				10
	17				300000				9				15
	18				180000				10				20

58

		№				Сума кредиту				Річна відсоткова				Термін,
										ставка
		варіанту												роки
	19				1000000				12				25
	20				1500000				11				10
	21				550000				13				12
	22				150000				14				15
	23				200000				15				13
	24				250000				16				24
	25				300000				17				8
	26				350000				18				6
	27				400000				19				15
	28				550000				20				18
	29				150000				17				10
	30				200000				13				7
	31				80000				18				12
	32				500000				15				17
	33				350000				14				20
	34				700000				12				25

59

8. РОЗВ’ЯЗАННЯ ОПТИМІЗАЦІЙНИХ ЗАДАЧ В EXCEL

8.1. ЗАГАЛЬНЕ ФОРМУЛЮВАННЯ ЗАДАЧІ ОПТИМІЗАЦІЇ

x Rn – n-мірний Евклідовий простір, тобто x x , x		2	,..., x	n	–
	1
n-мірна точка. Визначена функція f (x) на Rn ,	тобто кожній		n–мірній
точці і поставлено у відповідність дійсне число:	f : Rn R . Задана та-
кож деяка підмножина G Rn . Знайти: max f (x).
x G

Підмножина G у загальному випадку довільна, для тривимірного простору приклади наведено на рис. 8.1.1.

Рис. 8.1.1. Приклади підмножин для тривимірного простору

60

8.2. ЗАДАЧА ЛІНІЙНОГО ПРОГРАМУВАННЯ (ЗЛП)

Відносно простим варіантом загальної оптимізаційної задачі є задача лінійного програмування, у якому цільова функція і всі обмеження задані лінійними функціями від змінних, що оптимізуються. Термін «програмування» потрібно розуміти як «планування» (один із перекладів англійського слова «programming»). Він був запропонований у середині 1940–х років Джорджем Данцигом, одним із засновників лінійного програмування, ще до того, як комп’ютери були використані для вирішення лінійних задач оптимізації.

У ЗЛП цільова функція задається сумою добутків (лінійна згортка) заданих коефіцієнтів зі змінними, що оптимізуються:

n

Z c1 x1 c2 x2 ... cn xn ci xi max(min) .

i 1

Множина G Rn також задається у вигляді лінійних обмежень:

a x a x

...

a

x

b

11 1

12

2

1n

n

1

...........................................

a

a

b

a

x

x

x

r1 1

r 2

2

rn

n

r

ar 11 x1 ar 12 x2 ...

ar 1n xn br 1

..........................................

am2 x2

amn xn bm

am1x1

Кількість обмежень m не пов’язано з розмірністю n вектора змін-

них, тобто m

може бути менше

n , більше

n

або дорівнювати

n . Об-

меження можуть бути двох типів: у вигляді нерівностей і (або) рівності. Обмеження, найчастіше, утворюють багатокутник в n-мірному просторі. На рис. 8.2.1 наведено приклад геометричного виду області обмеження для двовимірної ЗЛП.