Как сделать тест в excel
Как создать тест в программе Excel
«Управление общеобразовательной организацией:
новые тенденции и современные технологии»
Свидетельство и скидка на обучение каждому участнику
Выбранный для просмотра документ Создание теста по физике.doc
«Создание тестов по физике
учитель физики и информатики
Открыть программу Microsoft Excel
В нужном месте открывшегося окна сделать заголовок «Тестовая программа по физике». Для этого можно заранее подготовить ячейку, путем объединения ячеек: выделить объединяемые ячейки, выполнить следующие команды: Формат – ячейки – выравнивание, проставить галочку напротив объединения ячеек и нажать ОК.
В объединенных ячейках записываете заголовок теста:
3. Переименуем «лист1» в «Тест», а «лист2» в «Результат».
4. На листе «Тест» в ячейках таблицы сделаем заголовки столбцов, где будут размещены вопросы, ответы, проверка.
Программа Excel позволяет создавать тесты со свободным ответом (когда обучаемому не дается варианта ответа) и с выборочным ответом (когда обучаемому предлагаются варианты ответов, из которых он выбирает правильный).
При создании теста со свободным ответом создается группа ячеек для ввода ответа.
При создании теста с выборочным ответом или теста на сопоставление выполняется следующая последовательность действий:
5. Наберём вопросы и разместим варианты ответов:
Оформим выбор: в ячейке В4 (напротив первого вопроса), выделим эту ячейку, нажмем меню Данные-Проверка
Из опций «Тип данных» нужно выбрать «список», после этого появится окно ввода «Источник». Нажать на кнопку 
, после чего выделить ячейки с ответами (в нашем случае это)
Теперь нажмем на кнопку 
и далее, на кнопку «ОК».
Мы увидим, что около ячейки В4 имеется кнопка для выбора. Нажав её, получим варианты.
Поместим в соседнюю ячейку С4 функцию проверки
где K4 –это адрес ячейки с правильным ответом на первый вопрос.
Смысл функции в следующем:
Если в тестируемой ячейке ответ не верный, то в ячейку, содержащую функцию, поместить пусто.
Иначе, если там не пусто, то, если ответ совпадает с верным, поместить «да».
Аналогично строим вопросы и ответы, копируем функцию и не забываем менять адрес ячейки с правильным ответом в функции.
Далее приводим страничку в надлежащий вид.
выделить заголовки столбцов, в ячейках которых имеются ответы и нажать Формат-Скрыть. Можно также скрыть формулы.
Чтобы посчитать число верных ответов, надо в данном случае ввести в ячейку С9 функцию СЧЕТЕСЛИ
Функция даст столько баллов, сколько раз слово «да» встретилось в ячейках с С4 по С8. Затем (Сервис) защитить лист.
Далее необходимо защитить лист.
с помощью меню Формат лишить защиты те ячейки, в которые вносится ответ и реакция, а также подсчет баллов:
выделить заголовки столбцов, в ячейках которых имеются ответы и нажать Формат-Скрыть. Можно также скрыть формулы. Затем (Сервис) защитить лист.
Можно оценить тест. Для этой цели нужно выбрать ячейку, в которой создать формулу для получения оценки.
Например, для нашего примера в ячейку С11 поместить формулу
А теперь проверьте, как работает программа.
Если вы ответили на все вопросы теста правильно, то окно выполненных действий выглядит так:
Однако придать тесту подобие строгости можно.
Перейдем на лист «Результат».
В нужном месте открывшегося окна сделать заголовок «Результаты тестов». Для этого можно заранее подготовить ячейку, путем объединения ячеек: выделить объединяемые ячейки, выполнить следующие команды: Формат – ячейки – выравнивание, проставить галочку напротив объединение ячеек и нажать ОК.
В ячейку А3 поместить «Количество баллов»и в А4 «Оценка».
Оформим выбор: в ячейке В3 набираем «=ЛИСТ1С9», а в В4 набираем «=ЛИСТ1С11».
Далее приводим лист в надлежащий вид.
выделить заголовки столбцов, в ячейках которых имеется проверка и нажать Формат-Скрыть. Можно также скрыть формулы.
выделить ячейки ЛИСТА1 «Твоя оценка» и «Количество баллов» и и нажать Формат-Скрыть.
Затем (Сервис) защитить ЛИСТ2.
А теперь проверьте, как работает программа.
Если вы ответили на все вопросы теста правильно, то окно выполненных действий выглядит так:
Курс повышения квалификации
Дистанционное обучение как современный формат преподавания
Курс повышения квалификации
Педагогическая деятельность в контексте профессионального стандарта педагога и ФГОС
Курс повышения квалификации
Авторская разработка онлайн-курса
Ищем педагогов в команду «Инфоурок»
Номер материала: ДВ-303316
Не нашли то, что искали?
Вам будут интересны эти курсы:
Оставьте свой комментарий
Авторизуйтесь, чтобы задавать вопросы.
Учителя о ЕГЭ: секреты успешной подготовки
Время чтения: 11 минут
Названы главные риски для детей на зимних каникулах
Время чтения: 3 минуты
Учителя о ЕГЭ: секреты успешной подготовки
Время чтения: 11 минут
Большинство родителей в России удовлетворены качеством образования в детсадах
Время чтения: 2 минуты
В Думу внесли законопроект об обязательном образовании для находящихся в СИЗО подростков
Время чтения: 2 минуты
В Минпросвещения рассказали о формате обучения школьников после праздников
Время чтения: 1 минута
Минздрав включил вакцинацию подростков от ковида в календарь прививок
Время чтения: 1 минута
Подарочные сертификаты
Ответственность за разрешение любых спорных моментов, касающихся самих материалов и их содержания, берут на себя пользователи, разместившие материал на сайте. Однако администрация сайта готова оказать всяческую поддержку в решении любых вопросов, связанных с работой и содержанием сайта. Если Вы заметили, что на данном сайте незаконно используются материалы, сообщите об этом администрации сайта через форму обратной связи.
Все материалы, размещенные на сайте, созданы авторами сайта либо размещены пользователями сайта и представлены на сайте исключительно для ознакомления. Авторские права на материалы принадлежат их законным авторам. Частичное или полное копирование материалов сайта без письменного разрешения администрации сайта запрещено! Мнение администрации может не совпадать с точкой зрения авторов.
Как сделать тест в excel
Меньше 6 баллов – оценка «неудовлетворительно»
От 6 до 7 баллов – «удовлетворительно»
От 8 до 9 баллов – «хорошо»
От 10 до 11 баллов – «отлично»
Алгоритм создания теста со списком:
В ячейке В1 – «Вопрос»
Максимальное количество предлагаемых вариантов ответов на вопрос равно 4, поэтому объединяем ячейки С1, D1, E1, F1 и набираем текст «Вариант ответа».
Получаем таблицу (рис. 1)
4. В диапазон ячеек В2:В22 набираем тексты вопросов, соответственно их номерам в ячейках А2:А22
5. Набираем варианты ответов:
В поле Логическое значение пишем условие сравнения правильного варианта ответа с ответом который набрал ученик.
В поле «Значение если истина » записываем 1
в поле «Значение если ложь» записываем 0
Аналогично заполняем формулами с использованием функции «Если» все ячейки для проставления баллов. Получаем (рис. 10), таблица в режиме отображения формул). В обычном режиме, после ввода формулы в ячейке появится число 0.
9. В ячейке J23 находим сумму полученных баллов с помощью функции СУММ(J2:J22)
Итак, по шкале оценивания для этого теста, получаем (рис. 11):
Сумма баллов не может быть
Полужирным начертанием в предложении выделены логические условия.
Записываем полученное с помощью функции ЕСЛИ. Так как формула получается достаточно сложной, одна функция ЕСЛИ вложена в другую, то далее подробно описывается последовательность набора формулы.
В результате в ячейке J24 должна появиться оценка, соответствующая сумме баллов.
В ячейку I23 можно набрать текст «Сумма баллов», а в I24 – «Оценка».
11. Отображение результатов для ученика
в ячейке В24 набираем текст «Количество правильных ответов»
в ячейке В25 набираем текст «Ваша оценка»
Теперь, при прохождении теста ученик будет видеть количество набранных баллов и свою оценку.
12. Переименовываем Лист1 в Тест. Подробное описание действия шаг 7 в алгоритме на предыдущей странице.
13. Необходимо защитить Тест от редактирования и просмотра формул с правильными ответами со стороны тестируемого. Для этого можно на Тест выделить ячейки с вариантами правильных ответов и итоговыми формулами, в нашем примере это диапазон F1:J24, сделать правый щелчок мышью и выбрать скрыть или выбрать белый цвет шрифта.
14. Оформление внешнего вида теста. Используем инструменты «Границы», «Заливка». Подробное описание шаг 6 алгоритма на предыдущей странице.
Внимание! Для того, чтобы ответы учащегося не сохранились, надо закрыть файл с тестом без его сохранения.
Составление тестов с использованием логической функции ЕСЛИ в среде Microsoft Excel 2003
При изучении с воспитанниками такой серьезной и «взрослой» программы как Microsoft Excel большое внимание уделяю созданию работ, которые повышали бы их компетентность, помогали социально адаптироваться в обществе, развивали логически, информационно и творчески, а проще говоря, пригодились бы им в жизни. К таким работам относятся тесты. В данной статье мне бы хотелось поделиться опытом разработки тестов с использованием функции ЕСЛИ.
Тесты могут быть контролирующими, развивающими, развлекательными и др. Они могут быть с оценкой по 5-ти или 10-ти бальной системе или со словесными комментариями. Тесты могут содержать ответы на вопросы или ответ нужно вводить. Вопросы к тесту предлагаю ребятам придумать самостоятельно на любую тему. Если в этом возникают проблемы, помогаю им. Кроме того, что тест должен функционировать правильно, он и выглядеть должен красиво, привлекательно и соответственно теме. Поэтому большое внимание уделяется дизайну оформления работы. И заключительным этапом является установка защиты на тест.
Хочется отметить, что с работой над такими тестами успешно справляются не только старшеклассники, но и ученики среднего звена. А поскольку воспитанникам дается почти полная свобода выбора вопросов и оформления, возможность протестировать тест друг другом и педагогом, то эта работа становится им очень интересной.
ТЕСТ 1 – тест, в котором к вопросу даны три ответа (Приложение 1).
Каждый ответ в отдельной ячейке (рис. 1). Справа от ответов в соответствующие ячейки вводится символ 1 (или другой какой-нибудь символ по желанию ученика). В ячейках, расположенных еще правее, должен выводиться словесный результат в виде фразы соответствующей правильному или неправильному ответу.
Для этого в ячейку Е2 (рис. 1) вводится функция ЕСЛИ (категория Логические): =ЕСЛИ(D2=1;”Какой ты умный!”;””). Эта функция копируется в ячейки столбца Е, расположенные ниже, т.е. напротив каждого ответа, и изменяется только словесный комментарий.
Затем тест оформляется. Вставляется заголовок (можно использовать WordArt). Ставится подложка: меню Формат––>Лист––>Подложка. Заливаются каким-нибудь цветом ячейки теста: панель инструментов Форматирование––>кнопка Цвет заливки или меню Формат––>Ячейки––>вкладка Вид. Украшается картинками.
Скрываем ненужные нам теперь элементы листа – сетку, имена строк и столбцов, ярлычки листов, авторазбиение на страницы, горизонтальную и вертикальную полосы прокрутки, можно также не отображать область задач при запуске, строку формул, строку состояния: меню Сервис––>Параметры––>вкладка Вид.
Ставим на тест защиту. Выделяем все ячейки, куда должна вводиться 1, открываем меню Файл––>Ячейки––>вкладка Защита и снимаем защиту с ячеек и скрытие формулы. Затем выделяем ячейки, в которые вставлена функция, и наоборот ставим защиту на ячейки и скрываем формулы. И последний этап – защита листа: меню Сервис––>Защита––>Защитить лист. В диалоговом окне Защита листа дать команду Защитить лист и содержимое защищаемых ячеек; разрешить всем пользователем этого листа только выделение незаблокированных ячеек и можно ввести пароль для отключения защиты листа. При такой защите выделяются только ячейки, в которые вводится 1, остальные выделить и изменить невозможно. Тест готов к работе.
ТЕСТ 2 – тест, в котором к вопросу даны три ответа и ставится оценка (Приложение 2).
Располагать ячейки с вопросами и ответами можно по-разному. Например, так, как показано на рис. 2.
В ячейку D5 (рис. 2) вводится функция: =ЕСЛИ(С5=2;”Верно”;”Не верно”). Если в тесте 1 прошу ребят выводить результат в виде различных текстовых фраз, то в этом тесте – в виде всего лишь двух слов: Верно или Не верно (или Да, Нет или Правильно, Не правильно и др.). Эта функция копируется во все ячейки справа от ячеек с ответом и изменяется только номер правильного ответа. В ячейку С5 и в соответствующие ниже вводится номер правильного ответа. В конце теста подсчитывается количество верных ответов с помощью функции СЧЁТЕСЛИ (категория Статистические), т.е. в ячейке D83 (рис. 3) должно быть так: =СЧЁТЕСЛИ(D5:D80; ”Верно”).
Затем ниже теста в ячейку В83 (рис. 4) вводится текст ТВОЯ ОЦЕНКА, а в ячейку С83 – формула подсчета оценки: количество верных ответов умножить на 5, если по 5-ти бальной системе (если по 10-ти бальной системе, то умножается на 10) и разделить на количество вопросов. Т.е. в данном случае формула получается следующей: =D83*5/16. С помощью функции округления (категория Математические) округляем полученный результат до целого: =ОКРУГЛ(D83*5/16;0).
Для того чтобы оценка не появлялась сразу же после ответа на первый вопрос (в этом случае можно проконтролировать правильность ответа), используем такую хитрость – оценка должна появиться только после ввода ответа на последний вопрос, рассчитывая на то, что нормальный человек будет отвечать на тест с начала, т.е. с первого вопроса. В конечном итоге формула в ячейке С83 выглядит следующим образом: =ЕСЛИ(С80=””;””;(ОКРУГЛ(D83*5/16;0))).
Оформляем тест – вставляем заголовок, подложку, картинки, заливаем ячейки и др. В таком варианте теста необходимо скрыть столбец D: из контекстного меню столбца D выбираем команду Скрыть. Скрываем ненужные элементы листа. Затем защищаем тест. Выделяем все ячейки, куда вводится номер ответа, и снимаем с них защиту. Выделяем ячейку с оценкой и наоборот ставим на нее защиту и скрываем формулу. Затем защищаем весь лист.
ТЕСТ 3 – тест, в котором ответ вводится в виде числа или слова (Приложение 3, Приложение 4).
Если такой тест делать без оценки (рис. 5), то в ячейках столбца D вводится формула с использованием вложения одной функции ЕСЛИ в другую функцию ЕСЛИ: =ЕСЛИ(С2=”;”;ЕСЛИ(С2=7;”Верно”;”Не верно”)). Делается это для того, чтобы, если в ячейке С2 ответ еще не введен, то в ячейке D2 было пусто. В противном случает в этой ячейке будет текст Не верно. В конце теста можно вывести количество верных и неверных ответов с помощью функции СЧЁТЕСЛИ (как в Тесте 2).
Если тест такого типа делать с оценкой, то можно не использовать вложение функции, т.е. формула будет выглядеть вот так: =ЕСЛИ(С2=7;”Верно”;”Не верно”). Столбец D впоследствии скрывается. Ниже теста выводится количество верных ответов с помощью функции СЧЁТЕСЛИ. А еще строчкой ниже подсчитывается оценка (как в Тесте 2). Строку с количеством верных ответов также необходимо скрыть. Тест оформить, скрыть ненужные элементы листа и поставить защиту.
Если ответом на вопрос является не число, а слово, то в функцию это слово вставляется в кавычках, например, =ЕСЛИ(С10=”компьютер”; ”Верно”;”Не верно”).
Создание интерактивного теста в Excel
«Управление общеобразовательной организацией:
новые тенденции и современные технологии»
Свидетельство и скидка на обучение каждому участнику
Создание интерактивного теста в Excel
1 этап
1. Запустите программу MS Excel.
2. Выполните команду Сервис – Макрос – Безопасность. В открывшемся диалоговом окне Безопасность во вкладке Уровень безопасности установите Средняя.
3. В ячейку D3 введите запись ФИО, а в ячейку D4 – Класс.
2 этап
Программа Excel позволяет создавать тесты со свободным ответом (когда обучаемому не дается варианта ответа) и с выборочным ответом (когда обучаемому предлагаются варианты ответов, из которых он выбирает правильный).
• При создании теста со свободным ответом создается группа ячеек для ввода ответа.
• При создании теста с выборочным ответом или теста на сопоставление выполняется следующая последовательность действий:
1) 
Выбирается меню Данные.
2) В ниспадающем меню выбирается команда Проверка.
Рисунок 1
Рисунок 2
4) В окне Источник
перечисляются варианты ответов через точку с запятой.
Рисунок 3
Результатом выполнения операций будет список с выборочными ответами, из которых обучаемый должен будет выбрать один ответ.
Рисунок 4
4. Закрепим полученные знания из п.1. Введите в ячейку E4 списки классов, которые будут проходить тестирование.
Рисунок 5
5. Оформим название теста: Тест по музыке на тему «Музыкальные термины». В строке 6 оформите заголовки столбцов теста. В ячейки В7:В16 введите вопросы, а в ячейки С7:С16 введите ответы в виде списка с выборочными четырьмя ответами, среди которых один правильный. Лист 1 переименуйте Тест.
Рисунок 6
Создадим макрос, который очищает поля для возможности тестирования многократно и назначим макрос кнопке с названием Очистка.
4. Выполните команду Сервис – Макрос – Начать запись. Дайте имя макросу Очистка. Выделите все поля с ответами и нажмите клавишу delete. Также удалите фамилию ученика и класс.
5. Выполните команду Сервис – Макрос – Остановить запись.
Теперь нарисуем кнопку и назначим ей макрос Очистка.
8. Выполните команду Вид – Панели инструментов – Формы.
9. Найдите инструмент Кнопка, активизируйте его (щелкните на нем) и нарисуйте кнопку на листе, правее ответов (см. Рис.6).
10. Назначьте ей макрос Очистка.
3 этап
Для подведения итогов тестирования можно предусмотреть специальный лист, переименовав его в Результат, на котором будут подведены итоги ответов.
Создадим на листе ответов 5 макросов:
• Ваш ответ – ученик может увидеть свои ответы
• Результат – ученик может увидеть, на какие вопросы он ответил неверно.
• Верный ответ – ученик может увидеть правильные ответы.
• Оценка – ученик может увидеть свою оценку.
• Очистка – для возможности многократного тестирования.
12. В строки А2 и А3 введите записи ФИО и Класс соответственно.
13. Скопируйте с первого листа номера вопросов и сами вопросы в столбцы А6:А15 и В6:В15.
14. Введите остальные заголовки таблицы, согласно рисунку (Ваш ответ, Результат, Верный ответ).
Рисунок 7 Создадим первый макрос – Ваш ответ.
Перед созданием макросов на втором листе курсор на листе ответов устанавливайте в какую-нибудь пустую ячейку, где нет записей, например, для нашего примера F9.
15. Выполните команду Сервис – Макрос – Начать запись. Дайте имя макросу Ваш_ответ.
Чтобы на этом листе отображались фамилия и имя ученика, создадим ссылку на соответствующую ячейку первого листа.
16. Установите курсор в ячейку В2, нажмите знак «=», перейдите на лист вопросов и щелкните мышью в ячейку Е4 (Петров Вася) и нажмите клавишу «Enter». Аналогично введите класс.
17. Таким же образом в листе ответов введите в ячейку С6 ответ с листа вопросов.
18. Скопируйте остальные варианты ответов: установите курсор в ячейку С6 и подведите его в правый нижний угол этой ячейки. Когда курсор примет вид «+», протяните вниз до ячейки С16.
19. Остановите макрос. Нарисуйте кнопку и назначьте ей макрос Ваш ответ.
Далее оформляем столбец Результат. Для этого используем логическую функцию «если».
20. Создайте второй макрос – Результат. На листе ответов установите курсор в ячейку D6.
21. Выполните команду Вставка – Функция (или кнопка f x рядом со строкой формул).
Выберите в категории Логические функцию Если.
22. Заполните поля согласно Рис 7. Текстовые ответы необходимо заключать в кавычки.
23. Аналогичным образом заполните ячейки D7:D10.
24. Остановите макрос. Нарисуйте кнопку и назначьте ей макрос Результат.
Рисунок 8 Далее оформляем столбец Верный ответ.
25. Создайте третий макрос – назовите его Ответ1. Установите курсор в ячейку Е6.
Введите в ячейки E6:E15 верные ответы к вопросам.
26. Остановите макрос. Нарисуйте кнопку и назначьте ей макрос Верный ответ.
Далее оформляем столбец Оценка. Для этого используем логическую функцию «если» и статистическую функцию «счетесли».
27. В строки В17 и В18 введите соответственно записи Количество верных ответов, Количество неверных ответов (см. Рис. 7).
28. Создайте четвертый макрос – назовите его Оценка.
29. Установите курсор в ячейку С17. Выполните команду Вставка – Функция ( или кнопка fx рядом со строкой формул). Выберите в категории Статистические функцию Счетесли.
30. Выделите на листе ответов диапазон D6:D15.
31. В строке критерий введите запись «верно» и нажмите кнопку ОК.
Рисунок 9
32. Аналогичным образом введите количество неверных ответов. Только в строке критерий введите запись «неверно».
Для выставления оценки используем функцию «если». Критерии оценивания: