Создание электронного диагностического теста с использованием MS Excel

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

ТЕМА: Создание диагностического теста по психологии с использованием MS Excel

ЦЕЛЬ: формирование у будущих психологов-бакалавров уменияиспользовать информационно-коммуникационные технологии для диагностики психических процессов школьников

ЗАДАЧИ:дать общее представление о возможностях MS Excel по созданию электронных диагностических тестов; научить приемам создания электронных диагностических тестов

План занятия:

1. Закрепление знаний о возможностях MS Excel
2. Особенности создания электронных диагностических тестов c использованием MS Excel: анализ примера
3. Практическая работа

ХОД ЗАНЯТИЯ

Теоретическое описание

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

Вам понадобится умения и знания:

Переименовать лист. Каждый Рабочий лист имеет название, которое отображается на ярлычке листа: Для того, чтобы переименовать необходимо кликнуть правой кнопкой мыши по названию листа (например, Лист1) в меню выбрать переименовать и дать новое имя.

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

Создать список. Данные → Проверка … → Параметры → Тип данных: Список; Источник: Заполнить значениями, разделяя их знаком ;(точка с запятой)

Функция ЕСЛИ. ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)

лог_выражение – это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. значение_если_истина – это значение, которое возвращается, если лог_выражение равно ИСТИНА. Значение_если_ложь – это значение, которое возвращается, если лог_выражение равно ЛОЖЬ. значение_если_ложь может быть формулой или функцией.

Функция ЕСЛИ может быть вложенной, если в значение_если вновь будет функция ЕСЛИ.

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

Изменение размеров столбцов. Установить курсор между названием столбцов А и В (нумерацией строк 1 и 2) до появления знака: илинажать левую кнопку мыши и изменить размер столбеца (строки).

Перенос слов в ячейке. Правая кнопка мыши (п.к.м) по номеру строки 2 → Формат ячеек …→ Выравнивание: по горизонтали – по центру; по вертикали – по центру; Отображение: переносить по словам.

Создание электронного диагностического теста с использованием MS Excel

Для изучения приемов создания тестов в среде MS Excel воспользуемся следующим примером

Пример.Пусть необходимо спроектировать тест «Сделай мне подарок, и я скажу кто ты». Имеется следующий исходный текстовый материал:

Вопросы теста с вариантами ответов:

1. Вкладываете ли Вы в подарок особый смысл? (Варианты ответов: да, не всегда, нет)
2. Долго ли Вы раздумываете перед тем, как выбрать подарок? (Варианты ответов: да, иногда, нет)
3. Предпочитаете ли Вы дарить подарки по предварительной договорённости? (Варианты ответов: да, иногда, нет)
4. Считаете ли Вы, что подарок должен быть прежде всего практичным? (Варианты ответов: да, иногда, нет)
5. Придерживаетесь ли Вы мнения, что лучше преподнести один солидный подарок, чем несколько маленьких? (Варианты ответов: да, иногда, нет)
6. Заботитесь ли Вы о том, чтобы подарок обязательно напоминал о Вас? (Варианты ответов: да, не всегда, нет)
7. Считаете ли Вы, что качество и цена подарка – решающие моменты при его выборе? (Варианты ответов: да, в некоторых случаях, нет)
8. Думаете ли Вы, что подарки обязывают? (Варианты ответов: да, зависит от обстоятельств, нет)
9. Случается ли Вам выбирать подарок с таким расчетом, чтобы он пригодился и Вам? (Варианты ответов: да, иногда, нет)
10. Согласны ли вы с мнением, что небольшие подарки поддерживают дружбу, а крупные обязывают? (Варианты ответов: да, не принимаю во внимание, нет)

Для данного примерного теста имеется следующее распределение баллов по вопросам в зависимости от ответа:

№ вопроса	Да	Нет	иной ответ

Интерпретация набранных баллов:

0–36 – Вы уважаете традиции, в Вашей жизни всё должно быть стабильным и постоянным. На Вас могут положиться и близкие, и друзья, и коллеги по работе;

37–44–Вы стараетесь быть лучше остальных. Боитесь проявлять свои слабости, поэтому на многих производите впечатление холодного человека. А это совсем не так;

более 44 – Ваша мечта – угадать сокровенные желания близких и друзей. Главное для Вас – помогать им во всём и понять их. Вы не ждёте благодарности или ответной услуги.

Рассмотрим алгоритм создания электронного варианта теста средствами MS Excel.

1.Запустите MS Excel: Пуск → Программы → Microsoft Office → Microsoft Excel

2.Подобный тест удобно сделать, используя три листа MS Excel. Первый лист будет предоставляться испытуемому, он будет содержать вопросы теста и возможные варианты ответов, переименуем его как тест.Второй лист испытуемый видеть не должен, он будет содержать обработку результатов и вычисление итоговой суммы баллов, переименуем его как обработка. Третий лист также не нужен тестируемому, он будет содержать все возможные интерпретации в зависимости от итоговой суммы баллов, переименуем его как интерпретация. В итоги вкладки листов должны выглядеть следующим образом:

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

