Posobie_1_chast2_Excel

•створення оберненої матриці;

•створення транспонованої матриці;

•множення матриці на обернену.

2.На листі “Формула” відобразіть формули.

3.Збережіть завдання на електронному носії у папці \шифру групи\№ _(варіанта індивідуального завдання)_ ПІБ \завдання № 5.

4.У висновку за звітом завдання № 5 має бути гіперпосилання на ім’я файлу.

5.Підготуйте документ до друку. Роздрукуйте завдання у вигляді значень і формул.

ЗАВДАННЯ 6.

1.У новій робочій книзі на листі “Погашення кредиту” побудувати вхідну таблицю в середовищі Excel.

		A	B	C
1.		Розрахунок динаміки погашення кредиту
2.
3.
4.		Сума кредиту	S
5.		Відсотки по кредиту	P%	10,00%
		за місяць
6.		Прибуток за місяць	R%	20,00%
7.		Кількість періодів	T	10
8.		Плата за період		?
9.		Місяці	Сума на кінець	Сума на початок
			місяця	місяця
10.		1	?	?
11.		2	?	?
12.		3	?	?
13.		4	?	?
14.		5	?	?
15.		6	?	?
16.		7	?	?
17.		8	?	?
18.		9	?	?
19.		10	?	?

2.Зробить розрахунок динаміки погашення кредиту й одержуваного прибутку від вкладання грошей у бізнес.

Нехай хтось узяв кредит сумою S (S = (№ варіанту)*1000) на T періодів.

210

Відсотки по кредиту на місяць складають P% за період. Гроші вкладаються в бізнес, прибуток від якого складає R% на місяць. Розплачуємося з кредитом частинами наприкінці кожного місяця. Плату за період обчислюємо за допомогою фінансової функції.

3.Необхідно розрахувати умовні суми на начало та кінець кожного місяця з врахуванням відсотку, що бере банк за надання кредиту.

4.Зробити форматування таблиці за зразком.

5.Задати відповідні формати для числових показників (Сума кредиту, Плата за період, Сума на кінець місяця, Сума на початок місяця – із двома знаками після десяткової коми, з відокремленням тисяч, грошовий – грн.).

6.На листі “Формула” відобразіть формули.

7.Збережіть завдання на електронному носії у папці \шифру групи\№ _(варіанта індивідуального завдання)_ ПІБ \завдання № 6.

8.У висновку за звітом завдання № 6 має бути гіперпосилання на ім’я файлу.

9.Підготуйте документ до друку. Роздрукуйте завдання у вигляді значень і формул, щоб були відображені назви колонок та строк.

ЗАВДАННЯ 7

1.Відповідно до індивідуального завдання створіть таблиці у новій робочій книзі, та за допомогою інструмента Поиск решения знайдіть оптимальне розв'язання транспортної задачі (див. Додаток Г) на листі “Перевезення” і задачі про призначення на листі “Праця” (див. Додаток Д).

Транспортне завдання.

Є п пунктів виробництва й т пунктів розподілу продукції. Вартість перевезення одиниці продукції з i–го пункту виробництва в j–й центр розподілу Сij наведена в таблиці, де під рядком розуміється пункт виробництва, а під стовпцем – пункт розподілу. Крім того, у цій таблиці в i–му рядку зазначений обсяг виробництва в i–м пункті виробництва, а в j-му стовпці зазначений попит в j–му центрі розподілу. Необхідно скласти план перевезень по доставці необхідної продукції в пункти розподілу, а сумарні транспортні витрати були б мінімальні.

Завдання про призначення.

Є п робітників і т видів робіт. Вартість Сij виконання i–м робітником j–ї роботи наведена в таблиці, де робітникові відповідає рядок, а роботі – стовпець. Необхідно скласти план робіт так, щоб всі роботи були виконані, кожний

211

робітник був зайнятий тільки одній роботі, а сумарна вартість виконання всіх робіт була б мінімальною.

2.Збережіть завдання на електронному носії у папці \шифру групи\№ _(варіанта індивідуального завдання)_ ПІБ \завдання № 7.

3.У висновку за звітом завданням № 7 має бути гіперпосилання на ім’я файлу.

4.Підготуйте документ до друку. Роздрукуйте завдання у вигляді значень і формул, щоб були відображені назви колонок та строк.

ЗАВДАННЯ 8

1.Використовуючи табличний процесор Excel, створіть нову базу даних, з дотриманням правил створення списків (БД) в Microsoft Excel (див. Додаток Е).

Робота повинна містити в собі:

•Вхідні, проміжні й кінцеві електронні таблиці;

•Діаграми;

•Зведену таблицю:

•Зведену діаграму.

Кожна електронна таблиця повинна складатися не менше ніж з 5 стовпців і 10 рядків. Дані для вихідних таблиць необхідно використовувати реальні, наприклад, із практичних занять спеціальних дисциплін або з практичної діяльності. Якщо підсумкова таблиця не вимагає проведення розрахунків, то необхідно її доповнити додатковими стовпцями або рядками для проведення розрахунків.

За даними вихідної або результуючої таблиці повинні бути побудовані 2 діаграми різних типів. Зведену таблицю та зведену діаграму необхідно будувати на підставі даних, створених на підставі кінцевої (підсумкової) електронної таблиці.

2.На листі “Формула” відобразіть формули.

3.Збережіть завдання на електронному носії у папці \шифру групи\№ _(варіанта індивідуального завдання)_ ПІБ \завдання № 8.

4.У висновку за звітом завдання № 8 мають бути імпортовані об’єкти табличного процесора Microsoft Excel: таблиці з результатами розрахунків, формули і діаграми.

5.Підготувати результати розрахунків і діаграми до друку.

212

ЗАВДАННЯ 9

1.У новій робочій книзі на листі “Ефективність капіталовкладень” побудувати вхідну таблицю в середовищі Excel.

2.Припустимо, що у вас просять у борг Р грн. і обіцяють повертати по А грн. протягом N років. При якій річній процентній ставці ця угода має смисл?

3.Крім того, для автоматизації складання таблиці в комірці C2, використовуючи логічну функцію, уведіть формулу, що відображає значення комірці В2 залежно від вартого значення (наприклад: "рік", "роки", "років") і в комірці В6, використовуючи логічну функцію, уведіть формулу, що відображає чи вигідна угода ("Вигідно дати гроші в борг", "Варіанти рівноважні", "Вигідніше гроші покласти під %"), що відображає співвідношення комірці В1 і В5.

4.Відповідно до індивідуального завдання (див. Додаток Ж) зробіть розрахунок Чистого поточного об'єму внеску. Розрахувати ефективність капіталовкладень за допомогою фінансової функції.

5.За допомогою інструмента Подбор Параметра табличного процесора Excel значення при якому буде виконуваться умови: “Вигідно дати гроші в борг” та “Варіанти рівноважні”.

Для варіантних фінансових розрахунків, заснованих на завданні різних значень аргументів функції, доцільно скористатися сценарним підходом, реалізованим засобами EXCEL.

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

7.Самостійно створіть сценарій при якому будуть виконуваться умови: “Вигідно дати гроші в борг” та “Варіанти рівноважні”.

8.Самостійно створіть звіти по сценарію типу Структура сценарія та Сводная

213

таблица.

9.На листі “Формула” відобразіть формули.

10.Збережіть завдання на електронному носії у папці \шифру групи\№ _(варіанта індивідуального завдання)_ ПІБ \завдання № 9.

11.У висновку за звітом завданням № 9 має бути гіперпосилання у вигляді піктограми на ім’я файла.

12.Підготуйте документ до друку. Роздрукуйте завдання у вигляді значень і формул, щоб були відображені назви колонок та строк.

ЗАВДАННЯ 10

1.Використовуючи табличний процесор Excel, відкрийте шаблон «Авансовый отчет».

2.Заповніть його за своїм Прізвищем.

3.Збережіть завдання на електронному носії у папці \шифру групи\№ _(варіанта)_ ПІБ \ завдання № 10.

4.У висновку за звітом завданням № 10 має бути гіперпосилання на ім’я файлу.

5.Підготуйте документ до друку. Роздрукуйте завдання.

ЗАВДАННЯ 11

1.У новій робочій книзі на листі “Схема погашення позики” побудувати вхідну таблицю в середовищі Excel.

2.Нижче приведена таблиця погашення позики в 70000 тис. грн., виданої терміном на 3 роки під 17% річних. Розрахуйте таблицю погашення позики за допомогою фінансових функцій ПЛТ, ПРПЛТ.ОСПЛТ.

				A	B	C	D	E	F
		4				Схема погашення позики
		5			Сума	Загальна	Платежі по	Сума основного	Сума
				Рік	позики на	сума	відсотках	платежу за	позики на
					початок	платежу		позикою,
							(ПРПЛТ)		кінець року
					року	(ПЛТ)		(ОСПЛТ)
	6			1	70000.00	?	?	?	?
	7			2	?	?	?	?	?
	8			3	?	?	?	?	?
	9			Разом		?	?	?
