Роздрукувати сторінку
Главная \ Методичні вказівки \ Методичні вказівки \ 1023 Практична робота №6 Застосування табличного процесору Microsoft Excel для аналізу даних

Практична робота №6 Застосування табличного процесору Excel для аналізу даних

« Назад

Практична робота № 6

Тема: Застосуваннятабличного процесору Microsoft Excel для аналізу даних.

Мета: Навчитись застосовувати для аналізу даних функції надбудови MS Excel: Подбор параметра, Таблица данных та Консолидация.

Порядок виконання роботи

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

Таблиця 6.1 - Розрахунок виплат за вкладами

 

A

B

C

 

11

 

Розрахунок виплат за вкладами

12

 

Розмір вкладу, грн., V

4000,00

 

13

 

Термін вкладу, років, T

5,00

 

14

 

Відсоткова ставка, %, P

12,00

 

15

 

Коефіцієнт нарощування, k

 

 

16

 

Сума до виплати, грн., S

 

 

2. В комірці С16 (міститься значення суми до виплат) виконати обчислення за формулою: S = V * (1 + P/100)T.

3. Активізувати функцію підбір параметру: Данные - Подбор параметра. У вікні Подбор параметра задати реквізити відповідно малюнку і натисніть Ок (Рис 6.1).

М1023, Рис. 6.1 - Вікно Подбор параметра

Рис 6.1 - Вікно Подбор параметра

Проаналізуйте таблицю. Яке значення терміну вкладу з’явилось в комірці С13?

4. На робочому аркуші 2 створити таблицю 6.2. Розрахуйте значення в комірці С16. Використовуючи функцію Подбор параметра та дані наведеної таблиці, знайдіть, при якому значенні відсоткової ставки сума виплат становитиме 12 000грн. Завдання виконується аналогічно як наведено в пункті 3.

Таблиця 6.2 - Розрахунок виплат за вкладами

 

A

B

C

 

11

 

Розрахунок виплат за вкладами

12

 

Розмір вкладу, грн., V

4000,00

 

13

 

Термін вкладу, років, T

5,00

 

14

 

Відсоткова ставка, %, P

12,00

 

15

 

Коефіцієнт нарощування, k

 

 

16

 

Сума до виплати, грн., S

 

 

5. На робочому аркуші 3 створити таблицю 6.3. та створіть таблицю підстановки (таблиця 6.4.), яка відображає вплив відсоткової ставки на суму виплат та коефіцієнт нарощування за заданими в таблиці даними.

Таблиця 6.3 - Розрахунок виплат за вкладами

 

A

B

C

 

11

 

Розрахунок виплат за вкладами

12

 

Розмір вкладу, грн., V

4000,00

 

13

 

Термін вкладу, років, T

5,00

 

14

 

Відсоткова ставка, %, P

12,00

 

15

 

Коефіцієнт нарощування, k

 

 

16

 

Сума до виплати, грн., S

 

 

Таблиця 6.4 - Список значень відсоткової ставки

М1023, Таблиця 6.4 - Список значень відсоткової ставки

6. Складіть список значень відсоткової ставки у комірках D22:D36 відповідно малюнку (таблиця 6.4.)

7. Введіть формулу розрахунку суми виплат в комірку Е22:

=$C$12*(1+D22/100)^ $C$13

8. Введіть формулу розрахунку коефіцієнта нарощування в комірку F22:

=(1+ D22/100)^ $C$13

9. Виділіть діапазон комірок D22:F37 та активізуйте пункт меню Данные - Таблица данных. Задайте реквізити відповідно малюнку 6.2. і натисніть Ок.

М1023, Рис. 6.2 - Пункт меню Данные - Таблица данных

Рис. 6.2 - Пункт меню Данные - Таблица данных

Проаналізуйте отримані результати - вміст комірок діапазонуD22:F37.

