41668

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

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

Информатика, кибернетика и программирование

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

Русский

2013-10-24

286.5 KB

247 чел.

Лабораторная работа № 1 Создание теста средствами Excel

Задания

  1.  Выполните методические указания к лабораторной работе.
  2.  Разработайте самостоятельно тест с соблюдением следующих условий:
    1.  количество вопросов 10;
    2.  кнопки перехода;
    3.  дизайн;
    4.  защита.

Оглавление

[1] Оглавление

[2] Методические указания к лабораторной работе
СОЗДАНИЕ КОМПЬЮТЕРНЫХ ТЕСТОВ СРЕДСТВАМИ Excel

[2.1] ОБУЧАЮЩИЕ ТЕСТЫ

[2.2] ПРОВЕРОЧНЫЕ ТЕСТЫ

[2.2.1] Тесты со свободным ответом

[2.2.1.1] Заполнение тестов

[2.2.1.2] Выставление отметки

[2.2.2] Тесты с выбором варианта ответа

[2.3] Оформление теста

[2.3.1] 3.1 Кнопки перехода

[2.3.2] 3.2 Формирование дизайна

[2.3.3] 3.3 Защита теста от несанкционированного изменения

[3] ПРИЛОЖЕНИЕ А. Пример теста со свободным ответом

[4] ПРИЛОЖЕНИЕ В. Пример теста с выбором ответа


Методические указания к лабораторной работе
СОЗДАНИЕ КОМПЬЮТЕРНЫХ ТЕСТОВ СРЕДСТВАМИ Excel

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

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

Перед работой в Microsoft Office Excel 2007 необходимо выполните следующие действия: кликните по кнопке | Параметры Excel |  Центр управления безопасностью | Параметры центра управления безопасностью | Параметры макросов | Включить все макросы | ОК | ОК | закрыть приложение Excel и открыть его заново.

Сохраните книгу Excel в выбранной вами папке (для Microsoft Office 2007 выберите тип файла Книга  Excel с поддержкой макросов)

  1.  ОБУЧАЮЩИЕ ТЕСТЫ

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

порядок работы при создании теста

  1.  Оформите на листе Лист 1 таблицу. Лист 1 назовите Вопросы.

.

В графу Вопрос введите вопросы. Ответы ученик будет записывать в графу Введите ответ. Установите размер шрифта для ячеек с вопросами и ответами равный 14 и назначьте  этим ячейкам другой цвет. Для примера вопросы можно брать из приложения В.

  1.  Перейдите на Лист2 | в ячейку А1 введите слово Верно | в ячейку А2 введите Не верно.
  2.  В столбец В введите номера вопросов, в столбец С в строку, соответствующую вопросу – верные ответы для данных вопросов.
  3.  Перейдите на Лист 1 и выделите ячейку С2.

Работа в Microsoft Office 2003

Работа в Microsoft Office 2007

Задайте команду Данные | Проверка | в поле Тип данных выберите Список | в поле Источник перечислите варианты ответа через точку с запятой

Лента Данные | группа Работа с данными | Проверка данных | Проверка данных | в поле Тип данных выберите Список | в поле Источник перечислите варианты ответа через точку с запятой

  1.  Кликните вкладку Сообщение для ввода и в поле Сообщение введите фразу Выберите правильный ответ из списка, ОК. Т.к. фраза Выберите правильный ответ из списка будет повторяться столько раз, сколько вопросов, то имеет смысл её скопировать в буфер (выделить и нажать Ctrl+C на клавиатуре), а затем , для следующих вопросов вставлять скопированную фразу из буфера (Ctrl+V).    
  2.  Заполните ячейки столбца С до конца списка вопросов (п.п.4, 5).
  3.  Рядом с введенным ответом, в столбце D, должна будет появиться запись Верно или Не верно в зависимости от выбранного ответа. Для этого поставьте курсор мышки на ячейку D2 | Кликните по в строке формул | Выберите категорию Логические | Если | ОК. Появится окно

|

