2430

Аналіз табличних даних

Реферат

Информатика, кибернетика и программирование

Корпоративна система призначається як правило для оперативного управління. Тому для менеджерів-аналітиків, яким необхідно шукати оптимальні рішення, дуже актуальним є застосування підручних програмних засобів. Одним з таких засобів є табличний процесор Excel, що входить у загальнодоступний пакет Microsoft Office.

Украинкский

2013-01-06

157 KB

6 чел.


Аналіз табличних даних

Корпоративна система призначається як правило для оперативного управління. Тому для менеджерів-аналітиків, яким необхідно шукати оптимальні рішення, дуже актуальним є застосування «підручних» програмних засобів. Одним з таких засобів є табличний процесор Excel, що входить у загальнодоступний пакет “Microsoft Office”, за допомогою якого можна розв’язувати задачі аналізу і прогнозу даних та багато іншого.

1. Табличний процесор Excel

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

Документ Excel представляється у вигляді книги, що розбита на довільне число робочих аркушів (чи просто аркушів), кількість яких визначається користувачем в залежності від розв'язуваної задачі. Кожен аркуш, розбитий на рядки і стовпці, на перетині яких знаходяться комірки даних. На аркуші можуть знаходитися одна чі кілька електронних таблиць, тобто звичайних таблиць, представлених в електронній формі.

Таким чином, на кожному робочому аркуші є двовимірна система координат, що призначена для іменування комірок таблиць. Кожен рядок має адресу, що є числом від 1 до 65536, і аналогічно кожний з 256 стовпців має адресу, якою є латинська літера від A до Z чі сполучення латинських літер від AA до IV.

Кожна комірка на аркуші має адресу і вміст. Адреса комірки складається з адреси стовпця і рядка, на перетині яких вона знаходиться, наприклад, А1, С7 і т.д. З кожною коміркою на аркуші неявно пов'язується алгебраїчна змінна, назва якої збігається з адресою комірки, а значення збігається з вмістом комірки. Вмістом комірки може бути будь-який рядок символів, що має довжину  до 32768 символів, і який відноситься до одного з наступних типів:

текстова константа;

числова константа;

формула.

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

Числові константи (числа) починаються з цифри і за замовчуванням вирівнюються в комірці вправо. Якщо число в комірці вирівнюється вліво, то, швидше за все, відбулася помилка в числовому форматі. У програмі Excel є 9 числових форматів, включаючи формати дати і часу.

Вся сила Excel полягає в формулах. Без них ця програма була б  просто калькулятором.

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

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

Наприклад, в комірці А1 може знаходитися формула =А2+1, у якій А2 є першим операндом (змінна), а константа 1 другим операндом операції додавання (+). Цю формулу можна сприймати як алгебраїчну рівність А1=А2+1. Тому, якщо комірка А2 містить, наприклад, число 5, то відповідно до законів арифметики комірка А1 буде містити число 6. В цьому випадку говорять, що в комірці А1 є посилання на комірку А2 чи, комірка А1 (інформаційно) залежить від А2. У програмі Excel у меню «Сервис» є спеціальна команда «Зависимости», яка дозволяє «побачити» всі комірки, від яких залежить дана комірка ( які впливають на дану комірку). Якщо у формулі є посилання на текстову, а не на числову константу, то у відповідній комірці з’являється повідомлення про помилку. Це найчастіше джерело помилок у формулах. Крім того, в комірці не може знаходитися формула, що посилається на ту ж комірку, в якій вона знаходиться. Це окремий випадок циклічних посилань, що заборонені.

З формулами пов'язані два фундаментальних механізми програми Excel:

Автоматичне переобчислення;

Автозаповнення при копіюванні формул.

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

Другий механізм включається при копіюванні формул. Наприклад, якщо формулу =А2+1 скопіювати з комірки А1 в комірку В1, то в комірці В1 з'явиться формула =В2+1.

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

Але, проте, іноді не потрібно змінювати формулу. Щоб більш гнучко керувати процесом копіювання вводиться поняття відносної й абсолютної адресації. Усі звичайні адреси у формулах розглядаються як відносні й модифікуються відповідно до загального правила. Якщо перед адресою рядка чи  стовпця стоїть знак долара $, то така адреса називається абсолютною і не модифікується при копіюванні. Таким чином, формула =$А$2+1 при копіюванні змінюватися не буде, оскільки адреса $А$2 є абсолютною. Можлива комбінована адресація, коли адреса рядка абсолютна, а адреса стовпця відносна, наприклад =А$2+1 чі навпаки =$А2+1. Такі формули будуть модифікуватися тільки при копіюванні по горизонталі чі, відповідно, тільки при копіюванні по вертикалі. Таким чином, загальне правило копіювання формул говорить:

При копіюванні формули усі відносні адреси в цій формулі модифікуються у відповідності до вектора зміщення формули.

Отже, у Excel [1] скрізь, де можливо, потрібно використовувати алгебраїчні вирази зі змінними кількостями, уникаючи константних арифметичних виразів. Константами повинні бути тільки вхідні дані. Тоді при зміні вихідних даних, новий результат виходить автоматично за рахунок механізму переобчислення.

2. Робота зі списками і підведення підсумків

Робота зі списками є, напевно, основним видом використання програми Excel у більшості компаній. Реєстри продажів, накладні, прайс-листи, відомості нарахування заробітної плати і т.п., усе це приклади списків. Але не будь-яка таблиця в Excel є списком і для списків існують спеціальні методи їхньої обробки.

Список у Excel це таблиця, що інтерпретується процесором як база даних. Тому списки в Excel також називаються «базами даних». Звичайно, до цієї назви потрібно відноситися з деякою обережністю, оскільки в наступній лекції ми познайомимося зі «справжніми» базами даних, в той же час певна аналогія тут, безумовно, є. Характерною ознакою (реляційної) бази даних є однорідність даних по стовпцях. Тому, щоб таблиця Excel могла розглядатися як список необхідно, насамперед, щоб у кожнім стовпці таблиці дані мали один тип, тобто або всі були текстовими, або всі числовими, або усі датами. Для більшості додатків, де використовуються списки, ця умова виконується. Крім того, щоб табличний процесор інтерпретував таблицю як список є необхідним виконання ряду формальних умов:

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

Якщо ці умови виконані, то процесор розпізнає список автоматично. Для цього досить ввести вручну заголовки стовпців і поставити курсор на порожній рядок під заголовком чи під останнім рядком даних, якщо вони вже були введені. Якщо Excel розпізнає таблицю як список, то в користувача з'являється можливість використовувати процедури обробки характерні для баз даних, а саме, введення даних у список через спеціальну форму, сортування даних, фільтрація (відбір) даних, групування даних і т.д. Розглянемо ці процедури по порядку.

Щоб викликати форму введення даних, введіть заголовки стовпців, виділіть їх жирним шрифтом, щоб вони відрізнялися від даних, поставте курсор у порожній рядок під заголовком одного зі стовпців, а потім виконайте команду «Данные →Форма». Якщо все в порядку, то з'явиться форма введення даних (див. рис. 3), у противному випадку, якщо порушена одна з вищезгаданих умов, Excel повідомить про помилку.

Рис.  Форма введення даних

Цілком можливо, що після виконання команди «Данные Форма» Excel видасть повідомлення типу: «не вдається знайти рядок списку, щоб використовувати дані першого рядка як підписи натисніть кнопку «ОК». У цьому випадку треба просто натиснути кнопку «ОК» і форма з'явиться на екрані. Після цього можна бути впевненим, що ваша таблиця інтерпретується процесором як список, і з нею можна працювати як з базою даних. Форма також може використовуватися не тільки для введення даних, але і для їх редагування і пошуку.

Сортування списку. Сортувати список можна за значенням одного стовпця за допомогою відповідної кнопки на панелі інструментів і за значеннями декількох стовпців (до трьох) за допомогою команди «Данные Сортировка». 

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

Фільтрація це вибір даних зі списку за заданими критеріями відбору. У відфільтрованому списку можна бачити тільки ті рядки, які задовольняють заданим критеріям. При цьому відповідні номери рядків виділяються синім кольором (ознака фільтрації), а інші рядки залишаються прихованими, але не знищеними. Приховані рядки можуть бути в будь-який момент відновлені. Для простої фільтрації використовується Автофильтр, а для фільтрації за складними критеріями - Расширенный фильтр [1].

