Зауваження

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

Можливості функції “НОРМА” розглянемо на наступному прикладі.

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

Вас цикавить, яка буде процентна ставка по цьому кредиту?

Починаемо працювати з функцією “НОРМА”, яка обраховує значення процентної ставки.

Встановлюємо табличний курсор у клітинку D2 і починаємо працювати з функцією НОРМА.

Процентна ставка дорівнює 0, 77%. Це місячна процентна ставка, тому що період погашення дорівнює 1 місяцю. Річна процентна ставка складе 0, 77%*12 = 9, 24%.

У цьому прикладі параметр Тип = 0, тобто погашення кредиту проводиться у кінці місяця.

КПЕР

Використання функції
“КПЕР”
для вирішення фінансових задач

Обраховує скільки разів буде зроблено виплати з цього рахунку, при фіксованих розмірах виплат та фіксованій % ставці.

Синтаксис функції КПЕР

КПЕР(Норма; Выплата; Нз; Бс; Тип)

Де,

Норма - % ставка за період;

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

Нз — поточна сума, або загальна сума усіх майбутніх платежів з поточного момента.

Бс – сума, яку маємо досягнути, або баланс готівки, якого необхідно досягнути після останнього платежу; при відсутності значення БС = 0 (наприклад, для займу Бс = 0);

Тип — це число 0 або 1, що вказуе у якій час необхідно робити виплату. Якщо аргумент Тип відсутній, то він приймається рівним 0.

Можливості функції “КПЕР” розглянемо на наступному прикладі.

Ви маєте рахунок у банку розміром 25000 грн. Річна % ставка, що сплачує банк 38, 16%, відсоток нараховується щоквартально. Кожен квартал, з рахунку, банк виплачує фіксовану суму 1000 грн.

Необхідно визначити, за яку кількість періодів, при цих умовах, на рахунку буде 90000 грн. (необхідно пам’ятати, що період розрахунків 1 квартал).

Створимо наступну таблицю і почнемо працювати з функцією “КПЕР”.

Встановлюємо табличний курсор у клітинку Е2 і починаємо працювати з функцією КПЕР.

Кликнути мишкою по кнопці ОК.

Встановлюємо табличний курсор у клітинку F2 і вводимо формулу, яка перераховує кількість періодів виплат у кількість років.

2.4.5.5.7. Функції для роботи з матрицями

В Excel передбачено кілька функцій, призначених для роботи з матрицями.

МУМНОЖ (MMULT)

Функція МУМНОЖ (MMULT) має два аргументи: перший - масив1; другий - масив2. Вона повертає добуток матриць, розміщених у масивах. Результат - масив з таким самим числом рядків, як і масив1, та таким самим числом стовпців, як масив2.

Як приклад роботи з матрицями розглянемо операцію множення матриці (клітинки А1: С4) на матрицю (клітинки Е1: Е3) (рис. 2.44). Щоб здобути результат, кількість стовпців першої матриці має дорівнювати кількості рядків другої матриці, тобто 3=3.

Рис. 2.44. Вихідні матриці та виділена область для результату множення

Перш ніж звернутися до Мастера функций, необхідно віділити діапазон клітинок, де буде поміщений результат множення (клітинки G1: G4). В цому діапазоні за правилом добутку матриць має бути стільки рядків, скільки їх є в першій матриці (в даному прикладі - 4), і стільки стовпців, скільки їх у другій матриці (в даному прикладі - 1) (рис. 2.44).

Далі з допомогою Мастера функций вибираємо функцію МУМНОЖ (MMULT) з категорії Математические и тригонометрические функции і вказуємо діапазони клітинок, де розміщені матриці (А1: С4) і (Е1: Е3), що перемножуються (рис. 2.45, 2.46).

Коли Мастер функций підготує функцію, то в клітинці G1 з'явиться перший елемент матриці-результату. Для відтворення інших елементів матриці-результату (клітинки G2: G4) необхідно натиснути клавішу < F2>, а потім комбінацію клавіш < Ctrl+Shift+Enter>.

Примітка. Для одержання результату при будь-яких операціях з матрицями натискання клавіші < F2>, а потім комбінації клавіш < Ctrl+Shift+Enter> обов'язкове.

Рис. 2.45. Крок 1 Мастера функций

Рис. 2.46. Крок 2 Мастера функций

Після виконання цих дій у клітинках G1: G4 буде відображено результат цієї операції (рис. 2.47).

