Практична робота №2 на тему Microsoft Excel та Аналіз даних підбір параметра, НУДПСУ

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

Тема: Табличний процесор Microsoft Excel. Аналіз даних: підбір параметра, таблиці підстановки, консолідація даних

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

Завдання:

1. Використовуючи  функцію Подбор параметра та дані наведеної таблиці, знайдіть, при якому значенні терміну вкладу сума виплат становитиме певне значення.

2. Створити таблицю підстановки, яка відображає вплив відсоткової ставки та терміну вкладу  на суму виплат  за заданими в таблиці даними.

3. Консолідувати дані окремих аркушів і розмістити консолідовані дані на одному робочому аркуші.

4. При консолідації обчислити суму продажу .

Література [1, 6, 7, 8, 9, 12, 20]

Хід роботи

1. Скопіюйте цю практичну роботу у свою папку на сервері.

2. Відмініть атрибут "Только чтение". Відкрийте цей файл для роботи.

3. Послідовно виконайте всі завдання, переходячи по черзі до робочих аркушів цієї книги відповідно Подбор параметра, Таблица подстановки та Консолидация.

Функція "Подбор параметра"

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

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

Алгоритм розв’язання

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

Для заданої таблиці це буде коміркаС16.

2. Меню Сервис à Подбор параметра.

а) Перевірте в діалоговому вікні Подбор параметра, чи задана адреса комірки С16, яка містить значення суми до виплат, в текстовому полі Установить в ячейке.

б) В діалоговому вікні Подбор параметра встановити текстовий курсор в текстове віконце Значение і введіть з клавіатури потрібне значення, у даному прикладі 12000.

в) В діалоговому вікні Подбор параметра встановити текстовий курсор в текстове віконце Изменяя значение ячейки і клацніть на комірці, в якій міститься значення терміну вкладу, у даному прикладі С13.

г) В діалоговому вікні Подбор параметра клацніть на кенопці  Ок.

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

Знайдене значення терміну вкладу і буде тим шуканим значенням терміну вкладу, при якому значення суми виплат досягне заданого розміру ( у даному разі 12000 грн.).

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

Зауваження. Розв’язання виконайте самостійно. Використайте наведений для прикладу 1  алгоритм, замінюючи в ньому значення терміну вкладу на значення відсоткової ставки.

Функція "Таблица подстановки"

Ця функція використовується для дослідження впливу одного чи двох параметрів на значення певної величини, яка задана формулою, яка містить ці параметри. Якщо параметр один, то список його значень і список значень досліджуваної величини утворюють таблицю, яка називається  таблицею підстановки з однією змінною, що відображає функціональну залежність S=f(P).

Завдання 3. Створити таблицю підстановки, яка відображає вплив відсоткової ставки на суму виплат та коефіцієнт нарощування за заданими в таблиці даними.

Алгоритм розв’язання

1. Складіть список значень відсoткової ставки у коміркахD22:D36.

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

=$C$12*(1+$C$15/100)^ $C$14

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

=(1+$C$15/100)^ $C$14 .

4. Виділіть діапазон комірокD22:F36 .

5. Меню Данные, клацніть на пункті Таблица подстановки.

У діалоговому вікні Таблица подстановки встановіть текстовий курсор у віконціПодставлять значения по строкам в:  і клацніть на комірці C15, потім – на кнопці ОК.

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

Завдання 4. Створити таблицю підстановки, яка відображає вплив відсоткової ставки та терміну вкладу  на суму виплат  за заданими в таблиці даними.

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

Алгоритм

1. Складіть список значень відсoткової ставки в діапазоні комірок Е67:Е82. Об’єднайте комірки і створіть зміст об’єднаних комірок як на рисунку нижче.

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

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

3. Введіть значення терміну вкладу в діапазон комірок F66:K66.

4. Виділіть діапазон комірокE66:K82.

5. Меню Данные, клацніть на пункті Таблица подстановки.

а) У діалоговому вікні Таблица подстановки встановіть

текстовий курсор у віконці Подставлять значения по столбцам в:  і клацніть на комірці В50.

б) текстовий курсор у віконці Подставлять значения по строкам в:  і клацніть на комірці В51.

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

Функція Консолідація даних

При консолідації даних об'єднуються значення з декількох діапазонів даних.

Наприклад, якщо є аркуш витрат для кожного регіонального представництва, консолідацію можна використовувати для перетворення цих даних в аркуш корпоративних витрат. Консолідувати дані в Microsoft Excel можна декількома способами. Найзручніший метод полягає в створенні формул, що містять посилання на комірки в кожному діапазоні об'єднаних даних. Формули, що містять посилання на декілька робочих аркушів, називаються тривимірними формулами. Консолідацію по розташуванню слід використовувати у випадку, якщо дані всіх початкових областей знаходяться в одному місці і розміщені в однаковому порядку; наприклад, якщо є дані з декількох аркушів, створених на основі одного шаблону. Якщо встановлено автоматичне оновлення консолідації при зміні початкових даних, змінити набір комірок і діапазонів, що входять в консолідацію, неможливо. Дана функція доступна тільки при оновленні консолідації вручну. Консолідацію по категорії слід використовувати у випадку, якщо вимагається узагальнити набір робочих аркушів, що мають однакові заголовки рядів і стовпців, але різну організацію даних. Цей спосіб дозволяє консолідувати дані з однаковими заголовками зі всіх аркушів.

Завдання 5. Консолідувати дані робочих аркушів Львів, Суми, Київ і розмістити консолідовані дані на цьому робочому аркуші.

При консолідації обчислити суму продажу .

Алгоритм розв’язання

1. Створіть заголовки для консолідованих даних:

Обсяг продаж у 200_ році

2. Виділіть комірку, починаючи з якої будуть вставлені консолідовані дані.

3. Меню Данные, пункт Консолидация.

4. В діалоговому вікні  Консолидация  

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

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

в) повторіть дії пункту б) для робочого аркуша Суми;

г) повторіть дії пункту б) для робочого аркуша Київ;

д) встановіть прапорці у віконцях індикаторних перемикачів як на зразку  вікна, натисніть кнопку Ок.

е) надайте виразності отриманій таблиці консолідованих даних. Відтворіть у таблиці дані діапазонів консолідації.

Захистіть виконану роботу, продемонструвавши викладачу результати роботи.

Перелік питань до захисту практичної роботи

1. Яке призначення має функція Подбор параметра?

2. Які фінансово- економічні задачі можна розв’язувати за допомогою функції Подбор параметра?

3. Яку  логічну функцію і як застосувати  для реалізації обчислювального процесу з двома розгалуженнями?

4. Яке призначення має функція Таблица подстановки?

5. Які фінансово- економічні задачі можна розв’язувати за допомогою функції Таблица подстановки?

6. Яке призначення має функція Консолідація?

7. Які фінансово- економічні задачі можна розв’язувати за допомогою функції Консолидация?

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