4.6. Робота з базами даних (списками)

Організувавши табличні дані у формі списку (бази даних), користувач одержує можливість виконувати в ET дії, подібні до процедур обробки баз даних у системах керування базами даних (наприклад, введення та редагування даних через спеціальні вікна, сортування та відбір да­них, групування та підбиття підсумків). Зазвичай таблиця-список суттєво відрізняється від баз даних у фахових системах керування базами даних, але наявність спеціальних команд і функцій для керування такою "ба­зою" істотно спрощує роботу й розширює можливості обробки даних. Зауважимо, що крім зазначених можливостей ЕТ підтримують обмін даними із системами керування базами даних, уможливлюючи читання або зберігання даних у традиційних форматах "справжніх" баз даних.

Створення списку. Список (таблиця бази даних)— це таблиця на робочому аркуші, побудована за принци­пами бази даних. Призначених для зберигання великих обсягів структурованої інформації. Список представляється у формі двувимірної електроної таблиці - відношень (relatuion).

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

Необхідно уникати порожніх рядків і стовпчиків всередині списку. Максимальний розмір списку обмежений хіба що розміром робочого аркуша.

EТ розпізнає списки автоматично. Перед виконанням дій зі списком достатньо активізувати будь-яку клітинку всередині списку. Вміст першого рядка ET вважає іменами полів, які він не опрацьо­вує, як інші дані. Якщо перед обробкою списку виділити окрему частину списку, то ET вважатиме списком тільки виділений діапазон клітинок.

Дії зі списками: організація введення даних або створення; перегляд даних; сортировка та фільтрація даних; підведення ітогів.

Як бачимо, створити список можна, використовуючи традиційні засоби роботи з таблицями. Задавши імена полів і порядок їх розміщення, визначивши ширину стовпчиків і параметри форматуван­ня (насамперед для дат, часу, числових значень), можна вводити записи списку. Список може також містити значення полів, отриманих у результаті обчислення формул.

При створенні списків доречно скористатися такими порадами.

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

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

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

Для роботи з великими списками, дані яких не відображуються одночасно в робочому вікні, використовують команду Окно Закрепить области для фиксирования областей або Окно  Разделить для поділу вікна на підвікна.

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

Використання форми даних (тільки у Excel). Хоча для додавання, видалення та редагування даних списку можна використовувати традиційні можливості програми, Excel для роботи зі списками додатково надає користувачеві стандартну екранну форму даних. Форма даних не тільки пропонує певні зручності при внесенні змін у список, а й дає змогу здійснювати відносно простий відбір даних за зазначеним критерієм, надає інформацію про кількість записів і номер поточного запису. У вікні форми даних можна вико­ристовувати до 32 полів списку.

Для виведення на екран вікна форми даних достатньо розмісти­ти активну клітинку в діапазоні списку і скористатися командою Данные  Форма... (Рис. 4.12). Якщо перед викликом вікна форми даних був виділений певний діапазон клітинок, то у вікні форми відображаються тільки поля і записи, включені в діапазон виділення. EТ автоматично розпізнає імена полів списку.

Рис.4.12. Введення баз даних у форму (Excel)

Для керування вікном форми даних можна використовувати відповідні кнопки та лінійку прокручування. Наприклад, скориставшись кнопками Добавить і Удалить, можна ввести новий або видали­ти поточний запис. За допомогою кнопки Вернуть можна скасувати зміни, внесені в поточний запис. Скориставшись кнопкою Критерии, можна ввести умову для пошуку та відбору записів списку. При запи­суванні критеріїв можна використовувати:

• традиційні відношення порівнянь ("=","<",">","<>","<=","=>");

• константи відповідних типів;

• для текстових полів крім звичайних символів символи шаблона "*" (для заміни довільної кількості символів) і "?" (для заміни одного символу).

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

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

Для роботи з базами даних використовується команда головного меню Данные. Для сортування списку достатньо розмістити активну клітинку в діапазоні списку і скористатися командою Данные Сортировка... програма автоматично визначає розмір списку, рядок з іменами полів (який не треба переміщувати) і відкриває вікно діалогу Сортировка диапазона, де обирають до трьох полів для сортування з урахуванням їх взаєм­них пріоритетів та порядку, що збільшується чи зменшується. При сортуванні для першого (найголовнішого) поля можна враховувати порядок, що визначається власним списком користувача. Вибрати потрібний власний список можна у вікні діалогу Параметри сортировки, відкривши його натисканням кнопки Параметры...

Якщо результат сортування потрібно скасувати, можна скористатися командою Правка Отменить Сортировка або комбінацією клавіш Ctrl+Z.

Крім того, доцільно зважати й на такі поради.

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

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

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

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

При сортуванні списків, що містять формули, потрібно дотримуватись простих правил:

• у формулах посилання на клітинки (адреси), що належать до полів одного запису, оформлювати як відносні;

• посилання на клітинки поза списком оформлювати у формулах тільки як абсолютні;

• уникати у формулах посилань на клітинки, що належать до полів з інших записів.

Відбір даних засобами Автофильтра. За допомогою фільтрів, що вбудовані в EТ, можна відібрати та відобразити тільки ті записи, які задовольняють задані критерії (умови). Це спрощує процес пошуку потрібної інформації, редагування та видалення записів, допомагає аналізувати дані. ЕТ надають дві команди для фільтрації даних:

• для простих критеріїв — Автофильтр;

• для складних критеріїв — Расширенный фильтр.

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

Можливостями Автофільтра можна скористатися, розмістивши активну клітинку всередині списку і скориставшись командою ДанныеФильтрАвтофильтр (Рис.13). Кнопки зі стрілкою, що з'являються справа від кожного імені поля, дають змогу відкрити перелік значень поля і накласти обмеження, створивши критерій відбору за кількома полями. У результаті відбору відображаються тільки ті записи, що відповідають заданому критерію, а рядок стану містить повідомлення про кількість знайдених записів.

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

Елемент Все списку дає змогу зняти обмеження щодо поля.

За допомогою елемента Первые 10... користувач може відібрати певну кількість записів з найбільшими або найменшими значеннями в числовому полі. Вікно діалогу Наложение условия по списку дає змогу вибрати максимальні або мінімальні значення, а також встано­вити числове або процентне обмеження на кількість потрібних записів.

Можна задавати й складніші критерії, ніж перевірка рівності. Скориставшись елементом списку Условие..., можна відкрити вікно діалогу Пользовательский автофильтр і з'єднати за допомогою логічних операторів окремі умови відбору записів за одним полем (з будь-якими відношеннями порівняння). При визначенні критеріїв за текстовими полями можна використовувати також символи шаблона "?" та "*".

Застосувати автофільтр можна тільки до одного списку на робо­чому листі.

Для відображення всіх записів списку можна скористатись командою ДанныеФильтрОтобразить все. Для відміни (деактивізації) автофільтра потрібно повторно скористатись командою Данные ФильтрАвтофильтр.

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

Використання Расширенного фильтра. Для фільтрації списків програм ЕТ крім команди Автофильтр пропонує користувачам команду Расширенный фильтр, яку використовують для створення складних умов відбору даних (Рис. 4.13).

Команда Расширенный фильтр має додаткові можливості порів­няно з командою Автофильтр, а саме:

- задавати для кількох стовпців умови, з'єднані логічним опера­тором ИЛИ.;

- задавати три або більше умов для конкретного стовпця з вико­ристанням хоча б одного логічного оператора ИЛИ;

- задавати обчислювані умови.

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

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

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

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

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

Рис.4.13. Використання інструмента Расширенный фильтр

У діапазоні умов можна ввести будь-яку кількість умов.

Програма сприймає їх за таким правилом:

- умови одного рядка вважаються з'єднаними логічним оператором И (тобто всі умови повинні виконуватись одночасно);

- умови у двох рядках вважаються з'єднаними логічним операто­ром ИЛИ (тобто достатньо виконання хоча б однієї умови).

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

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

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

Задаючи текстові умови, слід дотримуватися таких правил:

- одна літера означає, що пошуку підлягають усі значення, що по­чинаються із зазначеної літери;

- символ ">" або "<" означає, що пошуку підлягають значення, що за абеткою стоять після введеного текстового значення або перед ним;

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

Після введення діапазону умов відбору слід виконати команду Данные Фильтр Расширенный фильтр.

У вікні цієї команди треба ввести в поле Исходный диапазон діапа­зон клітинок таблиці, до якого буде застосовано розширений фільтр, а в поле Диапазон условий — діапазон клітинок робочого аркуша, де задаються умови відбору рядків, що містять заголовки стовпців.

Перемикач Обработка слід встановити в положення Фильтровать список на месте.

У результаті виконання команди Расширенньій фильтр прихо­вуються всі рядки, що не задовольняють введені умови. Номери відібраних рядків виділяються синім кольором.

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

- заголовок над обчислюваною умовою повинен відрізнятися від будь-якого іншого заголовка стовпця у списку. Заголовок умо­ви може бути порожній або містити довільний текст;

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

- посилання на клітинки списку повинні бути відносними (за од­ним винятком, який розглянемо далі).

Копіювання відфільтрованих рядків в іншу частину робочого аркуша. За допомогою вікна діалогу команди Расширенный фильтр можна встановити режим копіювання відібраних рядків в іншу частину аркуша замість виведення відфільтрованого списку. Щоб скопіювати рядки, треба перемикач Обработка встановити в по­ложення Скопировать результат в другоє место, а в полі Поместить результат в диапазон зазначити адресу діапазону, куди по­трібно скопіювати інформацію.

Режим Только уникальные записи у вікні команди Расширенный фильтр додає додатковий фільтр до заданих умов відбору. Він приховує рядки, що повторюються.

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

Операції підсумовування є різні: сума, максимум, мінімум, середнє значення, відхилення від норми тощо.

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

Для вилучення розрахованих проміжних підсумків слід установити курсор в БД й активізувати команди Данные  Итоги...  Убрать все.

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

1. Що таке список в Ехсеl?

2. Як створити потрібний список?

3. Які можливості надає користувачу форма даних?

4. Як скасувати внесені в запис зміни?

5. Як відсортувати список за власним порядком сортування?

6. Як забезпечити можливість відновлення початкового порядку записів списку?

7. Можливості автофільтра.

8. Можливості команди Расширенный фильтр.

9. Що таке обчислювані умови і як їх задають?

10. Як скопіювати відфільтровані дані в іншу частину робочого аркуша?

Рис.4.14. Отримання пыдсумкових результатыв у базах даних