Тест в excel как узнать ответы
При изучении с воспитанниками такой серьезной и «взрослой» программы как Microsoft Excel большое внимание уделяю созданию работ, которые повышали бы их компетентность, помогали социально адаптироваться в обществе, развивали логически, информационно и творчески, а проще говоря, пригодились бы им в жизни. К таким работам относятся тесты. В данной статье мне бы хотелось поделиться опытом разработки тестов с использованием функции ЕСЛИ.
Тесты могут быть контролирующими, развивающими, развлекательными и др. Они могут быть с оценкой по 5-ти или 10-ти бальной системе или со словесными комментариями. Тесты могут содержать ответы на вопросы или ответ нужно вводить. Вопросы к тесту предлагаю ребятам придумать самостоятельно на любую тему. Если в этом возникают проблемы, помогаю им. Кроме того, что тест должен функционировать правильно, он и выглядеть должен красиво, привлекательно и соответственно теме. Поэтому большое внимание уделяется дизайну оформления работы. И заключительным этапом является установка защиты на тест.
Хочется отметить, что с работой над такими тестами успешно справляются не только старшеклассники, но и ученики среднего звена. А поскольку воспитанникам дается почти полная свобода выбора вопросов и оформления, возможность протестировать тест друг другом и педагогом, то эта работа становится им очень интересной.
ТЕСТ 1 – тест, в котором к вопросу даны три ответа (Приложение 1).
Для этого в ячейку Е2 (рис. 1) вводится функция ЕСЛИ (категория Логические): =ЕСЛИ(D2=1;”Какой ты умный!”;””). Эта функция копируется в ячейки столбца Е, расположенные ниже, т.е. напротив каждого ответа, и изменяется только словесный комментарий.
Затем тест оформляется. Вставляется заголовок (можно использовать WordArt). Ставится подложка: меню Формат––>Лист––>Подложка. Заливаются каким-нибудь цветом ячейки теста: панель инструментов Форматирование––>кнопка Цвет заливки или меню Формат––>Ячейки––>вкладка Вид. Украшается картинками.
Скрываем ненужные нам теперь элементы листа – сетку, имена строк и столбцов, ярлычки листов, авторазбиение на страницы, горизонтальную и вертикальную полосы прокрутки, можно также не отображать область задач при запуске, строку формул, строку состояния: меню Сервис––>Параметры––>вкладка Вид.
Ставим на тест защиту. Выделяем все ячейки, куда должна вводиться 1, открываем меню Файл––>Ячейки––>вкладка Защита и снимаем защиту с ячеек и скрытие формулы. Затем выделяем ячейки, в которые вставлена функция, и наоборот ставим защиту на ячейки и скрываем формулы. И последний этап – защита листа: меню Сервис––>Защита––>Защитить лист. В диалоговом окне Защита листа дать команду Защитить лист и содержимое защищаемых ячеек; разрешить всем пользователем этого листа только выделение незаблокированных ячеек и можно ввести пароль для отключения защиты листа. При такой защите выделяются только ячейки, в которые вводится 1, остальные выделить и изменить невозможно. Тест готов к работе.
ТЕСТ 2 – тест, в котором к вопросу даны три ответа и ставится оценка (Приложение 2).
Располагать ячейки с вопросами и ответами можно по-разному. Например, так, как показано на рис. 2.
Оформляем тест – вставляем заголовок, подложку, картинки, заливаем ячейки и др. В таком варианте теста необходимо скрыть столбец D: из контекстного меню столбца D выбираем команду Скрыть. Скрываем ненужные элементы листа. Затем защищаем тест. Выделяем все ячейки, куда вводится номер ответа, и снимаем с них защиту. Выделяем ячейку с оценкой и наоборот ставим на нее защиту и скрываем формулу. Затем защищаем весь лист.
ТЕСТ 3 – тест, в котором ответ вводится в виде числа или слова (Приложение 3, Приложение 4).
Шкала оценивания:
Меньше 6 баллов – оценка «неудовлетворительно»
От 6 до 7 баллов – «удовлетворительно»
От 8 до 9 баллов – «хорошо»
От 10 до 11 баллов – «отлично»
Алгоритм создания теста со списком:
2. Оформляем заголовки столбцов для теста, набирая в соответствующих ячейках текст:В ячейке А1 - «№»
В ячейке В1 – «Вопрос»
Получаем таблицу (рис. 1)
4. В диапазон ячеек В2:В22 набираем тексты вопросов, соответственно их номерам в ячейках А2:А22
- ставим курсор в ячейку В3
- в строке команд выбираем "Данные"
- в группе команд "Работа с данными" выбираем "Проверка данных" (рис. 3)
В поле Логическое значение пишем условие сравнения правильного варианта ответа с ответом который набрал ученик.
B3=F2, где В3 - адрес ячейки для ввода ответа на первый вопрос, а F2 - адрес ячейки с правильным вариантом ответа.
В поле "Значение если истина " записываем 1
в поле "Значение если ложь" записываем 0
Аналогично заполняем формулами с использованием функции «Если» все ячейки для проставления баллов. Получаем (рис. 10), таблица в режиме отображения формул). В обычном режиме, после ввода формулы в ячейке появится число 0.
9. В ячейке J23 находим сумму полученных баллов с помощью функции СУММ(J2:J22)
10. В ячейке J24 записываем формулу, в которой, согласно полученной сумме баллов, вычисляется оценка за тест. Удобно сначала сформулировать содержание формулы в виде предложения, а потом записать его используя возможности функций Excel .
Итак, по шкале оценивания для этого теста, получаем (рис. 11):
Сумма баллов не может быть < 0, но, для того, чтобы перед прохождением теста ячейка для оценки отображала "0" первым условием проверки будет равенство суммы баллов нулю.
- если сумма баллов =0, то оценка "0", иначе
- если суммабаллов<6 , то оценка «2», иначе
- если сумма баллов>=6 и<=7 , то оценка «3», иначе
- если сумма баллов>= 8 и<= 9 , то оценка «4», иначе оценка «5»
Полужирным начертанием в предложении выделены логические условия.
Записываем полученное с помощью функции ЕСЛИ. Так как формула получается достаточно сложной, одна функция ЕСЛИ вложена в другую, то далее подробно описывается последовательность набора формулы.
- Ставим курсор в ячейку J24, вызываем окно Мастера функций, в Логических выбираем функцию ЕСЛИ
- В полученном окне в поле Логическое значение вводим первое условие J23=0 , в поле «Значение если истина» набираем 0 (оценка), переходим в поле «Значение если ложь» и снова вызываем окно Мастера функций нажав fx ( выбрать функцию).
- Выбираем функцию ЕСЛИ и вновь получаем окно для ввода. В поле «Логическое значение» набираем второе условие J23<6, в поле «Значение если истина» набираем 2 (оценка), переходим в поле «Значение если ложь» и снова вызываем окно Мастера функций нажав fx ( выбрать функцию).
- Выбираем функцию ЕСЛИ и вновь получаем окно для ввода. В поле «Логическое значение» набираем третье условие И(J23 >=6;J2 3 <=7). Внимание! «И» выносится за скобки. В поле «Значение если истина» набираем «3», переводим курсор в поле «Значение если ложь».
- Вновь вызываем окно функции ЕСЛИ и в поле «Логическое значение» набираем четвёртое условие проверки И(J23 >= 8 ;J2 3 <= 9 ). В поле «Значение если истина» ставим «4», в поле «Значение если ложь» набираем «5».
- Нажимаем ОК.
В результате в ячейке J24 должна появиться оценка, соответствующая сумме баллов.
В ячейку I23 можно набрать текст «Сумма баллов», а в I24 – «Оценка».
11. Отображение результатов для ученика
в ячейке С24 набираем «=» и делаем щелчок на ячейке с формулой суммирования баллов, в нашем примере это J23. Нажимаем Enter .
в ячейке В25 набираем текст «Ваша оценка»
в ячейке С25 набираем «=» и делаем щелчок на ячейке с формулой определения оценки, в нашем примере это J24. Нажимаем Enter .
Теперь, при прохождении теста ученик будет видеть количество набранных баллов и свою оценку.
12. Переименовываем Лист1 в Тест. Подробное описание действия шаг 7 в алгоритме на предыдущей странице.
14. Оформление внешнего вида теста. Используем инструменты "Границы", "Заливка". Подробное описание шаг 6 алгоритма на предыдущей странице.
Внимание! Для того, чтобы ответы учащегося не сохранились, надо закрыть файл с тестом без его сохранения.
В качестве примера на Яндекс - диск загружен файл с тестом, алгоритм создания которого представлен выше.
Рис. 10.5. Подготовка таблицы
Простыми словами: первый аргумент функции (лог_выражение) — логическое выражение, которое функция проверяет. Если выражение верно, то функция на выходе выдает второй аргумент (значение_если_ истина), если выражение неверно, то на выходе функции — третий аргумент (значение_если_ложь). Аргумент лог_выражение — обязательный. Например, В2=1 — логическое выражение; если значение в ячейке В2 равно 1, это выражение принимает значение ИСТИНА, в противном случае — значение ЛОЖЬ. В этом аргументе может использоваться любой оператор сравнения.
Операторы сравнения в Excel:
Аргумент значение_если_истина. Это значение, которое возвращается, если аргумент лог_выражение соответствует значению ИСТИНА.
Аргумент значение_если_ложь. Это значение, которое возвращается, если аргумент лог_выражение соответствует значению ЛОЖЬ.
На рис. 10.6 приведен пример использования этой функции.
Рис. 10.6. Пример использования функции ЕСЛИ
Во втором столбце — числа. Функция ЕСЛИ проверяет логическое выражение В1>3 (для последующих строчек берутся соответствующие значения ячеек в столбце В) и выдает значение «значение больше трех» в том случае, если число во втором столбце больше 3, и «значение не больше трех», если число во втором столбце меньше или равно 3. В качестве второго и третьего аргументов использован текст. Обратите внимание: текст обязательно берется в кавычки.
Выделите третью строку, перейдите на вкладку Главная, в группе Ячейки нажмите кнопку Формат и в появившемся меню выполните команду Скрыть или отобразить → Скрыть строки. Посмотрите, на рис. 10.7 третьей строки как не бывало. (Захотите отобразить строку обратно, используйте команду Отобразить строки.)
Рис. 10.7. Результаты
Зато на рисунке уже виден результат, который выдается тестируемому. Как я это сделала?
В Excel есть целый раздел логических функций. Они могут работать и в качестве отдельных функций, и в качестве аргументов друг друга. С их помощью вы сможете выстраивать самые сложные логические цепочки. Рассмотрим некоторые логические функции.
Функция И проверяет все логические значения, которые записаны у нее в аргументах. Если все они верны, то функция на выходе выдает значение ИСТИНА, если хотя бы одно выражение неверно, то на выходе ЛОЖЬ. Посмотрите пример на рис. 10.8.
Рис. 10.8. Пример использования функции И
Функцию И можно использовать как аргумент к функции ЕСЛИ. Например: =ЕСЛИ(И(1<A1; A1<100); A1;"Значение вне интервала") . Помните принцип «матрешки»? Разбираться начинаем с самой внутренней
функции.
И(1<A1; A1<100) — эта функция проверит два логических выражения. Если значение в ячейке А1 будет больше 1 и меньше 100, примет значение ИСТИНА, в противном случае — ЛОЖЬ. Если первый аргумент функции ЕСЛИ принимает значение ИСТИНА (в нашем случае это значит, что значение ИСТИНА принимает функция И, то есть значение в ячейке А1 больше 1 и меньше 100), то на выходе функции ЕСЛИ — второй аргумент. В данном случае число, которое находится в ячейке А1.
Если ЛОЖЬ (значение в ячейке А1 не попадает в интервал от 1 до 100), то на выходе — текст «Значение вне интервала». Посмотрите на рис. 10.9. Здесь в столбце A — исходные значения, а в столбце В — результат выполнения описанной функции.
Рис. 10.9. Вложенные функции
Подсчитайте число «Да» или «Нет» с помощью функции CountIf
Подсчитайте число «Да»
1. Выберите пустую ячейку, скопируйте и вставьте формулу. = СЧЁТЕСЛИ (B15: B21; «Да») в панель формул и нажмите клавишу Enter. Затем вы можете увидеть отображение результатов в выбранной ячейке. Смотрите скриншот:
Подсчитайте число «Нет»
1. Выберите пустую ячейку, скопируйте и вставьте формулу. = СЧЁТЕСЛИ (B15: B21; «Нет») в панель формул, а затем нажмите клавишу ВВОД.
Посчитайте другой ответ
Например, если есть ответ «нейтральность”Смешайте между Да и Нет, вы также можете подсчитать это с помощью функции CountIf.
Внимание: После копирования и вставки приведенных выше формул вам необходимо изменить диапазон ячеек в соответствии с вашим листом.
Быстро подсчитайте и выберите количество ячеек со значениями «Да» или «Нет» с помощью удивительного инструмента.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Щелкните Kutools > Выберите > Выбрать определенные ячейки.
2. в Выбрать определенные ячейки диалоговом окне выберите диапазон ячеек в Выберите ячейки в этом диапазоне раздел, выбрать Ячейка в разделе Тип выделения укажите Равно и Да (или Нет и другие) в Конкретный тип раздел и, наконец, щелкните Ok кнопка. Смотрите скриншот ниже.
3. Затем вы увидите, что общее количество ячеек Да отображается в Выбрать определенные ячейки диалоговое окно, а затем щелкните значок OK кнопку.
После нажатия OK Кнопка, все ячейки с Да в выбранном диапазоне ячеек выбираются автоматически.
Вы можете повторить вышеуказанные шаги, чтобы подсчитать ячейки с ответом Нет или другим ответом.
Если вы хотите получить 30-дневную бесплатную пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Читайте также: