Как обрабатывать результат теста в excel

Муниципальное бюджетное общеобразовательное учреждение

средняя школа №2 г.Лысково

МАСТЕР-КЛАСС

ДЛЯ ПЕДАГОГОВ-ПСИХОЛОГОВ Г.ЛЫСКОВО И

ЛЫСКОВСКОГО РАЙОНА

ПО ТЕМЕ

«Обработке результатов диагностики в программе Excel»

подготовила:

педагог-психолог Шутова И.Ю.

г.Лысково

15 мая 2018

Цель: передать участникам мастер-класса «инновационные продукты», полученные в результате творческой, экспериментальной деятельности педагога-психолога.

Задачи:

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

Материалы:

• Компьютеры;
• Бумага А4.

Ход мастер-класса

Здравствуйте уважаемые коллеги. Я рада приветствовать вас.

Наша работа будет подвигаться по данному плану:

— Ознакомление с возможностями программы Excel

— Обработка результатов теста в программе Excel

— Помощь участникам в возможности самостоятельно обработать результаты

— Анализ (рефлексия)

Упражнение 1.

«Улыбка»

Психолог: «Давайте подарим, друг другу радость и улыбку, ведь улыбка поднимает всем настроение. (Педагоги-психологи становятся в круг, берутся за руки, улыбаются друг другу).

Вступительная часть

Использование информационно – компьютерных технологий становится неотъемлемой частью образовательного процесса. Работа педагога-психолога не является здесь исключением. При этом, применяемые компьютерные технологии, органично дополняют традиционные методы, в том числе, расширяют возможности взаимодействия психолога с другими участниками образовательного процесса (педагогами, родителями и детьми).

Большие возможности для психолога в плане проведения тестирования и последующей обработки данных имеет программа Excel. Существуют программные продукты, например, «Excel на службе у психолога(автоматическая обработка тестов) v.09.08.24» Это программа автоматической обработки профессиональных психологических тестов в Excel. В нее включено более 40 профессиональных психодиагностических методик. Предназначена для психологов и всех интересующихся практической психодиагностикой. Выбор Excel обусловлен ее доступностью — она по умолчанию входит в стандартный пакет Microsoft Office, а также обладает широким спектром возможностей для работы с массивами данных.

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

На примере опросника школьной мотивации «Оценка школьной мотивации учащихся начальной школы» (методика Н.Г. Лускановой) покажу как быстро обработать результаты в обработчике.

Переходим к практической части.

Для этого нужно создать обработчик для данного опросника в Excel:

Подписываю столбцы и строки необходимых данных.

Чтобы подсчитать сумму нужно в строке форму ввести формулу подсчета:

Для этого нужно выбрать необходимые столбцы и строчки (правильность можно проверить). Клетки начинают выделяться цветом.

Далее вводим данные с опросника и выводим уровень мотивации:

Научиться правильным формулам достаточно легко, вставляя значки и данные, которые хотим получить.

Так мы узнаем уровень мотивации у каждого ученика, данные которого ввели в программу.

Благодаря возможностям программы мы можем подсчитать количество и вывести в процентах. Для этого тоже вводим в строку формул необходимую формулу с данными.

Можно получить проценты по каждому уровню мотивации от общего количества учащихся. Если ввести данные «нет», то можно посчитать процент от количества участников прошедших тестирование. То есть получим более точные данные.

Программа удобна в том, что можно готовые результаты можно вставлять в статистические справки для отчетности.

Совместная работа по созданию обработчика для личного использования в работе.

Анализ проведенной работы

Этот обработчик — готовый протокол для целого класса. Так можно обработать тест числовыми значениями, например, методику исследования словесно-логического мышления (методика разработана Э. Ф. Замбацявичене на основе теста структуры интеллекта Р. Амтхауэра).

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

Считаю, что мастер-класса достиг цели и вы научились работать в данной программе при обработке методик.

Упражнение 2.

И в завершении хочу предложить вам такое упражнение.

Положите обе руки к груди и послушайте как стучит сердце: «Тук-тук, тук-тук».  От этого становится тепло и приятно и вот это уже не помещается только в вас, поделитесь с коллегами, улыбнитесь им.