10
11				К-сть років		3
12				%		17%

214

3.На листі “Формула” відобразіть формули.

4.Збережіть завдання на електронному носії у папці \шифру групи\№ _(варіанта індивідуального завдання)_ ПІБ \завдання № 11.

5.У висновку за звітом завданням № 11 має бути гіперпосилання у вигляді піктограми на ім’я файла.

6.Підготуйте документ до друку. Роздрукуйте завдання у вигляді значень і формул, щоб були відображені назви колонок та строк.

ЗАВДАННЯ 12

1.У новій робочій книзі на листі “Розрахунки по цінним паперам” побудувати вхідну таблицю в середовищі Excel.

	1 варіант	2 варіант	3 варіант
дата_соглашения	1.07.2006	1.07.2006	1.08.2006
дата_вступления_в_силу	31.12.2008	31.12.2008	1.02.2008
периодичность	4	5	2
базис	1	1	1
ДНЕЙКУПОН
ЧИСЛКУПОН
ДАТАКУПОНДО
ДНЕЙКУПОНДО
ДАТАКУПОНПОСЛЕ
ДНЕЙКУПОНПОСЛЕ

2.Визначте основні показники тривалості для цінних паперів.

3.Зробіть розрахунки у таблиці за допомогою фінансових функцій

ДНЕЙКУПОН; ЧИСЛКУПОН; ДАТАКУПОНДО; ДНЕЙКУПОНДО;

ДАТАКУПОНПОСЛЕ; ДНЕЙКУПОНПОСЛЕ.

За допомогою технології Диспетчер сценариев змініть периодичность

виплат, прийнятий базис, дата_соглашения і повторіть розрахунок показників тривалості для цінних паперів, подайте у вигляді підсумкової таблиці сценаріїв.

4.На листі “Формула” відобразіть формули.

5.Збережіть завдання на електронному носії у папці \шифру групи\№ _(варіанта індивідуального завдання)_ ПІБ \завдання № 12.

6.У висновку за звітом завданням № 12 має бути гіперпосилання у вигляді піктограми на ім’я файла.

7.Підготуйте документ до друку. Роздрукуйте завдання у вигляді значень і формул.

215

ЗАВДАННЯ 13

1.У новій робочій книзі на листі «Успішність групи (шифру групи) за три семестри» побудувати вхідні таблиці 1, 2, 3 в середовищі Excel.

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

3.Довільно виставте бали за семестри, використовуючи усі граничні умови задачі (таблиця 2 – Сума набраних балів).

4.Підрахуйте середньо взвішане балів за три семестри для кожного студента. (Колонка F).

5.Зробіть розрахунки в колонках «Оцінка за національною шкалою», «Оцінка» та «Оцінка за шкалою ECTS», використовуючи дані таблиці 2.

6.В комірці I4 визначити ранг за середньо взвішаним значенням, відповідно в комірці J4 – зворотній ранг.

7.Добавте одну строку перед шостою строкою.

8.В комірках F6 : J6 відобразіть розрахункові формули комірок F7 : J7.

9.Зробіть розрахунки в таблиці 3, за допомогою функції СЧЁТЕСЛИ, кількість студентів, які отримали відповідні оцінки за національною шкалою.

10.Зробіть розрахунки в таблиці 4, визначивши: всього присутніх, відсоток присутніх від комірки G42, відсоток абсолютної успішності та відсоток якості успішності.

216

11.Відобразіть розрахункові формули пунктів 9 та 10 у колонці праворуч від колонки H.

12.Збережіть завдання на електронному носії у папці \шифру групи\№ _(варіанта індивідуального завдання)_ПІБ \завдання № 13.

13.У висновку за звітом завданням № 13 має бути гіперпосилання у вигляді піктограми на ім’я файла.

14.Підготуйте документ до друку. Роздрукуйте завдання, щоб були відображені назви колонок та строк.

ЗАВДАННЯ 14

1.У новій робочій книзі на листі Прайс-лист своріть базу даних, за допомогою прайс-листа знайденного в інтернеті, яка має не менш 5 стовпчиків та не менш 50 записів і повина містики всі типи даних які підтримує Excel. В комірці А2 створити гіперпосилання на веб-сайт з якого завантажено прайс-лист.

2.У цій робочій книзі на листі Курси валют своріть таблицю курсів євро, долар, рубль до гривні для розрахунку цін на усі позиції асортименту прайс-листа у чотирьох грошових одиницях з відповідними форматами (розрахунки повині містити тривимірні посилання).

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

