Учбова БД

Будемо вважати, що перший етап створення БД “Університет” уже виконаний – дані про функціонування об’єкта уже зібрані і розсортовані по таблицям:

- STUDENTI (№ залікової книжки, ПІП, шифр групи, стипендія);

- GRUPI (шифр групи, назва факультету, курс);

- DISCIPLINI (№ дисципліни, назва дисципліни);

- OZINKI (№ залікової книжки, дата, № дисципліни, отримана оцінка).

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

На базі цих даних потрібно в окрему довідку вибрати оцінки для заданої групи (наприклад, У-22) по іспитам останньої сесії (наприклад, РПС, вищої математики та інформатики) та розрахувати середній бал за сесію кожного студента.

2 БД на основі ЕП Excel

2.1 Створення і використання даних БД

Як правило, з допомогою ЕП Excel створюються порівняно невеликі за обсягом бази даних, в яких не вимагається великої кількості довідкового матеріалу. При чому такі довідки відповідають сталим стандартним формам.

Продуктом ЕП Excel є книга, яка складається із будь-якої кількості сторінок (від 1 до 255). В первинній книзі їх 3, потім командою ВставкаÞДобавить лист можна їх додавати в потрібній кількості. Бажано кожну таблицю зберігати на окремому аркуші. Тому в нашому прикладі потрібно мати книгу, що складається більше ніж із 5 листів (4 листи для основних таблиць, п’ятий і подальші для довідкових).

Будемо притримуватися таких правил створення БД:

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

- структуру таблиці (шапку) розмістимо на п’ятому рядку Excel;

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

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

Для роботи з БД в ЕП Excel є набір команд розміщених в секції меню Данные.

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

Рисунок 3 – Вигляд форми в ЕП Excel

Занесена інформація створеної БД має вигляд (рисунок 4):

Таблиця 1

Таблиця 2

Таблиця 3

Таблиця 4

Рисунок 4 – БД після введення даних

2.2 Сортування та фільтрація даних

Для сортування інформації БД потрібно відмітити поля фільтрації, а потім звернутися до команди ДанныеÞСортировка.

У відкрите вікно “Сортировка диапазона” задати критерії сортування. Вигляд розсортованої таблиці “Студенти” по стипендії, яку отримують студенти має вигляд: (рисунок 5)

СТУДЕНТИ                   № залікової книжки ПІП шифр групи стипендія БО-007305 Голуб О.В. БО-73 БОК-007825 Шукало А.П. БОК-78 М-004230 Яковлева О.О. М-42 У-002214 Овечко О.О. У-22 У-002228 Філь Ю.О. У-22 У-006216 Стеблина А.В. У-62 ФК-008531 Шевченко О.Т. ФК-85 БОК-007804 Врана Т.В. БОК-78 ЕК-003516 Затяміна О.С. ЕК-35 ПЛ-004401 Бараннік С.В. ПЛ-44 У-002210 Козачинська М.І. У-22 Ф-009117 Лобанова А.О. Ф-91 ФК-008520 Третяк О.М. ФК-85 БІ-050114 Конограй А.І. БІ-501 М-004220 Моренко Ю.Г. М-42 БОК-007803 Вишемірський С.С. БОК-78 ЕК-003510 Демчук О.П. ЕК-35 Ф-009119 Милосердна Г.О. Ф-91 М-004213 Кованда Н.М. М-42 У-002215 Оніщенко Т.В. У-22 У-006202 Бабенко О.О. У-62

Рисунок 5- Таблиця “Студенти” після сортування

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

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

Якщо потрібно повернутися до початкової таблиці, потрібно вибрати опцію “Все”, або знову використати команду ДанныеÞФильтрÞ ÞАвтофильтр.

Приклад фільтрації студентів зі стипендією більшою за 300грн. маємо на рисунку 6 (а, б).

Рисунок 6а – Фільтрація даних таблиці „Студенти” по заданій умові

Рисунок 6б – Відфільтровані дані таблиці „Студенти”

2.3 Створення проміжних підсумків

Для створення простих проміжних підсумків у відсортованій таблиці потрібно виконати команду ДанныеÞИтоги. Відкривається вікно “Промежуточные итоги” (рисунок 7а), в якому необхідно установити прапорці “Заменить текущие итоги”, “Добавить итоги по”, “Итоги под данными” і натиснути кнопку ОК. Вигляд середнього балу кожного студента по таблиці “Оцінки” має вигляд, зображений на рисунку 7б.

Рисунок 7а – Діалогове вікно „Промежуточные итоги”

Рисунок 7б – Підсумки балів студентів

2.4 Аналіз даних БД з допомогою графіків і діаграм

Числові дані таблиць БД можна наглядно представити для порівняння та аналізу з допомогою майстра діаграм. Його виклик відбувається командою ВставкаÞДиаграмма або кнопкою на панелі інструментів. З’являється вікно майстра діаграм (рисунок 8).