Покажите руки. В правую положите все то с чем вы сюда пришли – опыт, багаж знаний. А в левую положите все то, что нового получили на мастер-классе.

Громко хлопнем в ладоши и одновременно скажем: «Ура!»

Мне остается поблагодарить вас за работу и пожелать вам здоровья!

Приложение 1

Стимульный материал методики

1.Тебе нравится в школе?

-не очень

-нравится

-не нравится

2.Утром, когда ты просыпаешься, ты всегда с радостью идешь в школу или тебе часто хочется остаться дома?

-чаще хочется остаться дома

-бывает по-разному

-иду с радостью

3.Если бы учитель сказал, что завтра в школу не обязательно приходить всем ученикам, желающим можно остаться дома, ты бы пошел бы в школу или остался бы дома?

-не знаю

-остался бы дома

-пошел бы в школу

4.Тебе нравится, когда у вас отменяют какие-нибудь уроки?

-не нравится

-бывает по-разному

-нравится

5.Ты хотел бы, чтобы тебе не задавали домашних заданий?

-хотел бы

-не хотел бы

-не знаю

6.Ты хотел бы, чтобы в школе остались одни перемены?

-не знаю

-не хотел бы

-хотел бы

7.Ты часто рассказываешь о школе родителям?

-часто

-редко

-не рассказываю

8.Ты хотел бы, чтобы у тебя был менее строгий учитель?

-точно не знаю

-хотел бы

-не хотел бы

9.У тебя в классе много друзей?

-мало

-много

-нет друзей

10.Тебе нравятся твои одноклассники?

-да

-не очень

-нет

Обработка результатов

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

№ вопроса        Оценка за 1 ответ        Оценка за 2 ответ        Оценка за 3 ответ

1        1        3        0

2        0        1        3

3        1        0        3

4        3        1        0

5        0        3        1

6        1        3        1

7        3        1        0

8        1        0        3

9        1        3        0

10        3        1        0

Интерпретация результатов

1.        25-30 баллов (очень высокий уровень) — высокий уровень школьной мотивации, учебной активности. Такие дети отличаются наличием высоких познавательных мотивов, стремлением наиболее успешно выполнять все предъявляемые школой требования. Они очень четко следуют всем указаниям учителя, добросовестны и ответственны, сильно переживают, если получают неудовлетворительные оценки или замечания педагога.

2.        20-24 балла – (высокий уровень) хорошая школьная мотивация. Подобные показатели имеют большинство учащихся начальных классов, успешно справляющихся с учебной деятельностью. Подобный уровень мотивации является средней нормой.

3.        15 – 19 баллов – (средний уровень) положительное отношение к школе, но школа привлекает больше внеучебными сторонами. Такие дети достаточно благополучно чувствуют себя в школе, однако чаще ходят в школу, чтобы общаться с друзьями, с учителем. Им нравится ощущать себя учениками, иметь красивый портфель, ручки, тетради. Познавательные мотивы у них сформированы в меньшей степени и учебный процесс их мало привлекает.

4.        10 – 14 баллов – (низкий уровень) низкая школьная мотивация. Подобные школьники посещают школу неохотно, предпочитают пропускать занятия. На уроках часто занимаются посторонними делами, играми. Испытывают серьезные затруднения в учебной деятельности. Находятся в состоянии неустойчивой адаптации к школе.

5.        Ниже 10 баллов – (очень низкий уровень) негативное отношение к школе, школьная дезадаптация. Такие дети испытывают серьезные трудности в школе: они не справляются с учебной деятельностью, испытывают проблемы в общении с одноклассниками, во взаимоотношениях с учителем. Школа нередко воспринимается ими как враждебная среда, пребывание в которой для них невыносимо. Маленькие дети (5 – 6 лет) часто плачут, просятся домой. В других случаях ученики могут проявлять агрессивность, отказываться выполнить те или иные задания, следовать тем или иным нормам и правилам. Часто у подобных школьников отмечаются нарушения нервно – психического здоровья.