Розглянемо деякі можливості фільтрації за допомогою автофільтру. По-перше, за допомогою елемента списку автофільтру «(Первые 10…)» можна вибрати рядки по декількох найбільших чи найменших елементах відповідного числового стовпця (поля). По-друге, за допомогою елемента «(Условие…)» можна викликати вікно користувальницького автофільтру, в якому задається складна логічна умова відбору за відповідним стовпцем, наприклад можна вибрати рядки, відповідні значення в яких знаходяться між двома заданими числовими значеннями. Нарешті, щоб відобразити всі дані, приховані в результаті фільтрації, потрібно виконати команду «Данные Фильтр Отобразить все».    

Розширений фільтр дозволяє створювати повноцінні запити (у термінології баз даних) до списку. Наприклад, за допомогою розширеного фільтру можна задати умови відбору по двох і більше стовпцях, які з'єднані логічним сполучником “ИЛИ”, що за допомогою автофільтру зробити не можна. Перша відмінність розширеного фільтру полягає в тому, що тут у явному вигляді потрібно задати диапазон условий. Для цього звичайно перед списком роблять кілька порожніх додаткових рядків і в першому рядку повторюють назви всіх стовпців  списку чи частини з них, якщо відбір буде йти не по всіх стовпцях. Краще вказувати тут назви всіх стовпців, тому що зайві стовпці завжди можна залишити порожніми. Потім порядково вводяться критерії відбору, причому умови, що знаходяться в одному рядку з'єднуються сполучником «И», а самі рядки умов з'єднуються сполучником «ИЛИ». Наприклад, у наступній таблиці показані умови відбору за розширеним фільтром.  

Таблиця  Умови відбору за розширеним фільтром

Товар

Менеджер

Объем продаж

Артикул 2001

>1000

Иванов

За цим критерієм будуть відбиратися рядки, в яких зазначений товар з артикулом 2001, і обсяг продажів якого перевищує 1000, або рядки, в яких зазначене прізвище менеджера «Иванов».

Після заповнення діапазону умов потрібно викликати розширений фільтр за допомогою команди «Данные Фильтр Расширенный фильтр». У вікні цієї команди уточнюється, де знаходиться список, де знаходиться діапазон умов і чи треба копіювати фільтровані дані в нове місце чи фільтрувати список на місці. Якщо дані треба скопіювати в нове місце, то варто вказати адресу діапазону, куди їх потрібно копіювати. Після виконання цієї команди відбувається фільтрація даних у списку за умовами, зазначеними у розширеному фільтрі.

2.1. Підведення проміжних підсумків і консолідація даних

Перейдемо тепер до обчислення підсумків у списках даних. Кожному менеджеру після збору оперативних даних доводиться в тому чи іншому вигляді підводити підсумки. Взагалі кажучи, це операція багатогранна. Іноді треба просто підрахувати суму  стовпця, а іноді потрібно побудувати складну зведену таблицю, щоб з'ясувати, хто і скільки заробив. Щоб підрахувати табличну суму по рядку чи стовпцю, треба натиснути кнопку «» на панелі інструментів. Але при підведенні проміжних підсумків по групах даних, наприклад, по прізвищах  продавців чи сортам товарів, треба використовувати інші засоби. Отже, розглянемо коротко основні способи підведення підсумків.

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

Якщо, наприклад, необхідно визначити обсяги продажів кожного продавця, то групування потрібно проводити по прізвищах продавців, а підсумовування по обсягах продажів.

Оскільки Excel автоматично вставляє проміжні підсумки при кожній зміні даних у стовпці групування, то для одержання коректних результатів треба, насамперед, відсортувати список по стовпцю групування. Після цього треба виконати команду «Данные Итоги...» і у вікні, що відкрилося, вказати стовпець групування, підсумкову операцію і стовпець, по якому підводяться підсумки (див. рис. 4).

Можна в будь-який момент зняти проміжні підсумки зі списку даних, поставивши курсор на одне з проміжних значень і виконати команду «Данные Итоги...». Потім у вікні, що відкрилося, потрібно натиснути кнопку «Убрать все» (див. рис. 4).

Рис.4. Вікно підведення підсумків

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

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

