4.4. Функції та обчислення у ет

Обчислення в ЕТ. Обчислення в таблиці ведуться автоматично.

Це означає, що зміна будь-якого вхідного даного одразу ж веде до переобчислень всієї таблиці (OO Сервис → Содержимое ячейки → Пересчитать).

Режим Автоматично можна вимкнути і скористатися ручним переобчисленням за допомогою клавіші F9.

Використання стандартних функцій. Функції у ЕТ — це скорочений ваpiaнт формул, які часто застосовують, тобто це програми, що за готовими формулами реалізують обчислення над аргументами, які задаються користувачем згідно з правилами запису (синтаксису) функції. Кожна функція має ім'я (латинськi або кирилицею у Excel, тільки латинські у Calc) та аргументи, які записують у круглих дужках після iмeнi функції через крапку з комою. Аргументами функцій можуть бути числа, посилання на клітинки та діапазони, імена, текст, логічні значення, значення помилок, вкладені функції тощо. Доступ до функції здійснюється за її іменем.

Програми ЕТ мають декілько сотен стандартних функцій, а також є можливість створити власну функцію. Усі функції умовно поділяються на категорії:

Введення функцій. Можно вводити функції вручну безпосередньо з клавіатури у клітинці або у Строке Формул після знака «=», якщо синтаксис функції відомий, або за допомогою Мастера функций, який здійснює введення функцій у діалоговому режимі за декілько кроків.

Мастер функций побудовано за тематичним принципом (Рис. 4.8). У списку Категория вибрати потрібну, клацнувши лівою кнопкою миші, поряд зі списком можна отримати повний список імен функцій активної категорії. Обрав функцію, нижче з’явиться повний її запис та призначення. Функцію вик­ликають клацанням лівою кнопкою миші на її імені. У вікні Аргументы функции вводять у відповідне поле (чи поля) аргументи функції числа або адреси клітинок, що містить число. Для зручності введення адрес клітинок можна ско­ристатись кнопкою справа від поля, клацання на якій згортає вікно і відкриває активний лист, що дає змогу мишею визначити потрібну клітинку чи діапазон клітинок.

Рис.4.8. Вікна Мастера функцій

Для виклику довідки про використання функції і пояснення щодо її аргументів у вікні функцій у лівому нижньому куті вікна натисніть посилання Справка по этой функции.

Викликати Мастер функций можно декількома способами, спочатку виділити клітинку

• Вставка  Функция;

• (не має у Calc) Панель інструментів Стандартная  кнопку стрілка униз

• Другие функции… ;

• Строка Формул  кнопку (Вставка функции);

• ввести знак «=»  у Поле Имени  Другие функции…);

• (тільки у Calc) Вставка  Список функций (рис. 4.9).

Рис.4.9. Виклик Мастера функций кнопкою ПИ та в Поле Имени (Excel)

та виклик Мастера функций (Calc)

Також можно викликати функції, що використовувались в останнє, у Поле Имени.

Окремо виділено у ЕТ дію, що підсумовує значення, крім функції СУММ(), що підсумовує значення діапазону клітинок, застосовують Автосуму. Підсумувати значення рядка (стовпчика) суміжних клітинок можна, виділивши клітинку, що замикає рядок справа (стовпчик — знизу), і клацнувши кнопку Автосумма Ппанели инструментов Стандартная (Calc- Ппанель инструментов Строки Формул). Якщо виділити діапазон клітинок і натиснути кнопку Автосумма, функцію СУММ буде застосовано автоматично до всіх стовпців діапазону, а результати розташовано в наступних клітинках.

Крім того, окремі статистичні функції, наприклад СУММ, МАКС, МИН, можна швидко обчислити без використання формул, виділивши потрібний діапазон клітинок і ввімкнувши режим Автообчислення клацанням правої кнопки миші в рядку Строка Состояния на полі Автовычисление. Результат обчислень буде зображений у цьому ж полі, наприклад, Сумма=58. У подальшому значення останньої вибраної функції обчислюватиметься автоматично.

Функціїї ЕТ. Розглянемо деякі функції.

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

ГОД(дата в числовом форматие) – повертає рік відповідно дати (Calc-YEAR).

ДАТА(год;месяц;день) – повертає дату у встановленому форматі (Calc- DATA).

СЕГОДНЯ() – повертає поточну дату у встановленому форматі (Calc- TODAY).

ВРЕМЯ(часі;минуті;секунді) – повертає час у встановленому форматі (Calc- TIME).

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

Арифметичні функції

СУММ(число1; число2;...) - повертає суму аргументів (Calc- SUM).

ПРОИЗВЕД(число1; число2;...) - повертає добуток своїх аргументів (Calc- PRODUCT).

АВS(число) - повертає абсолютне значення числа.

