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

15 16 17 18 19 20 21

У випадку щомісячних виплат в ролі аргументів функції виступатимуть:

ставка – річна процентна ставка поділена на кількість місяців в році

кпер – кількість місяців сплати позики

нз – величина залишку розміру першого внеску зі знаком "-"

бз та тип – відсутні.

Для щорічних виплат матимемо:

ставка – річна процентна ставка

кпер – кількість років сплати позики

нз – величина залишку розміру першого внеску зі знаком "-"

бз та тип – відсутні.

Загальна суми щомісячних і щорічних виплат становитиме: періодичні виплати * кількість місяців (у випадку щомісячних виплат) та періодичні виплати * кількість років (для випадку щорічних виплат)

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

6. Потрібно проаналізувати таку угоду. Вас просять позичити 10000 грн. і обіцяють повертати по 2000 грн. протягом 6 років. Чи вигідна ця угода при річній процентній ставці 7%?

Створіть таблицю з такими даними:

Розмір позики	10000 грн.
Термін погашення боргу
Гроші, які повертаються щорічно	2000 грн.
Річна процентна ставка	7%
Чистий поточний об’єм вкладу
Висновок

Чистий поточний об'єм вкладу обчислимо за допомогою функції ПЗ, аргументами якої є адреси відповідних комірок:

ставка – річна процентна ставка;

кпер – термін погашення боргу;

выплата – гроші, які повертаються щорічно;

бз та тип – відсутні.

Для автоматизації створення електронної таблиці, за допомогою якої можна було б аналізувати подібні угоди, варто ввести у комірку, яка йде слідом за коміркою з певним терміном погашення боргу (припустимо, що цей термін записується в комірці В2), формулу:

=ЕСЛИ(В2=1; "рік";ЕСЛИ(И(В2?=2; В2<=4);"роки"; "років"))

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

Для того щоб вивести на екран висновок про вигідність чи недоцільність угоди, у комірку, в якій має цей висновок виводитись, введемо формулу (припустимо, що таблиця розпочинається коміркою А1)

=ЕСЛИ(В1>В5; "Вигідно позичити гроші";

ЕСЛИ(В1=В5; "Варіанти рівноцінні";

"Вигідніше гроші покласти під проценти"))

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

7. Створити таблицю для обчислення основних платежів, плати за проценти, загальної щорічної плати і залишку боргу на прикладі позики 10000 грн. Терміном на 5 років при річній ставці 2%.

Створіть та заповніть таблицю.

Процент	2%
Термін
Щорічна плата
Розмір позики	100000 грн.

Рік	Плата за проценти	Основна плата	Залишок боргу

...

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

Для розрахунку щорічної плати використаємо функцію з відповідними аргументами

=ППЛАТ(процент; термін; -розмір_позики;;)

Основну плату і плату за проценти можна розрахувати за допомогою функцій ПЛПРОЦ та ОСНПЛАТ (із синтаксисом цієї та інших функцій можна ознайомитись у довідковій системі програми):

=ПЛПРОЦ (процент; адреса комірки року;термін; -розмір_позики;;)

=ОСНПЛАТ(лроцент; адреса комірки року;термін; -розмір_позики;;)

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

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

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

Мал. 2.18. Фрагмент аркуша з розв'язком задачі 5 у формульному режимі з відображеними зв 'язками між комірками

8. Створити зведену відомість про результати роботи мережі магазинів у IV кварталі 2000 року (згідно із запропонованими даними) та проаналізувати об'єми реалізації продукції.

Створіть таблицю, використавши дані з табл. 2.7.

Таблиця 2.7.

Виручка мережі магазинів (в млн. грн.)

Магазин	Жовтень	Листопад	Грудень	Сумарна виручка	Місце	Середня виручка	Процент	Діапазони	Кількість







Разом

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

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

Щоб визначити місце магазину за об'ємом продажу, використаємо функцію РАНГ (якщо таблиця починатиметься з комірки А1, то функція для визначення місця першого магазину матиме вигляд:

=РАНГ(ЕЗ; $Е$3:$Е$9)

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

За допомогою функції ЧАСТОТА підрахуємо для множини сумарних виручок магазинів, скільки значень попадають в інтервали від 0 до 1000, від 1001 до 1100, від 1101 до 1200 і більше 1201 млн. грн. У діапазон комірок І3:І6 (за умови, що таблиця починатиметься з комірки А1) введемо верхні межі цих інтервалів 1000, 1100 і 1200, відповідно, а в діапазон комірок J3:J6 введемо формулу {=ЧАСТОТА(ЕЗ:Е9;ІЗ:І5)}.

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

У результаті виконання цієї функції в комірці J3 отримаємо, скільки значень знаходиться в інтервалі від 0 до 1000, в комірці J4 – від 1001 до 1100, в комірці J5 – від 1101 до 1200, в комірці J6 – кількість значень, які не менше 1200.

9. Розрахуйте заробітну плату для 15 працівників підрозділу, якщо нарахування складають погодинну оплату, премію, доплату на дітей та доплату на харчування. Утримання передбачають перерахунки на профспілкові внески, оплату в касу взаємо допомоги, перерахунок в ощадний банк на погашення кредиту та податок. Створіть відомість "До виплати".

– Погодинна оплата складається з оплати за денні та нічні зміни. Тривалість нічної зміни – 4 год, денної – 7 год. За 1 год денної роботи працівник отримує 2,30 грн, за 1 год нічної- на 35% більше.

– Премія становить 15% від погодинної оплати і нараховується працівникам, стаж яких перевищує 5 років.

– Доплата на дітей становить 50 грн на кожну дитину, її отримують ті працівники, у яких 2 і більше дітей.

– Доплата на харчування становить 10% від погодинної оплати в нічні зміни.

– Профспілкові внески складають 1% від усіх нарахувань.

– Внески у касу взаємодопомоги становлять 25 грн, у касу платять лише члени цієї каси.

– Якщо працівник брав кредит у банку, то щомісяця він сплачує 20% від суми нарахувань, але не більше 25 грн.

– Податок нараховується за такою шкалою:

• якщо нараховано до 50 грн., податок не сплачується,

• якщо нараховано до 120 грн., податок становить 10% від нарахувань,

• якщо ж нараховано 120 грн. і більше – сплачують 15% податку.

Для створення відомості "До виплати" необхідно попередньо

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

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

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

=D8*$С$20*Тариф_нічна+Е8*$С$21*Тариф_денна

У діапазоні комірок С19:Е21 знаходяться тарифи оплати нічної та денної зміни.

Maл. 2.19. Приклад створеної таблиці з додатковими даними та тарифами оплати праці

Премія обчислюється за формулою:

=ЕСЛИ(С8>5;І8*0,15;0)

Доплати на дітей та на харчування обчислюються відповідно за формулами:

=ЕСЛИ(F8>=2;50;0);

=ЕСЛИ(D8<>0;08*$С$20*Тариф_нічна*1%;0)

Профспілкові внески, внески у касу взаємодопомоги, сплата кредиту та податок обчислюються за формулами:

=М8*1%;

=ЕСЛИ(G8="так";25;0);

=ЕСЛИ(Н8="так";ЕСЛИ(М8*20%<25;М8*20%;25);0);

=ЕСЛИ(М8<50;0;ЕСЛИ(М8>=120;М8*15%;М8*10%))

Таблиця для проведення розрахунків може мати вигляд, подібний до табл. 2.8.

Щоб створити результуючу відомість, потрібно на новому аркуші робочої книги створити нову таблицю, з трьома колонками: Нараховано, Утримано, До виплати (мал. 2.20). У комірках Нараховано та Утримано повинні знаходитись посилання на відповідні комірки "чорнової" відомості (=Чорновик!М8 та =Чорновик!R8).