Міністерство освіти та науки України

Київський національний торговельно-економічний університет

Вінницький торговельно-економічний інститут

Кафедра інформаційних систем та мереж

Лабораторна робота

з дисципліни

"Комп'ютерний практикум"

на тему:

"Створення бази даних охоронної агенції «Гарант»

в Microsoft Excel та Access"

Вінниця 2008

Вступ

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

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

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

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

За допомогою Excel можна переглядати велику кількість варіантів розв´язування поставленої задачі і вибирати найоптимальніший варіант для рішення та використовувати принцип What-If-Else, тобто продивитися, яким чином на вихідні результати впливають нові вхідні результати.

До складу Microsoft Excel включено об´єктно орієнтовану мову четвертого покоління Visual Basic of Applications (VBA), за допомогою якої можна не тільки автоматизувати потрібні розрахунки, а й вести бухгалтерський облік, планувати та оптимізувати випуск і збут продукції та вирішувати практично будь-які економічні завдання на конкретному підприємстві.

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

Метою роботи є показ, на власних прикладах, можливостей обрахунку у Microsoft Excel та способи організації бази даних у Microsoft Access.

Завдання роботи:

• показати принцип роботи з базою даних у Microsoft Excel;

• створення реляційної бази даних.

Лабораторна робота №1. Створення інформаційних таблиць бази даних

Хід роботи

Завантаження Microsoft Access можна виконати за допомогою меню Пуск®Программы®Microsoft Access або скористатися піктограмою програми. На екрані з’явиться вікно , в якому треба задати потрібну операцію - створити або відкрити існуючу БД. Далі на екрані з’явиться вікно Microsoft Access. Створення бази даних виконується за допомогою меню Файл®Создать базу даних або відповідні піктограми на панелі інструментів.

Таблиці створюються у два етапи: на першому етапі створюється структура таблиці, а на другому – вводяться дані.

Спочатку створюємо таблицю Послуги. Для цього вибираємо вкладнику Таблицы, потім натискаємо на кнопку Создать та вибираємо метод створення таблиці (Рис.1).

Створення таблиці у режимі Конструктор:

Для цього потрібно вибрати Конструктор із списку і натиснути ok. На екрані з'явиться вікно. У графу Имя треба ввести ім’я полів таблиці. Ім’я поля не повинно містити більше 64-х символів, включаючи пропуски. У графі Тип данних треба задати типи полів. Для цього необхідно розкрити список (Рис.2) та вибрати потрібний тип даних. В Access застосовуються такі типи даних:

• Числовой - застосовується для числових даних, які використовуються у формулах.

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

• Поле МЕМО - використовується для уведення текстів або чисел довжиною до 64000 символів.

• Дата,время - довжина поля 8 байтів.

• Счетчик - використовується для автоматичного додавання номера запису.

• Логический - застосовується до полів, що можуть містити тільки одне з двох значень, такі як Да (Нет), Истина (Ложь)

• Поле обьекта ОLE - використовується для зв’язування або впровадження об’єктів, електронних таблиць, рисунків, тощо.

• Мастер подстановок - використовується для визначення поля, за допомогою котрого можна вибрати значення з іншої таблиці або зі списку значень.

В області Свойства поля необхідно задати властивості поля.

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

Аналогічним чином створюємо таблиці Працівники і Постачальники.

Встановлення зв’язків між таблицями

Ця робота розпочинається з натискання на значок “Схема данных”(), що розташований на панелі інструментів. В результаті відкривається вікно з такою же назвою “Схема данных”, де вже існують графічні образи зв¢заних при підстановці таблиць. Решта таблиць може бути додана при необхідності. Дія “Изменить связь” – редагування зв¢язку- передбачено діалог, який містить питання щодо цілісності даних. Якщо забезпечити цілісність (вибір значком “галочка”), то отримаємо зв¢язок “один-до-багатьох”. Якщо між таблицями попередньо ніякі зв¢язки не встановлені, то встановити іх можна за допомогою лівої кнопки миші: необхідно не відпускаючи кнопки миші з¢єднати нею відповідні поля. В результаті відкриється вікно “Связи”, де необхідно вибрати “Обеспечение целостности данных” (Рис.3).