Рис. 2.47. Результат множення матриць

МОБР (MINVERSE)

Функція МОБР (MINVERSE) має один аргумент - масив. Вона повертає обернену матрицю. Результат - це масив з таким самим числом рядків і стовпчиків, як і вихідний масив.

МОПРЕД (MDETERM)

Функція МОПРЕД (MDETERM) має один аргумент - масив. Вона повертає визначник матриці. Результат - число.

ТРАНСП (TRANSPOSE)

Функція ТРАНСП (TRANSPOSE) має один аргумент - масив. Вона повертає транспоновану матрицю. Транспонування полягає в тому, що перший рядок вихідного масиву стає першим стовпчиком транспонованого масиву, другий рядок - другим стовпчиком тощо.

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

До категорії Статистические віднесено 71 функцію, що дає змогу виконувати різноманітні розрахунки.

СРЗНАЧ (AVERAGE)

Функція СРЗНАЧ (AVERAGE) повертає середнє значення (середнє арифметичне) діапазону клітинок, що еквівалентне сумі вмісту всіх клітинок діапазону, поділеній на кількість клітинок у діапазоні. Наприклад, формула

=СРЗНАЧ(А1: А100) або =AVERAGE(А1: А100)

повертає середнє значення діапазону А1: А100.

Якщо в заданому діапазоні є пусті клітинки або клітинки з текстом, то вони не включаються в розрахунок середнього значення. Як і у випадку з функцією СУММ (SUM), користувач може використовувати будь-яку кількість аргументів.

Примітка. В Excel передбачені також функція МЕДИАНА (MEDIAN), яка розраховує медіану значень діапазону, і функція МОДА (MODE), яка повертає значення, що найчастіше трапляється в діапазоні.

МАКС (MAX) і МИН (MIN)

Функція МАКС (MAX) використовується для визначення найбільшого значення з діапазону, а функція МИН (MIN) - найменшого. Наступна формула відтворює на екрані найбільше і найменше значення діапазону клітинок А1: А50:

=" Найменше: " & МИН(А1: А50)& " Найбільше: " & МАКС(А1: А50)

або

=" Найменше: " & МIN(А1: А50)& " Найбільше: " & МАX(А1: А50)

Наприклад, якщо в клітинках діапазону А1: А50 вміщені числа від 7 до 385, то ця формула повертає рядок

Найменше: 7 Найбільше: 385.

НАИБОЛЬШИЙ (LARGE) і НАИМЕНЬШИЙ (SMALL)

Функція НАИБОЛЬШИЙ (LARGE) повертає n-не найбільше значення з вміщених у діапазон клітинок. Наприклад, для відтворення на екрані другого найбільшого значення діапазону клітинок А1: А50 використайте формулу

=НАИБОЛЬШИЙ(А1: А50; 2) або =LARGE(А1: А50; 2)

Функція НАИМЕНЬШИЙ (SMALL) повертає n-не найменше значення з вміщених у діапазон клітинок.

ДИСП (VAR)

Функція ДИСП (VAR) розраховує дисперсію випадково вибраних n чисел, причому їх кількість не може бути більшою від 30. Її аргументи (число1; число2;...). Ця функція передбачає, що її аргументи є вибіркою з генеральної сукупності. Якщо аргументами є вся генеральна сукупність, то необхідно використовувати функцію ДИСПР (VARР).

ДОВЕРИТ (CONFIDENCE)

Функція ДОВЕРИТ (CONFIDENCE) розраховує довірчий інтервал для середнього генеральної сукупності. Її аргументи (альфа; станд_откл; размер). Довірчий інтервал - окіл середнього вибірки (інтервал, який включає значення середнього вибірки, що рівновіддалений від кінців інтервалу). Наприклад, якщо товар був замовлений поштою, то можна визначити з певним рівнем надійності найбільш ранню та найбільш пізню дату прибуття товару.

Альфа. Рівень значущості, що використовується для розрахунку рівня надійності. Рівень надійності дорівнює 100*(1-a). a, що дорівнює 0, 05, означає 95%-й рівень надійності.

Станд_откл. Стандартне відхилення генеральної сукупності для інтервалу даних (передбачається відомим).

Размер. Розмір вибірки.

КВАДРОТКЛ (DEVSQ)

