Тема 2.2. Електронні таблиці

Мета: вдосконалити практичні навички у створюванні, редагуванні, форматуванні таблиць, введенні і редагуванні формул; використовувати абсолютні та відносні посилання на клітинки таблиці; використовувати вбудовані функції; редагувати введені функції, будувати та редагувати діаграми, використовувати фільтри.

ЗАВДАННЯ 16.

Створення та редагування таблиць: створити таблицю засобами MS Excel, виконати її редагування та форматування.

Виконання:

1. Дайте письмово відповідь на питання:

Що таке «електронна таблиця»?

Коли була створена перша електронна таблиця?

Дайте перелік з 5 табличних редакторів.

З яких елементів складається вікно табличного редактора?

2. Завантажте MS Excel.

3. Залишіть у Книзі лише Лист 1 та дайте йому назву – Автозаповнення.

4. Створіть наступну таблицю:

5. Ознайомтесь з засобом автозаповнення. Для цього виокремте по черзі введений текст в кожному стовпці і протягніть за допомогою миші до 20 рядка.

6. У клітинку F22 вставте фунцію Автосума.

7. За допомогою засобу автозаповнення скопіюйте цю функцію у клітинки G22, H22, I22 та J22.

8. Проаналізуйте отримані результати.

9. У клітинку F25 введіть формулу: =(F1+F5)/10

10. За допомогою засобу автозаповнення скопіюйте цю формулу у клітинки G25, H25, I25 та J25.

11. Проаналізуйте отримані результати.

12. Додайте у Книгу Лист 2 та дайте йому назву – Звіт.

13. Збережіть Книгу в папці Тема 2.2 з назвою – Додаток 6.

14. Створіть колонтитули:

верхній правий – Додаток 6;

нижній правий – ПІБ, дата створення, номер групи.

15. Створіть таблицю згідно зразка та виконайте форматування:

ЗАВДАННЯ 17.

Форматування таблиць: створити таблицю засобами MS Excel, виконати введення та редагування формул.

Виконання:

1. Дайте письмово відповідь на питання:

Що таке «формула»?

Для чого призначений «рядок формул»? З яких полів складається?

Яке значення помилок #####, #ЗНАЧ!, #ДЕЛ/0!, #ИМЯ?, #Н/Д, #ССЬІЛКА!, #ЧИСЛО?

2. Завантажте MS Excel.

3. Залишіть у Книзі лише Лист 1 та дайте йому назву – Формули.

4. Створіть таблицю згідно зразка:

5. Виконайте необхідні розрахунки Y1, Y2, Y3, Y4, Y5, за наступними формулами:

; ;;

;

6. Відобразіть в клітинках формули, за якими було виконано розрахунки.

7. Збережіть документ в папці Тема 2.2 з назвою – Додаток 7.

8. Створіть колонтитули: верхній правий – Додаток 7; нижній правий – ПІБ, дата створення, номер групи.

9. Додайте до Книги Лист та дайте йому назву – Задача.

10. Розв’яжіть на цьому аркуші задачу: Клієнт вносить гроші в банк на 12 місяців на умовах нарахування відсотків щомісяця за ставкою 24% річного доходу і додавання щомісячного доходу до основного вкладу (наприкінці місяч­ного терміну). Яку суму клієнт отримає через рік? Побудуйте таблицю щомісячних змін вкладу. Початкову суму вкладу введіть довільною. Всі грошові суми подайте з двома цифрами після десяткової коми.

11. Введіть заголовок таблиці «Рахунок у банку». Дайте назви стовпцям таблиці: Номер місяця, Початкова сума, Дохід, Кінцева сума. Клітинці, що міститиме значення відсоткової ставки банку, надайте назву Ставка. За річною ставкою визначить місячну. Введіть вхідні дані й лише три формули під назвами стовпців. Ско­піюйте формули в інші клітинки. Збільшить ширину клітинок, якщо треба.

12. Відформатуйте таблицю. Вирівняйте заголовок над таблицею. Усі грошові суми відобразіть з двома цифрами після коми і з назвою грошової одиниці – грн. Результат відобразіть напівжирним шрифтом більшого розміру. Таблицю охопіть прямокутною подвійною рамкою. Усі стовпці залийте різними кольорами світлих тонів так, щоб дані було добре видно.

13. Виконайте розрахунки в таблиці для різних вхідних даних:

• змініть ставку банку на 18%;