В
процессе обучения постоянно ощущается потребность в хорошо разработанных
методах измерения уровня обученности в самых различных областях знаний. Известно,
что профессиональное тестирование было начато еще в 2200 году до нашей эры,
когда служащие Китайского императора тестировались, чтобы определить их
пригодность для императорской службы. По некоторым оценкам в 1986 году по
крайней мере 800 профессий лицензировались в Соединенных Штатах на основании
тестирования (А.А. Захаров, А.В. Колпаков Современные математические методы
объективных педагогических измерений)

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

В
настоящее время существуют два теоретических подхода к созданию тестов:
классическая теория и современная теория IRT (Item Response Theory). Оба
подхода базируются на последующей статистической обработке так называемого
сырого балла (raw score), то есть балла, набранного в результате тестирования.
Только после проведения многократных статистических обработок можно говорить о
создании теста с устойчивыми параметрами качества (надежностью и валидностью).

Для
обработки данных, полученных на этапе тестирования, воспользуемся пакетом MS Office 2000 и электронными таблицами MS Excel.

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

Шаг 1. Формирование матрицы тестовых результатов.

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

Шаг 2. Преобразование
матрицы тестовых результатов.

На втором шаге из матрицы тестовых результатов устраняются строки и
столбцы, состоящие только из нулей или только из единиц. В приведенном выше
примере таких столбцов нет, а строк только две. Одна из них, нулевая строка
соответствует ответам одиннадцатого испытуемого, который не смог выполнить
правильно ни одного задания в тесте.

Рис. 1. Матрица результатов тестирования

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

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

Таким образом, на данном шаге необходимо удалить из матрицы данных 11 и
12 строки.

Шаг 3. Подсчет индивидуальных баллов испытуемых и количество
правильных ответов на каждое задание теста.

Индивидуальный балл испытуемого получается суммированием всех единиц,
полученных им за правильное выполнение задания теста. В Excel для суммирования данных по строке
можно воспользоваться кнопкой Автосумма  на панели
инструментов Стандартная. Для удобства полученные индивидуальные баллы
(Х_i)
приводятся в последнем столбце матрицы результатов (см. рис. 2).

Число правильных ответов на задания теста (Y_i) также получается
суммированием единиц, но уже расположенным по столбцам.(см. рис. 2)

Шаг 4. Упорядочение матрицы результатов.

Значения индивидуальных баллов необходимо
отсортировать по возрастанию, для этого в MS Excel:

1.   
выделим блок ячеек, содержащих номера
испытуемых, матрицу результатов и индивидуальные баллы. Начинать выделение
необходимо со столбца X
(индивидуальные баллы).

2.   
на панели инструментов Стандартная
нажимаем на кнопку Сортировка по возрастанию . Матрица результатов примет вид, изображенный на рис. 3.

Рис. 2. Матрица
с подсчетом итоговых сумм  

     Рис. 3 Упорядоченная матрица результатов

Шаг 5. Графическое представление данных.

Эмпирические результаты тестирования можно
представить в виде полигона частот, гистограммы, сглаженной кривой или графика.

Для
построения кривых упорядочим результаты эксперимента и подсчитаем частоту
получения баллов (см. рис. 4-6).

Рис. 4.
Несгруппированный ряд

Рис. 5.
Ранжированный ряд

Рис. 6. Частотное
распределение

Для расчета
рейтинга (ранга) каждого учащегося по индивидуальным балам необходимо применить
функцию РАНГ, которая возвращает ранг числа в списке чисел. Ранг числа –
это его величина относительно других значений в списке.

В MS Excel 2000 для вычисления
ранга используется функция

РАНГ (число;
ссылка; порядок), где

Число – адрес
на ячейку, для которой определяется ранг.

Ссылка —  ссылка на массив индивидуальных баллов
(выборка).

Порядок – число, определяющее способ упорядочения. Если порядок
равен 0 (нулю), или опущен, то Excel
определяет ранг числа так, как если бы ссылка была списком, отсортированным в
порядке убывания. Если порядок – любое ненулевое число, то Excel определяет ранг числа так, как
если бы ссылка была списком, отсортированным в порядке возрастания.

Примечание. Функция РАНГ присваивает повторяющимся числам
одинаковый ранг. При этом наличие повторяющихся чисел влияет на ранг
последующих чисел. Например, если в списке целых чисел дважды встречается число
10, имеющее ранг 5, число 11 будет иметь ранг 7 (ни одно из чисел не будет
иметь ранг 6).

