Microsoft Excel

трюкиприёмырешения

Как организовать управление организацией учебного процеса при помощи Excel

В настоящее время количество учебных заведений значительно возросло. Рынок коммерческого образования охватил практически все крупные и средние города. Организация учебного процесса требует автоматизации различных участков работы. Учет успеваемости, оплаты и распределения аудиторного фонда организуется с помощью разнообразных средств автоматизации. В этой статье мы рассмотрим несколько задач, связанных с разработкой автоматизированных рабочих книг, направленных на улучшение организации учебного процесса.

Учет успеваемости

Первая задача, которую мы рассмотрим, относится к учету успеваемости учащихся. Наша цель заключается в разработке удобного механизма выставления оценок и получения сводных отчетов по успеваемости. Книга будет представлять собой несколько листов, которые по функциональному назначению можно разделить на справочные и листы управления.

На рис. 5.1 показан «основной» справочный лист, содержащий перечень дисциплин, входящих в различные учебные планы. В столбце G указывается название плана, а в столбце В — название дисциплины. Каждая дисциплина в определенном учебном плане имеет свой уникальный номер, для которого отведен столбец А.

Рис. 5.1. Лист с информацией об учебных планах

Рис. 5.1. Лист с информацией об учебных планах

Также на листе указывается семестр, когда дисциплина проводится, форма отчетности (экзамен, зачет или курсовая работа), общее и аудиторное количество учебных часов.

Рис. 5.2. Лист со справочной информацией

Рис. 5.2. Лист со справочной информацией

В процессе работы список названий учебных планов будет требоваться достаточно часто, поэтому оформим его на отдельном листе, предназначенном для справочных данных (рис. 5.2).

Информация об успеваемости будет храниться на листе Учащиеся (рис. 5.3). Вообще, можно для каждого учащегося ввести уникальный код, который позволит идентифицировать конкретного учащегося, даже если у двух и более человек фамилия и инициалы совпадают. Мы этого не сделали, так как у каждого учащегося имеется реквизит План, значение которого позволит идентифицировать учащихся при совпадении фамилии и инициалов, если они обучаются на разных специальностях. В случае же, если фамилия и инициалы совпадают у студентов одной специальности, то в столбец Учащийся на рис. 5.3 можно добавить уточняющую информацию. Начиная с третьего столбца располагаются столбцы для оценок (Оценка 1 для первой дисциплины учебного плана, Оценка 2 — для второй, и т. д.). Таким образом, комбинация данных с листов Планы и Учащиеся позволяет получить оценки конкретного студента или слушателя.

Рис. 5.3. Организация листа Учащиеся

Рис. 5.3. Организация листа Учащиеся

Механизм ввода оценок

Таким образом, мы рассмотрели основную справочную информацию в книге. Теперь перейдем к разработке первого из листов управления, который назовем Оценки учащегося (рис. 5.4). Его назначение заключается в предоставлении сотруднику возможности просмотра и ввода оценок конкретного учащегося.

На рассматриваемом листе два поля со списком — для отражения перечня учебных планов и наших учащихся (располагаются ниже соответствующих подписей). Для первого поля со списком установим значение свойства Name — Plan, а для второго — Student. Выделенная оттенком ячейка А8 будет заполняться программно номером строки, где на листе Учащиеся располагается информация по указанному студенту.

Этот номер строки будет использоваться далее в программных процедурах.

Справа от полей со списками разместим две кнопки — Заполнить список учащихся (Name — SpStud) и Вывести информацию об оценках (Name — Osenki). В правой части листа разместим текстовое окно для ввода кодового слова (Name — Pass) и кнопку (Name — OK) для записи информации на лист Учащиеся.

Для повышения защищенности запись информации на лист Учащиеся будет производиться только в случае ввода определенного кодового слова в текстовом окне Pass.

Таким образом, функциональность листа Оценки учащегося определена, и теперь требуется ее реализовать. Для работы с листом поле со списком Plan должно быть предварительно заполнено. Это лучше всего выполнить при открытии книги. В листинге 5.1 приведена необходимая процедура Workbook_Open.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
' Листинг 5.1. Процедура, выполняемая при открытии книги
Private Sub Workbook_Open()
N = 0
While Worksheets("Справочник").Cells(N + 1, 1).Value <> ""
	N = N + 1
Wend
Worksheets("Оценки учащегося").Plan.Clear
Worksheets("Оценки учащегося").Student.Clear
For i = 1 To N
	a = Worksheets("Справочник").Cells(i, 1).Value
    Worksheets("Оценки учащегося").Plan.AddItem a