Перед виконанням консолідації даних потрібно виділити діапазон комірок на підсумковому аркуші, куди будуть записані результуючі дані, і виконати команду «Данные Консолидация...». У вікні цієї команди (див. рис. 5) потрібно вказати агрегуючу функцію (сума, середнє, добуток і т.д.) і список діапазонів з вихідними даними.  

Рис.5. Вікно консолідації даних

Для вказівки діапазонів у цьому вікні потрібно ввести адресу чергового діапазону в поле «Ссылка» і натиснути кнопку «Добавить» (чи «Удалить», якщо діапазон був зазначений невірно). Після додавання всіх діапазонів потрібно натиснути кнопку «ОК» і процесор обчислить результуючі значення. При вказівці діапазонів тут, як правило, використовуються тривимірні посилання, наприклад, посилання «Лист1!F5» вважається тривимірною, оскільки тут явно зазначений аркуш, на якому знаходиться комірка. Якщо комірки на вихідних аркушах розташовані однаково, то можна використовувати тривимірні діапазони. Наприклад, формула =СУММ (Лист1:Лист3!F5) підсумовує значення на трьох робочих аркушах в комірках F5.

Якщо консолідація проводиться по списках даних з однаковими заголовками, розташованими в рядку чи стовпці, то у вікні консолідації варто включити кнопку «подписи верхней строки» чи відповідно «значения левого столбца». Якщо необхідно створити зв'язок результуючих даних з вихідними, то варто включити кнопку «Создавать связи с исходными данными». У цьому випадку результуючі дані будуть змінюватися відразу після зміни вихідних даних.

2.2. Побудова зведених таблиць

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

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

Таких параметрів може бути багато, але, щоб зберегти видимість результуючої таблиці, звичайно використовують не більше чотирьох параметрів. Оскільки таблиці двовимірні, то значення одного узагальнюючого параметра розташовуються по горизонталі (вісь Х), значення другого по вертикалі (вісь У) і, нарешті, значення третього параметра (вісь Z) роблять зведену таблицю тривимірною. Четвертий підсумковий (агрегуючий) параметр використовується для заповнення зведеної таблиці числовими значеннями. Звичайно, і по горизонталі і по вертикалі можна розташовувати при необхідності кілька параметрів, а параметр Z використовується в основному для фільтрації двовимірних таблиць за значенням цього параметра. Відмітимо відразу, що зведені таблиці надають користувачу можливість фільтрації даних по будь-якій координаті і за значеннями будь-яких параметрів.

Наприклад, припустимо, що менеджеру треба довідатися, скільки і яких цукерок було продано за місяць у декількох магазинах. Тоді по горизонталі можна розташувати назви цукерок, по вертикалі - дати продажів і, нарешті, по третій координаті можна розташувати назви магазинів. Як підсумковий параметр тут можна використовувати обсяги продажів у вартісному чи кількісному виразі. У цьому випадку зведена таблиця буде показувати, скільки і коли було продано цукерок певного сорту у всіх магазинах. Якщо таблицю профільтрувати по осі Z, вибравши на ній значення «Центральный», то таблиця покаже, скільки і коли було продано цукерок певного сорту в центральному гастрономі і т.д.  

