Робота з базами даних в MS Excel. Вивчення можливостей пакету MS Excel при роботі з базами даних. Набуття навичок створення і обробки БД. Створення

 

 

Зміст

 

 

Вступ

 

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

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

В основному ми будемо мати справу з базами даних. База даних (БД) - систематизоване сховище даних.

Тому на даній науковій практиці ми повинні розібратися :

По-перше з поняттям  бази даних та основними її об’єктами;

По-друге з формулами в електронній таблиці MS Excel;

По-трете з базою даних в MS Excel.

На кінець роботи ми повинні закріпи знання по роботі з Power Point, створивши презентацію у розмірі 12-15 слайдів.

 

Розділ 1. Робота з базами даних

 

1.1. Поняття бази даних

 

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

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

Систематизоване сховище даних називається базою даних (БД).

Сучасні бази даних створюються на електронних носіях. Це забезпечує надійне збереження даних і зручні засоби роботи з ними. Для створення баз даних і маніпулювання ними використовують спеціальні програми — системи управління базами даних (СУБД). Основними функціями СУБД є:

введення даних у базу та їх збереження;

редагування даних;

пошук даних у базі.

Широкого розповсюдження набули СУБД, які зберігають базу даних з інструментами для її опрацювання. До таких СУБД належить Microsoft Access.

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

Класифікація баз даних

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

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

2. Документальними є бази даних, які відображають конкретні документи та містять їх бібліографічний опис. Інформація в документах може бути зафіксована в текстовому, звуковому або графічному вигляді. Документ обов’язково має реквізити (номер, дату затвердження, місце затвердження, підпис тощо), за якими його можна знайти. Документальні БД є дуже зручними в роботі багатьох фахівців. Наприклад, базами, що містять закони про оподаткування, користуються бухгалтери та економісти; базами даних зі зведенням законів про освіту користуються керівники шкіл, дитячих установ та вищих навчальних закладів.

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

1. В ієрархічній базі даних усі елементи розташовуються залежно від їх підпорядкування. Один елемент вважається головним. Йому підпорядковуються елементи першого рівня. У свою чергу, елементам першого рівня підпорядковуються елементи другого рівня і так далі. Тобто створюється структура впорядкованих елементів, яка називається ієрархією.  Наприклад, ієрархічну базу даних створюють каталоги і файли, що зберігаються на диску.

2. У мережній базі даних між елементами теж існує зв’язок, але зв’язки можуть бути довільними. Наприклад, мережну базу даних створює асортимент товарів меблевої фірми «Меблі», що має мережу магазинів, кожний з яких пропонує різні види меблів певних виробників.

3. Інформацію, що зберігається  в базі даних, можна подати  у вигляді таблиць. Наприклад, база  даних фірми «Меблі» складається  з таблиць, що містять дані  про магазини фірми та їх  асортимент, а також виробників, з якими фірма має домовленість про постачання товарів.  Відомості про виробника меблів (реквізити виробника, адреса, телефони тощо) повинні зазначатися на рахунку, який надається покупцеві. Проте ці дані можуть зберігатися в одній таблиці та автоматично заноситися до кожного рахунку, що видається в будь-якому з магазинів фірми.  Бази даних, які зберігають відомості у вигляді таблиць, між якими встановлені зв’язки,  називають реляційними (від англ. relation — відношення).

Основні поняття реляційних баз даних

Таблиця складається з рядків і стовпців. Кожний стовпець містить певну характеристику,  наприклад виробів, а кожний рядок — сукупність таких характеристик про окремий виріб.

У термінах СУБД назви стовпців називають іменами полів, а самі стовпці — полями.  Рядки називають записами. У різних записах значення окремих полів можуть повторюватися, проте бажано, щоб таблиця містила таке поле (або групу полів), значення в якому не повторюються і є унікальними для кожного запису. Таке поле називається ключовим.

За ключовим полем можна однозначно встановити запис.

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

Основні об’єкти  бази даних

Об’єктами БД є таблиці, форми, запити, звіти, макроси, модулі, сторінки.

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

Їх вважають головним об’єктом БД.

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

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

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

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

Модуль – це програма мовою VBA, яку використовують для реалізації нестандартних процедур при створенні програм.

Сторінки доступу до даних – веб-сторінка, призначена для додавання, редагування, перегляду або маніпулювання поточними даними в БД.

