Создание тестов в Excel

Лабораторная работа №1

Цель работы:

· Как разрабатывается тестовое задание в Excel без использования VBA;

· Функции: логические, текстовые.

Задание1. Ознакомиться с примером разработки тестового задания.

Пример разработки тестового задания:

Для составления тестовой программы понадобятся несколько листов рабочей книги Excel. Один лист будет титульным, второй — служить для показа итогов тестиро­вания, третий — расчетным, на нем будут содержаться все формулы и вся инфор­мация, которая будет дублироваться в билетах. Остальные листы будут использованы под собственно билеты, в которых будут формулироваться во­просы и тестируемые будут проставлять номера правильных ответов.

Оформление титульного листа

Начнем с титульного листа, который изображен на рис.1.

Рис.1

Оформление титульного листа начиналось с простого листа рабочей книги, показанного на рис.2. Затем на этом листе удалили сетку, что также показано на данном рисунке, и приступили к оформлению. Выделили площадку под оформление с помощью границы и дали заливку (рис.3).

Рис.2.

Рис.3.

Далее с помощью WordArt и Вставки рисунка завершили оформление титульного листа.

На титульном листе тестируемый вводит данные о себе: фамилию, класс, а также дату тестирования.

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

Рис.4

Диалоговое окно гиперссылки показано на рис.4.

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

Оформление тестового задания

На листе (рис.5), как мы видим, даны четыре определения, возле которых в ячейках надо поставить номера правильных понятий. Понятия обозначены под номерами на этом же листе. Ячейки в которые будут водятся ответы, должны быть не защищаемыми.

И ниже приведены примеры тестовых заданий (рис.5-8).

Рис.5

Лист под названием «Билет №2» (рис.6) содержит диаграммы, которые были выполнены в Excel на данном листе рабочей книги. Под каждой диаграммой есть ячейка для ввода номера вида диаграммы. Названия видов диаграмм пронумерованы и приводятся на данном листе.

Рис.6.

На рис.7 помимо диаграммы содержится фрагмент электронной таблицы, который был также выполнен на данном листе. Это наглядно показывает возможности Excel (а также преимущества) при создании документов.

Рис.7.

Лист «Билет № 4» (Рис.8) отличается от остальных тем, что при ответе на его вопрос можно пользоваться таблицей данных.

Рис.8.

Здесь необходимо отметить следующее. Для того чтобы все билеты оформить одинаково, надо нажать клавишу Shift и, удерживая ее, общелкать все ярлычки листов, в которых будут располагаться билеты. Все ярлычки станут белыми. Теперь, перейдя на любой лист, можно начать оформление дизайна билетов. Здесь же можно установить и общий масштаб для всех билетов. После оформления билетов (пока без ввода вопросов) листы следует разгруппировать. Чтобы это осуществить, надо щелк­нуть правой кнопкой мыши на ярлычке любого листа и выбрать соответствующий пункт меню. После этого можно вводить вопросы, создавать гиперссылки и защи­щать листы от несанкционированного изменения данных. Для этого надо выбрать команду Сервис, Защита, Защитить лист, далее отметить все пункты и ввести па­роль. Разумеется, перед этим следует снять защиту с ячеек, где будут вводится ответы тестируемого, иначе тестируемый не сможет ввести свои ответы. Пароль хранится в самой книге Excel, и вскрыть его для неспециалиста достаточно сложно.

Оформление итогового листа

Рассмотрим итоговый лист (рис.9). В ячейках «ученик», «сегодня» дублируется содержимое ячеек титульного листа (рис.10). «Ваша оценка» и ячейка, содержащая словесный комментарий, дублируют содержание расчетного листа, Дела­ется это следующим образом. Надо поставить курсорную рамку в ячейку «ученик», от­крыть Мастер функций, найти категорию Текстовые, в ней — функцию ПОВТОР, щелкнуть на кнопке ОК. В поле ввода текст следует ввести имя рабочего листа и адрес ячейки, чье содержимое вы хотите повторить. Число повторений в данном случае равно 1 (Рис.10).

Рис.9.

Рис.10.

Оформление расчетного листа

Перейдем к рассмотрению расчетного листа, который показан на рис.11.

Рис.11.

На расчетном листе тестирующей программы составлена расчетная таблица. Каждый столбец в первой ячейке имеет название «Билет» и номер билета. Во вторую (а также и в остальные, по числу вопросов) ячейку каждого столбца билета вводится формула, в которой используется логическая формула ЕСЛИ(), а также имеется ссылка на рабочий лист на котором находится билет. Внутри скобок записывается относительный адрес ячейки на листе, для которой проверяется выполнение условия. Проверяется, содержится ли в данной ячейке число, которое и является номером правильного ответа. Если в ячейку введено число правильного ответа, то значение ячейки на расчетном листе становится равным 1 (ИСТИНА), иначе 0 (ЛОЖЬ).