кликните  по полю Лог_выражение | кликните по ячейке C2| наберите на клавиатуре знак равенства « | перейдите на Лист 2| кликните по С1 (верному ответу на первый вопрос) | перейдите  в поле Значение_если_истина | Перейдите на Лист2 | кликните по ячейке А1 | нажмите клавишу F4 | перейдите  в поле Значение_если_ложь | Перейдите на Лист2 | кликните по ячейке А2 | нажмите клавишу F4 | ОК. В результате должна сформироваться формула =ЕСЛИ(C2=Лист2!C1;Лист2!$A$1;Лист2!$A$2) | скопируйте формулу на все вопросы.

  1.  ПРОВЕРОЧНЫЕ ТЕСТЫ

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

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

  1.  Тесты со свободным ответом

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

  1.  Заполнение тестов

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

Оформите на листе Лист 1 таблицу. Лист 1 назовите Вопросы. Назовите лист Вопросы.

.

В графу Вопрос введите вопросы. Ответы ученик будет записывать в графу Введите ответ. Установите размер шрифта для ячеек с вопросами и ответами равный 14 и назначьте  этим ячейкам другой цвет.

  1.  Выставление отметки

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

  1.  Перейдите на Лист 2. Оформите таблицу. В столбце А пронумерованы вопросы.
  2.  В столбец С для каждого вопроса в соответствующие строки введите верный ответ.
  3.  В столбец В для каждого вопроса будет заноситься 1, если ответ верный и 0 – если не верный. Для этого в ячейку В2 внесите формулу:
    1.  Поставьте курсор на ячейку В2;
    2.  Кликните по в строке формул;
    3.  Выберите категорию Логические | Если | ОК. Появится окно.

  1.  Щелкните по полю Лог_выражение | перейдите на Лист1 «Вопросы» | щелкните по первой ячейке с ответом ученика (в нашем случае –  это С2) |  наберите знак равенства | перейдите на Лист2 | щёлкните по правильному ответу (в нашем случае это С2) | в поле Значение_если_истина введите 1 | в поле Значение_если_ложь – 0 | ОК | Скопируйте формулу на все ответы.
  2.  На Листе 2 начиная со второй строки оформите таблицу

.

Значения в столбце Е подсчитываются по формулам.

  1.  В Е2 подсчитайте количество правильных ответов, для этого используйте формулу СЧЁТЕСЛИ. Порядок создания формулы:

6.1. Поставьте курсор на ячейку Е2;

6.2.Кликните по в строке формул;

6.3.Выберите категорию Статистические | Счетесли | ОК. Появится окно.

6.4.Поставьте курсор в поле Диапазон и выделите блок ячеек с результатом в столбце В.

6.5.В поле Критерий внесите 1, кликните по ОК.

  1.  Аналогично подсчитайте количество неправильных ответов, только в поле Критерий внесите 0.
  2.  Перейдите на Лист3 (назовите его Результат), здесь будет формироваться результирующая оценка.
  3.  Продумайте критерии выставления оценки, например, для вопросов, приведённых в Приложении А, могут быть такие критерии

Количество верных ответов

Оценка

5

5

4

4

3

3

Меньше 3