!Не забудьте, что на этом листе будут находиться вопросы, варианты ответов и словесная интерпретация результата!

4.Перейдем к оформлению листа тест. Сначала введем название теста, для этого объединим ячейки А1:В1 (выделим ячейки А1:В1 и нажмем ) и запишем: Сделай мне подарок, и я скажу кто ты. Далее в ячейке А2 напишем: №, в ячейке В2 напишем: выберете вариант ответа, в ячейке С2: варианты ответов. Перейдем к заполнению столбцов таблицы № и выберете вариант ответа.Для этого в ячейках А3:А12 автозаполнением введем номера вопросов с 1 по 10, в ячейках В3:В12 введем вопросы теста. Далее перейдем к созданию Вариантов ответа. Для этого выделим ячейку С3, далее выберем в системном меню пункт Данные → Проверка….В появившемся окне в разделе Условие проверки, пункте Тип данных выберем список, в пункте Источник введем да; не всегда; нет. Окно Проверки после выполнения описанных действий должно выглядеть следующим образом:

После нажатия ОКВы увидите, что варианты ответов теперь можно выбирать. Выполним аналогичные действия с вариантами ответов ко всем вопросам теста. Далее объединим ячейки А13:В14 и напишем обработка.

!Сам результат пока отображаться не будет!

Лист Тест может выглядеть следующим образом:

5.Перейдем к оформлению листа обработка. Этот лист должен в итоге выглядеть следующим образом:

В ячейках А2:А11 автозаполнением введем номера вопросов, в ячейке В1 напишем да, в С1 – нет, в D1 – иной ответ. Затем используя таблицу распределения баллов из исходных материалов, введем количество баллов для каждого ответа по всем вопросам. Перейдем к созданию столбца Е.Он необходим для бального отражения ответов, данных испытуемым по каждому вопросу. Для такого отражения необходимо связать два листа тест и обработка.Покажем, как это сделать на первом ответе. Выделим ячейку Е2, в строке сделаем ссылку на ячейку С3 с листа тест, в результате получится =тест!C3, автозаполнением откопируем формулы на все ответы.

Соответственно, затем выделяем E3, а в строке формул записываем до E11.

Подсчитаем баллы в ячейке F2 по формуле:

=ЕСЛИ(E2=$B$1;B2;ЕСЛИ(E2=$C$1;C2;D2)). Формулу распространяем на последующие ячейки автозаполнением.

Для нахождения итоговой суммы баллов в ячейке F12 с помощью просуммируем ячейки F2:F11, появится формула =СУММ(F2:F11).

6.Перейдем к оформлению листа интерпретация.В ячейке А1 используя интерпретацию исходных материалов введем значение для итоговой суммы баллов от 0 – 36,в ячейке А2 – значение для итоговой суммы баллов от 37 – 44, ,в ячейке А3 – значение для итоговой суммы баллов более 44. Лист интерпретация будет иметь вид:

7.Заключительным шагом в создании теста является отображение описания результата на листе тест.Покажем, как это сделать. Выделите ячейку B14. В строке формул введем (без пробелов):=ЕСЛИ(обработка!F12<37;интерпретация!A1;ЕСЛИ(обработка!F12<45; интерпретация!A2; интерпретация!A3)).Таким образом, мы осуществляем связь между итоговой суммой баллов, полученной на листе обработка соответствующем описанием результата на листе интерпертация. Кроме того, все это отображается на листе тест.

8.Примерный тест готов, можно переходить к самопроверке.

9.Для того чтобы можно было использовать этот тест для тестирования нескольких людей, необходимо поставить кнопку Очистить– чтобы убрать варианты ответа и интерпретацию предыдущего тестируемого и кнопку Интерпретация – делает видимым интерпретацию после ответов на вопросы тестов. Для выполнения этих действий необходимо поставить кнопку и записать Макрос.Для создания Макросанеобходимо установить панель Вид → Панели инструментов →Visual Basic.

10. Установка кнопки Очистить. Выбрать Вид → Панели инструментов → Формы. На появившейся панели найти знак , подвести мышь к нужному месту на листе и растянуть кнопку. Появляется окно:

Задаем имя макроса Очистить. И нажать кнопку Записать …

1) выделить левой кнопкой мыши все варианты ответа и нажать Del;

2) поставить мышь в ячейку B14 выбрать цвет букв под цвет экрана ;

3) поставить курсор в ячейку С3;

4) На панели Visual Basicнажать кнопку .

11. Установка кнопки Интерпретация. На панели Формы выбрать знак , подвести мышь к нужному месту на листе и растянуть кнопку. Задаем имя макроса Интерпертация. И нажать кнопку Записать …

1) поставить мышь в ячейку B14 выбрать цвет букв контрастирующий с цветом экрана;

2) поставить курсор в ячейку С13;

3) На панели Visual Basicнажать кнопку .

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