Лабораторна Робота № 22

Тема: Excel. Робота зі списками і датами. Використання фільтрів.

Мета: Формування умінь для роботи зі списками і з датами. Використання фільтрів.

 

Теоретичні відомості

 

Упорядкування табличних даних можна проводити одночасно за одним і більше ключами. Процедура сортування може порушити логічний зв’язок між комірками. Під час сортування необхідно дотримуватись таких правил:

Ø у формулі, що залежить від комірок поза списком, використовувати тільки абсолютні посилання;

Ø під час сортування рядків (стовпців) слід уникати застосування формул із посиланням на комірки, що знаходяться в інших рядках (стовпцях) таблиці.

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

 

Хід роботи

 

1. В середовищі Excel створити таблицю, яка містить такі дані про ліки в аптеці: назва ліків, ціна упаковки, дата випуску, термін придатності (у роках). У таблицю помістити не менше семи записів.

2. Для кожних ліків визначити дату придатності.

3. Вивести список ліків, які будуть придатні впродовж двох наступних років.

4. Вивести список ліків, які виготовлені 6 числа.

5. Вивести список ліків, які виготовлені в листопаді.

6. Вивести список ліків, виготовлених у середу, для яких термін придатності не закінчився.

Пслідовність дій

1.Створюємо таблицю:

Ø у комірки А1, В1, С1, D1 вводимо назви стовпців таблиці (номер, назва ліків, ціна упаковки, дата випуску, термін придатності);

Ø заповняємо таблицю (рис. 22.1);

 

 

Рисунок 22.1 – Таблиця. Відомість про ліки в аптеці

 

2.Визначаємо дату придатності для кожного з ліків:

Ø для цього в комірку F2 вводимо формулу: ДАТА (ГОД (D2)+E2; МІСЯЦЬ (D2); ДЕНЬ (D2)), (рис. 22.2).

Рисунок 22.2 – Таблиця. Визначення дати придатності ліків

 

3. Щоб вивести список ліків, які будуть придатні в плині двох наступних років (2005-2006 рр.), використовуємо фільтр. Для цього:

Ø У верхньому меню ДАНІ вибираємо підменю ФІЛЬТР Þ АВТОФІЛЬТР.

Ø Клацнувши на кнопці із зображенням трикутника у стовпці Дата придатності, вибираємо із списку (Условие...) і записуємо умови відбору (рис. 22.3).

 

Рисунок 22.3 – Вікно ПОЛЬЗОВАТЕЛЬСЬКИЙ АВТОФИЛЬТР

 

Ø Далі, натиснувши на кнопку ОК, отримуємо список ліків, які будуть пиридатні впродовж двох наступних років(рис. 22.4).

 

 

Рисунок 22.4 – Список ліків, які придатні впродовж двох наступних років.

 

4. Для того, щоб вивести список ліків, що виготовлені 6 числа, виконуємо допоміжні обрахунки:

Ø В комірку G2 вводимо формулу: =ДЕНЬ(D2) (рис. 22.5.).

 

 

Рисунок 22.5 – Допоміжна таблиця для визначення списку ліків, що виготовлені 6 числа.

 

Ø Застосуємо фільтр, клацнувши мишею на кнопці із зображенням трикутника у стовпці День.

Ø У вікні ПОЛЬЗОВАТЕЛЬСЬКИЙ АВТО-ФИЛЬТР записуємо умову “равно 6” і отримуємо список ліків, що виготовлені 6 числа (рис. 22.6).

 

 

Рисунок 22.6 – Список ліків, що виготовлені 6 числа.

 

5. Для того, щоб вивести список ліків, які виготовлені в листопаді, зробимо допоміжні розрахунки:

Ø У комірку H2 вводимо формулу =МЕСЯЦ(D6) (рис. 22.7).

 

 

Рисунок 22.7 – Допоміжна таблиця для визначення списку ліків, виготовлені в листопаді

Ø Застосуємо фільтр, клацнувши мишею на кнопці з трикутником у стовпці таблиці Місяць.

Ø У вікні ПОЛЬЗОВАТЕЛЬСЬКИЙ АВТОФИЛЬТР записуємо умову “равно 11” і отримуємо список ліків, що виготовлені в листопаді (рис. 22.8).

 

 

Рисунок 22.8 – Список ліків, виготовлені в листопаді

 

6. Для того, щоб вивести список ліків, які виготовлені в середу, зробимо допоміжні розрахунки:

Ø у комірку І2 введемо формулу =ДЕНЬНЕД(D2) (рис. 22.9);