Для побудови зведеної таблиці треба поставити курсор в комірку, де буде розташовуватися зведена таблиця, чи на вихідний список даних і виконати команду «Данные Сводная таблица…». З'явиться вікно майстра зведених таблиць, що за три кроки будує таблицю.

  •  На першому кроці треба вказати тип джерела даних і, звичайно, тут треба залишити (чи вибрати) першу альтернативу «в списке или базе данных Microsoft Excel», оскільки, як правило, вихідні дані знаходяться в тому ж документі Excel, де будується зведена таблиця. При необхідності як джерело можна вказати зовнішню базу даних. Крім того, на першому кроці треба вказати вигляд створюваного звіту ( таблиця чи таблиця разом з діаграмою). Наприкінці кожного кроку треба натискати кнопку «Далее»;
  •  На другому кроці треба вказати місце розташування вихідного списку даних. Якщо курсор стояв на вихідному списку, то процесор сам може визначити адресу діапазону, де знаходиться список. У противному випадку треба явно вказати адресу списку, переключившись на робочий аркуш, де знаходиться вихідний список, і обвести мишею весь список. Потім натисніть кнопку «Далее»;
  •  На третьому (самому головному) кроці треба задати структуру (макет) зведеної таблиці. Тут, насамперед, потрібно натиснути кнопку «Макет...», щоб почати побудову структури таблиці. Не слід відразу натискати кнопку «Готово», тому що макет таблиці все рівно прийдеться будувати, але в інтерактивному режимі, коли процесор буде супроводжувати кожну дію користувача безпосередньою побудовою таблиці. Утім, тут користувач може діяти за власним смаком. Після натискання на кнопку «Макет» з'явиться вікно задання структури таблиці (див. рис. 6). Тут потрібно перетягнути назви полів вихідного списку даних на осі, зведеної таблиці, що будується  і вказати агреговане поле, по якому будуть обчислюватися підсумкові значення. Якщо назву поля перетягнути в зону «Столбец», то вона буде розташована в зведеній таблиці по горизонталі (вісь Х), якщо в зону «Строка», то по вертикалі (вісь У), якщо в зону «Страница», то по осі Z (третя координата). Якщо назву поля перетягнути в зону «Данные», то по ньому буде обчислюватися підсумкова функція. Звичайно, у цю зону варто перетягувати тільки числові поля. Підсумковою функцією за замовчуванням є сума, але її можна змінити, двічі клацнувши лівою кнопкою миші по полю в зоні «Данные». Щоб закінчити побудови макета таблиці, потрібно клацнути по кнопці «ОК». Після цього знову з'явиться третє вікно майстра зведених таблиць, у якому можна уточнити місце розташування зведеної таблиці. Крім того, тут можна натиснути на кнопку «Параметры» і задати назву зведеної  таблиці чи змінити її зовнішній вигляд. Після закінчення всіх підготовчих дій потрібно натиснути на кнопку «Готово» і зведена таблиця з'явиться на робочому аркуші, починаючи з зазначеної адреси.  

Рис.6. Макет зведеної таблиці

Після створення зведеної таблиці з'являється панель інструментів «Сводные таблицы», за допомогою якої можна виконати наступні дії:

  •  Відформатувати таблицю, натиснувши кнопку «Формат отчета», і застосувати механізм авто форматування;
  •  Побудувати діаграму по зведеній таблиці, натиснувши на кнопку «Мастер диаграмм». При побудові діаграми варто видалити з таблиці загальні суми по рядках і по стовпцях і сховати в таблиці всі зайві елементи;
  •  Змінити макет таблиці, натиснувши на кнопку «Мастер сводных таблиц». Після цього знову з'явиться третє вікно майстра, в якому через кнопку «Макет» відкривається вікно, показане на рис. 6. Тут можна видалити поле з таблиці, забравши його за межі  макета чи ввести нове поле в макет, помістивши його у відповідну зону;
  •   Показати чи сховати детальну інформацію про поля таблиці. Якщо двічі клацнути мишею по внутрішній комірці таблиці, то детальна інформація про цей елемент таблиці буде скопійована на новий робочий аркуш. Якщо двічі клацнути по заголовку  рядка чи стовпця зведеної таблиці, то з'явиться вікно «Показать детали», у якому можна додати до даного поля інші поля вихідного списку. Потім кнопками «Скрыть детали» чи «Показать детали» можна змінювати вигляд зведеної таблиці,  ховаючи чи розкриваючи детальну інформацію про це поле;
  •  Обновити дані в зведеній таблиці, натиснувши на кнопку «Обновить данные». Зведена таблиця зв'язується з вихідним списком, і після зміни вихідних даних необхідно  таким чином обновляти дані в зведеній таблиці;
  •  Змінити формат будь-якої комірки в таблиці, натиснувши на кнопку «Параметры поля», а потім на кнопку «Формат...»;
  •  Сховати чи відобразити поля вихідного списку на панелі інструментів «Сводные таблицы», натиснувши на кнопку «Скрыть поля» чи «Отобразить поля».

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

Фінансовим менеджерам повсякденно доводиться аналізувати ефективність інвестицій, розраховувати терміни погашення кредитів, позик і позичок, визначати вигідність вкладень з урахуванням банківських відсотків і т.д. Процесор Excel містить багатий набір фінансових функцій, що дозволяють розв’язувати більшість з цих задач. Розглянемо основні фінансові функції в Excel і покажемо, як за їх допомогою можна розв’язувати стандартні задачі фінансового аналізу. З іншими фінансовими функціями, а їх більше 50, студенти можуть познайомитися в міру необхідності [4 ].