Вікно містить дві вкладники “Стандартные” і “Нестандартные” діаграми. В лівому нижньому кутку міститься довідка, яка дасть відповіді на питання, що можуть виникнути у користувача під час побудови. “Просмотр результата” дозволить переглянути загальний вигляд діаграми до виведення на екран.

Рисунок 8 – Перше вікно „Мастера диаграмм”

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

Рисунок 9 – Діаграма аналізу

2.5 Створення довідок та звітів

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

Якщо ж в довідку чи звіт потрібно включити дані із різних таблиць, такі документи потрібно готувати з допомогою модулів написаних на мові Visual Basic for Excel. Для виклику вікна програм потрібно виконати команду СервисÞМакросÞМакросы.

Нагадаємо, що мова Visual Basic є об’єктно-орієнтованою мовою. Об’єктами в ЕП Excel вважаються:

· книга – Workbook;

· аркуш – Worksheets;

· комірка на аркуші – Cells(i, j), де i, j – номер рядка і стовпця, на перетині яких знаходиться об’єкт;

· діапазон комірок – Range(“D1:D2”), де D1, D2 – діагональні адреси діапазону комірок.

Схематично об’єкти ЕП Excel представлені на рисунку 10.

Рисунок 10 – Схема ієрархії об’єктів в ЕП Excel

Звертання ведеться по ієрархічному закону. Наприклад, Workbook(“A:\книга2”).Worksheets(3).Cells(i, j). Тут іде звертання до комірки, що знаходиться в рядку, номер якого зберігається в змінній і, в стовпці з номером j, третього аркуша, книги, що зберігається в файлі з іменем книга2.xls на кореневому директорії А:.

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

Кожний об’єкт має свої властивості та методи. Властивості – це установки, які можна перевірити та змінити (товщина контуру, тип шрифту, значення комірки і т.д.). Worksheets(4).Range(“B4”).Value = ”Інформатика”. В комірку В4 аркуша 4 буде записаний текст “Інформатика”. Методи – дії, які може виконати об’єкт, якщо програма йому це загадає. Worksheets(2).Name = ”Grupi”. Аркушу 2 активної книги присвоюється ім’я ”Grupi”.

Для того щоб визначити властивість об’єкта, його засилають в змінну пам’яті, а потім її переглядають:

x = Range(“B4”).Font.Size

MsgBox “Значення маштабу шрифту в комірці В4 = ”&str(x)

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

Тип лінії об’єкту задається властивістю Selection.Borders(<розміщення>).Linestyle=<тип лінії>, а її товщина: Selection.Borders(<розміщення>).Weight=<товщина>.

В цих записах <розміщення> може приймати такі значення:

xlEdgeRight – праворуч;

xlEdgeLeft – ліворуч;

xlEdgeTop – зверху;

xlEdgeBottom – знизу.

<Тип лінії> може приймати значення:

xlContinuous – сплошна;

xlDash – пунктирна;

xlDashDot – штрихпунктирна;

xlDot – крапками;

xlDouble – подвійна;

xlLineStyleNone – пусто.

<Товщина> може мати значення:

xlHairlinie – дуже тонка;

xlThin – тонка;

xiMedium – середньої товщини;

xlThick – товста.

Розглянемо приклад, створення запиту. Із створеної БД (рисунок 4) потрібно роздрукувати оцінки по дисциплінам сесії та отримати середній бал, для студентів заданої групи. Будемо вважати, що в задану сесію здавалися дисципліни: РПС, вища математика, інформатика. Для цього із таблиці “Студенти” (лист 1) з допомогою таблиці “Групи” (лист 2) вибираємо студентів лише заданої групи, а потім в таблиці “Оцінки” (лист 3) вибираємо їх оцінки. На листі 4 розмістимо таблицю “Дисципліни”.

В кінці циклу перегляду таблиці “Студенти” заповнюємо поле середнього балу. Алгоритм розв’язку такої задачі представлений на рисунку 11.

Рисунок 11 – Блок-схема запиту БД “Університет”

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

Sub Університет()

Dim i As Integer, j As Long, l As Byte

Dim x1 As Byte, x2 As Byte, x3 As Byte, k1 As Byte

Dim k As Byte, s As Single

Worksheets(5).Name = "REZ"

Worksheets("REZ").Range("A1:Z100").Delete

'

'

' РОЗРАХУНКОВО-ГРАФІЧНА РОБОТА

' студента ФЕФ 2-го курсу групи ФК-81

' Короля Є.Д.

' Довідка про успішність заданої групи

'

MsgBox " РОЗРАХУНКОВО-ГРАФІЧНА РОБОТА " & Chr(13) & _

" студента ФЕФ 2-го курсу групи ФК-81 " & Chr(13) & _

" Короля Є.Д. " & Chr(13) & _

