Учет успеваемости студентов вуза средствами VBA в системе Excel

Приложения комплекта Microsoft Office используются повсеместно.

С самого начала офисные продукты корпорации Microsoft имели средства программного управления своей работой. Последние версии Microsoft Office позиционируются именно как среда разработки конкретных офисных приложений. Тем не менее, практические наблюдения показывают, что продукты Microsoft Office редко используются для разработки приложений.

Многие задачи Microsoft Office позволяет выполнить и без программирования, однако одним из наиболее важных и полезных его свойств является возможность автоматизации процессов взаимодействия пользователя и приложений Microsoft Office, которая позволяет решать практически все современные бизнес-задачи: от создания простых документов до полной автоматизации документооборота с использованием систем управления базами данных. Это обстоятельство связано с тем, что все приложения Microsoft Office поддерживают язык программирования Visual Basic for Applications (VBA), совместимый с Visual Basic 6.

Указанные возможности Microsoft Office и языка VBA были использованы при создании приложения, которое позволяет автоматизировать выполнения наиболее трудоемких задач по анализу результатов сессий.

Данное приложение состоит из шести книг: общая книга, 1–5 курс (рис. 1).

 

 

 

Рис. 1. Структура приложения

 

Книги 1–5 курс («1 курс.xlsm» — «5 курс.xlsm»)5 предназначены для ввода и корректировки всей исходной информации по соответствующему курсу. Общая книга («общая.xlsm») используется для автоматизированной обработки введенной информации.

Рассмотрим состав и назначение всех книг приложения.

Каждая из книг 1–5 курс состоит из определенного количества листов в зависимости от количества групп на курсе.

На листах «1», «2» и т. д. (рис. 2) записывается информация о студентах и их оценках за время учебы в ВУЗе. Каждый лист предназначен для одной группы. В первой строке этих листов указывается номера курсов, во второй номера семестров (с 1-го по 10-й).

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

Рис. 2 Книга «1 курс»

После ввода всей исходной информации, т. е. после заполнения книг 1–5 курс, необходимо открыть «Общую книгу» (рис. 3).

Рис. 3 Общая книга

Данная книга состоит из шести листов: «титульный», «список задолжностей», «успеваемость по преподавателям «, «успеваемость по группам», «список отличников», «стипендия». На листе «титульный» расположены списки групп по курсам и кнопка «формирование отчетов». При нажатии на которую появляется форма (рис.4).

Рис. 4. Форма «учет успеваемости»

 

С помощью элементов управления, расположенных на этой форме пользователь может сформировать нужный ему список для выбранных групп. Листы «Список отличников» и «Список задолжников» предназначены для формирования соответствующих списков, на листах «успеваемость по преподавателям», «успеваемость по группам» выводиться соответствующая успеваемость (рис. 5). На лист «стипендия» выводиться список выбранных групп с размерами академической стипендии.

Рис. 5. Успеваемость по выбранным группам и преподавателям

Компонент listbox позволяет выбирать несколько групп. При выборе формирования отчета по преподавателю осуществляется переход на следующую форму(рис.6). Данная форма позволяет из списка преподавателей выбрать необходимых преподавателей.

Рис.6. Форма для выбора преподавателей

Рассмотрим процедуру, которая формирует список отличников. Для данной процедуры кроме передаваемой информации необходимы еще некоторые переменные:

-        динамический массив otlic(), в который будут записываться фамилии и инициалы отличников, а также курс и группа;

-        r — для подсчета оценок отличных от 5 и не сданных зачетов;

-        ii — для задания размерности динамического массива;

Первоначально ii=0.

Процедура ReDim Preserve otlic(ii,2) необходима для изменения размерности массива без потери записанных данных.

Сначала просматривается курс, записанный в номере группы первым.

k = ListBox1.List(ii)

k1 = Mid$(k, 1, 1) + «курс.xlsm»

k2 = Mid$(k, 2, 1)

Далее открываем книгу с названием курса: Workbooks.Open (ActiveWorkbook.Path & "\" & k1)

Высчитываем, с какого столбца начинать проверку оценок: nn =4+Val(Mid$(k,1,1))*30 — q *15, где q — обозначает какую из сессий надо рассматривать (зимнюю — 2, летнюю — 1)

После этого переходим на лист Worksheets(k2).Activate.

Подсчитываем количество оценок, отличных от 5 и не сданных зачетов у каждого студента и если r=0, заносим фамилию и инициалы этого студента в массив otlic:

For t = 4 To Cells(1, 1) + 3

  r = 0

For jj = 4+Val(Mid$(k, 1, 1)) * 30 - q *15 4 + Val(Mid$(k, 1, 1)) * 30 - q *15 + 15

If (Cells(t, jj)<>5) And (Cells(t, jj)<>1) And (Cells(t, jj).Text <>"") Then r=r+1

 Next jj

 If r = 0 Then

           ii = ii + 1

         ReDim Preserve otlic(ii)

         otlic(ii,1) = Cells(k, 2) + " " + Cells(k, 3)

         otlic(ii,2) =k

      End If

Next t

В ячейке Cells(1, 1) подсчитывается количество студентов в данной группе.

После проверки всего курса происходит закрытие книги с этим курсом: Workbooks(k1).Close

После проверки всех выбранных групп переходим на лист «Список отличников» и заполняем его элементами из массива otlic:

   For i = 1 To ii

     Cells(i + 1, 1) = otlic(i,1)

     Cells(i + 1, 2) = otlic(i,2)

   Next i

Таким образом, данное приложение позволяет:

-          составлять списки отличников и задолжников заданных групп за сессию;

-          распределять стипендии;

-          выводить успеваемости по группе, по преподавателю или итоговую успеваемость.

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

Разработанное приложение может быть использовано не только в деканате, но также и в других подразделениях ВУЗа.

 

Литература:

 

1.                  Берндт Г., Каинка Б. «Измерение, управление и регулирование с помощью макросов VBA в Word и Excel» 2008 г.

2.                  Л. Д. Слепцова Программирование на VBA в Microsoft Office 2010. — М.: Диалектика, Вильямс, 2010, 432с., самоучитель.