19.7. Робота з базами даних

За допомогою Microsoft Excel можна створювати і обробляти бази даних. База даних в Microsoft Excel це таблиця, яка складається з однотипних записів (рядків). Стовпці таблиці є полями запису в базі даних. Під ім’я полів виділяється перший рядок бази даних. Наприклад, якщо базою даних вважати телефонний довідник, то полями запису будуть прізвища, номери телефонів і адреси абонентів. Для роботи з базою даних необхідно спочатку створити відповідну таблицю. Якщо виділити комірку в таблиці і вибрати одну з команд обробки баз даних в меню Дані, Microsoft Excel автоматично визначає і обробляє всю таблицю. Дані, розташовані в стовпцях і рядках робочого листа, обробляються як набір полів, які утворюють записи.

Сортування даних

Сортування дозволяє переупорядкувати рядки в таблиці за будь-яким полем. Для сортування даних слід виділити один елемент таблиці і викликати команду Сортування меню Дані.

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

Форми даних

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

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

Для використання форм даних таблиця повинна мати імена стовпців. Імена стовпців стають іменами полів у формі даних. Поле відповідає кожному стовпцю таблиці. Форма даних автоматично розкривається так, щоб вивести на екран відразу всі поля в даній таблиці. За допомогою смуги прокрутки можна прокручувати записи бази даних. Позиція виведеного запису указується у верхньому правому кутку. Пересуватися по полях форми можна за допомогою миші і клавіш Tab (вниз) Shift+Tab (вгору). У правій частині вікна розташовані наступні кнопки:

• Додати - очищає поля для введення нового запису бази даних.

• Видалити - видаляє виведений запис, інші записи бази даних зміщуються.

• Повернути - відновлює відредаговані поля у виведеному записі, видаляючи зроблені зміни.

• Назад - виводить попередній запис в списку.

• Далі - виводить наступний запис бази даних.

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

• Правка - служить для виходу з режиму введення критеріїв.

• Очистити - видаляє існуючий критерій з вікна діалогу.

• Закрити - закриває форму даних.

Для додавання запису до бази даних необхідно виділити комірку в таблиці, до якої слід додати запис, у меню Дані вибрати команду Форма та натиснути кнопку Додати, заповнити поля нового запису, після введення даних натиснути клавішу Enter. Нові записи будуть додані в кінець бази даних.

Встановлення діапазону критеріїв

Критерії бувають двох типів.

• Критерії обчислення – це критерії, які є результатом обчислення формули. Формула повинна повертати логічне значення ХИБА або ІСТИНА. При фільтрації будуть доступні тільки ті рядки, значення яких надаватимуть формулі значення ІСТИНА.

• Критерії порівняння – це набір умов для пошуку, що використовується для відбору даних при запитах за прикладом. Критерій порівняння може бути послідовністю символів (константою) або виразом (наприклад, Ціна > 700).

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

Фільтрування даних

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

Команда Автофільтр встановлює кнопки прихованих списків (кнопки із стрілками) безпосередньо в рядок з іменами стовпців. З їх допомогою можна вибирати записи бази даних, які слід вивести на екран. Після виділення елемента у списку, що відкрився, рядки, які не містять даного елемента будуть приховані. Якщо в полі списку вибрати пункт Умова, то з'явиться вікно Користувацький автофільтр, за допомогою якого можна вказувати складніші умови відбору.

Щоб після фільтрування вивести всі дані таблиці, необхідно викликати команду Відобразити все або відмінити команду Автофільтр меню Дані, підміню Фільтр.

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

• фільтрувати список на місці – приховує рядки, які не задовольняють вказаному критерію;

• скопіювати результат в інше місце – копіює відфільтровані дані на інший робочий лист або на інше місце на цьому ж робочому листі;

• Початковий діапазон – визначає діапазон, який містить список, що підлягає фільтрації;

• Діапазон умов – визначає діапазон комірок на робочому листі, який містить необхідні умови;

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

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

Після використання команд Автофільтр або Розширений фільтр, таблиця переходить у режим фільтрації. У цьому режимі багато команд Microsoft Excel діють тільки на видимі комірки. Після застосування фільтру для виведення тільки потрібних рядків, можна скопіювати одержану підмножину даних в інше місце для подальшого аналізу.