Creating an electronic textbook in EXCEL using the VBA programming language

Romanchenko Tatyana Nikolaevna PhD in Pedagogy Associate Professor, Department of Information Law and Digital Technologies, Saratov State Law Academy 410056, Russia, Saratovskaya oblast', g. Saratov, ul. Chernyshevskogo, 104, aud. 112 tat.romanchenko1@yandex.ru

Введение

На сегодня существуют различные средства создания электронных учебников: от языков программирования (Delphi, C++, Visual Basic, HTML, PHP, JavaScript, Dreamweaver и др.) и редакторов для создания веб-страниц (FrontPage, SharePoint Designer, Microsoft Expression Web и др.) до средств создания электронных книг (eBooksWriter ^[1], eBook Maestro ^[2], ChmBookCreator ^[3] и др.) и специальных пакетов для создания электронных учебников (SunRav BookOffice) и программ тестирования (SunRav TestOffcePro) ^[4]. Разработаны и учебные курсы по созданию электронных учебников ^[5].

Однако, многие авторы разрабатывают электронные учебники на основе специализированных программах средств (Mathcad и Matlab) ^[6], или в процессе освоения ряда дисциплин активно используют MS Excel ^[7,8,9], или MS Excel и Matlab ^[10], или MS Excel и возможности VBA ^[11,12]. Существует проблема создания образовательных ресурсов, которые не только представляют знания в электронном формате, но и одновременно погружают в среду моделирования и выполнения сложных математических расчетов. Кроме того, множество преподавателей затрудняются сделать первый шаг в направлении создания авторских электронных учебников.

Для дисциплин, требующих моделирования, мультимедийного представления процессов, выполнения сложных расчетов или многочисленных рутинных операций, эффективным средством разработки электронных учебников является табличный процессор MS Excel с возможностями программирования в среде VBA. Целесообразность его использования при создании электронных учебников для высшей школы заключается в широкой доступности MS Excel, наличии арсенала функций и надстроек для решения различных задач ^[7,11,12], а также знакомством обучающихся с его основами и готовностью работать в его среде.

Задачей является исследование возможностей создания обязательных элементов электронного учебника в среде Excel: способов оформления фрагментов теории, организации автоматического предъявления индивидуальных заданий с автопроверкой, реализации моделирования с элементами мультимедиа, наглядного представления итоговых результатов освоения темы с использованием возможностей Excel; а также программных кодов VBA.

Постановка задачи

Разработка электронного учебника довольно трудоемка и сложна, однако процесс можно разбить на этапы и создавать отдельные фрагменты электронного учебника по темам. Основными компонентами содержания электронного учебника являются информативная, репродуктивная, творческая, эмоционально-ценностная, деятельностная и интерактивная. ^[13]. В общем случае содержание и структура фрагмента электронного учебника по теме должны включать не только теоретический материал, но и задания на предметные и практические действия; задания типовые и творческие; а также интерактивные элементы обратной связи, средства моделирования, навигации и ориентировки ^[14,15,16]. Кроме того, принципиальными требованиями к конструкции электронного учебника являются наличие блока целеполагания и мотивации, краткость изложения, полнота представления учебного материала, возможность модификации, предоставление дозированной помощи ^[17]. Элементы обратной связи могут быть представлены всплывающими подсказками, краткой справкой, автоматической проверкой ^[17]. Электронный учебник желательно обеспечить средствами создания закладок и комментариев, собственных конспектов обучающимися ^{[17, 18]}.

Задачей исследования являлась демонстрация возможностей создания фрагмента электронного учебника в среде MS Excel c использованием языка программирования Visual Basic for Applications. Область исследования ограничивается определением:

· способов представления теоретического материала и наглядной демонстрации;

· возможностей моделирования с наглядной демонстрацией;

· автоматического предъявления индивидуальных заданий;

· автоматической проверки результатов выполнения заданий;

· приемов создания элементов навигации;

· приемов автоматического наглядного отображения результатов освоения темы.

Методы исследования

Исследование проводилось на примере создания фрагмента электронного учебника в среде Excel и VBA по теме «Нормальный закон распределения непрерывных случайных величин».

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

Текст последовательно располагается в ячейках одного столбца с выравниванием «перенос по словам» (Формат ячеек – Выравнивание –Перенос по словам). Формулы создаются в MS Word и копируются в ячейки. Отображение сетки и заголовков столбцов/строк снимается вручную (Лента Вид – снять флажки Сетка, Заголовки) или посредством следующих кодов в процедуре активизации данного листа:

