2015年1月12日 星期一

Excel-比對答案自動計算分數(SUMPRODUCT)

有時候老師取得一次考試的答案,如何在 Excel 中根據標準答案,快速計算總分?以下圖為例,共有 25 個題目的答案,每一題答對得 4 分。

(1) 直向資料

檢視下圖中的學生答案和標準答案,經比對後可見有 6 題的答案不一致,所以應得分 76 分。設計公式時只要利用 SUMPRODUCT 函數即可求得分數。

儲存格E2:=SUMPRODUCT((A2:A26=B2:B26)*4)

因為 SUMPRODUCT 函數的每個參數都是陣列組成,A2:A26=B2:B26 運算後會傳回 TRUE/FALSE 的陣列,公式中經由「*4」運算後,TRUE/FALSE 陣列會轉換為 1/0 陣列。透過 SUMPRODUCT 函數執行乘積和運算,即為所得。

(2) 橫向資料

如果你把陣列轉置(旋轉 90 度),參考下圖,如何設計公式?

儲存格AB2:=SUMPRODUCT((B1:Z1=B2:Z2)*4)

(3) 多個填答資料

由以上二種做法可得,假如你讓學生以 Google 表單來作答,其結果會像 (2) 的呈現方式,所以很容易依據標準答案,計算出每個填答者的成績。參考下圖

儲存格AB2:=SUMPRODUCT((B2:Z2=$B$13:$Z$13)*4)

複製儲存格AB2,往下各列貼上。

沒有留言:

張貼留言

好康東東