Как в Excel 2010 составить таблицу для проверки теста
Представьт себе ситуацию: вам поручили провести тестирование учеников. Учеников в классе более 20, вопросов в тесте — 10. Времени на подведение итогов у вас совсем немного. И тут вы применяете свои знания в Excel 2010 и составляете таблицу, в которой ученик вводит ответы на вопросы теста, а созданная вами программа в виде таблицы выдает ему оценку в зависимости от количества ошибок. А теперь опишем процесс создания такой таблицы.
Готовим таблицу (рис. 10.5). В первой строке — номера 10 вопросов. Тексты вопросов могут быть даны ниже, сделаны примечаниями и т. д. Как вам удобнее.
Рис. 10.5. Подготовка таблицы
Во вторую строку, которая называется «Опрос по теме № 1», ученики будут вводить свои варианты ответов. В третьей строке введены формулы, которые содержат правильные ответы теста. Допустим, в нашем тесте ученики выбирают из трех вариантов ответов. Не волнуйтесь, эту строку мы потом скроем и она не будет видна. Обратите внимание на формулу в ячейке B3 на рис. 10.5. Я использовала здесь логическую функцию из библиотеки Excel. В общем виде она выглядит так: ЕСЛИ(лог_выражение; значение_если_истина; значение_если_ложь). Функция ЕСЛИ возвращает одно значение, если указанное условие дает в результате значение ИСТИНА, и другое значение, если условие дает в результате значение ЛОЖЬ.
Простыми словами: первый аргумент функции (лог_выражение) — логическое выражение, которое функция проверяет. Если выражение верно, то функция на выходе выдает второй аргумент (значение_если_ истина), если выражение неверно, то на выходе функции — третий аргумент (значение_если_ложь). Аргумент лог_выражение — обязательный. Например, В2=1 — логическое выражение; если значение в ячейке В2 равно 1, это выражение принимает значение ИСТИНА, в противном случае — значение ЛОЖЬ. В этом аргументе может использоваться любой оператор сравнения.
Операторы сравнения в Excel:
- = — равно;
- > — больше;
- < — меньше;
- >= — больше или равно;
- < = — меньше или равно;
- <> — не равно.
Аргумент значение_если_истина. Это значение, которое возвращается, если аргумент лог_выражение соответствует значению ИСТИНА.
Аргумент значение_если_ложь. Это значение, которое возвращается, если аргумент лог_выражение соответствует значению ЛОЖЬ.
На рис. 10.6 приведен пример использования этой функции.
Рис. 10.6. Пример использования функции ЕСЛИ
Во втором столбце — числа. Функция ЕСЛИ проверяет логическое выражение В1>3 (для последующих строчек берутся соответствующие значения ячеек в столбце В) и выдает значение «значение больше трех» в том случае, если число во втором столбце больше 3, и «значение не больше трех», если число во втором столбце меньше или равно 3. В качестве второго и третьего аргументов использован текст. Обратите внимание: текст обязательно берется в кавычки.
Но вернемся к нашей формуле на рис. 10.5. Смысл ее в том, что если ученик отвечает правильно, то значение ячейки третьей строки приобретает значение 1, если неправильно, то 0. Если В2 = 1 (то есть правильный ответ на вопрос под номером 1), то ячейка В3 = 1, если ответ неправильный, то В3 = 0. На рис. 10.5 ученик дал 5 правильных ответов. В третьей строке 5 единиц, остальное — нули. В ячейке L3 — сумма диапазона B3:К3. Теперь нужно скрыть третью строку, чтобы отвечающий на вопросы теста не видел правильных ответов, которые заложены в формулу.
Выделите третью строку, перейдите на вкладку Главная, в группе Ячейки нажмите кнопку Формат и в появившемся меню выполните команду Скрыть или отобразить → Скрыть строки. Посмотрите, на рис. 10.7 третьей строки как не бывало. (Захотите отобразить строку обратно, используйте команду Отобразить строки.)
Рис. 10.7. Результаты
Зато на рисунке уже виден результат, который выдается тестируемому. Как я это сделала?
Ячейка B4 выводит результат по десятибалльной системе (значение в ячейке В4 равно значению в ячейке L3, которое мы скрыли от тестируемого). На рис. 10.7 результат равен 8, значит, из 10 вопросов ученик дал 8 правильных ответов. Ячейка В5 выдает результат по пятибалльной системе, и тут все сложнее. Подход такой:
- если правильных ответов 9 или 10, то оценка 5;
- если правильных ответов 8 или 7, то оценка 4;
- если правильных ответов от 6 до 4, то оценка 3;
- если правильных ответов от 3, то оценка 2;
- если правильных ответов 2 или 1, то оценка 1.
Я привожу этот пример, чтобы рассказать, что в качестве аргумента функции ЕСЛИ может быть любая логическая функция. И таких вложенных функций может быть не больше 64. Но я себе слабо представляю, как можно соорудить логическую конструкцию из 64 вложенных функций и не запутаться… Давайте хотя бы с тремя попробуем разобраться.
Смотрите на формулу на рис. 10.7. Разбор таких «матрешек» всегда нужно начинать с самой маленькой, то есть с самой внутренней функции, в нашем случае она выглядит так: =ЕСЛИ(L3>2;2;1). Эта функция проверяет количество правильных ответов (строка 3 у нас скрыта, ее можно увидеть на рис. 10.5). Если правильных ответов больше 2, то на выходе оценка 2, если не больше 2 (а не больше 2, значит, меньше или равно 2),
то 1.
Следующая «матрешка»: =ЕСЛИ(L3>3;3;ЕСЛИ(L3>2;2;1)). Если количество правильных ответов больше 3, то оценка 3, если не больше 3 (то есть меньше или равно 3), то проверяется первое условие и в зависимости от результата выставляется 2 или 1.
Третий уровень: =ЕСЛИ(L3>6;4;ЕСЛИ(L3>3;3;ЕСЛИ(L3>2;2;1))). Если количество правильных ответов больше 6, то оценка 4. Если меньше, то проверяются последовательно второе и первое условие и ставятся оценки 3, 2 или 1.
И последний уровень. =ЕСЛИ(L3>8;5;ЕСЛИ(L3>6;4;ЕСЛИ(L3>3;3;ЕСЛИ(L3>2;2;1)))) Для отличников — тех, кто даст больше 8 правильных ответов. Им — оценка 5. Если правильных ответов меньше 8, то программа проверяет последовательно все ЕСЛИ и выставляет 4, 3, 2 или 1.
В Excel есть целый раздел логических функций. Они могут работать и в качестве отдельных функций, и в качестве аргументов друг друга. С их помощью вы сможете выстраивать самые сложные логические цепочки. Рассмотрим некоторые логические функции.
И(логическое_значение1;логическое_значение2;…) — возвращает значение ИСТИНА, если в результате вычисления всех аргументов получается значение ИСТИНА; возвращает значение ЛОЖЬ, если в результате вычисления хотя бы одного из аргументов получается значение ЛОЖЬ. Аргумент логическое_значение1 — обязательный. Это первое проверяемое условие, вычисление которого дает значение ИСТИНА или ЛОЖЬ. Аргументы логическое_значение2 и далее — необязательные. Этих условий может быть не более 255.
Функция И проверяет все логические значения, которые записаны у нее в аргументах. Если все они верны, то функция на выходе выдает значение ИСТИНА, если хотя бы одно выражение неверно, то на выходе ЛОЖЬ. Посмотрите пример на рис. 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. Вложенные функции
ИЛИ(логическое_значение1;логическое_значение2;…) — возвращает значение ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА. Возвращает значение ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ. Аргумент логическое_значение1 — обязательный. Аргумент логическое значение2 и далее — необязательные. Функция работает так же, как и И, только значение ИСТИНА она принимает гораздо чаще. Ей для этого достаточно, чтобы хотя бы одно из поставленных условий соблюдалось.