在 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%沒有特別意義)
【延伸閱讀】
Excel-利用Google表單讓學生依評分量表實施分組互評並計算分數
Excel-依類別成績代碼轉換為分數(SUMPRODUCT)
Google Classroom-使用評分量表對學生作業評分
Excel-設計Google表單測驗多選題並在Excel中自動帶入結果得到分數
Excel-設計Google表單測驗多選題並以Excel計算分數
Excel-取用Google表單的線上測驗結果自行計算分數(SUMPRODUCT)
留言列表