Хід роботи

Бази даних (списки) в Excel – це таблиці, рядки яких містять однорідну інформацію. Рядки таблиці називаються записами, а стовпці – полями запису. Стовпцям присвоюють унікальні імена полів, які заносяться в перший рядок бази даних (списку) – рядок заголовка.

Щоб бази даних (списки) працювали ефективно, вони повинні відповідати встановленим вимогам:

1) Розмір і розташування списку:

на аркуші можна розташовувати будь-яку кількість списків;

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

2) Назви стовпців:

назви стовпців необхідно розміщувати в першому рядку;

не об'єднувати комірки назв;

не фарбуйте назви, їх досить виділити жирним шрифтом;

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

3) Зміст рядків і стовпців:

в стовпець вносити дані одного типу;

кожну запис виводити в окремому рядку;

всередині бази даних не залишати порожніх рядків і стовпців;

не допускати початкових і кінцевих пробілів;

якщо назва вулиці починається з цифри, наприклад, 12 Десантників, необхідно поставити перед цифрою апостроф.

У середовищі табличного процесора MS Excel створити базу даних із вибраної предметної області, як наприклад – основни роботи відділу кредитування філії №1 КБ "Банкірчик", вхідна інформація для якої відповідає рис. 1.1.

Завдання

1. Вводимо назву бази даних в комірки A1: I1 та строку заголовку (A2:I2) (рис. 1.1).

Рис. 1.1. Строка заголовку

 

2. Виділяємо строку заголовку та правою кнопкою миші визиваємо вікно «Формат ячеек…» за командою (рис. 1.2).

 

Рис. 1.2. Вікно «Формат ячеек»

3. Вносимо необхідні зміни. Встановимо потрібні типи значень для кожного поля бази:

діапазони B3:B38 – дата, а в D3:D38, E3:E38, L3:L38 встановлюємо тип даних – загальний;

діапазони F3:F38, G3;G38, J3:J38- числовий (рис. 1.3);

діапазони H3:H38 та I3:I38 – текстовий.

 

Рис. 1.3. Вікно встановлення типів даних для комірок

 

4. Виділимо потрібні діапазони, встановлюємо перевірку значень за командою ДАННЫЕ → ПРОВЕРКА(рис. 1.4).

 

 

Рис. 1.4. Встановлення параметрів перевірки даних

 

Існують наступні способи введення даних у базу даних (список):

a) використання форми даних, яка автоматично створюється після визначення заголовка бази даних за допомогою команди ДАННЫЕ → ФОРМА;

b) введення даних в БД набираючи текст в порожні рядки (безпосереднє введення даних);

c) використання майстра шаблонів для перетворення робочого листа Microsoft Excel у форму;

d) застосування VBA (Visual Basic for Appli cation) та інші.

Завдання

5. Встановлюємо рамку виділення у заголовний рядок бази даних та виконуємо команду ДАННЫЕ → ФОРМА (рис. 1.5).

 

Рис. 1.5. Форма для вводу значень

 

6. Невірно введене значення в полі Дата укладання договору (рис. 1.6):

 

 

Рис. 1.6. Повідомлення про невірно введене значення

7. Результат заповнення бази даних представлено на рис. 1.7.

 

 

Рис. 1.7. Заповнена база даних

 

8. У вікні «Форма» вивести інформацію про роботу відділу кредитування філії №1 КБ "Банкірчик (рис. 1.8).

 

 

Рис. 1.8. Інформація про роботі банків в БД за допомогою форм

 

Робота з базою даних в Microsoft Excel може здійснюється за трьома напрямками:

сортування – вибудовування записів даних в потрібному порядку;

відбір даних – вилучення записів даних з бази даних відповідно з деякими вимогами (критеріями);

аналіз даних – обробка різними засобами інформації, що знаходиться в базі даних або в відфільтрованих даних.

Базу даних можна сортувати:

за зростанням;

за зменшенням;

в логічному порядку;

в призначеному для користувача порядку.

У Microsoft Excel використовується наступний порядок сортування:

числа (від - ∞ до + ∞);

текст і формули;

значення БРЕХНЯ;

значення ІСТИНА;

значення помилок;

порожні значення.

При використанні сортування слід мати на увазі наступне:

порядок сортування даних у Microsoft Excel залежить від національних налаштувань Windows;

якщо необхідно впорядкувати (відсортувати) числові величини в алфавітному порядку, потрібно перед числовими величинами поставити апостроф, або відформатувати числа як текст;

для повернення до первісної бази даних необхідно ввести перед базою додаткове індексне поле, що містить зростаючу числову послідовність (1,2,3,4,5, ...). Тоді, виділивши клітинку у стовпці індексів і клацнувши навпроти По возрастанию, можна повернутися до первісної базі даних;

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

сортування по полях, здійснюється послідовними угрупованнями, починаючи з ключів самого нижчого рівня;

Microsoft Excel може сортувати не тільки рядки, але й стовпці.

Завдання

9. Для сортування запису у базі даних за полем Позичальник у відповідності до алфавіту виділіть заголовний рядок бази даних та виконайте команду ДАННЫЕ → СОРТИРОВКА(рис. 1.9).

 

 

Рис. 1.9. Визначення параметрів сортування даних

 

10. Зовнішній вигляд бази показано на рис. 1.10.

 

 

Рис. 6.10. База даних в MS Excel

Процес пошуку і відбору інформації в базі даних MS Excel називається фільтрацією. У MS Excel є два види фільтра: автофільтр і розширений фільтр.

Застосовуються такі критерії пошуку:

за точною відповідністю;

на основі порівняння – використовують різні операції порівняння: = 200, = (пропуск) - шукають порожні поля,>, <=, і т.д. Такі операції можна застосовувати до різних форматів даних – до чисел, тексту, окремих символів, дат, часу і т.д.;

за близькою відповідністю з використанням зразка – задають зразок пошуку, використовуючи символи шаблону -? або / та *. Для знаходження полів, що містять просто? або *, перед ними ставиться ~ (знак «тільда»): ~? або ~ *;

з пошуку відповідності з використанням множинного критерію з операціями И і ИЛИ - пошук даних по декількох умов.

Завдання

11. За допомогою користувальницького фільтру вивести інформацію про роботу відділення, наприклад, вивести мету кредитування – «Купівля нерухомості» (рис. 1.11).

Рис. 1.11. Налаштування користувальницького фільтру

12. Встановіть автофільтр для бази даних рис. 1.12.

Автофільтр дозволяє вивести на робочий лист всі записи, що задовольняють заданому критерію. Пошук з використанням автофільтру проводиться в наступному порядку:

1) Клацнути на будь-яку комірку всередині бази даних.

Рис. 1.12. Результат встановлення автофільтру

 

2) Виконати команду ДАННЫЕ → ФИЛЬТР → АВТОФИЛЬТР. Біля кожного поля рядка заголовка з'являться розкриваючі списки у вигляді кнопки з трикутником (див. рис. 1.13).

Зовнішній вигляд бази показано на рис. 1.13.

 

 

Рис. 1.13. Результат застосування автофільтру за умовою