КОРЕНЬ(число) - повертає додатне значення квадратного кореня числа (Calc- SQRT). Якщо число від'ємне, з'являється повідомлення про помилку #ЧИСЛО!.

СТЕПЕНЬ(число; показник степеня) - повертає результат піднесення до степеня (Calc- POWER). Цей вираз є еквівалентним до виразу число^показник степеня.

ОКРУГЛ(число; кількість розрядів) - повертає результат округлення до зазначеної кількості десяткових розрядів (Calc- ROUND).

ОКРВВЕРХ(х; точність) та ОКРВНИЗ(х; точність) - повертає результат округлення з надлишком (з нестачею) до найближчого числа, що кратне точності (Calc- ROUNDDOWN та ROUNDUP).

ОТБР(число; кількість_розрядів) - залишає зазначену кількість розрядів числа після коми, відкидаючи всі наступні (не виконую­чи округлення) (Calc – TRUNC).

СЧЁТЕСЛИ(Диапазон;Критерий) - повертає кількість тільки тих ар­гументів, які відповідають певним умовам (Calc – COUNTIF).

Диапазон — діапазон клітинок, для яких перевіряється крите­рій; Критерий — умова відбору у формі числа, виразу або тексту.

Розривні діапазони не підтримуються. Значеннями в клітинках можуть бути числові дані в усіх різновидах, логічні зна­чення, слова і фрази (клітинки можуть бути також порожні). До­пустимі значення можна задавати як константою, так і форму­лою.

СУММЕСЛИ (Диапазон; Критерий;Диапазон_Суммирования) - повертає суму клітинок діапазону, які задовольняють зада­ний критерій (Calc - SUMIF).

Диапазон_Суммирования визначає діапазон клітинок, в якому, власне, виконується додавання значень клітинок, для яких відповідні клітинки у параметрі Диапазон задовольняють Критерий. Па­раметр Диапазон_Суммирования можна не зазначати — необов'язковий параметр, що визначає клітинки, значення яких долучаються до підсумку в разі виконання умо­ви. Діапазони Интервал та Сумм_интервал повинні мати одна­кову кількість клітинок. Якщо аргумент Сумм_интервал про­пущений, то розраховується сума з клітинок діапазону Интервал, для яких справджується критерій.

Логарифмічні функції

LN(числo) повертає натуральний логарифм числа.

LOG10(число) повертає десятковий логарифм числа.

Тригонометричні функції

ПИ() повертає число  з точністю до 15 цифр (Calc- PI). Наявність відкриваючих та закриваючих дужок обов'язкова.

