錦子老師

在職校教學歷程中，常常會碰到學生出勤、學習出現一些狀況，而為了方便記錄則以一些英文字母註記，換讓學生覺得老師公平，往往皆會讓學生檢視，但註記完成後到了學期結束時又要將其轉換為分數。

出勤狀況    字母                    分數                    學習                  字母                    分數

缺課            A                          X                         實做佳             X                          85

玩手機        B                          扣20分              實做可             Y                          75

未關機        C                          扣15分              實做差             Z                          65

遲到            D                          扣10分

睡覺            E                          扣10分

案例：光華商職資一1班文書處理課程平時成績統計，將B~L欄的標記轉成分數到N~X欄中，如下圖所示。

在昨天我們介紹過使用VLOOKUP函數來完成，但VLOOKUP函數必須要建立一個對照表表格，而使用SUMPRODUCT函數則不需要建立表格，但由於其是用陣列的方式來處理，公式變得複雜且高深了，對於一般初學者來說很難融入該公式的境界。

步驟1.在N2儲存格中輸入公式「=IF(B2="A","X",SUMPRODUCT((SUBSTITUTE(B2,{"X", "Y","Z"},"")<>B2)*{85,75,60})-SUMPRODUCT((SUBSTITUTE(B2,{"B","C","D","E"},"")<>B2)* {20,15,10,10}))」後，按Enter鍵完成輸入。

公式解析

IF(B2=”A”,”X”,….) 表示如果B2儲存格內容為A字元，則傳回X字元，否則繼續下面的判斷作業。

SUMPRODUCT((SUBSTITUTE(B2,{"X","Y","Z"},"")<>B2)*{85,75,60}) 表示如果B2儲存格內容不是A時，先計算其得分。

SUBSTITUTE(B2,{"X","Y","Z"},"") 將B2儲存格的內容分別置換 X、Y、Z 為空白。其中 {"X","Y","Z"} 為定數陣列的寫法，同一個公式可以做三次動作。

(SUBSTITUTE(B2,{"X","Y","Z"},"")<>B2) 這是用來判斷SUBSTITUTE函數執行後儲存格內容是否有變動。

SUMPRODUCT((SUBSTITUTE(B2,{"X", "Y","Z"},"")<>B2)*{85,75,60}) 這是用來判斷B2儲存中若有{"X", "Y","Z"}其中一個，那一個字串變成空白，則傳回值為TRUE，即數值1，對應到{85,75,60}抓取分數，因為只會有一個故其他二個皆變為FALSE，即為數值0。

SUMPRODUCT((SUBSTITUTE(B2,{"B","C","D","E"},"")<>B2)*{20,15,10,10})) 則是計算除得分外，要再被扣除的分數(學習)。

步驟2.將滑鼠指標移到N2儲存格右下角，直到遊標為「+」時，按住滑鼠左鍵不放向下拉至N26儲存格，再向右拉至X26儲存格，如下圖所示。

SUMPRODUCT函數解析：

SUMPRODUCT(array1,array2,…) 傳回多個陣列或儲存格範圍的各相對應元素乘積之總和。

array1,array2,…：為2~255個陣列或儲存格範圍，用以求乘積後再加總的陣列或儲存格範圍，所有陣列大小須相同。