Next
Worksheets("Оценки учащегося").Range("A8").Value = ""
End Sub
Рис. 5.4. Организация просмотра и ввода оценок учащихся

Рис. 5.4. Организация просмотра и ввода оценок учащихся

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

Первое действие пользователя заключается в том, что он должен выбрать интересующий его учебный план и далее щелчком на кнопке Заполнить список учащихся внести в нижнее поле со списком тех учащихся, которые обучаются по данному плану. Необходимая для этого процедура приведена в листинге 5.2.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
' Листинг 5.2. Процедура обработки щелчка на кнопке Заполнить список учащихся
Private Sub SpStud_Click()
N = 0
While Worksheets("Учащиеся").Cells(N + 1, 1).Value <> ""
	N = N + 1
Wend
Student.Clear
For i = 1 To N
	If CStr(Worksheets("Учащиеся").Cells(i + 1, 2).Value) = _
    CStr(Plan.Text) Then
    a = Worksheets("Учащиеся").Cells(i + 1, 1).Value
    Student.AddItem a
    End If
Next
End Sub

Следующее действие пользователя заключается в том, что он должен выбрать учащегося в поле со списком Student. В этом случае процедура обработки щелчка на списке учащихся (листинг 5.3) вносит в ячейку А8 рассматриваемого листа номер строки на листе Учащиеся, где расположена информация по интересующему нас человеку.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
' Листинг 5.3. Процедура обработки щелчка на списке учащихся
Private Sub Student_Click()
N = 0
While Worksheets("Учащиеся").Cells(N + 2, 1).Value <> ""
	N = N + 1
Wend
For i = 1 To N
	If CStr(Worksheets("Учащиеся").Cells(i + 1, 2).Value) = _
    CStr(Plan.Text) And
    CStr(Worksheets("Учащиеся").Cells(i + 1, _
    1).Value) = CStr(Student.Text) Then
    Cells(8, 1).Value = i + 1
    Exit For
    End If
Next
End Sub

Теперь от пользователя требуется щелчком на кнопке Вывести информацию об оценках отобразить дисциплины, которые имеются в учебном плане, по которому учащийся обучается, а также вывести оценки (разумеется, если они имеются). В листинге 5.4 показана процедура, которая выполняет данное действие, а на рис. 5.5 продемонстрирован результат вывода имеющихся оценок в базе данных по одному из учащихся.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
' Листинг 5.4. Процедура обработки щелчка на кнопке Osenki
Private Sub Osenki_Click()
' Предварительная очистка области на экране
Range("A11:E200").Value = ""
If Student.Text = "" Then
	MsgBox ("не выбран учащийся")
    Exit Sub
End If
' Подсчет дисциплин на листе Планы
N = 0
While Worksheets("Планы").Cells(N + 2, 1).Value <> ""
	N = N + 1
Wend
Num = 11
' Номер строки на листе, с которой начинается вывод информации
For i = 1 To N 
' Цикл по числу дисциплин на листе Планы
	a = Worksheets("Планы").Cells(i + 1, 7).Value
    If CStr(a) = CStrPlan.Text) Then
' Если дисциплина из выбранного учебного плана
	b = CInt(Worksheets("Планы").Cells(i + 1, 1).Value)
' Запись номера дисциплины
	Cells(Num, 1).Value = b
' Запись названия дисциплины и ее параметров
	Cells(Num, 2).Value = Worksheets("Планы").Cells(i + 1, 2).Value
	Cells(Num, 3).Value = Worksheets("Планы").Cells(i + 1, 3).Value
	Cells(Num, 4).Value = Worksheets("Планы").Cells(i + 1, 4).Value
' Извлечение оценки по дисциплине с листа Учащиеся
	osenka = Worksheets("Учащиеся").Cells(Cells(8, 1).Value, b + 2).Value
' Запись оценки
	Cells(Num, 5).Value = osenka
' Увеличение счетчика выводимых строк на листе
	Num = Num + 1
    End If
Next
End Sub

Таким образом, мы обеспечили вывод информации на лист Оценки учащегося с листа Учащиеся. Следующий шаг — обеспечить возможность обратного действия (с листа Оценки учащегося информация должна записываться на лист Учащиеся). Для этого мы уже разместили на листе кнопку Запись и текстовое окно для указания кодового слова при записи. В этом случае информация будет зафиксирована на листе Учащиеся только при определенном пароле. Процедура, которая обеспечивает данное действие, представлена в листинге 5.5.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
' Листинг 5.5. Процедура для записи информации в базу данных
Private Sub OK_Click()
' Проверка пароля и выбора учащегося
If Pass.Text = "csm" And Cells(8, 1) <> "" Then
' Извлекаем номер строки, в которую будем производить запись информации
	NumStud = Cells(8, 1).Value
