Главная страница «Первого сентября»Главная страница журнала «Информатика»Содержание №7/2009


Предлагаю коллегам

Использование MS Excel в локальной сети для оценки конкурсов и не только

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

Если же турнир проводится в условиях кабинета информатики, то организация судейства “по старинке”, во-первых, неоправданна, а во-вторых, может вызвать даже недоумение: “Почему, имея такой арсенал современной техники и программного обеспечения, невозможно его применить и организовать состязания так, как это уже стало привычным при проведении соревнований и конкурсов, за которыми мы следим по телевидению?” Если это не может сделать учитель информатики, то тем самым не только подрывается его авторитет, но и углубляется раскол в сознании учащихся между теорией и практикой. Да и найдет ли отклик в душах воспитанников и учителей других предметов его миссионерская деятельность по пропаганде использования информационных технологий в учебной и профессиональной деятельности?

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

В минувшем учебном году в нашей школе проводился “Турнир знатоков информатики” между командами 8–9-х и 10–11-х классов. Были подобраны такие конкурсы, чтобы с ними могли справиться как старшие, так и младшие школьники. Заданий набралось много, и даже после тщательного отбора осталось около двадцати. Хватало и командам, и капитанам, и болельщикам. Однако временные рамки мероприятия позволяли реализовать задуманное только при тщательной организации турнира.

Информационную составляющую турнира обеспечивали двенадцать автоматизированных рабочих мест. Центром управления стал учительский сервер, еще три ЭВМ — предоставлены членам жюри. Каждая команда из 6 человек получила в свое распоряжение по три компьютера. Остальные два достались болельщикам — по одному на каждую группу.

Реализации сценария турнира способствовала специально созданная для этих целей презентация, которая включала в себя последовательность заданий на слайдах с максимальным числом баллов за каждое и при необходимости правильные ответы, которые также могли быть продемонстрированы с использованием динамических возможностей MS PowerPoint. Звуковое и музыкальное сопровождение соревнования частично легло в канву презентации, а оставшаяся часть была задействована с помощью проигрывателя AIMP2, плей-лист которого содержал в соответствующем порядке ссылки на необходимое шумовое и фоновое оформление.

Демонстрация (как одновременная, так и выборочная) всех этапов конкурса на компьютеры болельщиков, команд и жюри выполнялась с помощью демоверсии приложения NetSupport School, позволяющего осуществить широкий спектр сетевого взаимодействия между учительским и ученическими рабочими местами. С таким же успехом можно использовать потенциал утилиты NetMeeting, входящей в состав операционной системы Windows, проектор или интерактивную доску.

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

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

Рис. 1. Фрагмент листа таблицы оценок конкурсов

Сбор и обработка результатов судейства осуществлялись также в приложении MS Excel с помощью специально созданного для этих целей файла, но на учительском компьютере. Один из листов книги этого файла представлял собой копию листа, разосланного членам жюри, но столбцов оценки в нем содержалось уже три (по одному на каждого судью). Кроме того, пришлось добавить еще один столбец определения средней оценки.

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

=[Жюри.xls]Оценки!$D$4

Здесь: Жюри.xls — название файла оценок, Оценки — название листа оценок, $D$4 — имя конкретной ячейки в столбце оценки.

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

='\\U01\Temp\[Жюри.xls]Оценки'!$D4

Здесь U01 — имя компьютера члена жюри, Temp — имя папки свободного доступа на компьютерах.

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

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

1. Каждый судья после выставления оценки должен нажать кнопку — сохранить файл.

2. В файле сбора и обработки результатов на учительском компьютере выбрать Правка – Связи… В открывшемся диалоговом окне последовательно указать каждый источник и нажать кнопку “Обновить”. Желательно воспользоваться кнопкой “Запрос на обновление связей…” и выбрать “Не задавать вопрос и не обновлять связи” при открытии книги Excel.

Выполнение этих операций можно упростить, поручив их соответствующим макросам. Если в файле жюри, включив панель инструментов Visual Basic кнопкой , перейти в Редактор Visual Basic и, выделив в Проводнике проекта строку “Эта книга”, в верхней части окна кода в списке объектов (General) выбрать Workbook, а в списке событий (Declarations) — SheetChange, то в появившуюся заготовку процедуры достаточно будет добавить одну короткую строку, чтобы избавить членов жюри от обязанности каждый раз сохранять файл. При этом процедура будет выглядеть так:

Private Sub Workbook_SheetChange(ByVal

Sh As Object, ByVal Target As Range)

ActiveWorkbook.Save

End Sub

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

Чтобы создать макрос обновления связей в файле сбора информации и подведения итогов, нажмите кнопку “Запись макроса” на панели Visual Basic, щелкните по кнопке OK в появившемся окне и выполните все действия по обновлению данных. В завершение остановите запись макроса кнопкой , появившейся на месте предыдущей. Теперь для получения сведений от жюри достаточно кликнуть по кнопке “Выполнить макрос”.

При отсутствии данных в файлах жюри в ячейках итоговой таблицы появляются нули, а при отсутствии доступа к ним после обновления может появиться сообщение #ССЫЛКА. Если такие сведения нежелательны, то предотвратить их появление можно вводом в формулы проверки условий с помощью логической функции ЕСЛИ() и функции проверки свойств и значений ЕОШ().

