學不完．教不停．用不盡

如何利用 Excel 來計算多重選擇的總得分？如下圖，假設每個題目的答案由 A, B, C, D 所組成，答案可能是其中的 1 ~ 4 個所組成。

其得分的標準：(可能和實際計分方式不一樣，以下標準僅供練習公式對照之用。)

●該選的選項也有選：+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 函數運算先理解。