2011年4月6日 星期三

Excel-利用答案卡刷卡資料計算各班平均成績

在 Excel 中取得一個學生考試的讀卡檔案(如下圖),利用這些資料要來計算各班(201~214)的平均成績,和每個題目學生的作答鑑別情形,該如何設計呢?

(1) 計算各班平均分數

儲存格I2:{=AVERAGE(IF($B$2:$B$504=VALUE(RIGHT(H2,2)),$E$2:$E$504,""))}

陣列公式,輸入完成成後要按 Ctrl + Alt + Enter 鍵。複儲儲存格I2到儲存格I2:I15。

公式中的 VALUE(RIGHT(H2,2)) 為取得H欄的班級代碼之後2碼。

利用陣列公式將所有符合該班的分數找出來,再加以平均。

注意:以下兩種寫法均無法得到正確的解答(差異在公式最末之「""」,不可填入1或留空)。

{=AVERAGE(IF($B$2:$B$504=VALUE(RIGHT(H2,2)),$E$2:$E$504,1))}

{=AVERAGE(IF($B$2:$B$504=VALUE(RIGHT(H2,2)),$E$2:$E$504,))}

(2) 計算每一題ABCD答案分別作答人數

儲存格L2:{=SUM(IF(MID($F$2:$F$504,$K2,1)=L$1,1,0))/COUNT($A$2:$A$504)}

陣列公式,輸入完成成後要按 Ctrl + Alt + Enter 鍵。複製儲存格L2到儲存格L2:O41(共有40題)

因為每個學生的作答1~40題表示例如:BDBCDAACABCCBBBACBBACBABBCACBCACCADDDBDD

公式中MID($F$2:$F$504,$K2,1):找出學生答案($F$2:$F$504)中第 1~40 題的答案為 A 或 B 或 C 或 D者。

公式IF(MID($F$2:$F$504,$K2,1)=L$1,1,0)會根據L$1,L$2,L$3,L4取得A,B,C,D的陣列。

SUM(A,B,C,D的陣列)/COUNT($A$2:$A$504)可以計算A,B,C,D數量所佔的比例。

接著要找出每一個答案超過20%選答者,如果A,B,C,D超過一個以上達20%,則標示「V」

儲存格P1:=IF(COUNTIF(L2:O2,">20%")>1,"V","")

複製儲存格P1至儲存格P1:P41。

如果要將低於20%的數字自動淺色化,則要設定格式化規則:

註:標示「V」的用意是為了找出答案較不集中的題目。(其中20%沒有特別意義)

沒有留言:

張貼留言

檢視其他文章

好康東東