Б136

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

Найчастіше аргументами фінансових функцій є такі величини:

• майбутня вартість — вартість вкладення або позики по завершенні усіх платежів;

• кількість виплат — загальна кількість платежів або періодів виплат;

• виплата — обсяг періодичних платежів по вкладенню або позиці;

• поточна вартість — початкова вартість вкладення або позики. Наприклад, початкова вартість позики дорівнює сумі позики;

• ставка — відсоткова ставка або знижка по вкладенню або позиці;

• тип — режим, що показує, як здійснюються виплати (наприкінці періоду чи на його початку).

- Функція НОРМА

Для розрахунку відсоткової ставки застосовується функція =НОРМА(к-сть платежів;виплата;поточна вартість;майбутня вартість;тип)

- Функція КПЕР

Для розрахунку строку платежів використовується функція =КПЕР(ставка; виплата; поточне значення; майбутнє значення; тип)

- Функція БЗ

Вираховує майбутнє значення вкладу на основі постійної відсоткової ставки і має такий вигляд:

=БЗ (ставка; к-сть виплат; виплата; поточна вартість; тип)

- Функція ПЗ

Для розрахунку поточної вартості єдиної суми вкладення (позики) і фіксованих періодичних платежів застосовується функція

=ПЗ(ставка; к-сть платежів; виплата; майбутня вартість; тип)

Функція ПЗ є оберненою щодо функції БЗ.

- Функція НПЗ

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

Функція =НПЗ(ставка; сумаї; сума 2; ...; сума N)

сума 1,2,3... N — значення виплат та надходжень

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

Возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянства процентной ставки.

ОСПЛТ(ставка ;период;кпер;пс;бс;тип)

Возвращает величину платежа в погашение основной суммы по инвестиции за данный период на основе постоянства периодических платежей и постоянства процентной ставки.

ПРОЦПЛАТ(ставка;период;кпер;пс)

Вычисляет проценты, выплачиваемые за определенный инвестиционный период.

=СРЗНАЧ(параметр) - Середнє значення чисел

=СЧЕТЕСЛИ(діапазон для перевірки умови; «умова») - Кількість разів виконання умови

У процесі роботи цієї функції проглядається діапазон для перевірки умови і підраховується кількість виконань умови. Наприклад:

• визначити, скільки разів робив замовлення клієнт Іванов: Діапазон для перевірки — В12:В24, умова — «Іванов». Функція має такий вигляд: =СЧЕТЕСЛИ(В12:В24;«Іванов»)

• визначити, скільки разів робились замовлення до 1998 року: Діапазон для перевірки — С12:С24; умова — «<01.01.98». Функція має такий вигляд:

=СЧЕТЕСЛИ(С 12:С24;«<01.01.98»)

=НАИМЕНЬШИЙ(параметр;N) - N-e мінімальне з чисел

=МАКС(параметр) - Максимальне з чисел

=СЧЕТ(параметр 1 ;параметр2;... ;параметр30) - Кількість чисел серед параметрів.

Для підрахунку суми числових значень у певному діапазоні використовується функція СУММ (діапазон комірок), проте вона підраховує суму із усіх значень, розташованих у вказаному діапазоні. Коли ж потрібно підрахувати суму числових значень, які відповідають певній умові у таблиці, використовується функція СУММЕСЛИ, яка має такий вигляд: =СУММЕСЛИ(діапазон для перевірки умови; «умова»; діапазон для підрахунку суми).- Сума чисел за умовою.

Наприклад, за даними попередньої таблиці треба підрахувати суму податку, що сплатив клієнт Іванов.

Діапазоном для перевірки умови є стовпчик з прізвищами клієнтів — В12:В24, серед яких зустрічається (або ні) потрібне прізвище.

Умовою для пошуку є прізвище «Іванов», а діапазоном для підрахунку суми значень є стовпчик з нарахованими податками —112:124. Тоді функція набуває такого вигляду: =СУММЕСЛИ(В12:В24;«Іванов»;І12:І24)

=НАИБОЛЬШИЙ(параметр;N) - N-e максимальне з чисел

=МИН(параметр) - Мінімальне з чисел.

Для проведення прогнозування за допомогою статистичних \ функцій Microsoft Excel застосовують регресивний аналіз. Це вид І статистичного аналізу, що дозволяє оцінити міру залежності між І змінними, пропонуючи механізм обчислення передбачуваного значення змінної з декількох уже обчислених значень. Іншими словами, на основі статистичної вибірки відомих значень функції F(x) та аргументів х, можна спрогнозувати поведінку функції шляхом підстановки нових значень аргументів.