Ø застосуємо фільтр, клацаємо мишкою на кнопці із зображенням трикутника у стовпці День тижня;

 

 

Рисунок 22.9 – Допоміжна таблиця ліків, що виготовлені в середу

 

Ø у вікні ПОЛЬЗОВАТЕЛЬСЬКИЙ АВТО-ФИЛЬТР записуємо умову “равно середа” і отримуємо список ліків, які виготовлені в середу (рис. 22.10).

 

 

Рисунок 22.10 – Список ліків, що виготовлені в середу

 

4 Контрольні запитання

1. Як ввести формат клітинки по типу?

2. Як ввести поточну дату?

3. Як задати чи змінити формат відображення дати?

4. Як користуватися командою АВТОФІЛЬТР?

5. Як працює команда підсумок даних?

6. Як с творити список користувача?

7. Як виконати пошук даних у таблиці?

8. Як задати чи змінити формат відображення чисел?

9. Як відредагувати дане в клітинці?

10. Як змінити ширину стовпця в клітинці?

11. Як знайти різницю між датами?

12. Як вивести місяць, день, рік окремо в клітинках?

13. Наведіть приклади даних текстового типу?

 

Варіанти завдань наведені нище:

 

Варіант 22.1

1. Створити в середовищі Excel таблицю, яка містить такі дані: код робітника, П.І.П., дата народження, розряд, дата влаштування на роботу. У таблицю помістити не менше семи записів.

2. Визначити стаж і вік кожного робітника.

3. Визначити кількість робітників, вік яких не більший 30 років.

4. Вивести список робітників, стаж роботи яких на даному підприємстві не перевищує 6 місяців.

5. Вивести список робітників, які влаштувались на роботу в травні минулого року.

6. Визначити дату початку першої відпустки (11 місяців від дати влаштування на роботу).

Варіант 22.2

1. Створити в середовищі Excel таблицю, яка містить такі дані: П.І.П. абонента, дата підключення телефону, дата розмови, кількість хвилин. У таблицю помістити не менше семи записів.

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

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

4. Вивести список абонентів, переговори яких відбулися в квітні поточного року.

5. Вивести список абонентів, переговори яких відбулися 1 числа.

6. Визначити день тижня кожної розмови.

 

Варіант 22.3

1. Створити в середовищі Excel таблицю, яка містить такі дані: назву групи, прізвище студента, дата народження, дата поступлення в університет. У таблицю помістити не менше семи записів.

2. Визначити вік кожного студента.

3. Вивести список студентів третього курсу.

4. Вивести список студентів народжених у січні.

5. Визначити день тижня, у який народився студент.

6. Вивести список студентів, які поступили в університет у серпні 2003 року.

Варіант 22.4

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

2. Визначити тривалість кожного рейсу.

3. Вивести список рейсів до Києва, які були виконані минулого року.

4. Вивести список пунктів призначення, куди літають літаки в понеділок з даного аеропорту.

5. Визначити день тижня, вильоту кожного рейсу.

6. Визначити дату прибуття кожного рейсу.

 

Варіант 22.5

1. Створити в середовищі Excel таблицю, яка містить такі дані про студії звукозапису: назва групи, назва альбому, назва студії, дата запису, дата продажу. У таблицю помістити не менше семи записів.

2. Визначити час реалізації кожного альбому (кількість днів від створення альбому до продажу).

3. Для кожного альбому визначити день тижня, коли він був проданий.

4. Вивести список альбомів, записаних у січні минулого року.

5. Вивести список альбомів, проданих за перший квартал 1 року.

6. Вивести список альбомів, проданих 30 числа.

 

Варіант 22.6

1. Створити в середовищі Excel таблицю, яка містить такі дані про продаж товарів: шифр товару, дата продажу, прізвище продавця, дата виготовлення товару, термін придатності товару. У таблицю помістити не менше семи записів.

2. Для кожного товару визначити дату закінчення терміну його придатності.

3. Вивести список товару, що виготовлений у лютому і має термін придатності більше 2-х років.

4. Вивести список товару, який проданий у середу.

5. Визначити день тижня, в який товар проданий.

6. Вивести список товарів виготовлених 7 березня.

 

Варіант 22.7

1. Створити в середовищі Excel таблицю, яка містить такі дані про продаж товарів: номер відділу, шифр товару, дата продажу, прізвище продавця, датанародження продавця. У таблицю помістити не менше семи записів.

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

3. Вивести список товарів, які були продані у травні минулого року.