ActiveWindow.DisplayGridlines = False

ActiveWindow.DisplayHeadings = False

Восстановление отображения сетки и заголовков столбцов/строк задается аналогичными кодами со значением True. Фрагменты теории могут быть полностью подготовлены в отдельном файле Word, который вставляется на лист Excel как объект из файла. В этом случае поля документа Word не отображаются. Кратко теория может быть представлена в ячейках, для которых снято отображение внутренних и внешних границ, или как фрагмент текста из документа Word, скопированный как рисунок и т.д.

Наглядный материал, в общем случае, – это рисунки из других приложений. Эффективным средством наглядной демонстрации являются диаграммы Excel. Они могут представляться как активный объект Диаграмма, изменяющийся при изменении данных в области построения, или как обычный рисунок. Оптимальный способ снятия связи Диаграммы с областью построения – это вставка объекта Диаграмма как рисунка. (Выделить диаграмму – Копировать – Вставить – Параметр вставки – Рисунок).

Средства моделирования в Excel. Использование функций, представленных в Excel, – простейший способ моделирования различных процессов. Применительно к указанной теме моделирование заключается в задании значений функции распределения F(x) и плотности вероятности p(x) нормально распределенной случайной величины с параметрами (μ, σ). Моделирование стандартного нормального распределения реализуется функцией – НОРМ.СТ.РАСП(z; интегральная), нормального распределения с параметрами (μ, σ) функцией – НОРМ.РАСП(x; среднее; стандартное_откл; интегральная). Названные функции одновременно являются и средством моделирования нормального закона распределения случайных величин, и объектом изучения как средства автоматизированных вычислений при решении задач по теории вероятностей.

Рис.2.Фрагмент листа моделирования стандартного нормального распределения.

Моделирование стандартного нормального распределения выполняется на отдельном листе, где формируется массив ячеек со значениями x, F(x) и p(x), фрагмент которого приведен на рис.2. Формулы моделирования =НОРМ.СТ.РАСП(B10;ИСТИНА) и =НОРМ.СТ.РАСП(B10;ЛОЖЬ) введены, соответственно, в ячейки C10, D10 Шаг в столбце для переменной x выбирается по усмотрению, в примере на рис.2. он равен 0,25.

В зависимости от сценария занятия формулы моделирования могут быть введены автоматически вызовом соответствующей процедуры по нажатию кнопки «ввод функций», либо вручную. При необходимости выполняется процедура очистки данных (кнопка «очистка»). Наглядной демонстрацией служат заранее построенные точечные диаграммы с гладкими кривыми: «Плотность вероятности p(x) для N(0,1)», «Функция распределения F(x) для N(0,1)», связанные с заполняемым диапазоном. При вводе или изменении данных на них отображаются соответствующие графики функций.

Вероятность попадания случайной величины, распределенной по стандартному нормальному закону, в заданный интервал моделируется заданием нижней и верхней границ интервала – это ячейки H8 и I8 (см. рис.2.). В ячейки H9 и I9 для автоматического определения значений F(a) и F(b) из массива смоделированных выше данных значений функции F(x) введены, соответственно, формулы:

=ИНДЕКС(B10:C38;ПОИСКПОЗ($H$8;B10:B38);2),

=ИНДЕКС(B10:C38;ПОИСКПОЗ($I$8;B10:B38);2).

При изменении значений в ячейках H8 и I8 значения в связанных ячейках H9 и I9 меняются. Данные для построения линии нижнего квантиля формируются автоматически в ячейках H10:H11 (значения x) и I10:I11 (значения y), для верхнего квантиля, соответственно, в H12:H13 и I12:I13. Диаграмма с заливкой области между квантилями представлена на рис.3. Для ее построения в графе «Область между квантилями» (см. рис.2.) введен дополнительный ряд значений функции F(x) с изменениям величины x в пределах указанных границ. В ячейке E10 введена формула

=ЕСЛИ(И(B10>=$H$11;B10<=$H$13);НОРМ.СТ.РАСП(B10;ИСТИНА);НД())

Функция НД() используется, чтобы из области построения F(x) вывести значения вне интервала (a,b).