Всі фінансові функції в Excel використовують приблизно той самий набір аргументів, що наведені в наступній таблиці, але фінансовий зміст цих функцій може залежати від знака аргументів. Тут можна дотримуватися наступного правила: якщо гроші віддаються, то аргумент береться зі знаком мінус, у противному випадку аргумент повинен бути більше нуля.

Таблиця 4. Аргументи фінансових функцій

Аргумент

Призначення

Ставка (Норма)

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

Кпер

Кількість періодів (тривалість) фінансової операції.

ПЛТ (Виплата)

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

Пс

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

Бс

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

Тип

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

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

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

Значення фінансових змінних, наведені в таблиці 4, задовольняють наступному співвідношенню, що виражає нарахування складного банківського відсотка (за умови, що «Ставка» не дорівнює нулю):

.   (1)

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

1. Функція БС

Функція БС (майбутня вартість) повертає майбутню суму внеску, зробленого на основі постійної процентної ставки. Ця функція виражає значення «Бс» через інші чотири аргументи, виходячи зі співвідношення (1). Вона використовується звичайно для оцінки доцільності проведення банківських вкладів (депозитів). Синтаксис виклику цієї функції наступний:

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

Тут «Пс» означає одноразову виплату, що проводиться на початку операції. Хоча третій і четвертий аргументи необов'язкові, але, принаймні, один з них повинен бути зазначений. Зі співвідношення (1) видно, що значення функції буде позитивним, якщо третій чи четвертий аргументи негативні.

Задача 2. Фірма вирішила відкладати на депозит по 10000 у.е. щорічно під 14% річних. Скільки буде на рахунку фірми через 3 роки? Розв’язання. Викликати функцію БС з аргументами: Ставка = 14%, Кпер=3, Плт = -10000, (Пс = 0). Відповідь: 34396.

Задача Фірма вирішила покласти на депозит 10000 у.е. під 14% річних. Скільки буде на рахунку фірми через 3 роки. Розв’язання. Викликати функцію БС з аргументами: Ставка = 14%, Кпер = 3, (Плт = 0), Пс = -10000. Відповідь: 14815,44.

Задача 4. Передбачається вкласти в банк 1000 у.е. під 6% річних і вкладати потім по 100 у.е. на початку кожного з наступних 12 місяців. Скільки грошей буде на рахунку наприкінці 12 місяців? Розв’язання. Викликати функцію БС з аргументами: Ставка = 6%/12 = 0,5%, Кпер = 12, Плт = -100, Пс = -1000, Тип = 1. Відповідь: 2301,40.

2. Функція КПЕР

Функція КПЕР (кількість періодів) повертає загальну тривалість виплати для даної позики чи депозиту на основі постійної процентної ставки. Ця функція виражає значення «Кпер» через інші чотири аргументи, виходячи зі співвідношення (1). Вона використовується для оцінки термінів зворотності позик і тривалості депозитів. Синтаксис виклику цієї функції наступний:

КПЕР(Ставка, Плт, Пс, Бс, Тип);

Третій і четвертий аргументи необов'язкові, але, принаймні, один з них повинен бути зазначений. Використовувати цю функцію можна для оцінки тривалості позик, коли Пс > 0 і Бс = 0, чи для оцінки тривалості депозитів, коли Пс  0 і Бс > 0. Функція повертає звичайно дробове значення, тому варто встановити в комірці виклику формат цілих  чисел чи заокруглити результат до цілого числа, щоб одержати ціле число періодів.

Задача 5. Скільки місяців знадобиться для виплати позики в 1000 у.е., взятої під 12% річних, якщо передбачається виплачувати по 100 у.е. щомісяця? Розв’язання. Викликати функцію КПЕР з аргументами: Ставка = 12%/12 = 1%, Плт = -100, Пс = 1000, (Бс = 0). Відповідь: 10,58.

Задача 6. Передбачається вкласти в банк 1000 у.е. під 12% річних і вкладати потім по 100 у.е. щомісяця, поки на рахунку не стане 10000. Скільки місяців необхідно для цього?  Розв’язання. Викликати функцію КПЕР з аргументами: Ставка = 12%/12, ПЛТ = -100, Пс= -1000, Бс = 10000. Відповідь: 60.

