Методичний посібник "Робота з базами даних в MS Excel"

Департамент освіти і науки Запорізької обласної державної адміністрації

ДНЗ «Запорізьке вище професійне училище»

 

 

 

 

Методичний посібник

до виконання лабораторно – практичних завдань

з предмету "Технології комп’ютерної обробки інформації"

тема: «Робота з базами даних в MS Excel»

 

 

 

 

 

 

 

 

 

 

 

 

 

м. Запоріжжя

1. Особливості бази даних в Excel.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Приклад бази даних в Excel наведений на рис. 1.

 

Рис. 1 Приклад бази даних в Excel

Формування списку

Список формується на основі трьох основних структурних елементів поля.

Запис – повний опис конкретного елемента списку.

Поле – окремі елементи даних в запису.

При формуванні списку потрібно виконувати такі вимоги:

1. Заголовок списку повинен мати інше форматування в порівнянні з рештою списку, наприклад інший розмір або тип шрифту.

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

3. Не можна починати поля з пробілів.

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

Структура бази даних в Excel

На одному робочому листі потрібно розмістити лише один список.

Інформація в базах даних має постійну структуру. Кожний рядок можна розглядати як одиничний запис. Інформація в межах кожного запису міститься в полях.

Працюючи з базою даних в Excel, насамперед потрібно ввести заголовки стовпців. Після цього можна ввести інформацію в базу даних. Введення даних і перегляд інформації можна здійснювати за допомогою команди Данные→Форма.

Структурні компоненти БД

Структурними компонентами бази даних є записи, поля і заголовки. Структура зображена на рис. 2.

Запис – це повний опис конкретного об’єкта, він містить ряд різноманітних, логічно пов’язаних між собою полів.

Наприклад:  Таблица 1

Іванов А П

1972

інженер

Харків

92

Кожний запис – це рядок бази даних. Усі записи мають однакову фіксовану довжину, їх кількість не обмежена.

Поле – це певна характеристика об’єкта або окремий елемент даних у записі.

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

Наприклад, замість поля: «Прізвище, ім’я, по батькові» краще задати три поля «Прізвище», «Ім’я», «По батькові».

Заголовний рядок – це рядок, що містить імена полів, тобто заголовки стовпців і розміщується на самому початку списку.

Щоб підкреслити заголовки, необхідно скористатися вкладкою Шрифт або Граница діалогового вікна Формат ячеек, що відкривається за командою Формат→Ячейки.

 

Рис 2 Структура бази даних

Робота з базою даних в Excel

Не потрібно вставляти окремий рядок з дефісів чи знаків рівності, тому що Excel може витлумачити таке підкреслення, як дані.

Нові дані можна додавати безпосередньо в кінець списку. Дуже зручним може виявитися порядкове редагування списку за допомогою стандартної екранної форми. Для цього необхідно виділити якусь комірку в списку та виконати командуДанные→Форма. Необхідно пам’ятати, що перед виконанням команди Данные→Форма повинна бути виділена тільки одна комірка в списку, інакше у формі можуть з’явитися неправильні заголовки стовпців.

У Excel є команда текст по стовпцях, що дає можливість дуже просто створювати списки з даними, які містяться в текстових файлах.

Також можна скористатися Майстром перетворень.

Пошук записів у списку

За допомогою форми даних можна виконувати пошук записів у списку. Перед початком пошуку записів потрібно активізувати перший запис списку і відкрити вікно форми даних. Для пошуку за­писів потрібно натиснути кнопку Критерии. В результаті на екрані з'явиться порожня форма без записів. У цій формі задаються кри­терії пошуку. Якщо потрібно знайти запис із точно відомим значенням деякого поля, потрібно задати це значення у відповідному полі. Після цього потрібно натиснути кнопку Далее. У формі відобразиться перший запис, що задовольняє зазначені критерії. Натискаючи кнопку Далее, можна переглянути решта знайдених запи­сів. Щоб повернутися до побудови умов, потрібно натиснути кнопку Критерии. Якщо невідоме точне значення поля, а відоме приблизне (наприклад, потрібно знайти записи, в яких деяке поле починається з букви К), то при заданні критеріїв відбору використовують символи «*» (для заміни довільної кількості невідомих символів) або «?» (для заміни одного невідомого символ). Для пошуку числових зна­чень у критеріях пошуку використовують оператори порівняння

(>, <, = ,<>,<=, >=).

Наприклад, якщо ми хочемо знайти всіх студентів, у яких стипендія 100 грн., то у полі стипендія вікна форми після натискання кнопки Критерии необхідно записати =100, і у списку послідовно з’являться записи тих студентів, у яких стипендія дорівнює 100 грн.

Критерії пошуку

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

До критерію пошуку може входити одна або більше умов, причому декілька умов можуть накладатися або на один стовпчик таблиці (поле списку) або За типом критерії поділяють на прості, складні, складені та обчислювальні:

