2018年7月2日 星期一

Excel-教師將平時標記的符號轉換為分數(SUBSTITUTE,SUMPRODUCT)

在教學現場,每次上課都要記錄學生學習狀況,為了方便、快速,也不想分分計較,所以會使用代碼來記錄。最後在學期末時,要如何將這些代碼轉換為分數?
例如,對照以下的代碼和分數來轉換:
代碼:A缺課 P玩手機 D遲到 C未關機 S睡覺 V實作佳 O實作可 X實作差
分數:A標記X P扣20 D扣10 C扣15 S扣10 V得85 O得75 X得60   
Excel-教師將平時標記的符號轉換為分數(SUBSTITUTE,SUMPRODUCT)

【公式設計與解析】
根據訂定的代碼和分數來轉換:
代碼:A缺課 P玩手機 D遲到 C未關機 S睡覺 V實作佳 O實作可 X實作差
分數:A標記X P扣20 D扣10 C扣15 S扣10 V得85 O得75 X得60
Excel-教師將平時標記的符號轉換為分數(SUBSTITUTE,SUMPRODUCT)
儲存格N2:=IF(B2="A","X",SUMPRODUCT((SUBSTITUTE(B2,{"V","O","X"},"")
<>B2)*{85,75,60})-SUMPRODUCT((SUBSTITUTE(B2,{"D","C","S","P"},"")<>B2)*
{10,15,10,20}))
複製儲存格N2,貼至儲存格X37。
(1) IF(B2="A","X", ...)
判斷如果儲存格內容為「A」,表示缺課,標記為「X」。
(2)SUMPRODUCT((SUBSTITUTE(B2,{"V","O","X"},"")<>B2)*{85,75,60})
如果儲存格內容不是為「A」,先計算得分項目:
利用:SUBSTITUTE(B2,{"V","O","X"},"") 來將儲存格B2的內容分別置換 V、O、X 為空白。其中 {"V","O","X"} 為定數陣列的寫法,同一個公式可以做三次動作。
再利用 SUBSTITUTE(B2,{"V","O","X"},"")<>B2 來判斷置換後的結果是否與原內容相符,傳回 TRUE/FALSE 。(如果相符,代表含有該字元,若不相符,表示不含有該字元。而且,傳回的結果必定只有一個 TRUE、二個 FALSE。)
於 SUMPRODUCT 函數執行 (SUBSTITUTE(B2,{"V","O","X"},"")<>B2)*{85,75,60})運算,結果會傳回 85、75、60 其中一個數值。
(3) SUMPRODUCT((SUBSTITUTE(B2,{"D","C","S","P"},"")<>B2)*{10,15,10,20}))
原理同第(2)式,計算扣分項目。

沒有留言:

張貼留言

檢視其他文章

好康東東