Лабораторна робота №4

Тема: Робота з базами даних в середовищі табличного процесора Microsoft Excel

Мета роботи: придбати навички створення та роботи з базами даних в Microsoft Excel.

Основні теоретичні відомості Поняття про бази даних в Excel

Управління великими масивами даних в Microsoft Excel можна з використанням засобів, призначених для роботи з базами даних.

База даних – це електронна таблиця, організована певним чином.

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

В Excel базою даних є список.

Список – це набір даних, що містить інформацію про певні об’єкти.

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

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

Умови до бази даних

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

Формування електронної таблиці, як бази даних, виконується, якщо:

1) у кожному стовпчику електронної таблиці знаходяться однотипні дані;

2) кожний стовпчик має заголовок;

3) у тій частині електронної таблиці, яку необхідно обробити як базу даних, не повинно бути порожніх рядків або стовпчиків;

4) на одному робочому листі не можна розміщувати більше одного списку;

5) список має бути відокремленим від інших даних робочого листа щонайменше одним порожнім стовпчиком і одним порожнім рядком.

Сортування елементів стовпців

1. Створити БД «Інформація про поточні оцінки студентів» (Див. рис. 1).

Рисунок 1. БД «Інформація про поточні оцінки студентів»

2. Виділити довільну комірку БД.

3. Виконати команди ДаніСортування. На екрані з’явиться діалогове вікно Сортування діапазону (Див. рис. 2).

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

5. Натиснути ОК.

На екрані з’явиться результат сортування ( Див. рис. 3).

Рисунок 2. Діалогове вікно Сортування діапазону

Рисунок 3. Результат сортування елементів стовпців

Сортування елементів рядків

1. В БД «Інформація про поточні оцінки студентів» виділити частину рядків, для яких виконується сортування (Див. рис 4)

Рисунок 4. БД «Інформація про поточні оцінки студентів»

2. Виконати команди ДаніСортуванняПараметри сортування

3. Сортувати: стовпці (!) діапазону.

4. Команда ОК.

5. В поле для введення Сортувати за вести номер рядка електронної таблиці Excel (а не порядковий номер рядка в БД) і вказати напрямок сортування.

6. Команда ОК.

На екрані з’явиться результат сортування (Див. рис.5):

Рисунок 5. Результат сортування елементів рядків

Розглянутий алгоритм сортування забезпечує впорядкування даних як по стовпцях, так і по рядках. Ще однією важливою, якщо не основною, задачею БД є пошук записів, що задовольняють призначеному критерію. Однак пошук виконується тільки по полях, тобто по стовпцях електронної таблиці.

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

Транспонування бази даних

1. Виділити весь діапазон БД «Інформація про поточні оцінки студентів»

2. Виконати команди ВиправленняКопіювати...

3. Виділити комірку, де буде розміщена транспонована БД.

4. Виконати команди ВиправленняСпеціальна вставкаТранспонувати.

5. Команда ОК.

В результаті на екрані буде отримано транспоновану БД, у якій рядки вихідної БД стали стовпцями, а стовпці – рядками (Див. рис 6):

Рисунок 6. Транспонована БД.

Пошук

Під пошуком, що часто називають фільтрацією, будемо розуміти знаходження в БД таких записів, які задовольняють вимогам, що вводять для полів. Ці вимоги називаються критерієм пошуку (фільтрації).

Пошук автофильтром

1. В БД «Інформація про поточні оцінки студентів» виділити довільну комірку.

2. Виконати команди ДаніФільтрАвтофильтр. В результаті на кожному імені поля з'явиться стрілка.

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

4. Серед них вибрати значення, за яким потрібно виконати пошук (Див. рис 7, 8 )

Рисунок 7. Випадаючий список значень

Рисунок 8. Результат пошуку

5. Якщо потрібно виконати пошук за таким значенням, якого немає в жодному записі, необхідно виконати команду Умова...

6. В діалоговому вікні Користувальницький автофільтр ввести необхідний критерій пошуку (Див. рис. 9 )

Рисунок 9. Вибір критерію пошуку

7. Команда ОК.

Результат пошуку (Див. рис. 10):

Рисунок 10. Результат пошуку

Такий пошук можна послідовно виконувати по декількох полях.

Відновлення вихідної бази даних

1. Виконати команди ДаніФільтрПоказати всі.

2. Виконати команди ДаніФільтрАвтофільтр.

В результаті отримаємо вихідну БД.