Для подсчета суммы правильных ответов используется встроенная функция СУММ() (строка 6), но можно и непосредственно ввести в ячейку название функции с клавиатуры.

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

Следующую свободную ячейку используем для вывода оценки. Оценка зависит от доли правильных ответов. В данной ячейке используется принцип вложенности функций. Если значение в ячейке, подсчитывающей долю правильных ответов, менее 0,66 (т.е. ответы получены только на две трети вопросов), то оценка 2; если в ячейке значение менее 0,75 (но более 0,66), то оценка 3; если значение менее 0,9 (но более 0,75), то оценка 4; если значение в ячейке более 0,9, то оценка 5.

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

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

Рис.12.

Рис.13.

Защитить расчетный лист можно следующим образом. Надо сначала выделить весь расчетный лист, щелкнув на специальной кнопке, которая находится на пере­сечении номеров строк и названий столбцов в верхнем левом углу рабочего листа. После выделения следует присвоить шрифту белый цвет, т. е. цвет фона. Теперь расчетный лист будет казаться пустым. Далее надо выбрать команду Сервис, Защита, Защитить лист, два раза ввести пароль и щелкнуть на кнопке ОК. Лучше, если пароль защиты этого листа будет отличаться от пароля защиты других лис­тов. Затем для контроля надо попробовать активизировать кнопку, отвечающую за цвет шрифта, и посмотреть, стала ли она активной. Если она неактивна, то все в порядке. После установления защиты также невозможно изменить ширину и высоту ячеек, становятся недоступными многие опции и проведение многих опера­ций с данными и ячейками. Далее этот лист можно скрыть (Формат, Лист, Скрыть).

До того как файлу, содержащему тест, будет присвоен атрибут Только для чтения, записываем макрос, который подготовит Excel к проведению теста, т.е. очищает все ячейки, которые тестируемый будет заполнять, от возможных остатков предыдущего тестирования. После этого начать запись макроса: задать имя макроса «Подготовка_теста» (рис.14), сохранить макрос в этой книге макросови щелкнуть на кнопке ОК.

Рис.14.

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

Задание2. Создать тестовые задания в MS Excel. Информацию для ТЗ брать из ЕГЭ, согласно своему варианту и специальности.

Номер варианта	Специальность «Математика»	Специальность «Информатика»	Специальность «Физика»
	ЕГЭ 2003, А1-А8	ЕГЭ 2005, А1-А8	ЕГЭ 2005, А1-А8
	ЕГЭ 2003, А8-А16	ЕГЭ 2005, А9-А16	ЕГЭ 2005, А9-А16
	ЕГЭ 2003, В1-В8	ЕГЭ 2005, А17-А24	ЕГЭ 2005, А17-А24
	ЕГЭ 2004 А1-А8	ЕГЭ 2006, А1-А8	ЕГЭ 2006, А1-А8
	ЕГЭ 2004, А9-А14, В1, В2	ЕГЭ 2006, А9-А16	ЕГЭ 2006, А9-А16
	ЕГЭ 2005, А1-А8	ЕГЭ 2006, А17-А20, В1, В2, В5, В6	ЕГЭ 2006, А17-А24
	ЕГЭ 2005, А9-А10, В1-В6	ЕГЭ 2007, А1-А8	ЕГЭ 2007, А1-А8
	ЕГЭ 2006, А1-А8	ЕГЭ 2007, А9-А16	ЕГЭ 2007, А9-А16
	ЕГЭ 2006, А9-А10, В1-В6	ЕГЭ 2007, А17-А20, В1, В2 В5, В8	ЕГЭ 2007, А17-А24
	ЕГЭ 2007, А1-А8	ЕГЭ 2008, А1-А8	ЕГЭ 2008, А1-А8
	ЕГЭ 2007, А9-А10, В1-В6	ЕГЭ 2008, А9-А16	ЕГЭ 2008, А9-А16
	ЕГЭ 2008, А1-А8	ЕГЭ 2008, А17-А20, В1, В2, В5, В7	ЕГЭ 2008, А17-А24
	ЕГЭ 2008, А9-А10, В1-В6	ЕГЭ 2009, А1-А8	ЕГЭ 2009, А1-А8
	ЕГЭ 2009, А1-А8	ЕГЭ 2009, А9-А16	ЕГЭ 2009, А9-А16
	ЕГЭ 2009, А1-А8 В1-В6	ЕГЭ 2009, А17-А18, В1- В4	ЕГЭ 2009, А17-А24

Дата добавления: 2014-12-27