' Подсчет числа дисциплин выбранного учебного плана
N = 0
While Cells(N + 11, 1).Value <> ""
	N = N + 1
Wend
For i = 1 To N
' Вычисляем номер столбца на листе Учащиеся, где находится оценка
	a = 2 + Cells(i + 10, 1).Value
' Запись оценки
	Worksheets("Учащиеся").Cells(NumStud, a).Value = Cells(i + 10, 5).Value
    Next
    MsgBox ("Информация записана")
Else
    MsgBox ("Информация не записана")
End If
End Sub
Рис. 5.5. Отображение успеваемости на листе Оценки учащегося

Рис. 5.5. Отображение успеваемости на листе Оценки учащегося

Ввод новых учащихся

Понятно, что в рассматриваемой автоматизированной книге требуется постоянное пополнение списка учащихся. В принципе, добавление можно выполнить прямо на листе Учащиеся, однако для удобства мы создадим еще один лист, Ввод учащихся, который показан на рис. 5.6. Здесь два элемента управления: поле со списком Plan и кнопка ОК. Пользователь (сотрудник учебного заведения) вводит в ячейку C3 фамилию учащегося, выбирает из списка учебных планов учебный план и затем щелчком на кнопке фиксирует информацию на листе Учащиеся.

Рис. 5.6. Лист для ввода новых студентов

Рис. 5.6. Лист для ввода новых студентов

Рассмотрим технические шаги для реализации описанного «функционала». Первое, что нам потребуется сделать, — обеспечить заполнение поля со списком Plan. Это мы выполним при открытии книги. Поэтому сделаем добавление (листинг 5.6) в уже встречавшейся в этой статье процедуре Workbook_Open.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
' Листинг 5.6. Измененная процедура Workbook_Open (вариант 2)
Private Sub Workbook_Open()
N = 0
While Worksheets("Справочник").Cells(N + 1, 1).Value <> ""
	N = N + 1
Wend
Worksheets("Оценки учащегося").Plan.Clear
Worksheets("Оценки учащегося").Student.Clear
Worksheets("Ввод учащихся").Plan.Clear
For i = 1 To N
	a = Worksheets("Справочник").Cells(i, 1).Value
    Worksheets("Оценки учащегося").Plan.AddItem a
    Worksheets("Ввод учащихся").Plan.AddItem a
Next
Worksheets("Оценки учащегося").Range("A8").Value = ""
End Sub
Рис. 5.7. Результат ввода нового учащегося

Рис. 5.7. Результат ввода нового учащегося

Вторая процедура, которая нам понадобится, связана с записью введенной информации на лист Учащиеся. Это действие (формирование новой строки с данными) выполняется с помощью процедуры, связанной со щелчком на кнопке Запись (она приведена в листинге 5.7). Для имеющихся данных результат ввода продемонстрирован на рис. 5.7.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
' Листинг 5.7. Процедура обработки щелчка на кнопке Запись
Private Sub OK_Click()
' Проверка указания учебного плана
If Plan.Text = "" Then
	MsgBox ("Не указан поток")
    Exit Sub
End If
' Подсчет уже имеющихся записей на листе Учащиеся
N = 0
While Worksheets("Учащиеся").Cells(N + 2, 1).Value <> ""
	N = N + 1
Wend
' Внесение фамилии
Worksheets("Учащиеся").Cells(N + 2, 1).Value = Cells(3, 3).Value
' Внесение названия учебного плана
Worksheets("Учащиеся").Cells(N + 2, 2).Value = Plan.Text
MsgBox ("Данные введены")
' Очистка ячейки с фамилией
Range("C3").Value = ""
End Sub

Лист Ведомость

Понятно, что наиболее важный функциональный компонент связан с организацией электронной ведомости — сотруднику учебного заведения должна быть предоставлена удобная возможность ввода оценок в базу данных. На рис. 5.8 такой лист показан. Здесь размещено два поля со списком. Одно поле со списком (Name — Plan) располагается в левой верхней части листа, а другое (Name — Disp) находится в правой части листа и предназначено для дисциплин. Также на листе присутствуют три кнопки. По нажатию кнопки Заполнить дисциплины (Name — ComD) после выбора плана (в поле со списком Plan) производится заполнение поля со списком Disp дисциплинами, относящимися к выбранному учебному плану. После указания необходимой дисциплины, сотрудник учебного заведения с помощью щелчка на кнопке Заполнить ведомость (Name — ComV) формирует ведомость с фамилиями учащихся и оценками (если они имеются).

