Методические рекомендации в использовании электронных таблиц

Microsoft Excel для компьютерного тестирования
Проверка знаний учащихся с помощью компьютера значительно ускоряет проце дуру подведения итогов выполненных работ и снижает количество ошибок при их оценке. Для создания программы компьютерного тестирования обычно привлекают программистов. Однако существуют компьютерные программные средства, позво ляющие грамотному пользователю решать подобные задачи самостоятельно. Одно из них — электронные таблицы Microsoft Excel. Особенность этой программы состоит в том, что она позволяет применять для расчетов логические выражения. Ими можно воспользоваться и при подготовке тестов, контрольных работ и т.д. Ниже приведен пример использования электронных таблиц Microsoft Excel для компьютерного тестирования школьников. Тестовый документ состоит из шестнадцати листов. На пятнадцати из них размещены тесты — один на каждом листе, содержащий 20 заданий с разным количеством вариантов ответов, на шестнадцатом — ведомость, в которой автоматически отображаются результаты тестирования. Листы с тестами оформлены следующим образом. С рабочего поля удалена сетка, для чего в меню
Сервис
выбрана команда
Параметры,
далее в диалоговом окне Параметры на вкладке Вид удален флажок перед строкой сетка. Предназначенные для ввода фамилии тестируемого, его инициалов и ответов на тестовые задания ячейки имеют темные границы. Для этого выполнены следующие действия: выделены соответствующие ячейки, в меню
Формат
выбрана команда
Ячейки,
в диалоговом окне Формат ячеек на вкладке Границы выбраны внешние. На листе с ведомостью в столбец А внесены номера билетов с тестами (с I по XV), в строку 4 — номера тестовых заданий (с 1 по 20). Учащийся должен выполнить тестовые задания, содержащиеся на одном из лис тов. Предварительно он вводит с помощью клавиатуры свои
фамилию и инициалы в специально предназначенные для этого поля в верхней части листа. Автоматически компьютер вносит эти данные в ведомость в ячейку, соседнюю с той, которая содержит номер этого листа. Затем тестируемый на листе с тестом вводит ответы на тестовые задания в поля, расположенные справа от строки Правильный ответ. В случае верного ответа напротив фамилии учащегося в ведомости компьютер в соответствующую ячейку вносит знак «+», неправильного - знак «-». По окончании тестирования в графе Сумма компьютер указывает количество правильных ответов и в графе Оценка выставляет отметку. Нами установлен следующий критерий оценки результатов: если количество пра вильных ответов больше или равно 18, то тестируемый получает отметку «отлично», если больше или равно 15 - «хорошо», если больше или равно 12 - «удовлет ворительно», меньше или равно 11 - «неудовлетворительно». Однако при желании этот критерий можно изменить. Следует заметить, что в ведомости сохраняются результаты всех тестируемых. Для того чтобы нужная информация из листов с тестами автоматически вводилась в лист с ведомостью, соответствующие ячейки связаны между собой с помощью формул. Это сделано следующим образом. В ячейку В5 листа ведомости введена формула: =Лист 1! D$1, где Лист 1 - имя (номер) листа с тестом; D1 - адрес ячеек для ввода фамилии и инициалов тестируемого на листах с тестами; «!» - знак, позволяющий в формуле отделить имя листа от адреса ячейки; знак «$» - знак, добавляемый к адресу ячейки (в нашем примере D1) для того, чтобы при копировании формулы этот адрес не менялся. Далее эта формула скопирована в блок ячеек В6 : В15. Номера (имена) листов тестирования в скопированных формулах скорректированы вручную. В результате в ячейки В5... В15 листа ведомости введены формулы: =Лист1! D$1, =Лист2! D$1, =Лист3! D$1, =Лист4! D$1, =Лист5! D$1, =Лист6! D$1, =Лист7!D$1, =Лист8! D$1, =Лист9! D$1, =Лист10! D$1, =Лист11! D$1,
=Лист12!D$1, =Лист13!D$1, =Лист14!D$1, =Лист15!D$1. После этого в ячейки С5... V20 листа ведомости введены формулы, содержащие логическую функцию
ЕСЛИ().
В общем виде эта функция может быть записана так:
ЕСЛИ (<логическое условие>; <выражение 1>;

<выражение 2>).
Она позволяет учесть одно или несколько условий. Если логическое условие выполняется, то значение данной ячейки определит <выражение 1>, в противном случае - <выражение 2>. Формула, введенная в ячейку С5, имеет следующий вид:
=ЕСЛИ(Лист1! F$11=1;
«+»; «-»), где Лист1 — имя листа тестирования, F$11 — адрес ячейки, в которые на листе 1 вводится ответ на первое тестовое задание. Запись:
Лист1!F$11=1
представляет собой логическое условие; запись: «+» - выражение 1; запись: «-» - выражение 2. Формула расшифровывается так: если в ячейку F11 на листе тестирования 1 ввести цифру 1 (номер правильного ответа на первый вопрос), то в ячейке С5 листа ведомости следует отобразить знак «+», в противном случае - знак «-». Формула, введенная в ячейку D5, представляет собой следующую последовательность: =ЕСЛИ(Лист1! F$24=1;«+»;«-») - и отличается от формулы в ячейке С5 логиче ским выражением, содержащим адрес ячейки для ответа на второе тестовое задание и правильный ответ. Для ячеек Е5... V20 составлены аналогичные формулы. Затем формулы из ячеек С5... V5 скопированы в соответствующие ячейки листа ведомости и вручную скорректированы номера (имена) листов тестирования. Установив связь между ячейками листов тестирования и листа ведомости, нужно ввести в ячейки ведомости формулы, позволяющие оценивать результаты тестирования. Для подсчета количества правильных ответов (значков «+»), использована статистическая функция
=СЧЕТЕСЛИ(),
дающая возможность подсчитать в определенном блоке ячеек количество тех, которые удовлетворяют заданному условию. Сначала в ячейку W5 была введена следующая формула:
=СЧЕТЕСЛИ(С5: V5
;«+»),
где знак «+» - заданное условие, C5:V5 - блок ячеек, в котором требуется подсчитать количество этих знаков. После этого формула скопирована в блок ячеек W6: W19. В нашем примере для оценки результатов тестирования необходимо учесть несколько условий: если количество правильных ответов больше или равно 18, если больше или равно 15, если больше или равно 12. Поэтому при составлении формулы ис пользуется система вложенных одна в другую функций ЕСЛИ(). В результате формула для оценки тестирования имеет вид: =ЕСЛИ(W5>=18;"5";ЕСЛИ( W5>=15;"4";ЕСЛИ( W5>=12;"3"; ЕСЛИ(W5<=11;"2")))) Она введена в ячейку Х5 и скопирована в блок ячеек Х6 : Х19. Чтобы учащиеся не могли самостоятельно узнать результаты тестирования, лист ведомости убран с экрана. Для этого в меню
Формат
выбраны команды
Лист/Скрыть.
Чтобы этот лист вновь вывести на экран, следует в меню
Формат
выбрать команды
Лист/Отобразить.
Чтобы учащиеся не могли заранее увидеть правильные ответы в листе ведомости, этот лист настроен таким образом, что формулы не отображаются на экране. Для этого были выполнены следующие действия: выделены ячейки с формулами, в меню
Формат
выбрана команда
Ячейки,
в диалоговом окне Формат ячеек на вкладке Защита установлен флажок перед строкой Скрыть формулы, а затем в меню
Сервис
выбраны команды
Защита/Защитить лист
и в диалоговом окне введен пароль. Созданный документ сохраняют в памяти компьютера. При желании ведомость можно хранить в отдельном файле. В этом случае в формулах, приведенных выше, указывая адреса ячеек, следует включать имя файла, в котором находятся листы тестирования. Число тестовых заданий, количество листов с тестами, их оформление, а также оформление листа ведомости с результатами тестирования могут быть различными.