Рис. 3

Лабораторна робота №2. Створення екранних форм

Хід роботи

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

В Access є три стандартних види форм, які будуються автоматично :

• стовпцеві (В столбец) - всі поля запиту чи таблиці розташовуються на формі в одному чи кількох стовпчиках;

• стрічкова (Ленточная) – всі поля запиту чи таблиці розміщуються на формі у вигляді, подібному до електронної таблиці;

• таблична (Табличная) – форма відкрита в режимі таблиці.

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

Автоформи

В столбец (Рис.4):

1. Обрати вкладку Формы

2. Натиснути кнопку Создать ® Автоформа в столбец. Обрати із списку джерело даних.

У результаті з’явиться вікно форми, де всі поля розташовані в стовпець. Такі форми зручні для швидкого виведення даних. Порядок полів форми відповідає порядку полів у таблиці чи запиті: перше поле таблиці чи запиту буде першим полем форми.

Рис. 4

Ленточная форма

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

1. Обрати вкладку Формы

2. Натиснути кнопку Создать ® Автоформа: ленточная. Обрати із списку джерело даних.

Створення форм за допомогою майстра (Рис.6):

1. Вибрати вкладинку Формы та натиснути на кнопку Создать.

2. У вікні Новая форма вибрати джерело даних (Продукція), у списку засобів створення форми вибрати Мастер форм та натиснути на кнопку Ok.

3. Вибрати поля, які повинні вводитися у форму.

4. Вибрати зовнішній вигляд форми (наприклад, В один столбец) та натиснути на кнопку Далее.

5. Задати стиль форми та натиснути на кнопку Далее.

6. Увести ім’я форми та натиснути на кнопку Готово.

Рис. 6

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

Створення підпорядкованих форм(Рис. 7):

Наприклад, створити форму, яка основана на таблиці Виробники і містить підпорядковану форму, яка основана на таблиці Товар:

1. Вибрати вкладинку Форми, натиснути на кнопку Создать, вибрати Мастер форм.

2. Вибрати таблицю Виробники та необхідні поля.

3. Вибрати таблицю Товар та необхідні поля, натиснути Далее.

4. Натиснути на селекторну кнопку Подчиненные формы та набрати таблицю, по якій буде створена головна форма- Виробники. Натиснути Далее.

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

6. Вибрати стиль форми та натиснути Далее.

7. Уваести імена головної та підпорядкованої форми і натиснути Готово.

Рис. 7

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

Один з цікавих засобів роботи з формами в Microsoft Access – можливість подальшого відбору інформації , яка виводиться в формі, без створення нового запиту. Це можна виконати за допомогою фільтра, який задається під час роботи з формою. Якщо застосувати фільтр, то на екран будуть виведені тільки ті дані, які задовольняють задані умови. Фільтр можна застосовувати тільки до записів основної форми, але не підлеглої. Існує декілька типів фільтрації:

Фільтр по виділеному:

1. Відкрити таблицю Товар (запит або форму).

2. Виділити значення, яке повинні містити записи (Напівчеревики чоловічі).

Вибрати меню Записи®Фильтр®Фильтр по выделенному, або натиснути кнопку .

Для відміни дії фільтру вибрати меню Записи®Удалить фильтр або натисути .

Фільтр звичайний:

Дозволяє відібрати записи шляхом уведення критеріїв у порожню таблицю. Наприклад, з таблиці Товар відібрати сандалі дитячі ціною більше 170 грн:

1. Відкрити таблицю Товар (або форму).

2. Вибрати меню Записи ®Фильтр ®Изменить фильтр або натиснути на .