Рис. 5.8. Организация листа Ведомость

Рис. 5.8. Организация листа Ведомость

В правой нижней части листа Ведомость расположена кнопка Запись (Name — OK) для внесения информации на лист Учащиеся. Как и ранее, для повышения защищенности на листе размещено текстовое окно (Name — Pass). Первое действие в плане программных конструкций связано с еще одним изменением процедуры Workbook_Open. Она теперь должна обеспечивать еще и заполнение поля со списком Plan на листе Ведомость. В листинге 5.8 представлен необходимый вариант рассматриваемой процедуры.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
' Листинг 5.8. Измененная процедура Workbook_Open (вариант 3)
Private Sub Workbook_Open()
N = 0
While Worksheets("Справочник").Cells(N + 1, 1).Value <> ""
	N = N + 1
Wend
Worksheets("Оценки учащегося").Plan.Clear
Worksheets("Оценки учащегося").Student.Clear
Worksheets("Ввод учащихся").Plan.Clear
Worksheets("Ведомость").Plan.Clear
For i = 1 To N
    a = Worksheets("Справочник").Cells(i, 1).Value
    Worksheets("Оценки учащегося").Plan.AddItem a
    Worksheets("Ввод учащихся").Plan.AddItem a
    Worksheets("Ведомость").Plan. AddItem a
Next
Worksheets("Оценки учащегося").Range("A8").Value = ""
End Sub

При переходе на лист Ведомость от пользователя после выбора плана требуется щелкнуть на кнопке Заполнить дисциплины. На листинге 5.9 приведена процедура, которую необходимо написать для обработки щелчка на данной кнопке.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
' Листинг 5.9. Процедура обработки щелчка на кнопке Заполнить дисциплины
Private Sub ComD_Click()
Disp.Clear
' Подсчет числа дисциплин на листе Планы
N = 0
While Worksheets("Планы").Cells(N + 2, 1).Value
    N = N + 1
Wend
For i = 1 To N
b = Worksheets("Планы").Cells(i + 1, 7).Value
    If CStr(b) = CStr(Plan.Text)
Then
' Если название учебного плана совпадает с указанным
    Disp.AddItem Worksheets("Планы").Cells(i + 1, 2).Value
    End If
Next
End Sub

Теперь щелчок на конкретном элементе в поле со списком Disp позволяет нам определиться, с какой дисциплиной мы собираемся работать. Процедура, представленная в листинге 5.10, позволяет заполнить данными шестую строку листа, которая информирует пользователя о реквизитах дисциплины, с которой мы собираемся работать.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
' Листинг 5.10. Процедура обработки щелчка на поле со списком Disp
Private Sub Disp_Click()
N = 0
While Worksheets("План").Cells(N + 2, 1).Value <> ""
N = N + 1
Wend
For i = 1 To N
If Plan.Text = Worksheets("План").Cells(i + 1, 7).Value And _
    Disp.Text = Worksheets("План").Cells(i + 1, 2).Value Then
' Извлечение номера дисциплины
    kod = Worksheets("План").Cells(i + 1, 1).Value
    Exit For
    End If
Next
' Отражение параметров дисциплины в шестой строке листа
Range("A6").Value = Worksheets("План").Cells(i + 1, 2).Value
Range("B6").Value = Worksheets("План").Cells(i + 1, 4).Value
Range("C6").Value = Worksheets("План").Cells(i + 1, 3).Value
Range("D6").Value = kod
End Sub
Рис. 5.9. Результат выбора плана и дисциплины

Рис. 5.9. Результат выбора плана и дисциплины

В качестве примера на рис. 5.9 показан результат выбора дисциплины Рисунок и живопись по плану 6ИВУ. Далее сотруднику учебного заведения необходимо щелчком на кнопке Заполнить ведомость внести информацию об учащихся и их оценках в определенную область листа. Для этого нам потребуется процедура, представленная в листинге 5.11.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
' Листинг 5.11. Процедура заполнения ведомости
Private Sub ComV_Click()
' В ячейке D6 предварительно мы записали код (номер) дисциплины
NomerDisp = Cells(6, 4).Value
' Выход, если не указана дисциплина
If NomerDisp = "" Then
    MsgBox ("Не указана дисциплина")
    Exit Sub
End If
' Очистка области вывода
Range("A10:B200") = ""
' Подсчет числа учащихся
N = 0
While Worksheets("Учащиеся").Cells(N + 2, 1).Value <> ""
    N = N + 1