По частотному
распределению можно построить гистограмму (см. рис.7).

Гистограмму
можно построить и по индивидуальным баллам (см. рис. 8).

Рис. 7.
Столбиковая гистограмма               

Рис. 8. Гистограмма распределения инд. баллов

При разработке тестов необходимо помнить о том, что кривая распределения
индивидуальных баллов, получаемых по репрезентативной выборке, является
следствием кривой распределения трудности заданий теста. Этот факт удачно
иллюстрируется на рис.9.

Рис. 9. Связь распределения
индивидуальных баллов и трудности заданий теста

Для первого распределения слева характерно явное смеще­ние в тесте
в сторону легких заданий, что, несомненно, приве­дет к появлению большого числа
завышенных баллов у ре­презентативной выборки учеников. Большая часть учеников
выполнит почти все задания теста.

Второй случай (слева) отражает существенное смещение в сторону
трудных заданий при разработке теста, что не мо­жет не сказаться на снижении
результатов учеников, поэтому распределение индивидуальных баллов имеет явно
выражен­ный всплеск вблизи начала горизонтальной оси. Основная часть учеников
выполнит незначительное число наиболее лег­ких заданий теста.

B третьем случае
задания теста обладают оптимальной трудностью, поскольку распределение имеет
вид нормальной кри­вой. Отсюда автоматически возникает нормальность распре­деления
индивидуальных баллов репрезентативной выборки учеников, что в свою очередь
позволяет считать полученное распределение устойчивым по отношению к
генеральной со­вокупности.

В профессионально разработанных нормативно-ориентированных тестах
типичным является результат, когда приблизительно 70%  учеников выполняют правильно от 30 до 70%
заданий теста. а наиболее часто встречается результат в 50%.

Шаг 6. Определение выборочных
характеристик результатов.

На данном этапе необходимо вычислить среднее
значение, моду, медиану, дисперсию, стандартное отклонение выборки, ассиметрию
и эксцесс (см. рис.10).

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

Функция СКОС MS Excel возвращает
ассиметрию распределения.

СКОС (число 1; число 2), где число1 – ссылка
на массив данных, содержащих индивидуальные баллы учеников.

При интерпретации полученного значения асимметриии 0,277
необходимо обратить внимание на то, что величина ассиметрии получилась
положительной и небольшой (см.  рис. 10,
11).

Рис. 10.
Описательные характеристики выборки

Рис. 11. Кривые распределения с
отрицательной, нулевой и положительной ассиметрией (слева направо)
соответственно.

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

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

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

С помощью эксцесса можно получить представление о том,
является ли функция распределения частот островершинной, средневершинной или
плоской.

Для расчета данного параметра применим функцию ЭКСЦЕСС
(число1; число2; …), где число1 – ссылка на массив данных,
содержащих индивидуальные баллы учеников.

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

Тест Фллипса хорошо знаком школьным психологам. Он широко используется, потому что хорошо работает. Используя Excel можно облегчить процесс обработки данных диагностики и написания итоговыхпротоколов.

Если вы будете использовать для этого наши несложные разработки, то для получения протоколов достаточно из бумажных бланков, заполненных при тестировании перенести на лист «Список» фамилии и имена испытуемых, а н алист «Сырые» их ответы. Ответы «Да» надо обозначать «1», а ответы «Нет» — «0».

После внесения данных всех испытуемых на листе «Протокол 1» вы получите готовый к распчатке протокол группового обследования. Если при этом вам понадобятся протоколы индивидуального обследования, то надо перейти на страницу «Протокол 2». Для получения протокола для кого-либо из внесенных в список испытуемых достаточно на листе «Протокол 2» в первой строке в ячейке, расположенной после слова «ПРОТОКОЛ», надо поставить номер, под которым этот испытуемый записан на листе «Список» и нажать клавишу Enter. Протокол готов к распчатке.

В оличие от стандартной интерпретации данных показатели теста, меньшие 25% в нашей разработке в протоколе индивидуального обследования обозначаются как «низкий уровень тревожности».

И еще, на всякий случай, на странице «опросник» размещен текст вопросов теста Филлипса с ключами.

Файл Excel, предназначенный для обработки теста можно скачать здесь.

