在教學現場，每次上課都要記錄學生學習狀況，為了方便、快速，也不想分分計較，所以會使用代碼來記錄。最後在學期末時，要如何將這些代碼轉換為分數？
例如，對照以下的代碼和分數來轉換：
代碼：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)式，計算扣分項目。
留言列表