Wend
' В следующей переменной мы организуем счетчик фамилий на дан-
ном листе
NumStud = 0
For i = 1 To N
If СStr(Worksheets("Учащиеся").Cells(i + 1, 2).Value) = _
    CStr(Plan.Text) Then
' Внесение фамилии
    Cells(NumStud + 10, 1).Value = _
    Worksheets("Учащиеся").Cells(i + 1, 1).Value
' В 100-й столбец записываем номер строки, которая отводится для учащегося
Cells(NumStud + 10, 100).Value = i + 1
NumStud = NumStud + 1
End If
Next
' Цикл по числу отобранных фамилий
For j = 1 To NumStud
' Извлечение номера строки очередного учащегося
IndStud = Cells(j + 9, 100).Value
' Извлечение оценки учащегося
Osenka = Worksheets("Учащиеся").Cells(IndStud,2 + CInt (NomerDisp)).Value
' Отражение оценки
Cells(j + 9, 2).Value = Osenka
Next
End Sub

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

На рис. 5.10 показан результат щелчка на кнопке Заполнить ведомость. Сотрудник теперь может внести необходимые оценки с печатного документа (реальной экзаменационной ведомости). И теперь эти данные необходимо записать на лист Учащиеся. Поэтому перейдем к следующему программному фрагменту, относящемуся к данному листу, — записи информации в базу оценок. Для этого предназначена кнопка Запись, а также соседнее текстовое окно. Процедура, представленная в листинге 5.12, обеспечивает внесение введенных оценок при правильном пароле в текстовом окне.

Рис. 5.10. Извлеченная информация по группе учащихся

Рис. 5.10. Извлеченная информация по группе учащихся

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
' Листинг 5.12. Процедура записи информации на лист Учащиеся
Private Sub OK_Click()
If Pass.Text = "csm" Then
' Подсчет числа фамилий, внесенных на данный лист
N = 0
While Cells(N + 10, 1) <> ""
    N = N + 1
Wend
' В данную ячейку мы предварительно записали номер дисциплины
NomerDisp = Cells(6, 4).Value
For j = 1 To N
' Получение номера строки с информацией по студенту
    IndStud = Cells(j + 9, 100).Value
' Получение информацию об оценке с текущего листа
    Osenka = Cells(j + 9, 2).Value
' Внесение оценки на лист Учащиеся
    Worksheets("Учащиеся").Cells(IndStud, 2 + NomerDisp).Value = Osenka
Next
MsgBox ("Информация внесена")
Else
MsgBox ("Информация не внесена")
End If
End Sub

Если теперь после записи оценок повторно извлечь данные, то мы должны получить ту же информацию.

Лист Печатная форма

Кроме электронной ведомости, в реальной работе требуется и печатный вариант для преподавателей во время проведения экзамена или зачета.

На рис. 5.11 представлена эта печатная форма. В ней присутствует традиционная заголовочная часть, а также таблица для внесения оценок. Для автоматического заполнения печатной формы на листе Ведомость следует расположить кнопку Печать (рис. 5.12), и само заполнение производится по щелчку на кнопке.

Более конкретно можно пояснить, что на листе Печатная форма информация с первой по одиннадцатую строки фиксирована (за исключением ячеек, выделенных оттенком). Остальной фрагмент листа формируется программно, исходя из данных, присутствующих на листе Ведомость.

Фактически печатный вариант ведомости является основным и в ряде ситуаций единственным.

Рис. 5.11. Печатная форма экзаменационной ведомости

Рис. 5.11. Печатная форма экзаменационной ведомости

В листинге 5.13 приведена процедура заполнения печатной формы ведомости.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
' Листинг 5.13. Процедура, выполняемая по щелчку на кнопке Печать
Private Sub Prn_Click()
' Заполнение шапки документа
Worksheets("Печатная форма").Cells(3, 6) = Plan.Text
Worksheets("Печатная форма").Cells(7, 8) = Cells(4, 6)
Worksheets("Печатная форма").Cells(3, 5) = Cells(6, 2)
Worksheets("Печатная форма").Cells(7, 8) = Cells(6, 3)
Worksheets("Печатная форма").Cells(4, 3) = Disp.Text
' Очистка табличной области
Worksheets("Печатная форма").Range("A12:M200") = ""
' Удаление границ
With Worksheets("Печатная форма").Range("A12:M200")
.Borders.LineStyle = xlNone
End With
' Подсчет числа студентов в группе
N = 0
While Cells(10 + N, 1).Cells <> ""
N = N + 1
Wend
' Заполнение печатной формы
For i = 1 To N
' Внесение порядкового номера
Worksheets("Печатная форма").Cells(i + 11, 1) = i
' Внесение фамилии
Worksheets("Печатная форма").Cells(i + 11, 2) = Cells(i + 9, 1)
' Оформление границ ячеек
    For j = 1 To 8
    With Worksheets("Печатная форма").Cells(i + 11, j)
    .Borders.LineStyle = xlContinuous
    End With
    Next