1. Простий критерій – це критерій, що містить тільки одну умову.

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

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

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

У вікні форми натискається кнопка Критерии і заповнюється шаблон запиту: у відповідному полі записується одна умова – простий критерій, або у декількох полях записується декілька умов – складний критерій. Складений та обчислювальний критерії у формах використовувати не можна.

 

2. Сортування списків.

Поняття сортування

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

Сортування – це процес упорядкування записів у базі даних.

Для сортування даних в Excel передбачений визначений порядок сортування

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

Дані можна сортувати за алфавітом, по зростанню/спаданню числового значення або дати. Сортувати значення полів можна в порядку зростання чи спадання (текстові значення сортуються в алфавітному порядку, числові від меншого до більшого або навпаки).

Порядок сортування

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

Ø Розташувати курсор миші в одній з комірок таблиці або виді­лити всю таблицю. Не можна виділяти частину таблиці. Тоді дані будуть впорядковані лише у виділеному діапазоні.

Ø Викликати меню Данные→Сортировка.

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

У вікні Сортировка диапазона (рис.4) необхідно вибрати поля, по яких буде проведене сортування, та встановити порядок сортування. Можна встановити сортування по одному, двох або трьох стовпцях.

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Рис. 4 Сортування

 

3. Фільтрація даних.

Поняття фільтрації даних

Для пошуку даних можна використовувати фільтрацію.

Фільтрація - це процес пошуку і вибору записів відповідно до встановлених критеріїв.

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

Типи фільтрів у Excel

В Excel  є такі типи фільтрів: автофільтр та розширений фільтр.

1. Автофільтр – це фільтр, що дозволяє задати прості критерії пошуку записів, у результаті відображаються ті записи, що задовольняють умову пошуку, і приховуються ті записи, які не задовольняють таку умову.

2. Розширений фільтр – це фільтр, що дозволяє задати складні критерії для по­шуку і за необхідності дозволяє задати відображення результатів фільтрації в окремій області таблиці.

Автофільтр

Автофільтр дозволяє проводити відбір записів безпосередньо на робочому листі. Для відбору записів з використанням автофільтрів можна задати цілий ряд критеріїв.

Для виклику функції автофільтра потрібно вибрати Данные→Фильтр →Автофильтр (рис. 5).

 

 

 

 

Рис. 5 Виклик автофільтра

При цьому курсор повинен бути встановлений всередині списку або виділена вся таблиця. Після виклику автофільтра поряд з назвою кожного стовпчика з'явиться відповідна кнопка (рис.6).

 

Рис. 6 Фрагмент списку після виклику автофільтра

Розширений фільтр

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

Щоб використати розширений фільтр, потрібно задати початковий діа­пазон, діапазон критеріїв та де фільтрувати список (безпосередньо в таблиці або розмістити результат фільтрації в окрему частину таблиці).

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

 

 

 

 

 

 

 

 

 

Рис. 7 Розширений фільтр

Кроки використання розширеного фільтра

Пошук розширеним фільтром можна розбити на такі кроки:

1. Викликати базу даних.

2. Сформувати та записати критерій у базу даних, причому критерій записується обов’язково над досліджуваною базою даних і між критерієм та базою даних повинен бути хоча б один порожній рядок.

3. Визначити координати комірок, в яких сформовано критерій.

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

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

6. Вибрати команду головного меню

Данные→Фильтр→Расширенный фильтр.

7. Ввести необхідні дані:

а) обробка:

Ø фильтровать список на Листе;

Ø скопировать результат в другое место;

б) дані для пошуку:

Ø исходный диапазон;

Ø диапазон условий;

Ø поместить результат в диапазон.

8. Натиснути кнопку Ок.

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

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

Розв’язання

Спочатку під таблицею (можна і поруч із таблицею) створюємо критерій для відбору, який містить назву стовпчиків Спеціальністьта Форма навчання, в яких відповідно записано Економіка та Б (рис.8).

 

Рис.8 Критерії відбору

Далі викликаємо команди Данные→Фильтр→Расширенный фильтр.

 Після виконання цих команд з’явиться вікно Расширенный фильтр, в якому у полі Исходный диапазон необхідно виділити діапазон, в якому розміщена початкова таблиця. Виділений діапазон повинен бути без заголовків. У полі Диапазон условий необхідно вказати діапазон, де розміщені критерії відбору. Далі, щоб розмістити результат під початковою таблицею, необхідно в полі Обработка поставити прапорець скопировать результат в другое место. Після цього активується поле Поместить результат в диапазон, в якому необхідно вказати діапазон, де буде розміщений результат виконання розширеного фільтра. (рис.9).

 

Рис. 9 Параметри налагодження

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

Рисунок 10 – Результат виконання