3. На екрані з´явиться таблиця для введення критеріїв, у полі Назва товару обрати сандалі дитячі, а у полі Ціна- >170.

4. Вибрати меню Записи ®Применить фильтр або натиснути на відповідну кнопку.

Розширений фільтр:

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

1. Відкрити таблицю Товар (або форму).

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

3. Додати поля Назва товару, Ціна.

4. Задати сортування по спаданню.

5. Задати <400 у рядку Условие отбора.

6. Вибрати меню Записи ®Применить фильтр або натиснути на відповідну кнопку.

Лабораторна робота №3. Пошук даних у БД. Створення запитів

Хід роботи

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

Запит на вибірку (Рис. 8)

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

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

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

Обрати необхідні таблиці (Товар).

1. Методом перетягування вибрати поля (Назва товару, Ціна, Виробник).

2. Встановити у рядку Условие отбора

3. Вибрати Запрос ®Запуск або кнопка .

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

Підсумковий запит

Іноді цікаві не окремі записи таблиці, а підсумкові значіння по групам даних.

Для обчислення підсумкових значінь потрібно натиснути кнопку Групповые операциина панелі інструментів конструктора запитів, щоб у бланку QBE з’явився рядок Групповая операция.

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

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

• Sum- Вираховує суму всіх значень заданого поля в кожній групі. Використовується тільки для числових та грошових полів.

• Avg- Вираховує середнє арифметичне всіх значень даного поля в кожній групі. Використовується тільки для числових та грошових полів.

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

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

• Count- Повертає число записів, в яких значення даного поля відмінні від Null (пусто).

• StDev- Вираховує стандартне відхилення всіх значінь даного поля в кожній групі. Ця функція використовується тільки для числових або грошових полів.

• Var- Вираховує дисперсію значінь даного поля в кожній групі. Ця функція використовується тільки для числових або грошових полів.

• First- Повертає перше значення цього поля в групі.

• Last- Повертає останнє значення цього поля в групі.

Запит з параметром

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

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

1. Режим Конструктор, відкрити таблицю Товар.

2. Обрати необхідні поля: Назва товару, Ціна і Виробник.

3. У рядку Условие отбора під полем Виробник, у квадратних дужках написати необхідний вираз, у нашому випадку – [Введіть виробника].

4. Можна також задати сортуванн.

5. Вибрати Запрос ®Запуск або кнопка .

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

Створення перехресного запиту за допомогою майстра

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

Створення запиту за допомогою майстра:

1. Натискаємо Создать та із списку вибираємо перехресний запит
   ( Рис.13).

2. Обираємо таблицю Товар.

3. Обираємо поля, значення яких будуть використовуватися як заголовки записів (Назва товару, Виробник).

4. Обираємо поля для використання їх значень як заголовків стовпців (Постачальник).

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

6. Обрати пункт Вычислять итоговое значение.

7. Даємо ім´я запиту, Готово.

Створення перехресного запиту за допомогою конструктора
Натискаємо Создать®Конструктор. Відкривається стандартне вікно конструктора, обираємо таблицю, на основі якої буде створюватись запит.

1. Обираємо пункт меню Запрос®Перекрестный, при цьому додається додатковий рядочок Перекрестная таблица і Групповая операция.

2. Методом перетягування вставляємо у рядок Поле потрібні поля (Назва товару, Виробник, Постачальник, Ціна), у рядку Перекрестная таблица обираємо із списку для поля Назва товару і Виробник заголовки строк, для поля Постачальник- заголовок столбцов, для поля Кількість у рядку Групповая операция обираємо суму, додаєм поле Итоговое значение (сума).

3. Вибрати Запрос ®Запуск або кнопка .

Обчислення в запитах

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

Якщо при побудові виразу потрібно додати стандартну вбудовану функцію, потрібно в лівому нижньому списку вибрати папку Функции ®Встроенные функции. В результаті в правому списку з’являться назви функцій.