Next
Worksheets("Печатная форма").Cells(i + 11, 1) = _
" Число студентов на экзамене (зачете) _______________"
Worksheets("Печатная форма").Cells(i + 12, 1) = _
" Из них получивших отлично _______________"
Worksheets("Печатная форма").Cells(i + 13, 2) = _
" получивших хорошо _______________"
Worksheets("Печатная форма").Cells(i + 14, 2) = _
"получивших удовлетворительно _______________"
Worksheets("Печатная форма").Cells(i + 15, 2) = _
"получивших неудовлетворительно _______________"
Worksheets("Печатная форма").Cells(i + 16, 2) = _
" Число студентов, не явившихся на экзамен (зачет) _________"
Worksheets("Печатная форма").Cells(i + 17, 1) = _
"Число студентов, не допущенных к экзамену (зачету) _________"
Worksheets("Печатная форма").Cells(i + 19, 4) = "Декан факультета _______________ Ю.В.Воронков "
Worksheets("Печатная форма").Activate
End Sub
Рис. 5.12. Добавление кнопки Печать на лист Ведомость

Рис. 5.12. Добавление кнопки Печать на лист Ведомость

Сводная ведомость

Рассмотрим разработку важного сводного отчета, который называется сводная ведомость. Заполненный вариант этого отчета (для варианта имеющихся в базе учащихся и их оценок) представлен на рис. 5.13. На листе Сводная ведомость три элемента управления: два поля со списком и одна кнопка. Поле со списком (Name — Sem), расположенное рядом с подписью Семестр, предназначено для указания семестра, за который мы собираемся получить сводную ведомость. Другое поле со списком (Name — Plan), расположенное рядом с подписью План, предназначено для указания учебного плана. Кнопка на листе (Name — OK) позволяет заполнить сводную таблицу фамилиями учащихся и их оценками. В листинге 5.14 приведена необходимая техническая процедура, выполняемая при активизации листа и заполняющая поля со списками (семестрыи учебные планы).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
' Листинг 5.14. Процедура, выполняемая при активизации листа
Private Sub Worksheet_Activate()
Sem.Clear
' При указании числа семестров мы ограничились пятью
For i = 1 To 5
    Sem.AddItem i
Next
Plan.Clear
N = 0
While Worksheets("Справочник").Cells(N + 1, 1).Value <> ""
    N = N + 1
Wend
For i = 1 To N
    a = Worksheets("Справочник").Cells(i, 1).Value
    Plan.AddItem a
Next
End Sub
Рис. 5.13. Сводная ведомость

Рис. 5.13. Сводная ведомость

Основное функциональное действие связано со щелчком на кнопке Заполнить таблицу. Для заполнения сводной таблицы просматривается содержание учебных планов, перечень учащихся и их оценки. В листинге 5.15 приведена процедура заполнения сводной таблицы.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
' Листинг 5.15. Процедура обработки щелчка на кнопке Заполнить таблицу
Private Sub OK_Click()
If IsNumeric(Sem.Text) = False Then
    MsgBox ("Не выбран семестр")
    Exit Sub
End If
    Semestr = CInt(Sem.Text)
' Очистка области заголовков столбцов
Range("B7:Z9") = ""
' Очистка основной области
Range("A10:Z200") = ""
' Подсчет общего числа дисциплин
N = 0
While Worksheets("Планы").Cells(N + 2, 1).Value <> ""
    N = N + 1
Wend
'Заполнение дисциплин, попавших в указанный семестр
Nom = 2
' Номер столбца, с которого начинается заполнение
ColsDisp = 0
' Счетчик количества дисциплин
For i = 1 To N
SemNum = CInt(Worksheets("Планы").Cells(i + 1, 3).Value)
If CStr(Worksheets("Планы").Cells(i + 1, 7).Value) = CStr (Plan.Text) And SemNum = Semestr Then
' Внесение названия очередной дисциплины в сводную таблицу
Cells(7, Nom).Value = Worksheets("Планы").Cells(i + 1, 2).Value
' Внесение номера семестра
Cells(8, Nom).Value = Sem
' Внесение формы отчетности
Cells(9, Nom).Value = Worksheets("Планы").Cells(i + 1, 4).Value
' Фиксирование в 200-й строке номера дисциплины
Cells(200, Nom).Value = Worksheets("Планы").Cells(i + 1, 1).Value
Nom = Nom + 1
ColsDisp = ColsDisp + 1
End If
Next
' Подсчет числа студентов
Nstud = 0
While Worksheets("Учащиеся").Cells(Nstud + 2, 1).Value <> ""
Nstud = Nstud + 1
Wend
Num = 0
For i = 1 To Nstud
    If Worksheets("Учащиеся").Cells(i + 1, 2).Value = Plan Then