2

  1.  Согласно выработанным критериям с использованием функции Если, опираясь на количество правильных ответов подсчитанных на Листе2, выставьте оценку. Алгоритм формирования команды для выставления оценки:
    1.  fx | Категория: Логические | ЕСЛИ | ОК
    2.  Курсор находится в поле Лог_выражение: кликните по ячейке с указанием количества правильных ответов и допишите >=5 (см.критерии оценки).
    3.  В поле Значение_если_истина введите 5  (см.критерии оценки).
    4.  Кликните по полю Значение_если_ложь.
    5.  Кликните по ЕСЛИ , находящийся перед строкой формул.
    6.  Курсор находится в поле Лог_выражение: кликните по ячейке с указанием количества правильных ответов и допишите >=4 (см.критерии оценки).
    7.  В поле Значение_если_истина введите 4  (см.критерии оценки).
    8.  Кликните по полю Значение_если_ложь.
    9.  Кликните по , находящийся перед строкой формул.
    10.  Курсор находится в поле Лог_выражение: кликните по ячейке с указанием количества правильных ответов и допишите >=3 (см.критерии оценки).
    11.  В поле Значение_если_истина введите 3  (см.критерии оценки).
    12.  Кликните по полю Значение_если_ложь и впишите 2 | ОК.
  2.  Опираясь на таблицу правильных и неправильных результатов, расположенную на Листе2, на Листе 3 постройте круговую диаграмму с указанием количества правильных значений и процентного соотношения правильных и неправильных ответов, т.е. при построении диаграммы установите Подписи данных: Значения и Доли.
  3.  Перейдите на лист Вопросы. В любую свободную ячейку под вопросами введите текст Ваша оценка. Поставьте курсор на ячейку с этим текстом и задайте команду Вставка (для работы в Microsoft Office 2007: кликните правой кнопкой по ячейке с текстом)| Гиперссылка | в появившемся окне кликните по Местом в документе | в поле Или выберите место в документе кликните по Результат | ОК.

  1.  Перейдите на Лист 3 (Результат). В любую свободную ячейку под диаграммой введите текст Назад к вопросам. Поставьте курсор на ячейку с этим текстом и задайте команду Вставка (для работы в Microsoft Office 2007: кликните правой кнопкой по тексту) | Гиперссылка | в появившемся окне кликните по Местом в документе | в поле Или выберите место в документе кликните по Вопросы |ОК.
  2.  Сформируйте дизайн теста (п.3.2 главы II).
  3.  Создайте защиту теста от несанкционированного изменения (п.3.3 главы II).
    1.  Тесты с выбором варианта ответа

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

  1.  Переименуйте Лист 1 на Тест.

  1.  Оформите таблицу
  2.  Выделите ячейку С2.

Работа в Microsoft Office 2003

Работа в Microsoft Office 2007

Задайте команду Данные | Проверка | в поле Тип данных выберите Список | в поле Источник перечислите варианты ответа через точку с запятой

Лента Данные | группа Работа с данными | Проверка данных | Проверка данных | в поле Тип данных выберите Список | в поле Источник перечислите различные варианты ответа для первого вопроса через точку с запятой

  1.  Кликните вкладку Сообщение для ввода и в поле Сообщение введите фразу Выберите правильный ответ из списка, ОК
  2.  Заполните ячейки столбца С до конца списка вопросов (п.п.3, 4).
  3.  Оформление теста

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

3.1 Кнопки перехода

Переходить от листа к листу можно не с помощью гиперссылок, а с помощью кнопок. Кнопки создаются до создания защиты листа.

Пусть Лист 1 будет титульный, на котором написана тема теста и кнопки Начать тестирование и Выход.  На Лист 2 поместим вопросы с полями для ответов и кнопки Результат, Очистить и Выход.  Лист 3 – рабочий, который впоследствии будет скрыт от учеников. Лист 4 содержит информацию о результатах выполнения теста и кнопки Вернуться к вопросам и Выход.

Кнопка  Начать тестирование содержит следующие команды:

  •  Переход на лист с вопросами;
  •  Выделение блока ячеек с ответами и нажатие кнопки <Delete>;
  •  Установка курсора на первую ячейку с ответом.

Кнопка  Выход на первом листе содержит с команду завершения работы

Кнопка  Выход на других листах содержит команды:

  •  Переход на первый лист;
  •  Завершение  работы.

Кнопка  Результат содержит команду перехода  на лист с результатами.

Кнопка  Очистить содержит команды:

  •  Выделение блока ячеек с ответами и нажатие кнопки <Delete>;
  •  Установка курсора на первую ячейку с ответом.

Кнопка  Вернуться к вопросам содержит команду перехода  на лист с вопросами.

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

Рисование кнопки

Работа в Microsoft Office 2003

Работа в Microsoft Office 2007

Если не включена панель рисования, то кликните по  | На панели рисования выберите Автофигуры | Основные фигуры | выберите фигуру для кнопки и нарисуйте кнопку | впишите Показать ошибки..

Лента Вставка  группа Иллюстрации | Фигуры | выберите форму для кнопки | нарисуйте кнопку | впишите Показать ошибки.

Создание макроса.

Работа в Microsoft Office 2003

Работа в Microsoft Office 2007

Сервис | Макрос | Начать запись | выполнить все необходимые команды | Сервис | Макрос | Остановить запись.

Лента Вид | Макросы | Запись макроса | введите имя макроса | ОК | выполнить все необходимые команды | Макросы | Остановить запись.

Все команды выполняются мышкой и кнопками на клавиатуре, исключением является только команда Завершение работы. Для того, чтобы её вставить необходимо перейти на лист с кнопкой | Сервис | Макрос | Макросы | Изменить | найти макрос по имени | перед строкой End Sub вставьте команду Application.Quit | в меню выберите View | Microsoft Excel.

Все команды выполняются мышкой и кнопками на клавиатуре, исключением является только команда Завершение работы. Для того, чтобы её вставить необходимо перейти на лист с кнопкой | лента Вид | Макросы | Макросы | найти макрос по имени | Изменить | перед строкой End Sub вставьте команду Application.Quit | в меню выберите View | Microsoft Excel.

На каждом листе есть кнопка Выход. Для каждой из этих кнопок должен быть свой макрос. Кнопка Выход на первом листе содержит лишь только команду  Application.Quit, поэтому создавая для неё макрос, начните запись, задайте имя макроса, а затем завершите запись, после чего вставьте команду Application.Quit.

Назначение макроса.

Кликните по нарисованной кнопке правой кнопкой мыши | Назначить макрос | выберите необходимый макрос  по имени | ОК.

3.2 Формирование дизайна

  1.  Скройте Рабочий лист:

Работа в Microsoft Office 2003

Работа в Microsoft Office 2007

Формат | Лист | Скрыть

Кликните левой кнопкой по ярлычку листа | Скрыть).

  1.  Для того чтобы учащиеся не отвлекались на стандартное оформление приложения Excel, можно убрать все параметры, характерные для этого приложения на  титульном листе и листах  Вопросы и Результаты:

Работа в Microsoft Office 2003

Работа в Microsoft Office 2007

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

Кликните по | Параметры Excel | Дополнительно | выберите группу Экран | уберите галочку Показать строку формул | в группе Показать параметры для следующей книги уберите все галочка | в группе Показать параметры для следующей листа уберите галочку Показать сетку 

Для создания фона:  Формат | Лист | Подложка | двойным кликом мышки выберите рисунок, который будет подложкой.

Для создания фона:  лента Разметка | Подложка | выберите рисунок, который будет подложкой.

Вид | Панели инструментов | если стоят галочки на Стандартная и Форматирование, то уберите.

Кликните по любой ленте правой кнопкой | Свернуть ленту.

3.3 Защита теста от несанкционированного изменения

Для того чтобы защитить вопросы от внесения изменений в них учеником   необходимо выполнить следующие команды:

Зайдите на лист с вопросами

Работа в Microsoft Office 2003

Работа в Microsoft Office 2007

Выделите весь лист (кликните мышкой по прямоугольнику в верхнем левом углу на пересечении областей с именами строк и столбцов) .

Выделите весь лист (кликните мышкой по прямоугольнику в верхнем левом углу на пересечении областей с именами строк и столбцов)

Формат | Ячейки | закладка Защита | уберите галочку в поле Защищаемая ячейка | ОК.

Лента Главная | группа Шрифт | кликните по кнопке вызова диалогового окна  | вкладка Защита | снимите флажок Защищаемая ячейка | ОК

Выделите ячейки с вопросами | Формат | Ячейки | закладка Защита | установите галочку в поле Защищаемая ячейка | ОК

Выделите все столбцы, кроме того, в который будут заноситься ответы | Лента Главная | группа Шрифт | кликните по кнопке вызова диалогового окна  | вкладка Защита | установите флажок Защищаемая ячейка | ОК

Сервис | Защита | Защитить лист | введите пароль|ОК|повторите пароль|ОК.

Лента Рецензирование | группа Изменение | Защитить лист | ОК.

ПРИЛОЖЕНИЕ А. Пример теста со свободным ответом

Тема: «Системы счисления»

Вопрос

Правильный ответ

Сколько значащих нулей в двоичной записи числа125.

1

Сколько единиц в двоичной записи числа 195?

4

Вычислите сумму двоичных чисел 1010101 и 1010011.

10101000

Вычислите в двоичной системе счисления значение выражения 1016+108∙102

11010

В системе счисления с некоторым основанием число 12 записывается в виде 110. Укажите это основание

4

ПРИЛОЖЕНИЕ В. Пример теста с выбором ответа

Тема: «Системы счисления»

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

1)5 2)6 3)25 4)50

2) Сколько существует различных последовательностей из символов «ноль» и «единица», длинной ровно в пять символов?

1)64 2)50 3)32 4)20

3) Получено сообщение, информационный объём которого равен 64 битам. Чему равен этот объём в байтах?

1)8 2)2 3)3 4)5

4) Количество значащих нулей в двоичной записи числа 126 равно

1)1 2)2 3)3 4)0

5) Как представлено десятичное число 25 в двоичной системе счисления?

1)1001 2)11001 3)10011 4)11010

№ вопроса

№ ответа

№ вопроса

№ ответа

№ вопроса

№ ответа

  1.  

2

3)

1

5)

2

  1.  

3

4)

1


 

А также другие работы, которые могут Вас заинтересовать

18219. Загальна характеристика та основи методики розвитку рухових здібностей 262 KB
  Змістовий модуль 3 Тема 6. Загальна характеристика та основи методики розвитку рухових здібностей. Поняття про рухові здібності та основні форми їх прояву. 1.1. Визначення поняття рухові здібності€ потенціальні€ та актуальні€ рухові здібності конди
18220. Теорія і методика фізичного виховання, як наукова та навчальна дисципліна. Система фізичного виховання 122 KB
  Змістовий модуль 1 Тема 1. Теорія і методика фізичного виховання як наукова та навчальна дисципліна. Система фізичного виховання. Під терміном теорія€ в науці і зокрема в ТМФВ розуміють систему основних ідей форму наукового знання що дає цілісне уявлення про законо...
18221. Урок – основна форма фізичного виховання молодших школярів 308.5 KB
  Змістовий модуль 4 Тема 8. Урок – основна форма фізичного виховання молодших школярів. Зміст навчального предмету Фізична культура€. 1.1. Аналіз програми Основи здоров’я і фізична культура€ Київ 2001 року програмовий матеріал години на проходження зміст к...
18222. Фізична культура в системі виховання дітей шкільного віку 106.5 KB
  Змістовий модуль 5 Тема 10. Фізична культура в системі виховання дітей шкільного віку. План. Соціальнопедагогічне значення фізичної культури дітей шкільного віку. 1.1. Мета завдання спрямованість фізичного виховання школярів. 1.2. Вікові особливості розвитк...
18223. Форми організації занять фізичними вправами в школі 174 KB
  Змістовий модуль 4 Тема 7. Форми організації занять фізичними вправами в школі. Форми фізичного виховання протягом навчального дня. 1.1. Гімнастика перед заняттями. 1.2. Фізкультурні хвилинки і фізкультурні паузи. 1.3. Години здоров’я. 1.4. Спортивна година в групах подо...
18224. Математичні терміни 154.5 KB
  Математичні терміни. Твердження судження думка в якій виділяється певний об'єкт встановлюються його властивості або зв'язки з іншими об'єктами. Ознака думка про властивість об'єктів. Ознака істотна – ознака без якої об'єкт існувати не може. Ознака неі...
18225. Поняття інформаційних системи, б/д - визначення, властивості, етапи розвитку, класифікація; інформаційна модель концептуального рівня 94.5 KB
  Поняття інформаційних системи б/д визначення властивості етапи розвитку класифікація; інформаційна модель концептуального рівня. 1.1. Поняття інформаційної системи. При самому загальному підході інформаційну систему ІС можна визначити як сукупність організац
18226. Реляційне числення. Мова Альфа 87.5 KB
  Реляційне числення. Мова €œАльфа€ Реляційне числення Кодда є одним із найважливіших наріжних каменів теорії реляційних моделей баз даних. У СУБД що існували до появи реляційного підходу було багато засобів для обробки даних і формулювання запитів. Основою для їх р
18227. Логічне проектування баз даних 106.5 KB
  Логічне проектування баз даних. Функціональна залежність. При логічному проектуванні баз даних вирішуються проблеми відображення об’єктів предметної області в абстрактні об’єкти моделі даних. Це відображення не повинно бути у протиріччі з семантикою предметної