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. 將學生答案改成「正確以1表示,錯誤以0表示」
  2. 將學生得分和試題答對人數作排序
  3. 畫出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啟用巨集才可以使用



裡頭有五個工作表,分別為
  • students:學生的答案
  • answer:標準答案
  • sp_table:學生問題分析表(自動產生)
  • CS:學生診斷分析圖(自動產生)
  • CP:試題診斷分析圖(自動產生)


先到students裡頭,把學生資料打上去,或是從讀卡後的資料貼過來。

第一橫列放的是題號,第一直行放的是學生座號,其他是學生答案




answer工作表放的是標準答案,在第一橫列放上標準答案,不需題號



接下來是使用巨集了


按下執行之後,就會開始產生各種圖表,如果資料很多,程式會跑比較久。





在sp_table工作表的這張圖意義是這樣的:



  • 最高分的學生在最上面,最低分的學生在最下面
  • 最多人答對的題目在最左邊,最少人答對的在最右邊
  • 紅色線是S線,綠色線是P線
    • S曲線
      • 是學生得分的累加分佈曲線,此曲線以左的範圍,代表學生大都答對了試題
      • 位置可以看出學生學習成就達成的程度
    • P曲線
      • 試題答對人數的累加分佈曲線,此曲線以上的範圍,代表試題大都被學生答對了
      • 位置可以看出班級學生達成與未達成教學目標的程度
    • S曲線以左的部份,或P曲線以上的部份,對整個S-P表所佔的比例,表示該次測驗的平均答對率



單就這張圖就可以看出很多有趣的事,例如17題是最簡單的題目,大家都答對,而第3題也很簡單,不過只有6號同學答錯。
理論上高分群的同學,相對來說簡單的題目應該都答對,可是像17號同學居然答錯了很多別人都答對的題目,這很可能就是粗心大意。


再來看的是CS工作表,這裡是學生診斷分析圖
根據學生的注意係數和得分百分比,可以把表現區分以下組別




  • A 學習穩定型學生:表示該學生學習狀況十分良好、穩定,這些學生在班上程度好、學習成就較高、考試表現亦屬穩定、正常。
  • A' 粗心大意型學生:表示該學生學習狀況稍欠穩定,能力好,但考試卻粗心大意,有搶當第一個交卷的衝動與習慣。
  • B 努力不足型學生:該學生學習狀況尚稱良好、穩定,努力程度不足,需再多用功。能力屬中上程度,
  • B'缺乏充分型學生:該學生學習準備不夠充分,會粗心犯錯,學習漸趨不穩定,努力也不夠。
  • C 學力不足型學生:該學生基本能力不足,學習不夠充分,努力亦不足,學習成就偏低。
  • C' 學習異常型學生:該學生學習極不穩定,具隨性讀書習慣,對考試內容沒充分準備,考試成績時好時壞。


以上圖來看,17號就是屬於成績不錯,但是會粗心大意的學生。

最後是CP工作表,這是試題診斷分析圖
根據試題的注意係數和答題百分比,區分以下組別





以這張圖來看,第4題和第6題的異質成份就特別值得注意