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

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

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

Excel-依多種作業繳交狀況和加權以計算總分

【設計與解析】

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

Excel-依多種作業繳交狀況和加權以計算總分

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 函數:

Excel-依多種作業繳交狀況和加權以計算總分

則公式改為:

儲存格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 函數:

Excel-依多種作業繳交狀況和加權以計算總分

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

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

 

【參考資料】

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

https://support.microsoft.com/zh-tw/office/switch-函數-47ab33c0-28ce-4530-8a45-d532ec4aa25e

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

https://support.microsoft.com/zh-tw/office/sumproduct-函數-16753e75-9f68-4874-94ac-4d2145a2fd2e

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

https://support.microsoft.com/zh-tw/office/vlookup-函數-0bbc8083-26fe-4963-8ab8-93a18ad188a1

學不完.教不停.用不盡文章列表

    vincent 發表在 痞客邦 留言(0) 人氣()