Створимо запит, в якому буде додаткове поле, в якому буде підраховуватись загальна ціна кожного товару в залежності від його кількості:

1. Создать®Конструктор, обираємо потрібну таблицю (Продукція).

2. У рядок Поле вставляємо необхідні поля (Назва товару, Ціна, Кількість та ін).

Для створення розрахункового поля потрібно встановити курсор у рядок Поле порожнього стовпчика та увести назву поля, символ «:» і вираз (Выражение1:Товар!Ціна*Товар!Кількість). Для уведення виразу можна використовувати будівник виразів. Для цього треба натиснути кнопку на панелі інструментів або натиснути на праву кнопку миші та вибрати Построить.

У вікні Построитель выражений (Рис.14) обираємо вкладку Таблицы, знаходимо свою таблицю Товар. Із списку полів обираємо ті поля, які беруть участь у формулі: Ціна, натискаємо Вставить, натискаємо на кнопку операції множення (*); Кількість- Вставить. Натискаємо Ok.

Вибрати Запрос ®Запуск або кнопка .

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

Лабораторна робота №4. Оформлення звітів

Хід роботи

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

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

Створення звіту за допомогою “Мастера отчетов”

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

Для створення звіту за допомогою Мастера отчетов потрібно

(Додаток Н)

1. Відкрити вкладку Отчеты у вікні бази даних, а потім натиснути кнопку Создать. З’явиться діалогове вікно Новый отчет.

2. Як для форми, так і для звіту потрібне джерело даних у вигляді таблиці або запиту. Виділіть потрібну таблицю Товар у випадаючому списку діалогового вікна Новый отчет. Із списку у верхньому правому кутку діалогового вікна виділіть Мастер отчетов і натиснть кнопку ОК. На екрані з’явиться початкове вікно Мастера отчетов.

3. Тепер необхідно визначити, які поля будуть утворювати рядки звіту. Виберіть за допомогою кнопки “>” потрібні поля зі списку Доступные поля. Коли поле добавляється у список Выбранные поля, Access автоматично забирає його ім’я зі списку Доступные поля. Можна також подвійно натиснути на ліву клавішу миші на імені поля у списку Доступные поля і воно переміститься у поле Выбранные поля. У звіті поля з’являються зліва направо, у відповідності з послідовністю їх появи зверху вниз у списку Выбранные поля.

4. На наступному кроці Мастер отчетов спитає, чи хочете ви добавити рівні групування до звіту. За допомогою кнопки “>” встановіть бажаний рівень групування. За допомогою кнопки Группировка встановіть бажаний інтервал групування. Змінюючи інтервал групування, можна впливати на те, як Access групує дані у звіті. Для числових полів можна задати групування по десятках, півсотнях, сотнях тощо. Для текстових полів можливе групування по першій букві, першим трьом буквам тощо.

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

6. В наступному вікні Мастера отчетов необхідно вибрати для звіту один з шести стилів. У вікні у лівій частині діалогового вікна майстра показаний вигляд вибраного стилю макета.

7. Наступне вікно Мастера отчетов призначене для вибору одного з визначеного стилів. У лівій частині діалогового вікна показаний вигляд звіту вибраного стилю. Можна настроїти стандартний стиль або створити власний.

8. Після переходу в наступне вікно Мастера отчетов натисніть кнопку Готово. Access згенерує звіт.

В одному з кроків майстра можна натиснути кнопку Итоги. У відчиненому діалоговому вікні потрібно визначити, які підсумкові дані потрібно обчислити. Потрібно обрати поле, в якому будуть обчислення і функцію: Sum, Avg, Min, Max.

Захист бази даних

База даних містить цінну інформацію, тому іноді її необхідно захищати, встановлбвати паролі. Захист можна здійснити викликавши меню Сервис ®Защита, из списку можна обрати такі пункти: Задать пароль базы даних, Администратор рабочих групп, Разрешение, Мастер, Шифровать/Разшифровать та ін.