' Извлечение очередной фамилии студента, обучающегося по указанному плану
    a = Worksheets("Учащиеся").Cells(i + 1, 1).Value
' Запись фамилии учащегося в первый столбец
    Cells(Num + 10, 1).Value = a
' Фиксирование в 100-м столбце номера строки учащегося
    Cells(Num + 10, 100).Value = i + 1
    Num = Num + 1
    End If
Next
' Внесение оценок
For j = 1 To Nstud
If CStr(Worksheets("Учащиеся").Cells(j + 1, 2).Value) = CStr (Plan) Then
' Извлечение номера строки студента
    IndStud = Cells(j + 9, 100).Value
    For ld = 1 To ColsDisp
    NumDisp = Cells(200, ld + 1).Value
' Извлечение и фиксирование оценок
Osenka = Worksheets("Учащиеся").Cells(IndStud, 2 + NumDisp).Value
Cells(j + 9, ld + 1).Value = Osenka
Next
End If
Next
End Sub

Таким образом, мы получили удобный механизм просмотра оценок в определенном интервале семестров. Фактически основные функциональные действия мы рассмотрели. Однако для повышения сервиса работы сделаем еще несколько дополнений в рассматриваемой разработке.

Лист Коррекция

Рассмотренный ранее лист План в процессе работы будет требовать внесения изменений. Эти изменения будут касаться и названия дисциплин, и номера семестра,и числа часов. Однако при этом необходимо обеспечить защиту от изменений кода (порядковый номер) дисциплины.

Это связано с имеющимися оценками по данной дисциплине. И если мы изменим код дисциплины, оценки уже будут относиться к другой дисциплине.

Для удобства коррекции планов мы создадим дополнительный лист, который назовем Коррекция (рис. 5.14). Фактически он будет представлять некий «браузер» для учебных планов.

Рис. 5.14. Лист коррекции учебных планов

Рис. 5.14. Лист коррекции учебных планов

Сотрудник, работающий с программой, выбирает интересующий его план в поле со списком. После этого щелчком на кнопке Заполнить рассматриваемый лист заполняется информацией. При этом далее мы обеспечим автоматическую сортировку дисциплин по семестрам. Для заполнения |поля со списком мы воспользуемся процедурой, автоматически выполняемой при активизации листа (листинг 5.16).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
' Листинг 5.16. Процедура Worksheet_Activate
Private Sub Worksheet_Activate()
Plan.Clear
' Подсчет различных учебных планов
N = 0
While Worksheets("Справочник").Cells(N + 1, 1).Value <> ""
    N = N + 1
Wend
' Заполнение поля со списком
For i = 1 To N
    a = Worksheets("Справочник").Cells(i, 1).Value
    Plan.AddItem a
Next
End Sub

Если в процессе работы сотрудник меняет семестры дисциплин (наиболее частое изменение), то для сортировки можно воспользоваться кнопкой Сортировка по семестрам. В этом случае вы получите пересортированный учебный план прямо на этом листе. Кнопка Внести изменения в план позволяет перенести измененную информацию на лист Планы. Конечно, последовательное нажатие кнопки Внести изменения в план и затем Заполнить позволит получить на листе Коррекция аналогичный результат. Однако по времени этот вариант будет чуть дольше.

Теперь приведем «ключевую» в функциональном плане процедуру (листинг 5.17), которая позволяет заполнять лист информацией.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
' Листинг 5.17. Процедура обработки щелчка на кнопке Заполнить
Private Sub OK_Click()
If Plan.ListIndex = -1 Then
    MsgBox ("Не указан поток")
    Exit Sub
End If
Range("a11:f200").Value = "" ' Очистка области вывода
' Устранение заливки в области вывода
Range("a11:f200").Select
With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ColorIndex = 0
End With
N = 0
While Worksheets("Планы").Cells(N + 2, 1).Value <> ""
    N = N + 1
Wend
Num = 11 ' Номер строки, с которой начинается вывод информации
For i = 1 To N
    a = Worksheets("Планы").Cells(i + 1, 7).Value
