Проведение статистического анализа качества заданий в среде MS EXCEL

Роман Дубинка

г. Барановичи, Республика Беларусь

xxxroman@bk.ru

Опубликовано в ж. Педагогические Измерения» №1 2008 г.  

Аннотация

В данной статье рассмотрен процесс расчета основных показателей качества теста посредством стандартных встроенных функций Microsoft Excel 2007.

Ключевые слова: статистический анализ, Microsoft Excel, мастер функций, коэффициент корреляции.

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

Существует множество специализированных программ, так называемых статистических пакетов, которые позволяют глубоко и многосторонне обрабатывать математические данные, примером таких программ является SPSS, STATISTICA, STATGRAРHICS. Однако в силу того, что большинство статистических пакетов относятся к специализированным программам и недоступны для обычного пользователя, появляется необходимость прибегать к помощи универсальных программ способных проводить сложные математические расчеты и статистический анализ данных.

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

Рассмотрим проведение статистического анализа на примере. Предположим, имеется 10 заданий, а тестирование проводилось в группе, состоящей из 20 испытуемых. Проведение эмпирического анализа начинается с занесения результатов в матрицу и с упорядочения данных. Для начала работы открываем MS Excel, при обычной установке Пуск/программы/Microsoft Office/Microsoft Office Excel 2007

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

Соответственно заполнение испытуемых, начинается с ячейки А2 вниз по столбцу, в ячейки (В1:К1) вводятся номера заданий в тестовой форме. Группа ячеек (В2:К21) заполняется результатами тестирования: нулям и единицами.

Посредством встроенной функции СУММ подсчитываем количество правильных ответов по каждому из заданий. Для реализации данной функции можно использовать два способа. Первый – когда формула напрямую вводится в нужную ячейку. Второй способ используется в случае обработки большого количества данных или слабого опыта работы. Он заключается в использовании мастера функций. Что бы запустить мастер фукнции, необходимо «кликнуть» по его значку на панели инструментов . В отрывшемся диалоговом окне из раскрывающегося списка Категория, необходимо выбрать Математические.

Из списка «Выберите функцию», указать СУММ, ниже появиться расшифровка функции: Суммирует аргументы. «Кликаем» Ок. На следующем шаге мастера функции выбираем суммируемые аргументы. Для этого помещаем курсор в поле Число1 и «кликаем» по значку . Выделяем область с баллами по первому заданию (B2:B21) и вновь возвращаем окно мастера функции к исходному размеру . Для завершения создания функции «кликаем» Ок. После этого растягиваем содержимое ячейки по строке до ячейки К22. Для этого выделяем ячейку с имеющейся функцией, и наводим курсор на правый нижний угол рамки, которая выделяет ячейку, когда курсор примет форму черного крестика кликаем левой клавишей мыши и не отпуская ее двигаем курсор вправо по строке до последней ячейки в которой должна быть функция.

Количество неправильных ответов определяем как разницу между общим числом испытуемых и числом уже найденных правильных ответов. Следующие две строки включают в себя доли правильных и неправильных ответов, для этого, как упоминалось выше, необходимо разделить поочередно значение ячеек (В22:К23) на 20 и поместить полученные цифры в следующие две строки.

Переведя формулу на язык MS Excel для 1-го задания она примет вид: «=B24*B25», это произведение помещается в ячейку B26 и растягивается по строке на все задания, т.е. до ячейки K26 включительно.

В ячейки таблицы MS Excel вносятся следующие формулы: L2 «=СУММ(B2:K2)»; M2 «=10-L2»; N2 «=L2/10»; O2 «=1-N2», а затем выделяется массив (L2:O2) с введенными в него формулами и растягивается на ячейки (L2:O21), тем самым пропадает необходимость вводить заново эти же формулы для каждого испытуемого.

Следующим элементом расчета является дисперсия баллов испытуемых и среднеквадратическое отклонение, эти показатели рассчитываются как для заданий, так и для испытуемых. В последнюю свободную строку вносим простейшую функцию умножения доли правильных и неправильных ответов: ячейке B26 присваивается значение «=B24*B25».

В современных технологиях адаптивного обучения и контроля используется другая мера трудности задания, равная ln qj/pj, а так же логит уровня знаний, равный ln pi/qi.

Если вручную вычисления данного показателя займет много времени, то с помощью встроенной функции MS Excel, данная процедура значительно упрощается. В ячейку B27 вставляем математическую функцию вычисления логарифма LN, и вводим выражение из которого будем находить логарифм, «=LN(B25/B24)». Выделив ячейки B26-B27, растягиваем их на все задания. Логит уровня знаний рассчитывается по формуле «=LN(N2/O2)», которая заносятся в ячейку P2, где N2 – доли правильных ответов первого испытуемого по всему тесту; O2 – доли неправильных ответов.

Теперь переходим к расчету ключевого показателя – коэффициента корреляции ответов на задание с ответами на сумму баллов, потому что задание в тестовой форме нельзя называть тестовым, если оно не коррелирует с суммой баллов по всему тесту. Этот показатель будет заноситься в строку 28. «Кликнув» левой клавишей мыши по кнопке добавления функции , на в кладке категория выбираем «статистические». В графе «Выберите функции», выбираем КОРЕЛЛ. Нажимаем на кнопку Ок.

В окне два поля: Массив1 и Массив2, в поля заносятся массив с ячейками данных между которыми устанавливается корреляционная зависимость. В качестве первого массива выбирается область ячеек с результатами испытуемых по первому заданию (B2:B21), в качестве второго массива область ячеек с суммой баллов по всему тесту. Для того что бы не пришлось набирать функцию для каждой ячейки, необходимо закрепить элементы Массива2, т.е. вместо (L2:L21) заносим в поле ($L$2:$L$21), внесенные изменения позволят свободно растянуть значение ячейки, потому что при проведении данной операции значения Массива1 будут изменяться автоматически, так же как и Массива2, но необходимо что бы изменялся только первый массив, поэтому с помощью символа $ закрепляется значение Массива2. В этом же окне можно просмотреть результат вычислений. Для завершения ввода функции нажимаем  Ок! Дублируем значение ячейки.

Попарная корреляция ответов на задания – рассчитывается аналогично коэффициенту корреляции ответов на задание с ответами на сумму баллов, разница заключается в том, что при расчете данного показателя устанавливается связь между баллами по заданию и суммой баллов по остальным заданиям. При расчете, в поле Массив2 вводим следующее (L$2:$L$21-B2:B21), т.е. произвели вычитание баллов задания из общей суммы баллов. Результаты вычислений заносятся в строку 29.

После всех манипуляций таблица примет следующий вид.

Использование Microsoft Excel, при проведении эмпирического анализа теста, позволяет:

1. Сократить затрачиваемое на вычисления время

2. Снизить вероятность допущения ошибки при вычислениях

3. Обрабатывать огромные массивы данных

4. Легко переносить и публиковать результаты вычислений, как в виде таблиц, так и в виде графиков и диаграмм.

5. Импортировать в табличный процессор результаты тестирования из специальных тестовых оболочек и программ.