• 3 використанням одиничної лінійної регресії — =ПРЕДСКАЗ(новийХ;відоміУ;відоміХ) — обчислює (прогнозує) одне нове значення функції на основі нового заданого X і статистичної виборки відомих значень Х та Y.

=ТЕНДЕНЦИЯ(відоміУ;відоміХ;новіХ) — на відміну від функції ПРЕДСКАЗ прогнозує не одне, а декілька нових значень функції на основі декількох нових заданих Х'\ статистичної виборки.

• 3 використанням одиничної нелінійної регресії — =РОСТ(відоміУ;відоміХ;новіХ) — так само, як і ТЕНДЕНЦИЯ, прогнозує декілька нових значень функції на основі декількох нових заданих X і статистичної виборки.

Приклад.

Задана статистична вибірка курсу американського долара та німецької марки за 8 місяців. Спрогнозувати зміну курсу німецької марки, якщо курс американського долара на наступні 4 місяці прогнозується згідно з таблицею.

Статистична вибірка характеризується функцією DM = F($US), де відомими значеннями х є значення курса американського долару (комірки В15:В22), відомими значеннями У є значення курсу німецької марки (комірки С15:С22).

За допомогою функції ПРЕДСКАЗ можна спрогнозувати значення курсу німецької марки тільки для одного місяця (вересня) на основі одного нового значення курсу американського долара (комірка В24 =5,42).

Функція ПРЕДСКАЗ заноситься у відповідну комірку С24:

=ПРЕДСКАЗ(В23;В15:В22;С15:С22 Enter і отримується результат.

Для прогнозування курсу німецької марки можна використати функції

=ТЕНДЕНЦИЯ(С15:С22;В15:В22;В24:В27), або

=РОСТ(С15:С22;В15:В22;В24:В27) де В24:В27 — це значення нових х — передбачуваних значень курсу американського долара протягом наступних чотирьох місяців.

Функція ТЕНДЕНЦИЯ має бути занесена у комірки D24:D27, а функція РОСТ — у комірки Е24:Е27 як формула масиву. Для уведення функції як формули масиву треба виділити діапазон клітин, де має міститися така функція, занести її і натиснути спочатку на клавішу F2, а потім клавіші CTRL+SHIFT+Enter.

Для проведення прогнозування за допомогою статистичних \ функцій Microsoft Excel застосовують регресивний аналіз. Це вид І статистичного аналізу, що дозволяє оцінити міру залежності між І змінними, пропонуючи механізм обчислення передбачуваного значення змінної з декількох уже обчислених значень. Іншими словами, на основі статистичної вибірки відомих значень функції F(x) та аргументів х, можна спрогнозувати поведінку функції шляхом підстановки нових значень аргументів.

• 3 використанням множинної лінійної регресії — =ЛИНЕИН(відоміY;відомiX) — прогнозує значення коефіцієнтів а, (і = п, п-\, ..., 2,1) та Ь. Отримані при прогнозуванні значення коефіцієнтів підставляються у рівняння множинної лінійної регресії і отримується значення Y.

• 3 використанням множинної нелінійної регресії — =ЛГФПРИБЛ(відоміY; відоміX) — прогнозує значення коефіцієнтів а, (і = п, п- 1, ..., 2,1) та b. Отримані при прогнозуванні значення коефіцієнтів підставляються у рівняння множинної нелінійної регресії і отримується значення Y.

Приклад:

Фірма бажає придбати будівлю під офіс і має оцінити вартість нерухомості, запропонованої на ринку.

Розв 'язання:

Можна використати множинний регресійний аналіз для прогнозування вартості будівлі. Передбачається, що використовується функція У = F(xi), де Y— вартість будівлі, хі — площа, х2 — кількість поверхів, хЗ — кількість входів, х4 — строк експлуатації будівлі.

Для прогнозування вартості триповерхової будівлі, що експлуатується ЗО років, з площею у 2000 м" та 2 входами, використовують Прайс-лист для нерухомості подібного типу у вигляді таблиці:

Для оцінки вартості використовується функція =ЛИНЕЙН (ві-доміУ;відомь¥), або функція

=ЛГФПРИБЛ(в ідом і Y; відомії).

Кожна з функцій не визначає Y у відповідному рівнянні множинної регресії, а визначає значення коефіцієнтів а, та Ь, починаючи з старшого коефіцієнта ап. Обчислені коефіцієнти використовуються у формулі, яка реалізує рівняння множинної регресії (лінійної, якщо використовувалась функція ЛИНЕЙ, і нелінійної, якщо використовувалась функція ЛГФПРИБЛ).

Відомими Ye значення, розташовані у комірках ЕЗ:Е13, відомими Хе значення, розташовані у комірках A2:D13.

Для визначення коефіцієнтів треба створити заголовки у комірках А15:Е15 і занести вибрану статистичну функцію у комірки А16:Е16 як формулу масиву:

у комірку Е20 заноситься формула яка реалізує рівняння множинної лінійної регресії =D16*A20+C16*B20+B16*C20+A16*D20+E16, і отримується результат.

Макрос — це серія команд і функцій, що зоерігаються в мо-дулі Visual Basic for Applications (VBA) — мови програмування четвертого покоління. Один раз створивши макрос, його можна виконувати стільки разів, скільки необхідно виконати дану задачу.

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

Макрос може бути виконаний за доп. Ком. Меню, за доп. Комбінації клавіш, і Кнопки. Для створення – Сервіс → Макрос → Начать запись; у вікні вказуємо ім’я макроса, під яким він буде зберігатися (перший символ – літера, без пробелів); з’являється панель, що називається “Остановка макроса” (містить дві кнопки: Остановить запись, Относительная ссылка, яка за умовчуванням активна); ком., які хочемо записати у макрос → Сервіс → Запись → Остановить запись. Макрос створено, для активізації потрібно: комб. Клавіш; Сервис → Макрос → Макросы, де у переліку вибираємо потрібний макрос → Выполнить; створюється кн. Панель інструментів → Формы → кн. ■ → змінюємо вказівник мишки і на роб. Полі створюємо кнопку. Цій кнопці можна надати ім’я. Для вик-я макросу – клацнути на цій кнопці. Знач-я стовпця повинно бути виділеним → вик-я макросу. Для перевірки дії макросу або повернутися, або виконати з іншим стовпцем.

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

За виглядом функція користувача не відрізняється від стандартних:

=ім' яфункції_користувача(аргументи) і вводиться у комірку, де треба виконати потрібну процедуру над даними — аргументами.

Але для того, щоб подані аргументи оброблялись належним чином, мовою VBA створюється процедура — функція.

Процедура Function є послідовністю інструкцій мови Visual Basic, обмежених інструкціями Function і End Function. Процедура Function схожа на процедуру Sub, однак на відміну від останньої вона повертає обчислене значення у робочій аркуш, звідки вона була викликана у супроводі формальних аргументів. Отримані аргументи (константи, змінні, або вирази) процедура Function підставляє в тому ж порядку замість власних умовних аргументів, які містяться у дужках після імені функції.

У поданій нижче таблиці клієнтів є формули, за якими визначаються вихідні результати — Податок і Сума замовлення.

Сума замовлення = Податок + Загальна вартість

- Викликається редактор VBA, активізується модуль з уже створеними макросами або створюється новий модуль, де записуються інструкції для виконання функції визначення податку:

Function Tax(Cost)

If Cost > 50000 Then

Tax = 0.1 *Cost

Elself Cost > 25000 Then

Tax = 0.12* Cost

Elself Cost > 10000 Then

Tax = 0.15* Cost

Else

Tax = 0.18* Cost

End If

End Function

Назва функції користувача Tax, Cost — умовний аргумент, від якого залежить значення функції Tax.

Для обчислення податку у робочому аркуші в комірці обчислення податку 12 вводиться створена функція = Тах(Н2).

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

В вікні, що відкрилось, у полі Установление цели вводимо адресу комірки, на яку ми накладаємо обмеження. В інших полях встан. Умову. При ОК активізується попереднє вікно, при натисканні Добавить ств-ся інші обмеження: ком. Виполнить. з’являється вікно з повідомленням, що рішення знайдено або може бути. У переліку Тип отчета можна ств. Звіт за результатами: перемикач Результат, в якому виведені попередні і нові значення комірок, які змінююься.

Комірка з кінцевим значенням називається цільовою коміркою, а комірки з вхідними даними, що підлягають зміні, називаються змінними комірками. Цільова комірка обов'язково повинна містити формулу або функцію, параметрами якої є значення змінних комірок.

База даних — це інтегроване сховище взаємопов'язаних даних конкретної предметної області.

Система управління базами даних (СУБД) — це комплекс програмних засобів, призначений для інтегрованого зберігання та обробки даних.

Система управління базами даних Microsoft Access відноситься до реляційних баз даних. База даних Access (фізична структура) міститься в одному файлі з розширенням MDB. Логічна структура СУБД Access складається з таких об'єктів: таблиць, запитів, форм, звітів, макросів та модулів. Доступ до цих об'єктів відбувається за допомогою відповідних вкладинок вікна Access.

СУБД Access дає змогу створювати такі типи об’єктів:

“Таблицы” – основний об’єкт БД, оскільки в ньому зберігається вся інформація.

“Запрос” – служить для фільтрації або селекції даних. Дає змогу вибрати з БД необхідну інформацію, яка відповідатиме певним критеріям і далі використовуватиметься для розв’язання конкретного завдання.

• “Форма” – це певний бланк для заповнення його даними або маска, що ніби накладається на БД. Головне його призначення – спростити процес заповнення БД.

• “Отчеты” – це певним чином оформлена програма, що дає змогу видавати для друкування або на екран інформацію з БД.

• “Макросы” використовують для автоматизації процесу виконання операцій, які часто повторюються.

• “Модули” – це програмні модулі, написані мовою VBA длярозв’язання складних завдань.

База даних — це інтегроване сховище взаємопов'язаних даних конкретної предметної області.

Система управління базами даних (СУБД) — це комплекс програмних засобів, призначений для інтегрованого зберігання та обробки даних.

Етапи створення бази даних у середовищі Microsoft Access:

• визначення мети створення бази даних;

• визначення таблиць, які повинна містити база даних;

• визначення структури таблиць (полів та їх типів);

• призначення ключів таблиць та створення потрібних індексів;

• визначення зв'язків між таблицями;

• завантаження даних;

• створення інших об'єктів бази даних: запитів, форм, звітів, макросів та модулів;

• «аналіз ефективності бази даних за допомогою майстра таблиць (меню СЕРВИОАНАЛИЗ>ТАБЛИЦА) та аналізатора швидкодії (меню СЕРВИОАНАЛИЗ>БЬІСТРОДЕЙСТВИЕ).

Усі дані бази даних зберігаються у таблицях. Таблиці створюються в два етапи. На першому етапі створюється структура таблиці, а на другому — вводяться дані.

Створення таблиці. Вибрати вкладнику Таблицы, натиснути на кнопку Создать та вибрати метод створення таблиці. Вважаючи, що структура таблиці, яка створена за допомогою майстра або в режимі таблиці, все одно підлягає редагуванню, розглянемо створення таблиці у режимі конструктора. Для цього треба у списку вибрати КОНСТРУКТОР і натиснути на кнопку Ok.

На екрані з'явиться вікно. У графу Имя поля треба ввести ім'я першого поля таблиці. Ім'я поля не повинно містити більше 64-х символів, включаючи пропуски, та не повинно містити символ «.».

У графі Тип данных треба задати тип поля. Для цього необхідно розкрити список та вибрати потрібний тип даних. В Access застосовуються такі типи даних:

• Числовой (NUMBER) — застосовується для числових даних, які використовуються у формулах. Тип та розмір значень задаються у властивостях РАЗМЕР ПОЛЯ та ФОРМАТ ПОЛЯ;

• Текстовый (TEXT) — застосовується для тексту та чисел (наприклад, табельний номер), які не використовуються у формулах. Поле цього типу може містити до 255 символів, за замовчанням — 50. Для визначення розміру поля треба задати властивість Размер поля;

• Поле MEMO — використовується для уведення текстів або чисел довжиною до 64000 символів;

• Дата/время (DATE/TIME) — довжина поля 8 байтів;

• Денежный — використовується для попередження округлення під час обчислень. Розмір поля — 8 байтів;

• Счетчик (AUTONUMBER) — використовується для автоматичного додавання номера запису. Якщо властивість поля Новые значения має значення: Последовательные — виконується додавання числа, яке отримується збільшенням на одиницю номера попереднього запису; Случайные — для лічильника генерується випадкове число. Розмір поля — 4 байти;

• Логический (YES/NO) — застосовується до полів, що можуть містити тільки одне з двох значень, такі як ДА/НЕТ, Исти-на/Ложь, ВКЛ/ВЫКЛ. Розмір поля — 1 біт;

• Поле объекта OLE (OLE OBJECT) — використовується для зв'язування або впровадження об'єктів (документів MICROSOFT WORD, електронних таблиць (MICROSOFT EXCEL), рисунків, звуків тощо). Для зображення об'єктів у формах та звітах необхідно застосовувати елемент управління Присоединенная рамка объекта. Розмір поля — до 1 гігабайта.

В Access є 4 способи створення таблиць: за допомогою “конструктора” таблиць; за допомогою “мастера створення таблиць”, “в режимі таблиці” («режим вводу даних») та “в режимі імпорт таблиць”.

Вікно БД → Вкладинка Таблиці → Создать → Конструктор

Або

Головне меню → Вставка → Таблиця → Создать → Конструктор.

На екрані з'явиться вікно. У графу Имя поля треба ввести ім'я першого поля таблиці. Ім'я поля не повинно містити більше 64-х символів, включаючи пропуски, та не повинно містити символ «.».

У графі Тип данных треба задати тип поля. Для цього необхідно розкрити список та вибрати потрібний тип даних.

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

Властивість Размер поля визначає максимальний розмір даних типу Текстовый, Счетчик або Числовой.

Властивість Формат поля дозволяє задати формати відображення тексту, чисел, дат та значень часу на екран та на принтер. Наприклад, для поля числового типу можна задати один з форматів.

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

В Access є 4 способи створення таблиць: за допомогою “конструктора” таблиць; за допомогою “мастера створення таблиць”, “в режимі таблиці” («режим вводу даних») та “в режимі імпорт таблиць”.

СУБД Access має значну кількість спеціальних програм – “майстрів”. Є майстер таблиць, майстер кнопок, майстер форм та ін. Майстри здійснюють діалог з користувачем, у процесі якого визначаються дані, необхідні для розв’язування відповідної задачі. Для зручності роботи кожен майстер має певні етапи (кроки). Будь-який етап можна пропустити або звернутись до попередніх.

Вікно БД → Вкладинка Таблиці → Создать → Майстер таблиць

Або

Головне меню → Вставка → Таблиця → Создать → Майстер таблиць.

В цьому вікні користувачу пропонується вибрати поля для таблиці з набору «стандартних» полів, сгрупованих по категоріях.

1. вибрати категорію Деловие або Личние;

2. В списку Образи таблиц виділити подходящу таблицю;

3. Вибрати потрібні поля, переміщаючи їх у список Поля новой таблици. Можна перейменувати поля, клацнувши на Переименовать поле;

4. Натискуємо Далі або Готово;

5. В наступних вікнах (якщр натиснули далі), можна вуазати ключові поля, та встановити зв’язки з іншими таблицями БД.

В Access є 4 способи створення таблиць: за допомогою “конструктора” таблиць; за допомогою “мастера створення таблиць”, “в режимі таблиці” («режим вводу даних») та “в режимі імпорт таблиць”.

Вікно БД → Вкладинка Таблиці → Создать → Режим таблиці

Або

Головне меню → Вставка → Таблиця → Создать → Режим таблиці.

На екрані з’явиться «заготівка» пустої таблиці, яка не містить ніяких даних.

Поля таблиці можно перейменовувати в режимі таблиці. Для цього двічі клацнути на імені стовпчика (поля), ввести нову назву та натиснути Ентер.

В Access є 4 способи створення таблиць: за допомогою “конструктора” таблиць; за допомогою “мастера створення таблиць”, “в режимі таблиці” («режим вводу даних») та “в режимі імпорт таблиць”.

Вікно БД → Вкладинка Таблиці → Создать → Імпорт таблиць

Або

Головне меню → Вставка → Таблиця → Создать → Імпорт таблиць.

З’являється вікно імпорту, де в полі Тип файлов вибирають потрібний тип (наприклад Microsoft Excel), натискають Импорт, у наступному вікні вибирають діапазон значень. З’являється таблиця Microsoft Excel, вибирають поля, у наступному вікні визначають, чи заголовок полів Excel будуть заголовками полів таблиці Access, визначають місце збереження даних у новій таблиці чи існуючій, визначають наявність і тип ключового поля, кнопка Готово. Далі з’являється повідомлення про успішне завершення імпорту. У режимі конструктора можна перевірити тип поля.