Учбова БД

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Таблиця 1

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

Таблиця 2

	ГРУПИ
шифр групи	назва факультету	курс
БІ-501	ФЕТ
БО-73	ФЕФ
БОК-78	ФЕФ
ЕК-35	БФ
М-42	ФЕУ
ПЛ-44	ЛФ
У-22	ФЕУ
У-62	ФЕУ
Ф-91	ФЕФ
ФК-85	ФЕФ

Таблиця 3

	ОЦІНКИ
№ залікової книжки	дата	№ дисципліни	отримана оцінка
БІ-050114	12.12.2007
БІ-050114	18.10.2007
БО-007305	30.11.2007
БО-007305	14.12.2007
БОК-007804	20.11.2007
БОК-007825	25.10.2007
БОК-007825	16.11.2007
ЕК-003510	28.11.2007
ЕК-003510	28.11.2007
ЕК-003516	26.12.2007
ЕК-003516	12.10.2007
М-004213	10.11.2007
М-004213	15.12.2007
М-004220	31.12.2007
ПЛ-004401	24.12.2007
ПЛ-004401	04.09.2007
ПЛ-004401	13.10.2007
У-002214	30.10.2007
У-002214	25.12.2007
У-002214	26.12.2007
У-002215	14.10.2007
У-002215	19.10.2007
У-002215	20.10.2007
У-002215	21.10.2007
У-002228	12.11.2007
У-006202	25.12.2007
У-006202	20.10.2007
У-006216	13.11.2007
У-006216	16.12.2007
Ф-009119	20.12.2007
Ф-009119	03.11.2007
ФК-008520	30.11.2007
ФК-008531	29.12.2007
ФК-008531	24.11.2007
ФК-008531	12.12.2007

Таблиця 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а – Діалогове вікно „Промежуточные итоги”

	ОЦІНКИ
№ залікової книжки	дата	№ дисципліни	отримана оцінка
БІ-050114	12.12.2007
БІ-050114	18.10.2007
БІ-050114 Среднее
БО-007305	30.11.2007
БО-007305	14.12.2007
БО-007305 Среднее			4,5
БОК-007804	20.11.2007
БОК-007804 Среднее
БОК-007825	25.10.2007
БОК-007825	16.11.2007
БОК-007825 Среднее			3,5
ЕК-003510	28.11.2007
ЕК-003510	28.11.2007
ЕК-003510 Среднее
ЕК-003516	26.12.2007
ЕК-003516	12.10.2007
ЕК-003516 Среднее			3,5
М-004213	10.11.2007
М-004213	15.12.2007
М-004213 Среднее
М-004220	31.12.2007
М-004220 Среднее
ПЛ-004401	24.12.2007
ПЛ-004401	04.09.2007
ПЛ-004401	13.10.2007
ПЛ-004401 Среднее			3,333333333
У-002214	30.10.2007
У-002214	25.12.2007
У-002214	26.12.2007
У-0022014 Среднее
У-002215	14.10.2007
У-002215	19.10.2007
У-002215	20.10.2007
У-002215	21.10.2007
У-0022015 Среднее			4,75
У-002228	12.11.2007
У-0022028 Среднее
У-006202	25.12.2007
У-006202	20.10.2007
У-006202 Среднее
У-006216	13.11.2007
У-006216	16.12.2007
У-006216 Среднее			4,5
Ф-009119	20.12.2007
Ф-009119	03.11.2007
Ф-009119 Среднее			4,5
ФК-008520	30.11.2007
ФК-008520 Среднее
ФК-008531	29.12.2007
ФК-008531	24.11.2007
ФК-008531	12.12.2007
ФК-008531 Среднее			2,666666667
Общее среднее			3,914285714

Рисунок 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 – Результат роботи програми “Університет”

Дата добавления: 2014-10-15