Таблиці та форми використовують для обслуговування змісту БД, а запити та звіти виконують основну функцію БД – перетворення та подання інформації, яка необхідна користувачу.

 

1.2. Робота з формулами в електронній таблиці MS Excel

 

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

Запис формули починається знаком =.

Ввід формули можна здійснювати безпосередньо, помістивши табличний курсор в потрібну комірку і набравши за допомогою клавіатури знак дорівнює (=), а далі саму формулу. Можна також скористатись кнопкою Змінити формулу, клацнувши на ній мишею. Ввід адрес комірок у формулу можна здійснювати клацанням мишею на відповідних комірках. Наприклад, для вводу формули =А1*В1 потрібно клацнути на кнопці Змінити формулу або набрати знак =, після чого, клацнути на комірці А1, далі, набрати знак *, клацнути на комірці В1, натиснути клавішу Enter.

MS Excel містить дуже велику кількість  вбудованих функцій. Для зручного  їх вибору зі списку при  вводі, вони поділені на категорії: математичні, фінансові, інженерні, текстові, інформаційні та інші. Детальний  опис усіх вбудованих (деколи  ще кажуть – стандартних) функцій  в рамках даного курсу неможливий  по двох причинах: по-перше, функцій  дуже багато, а по-друге, опис деяких  категорій потребує спеціальних  знань. Тому ми розглянемо лише  найбільш важливі з них. В той  же час, розглянутих функцій цілком  достатньо для виконання завдань  контрольної роботи і розуміння  матеріалу курсу.

В Excel немає вбудованої функції для запису кореня n-го степеню. Для його обчислення потрібно скористатись функцією СТЕПЕНЬ(a; b).

 

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

 

Загальні правила формування бази даних:

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

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

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

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

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

фон сусідніх записів доцільно чергувати: при цьому поліпшується сприйняття даних користувачем;

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

Формати файлів баз даних

Базу даних, створену засобами Excel, можна зберегти не тільки у стандартному форматі книги (.xls), а й у звичному для баз даних форматі, наприклад, dbf-файлі. У цьому випадку програма коректно визначає типи полів (текст, числове, дата) та їхні довжини (для тексту) на підставі наявних даних. Для створення dbf-файлу достатньо вибрати значення DBF 4 (dBASE IV) (*.dbf) у полі Тип файлу діалогового вікна Збереження документа, яке викликається командою Файл/Зберегти як...

Excel дає змогу також  працювати з dbf-файлами та файлами баз даних системи керування базами даних (СКБД) Microsoft Office Access. Для відкриття таких файлів у полі Тип файлу діалогового вікна Відкриття документа треба, відповідно, вибрати значення Файли dBase (*.dbf) або Бази даних Access (*.mdb; *.mdb). Іншим способом відкриття таких файлів є використання команди Дані/Імпорт зовнішніх даних/Імпорт даних...

Для побудови запитів до баз даних, створених спеціалізованими СКБД (наприклад, SQL Server, MS Access, Paradox, FoxPro), в Excel є спеціальний засіб - програма MS Querry, яку активізують командою Дані/Імпорт зовнішніх даних/Створити запит. Перевага використання запитів полягає в тому, що в Excel переноситься лише потрібна (відфільтрована) частина записів з бази даних.

Створення бази даних

Базу даних, як і будь-яку книгу Excel, створюють командою Файл—Создать. Спочатку можна ввести назву таблиці, наприклад «Відомості про замовлення товарної продукції», потім — її заголовний рядок із однорядковими та короткими іменами полів: «Замовник», «Шифр товару» і т. Далі потрібно замінити системне ім'я бази «Книга1» на призначене для користувача, наприклад на «Замовлення», клацнувши мишею на кнопці Сохранить.

Після натиснення мишею на будь-якій із комірок таблиці командою Данные—Форма на екран викликається діалогове вікно, де відображається список уведених даних .

Значення полів запису вводять до відповідних комірок діалогового вікна у тій послідовності, в якій вони сформовані в таблиці. Перехід між комірками виконується за допомогою клавіші <Tab> або клавіш керування курсором. Усі комірки вікна мають таку ж ширину, як і найширше поле (стовпець) бази. При потребі її можна збільшити (зменшити) «буксируванням» правої межі стовпця таблиці. Запис передають у кінець бази даних командою Добавить. Після цього вводять наступний запис і т. п. до кінця бази.

Використання фільтра та сортування даних

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

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

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

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

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

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