ТЕМА: Табличний процесор

Скачати 109.15 Kb.

Назва	ТЕМА: Табличний процесор
Дата	05.05.2013
Розмір	109.15 Kb.
Тип	Методичні рекомендації

bibl.com.ua > Математика > Методичні рекомендації

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

МЕТА: навчитись застосовувати для аналізу даних засоби надбудови MS Excel – Подбор параметра, Таблица подстановки та Консолидация.
ХІД РОБОТИ

Запишіть тему і мету роботи. Складіть звіт про виконання практичної роботи.

ЗАВДАННЯ 1. Створіть документ MS Excel з ім’ям Лаборат_15. Кожне завдання виконайте на окремому робочому аркуші.
Використовуючи засіб Подбор параметра та дані табл. 15.1, знайдіть, за якого значення терміну вкладу сума виплат становитиме 12 000 грн.
Таблиця 15.1

	А	В	С
11		Розрахунок виплат за вкладами
12		Розмір вкладу (грн, V)	4000,00
13		Термін вкладу (роки, Т)	5,00
14		Відсоткова ставка (%, Р)	12,00
15		Коефіцієнт нарощування (k)
16		Сума до виплати (грн, S)

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

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

2. Меню Сервис→Подбор параметра. У діалоговому вікні Подбор параметра потрібно (рис. 15.1):

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

б) встановити текстовий курсор у текстове віконце Значение і ввести з клавіатури потрібне значення, у цьому прикладі 12 000;

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

г) клацнути на кнопку ОК.

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

Обчислене значення терміну вкладу і буде тим шуканим значенням терміну вкладу, за якого значення суми виплат досягне заданого обсягу (у цьому разі 12 000 грн).
ЗАВДАННЯ 2. Використовуючи засіб Подбор параметра та дані табл. 15.2, обчисліть, за якого значення відсоткової ставки сума виплат становитиме 12 000 грн.

Таблиця 15.2

	А	В	С
11		Розрахунок виплат за вкладами
12		Розмір вкладу (грн, V)	4000,00
13		Термін вкладу (роки, Т)	5,00
14		Відсоткова ставка (%, Р)	12,00
15		Коефіцієнт нарощування (k)
16		Сума до виплати (грн, S)

Розв’язання виконайте самостійно. Використайте наведений для завдання 1 алгоритм, замінюючи в ньому комірку значення терміну вкладу на комірку значення відсоткової ставки.
ЗАВДАННЯ 3. Створіть таблицю підстановки, що відображає вплив відсоткової ставки на суму виплат і коефіцієнт нарощування за даними табл. 15.3 та їх розміщенням на робочому аркуші.
Таблиця 15.3

	А	В	С
11		Розрахунок виплат за вкладами
12		Розмір вкладу (грн, V)	4000,00
13		Термін вкладу (роки, Т)	5,00
14		Відсоткова ставка (%, Р)	12,00
15		Коефіцієнт нарощування (k)
16		Сума до виплати (грн, S)

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

y = f(x).
Алгоритм створення одновимірної таблиці підстановки

Створіть заголовки, складіть список значень відсоткової ставки, як наприклад у комірках D21:D36 (рис. 15.2). Використайте засоби автозаповнення.
Введіть формулу розрахунку суми виплат у комірку Е22

= $С$12*(1+$С$14/100)^$С$13.

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

= (1+$С$14/100)^$С$13.

Рис. 15.2. Створення таблиці підстановки з одним параметром

Виділіть діапазон комірок E22:F36.
Увійдіть в меню Данные, клацніть на пункті Таблица подстановки.

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

Рис. 15.3. Діалогове віно Таблица подстановки при створенні одновимірної таблиці
6. Проаналізуйте отримані результати, тобто вміст комірок діапазону E22:F36.

ЗАВДАННЯ 4. Створіть двовимірну таблицю підстановки, що відображатиме вплив відсоткової ставки та терміну вкладу на суму виплат за заданими в табл. 39.4 даними та їх розміщенням.
Таблиця 15.4

	А	В
48	Розрахунок виплат за вкладами
49	Розмір вкладу (грн, V)	7000,00
50	Термін вкладу (роки, Т)	5,00
51	Відсоткова ставка (%, Р)	11,00
52	Коефіцієнт нарощування (k)
53	Сума до виплати (грн, S)

Якщо параметрів два, то список їх значень і список значень досліджуваної величини