Табло “Общий счет” было красочно оформлено на отдельном листе, где в ячейки с общей суммой баллов введены формулы суммирования данных по каждой команде.

Рис. 2. Табло “Общий счет”

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

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

Вскоре после конкурса подошло время сдачи и защиты творческих проектов в виде презентаций и web-ресурсов по элективным курсам и основной программе.

Следует отметить, что среди учащихся 8–9-х классов поначалу не все отнеслись к этому виду работы ответственно и добросовестно. Вот здесь и пришлось воспользоваться тем, что для подростков, как правило, мнение своих сверстников имеет значительно больший вес, чем точка зрения учителя или родителей. Ребятам было объявлено, что оценивать проекты они будут сами, а в журнал будет выставлена та оценка, которая является средним значением полученных баллов. Деятельность сразу же заметно оживилась.

Для реализации задуманного нашлась работа и преподавателю. В ее основу была положена уже отработанная идея сбора информации по локальной сети из файлов электронных таблиц на компьютерах учащихся. Такой файл представляет собой книгу, в которой на каждом листе размещен нумерованный список учащихся класса, за которым следует столбец оценок. Число листов соответствует количеству классов. Чтобы исключить влияние со стороны соседей на выставляемые оценки, цвет знаков в этом столбце был выбран белым под цвет фона, а строка формул через меню “Сервис — Параметры… — Вид” отключена.

Над файлом подведения итогов пришлось потрудиться больше, чем для турнирного, так как надо было предварительно открыть по сети каждый файл с рабочего места учащихся и собрать ссылки на ячейки с оценками каждого рабочего листа. Часть этой работы была проделана с помощью редактирования аналогичных формул с последующим автозаполнением. Следует отметить, что такие операции могут быть осуществлены даже в том случае, если файлы оценки вами еще не разосланы на все компьютеры и даже если остальные компьютеры вашей сети выключены. Однако в этом случае вам придется потратить значительное количество времени и нервов, дожидаясь, пока операционная система не убедится, что доступа к указанному компьютеру нет, а убедившись, будет предлагать найти источник самим через диалоговое окно открытия файла, закрывая которое кнопкой Отмена, вам придется изрядно поработать, терзая мышку длинными очередями кликов. Функция СРЗНАЧ() нахождения среднего арифметического из-за уже упоминавшейся проблемы нулей и ссылок может давать неверные значения или выводить сообщение об ошибке, поэтому лучше воспользоваться формулой вида:

= СУММ(D4:F4)/СЧЁТЕСЛИ(D4:F4;">0").

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

Эта проблема навела на мысль о необходимости автоматизировать с помощью макросов рассылку новых или отредактированных файлов оценки и создание файла подведения итогов. В результате в электронных таблицах MS Excel был создан “Менеджер оценки”, который реализует эти операции. На его титульном листе необходимо ввести имя файла, предназначенного к рассылке, который должен находиться в той же папке, что и файл менеджера, а также имена компьютеров учащихся в локальной сети и имя папки свободного доступа на них. Отметив флажками необходимые рабочие места, можно соответствующими кнопками произвести рассылку, удаление файлов или установку их атрибутов. Информация о ходе выполнения этих операций будет отображаться на этом же листе. Для формирования итогового отчета достаточно щелчка по кнопке “Создать”. Процесс завершится быстрее, если необходимые файлы уже разосланы, а выбранные компьютеры включены и доступны по сети.

Применение менеджера оценки налагает некоторые ограничения:

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

— в первом столбце следует пронумеровать список, так как количество учащихся определяется макросом по максимальному значению в нем;

— для оценок предназначен третий столбец;

— объединение ячеек нежелательно, так как может привести к сбоям.

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

Рис. 3. Титульный лист менеджера оценки

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

Во многих конкурсах финала межрегионального молодежного фестиваля “Интернет и Мы” наряду с оценкой основного жюри, в котором приходилось участвовать и автору этих строк, учитывалась оценка общественного жюри — самих команд-участниц. Организация, сбор и обработка бумажных протоколов таких оценок отнимали львиную долю рабочего времени судей, а результаты не отличались оперативностью. Такую процедуру не трудно автоматизировать, если воспользоваться предложенной методикой, для чего достаточно список учащихся заменить списком команд, выделив по рабочему листу электронных таблиц на каждый конкурс или на каждый критерий конкурса. Менеджер оценки также можно использовать при проведении различных опросов и определении рейтинга.

Готовые файлы, на которые делались ссылки в этой статье, с макросами и примерами можно найти в Интернете по адресу: http://www.rosinka.vrn.ru/dinex/ocenki.htm — или загрузить с портала газеты “Информатика”: http://inf.1september.ru/download/men_oc.rar. Макросы не содержат вирусов, доступны для анализа и снабжены подробными комментариями.

Использованные источники информации

1. Комаровский А.Н. Тестирование в электронных таблицах. / Информатика № 5/2008.

2. Справочная система Microsoft Excel 2003.

3. Справочная система Visual Basic for Application 6.5.

4. http://msdn.microsoft.com/library/.

5. http://msdn2.microsoft.com/ru-ru/library/.

А.. Н.. Комаровский,
Россошанская школа-интернат

TopList