Моделирование расположения и вида кривых – функции распределения и кривой плотности распределения для нормального закона распределения в зависимости от величины математического ожидания μ и стандартного отклонения σ реализуется использованием функции НОРМ.РАСП(x;среднее;стандартное_откл; интегральная), которая в зависимости от значения аргумента «интегральная»: истина или ложь, – возвращает значение F(x) или p(x), соответственно. На рис.4.приведен фрагмент листа для ввода параметров моделируемых распределений.

Значение математического ожидания вводится в ячейку D10. Ячейки E10, F10 принимают такое же значение, в них расположена формула =$D$10. В ячейки D11, E11, F11 вводятся значения стандартного отклонения. Шаг по х вычисляется в ячейке D12 по формуле: =макс(D11:F11)/5. В ячейку D14 вводится формула =НОРМРАСП($C14;D$10;D$11;$G$11), которая копируется на ячейки E14 и F14. Вид кривой на диаграмме кривой (функция распределения или плотность вероятности) зависит от вводимых в ячейку G11 значений: «истина» или «ложь». Диапазон изменения величин x, P1(x), P2(x),P3(x) заполняется автоматически и по нему строится диаграмма – точечная с гладкими кривыми. Название диаграммы задается как ссылка на ячейку $B$8, где находится формула =ЕСЛИ(G11;C9;C8), а в ячейках C8 и C9, соответственно, формулы:

="плотность распределения для σ1 ="&D11&" σ 2="&E11&" σ 3="&F11&""

="функция распределения для σ1 ="&D11&" σ 2="&E11&" σ 3="&F11&""

Получаемые диаграммы имеют вид, представленный на рис.5

Рисунок 5. Графики функций F(x) и P(x).

Моделирование влияния параметров «математическое ожидание» и «стандартное отклонение» на форму и расположение кривых плотностей распределения вероятностей для двух рядов выполняется также с использованием функции НОРМ.РАСП(x;среднее;стандартное_откл; интегральная). После ввода значений параметров моделирование выполняется автоматически или в мультимедийном режиме. Фрагмент листа Excel для ввода данных приведен на рис.6. В ячейку B3 введена формула –

="плотность распределения для μ1="&C4&" σ1 ="&C5&" μ2="&D4&" σ2="&D5&" номер"&E4&"" –

для отображения в названии строящихся диаграмм значений параметров μ и σ, номера задания. На листе значение ячейки не отображается ввиду задания белого цвета текста.

В ячейки С4, С5 и D4, D5 вводятся, соответственно, математическое ожидание и стандартное отклонение моделируемых рядов, в ячейку E4 – номер задания. При нажатии кнопки «ОК» запускается программа автоматического построения рядов данных и точечной диаграммы с гладкими кривыми, соответствующих введенным параметрам μ и σ. Диаграмма автоматически располагается в фиксированном месте листа. С целью сохранения графического изображения построенной диаграммы процедура завершается автоматическим копированием и специальной вставкой диаграммы в формате рисунка, позиция которого на листе зависит от номера задания. Текущая диаграмма, данные для ее построения автоматически удаляются для проведения очередного исследования. Код программы на VBA приведен ниже.

Фрагмент программы на VBA, моделирующий значения функций плотности P(x) для двух распределений с задаваемыми значениями математического ожидания и стандартного отклонения (адреса вводимых значений см. на рис.6) имеет вид:

Лист = ActiveSheet.Name

‘ определение минимального и максимального значений интервала для x

'количество интервалов равно 48

Range("C6").FormulaLocal = "=мин(C4:D4)-макс(C5:D5)*3-1"

n = Range("C6").Value

Range("C6").FormulaLocal = "=макс(C4:D4)+макс(C5:D5)*3+1"

k = Range("C6").Value

S = (k - n) / 48

Cells(6, 2) = S

'очистка промежуточных значений в ячейке С6

Range("C6").Select

Selection.ClearContents

'моделированиезначений P1(x), P2(x)

i = 1

While n <= k

Cells(i + 7, 1) = i

Cells(i + 7, 2) = n

Cells(i + 7, 3).Select

ActiveCell.FormulaR1C1 = "=NORM.DIST(RC^[-1],R4C3,R5C3,FALSE)"

Cells(i + 7, 4).Select

ActiveCell.FormulaR1C1 = "=NORM.DIST(RC^[-2],R4C4,R5C4,FALSE)"