Успешной вам работы!

Диагностическое направление деятельности – один из основных видов работы каждого психолога. Это затратный по времени процесс, так как необходимо подобрать правильную методику или набор методик, профессионально организовать проведение диагностики, обработать и увидеть то, что скрывается за цифрами и графиками и недоступно человеку без психологического образования. Перегрузки в работе, которые часто бывают в практике школьного психолога, сказываются на качестве работы, в процессе монотонной обработки концентрация внимания нарушается, что может привести к нежелательным ошибкам. Результатом этого могут стать неверные аналитические справки, недейственные рекомендации, в итоге — просто неудовлетворенность от своей работы. Тем не менее, у каждого из нас есть замечательный помощник, который может ощутимо помочь в нашей диагностической деятельности. Нужно только научиться находить к нему подход, а имя этому помощнику – компьютер.

Некоторые психологи с недоверием относятся к компьютерной обработке диагностик, так как привыкли работать на бумаге, а с компьютером неизвестно что с этими результатами произойдет, если нажать не ту кнопку. Кроме этого, что бы научиться обрабатывать на компьютере, нужно время, которое можно было бы потратить на ту же диагностику. Да, они правы, если контингент их работы ограничивается максимум несколькими десятками человек. Но, если счет идет на сотни, то тут никак не обойтись без компьютерных возможностей. В качестве примера возьмем обработку такой серьезной и зарекомендовавшей себя методики, как социометрия. Сколько бы вы обрабатывали социометрию из трех положительных и трех отрицательных выборов в стандартном классе из 26 человек? А если еще взять два критерия: учеба и дружба? Вспомните поиск фамилий, мелькание перед глазами одинаковых клеточек, подсчитывание взаимных выборов и т.д. Трудолюбивый психолог потратил бы не менее двух часов, да еще устал бы от однообразной работы. Компьютерная обработка в программе «MO Excel» позволяет тратить на обработку одного класса полчаса работы в обычном режиме, без лишней спешки. Оставшееся время можно использовать для того, что бы внимательно ознакомиться с результатами и подумать над дальнейшей работой с этим классом.

В данная статья поможет вам освоить быстрый и точный способ обработки социометрии. Но сначала вы должны знать следующее. Любая компьютерная программа – это всего лишь инструмент. И она никак вам не поможет, если вы не посвятите часть себя этому самому предмету. Не бойтесь экспериментировать. Вокруг вас много людей, которые неплохо разбираются в компьютерах, достаточно только захотеть научиться и попросить помощи. Зато, потратив время сейчас, вы получите просто огромную экономию времени и сил в будущем, и общий уровень вашей работы существенно поднимется.

Содержание статьи рассчитано на тех, кто часто использует социометрию в своей психологической практике и знаком с программой «MO Excel». Это очень популярный продукт компьютерной индустрии. Даже если вы ранее не работали в данной среде, вам пригодятся навыки, полученные в работе с данной информационной средой.  Содержание инструкции содержит следующие этапы:

1. Создание и расширение электронной базы данных социометрии.
2. Подготовка программы к обработке конкретного класса.
3. Ввод данных в программу обработки.
4. Знакомство с результатами обработки.
5. Правильный перенос данных из программы в базу.
6. Альтернативный способ сохранения данных.

Инструкция может показаться большой и сложной, но если у вас есть опыт работы с Excel, многое вам покажется знакомым и простым. Большинство инструкций снабжено подробными изображениями с указательными стрелочками.

Программа предназначена исключительно для подсчета выборов и отклонений и дает только статистическую информацию. Сама методика требует тщательного изучения перед использованием и интерпретацией. От вопроса, заданного респондентам, зависит результат выборов в группе. В данной программе идет не только количественный подсчет данных, но и вычисляются дополнительные индексы по специальным формулам. Описание этих индексов вы найдете в самой методике. Программа продолжает видоизменяться и совершенствоваться. За дополнительными разъяснениями и новыми версиями программы обращайтесь к автору: kynakbaev@gmail.com. В будущем планируется создать файл обработки социометрии в группе до 100 человек, представление данных в графическом виде и перевод работы с базы «MO Excel» на автономную среду.

Приложение 1

Приложение 2

Приложение 3