ЛАБОРАТОРНА РОБОТА 12
РОБОТА З БАЗАМИ ДАНИХ У MICROSOFT EXCEL Мета роботи: придбання практичних навичок роботи з базами даних в MICROSOFT EXCEL Завдання: 4. Ознайомитися з теоретичним матеріалом до теми. 5. Результати роботи подати у вигляді звіту. 6. Підготуватися до захисту роботи. Теоретичний матеріал до теми За допомогою Microsoft Excelможна створювати і обробляти бази даних. База даних у Microsoft Excel– таблиця, що складається з однотипних записів (рядків). Стовпці таблиці є полями запису в базі даних. Під імена полів приділяється перший рядок у базі даних. Наприклад, якщо базою даних вважати телефонний довідник, то полями запису будуть: прізвища, номера телефонів і адреси абонентів.
Рис. 1 Для роботи з базою даних необхідно спочатку створити відповідну таблицю. Якщо виділити вічко в таблиці і вибрати одну з команд обробки баз даних у меню Данные ,Microsoft Excel автоматично визначає й обробляє всю таблицю. Дані, розташовані в стовпцях і рядках робочого аркушу, обробляються як набір полів, що утворюють записи(рис.1). Сортування даних Сортування дозволяє переупорядкувати рядки в таблиці по будь-якому полі. Наприклад, щоб упорядкувати дані за ціною виробу. Для сортування даних слід виділити одне вічко таблиці і викликати команду Сортировка із менюДанные. У полі прихованого переліку Сортировать по (рис.2) вибирається поле, по якому будуть відсортовані дані, і тип сортування: по возростанию – цифри сортуються по зростанню, текст – за абеткою, логічні вираження – ЛОЖЬ передує ИСТИНА . по убыванию –сортування в зворотному порядку. У полі прихованого переліку Затем по указується поле, по якому будуть відсортовані дані, що мають однакові значення в першому ключовому полі. У другому полі Затем по указується поле, по якому будуть відсортовані дані, що мають однакові значення в перших двох ключових полях. Для сортування даних також використовуються кнопки . Перед їх використанням слід виділити стовпець, по якому необхідно сортувати таблицю. При сортуванні по однім стовпці, рядки з однаковими значеннями в цьому стовпці зберігають колишнє упорядкування. Рядки з порожніми коміркими в стовпці, по якому ведеться сортування, розташовуються наприкінці переліку, що сортується. Microsoft Excel дозволяє також сортувати не всю таблицю, а тільки виділені рядки або стовпці. Рис. 2 (відсутні дані для сортування)
Форми даних
Рис. 3 (відсутні дані для форми) У Microsoft Excel не слід робити нічого особливого для того, щоб використовувати перелік в якості бази даних. При виконанні характерних для баз даних операцій, таких як пошук, сортування, підведення підсумків, Microsoft Excel автоматично розглядає таблицю як базу даних. При перегляді, зміні, додаванні і видаленні запису в базі даних, а також при пошуку конкретних записів за визначеним критерієм зручно використовувати форми даних. При звертанні до команди Форма меню Данные Microsoft Excel читає дані й створює діалогове вікно форми даних (рис.3). У формі даних на екран виводиться один запис. При уведенні або зміні даних у полях цього вікна змінюється уміст відповідних комірок у базі даних. Для використання форм даних таблиця повинна мати імена стовпців. Імена стовпців стають іменами полів у формі даних. Поле відповідає кожному стовпцю в таблиці. Форма даних автоматично розгортається так, щоб вивести на екран відразу усі поля в даній таблиці, до 32 полів за один раз. За допомогою смуги прокручування можна прокручувати записи в базі даних. Позиція виведеного запису вказується у верхньому правом куті. Пересуватись по полях форми можна за допомогою миші та клавіш Tab (униз), Shift+Tab (угору). Праворуч розташовані такі кнопки. Добавить – очищує поля для уведення нового запису бази даних. Якщо знову натиснути кнопку Добавить,то уведені дані будуть додані як новий запис у кінець бази даних. Удалить – видаляє виведений запис, інші записи бази даних зсуваються. Видалені записи не можуть бути відновлені. Вернуть – відновлює відредаговані поля у виведеному запису, видаляючи зроблені зміни. Щоб відновити запис, необхідно зробити це перед натисканням клавіші Enter або перед переходом до іншого запису. Назад – виводить попередній запис у переліку. Якщо був визначений критерій за допомогою кнопки Критерии ,то кнопка Назад виведе попередній запис із тих, що задовольняють заданому критерію. Далее – виводить наступний запис у базі даних. Критерии – очищає поля для уведення критеріїв порівняння з операторами порівняння для пошуку необхідної підмножини записів. Правка – слугує для виходу з режиму уведення критеріїв. Доступна тільки тоді, коли натиснута кнопка Критерии . Очистить – видаляє існуючий критерій із вікна діалогу. Доступна тільки тоді, коли натиснута кнопка Критерии . Закрыть – закриває форму даних. Для додавання запису до бази даних необхідно: виділити вічко в таблиці, до котрого слід додати запис; у меню Данные вибрати команду Форма ; натиснути кнопку Добавить ; заповнити поля нового запису; для переміщення до наступного поля натиснути клавішу Тab ; після уведення даних натиснути клавішу Enter для додавання запису; після додавання усіх необхідних записів, натиснути кнопку Закрыть . Нові записи будуть додані в кінець бази даних. Установлення інтервалу критеріїв Критерії бувають двох типів. 1. Критерії обчислення – це критерії, що є результатом обчислення формули. Наприклад, інтервал критеріїв =F7>СРЗНАЧ($F$7:$F$21 ) виводить на екран рядки, що мають у стовпці F значення більше, ніж середнє значення розмірів у комірких F7:F21 . Формула повинна повертати логічне значення ЛОЖЬ абоИСТИНА .При фільтрації будуть доступні тільки ті рядки, значення яких будуть додавати формулі значення ИСТИНА . 2. Критерії порівняння – це набір умов для пошуку, використовуваний для витягу даних при запитах за прикладом. Критерій порівняння може бути послідовністю символів (константою) або вираженням (наприклад, Ціна > 700 ). Для пошуку за допомогою форми даних записів, що відповідають критерію, необхідно: виділити вічко в таблиці; у меню Данные вибрати команду Форма ; натиснути кнопку Критерии ; у полях редагування увести критерії для пошуку даних; для виводу на екран першого запису, що відповідає критерію, натиснути кнопку Далее ; для виводу на екран попереднього запису, що відповідає критерію, натиснути кнопку Назад ; для пошуку записів у переліку по іншому критерії натиснути кнопку Критерии і увести новий критерій; по закінченні натиснути кнопку Закрыть . Щоб знову одержати доступ до усіх записів переліку необхідно натиснути кнопку Критерии , а потім натиснути кнопку Правка. Команда Фильтр меню Данные дозволяє відшукувати і використовувати потрібну підмножину даних у переліку. У відфільтрованому переліку виводяться на екран тільки ті рядки, що містять визначене значення або відповідають визначеним критеріям, при цьому інші рядки виявляються приховані. Для фільтрації даних використовуються команди Автофильтр і Расширенный фильтр з пункту Фильтр меню Данные . Автофільтр Команда Автофильтр установлює кнопки прихованих переліків (кнопки зі стрілкою) безпосередньо в рядок із іменами стовпців (рис.4). З їхньою допомогою можна вибрати записи бази даних, що слід вивести на екран. Після виділення елементу в переліку, що розкривається, рядки, що не містять даний елемент, будуть приховані. Наприклад, якщо у прихованому переліку поля Цена вибрати 99грн. , то будуть виведені тільки записи, в яких у полі Цена міститься значення 99грн .
Рис. 4
Рис. 5 Якщо у прихованому переліку вибрати пункт Условие …, то з’явиться вікно Пользовательский автофильтр (рис.5). У верхньому правому переліку слід вибрати один з операторів (равно, больше, меньше та ін.), а у полі праворуч вибрати одне зі значень. У нижньому правому переліку можна вибрати іншій оператор, і у полі ліворуч – значення. Коли увімкнений перемикач И , то будуть виводитися тільки записи, які задовольняють обидві умови. При увімкненому перемикачу ИЛИ будуть виводитися записи, які задовольняють одну з умов. Наприклад, у вікні на рис.36 введені умови для виведення записів по виробам з ціною більше 99грн і менше 187грн . Для виведення декількох записів з найбільшими або найменшими значенням по будь-якому полю слід у прихованому переліку поля вибрати пункт Первые 10 . У діалоговому вікні Наложение условия по списку у першому полі з лічильником необхідно вибрати кількість записів, а у полі праворуч вибрати наибольших або наименьших . Щоб вивести усі дані переліку, необхідно викликати команду Отобразить все або скасувати команду Автофильтр меню Данные ,підміню Фильтр . Розширений фильтр Команда Расширенный фильтр дозволяє фільтрувати дані з використанням інтервалу критеріїв для виводу тільки тих записів, що задовольняють визначеним критеріям (рис.37). При повторній фільтрації будуть проглядатися усі рядки, і приховані і відкриті. Значення перемикачів і полів вікна Расширенный фильтр наступне:
Рис. 6 фильтровать список на месте – перемикач, що приховує рядки, які не задовольняють зазначеному критерію; скопировать результат в другое место – копіює відфільтровані дані на інший робочий аркуш, або на інше місце на тому ж робочому аркуші; Исходный диапазон – поле, що визначає інтервал, якій містить перелік, що підлягає фільтрації; Диапазон условий – поле, що визначає інтервал комірок на робочому аркуші, якій містить необхідні умови; Поместить результат в диапазон – поле, що визначає інтервал комірок, в який копіюються рядки, що задовольняють визначеним умовам; це поле активно тільки в тому випадку, якщо обраний перемикач скопировать результат в другое место; Только уникальные записи – перемикач, що виводить тільки рядки, що задовольняють критерію і не містять неповторюваних елементів. Якщо інтервал критеріїв не визначений, то в цьому випадку усі рядки переліку, що містять дублікати, будуть приховані. Для установлення складних критеріїв необхідно: 1. уставити декілька рядків у верхній частині робочого аркушу; 2. в одному із уставлених порожніх рядків увести імена стовпців, по яких слід відфільтрувати перелік; 3. при використанні критеріїв порівняння, імена критеріїв повинні бути ідентичні іменам стовпців, що перевіряються; 4. у рядках, розташованих під рядком із іменами стовпців, що перевіряються, увести критерії, яким повинні відповідати комірки стовпців, що перевіряються; 5. вибрати в меню Данные підміню Фильтр ,а потім команду Расширенный фильтр ,і в діалоговому вікні увести умови фільтрації.
Рис. 7 Для об’єднання критеріїв за допомогою умовного оператораИ потрібно зазначити критерії в одному і тому ж рядку, а для об’єднання критеріїв за допомогою умовного оператора ИЛИ слід подати критерії в різних рядках. Наприклад, інтервал критеріїв на рис.38 виводить на екран усі записи, що мають у стовпці Цена значення більше 50 і менше 100. Після використання команд Автофильтр або Расширенный фильтр ,таблиця переходить у режим фільтрації. У цьому режимі багато команд Microsoft Excel впливають тільки на зримі комірки. Після застосування фільтру для виводу тільки потрібних рядків, можна скопіювати отриману підмножину даних в інше місце для подальшого аналізу. Щоб знову вивести усі записи слід у меню Данные вибрати пункт Фильтр і потім пункт Отобразить все ©2015 arhivinfo.ru Все права принадлежат авторам размещенных материалов.
|