2018年1月20日 星期六

Excel-比對答案自動計算分數(多重選擇)(SUMPRODUCT,SUBSTITUTE)

如何利用 Excel 來計算多重選擇的總得分?如下圖,假設每個題目的答案由 A, B, C, D 所組成,答案可能是其中的 1 ~ 4 個所組成。
Excel-比對答案自動計算分數(多重選擇)(SUMPRODUCT,SUBSTITUTE)
其得分的標準:
●該選的選項也有選:+1分
●該選的選項沒有選:+0分
●不該選的選項沒有選:+1分
●不該選的選項卻有選:+0分

【公式設計與解析】
1. 計算每個題目題分
儲存格D2:=SUMPRODUCT(((SUBSTITUTE(B2,{"A","B","C","D"},"")=B2)=
(SUBSTITUTE(C2,{"A","B","C","D"},"")=C2))*1)
複製儲存格D2,貼至儲存格D2:D26。
在 SUMPRODUCT 函數中,公式可以陣列形式來運算。{"A","B","C","D"} 表示由 A、B、C、D 字元組成的陣列。
(1) SUBSTITUTE(B2,{"A","B","C","D"},"")=B2
利用 SUBSTITUTE 函數將儲存格B2中的 A、B、C、D 分別置換成空字串(共會運算 4 筆)。再分別判斷 4 個傳回值是否和儲存格B2相同。例如,置換 A 後若傳回相同,代表儲存格B2中沒有 A 字元。

(2) SUBSTITUTE(C2,{"A","B","C","D"},"")=C2
利用 SUBSTITUTE 函數將儲存格C2中的 A、B、C、D 分別置換成空字串(共會運算 4 筆)。再分別判斷 4 個傳回值是否和儲存格C2相同。

(3) (第(1)式)=(第(2)式))*1
在 SUMPRODUCT 函數中,判斷第(1)式和第(2)式的結果是否相等,傳回 TRUE/FALSE 陣列。因為 TURE 代表得分(+1),FALSE 代表沒有得分(+0),所以利用公式中的『*1』運算,將 TRUE/FALSE 陣列轉換為 1/0 陣列。
最後經由 SUMPRODUCT 函數予以加總,即為該題得分。

2. 計算所有題目總得分
儲存格G2:=SUMPRODUCT(((SUBSTITUTE(B2:B26,{"A","B","C","D"},"")=B2:B26)
=(SUBSTITUTE(C2:C26,{"A","B","C","D"},"")=C2:C26))*1)
有了「1. 計算每個題目題分」的運算經驗,只要將公式中的儲存格B2置換成儲存格B2:B26,和將儲存格C2置換成儲存格C2:C26,即為所求。
由本例來看,善用 SUMPRODUCT 函數來處理陣列形式的運算,可以縮短公式的總量。或許對很多人而言,其實看不懂這樣的公式,建議由其他較簡單的 SUMPRODUCT 函數運算先理解。

沒有留言:

張貼留言

檢視其他文章

好康東東