Составление тестов с использованием логической функции ЕСЛИ в среде 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=”компьютер”; ”Верно”;”Не верно”).