10. Створити на робочому аркуші 3 таблицю підстановки, яка відображає вплив відсоткової ставки та терміну вкладу на суму виплат за заданими в таблиці даними (таблиця 6.5).

Таблиця 6.5 - Розрахунок виплат за вкладами

 

A

B

 

48

Розрахунок виплат за вкладами

49

Розмір вкладу, грн., V

7000,00

 

50

Термін вкладу, років, T

5,00

 

51

Відсоткова ставка, %, P

11,00

 

52

Коефіцієнт нарощування, k

 

 

53

Сума до виплати, грн., S

 

 

Якщо параметрів два, то список їх значень і список значень досліджуваної величини визначається: S=f(P,Т).

11. Складіть список значень відсоткової ставки в діапазоні комірок F67:F82. Об’єднайте комірки і створіть зміст об’єднаних комірок як показано в таблиці 6.6.

Таблиця 6.6 - Список значень відсоткової ставки

М1023, Таблиця 6.6 - Список значень відсоткової ставки

12. Введіть формулу розрахунку суми виплат в комірку F66:

=$B$49*(1+$B$51/100)^ $B$50 .

13. Введіть значення терміну вкладу в діапазон комірок G66:L66.

14. Виділіть діапазон комірок F66:L82 та активізуйте пункт меню Данные - Таблица данных. Заповніть реквізити вікна відповідно малюнку 6.3.

М1023, Рис. 6.3 – Вікно Таблица данных

Рис. 6.3 – Вікно Таблица данных

Проаналізуйте отримані результати - вміст комірок .

15. Створити робочі аркуші надавши їм назви: Львів, Суми, Київ. Необхідно консолідувати дані з робочих аркушів і розмістити консолідовані дані на цих робочих аркушах. При консолідації обчислити суму продажу.

16. Створіть заголовки для консолідованих даних на робочих аркушах Львів, Суми, Київ: Обсяг продаж у 200_ році. Створити таблицю 6.7 на робочих аркушах.

17. Створити новий робочій аркуш Консолідація. Виділіть на робочому аркуші комірку, де будуть вставлені консолідовані дані та активізуйте пункт меню Данные - Консолидация. В діалоговому вікні (рис. 6.4.) Консолидация задайте:

- в полі Функция виберіть зі списку Сумма;

- в полі Ссылка встановіть текстовий курсор і перейдіть на робочий аркуш Львів, виділіть на ньому комірки А2:Е6;

- встановіть прапорці у віконцях індикаторних перемикачів як на зразку вікна;

- повторіть попередні дії для робочих аркушів Київ та Суми (натиснувши кнопку Добавить діалогового вікна Консолидация натисніть кнопку Ок.

М1023, Рис. 6.4 - Діалогове вікно Консолидация

Рис 6.4 - Діалогове вікно Консолидация

М1023, Рис. 6.5 - Кнопка Добавить діалогового вікна Консолидация

Рис. 6.5 - Кнопка Добавить діалогового вікна Консолидация

18. Надайте виразності отриманій таблиці консолідованих даних. Відтворіть у таблиці дані діапазонів консолідації (таблиці 6.7, 6.8, 6.9).

Таблиця 6.7 - Робочий аркуш Львів

М1023, Таблиця 6.7 - Робочий аркуш Львів

Таблиця 6.8 - Робочий аркуш Суми

М1023, Таблиця 6.8 - Робочий аркуш Суми

Таблиця 6.9 - Робочий аркуш Київ

М1023, Таблиця 6.9 - Робочий аркуш Київ

19. Збережіть результати в своїй папці на сервері.

20. Оформити звіт, зробити висновки і відповісти на контрольні питання.

Контрольні питання

1. Пояснити призначення і роботу вікна Консолидация данных.

2. Пояснити призначення і роботу вікна Таблица подстановки.

3. Які функції можна використовувати у вікні Консолидация даннях.

З повагою ІЦ "KURSOVIKS"!