• змініть початкову суму вкладу на удвічі більшу.

14. Збережіть книгу і закрийте всі вікна.

ЗАВДАННЯ 18.

Введення та редагування формул: створити таблицю засобами MS Excel, виконати розрахунки, використовуючи абсолютні та відносні посилання на клі­тинки таблиці.

Виконання:

1. Дайте письмово відповідь на питання:

Що таке «абсолютна адресація клітинки»?

Що таке «відносна адресація клітинки»?

Як присвоїти клітинці ім’я?

2. Завантажте MS Excel.

3. Збережіть Книгу в папці Тема 2.2 із назвою – Завдання 18.

4. Залишіть у Книзі лише Лист 1 та дайте йому назву – Прайс-лист.

5. У клітинку G2 вставте формулу категорії Дата и время – СЕГОДНЯ.

6. Клітинці G3 присвойте ім’я – курс валюти.

7. Створіть таблицю згідно зразка та виконайте розрахунки у стовпчиках зі знаком «?»:

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

9. За допомогою Мастера функций введіть формули для обчислення максимальної, мінімальної і середньої ціни.

10. Відформатуйте клітинки таблиці за наведеним зразком. Дані у клітинках зі значенням цін повинні мати грошовий формат (відповідно $ та грн.).

Самостійна робота

1. Додайте до Книги Лист і дайте йому назву – Таблиця множення.

2. Створіть таблицю згідно зразка:

3. Збережіть книгу і закрийте всі вікна.

ЗАВДАННЯ 19.

Використання математичних функцій: створити таблицю засобами MS Excel, виконати розрахунки, використовуючи вбудовані функції.

Виконання:

1. Дайте письмово відповідь на питання:

Що таке «вбудована функція»?

Які категорії вбудованих функцій використовують в MS Excel?

2. Завантажте MS Excel.

3. Залишіть у Книзі лише Лист 1 та дайте йому назву – Фірма.

4. Здійсніть нарахування зарплатні 15 співробітникам Вашої фірми, якщо кожен з них відпрацював протягом місяця різну кількість днів, денна платня від 100 грн. до 250 грн., сплачується податок 15 % від заробленої суми. Результати оформіть у таблицю згідно зразка:

5. Впорядкуйте прізвища співробітників за алфавітом.

6. Застосуйте фільтр, щоб з’ясувати хто зі співробітників працював понад 15 днів.

Самостійна робота

1. Зробіть копію аркуша Фірма та дайте їй назву – статистичні функції.

2. Додайте рядки: мінімальне значення; максимальне значення; середнє значення; кількість співробітників, заробітна плата яких перебільшує 1500 грн.

3. Виконайте розрахунки, використовуючи відповідно статистичні функції: МИН, МАКС, СРЗНАЧ, СЧЕТЕСЛИ.

4. Додайте до Книги ще один аркуш та дайте йому назву – математичні функції.

5. Побудуйте таблицю для обчислення значень математичних функцій на проміжку [-1;1] з кроком = 0,5 згідно наступного зразка:

6. Додайте до Книги ще один аркуш та дайте йому назву – фінансові функції.

7. Розв’яжіть задачі за допомогою фінансових функцій.

Задача 1. Розрахувати суму щомісячного вкладення під 10% річних, яке через 15 років складе суму вкладу в 50 000 грн. Виплата робиться на початку періоду. (Використання функції ПЛТ()).

Початкові дані:

Річна відсоткова ставка - 10%

Число років зберігання - 15

Необхідна величина заощаджень - 50 000 грн.

Задача 2. Розрахувати величину вкладень під 18 % річних, які приноситимуть щорічно впродовж 5 років 20 000 грн. (Використання функції ПС()).

Початкові дані:

Щорічний дохід - 20 000 грн.

Відсоткова ставка - 18%

Число років - 5

8. Додайте до Книги ще один аркуш та дайте йому назву – логічні функції.

9. Створіть таблицю згідно зразка:

За допомогою функції ЕСЛИ заповнити рядок «Вид опадів», враховуючи наступне:

• якщо кількість опадів дорівнює нулю, то у рядку вид опадів повинен відобразитися запис - «Опадів немає»;

• якщо температура < 0 і є опади, то у рядку вид опадів повинен відобразитися запис – «Сніг»;

• якщо температура = 0 і є опади, то у рядку вид опадів повинен відобразитися запис – «Мокрий сніг»;

