Практична робота №6 Застосування табличного процесору Excel для аналізу даних
« НазадПрактична робота № 6Тема: Застосуваннятабличного процесору Microsoft Excel для аналізу даних. Мета: Навчитись застосовувати для аналізу даних функції надбудови MS Excel: Подбор параметра, Таблица данных та Консолидация. Порядок виконання роботи1. На робочому аркуші 1 створити наведену таблицю 6.1. Використовуючи функцію Подбор параметра та дані наведеної таблиці необхідно знайти, при якому значенні терміну вкладу сума виплат становитиме 12 000грн. Таблиця 6.1 - Розрахунок виплат за вкладами
2. В комірці С16 (міститься значення суми до виплат) виконати обчислення за формулою: S = V * (1 + P/100)T. 3. Активізувати функцію підбір параметру: Данные - Подбор параметра. У вікні Подбор параметра задати реквізити відповідно малюнку і натисніть Ок (Рис 6.1). Рис 6.1 - Вікно Подбор параметра Проаналізуйте таблицю. Яке значення терміну вкладу з’явилось в комірці С13? 4. На робочому аркуші 2 створити таблицю 6.2. Розрахуйте значення в комірці С16. Використовуючи функцію Подбор параметра та дані наведеної таблиці, знайдіть, при якому значенні відсоткової ставки сума виплат становитиме 12 000грн. Завдання виконується аналогічно як наведено в пункті 3. Таблиця 6.2 - Розрахунок виплат за вкладами
5. На робочому аркуші 3 створити таблицю 6.3. та створіть таблицю підстановки (таблиця 6.4.), яка відображає вплив відсоткової ставки на суму виплат та коефіцієнт нарощування за заданими в таблиці даними. Таблиця 6.3 - Розрахунок виплат за вкладами
Таблиця 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. і натисніть Ок. Рис. 6.2 - Пункт меню Данные - Таблица данных Проаналізуйте отримані результати - вміст комірок діапазонуD22:F37. 10. Створити на робочому аркуші 3 таблицю підстановки, яка відображає вплив відсоткової ставки та терміну вкладу на суму виплат за заданими в таблиці даними (таблиця 6.5). Таблиця 6.5 - Розрахунок виплат за вкладами
Якщо параметрів два, то список їх значень і список значень досліджуваної величини визначається: S=f(P,Т). 11. Складіть список значень відсоткової ставки в діапазоні комірок F67:F82. Об’єднайте комірки і створіть зміст об’єднаних комірок як показано в таблиці 6.6. Таблиця 6.6 - Список значень відсоткової ставки 12. Введіть формулу розрахунку суми виплат в комірку F66: =$B$49*(1+$B$51/100)^ $B$50 . 13. Введіть значення терміну вкладу в діапазон комірок G66:L66. 14. Виділіть діапазон комірок F66:L82 та активізуйте пункт меню Данные - Таблица данных. Заповніть реквізити вікна відповідно малюнку 6.3. Рис. 6.3 – Вікно Таблица данных Проаналізуйте отримані результати - вміст комірок . 15. Створити робочі аркуші надавши їм назви: Львів, Суми, Київ. Необхідно консолідувати дані з робочих аркушів і розмістити консолідовані дані на цих робочих аркушах. При консолідації обчислити суму продажу. 16. Створіть заголовки для консолідованих даних на робочих аркушах Львів, Суми, Київ: Обсяг продаж у 200_ році. Створити таблицю 6.7 на робочих аркушах. 17. Створити новий робочій аркуш Консолідація. Виділіть на робочому аркуші комірку, де будуть вставлені консолідовані дані та активізуйте пункт меню Данные - Консолидация. В діалоговому вікні (рис. 6.4.) Консолидация задайте: - в полі Функция виберіть зі списку Сумма; - в полі Ссылка встановіть текстовий курсор і перейдіть на робочий аркуш Львів, виділіть на ньому комірки А2:Е6; - встановіть прапорці у віконцях індикаторних перемикачів як на зразку вікна; - повторіть попередні дії для робочих аркушів Київ та Суми (натиснувши кнопку Добавить діалогового вікна Консолидация натисніть кнопку Ок. Рис 6.4 - Діалогове вікно Консолидация Рис. 6.5 - Кнопка Добавить діалогового вікна Консолидация 18. Надайте виразності отриманій таблиці консолідованих даних. Відтворіть у таблиці дані діапазонів консолідації (таблиці 6.7, 6.8, 6.9). Таблиця 6.7 - Робочий аркуш Львів Таблиця 6.8 - Робочий аркуш Суми Таблиця 6.9 - Робочий аркуш Київ 19. Збережіть результати в своїй папці на сервері. 20. Оформити звіт, зробити висновки і відповісти на контрольні питання. Контрольні питання1. Пояснити призначення і роботу вікна Консолидация данных. 2. Пояснити призначення і роботу вікна Таблица подстановки. 3. Які функції можна використовувати у вікні Консолидация даннях. З повагою ІЦ "KURSOVIKS"! |