阿簡生物筆記: 用Excel VBA製作學生問題分析表(S-P Table)

2008年5月16日

用Excel VBA製作學生問題分析表(S-P Table)

前一陣子梅子忙著教師專業評鑑的資料，想幫自己的考題作試題分析，後來學校老師給了她一份資料，是余民寧教授的《教育測驗與評量-成就測驗與教學評量》(心理出版社2002年出版）。其中有一個稱為學生問題分析表（S-P Tabel或S-P Chart)的試題分析方式，引起了我們的興趣，因為這種分析是「圖形化」的分析方法，和我以前學到的試題分析方法很不同。其實在余教授書後面就有附光碟，裡頭就有試題分析的程式，不過在底下我還是試試看用自己寫的EXCEL VBA來跑這個試題分析。

學生問題分析表是日本學者佐藤隆博(Takahiro Sato)在1970年代發明的試題分析方法，可以用來分析每位學生及每個試題的作答反應類型，有量化的指標來反應作答類型是否異常。很適合用在形成性評量的分析與診斷，最好是用在班級人數40-50人，試題數20-30題的測驗。

手工製作的方法如下（底下我會介紹如何用最簡單快速的方式來製作）

將學生答案改成「正確以1表示，錯誤以0表示」
將學生得分和試題答對人數作排序
畫出S曲線及P曲線

S曲線：

學生得分的累加分佈曲線，此曲線以左的範圍，代表學生大都答對了試題
S曲線的位置可以看出學生學習成就達成的程度

P曲線：試題答對人數的累加分佈曲線，此曲線以上的範圍，代表試題大都被學生答對了

P曲線的位置可以看出班級學生達成與未達成教學目標的程度

S曲線以左的部份，或P曲線以上的部份，對整個S-P表所佔的比例，表示該次測驗的平均答對率

此外，還有兩類係數的計算

第一個是差異係數D*，它看的是S和P兩曲線之分離面積，佔隨機情況下S-P曲線所圍部份之期望值的比值，值會介於0-1間。
意義： D*>0.5表示試題具有相當多異質成份
計算方式是：D*=C/(4NnP(1-P)DB(M)
    C：SP兩曲線包含1,0個數的總合
    N：學生數, n：試題數, M=(√Nn+0.5)取高斯值
    P：(每位學生得分總和)/nN

第二個是注意係數，包括學生注意係數CS和題目注意係數CP
這是S-P表資料中的實際反應組型與完美反應組型間的差異，占完美反應組型最大差異的比值
意義：注意係數愈小愈好

接下來要看如何用電腦幫我們作這些事。
檔案下載處：http://pancala.googlepages.com/sp_table.xls（這是第一版）
(2012.02.16更新第二版 sp_table2.0.xls，增加拆開答案、按鈕執行巨集和標示sp chart的0值）

(2020.08.21 更新第三版由於當時製作的excel版本僅支援256列，所以無法分析大樣本數的資料。所以改成excel 2016版本來製作，已經可以分析超過256個學生的資料，但是演算法是我早期寫的，沒有最佳化，所以分析的時間會非常長）

余教授的分析程式，在這裡可以下載

http://chensf.npue.edu.tw/cat/2012/

因為這個檔案含有巨集，所以必須允許EXCEL啟用巨集才可以使用