Тема 8. Системи табличної обробки даних (Microsoft Excel)

Лабораторна робота № 3 (6 годин)

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

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

A	B	C	X	Y1	Y2	Y3	Y4

• ввести шапку таблиці;

• внести вхідні дані: значення A, B (або С), X;

• вирахувати вихідні дані, використовуючи формули та вбудовані функції, а також функції автозаповнення; у формулах використовувати відносні посилання (значення Х) та абсолютні посилання (значення А, В, С); Y1 вираховується за завданням 1, Y2 — за завданням 2, Y3 та Y4 — за завданням 3, під час вираховування Y4 використовувати логічні функції И та ИЛИ. Значення Y3 та Y4 мають збігатися;

• знайти мінімальне, максимальне та середнє значення серед вхідних та вихідних даних;

• відформатувати таблицю — числові та текстові формати, ширину та висоту клітинок. Під час форматування використовувати автоформат та ручний формат;

• оформити рамки таблиці.

2. За допомогою редактора формул MS Equation 3.0 під таблицею записати завдання на визначення функцій Y1, Y2 та Y3.

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

• ввести шапку таблиці;

• внести вхідні дані (деякі з даних мають повторюватися);

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

• відформатувати таблицю — числові та текстові формати, ширину та висоту клітинок (якщо необхідно, текст у заголовках таблиці розташувати в кілька рядків). Під час форматування використовувати автоформат, ручний формат тощо;

• оформити рамки таблиці.

4. Роздрукувати робочі таблиці:

• вхідні та вихідні дані в книжковому форматі;

• відформатовані таблиці в альбомному форматі;

• вихідні дані у вигляді формул в альбомному форматі;

5. Описати дії під час виконання лабораторної роботи.

6. За результатами лабораторної роботи № 3 зробити висновки.

Тема 8. Системи табличної обробки даних (Microsoft Excel)

Лабораторна робота № 1 (4 години)

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

1. Створити запити, використовуючи вбудовані функції бази даних:

• БДСУММ — для визначення загальної суми в полі за критерієм — два значення;

• БИЗВЛЕЧЬ — для отримання унікального значення за критерієм — два значення;

• ДМАКС — для визначення максимального значення за критерієм — одне значення;

• ДМИН — для визначення мінімального значення за критерієм — одне значення;

• ДСРЗНАЧ — для визначення середнього значення за критерієм — два значення;

• БСЧЕТ — для підрахунку числових значень за критерієм — одне значення;

• БСЧЕТА — для підрахунку текстових значень за критерієм — одне значення.

2. Використати одну з функцій бази даних для реалізації додаткової умови задачі.

Для кожної з функцій використовувати прості, складні, складені та обчислювальні критерії пошуку, створені в практичній роботі № 2.

3. Роздрукувати отримані значення вбудованих функцій та самі функції.

4. Роздрукувати критерії, що використовувались (у вигляді значень та у вигляді формул).

5. До бази даних додати нове поле із значеннями дати та текстове поле із значеннями «так» або «ні». (Наприклад, поля — Сплачено, Отримано, Доставлено, Виготовлено, Здано і т. ін.).

6. Створити зведені таблиці (Сводная таблица) на основі бази даних:

• у першій розташувати поле з текстовими значеннями та поле дати в зонах Строка та Столбец, поле (поля) з числовими значеннями — у зоні Данные (вираховувати загальну суму значень та середнє);

• у другій поміняти місцями поле з текстовими значеннями і поле дати в зонах Строка та Столбец, а в зоні Данные вираховувати мінімальне та максимальне значення серед визначених числових полів;

• у третій — одне поле з текстовими значеннями розташувати в зоні Страница, друге — в зоні Столбец, поле дати — в зоні Строка, числове поле — в зоні Данные (вираховувати загальну суму значень та середнє);

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

7. По кожній зведеній таблиці створити запити за підсумками.

8. Роздрукувати зведені таблиці та результати запитів.

9. Створити колонтитули з прізвищем та групою студента і назвою роботи.

10. Описати функції та дії, що здійснювалися під час виконання лабораторної роботи.

11. За результатами лабораторної роботи № 1 зробити висновки.

Лабораторна робота № 2 (4 години)

_ Частина 1. Технологія використання статистичних функцій (задачі прогнозування) (2 год)

1. Завантажити робочу таблицю з лабораторної роботи № 3 (І семестр) «Розв’язання математичних задач у середовищі Excel».

2. Створити три копії таблиці (копіювати тільки значення величин A, B, (C), X, Y1 — без формул).

3. Записати поруч з кожною копією таблиці формулу одиничної регресії (лінійної або нелінійної). Формули створити у WORD і впровадити в Excel.

4. Описати статистичні функції, що використовуються для прогнозування.

5. Спрогнозувати поведінку функції Y = F(X):

• за допомогою функції ПРЕДСКАЗ для одного нового значення Х у першій копії таблиці;

• за допомогою функції ТЕНДЕНЦИЯ для кількох нових значень Х у другій копії таблиці;

• за допомогою функції РОСТ для кількох нових значень Х у третій копії таблиці.

6. Створити лінійні графіки для функцій Y = F(X) включно з новими значеннями.

7. Порівняти отримані значення за величиною та на графіках.

8. Завантажити робочу таблицю з лабораторної роботи № 3 (І семестр) «Розв’язання економічних задач у середовищі Microsoft Excel».