Функція КВАДРОТКЛ (DEVSQ) розраховує суму квадратів відхилень точок даних від їх середнього. Її аргументи (число1; число2;...). Кількість аргументів не може перевищувати 30. Можна використовувати масив чи посилання на масив замість аргументів, що розділені крапкою з комою.

КВПИРСОН (RSQ)

Функція КВПИРСОН (RSQ) розраховує квадрат коефіцієнта кореляції Пірсона для точок даних в аргументах " известн_знач_х" та " известн_знач_у". Значення r-квадрат можна інтерпретувати як відношення дисперсії у до дисперсії х. Її аргументи (известн_знач_у; известн_знач_х).

Известн_знач_у. Масив чи інтервал точок даних у.

Известн_знач_х. Масив чи інтервал точок даних х.

КОВАР (COVAR)

Функція КОВАР (COVAR) розраховує коваріацію (середній добуток відхилень для кожної пари точок даних). Її аргументи (масив1; масив2;...).

КОРРЕЛ (CORREL)

Функція КОРРЕЛ (CORREL) розраховує коефіцієнт кореляції між масивами даних (масив залежних даних у і масив незалежних даних х). Її аргументи (масив1; масив2). Використовується для визначення щільності лінійного зв'язку між двома показниками.

ЛИНЕЙН (LINEST)

Функція ЛИНЕЙН (LINEST) розраховує оцінки параметрів b -лінійної регресії, використовуючи метод найменших квадратів:

Y=b ₀ + b ₁x₁ + b ₂x₂ +... + b _kx_k або Y=Xb + b ₀,

де залежне значення у є функцією незалежного значення х. b -матриця значень кутового коефіцієнта підсумовуючої прямої найкраще апроксимує наявні дані: b ₀ - абсциса точки перетину прямої з віссю Y. Її аргументи (известн_знач_у; известн_знач_х; конст; статистика). Функція може додатково розраховувати регресійну статистику.

Известн_знач_у. Масив значень у. Якщо масив у має один стовпчик, то кожний стовпчик масиву " известн_знач_х" інтерпретується як окрема змінна. Якщо масив у має один рядок, то кожний рядок масиву " известн_знач_х" інтерпретується як окрема змінна.

Известн_знач_х. Масив значень х, що вміщує або одну змінну (парна регресія), або кілька змінних (множинна регресія). Якщо аргумент " известн_знач_х" пропущений, то вважається, що це масив {1; 2; 3;...} такого самого розміру, як і масив " известн_знач_у".

Конст. Логічне значення. Якщо " конст" має значення " ложь", то b ₀ береться таким, що дорівнює нулю, і значення b підбирається так, щоб виконувалось рівняння Y=Xb (модель без вільного члена). Якщо " конст" має значення " истина", то b ₀ розраховується традиційно (модель з вільним членом).

Статистика. Логічне значення, яке вказує, чи треба підраховувати додаткову статистику за регресією. Якщо " статистика" має значення " истина", то функція ЛИНЕЙН (LINEST) розраховує додаткову регресійну статистику у вигляді масиву (рис. 2.48).

Рис. 2.48. Статистика функції ЛИНЕЙН

В таблиці на рис. 2.48 використані такі позначення:

b ^{^}_j - оцінка параметра b _j, j= 1, 2,..., k.

b ^{^}₀ - оцінка вільного члена регресії.

s b _j - стандартна помилка оцінки параметра.

R² - коефіцієнт детермінації.

s _y - стандартна помилка.

F - статистика.

Ступінь волі - дорівнює (n-k-1), де n - кількість спостережень, а k - кількість незалежних значень х у моделі. Цей параметр є необхідним для знаходження табличного значення F-критичного.

S (Y^{^}₁-Y)² - сума квадратів відхилення, що пояснюється регресією.

S (Y^{^}₁-Y₁)² - сума квадратів відхилення, що пояснюється похибкою e.

Якщо статистика має значення " ложь" або її пропущено, то функція ЛИНЕЙН (LINEST) повертає тільки коефіцієнти b та константу b ₀.

ТЕНДЕНЦИЯ (TREND)

Функція ТЕНДЕНЦИЯ (TREND) передбачає, що тенденція зміни залежності змінної у, що була виявлена за " известн_знач_х", буде збережена. Її аргументи (" известн_знач_у"; " известн_знач_х"; конст).

Наприклад, за даними спостережень зростання випуску продукції за попередні 12 місяців (рис. 2.49) можна обчислити обсяг очікуваного випуску у наступних двох місяцах (13-му та 14-му).