' Если дисциплина соответствует выбранному учебному плану
    If CStr(a) = CStr(Plan.Text) Then
    b = Worksheets("Планы").Cells(i + 1, 1).Value ' Код дисциплины
' Перенос на лист параметров дисциплины
    Cells(num, 1).Value = b
    Cells(num, 2).Value = Worksheets("Планы").Cells(i + 1, 2).Value
    Cells(num, 3).Value = Worksheets("Планы").Cells(i + 1, 3).Value
    Cells(num, 4).Value = Worksheets("Планы").Cells(i + 1, 4).Value
    Cells(num, 5).Value = Worksheets("Планы").Cells(i + 1, 5).Value
    Cells(num, 6).Value = Worksheets("Планы").Cells(i + 1, 6).Value
    num = num + 1
    End If
Next
' Сортировка по семестрам
Range("A11:F110").Select
Selection.Sort Key1:=Range("C11"), Order1:=xlAscending, _
Key2:=Range("B11"), Order2:=xlAscending, Key3:=Range("D11"), _
Order3:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3: = xlSortNormal
N = 0
While Cells(N + 11, 1).Value <> ""
    N = N + 1
Wend
' Расцветка дисциплин различных семестров
For i = 1 To N
    semestr = Cells(i + 10, 3).Value
    If semestr Mod 2 = 0 Then
    Range(Cells(i + 10,1), Cells(i + 10,6)).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ColorIndex = 35
	End With
	End If
    If semestr Mod 2 = 1 Then
    Range(Cells(i + 10,1), Cells(i + 10,6)).Select
    With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ColorIndex = 40
    End With
    End If
Next
Range("A1").Select
End Sub

Как уже говорилось, в процессе работы с листом Коррекция может понадобиться процедура, осуществляющая сортировку дисциплин прямо на этом листе. Приведем процедуру (листинг 5.18), выполняющую обыкновенную сортировку но семестрам.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
' Листинг 5.18. Процедура обработки щелчка на кнопке Сортировка по семестрам
Private Sub SortSem_Click()
Range("A11:F110").Select
Selection.Sort Key1:=Range("C11"), Order1:=xlAscending, _
Key2:=Range("B11"), Order2:=xlAscending, Key3:=Range("D11"), _
Order3:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3: = xlSortNormal
N = 0
While Cells(N + 11, 1).Value <> ""
    N = N + 1
Wend
' Расцветка дисциплин различных семестров
For i = 1 To N
    semestr = Cells(i + 10, 3).Value
    If semestr Mod 2 = 0 Then
    Range(Cells(i + 10,1), Cells(i + 10,6)).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ColorIndex = 35
    End With
    End If
    If semestr Mod 2 = 1 Then
    Range(Cells(i + 10,1), Cells(i + 10,6)).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ColorIndex = 40
    End With
    End If
Next
Range("A1").Select
End Sub

Не рассмотренной осталась кнопка, обеспечивающая внесение изменений в учебный план. В листинге 5.19 приведена необходимая процедура. Из названия процедуры видно, что для значения свойства Name рассматриваемой кнопки выбрано IzmPlan.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
' Листинг 5.19. Процедура обработки щелчка на кнопке Внести изменения в план
Private Sub IzmPlan_Click()
' Подсчет дисциплин в листе План
N = 0
While Worksheets("Планы").Cells(N + 2, 1).Value <> ""
    N = N + 1
Wend
' Подсчет дисциплин на данном листе
L = 0
While Cells(L + 11, 1).Value <> ""
    L = L + 1
Wend
PlanCorr = Plan.Text
For i = 1 To L
    kod = CInt(Cells(10 + i, 1).Value)
' Код дисциплины на данном листе
    For j = 1 To N
' Код дисциплины учебного плана
    kodplan = CInt(Worksheets("Планы").Cells(j + 1, 1).Value)
    If kodplan = kod And PlanCorr = _
    CStr(Worksheets("Планы").Cells(j + 1, 7).Value) Then
    Worksheets("Планы").Cells(j + 1, 2).Value = Cells(10 + i, 2).Value
    Worksheets("Планы").Cells(j + 1, 3).Value = Cells(10 + i, 3).Value
    Worksheets("Планы").Cells(j + 1, 4).Value = Cells(10 + i, 4).Value
    Worksheets("Планы").Cells(j + 1, 5).Value = Cells(10 + i, 5).Value
    Worksheets("Планы").Cells(j + 1, 6).Value = Cells(10 + i, 6).Value
    End If
    Next
Next
End Sub
Top