Функція ПС

Функція ПС (минула (зведена) вартість) повертає поточний обсяг внеску, тобто суму, яку потрібно покласти, щоб одержати одноразовий еквівалент майбутніх періодичних  виплат чи щоб досягти в майбутньому заданого балансу. Ця функція виражає значення «Пс» через інші чотири аргументи, виходячи зі співвідношення (1). Вона використовується для оцінки прибутковості депозитів та інвестицій. Ця функції має наступний синтаксис:

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

Третій і четвертий аргументи необов'язкові, але, принаймні, один з них повинен бути зазначений. Якщо Плт < 0 і Бс = 0, то функція ПС повертає одноразовий еквівалент періодичних виплат, якщо ж Плт = 0 і Бз > 0, то вона показує, як досягти заданого балансу.

Задача 7. Банк пропонує Вам викупити приміщення відразу за 10000 у.е. чи платити по 600 у.е. щомісяця протягом двох років при ставці 8% річних. Визначите, який з цих варіантів вигідніший. Розв’язання. Викликати функцію ПС з аргументами Ставка = 8%/12 = 0,6%, Кпер = 24, Плт = -600, (Бс = 0). Відповідь: Функція ПС повертає значення більше 13000, отже, перший варіант є більш вигідним.

Задача 8. Яку суму потрібно покласти в банк на депозит, щоб через 4 роки вона досягла 100000 у.е. при нарахуванні 10% річних? Розв’язання. Викликати функцію ПС з аргументами Ставка = 10%, Кпер = 4, (Плт = 0), Бс = 100000. Відповідь: -68301,35. Відповідь негативна, оскільки гроші потрібно віддати.

4. Функція ПЛТ

Функція ПЛТ (періодична плата) обчислює величину періодичної виплати по позичці на основі постійної процентної ставки. Ця функція виражає значення «Выплата» через інші чотири аргументи, виходячи зі співвідношення (1). Вона використовується для оцінки обсягу періодичних виплат по позичках, позикам і кредитам. Ця функція має наступний синтаксис:

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

Третій і четвертий аргументи необов'язкові, але, принаймні, один з них повинен бути зазначений. Якщо Пс > 0 і Бс  0, то функція ПЛТ повертає суму періодичної виплати по позиці, якщо ж Пс  0 і Бс > 0, то по депозиту.

Задача 9. Банк дає в кредит 50000 у.е. на придбання житла під 10% річних з розстрочкою виплат на 20 років. При цьому 20% кредитної суми потрібно виплатити відразу. Оцініть суму щомісячних виплат по цьому кредиту. Розв’язання. Викликати функцію ПЛТ з аргументами Ставка = 10%/12 = 0,8%, Кпер = 20*12 = 240, Пс = 50000, Бс = - 50000*20/100 = - 10000.

Відповідь: -469,34. Відповідь негативна, оскільки гроші потрібно віддавати.

Задача 10. Передбачається вкласти в банк 2000 у.е. під 12% річних і потім вкладати гроші щорічно протягом 5 років, поки на рахунку не буде 10000. Оцініть суму щорічних виплат по цьому депозиту. Розв’язання. Викликати функцію ПЛТ з аргументами Ставка = 12%, Кпер = 5, Пс = - 2000, Бс = 10000. Відповідь: - 1019,28.


Список літератури до лекції 3

1. Бахонський О.В. і др. Табличний процесор Microsoft Excel. Частина 1 і 2. - К.: МАУП, 2002.

2. Додж М., Стинсон К. Эффективная работа с Microsoft Excel 2000. – СПб.: Питер, 2000.

Рыжков В. Самоучитель Excel 2000. – СПб.: Питер, 1999.

4.  Бухвалов А., Бухвалова В., Идельсон А. Финансовые вычисления для профессионалов. – СПб.: BHV, 200


 

А также другие работы, которые могут Вас заинтересовать