РАДИАНЫ(кут в градусах) - обчислює значення кута в радіанах (Calc- RADIANS та оборотня - DEGREES.

SIN(кyт) - повертає синус кута, заданого в радіанах. Часто значення кута задане у градусах. У такому разі необхідно використати попередню функцію.

COS(число), TAN(число) - повертає косинус і тангенс заданого числа відповідно.

АSIN(число) - повертає арксинус числа, тобто кут, синус якого до­рівнює числу.

Матричні функції

Як правило, матричні функції застосовують для розв'язування сис­тем лінійних рівнянь. Наведемо деякі матричні функції. У якісті аргументів у Excel використовується значення масив, у Calc - матриця.

МУМНОЖ(масив1;масив2) – повертає добуток матриць (Calc – MMULT).

МОПРЕД(масив) - повертає визначник матриці (Calc - .

МОБР(масив) - повертає обернену матрицю (Calc- MINVERSE).

ТРАНСП(масив) – повертає транспоновану матрицю (Calc- TRANSPOSE).

Статистичні функції

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

МАКС(число1;число2;...) та МИН(число1;число2;...) - повертають відповідно найбільше та най­менше значення з набору аргументів (Calc - MAX та MIN).

СЧЁТ(значение1;значение2;...) - визначає кількість клітинок у заданому діапазоні, які містять числа (у тому числі дати й формули, що повертають числа) (Calc – COUNT).

СРЗНАЧ(Число1 ;Число2;...) - обчислює середнє арифметичне своїх аргументів (Calc - AVERAGE).

Логические функції

У ЕТ логическі функції, це функції з допомогою яких реалізуються базові логичні операції: множення -AND; складання - OR ; заперечення -NOT.

ЕСЛИ(условие; Значение_для_Истина; Значение_для_НЕ_Истина) – повертає одне значення аргумента условие ИСТИНА або ЛОЖЬ (Calc - IF).

Значение_для_Истина — це значення чи вираз, який поверне функ­ція ЕСЛИ() як істинне значення параметра Условие. Якщо параметр не зазначити, то як результат ЕСЛИ() повернеться ИСТИНА. Виразом у параметрі, зокрема, може бути будь-яка інша функція, зокрема ЕСЛИ().

Значение_для_НЕ_Истина — це значення чи вираз, який поверне функція ЕСЛИ() як хибне значення параметра Условие. Якщо параметр не зазначити, то як результат ЕСЛИ() повернеться ЛОЖЬ. Виразом у параметрі, зокрема, може бути будь-яка інша функція, зокрема ЕСЛИ()

Зверніть увагу:

• якщо у функції ЕСЛИ() пропускається другий параметр, то сим­вол ";" перед третім параметром має бути обов'язково, ЕСЛИ(Условие; ;Значение_для_НЕ_Истина);

• допускається до семи вкладень функцій ЕСЛИ() одна в одну.

• для складніших умовних виразів, коли задається багато значень, потрібно використовувати функції И(Рr;Рr;...) та ИЛИ(Рr;Рr;...) (AND або OR), де Рr — умовний вираз. Ці функції мають бути вкладені у функ­цію ЕСЛИ() на місці Условие.

Финансові функції.

Фінансові функції використовують для розв'язування задач планування фінансової діяльності, визначення прибутків, аналізу вигідності капіталовкладень, кредитно-інвестиційної політики тощо.

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

Розглянемо головні параметри фінансових функцій та їхні скорочені назви:

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

кількість періодів Кпер (англ.: NPER), кожний тривалістю доба, місяць, рік тощо;

періодична виплата Плт (англ.: РМТ) — сума, яку виплачує клієнт за кожний період (це від'ємне число), або сума, яку отримує клієнт за кожний період (це додатне число);

сума внеску Пс (англ.: PV) — сума інвестиції, капіталовкладення, початкового внеску (від'ємне число або нуль);

тип операції Тип (англ.: Туре або F) — число 0, якщо виплата дійснюється наприкінці кожного періоду, і число 1, якщо на початку.

Вирізняють кредитну і депозитну процентні ставки. Кредитна ставка с вищою за депозитну. Процентна ставка має бути узгодженою з тривалістю періоду, наприклад, річна ставка 24% рівносильна місячній ставці 2%.

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

БС(Ставка; Кпер; Плт; Пс; Тип) (FV)

Функція для визначення майбутньої вартості інвестиційного капіталу на умовах нарахування різних процентів за певну кількість (до 30) періодів

БЗРАСПИС (капітал; масив процентів) (англ.: FVSCHEDULE)

Функції для визначення виплат для погашення позики.

Введемо нові терміни та їхні скорочені назви:

номер періоду Период (англ.: Р), Период = 1 на початку періоду;

сума позики Пс (англ.: PV);

кінцеве значення позики Бс (англ..: FV).

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

ПЛТ(Ставка; Кпер; Пс; Бс; Тип) (англ.: РМТ)

Така виплата складається з двох частин, які обчис­люють за допомогою двох функцій:

а)виплата за процентами ПРПЛТ(Ставка; Период; Кпер; Пс; Бс; Тип); (англ.: ІРМТ)

б)основна виплата ОСПЛТ(Ставка; Период; Кпер; Пс; Бс; Тип). (англ.: РРМТ)

Виплата за процентами кожний період зменшується, а основна виплата кожний період зростає, їхня сума постійна і дорівнює ПЛТ.

Функція яка обчислює кількість періодів, потрібних для погашення суми позики, на­ на­даної під деяку процентну ставку за умови заздалегідь заданої суми періодичних виплат

КПЕР(Ставка; Плт; Пс; Бс; Тип) (англ.: NPER)

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

СТАВКА(Кпер; Плт; Пс; Бс; Тип; початкове наближення) (англ.: RATE)

Тут по­трібно задати деяке початкове наближення до шуканої процентної ставки, наприклад 0,1 (10%)..

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

Функція обчислює сьогоднішню вартість низки майбутніх надходжень (ренти) від бізнесу

ПС(ДПС; Кпер; Плт; Бс; Тип) (англ.: PV)

де ДПС — депозитна процентна ставка, тобто ставка, яку банк виплачує за вклади клієнтів.

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

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

ЧПС(ДПС; рента1; рента2;...) (англ.: NPV)

Функції користувача. Користувач може для власних розрахунків створити свою функцію з певними діями та користуватися їй як стандартними. Для цього треба скористатися вбудованим редактором Visual Basic Сервис  Макрос Редактор Visual Basic. У вікні редактора необхідно записати всі дії майбутньої функції та дати їй ім’я.

Питання для самоперевірки

1. Як здійснити обчислення в таблиці?

2. Які елементи може містити формула?

3. Що таке копіювання формул?

4. В чому суть автоматичного переобчислення у ЕТ?

5. На які категорії поділяють стандартні функції ЕТ?

6. Як ввести у формулу функцію?

7. Як реалізується розгалуження в ЕТ?

8. Який розділювач можна використовувати у списках аргументів функції?

9. Яки способи існують у ЕТ для обчислення суми чисел?

10. Як створити фукцію користувача?