4.В цій робочій книзі додайте 18 робочих листів та назвіть їх відповідно номерам наступних підпунктів та пунктів. Скопіювати на ці листи таблицю з попереднього пункту.

5.У листах з іменами 3.1.1 3.4 робочої книги за допомогою команди Автофильтр відберіть дані, що задовольняють декільком умовам.

Використуйте не тільки критерії, що автоматично пропонуєйте Excel, але і задайте власні умови відбору значень.

Створити Автофильтр:

5.1.з простими критеріями, для умов фільтрації використайте:

5.1.1.на конкретне значення;

5.1.2.символи шаблонів;

5.1.3.оператори порівняння;

5.1.4.три записи з найбільшими числовими значеннями;

5.1.5.п’ять записів з найменшими числовими значеннями;

5.2.з складними критеріями – дві умови за одним полем, для умов фільтрації використайте:

5.2.1.символи шаблонів;

217

5.2.2.оператори порівняння;

5.3.з складеними критеріями, умови фільтрації оберіть довільно.

5.4.з логічними операторами И та ИЛИ, умови фільтрації оберіть довільно.

6.У листах з іменами 6.1 6.4 робочої книги за допомогою команди Расширенный фильтр організуйте відбір даних у вільне місце таблиці, що задовольняють більш складним критеріям, а також критеріям, що обчислюються. Для цього задайте декілька блоків критеріїв і блоків виведення.

Приклад:

6.1.з простими критеріями, умови фільтрації оберіть довільно;

6.2.з складними критеріями, умови фільтрації оберіть довільно;

6.3.з складеними критеріями, умови фільтрації оберіть довільно;

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

7.Обчисліть середні, максимальні і мінімальні значення за числовими полями для даних, що задовільняють різноманітним умовам, використовуючи функції бази даних.

8.Підрахуйте кількість записів у списку, що задовольняють декільком умовам, які оберіть довільно.

9.Створіть форму для введення і коригування даних у списку та вивчить всі можливості роботи з формою.

10.Організуйте для декількох полів перевірку значень, що вводяться.

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

12.Збережіть завдання на електронному носії у папці \шифру групи\№ _(варіанта)_ ПІБ \ Прайс–лист_ ПІБ.

13.У висновку за звітом завданням № 14 має бути гіперпосилання на ім’я файлу.

14.Підготуйте документ до друку.

218

6. КОНТРОЛЬНІ ЗАХОДИ

Питання до модульного контролю № 2

1.Поняття електронної таблиці (ЕТ). Основні елементи ЕТ.

2.Табличний процесор Microsoft Excel: інтерфейс користувача.

3.Довідкова система, формат робочого аркуша та робочої книги.

4.Типи даних ЕТ. Дії під час розв'язання завдань за допомогою табличного процесора: введення даних в ЕТ, редагування, запис математичних формул та обчислення за ними.

5.Копіювання та переміщення інформації, використання принципу «Drag & Drop» для роботи з даними.

6.Навігація по листах книги. Погоджені формати й формули. Перейменування листів. Переміщення й копіювання листа.Одночасна робота з декількома листами. Вставка й видалення листа. Копіювання листа з іншої книги.

7.Приховання листа, рядків і стовпців робочої книги. Зв'язки у формулах. Імена комірок

8.Примітки до комірок.

9.Спеціальна вставка. Копіювання формул на інший лист. Об'єднання діапазонів арифметично. Транспонування.

10.Збереження змісту ЕТ на магнітному носії.

11.Виведення результатів на принтер.

12.Технологія використання формул для розв'язання задач.

13.Типи вбудованих функцій Microsoft Excel. Аргументи вбудованих функцій, складені функції.

14.Пошук даних на листі. Заміна даних на листі. Автоматичний перехід і виділення спеціальних комірок

15.Типи помилок. Комірки, що впливають і зависимі комірки. Пошук помилок

16.Використання гіперпосилань. Збереження даних у форматі HTML .

17.Види захисту книги. Захист комірок і формул. Приховання формул. Захист листа. Обмеження на введення даних в комірку.

10.Таблична база даних. Створення списку . Заповнення списку за допомогою форми даних. Автоматизація введення даних. Пошук записів за допомогою форми даних

11.Сортування даних списку. Сортування по декількох стовпцях.

12.Автофільтр. Створення користувальницького автофільтра. Розширений фільтр. Поняття критерію пошуку та типи критеріїв у розширеному фільтрі.

219