73827. Системы уравнений в линейной алгебре 467.5 KB
  Если это определение озвучить в терминах определителей то оно будет выглядеть примерно так: Матрица размера m×n имеет ранг r если существует хотя бы один отличный от нуля определитель rго порядка тогда как определитель любой подматрицы более высокого порядка равен нулю. Для вычисления ранга матрицы можно использовать метод элементарных преобразований строк и столбцов – в точности тот самый метод который применяется для вычисления определителей. Целью элементарных преобразований является приведение матрицы к...
73828. Модель затраты- выпуск (модель В. Леонтьева) 121 KB
  Либо не весь объём производства расходуется на потребление и его достаточно для расширения производства тех видов продукции на которые имеется растущий спрос либо объём производства недостаточен для воспроизводства трудового ресурса на постоянном уровне. Свойство наличия баланса состоит как раз в том что полные объёмы всей продукции складываются только из объёмов её конечного потребления и объёмов потребления продукции в производственных процессах межотраслевых потоков. Примером такой взаимосвязи может служить например потребление с х...
73829. Комплексные числа 388 KB
  Определение комплексного числа. Первая компонента комплексного числа действительное число называется действительной частью числа это обозначается так; вторая компонента действительное число называется мнимой частью числа. Два комплексных числа и равны тогда и только тогда когда равны их действительные и мнимые части.
73830. Многочлены -ой степени 536.5 KB
  Многочленом ой степени называется функция где постоянные комплексные числа коэффициенты многочлена комплексная переменная. Число в котором многочлен принимает нулевое значение называется корнем многочлена. Представим в виде многочлена по степеням. Очевидно отсюда следует утверждение: для того чтобы число было корнем многочлена необходимо и достаточно чтобы коэффициент при нулевой степени в разложении по степеням был равен нулю: .
73831. Линейные пространства 451.5 KB
  Обозначим множества векторов направленных отрезков на прямой на плоскости в пространстве соответственно с обычными операциями сложения векторов и умножения векторов на число. Вместо свободных векторов можно рассмотреть соответствующие множества радиус-векторов. Например множество векторов на плоскости имеющих общее начало т. Множество радиус-векторов единичной длины не образует линейное пространство так как для любого из этих векторов сумма не принадлежит рассматриваемому множеству.
73832. Проектирование операционных технологических процессов обработки заготовок 67.5 KB
  обработки позволяет правильно выбрать станок из имеющегося парка или по каталогу. По типу обработки устанавливают группу станков: токарный сверлильный В соответствии с назначением станка его компоновкой степенью автоматизации определяют тип станка: токарный одношпиндельный многошпиндельный револьверный полуавтомат и т. Если эти требования выполнимы на различных станках то при выборе учитывают следующие факторы: 1 соответствие основных размеров станка габаритным размерам обрабатываемой заготовки или нескольких одновременно...
73833. Анализ технологичности конструкции деталей 43 KB
  Ее следует отрабатывать на технологичность комплексно учитывая зависимость технологичности от следующих факторов: исходной заготовки вида обработки технологичности СЕ в которую эта деталь входит. Конструкция должна быть такой чтобы для ее изготовления можно было применять высокопроизводительные методы обработки. Повышение технологичности конструкции изделия предусматривает проведение следующих мероприятий: Создание конфигурации деталей и подбор их материалов позволяющих применение наиболее совершенных исходных заготовок сокращающих объем...
73834. Выбор вариантов схем базирования 40.5 KB
  Для создания возможности повышения уровня концентрации обработки в операции и снижения разнообразия технологической оснастки лучше принять в качестве базы для обработки всех поверхностей детали одну и туже базу – Е. Синтез маршрута обработки заготовки Первый шаг синтеза маршрута обработки заготовки – распределение отобранных переходов обработки типовых поверхностей заготовки по этапам типовой схемы изготовления деталей соответствующего класса или подкласса. Типовая схема обработки является вариантом полного типового решения. Причиной...
73835. Проектирование маршрутных технологических процессов механической обработки 52 KB
  Маршрутное описание ТП заключается в сокращенном описании всех технологических операций в маршрутной карте в последовательности их выполнения без переходов и технологических режимов. Операционное описание ТП характеризуется полным описанием всех технологических операций в последовательности их выполнения с указанием переходов и технологических режимов. Маршрутнооперационным описанием ТП называют сокращенное описание технологических операций в маршрутной карте в последовательности их выполнения с полным описанием отдельных операций в других...