" про успішність заданої користувачем групи " & Chr(13) & _

" за останню сесію."

' Введення початкових даних

N$ = InputBox("Введіть назву групи", "Група")

x1 = InputBox("Введіть код першої іспитової дисципліни", "X1", 0)

x2 = InputBox("Введіть код другої іспитової дисципліни", "X2", 0)

x3 = InputBox("Введіть код третьої іспитової дисципліни", "X3", 0)

n1 = InputBox("Введіть номер останнього рядка таблиці СТУДЕНТИ")

n2 = InputBox("Введіть номер останнього рядка таблиці ГРУПИ")

n3 = InputBox("Введіть номер останнього рядка таблиці ОЦІНКИ")

n4 = InputBox("Введіть номер останнього рядка таблиці ДИСЦИПЛІНИ")

' Заповнення шапки таблиці

Worksheets("REZ").Range("c2").Value = "Успішність"

Worksheets("REZ").Range("b3").Value = " групи " & N$ & " по заданним дисциплінам"

Worksheets("REZ").Range("a5").Value = "№ залікової книжки"

Worksheets("REZ").Range("b5").Value = "Прізвище І.П."

For i = 6 To n4

If Worksheets(4).Cells(i, 1).Value = x1 Then

Worksheets("REZ").Range("c5").Value = Worksheets(4).Cells(i, 2).Value

End If

If Worksheets(4).Cells(i, 1).Value = x2 Then

Worksheets("REZ").Range("d5").Value = Worksheets(4).Cells(i, 2).Value

End If

If Worksheets(4).Cells(i, 1).Value = x3 Then

Worksheets("REZ").Range("e5").Value = Worksheets(4).Cells(i, 2).Value

End If

Worksheets("REZ").Range("f5").Value = "Середній бал"

Next i

k = 6 ' Параметр цикла для Worksheets("REZ")

For i = 6 To n1 ' Параметр цикла для 1-го аркуша

M = 0 '

For j = 6 To n3 + 1 ' Параметр цикла для 3-го аркуша

If Worksheets(1).Cells(i, 1).Value = Worksheets(3).Cells(j, 1).Value Then

Select Case Worksheets(3).Cells(j, 3).Value

Case x1

k1 = 3

Case x2

k1 = 4

Case x3

k1 = 5

End Select

'MsgBox " K1=" & Str(k1) & Worksheets(1).Cells(i, 1).Value & "i=" & Str(i) & "j=" & Str(j)

End If

For l = 6 To n2 ' Параметр цикла для 2-го аркуша

'MsgBox Worksheets(2).Cells(l, 1).Value + Worksheets(1).Cells(i, 3).Value + N$

If Worksheets(2).Cells(l, 1).Value = Worksheets(1).Cells(i, 3).Value _

And Worksheets(1).Cells(i, 1).Value = Worksheets(3).Cells(j, 1).Value Then

If Trim(Worksheets(2).Cells(l, 1).Value) = Trim(N$) Then

Worksheets("REZ").Cells(k, 1).Value = Worksheets(1).Cells(i, 1).Value

Worksheets("REZ").Cells(k, 2).Value = Worksheets(1).Cells(i, 2).Value

Worksheets("REZ").Cells(k, k1).Value = Worksheets(3).Cells(j, 4).Value

M = M + 1

GoTo 50

End If

End If

Next l

If Worksheets("REZ").Cells(k, k1).Value = " " Then

MsgBox (" Неправильно задана назва групи " & N$)

GoTo 50

End If

50 Next j

If M <> 0 Then k = k + 1

Next i

With Worksheets("REZ")

For i = 6 To k - 1

s = 0

If .Cells(i, 3).Value <> 0 Then s = s + .Cells(i, 3).Value Else GoTo 100

If .Cells(i, 4).Value <> 0 Then s = s + .Cells(i, 4).Value Else GoTo 100

If .Cells(i, 5).Value <> 0 Then s = s + .Cells(i, 5).Value Else GoTo 100

.Cells(i, 6).Value = s / 3: GoTo 200

100 .Cells(i, 6).Value = "БОРЖНИК"

200 Next i

'

' Контури таблиці

'

For j = 1 To 6

For i = 5 To k - 1

Worksheets("REZ").Cells(i, j).Activate

Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous

Selection.Borders(xlEdgeRight).LineStyle = xlContinuous

Selection.Borders(xlEdgeTop).LineStyle = xlContinuous

Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous

Selection.Borders(xlEdgeLeft).Weight = xlThin

Selection.Borders(xlEdgeRight).Weight = xlThin

Selection.Borders(xlEdgeTop).Weight = xlThin

Selection.Borders(xlEdgeBottom).Weight = xlThin

Next i

Next j

End With

MsgBox " ПРОГРАМА РОБОТУ ЗАВЕРШИЛА"

End Sub

Рисунок 12 – Результат роботи програми “Університет”