9. Створити дві копії таблиці (копіювати тільки значення без формул).

10. Записати поруч з кожною копією таблиці формулу множинної регресії (лінійної або нелінійної). Формули створити у Word і впровадити в Excel.

11. Спрогнозувати поведінку вихідного значення залежно від вхідних значень для одного нового набору вхідних значень:

• за допомогою функції ЛИНЕЙН;

• за допомогою функції ЛГФПРИБЛИЗ.

12. Порівняти отримані значення.

13. Пояснити різницю між одиничною та множинною регресіями.

14. Створити колонтитули: верхній — назва роботи, нижній — прізвище та дата створення роботи.

15. Роздрукувати отримані результати.

16. Описати дії з виконання лабораторної роботи.

17. За результатами частини 1 лабораторної роботи № 2 зробити висновки.

_ Частина 2. Технологія використання фінансових функцій для розв’язування економічних задач (2 год)

1. Застосовуючи фінансові функції БЗ, КПЕР, НОРМА, НПЗ, ПЗ, розв’язати задачі (2 завдання вибираються за останньою цифрою в заліковій):

0. За вкладом розміром 2 млн грн нараховуються 10 % річних. Розрахувати, яка сума буде на рахунку через 5 років, якщо проценти нараховуються щомісяця.

1. На ощадний рахунок вносяться платежі по 200 тис. грн на початку кожного місяця. Розрахувати, яка сума виявиться на рахунку через 4 роки за ставки процента 13,5 % річних.

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

3. Розрахувати, яка сума буде на рахунку, якщо сума розміром 5 млн грн розміщена під 12 % річних на 3 роки, а % нараховуються кожні півроку.

4. Фірмі буде потрібно 60 млн грн через 12 років. У цей час фірма має в своєму розпорядженні гроші і готова покласти їх на депозит єдиним внеском, щоб через 12 років він сягнув 60 млн грн. Яку суму необхідно покласти на рахунок, якщо відсоткова ставка по ньому — 2 % на рік.

5. Розрахувати поточну вартість внеску, який через 3 роки становитиме 15 млн грн за нарахування 20 % на рік.

6. Визначити поточну вартість обов’язкових щомісячних платежів розміром 100 000 грн протягом 15 років, якщо процентна ставка становить 12 % на рік.

7. Визначити поточну вартість звичайних щомісячних платежів розміром 50 000 грн протягом 2 років, за нарахування 18 % річних.

8. Яку суму потрібно покласти на депозит, щоб через 4 роки вона досягла значення 20 млн грн за нарахування 9 % річних.

9. Визначити поточну вартість звичайних щоквартальних платежів розміром 350 000 грн протягом 7 років за нарахування 11 % річних.

0. Розрахувати чисту поточну вартість проекту, витрати за яким становитимуть 400 млн грн, а передбачувані прибутки за перші два роки реалізації проекту — 40 і 80 млн грн. Початок реалізації проекту — через два роки. Норма дисконтування — 15 % річних.

1. Визначити ефективність інвестиції розміром 200 млн грн, якщо очікувані прибутки за перші 5 років становитимуть відповідно: 20, 40, 50, 80 і 100 млн грн. Витрати залучення капіталу становлять 13,5 % річних.

2. Інвестиції в проект до кінця другого року його реалізації становитимуть 20 000 грн. У подальші три роки очікуються прибутки за проектом 5000 грн, 4500 грн, 8000 грн. Витрати залучення капіталу 11 %. Розрахувати чисту поточну вартість проекту.

3. Витрати за проектом в початковий період часу його реалізації становлять 30 000 грн, очікувані прибутки за перші три роки: 5000 грн, 900 грн, 10 000 грн. На четвертий рік очікується збиток в 3000 грн. Ціна капіталу 9 % річних. Розрахувати чисту поточну вартість проекту.

4. Створюється фонд, кошти до якого надходять у вигляді постійної річної ренти постнумерандо. Розмір разового платежу — 10 млн грн. На внески, що надійшли, нараховується 12 % річних. Визначити, коли величина фонду дорівнюватиме 85 млн грн.

5. Через скільки років обов’язкові щомісячні платежі розміром 15 000 грн принесуть прибуток в 10 млн грн за процентної ставки 13,5 % річних?

6. Через скільки років відбудеться повне погашення позики розміром 500 000 грн, якщо виплати по 100 000 грн здійснюються в кінці кожного кварталу, а відсоткова ставка — 15 % річних?

7. Через скільки років внесок розміром 500 000 грн досягне величини 1 млн грн за щомісячного нарахування процентів і ставки 35,18 % річних?

8. Через скільки років відбудеться повне погашення позики розміром 5 млн грн, якщо виплати по 100 тис. грн здійснюються в кінці кожного місяця, а ставка процента — 10 % річних?

9. Розрахувати процентну ставку для трирічної позики в 17 млн грн з щомісячним погашенням по 500 000 грн за умови, що позика повністю гаситься.

2. Створити колонтитули: верхній — назва роботи і спеціальність, нижній прізвище, група і курс.

3. Роздрукувати отримані результати.

4. Описати функції та їх аргументи, що використовувалися при виконанні лабораторної роботи.

5. За результатами частини 2 лабораторної роботи № 2 зробити висновки.