Рис. 2.49. Результати обчислення динамічного ряду

Відомі значення випуску продукції Y вміщені в клітинках (В2: В13) робочої таблиці, в клітинках (А2: А13) - місяці. Це відомі значення Х. Введені до клітинок А14 і А15 значення наступних двох місяців будуть новими значеннями Х (рис. 2.49).

Виділимо дві клітинки В14 і В15 для виведення результату і викличемо з допомогою Мастера функций функцію ТЕНДЕНЦИЯ (TREND) (рис. 2.50).

Рис. 2.50. Другий крок виконання функції ТЕНДЕНЦИЯ

Клацнемо мишкою на кнопці Готово, а потім натиснемо клавішу < F2> i комбінацію клавіш < Ctrl+Shift+Enter>. Результат (очікувані значення випуску продукції) з'явиться в клітинках В14 та В15 (рис. 2.49).

Тенденцію зміни Y від зміни виробничих факторів Х1 (Трудові ресурси) та Х2 (Виробничі фонди) в наступному періоді можна отримати, якщо продовжити ряд відомих значень Х1 та Х2 в таблиці.

Рис. 2.51. Другий крок виконання функції ТЕНДЕНЦИЯ

Наприклад, нові значення Х1=10 та Х2=14 вмістимо в клітинки С15 і D15. Тоді у позначеній клітинці В15 за допомогою функції ТЕНДЕНЦИЯ (TREND) (рис. 2.51) отримаємо очікуване значення Y (рис. 2.52).

Рис. 2.52. Залежність зміни випуску продукції від зміни виробничих факторів

2.4.5.5.9. Функції роботи з базою даних

До цієї категорії в Excel віднесено 12 функцій. Вони використовуються при роботі з табличними базами даних (також відомими, як списки), що зберігаються в аркуші робочої таблиці. Всі ці функції в англомовній версії Excel починаються з букви D і мають еквівалентні функції, не пов'язані з роботою над базами даних. Наприклад, функція БДСУММ (DSUM) є спеціальним варіантом функції СУММ (SUM), яка підраховує суму значень бази даних, що відібрані за заданим критерієм.

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

Зауваження. Для використання функцій роботи з базою даних користувач повинен спочатку визначити спеціальний діапазон критеріїв відбору елементів робочої таблиці. Цей діапазон збігається з тим, що використовується в команді Данные та її опціях Фильтр і Расширенный фильтр (Data Þ Filter Þ Advanced Filter).

Приклад. На рис.2.53 відображена таблична база даних зі створеним діапазоном критеріїв у клітинках А1: В2. Зверніть увагу, що діапазон критеріїв може бути розміщений у будь-якому місці робочої таблиці (вище або нижче від списку).

Рис. 2.53. Таблична база даних з діапазоном критеріїв у клітинках А1: В2

Функція БДСУММ (DSUM) визначає загальний обсяг продажів у конкретному регіоні, який вибрано відповідно до заданого діапазону критеріїв. Наприклад, для підрахунку суми продажів по східному регіону в поле Регіон діапазона критеріїв (клітинка А2) введіть рядок =" Схід". Після цього в будь-яку вільну клітинку аркуша робочої таблиці (наприклад, клітинка D2) запишіть формулу такого типу:

=БДСУММ(ДиапазонСписка; НазваниеПоля; Критерий)

або

=DSUM(ListRange; FieldName; Criteria)

Для нашого прикладу (див. рис. 2.54) ця формула матиме вигляд:

перший варіант:

=БДСУММ(А4: В15; В4; А1: В2)

або

=DSUM(А4: В15; В4; А1: В2)

другий варіант:

=БДСУММ(А: В; В4; А1: В2)

або

=DSUM(А: В; В4; А1: В2)

Формула повертає суму значень поля Сума продажів тільки тих записів, які задовольняють заданий у діапазоні Критерий критерій (Схід). Можна змінити критерій, і формула підрахує новий результат.

Рис. 2.54. Другий крок виконання функції БДСУММ

2.4.5.5.10. Пошук рішення

Використання функції
“ПОИСК РЕШЕНИЯ”
для вирішення задач виробництва

Розглянемо можливості функції “ПОИСК РЕШЕНИЯ” на конкретному прикладі.

За підсумками поточного року виробництво відображуеться такими показниками