• інакше – «Дощ»

10. Збережіть Книгу в папці Тема 2.2 з назвою – Робота з функціями.

ЗАВДАННЯ 20-21.

Використання логічних функцій. Використання статистичних функцій: створити таблицю засобами MS Excel, вдосконалити навички введення та редагування функцій із допомогою "майстра" функцій.

Виконання:

1. Дайте письмово відповідь на питання:

Що таке «Майстер функцій»?

Опишіть процес використання Майстра функцій.

Опишіть процес редагування функцій.

2. Завантажте MS Excel.

3. Створіть Книгу з назвою – Мережа філій.

4. Залишіть у Книзі лише Лист 1 та дайте йому назву – Звітна відомість.

5. Складіть звітну відомість за результатами діяльності торговельної фірми у весняно-літній період згідно зразка:

У звітній відомості треба визначити:

• сумарну та середню виручку кожної з філій за звітний пе­ріод;

• сумарну виручку всіх філій за кожен місяць звітного пе­ріоду;

• місце, яке займає кожна з філій в сумарному обсязі ви­ручки;

• частку кожної з філій в сумарному обсязі виручки;

• найменшу місячну виручку за звітний період;

• найбільшу місячну виручку за звітний період.

6. Доповніть таблицю потрібними заголовками, виконайте відповідні розрахунки за допомогою Майстра функцій:

7. Збережіть зміни в документі.

8. Побудуйте кругову діаграму об’ємного типу, на якій відображатиметься частка вкладу кожної філії в формуванні за­гальної виручки. Розмістіть діаграму на окремому робочому аркуші.

Самостійна робота

1. Додайте до Книги ще один лист і дайте йому назву – Графік функції 1.

2. Побудуйте таблицю із обчисленими значеннями функції y=sin2x на проміжку від «-5» до «+5» з кроком «0,5».

3. Побудуйте графік за даною таблицею.

4. Зробіть копію листа та змініть назву – Графік функції 2.

5. Відредагуйте значення функції – y=sin(x-2).

6. Зробіть копію листа та змініть назву – Графік функції 3.

7. Відредагуйте значення функції – y=соs(x-2).

8. Зробіть копію листа та змініть назву – Графік функції 4.

9. Відредагуйте значення функції – y=соs2x.

10. Збережіть Книгу.

ЗАВДАННЯ 22.

Використання фінансових функцій: створити таблицю засобами MS Excel, вдосконалити навички щодо побудови і редагування діаграм.

Виконання:

1. Дайте письмово відповідь на питання:

Для чого призначені фінансові функції?

Дайте характеристику функціям: ПЛТ, ПС, ПРПЛТ, та БС.

2. Завантажте MS Excel.

3. Розв’яжіть задачі, використовуючи фінансові функції:

Задача 1. Розрахувати суму щомісячного вкладення під 10% річних, яке через 15 років складе суму вкладу в 50 000 грн. Виплата робиться на початку періоду. (Використання функції ПЛТ()).

Початкові дані:

Річна відсоткова ставка – 10%

Число років зберігання – 15

Необхідна величина заощаджень – 50 000 грн.

Функція ПЛТ(10%/12; 15*12;;50000;0)=-120,64 грн.

Задача 2. Розрахувати величину вкладень під 18 % річних, які приноситимуть щорічно впродовж 5 років 20 000 грн. (Використання функції ПС()).

Початкові дані:

Щорічний дохід – 20 000 грн.

Відсоткова ставка – 18%

Число років – 5

Функція ПС(18%;5;20000)=-62 543,42 грн.

Задача 3. Вичислити виплати по відсотках за перший місяць для трирічної позики в 100 000 грн. з розрахунку 10% річних. (Використання функції ПРПЛТ()).

Початкові дані:

Відсоткова ставка – 10%

Місяць – 1

Термін кредиту (роки) – 3

Сума кредиту – 100 000 грн.

Функція ПРПЛТ(0%/12;1;3*12;100000)=-833,33 грн.

Задача 4. Вклад розміром в 5 000 грн. покладений з 10.01 по 03.04 попереднього року під 20% річних. Знайти величину капіталу на 03.04 при нарахуванні простих відсотків. (Використання функції БС()).

Початкові дані:

Відсоткова ставка – 30%

Вклад – 5000 грн.

Дата відкриття вкладу - 10.01.20__

Дата закриття – 04.03.20__

