學不完．教不停．用不盡

轉眼間，又來到的期末時刻，又是老師的夢魘諺！

有老師想要依多種作業繳交狀況和加權以計算總分，該如何處理？

如下圖中，學生作業繳交狀況有：未交、遲交、完成，三種狀況，分別對應分數：未交(0分)、遲交(70分)、完成(100分)。每個作業也有其加權的份量(如下圖)。

【設計與解析】

1. 將儲存格C3:L4，定義名稱：加權。

2. 設計公式

儲存格B5：=SUMPRODUCT(SWITCH(C5:L5,"完成",100,"遲交",70,"未交",0)*加權)/SUM(加權)

複製儲存格B5，貼至儲存格B5:B24。

(1) SWITCH(C5:L5,"完成",100,"遲交",70,"未交",0)

利用 SWITCH 函數傳回「未交、遲交、完成」所對應的分數「0、70、100」。

(2) SUMPRODUCT(第(1)式*加權)

計算各週第(1)式的傳回值乘以加權的分數加總。

(3) 第(2)式/SUM(加權)

把第(2)式的傳回值除以加權總和，即為加權後的實際分數。

如果改用 SUM 函數：

則公式改為：

儲存格B5：=SUM(SWITCH(C5:L5,"完成",100,"遲交",70,"未交",0)*加權/SUM(加權))

如果是Excel 2021 以前版本，則輸入完成要按 Ctrl+Shift+Enter 鍵。

儲存格B5：{=SUM(SWITCH(C5:L5,"完成",100,"遲交",70,"未交",0)*加權/SUM(加權))}

複製儲存格B5，貼至儲存格B5:B24。

假如，你也無法使用 SWITCH 函數，則可以改用 VLOOKUP 函數：

儲存格B5：=SUMPRODUCT(VLOOKUP(C5:L5,{"完成",100;"遲交",70;"未交",0},2,FALSE)*加權)/SUM(加權)

複製儲存格B5，貼至儲存格B5:B24。

【參考資料】

SWITCH 函數參考微軟提供的說明網頁：SWITCH 函數

SUMPRODUCT 函數參考微軟提供的說明網頁：SUMPRODUCT 函數

VLOOKUP 函數參考微軟提供的說明網頁：VLOOKUP 函數