i = i + 1

n = n + S

Wend

Фрагмент программы построения точечной диаграммы с гладкими кривыми по данным моделирования функций плотности распределения и размещения ее в определенном месте листа Excel представлен ниже:

'построение диаграммы точечной с гладкими кривыми

Range(Cells(7, 2), Cells(i + 7, 4)).Select

ActiveSheet.Shapes.AddChart2(240, xlXYScatterSmoothNoMarkers).Select

ActiveChart.SetSourceData Source:=Range(Cells(7, 2), Cells(i + 7, 4))

'определениеименидиаграммы

ИмяДиаграммы = Trim(Right(ActiveChart.Name, Len(ActiveChart.Name) - Len(Лист) - 1))

'задание названия диаграммы для отображения на диаграмме

НазвДиагр = Range("B3").Value

With ActiveChart

.HasTitle = True

.ChartTitle.Characters.Text = НазвДиагр

End With

'указание места размещения диаграммы

With ActiveSheet.Shapes(ИмяДиаграммы)

.Top = Range("F10").Top

.Left = Range("F10").Left

.Height = Range("F10:L24").Height

.Width = Range("F10:L24").Width

End With

Программа временной задержки с передаваемым параметром длительности представлена ниже:

Sub StopSub(Pause As Integer)

Dim Start As Single

Start = Timer

Do While Timer < Start + Pause

DoEvents

Loop

End Sub

Рис.7.Графики результатов моделирования.

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

Способы автоматического предъявления и проверки индивидуальных заданий. На каждом из листов книги Excel располагаются индивидуальные задания. Количество вариантов комплектов заданий произвольно. Например, для обеспечения полной самостоятельности при аудиторной работе количество вариантов соответствует числу компьютеров в учебном классе.

Строки или ячейки с заданиями оформляются заливкой одинакового цвета. Одна из ячеек листа используется как образец заливки цвета. При первоначальном открытии файла выполняется процедура, скрывающая строки с заливкой цветом. При выборе обучающимся номера варианта отображаются строки Excel с заданиями, соответствующие выбранному варианту. Фрагмент программы отображения строк с заливкой цветом представлен ниже.

Dim cell As Range

Application.ScreenUpdating = False

For Each cell In ActiveSheet.UsedRange.Columns(1).Cells

If cell.DisplayFormat.Interior.Color = Range("AA1").DisplayFormat.Interior.Color

Then cell.EntireRow.Hidden = True

Next

Application.ScreenUpdating = True

В приведенном коде ячейка с образцом заливки – АА1.Если не желательно иметь ячейку с заливкой цветом образца, то во фрагменте выше оператор If ….Then можно заменить следующим:

If cell.DisplayFormat.Interior.Color = RGB(204, 255, 204)

Then cell.EntireRow.Hidden = True

В этом случае предварительная заливка ячеек с заданиями выполняется цветом, соответствующим цветовой модели RGB на вкладке Спектр диалогового окна Цвета.

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

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

Автоматическая проверка числовых результатов выполнения заданий реализуется с помощью логических функций Excel. На рис.8 в ячейку E34 введена формула: =ЕСЛИ(ЕПУСТО(D34);"введите значения";ЕСЛИ(D34=6; "верно";"не верно")). При активизации ячейки с ошибочным результатом решения всплывает подсказка, которая создается, например, с помощью последовательности команд Лента Данные – Проверка данных – Проверка вводимых значений.

Использование вложенных логических функций позволяет выполнять в ячейках и проверку правильности вводимых формул. На рис.9 в ячейке F85 введена формула

=ЕСЛИ(И(ЕФОРМУЛА(E85);E85=НОРМ.РАСП($F$81;$D$80;D81; ИСТИНА) - НОРМ.РАСП($F$80;$D$80;D81;ИСТИНА));"верно"; ЕСЛИ(ЕПУСТО(E85);"введите ф-лу";"не верно"))

Рис. 9.Фрагмент листа с заданием (проверка формул).

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

Навигация. Способов создания средств навигации по фрагменту электронного учебника немало. Например, содержание фрагмента электронного учебника может иметь вид (см. рис.11). Переход на соответствующий лист выполняется по щелчку на кнопке. Тело процедур обработки событий по нажатию кнопок содержит всего одну строку кода: Sheets (“Имя Листа»).Select.

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

Заключение

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

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

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