Кількість розрахункових періодів – 1

Функція БС((Дата закриття вкладу – Дата відкриття вкладу)/360*30%;1;;-5000)=5220,83 грн.

4. Проаналізуйте процес розв’язування задач за допомогою фінансових функцій.

ЗАВДАННЯ 23-24.

Редагування введених функцій. Використання графічних можливостей MS Excel: створення та використання фільтрів, впровадження ділової графіки та інших графічних об’єктів засобами MS Excel

Виконання:

1. Дайте письмово відповідь на питання:

Що таке «Діаграма»?

Опишіть процес використання Майстра діаграм.

Які існують стандартні типи діаграм? Їх призначення.

Що таке фільтр?

Яка відмінність між автофільтром та розширеним фільтром?

Для чого використовують групування в MS Excel?

2. Запустіть електронну таблицю MS Excel.

5. Створіть Книгу в папці Тема 2.2 з назвою – Додаток 8.

6. Занесіть в комірку А1 заголовок «Дата», у комірку В1 — «Кількість вірусів».

7. Занесіть в комірку А2 дату «01.09.попередній рік», у комірку В2 — число 1.

8. Заповніть стовпець «Дата» за прогресією. (Зробіть комірку А2 активною. У вікні діалогу команди меню Правка/Заполнить/Прогрессия встановіть параметри:

•Прогрессия — по столбцам; •Тип — дата; •Единица даты — месяц; •Предельное значение — 01.10.поточний рік).

9. Заповніть стовпець «Кількість вірусів» прогресією. (Виділити діапазон В2:В15. У вікні діалогу команди меню Правка/Заполнить/Прогрессия встановіть параметри:

•Прогрессия — по столбцам; •Тип — геометрическая; •Шаг — 2).

10. Перемістіть таблицю на п'ять рядків униз і на один стовпець вправо. (Виділити діапазон комірок А1: В15. Встановіть курсор миші на межі виділеного діапазону. Натисніть ліву кнопку миші, відбуксуйте діапазон у нове місце).

11. Відформатуйте таблицю (обкресліть, вирівняйте значення по центру). За допомогою Word-Art надайте заголовок таблиці за власним бажанням. Побудуйте за даними таблиці графік, в який додайте заголовок та підписи даних.

12. Створіть копію Листа 1. Перейменуйте Лист 1 і Лист 2 на Вірус 1 і Вірус 2 відповідно.

13. Додайте малюнок до таблиці з Clip Gallery.

14. Додайте до Книги Лист 3 та дайте йому назву – Персонал швейного цеху.

15. Створіть таблицю, наведену нижче:

16. Виконайте фільтрацію даних (виділіть всю таблицю → Данные → Сортировка):

• за посадами (за зростанням),

• за статтю (за спаданням),

• за прізвищем (за зростанням)

11. Використовуючи Автофільтр (Данные → Фильтр → Автофильтр), виберіть з таблиці працівників, чий стаж роботи менше 10 років.

12. Відновіть загальний вигляд таблиці (Данные → Фильтр → Автофильтр).

13. На окремих листах сформуйте таблиці з відфільтрованими даними згідно наступних критеріїв:

• особи жіночої статі (критерій № 1);

• особи чоловічої статі з стажем роботи менше 10 років (критерій № 2);

• особи чоловічої статі старше 40 років (критерій № 3);

• чоловіки-вантажники та водії, а також жінки-швеї старше 30 років (критерій № 4).

11. Дайте Листу 4 назву – Критерій 1. Створіть таблицю згідно зразка:

12. Запустіть розширений фільтр та виберіть режим «скопировать результат в другое место» → укажіть діапазон клітинок для фільтрації → укажіть діапазон критеріїв → укажіть місце для розміщення → Ok.

13. Виконайте вибірку даних у відповідності з вказаними критеріями і розмістіть результат вибірки відповідно на листах 5 - 7.

Самостійна робота

1. Додайте до Книги ще один лист та створіть ЕТ реалізації морозива в магазині "Геркулес". Введіть дані у таблицю згідно зразка:

2. Виконайте розрахунки в клітинках зі знаком «?» та відформатувати створену ЕТ якнайкраще.

3. Впорядкуйте найменування морозива за алфавітом.

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

5. Перейменуйте лист у Геркулес.

6. Зробіть копію Листа та застосуйте фільтр: морозиво за ціною менше 5 грн.

7. Збережіть Книгу.