學不完．教不停．用不盡

在教學現場，每次上課都要記錄學生學習狀況，為了方便、快速，也不想分分計較，所以會使用代碼來記錄。最後在學期末時，要如何將這些代碼轉換為分數？

例如，對照以下的代碼和分數來轉換：

代碼：A缺課　P玩手機　D遲到　C未關機　S睡覺　V實作佳　O實作可　X實作差

分數：A標記X　P扣20　D扣10　C扣15　S扣10　V得85　O得75　X得60　　　

【公式設計與解析】

根據訂定的代碼和分數來轉換：

代碼：A缺課　P玩手機　D遲到　C未關機　S睡覺　V實作佳　O實作可　X實作差

分數：A標記X　P扣20　D扣10　C扣15　S扣10　V得85　O得75　X得60

儲存格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」，表示缺課，標記為「Ｘ」。

(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)式，計算扣分項目。