Z = F(x,y).
Алгоритм створення двовимірної таблиці підстановки

Складіть список значень відсоткової ставки в діапазоні комірок Е67:Е82. Об’єднайте комірки і створіть заголовки в об’єднаних комірках (рис. 15.4).

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

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

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

Виділіть діапазон комірок Е66:К82.

Увійдіть у меню Данные, клацніть на кнопку пункту Таблица подстановки. У діалоговому вікні Таблица подстановки (рис. 15.5) потрібно:

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

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

Рис. 15.4. Створення двовимірної таблиці підстановки

Рис. 15.5. Діалогове вікно Таблица подстановки в процесі створення двовимірної таблиці
6. Проаналізуйте отримані результати, тобто вміст комірок діапазону D22:F36.
ЗАВДАННЯ 5. Консолідуйте дані робочих аркушів Львів, Суми, Київ і розмістіть їх на новому робочому аркуші з ім’ям Консолідація. У процесі консолідації обчисліть суму продажу.
Методичні рекомендації
Консолідація даних
За консолідації даних об’єднуються дані з декількох діапазонів, розміщених у різних місцях, у тому числі й у мережі.

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

На робочому аркуші Консолидация створіть заголовок для консолідованих даних: Обсяг продажу у 200_ році.
Виділіть комірку, починаючи з якої вставлятимуться консолідовані дані.
Увійдіть в меню Данные→Консолидация.
У діалоговому вікні Консолидация (рис. 15.9) потрібно:

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

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

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

г) повторити дії пункту б для робочого аркуша Київ (рис. 15.8), але на кнопку Добавить не натискати;

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

Рис. 15.6. Робочий аркуш Львів

Рис. 15.7. Робочий аркуш Суми

Рис. 15.8. Робочий аркуш Київ

Рис. 15.9. Діалогове вікно Консолидация
5. Надайте виразності отриманій таблиці консолідованих даних (рис. 15.10). Відтворіть у таблиці дані діапазонів консолідації.

Рис. 15.10. Таблиця консолідованих даних
ЗАВДАННЯ 6. Захистіть виконану роботу, продемонструвавши викладачу результати роботи.
Запитання до захисту практичної роботи:

Яке призначення має засіб Подбор параметра"?
Які фінансово-економічні завдання можна розв’язувати за допомогою засобу Подбор параметра?
Яку логічну функцію і як застосовувати для реалізації обчислювального процесу з двома розгалуженнями?
Для чого призначений засіб Таблица подстановки?
Які фінансово-економічні завдання можна розв’язувати за допомогою засобу Таблица подстановки?
Яке призначення має засіб Консолидация?
Які фінансово-економічні завдання розв’язують за допомогою засобу Консолидация?

Схожі:

ТЕМА: Табличний процесор МЕТА: навчитись виконувати обчислення в MS Excel та використовувати у формулах відносні й абсолютні посилання на комірки	Тема: Табличний процесор Мета: Набуття практичних навичок у створенні, редагуванні електронних таблиць і виконанні в них арифметичних операцій
Тема: Табличний процесор Завдання: Для заданої таблиці, у якій наведені результати іспитів, створити електронну таблицю, виконати її редагування	ТЕМА: Табличний процесор МЕТА: навчитись виконувати основні операції в середовищі електронної таблиці Excel – налагодження робочого вікна, переміщення таблицею,...
ТЕМА: Табличний процесор МЕТА: засвоїти технологію адресації в Excel, проведення розрахунків, використання логічних функцій для реалізації обчислювальних...	Табличний процесор Мета: Набуття практичних навичок у створенні, редагуванні електронних таблиць і виконанні в них арифметичних операцій
Тема: Основні поняття та принципи роботи з базами даних в Excel Табличний процесор Excel крім основних функцій: побудови таблиць, обрахунків в цих таблицях та побудови діаграм, дозволяє ще й створення,...	План-конспект уроку Тема: Основні поняття та принципи роботи з базами даних в Excel Табличний процесор Excel крім основних функцій: побудови таблиць, обрахунків в цих таблицях та побудови діаграм, дозволяє ще й створення,...
Тема по програмі Табличний редактор Adobe Table може бути відкритий безпосередньо з програми PageMaker	Конспект з предмета «Інформатика» тема «Текстовий процесор» Для запуску редактора необхідно вибрати з пункту „Программы" головного меню Windows ярлик „Microsoft Office Word 2003"

Додайте кнопку на своєму сайті:
Портал навчання