Планирование учебного процесса при помощи Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

' Листинг 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. Организация просмотра и ввода оценок учащихся

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

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

' Листинг 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 рассматриваемого листа номер строки на листе Учащиеся, где расположена информация по интересующему нас человеку.

' Листинг 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 продемонстрирован результат вывода имеющихся оценок в базе данных по одному из учащихся.

' Листинг 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.

' Листинг 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.6. Здесь два элемента управления: поле со списком Plan и кнопка ОК. Пользователь (сотрудник учебного заведения) вводит в ячейку C3 фамилию учащегося, выбирает из списка учебных планов учебный план и затем щелчком на кнопке фиксирует информацию на листе Учащиеся.

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

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

' Листинг 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. Процедура обработки щелчка на кнопке Запись 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. Организация листа Ведомость

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

' Листинг 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 приведена процедура, которую необходимо написать для обработки щелчка на данной кнопке.

' Листинг 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, позволяет заполнить данными шестую строку листа, которая информирует пользователя о реквизитах дисциплины, с которой мы собираемся работать.

' Листинг 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 показан результат выбора дисциплины Рисунок и живопись по плану 6ИВУ. Далее сотруднику учебного заведения необходимо щелчком на кнопке Заполнить ведомость внести информацию об учащихся и их оценках в определенную область листа. Для этого нам потребуется процедура, представленная в листинге 5.11.

' Листинг 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.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.13 приведена процедура заполнения печатной формы ведомости.

' Листинг 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.13. На листе Сводная ведомость три элемента управления: два поля со списком и одна кнопка. Поле со списком (Name — Sem), расположенное рядом с подписью Семестр, предназначено для указания семестра, за который мы собираемся получить сводную ведомость. Другое поле со списком (Name — Plan), расположенное рядом с подписью План, предназначено для указания учебного плана. Кнопка на листе (Name — OK) позволяет заполнить сводную таблицу фамилиями учащихся и их оценками. В листинге 5.14 приведена необходимая техническая процедура, выполняемая при активизации листа и заполняющая поля со списками (семестрыи учебные планы).

' Листинг 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.15 приведена процедура заполнения сводной таблицы.

' Листинг 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.16).

' Листинг 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), которая позволяет заполнять лист информацией.

' Листинг 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), выполняющую обыкновенную сортировку но семестрам.

' Листинг 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.

' Листинг 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

VBA•Автоматизация•Решения

• Яндекс.Коллекции

• ВКонтакте

• Facebook

• Twitter

• Evernote

• Pocket

• WhatsApp

• telegram

Навигация по записям

Настраиваем учет внесения денежных средств при платных образовательных услугах при помощи Excel и сети Интернет

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

По теме

• Управление Excel из других офисных программ пакета Microsoft Office

• Как отправлять электронные сообщения Microsoft Outlook прямо из вашего листа таблицы Excel с помощью VBA

• Очень простой пример управления Word из Excel с помощью VBA

• О. А. Сдвижков — Непараметрическая статистика в MS Excel и VBA

• С. Роман — Использование макросов в Excel

Новые публикации

• Как обеспечить вывод значений выпадающего списка с двоеточием, как в бухгалтерских программах?

• Как при помощи инструментов Excel решить задачу о коробке максимального объема

• Построение графика функции одной переменной средствами Excel

• Как правильно вводить, редактировать и копировать формулы в таблицах Excel

• Как в Excel обойтись без подстановки шрифтов при применении малых кеглей



Популярно

Метки

ВКонтакте

• Как в Excel 2010 работает режим защищенного просмотра документов

• Эффективно выбираем ячейки на листе Excel

• Выбор «особых» диапазонов на листе Excel

• Отмена, возврат и повтор действий пользователя при работе в Excel

• Знакомство с полезными сочетаниями клавиш