4. Вивести список товарів, які були продані у четвер першим відділом.

5. Визначити день тижня, у який продано товар.

6. Вивести список продавців, у яких день народження 1 березня.

Варіант 22.8

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

2. Для кожного приладу визначити дату, коли закінчиться гарантійний термін.

3. Вивести список приладів, виготовлених 5 вересня, гарантійний термін яких не перевищує 5 років.

4. Вивести список приладів, проданих у четвер.

5. Визначити день тижня, у який виготовлений прилад.

6. Вивести список приладів, що перебувають на гарантії в поточному році.

 

Варіант 22.9

1. Створити в середовищі Excel таблицю, яка містить такі дані про хворих у лікарні: П.І.П. хворого, дата народження хворого, захворювання, дата поступлення на лікування, термін курсу лікування (в днях). У таблицю помістити не менше семи записів.

2. Для кожного хворого визначити дату виписки з лікарні.

3. Вивести список хворих, вік яких від 25 до 30 років.

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

5. Вивести список хворих на грип, що захворіли 1 січня.

6.Вивести список хворих, що перебувають на лікуванні.

 

Варіант 22.10

1. Створити в середовищі Excel таблицю, яка містить такі дані про хворих у лікарні: П.І.П. хворого, дата народження хворого, захворювання, дата поступлення на лікування, термін лікування, що лікує лікар, стаж роботи лікаря. У таблицю помістити не менше семи записів.

2. Для кожного хворого визначити вік.

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

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

5. Вивести список хворих, що надійшли 31 грудня до лікуючого лікаря Іваненка.

6. Вивести список хворих, що лікувалися в лікарні торік.

 

Варіант 22.11

1. Створити в середовищі Excel таблицю, яка містить такі дані про телефонні розмови: П.І.П. абонента, дата розмови, час початку розмови, кількість хвилин переговорів. У таблицю помістити не менше семи записів.

2. Визначити час закінчення кожної розмови.

3. Визначити кількість переговорів, що відбулися у вересні, тривалість яких перевищує 10 хвилин.

4. Вивести список абонентів, переговори яких відбулися в поточному році.

5. Вивести список абонентів, переговори яких відбулися в першій декаді.

6. Визначити день тижня кожної розмови.

 

Варіант 22.12

1. Створити в середовищі Excel таблицю, яка містить такі дані про випуск виробів підприємством: код виробу, дата виготовлення, дата реалізації, вартість, гарантійний термін. У таблицю помістити не менше семи записів.

2. Визначити час реалізації кожного виробу.

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

4. Вивести список виробів, які виготовлені в поточному році, гарантійний термін яких становить менше ніж 3 роки.

5. Вивести список виробів, які виготовлені 25 числа.

6. Визначити день тижня реалізації кожного виробу.

 

Варіант 22.13

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

2. Визначити стаж роботи кожного працівника на даному підприємстві.

3. Визначити робітників, що влаштувалися на роботу в грудні, стаж яких становить понад 6 років.

4. Вивести список робітників, що працювали 1 числа.

5. Вивести список робітників, що працювали по понеділках і дату відповідного понеділка.

6. Визначити день тижня, влаштування на роботу кожного робітника.

 

Варіант 22.14

1. Створити в середовищі Excel таблицю, яка містить такі дані про погодинну оплату праці: табельний номер робітника, П.І.П., дата народження, дата поступлення на роботу, кількість відпрацьованих робітником годин, вартість години праці. У таблицю помістити не менше семи записів.

2. Визначити вік кожного робітника.

3. Вивести список робітників, які влаштувалися на роботу в травні, вік яких понад 40 років.

4. Вивести список робітників, які влаштувалися на роботу 10 числа.

5. Вивести список робітників, які працювали в суботу та неділю.

6. Визначити дату першої відпустки (відпустка надається через 11 місяців з моменту влаштування на роботу).

 

Варіант 22.15

1. Створити в середовищі Excel таблицю, яка містить такі дані про ремонт приладів: прізвище замовника, дата початку ремонту, термін ремонту (у днях), код приладу, вид ремонту, вартість ремонту. У таблицю помістити не менше семи записів.

2. Визначити дату закінчення ремонту кожного приладу.

3. Вивести список приладів, які надійшли на ремонт у жовтні, термін ремонту якого перевищував 5 днів.

4. Вивести список приладів, які надійшли на ремонт 31 числа.

5. Вивести список робітників, які працювали в четвер.

6. Визначити кількість приладів, відремонтованих торік.