有時候老師取得一次考試的答案，如何在 Excel 中根據標準答案，快速計算總分？以下圖為例，共有 25 個題目的答案，每一題答對得 4 分。
(1) 直向資料
檢視下圖中的學生答案和標準答案，經比對後可見有 6 題的答案不一致，所以應得分 76 分。設計公式時只要利用 SUMPRODUCT 函數即可求得分數。
儲存格E2：=SUMPRODUCT((A2:A26=B2:B26)*4)
因為 SUMPRODUCT 函數的每個參數都是陣列組成，A2:A26=B2:B26 運算後會傳回 TRUE／FALSE 的陣列，公式中經由「*4」運算後，TRUE／FALSE 陣列會轉換為 1／0 陣列。透過 SUMPRODUCT 函數執行乘積和運算，即為所得。
(2) 橫向資料
如果你把陣列轉置(旋轉 90 度)，參考下圖，如何設計公式？
儲存格AB2：=SUMPRODUCT((B1:Z1=B2:Z2)*4)
(3) 多個填答資料
由以上二種做法可得，假如你讓學生以 Google 表單來作答，其結果會像 (2) 的呈現方式，所以很容易依據標準答案，計算出每個填答者的成績。參考下圖
儲存格AB2：=SUMPRODUCT((B2:Z2=$B$13:$Z$13)*4)
複製儲存格AB2，往下各列貼上。
文章標籤
全站熱搜
請問要如何讓錯誤的答案顯示紅字呢?謝謝您
先說聲謝謝, 對偶爾需要用到Excel做各種統計, 但有不是很熟練的我來說, 您的文章幫助非常多!!! 另外, 想再請教您, 若是複選題有倒扣的狀況下, 也有辦法這樣設計公式來計分嗎?
這篇是多重選擇的計分運算：http://isvincent.pixnet.net/blog/post/47754537 如果是複選題的倒扣（-1），則原公式改為： =SUMPRODUCT((A2:A26=B2:B26)*4)-SUMPRODUCT((A2:A26<>B2:B26)*1)
哇塞! 太厲害了!!! 叩謝版主!!!
謝謝你光臨我的網站。
從老師的文章中得到很多啟發，雖然對於Excel不像商業界需要那麼熟練，但是對於教學還是很有幫助的，這是我用google sheet寫的批次檔，可能我用的函數不夠精簡。只要有8題選題題，就會超過單格255字元，就不讓我繼續輸入條件了。請問老師有辦法簡化嗎?這是高中數學科選填題的計分方式，指定哪幾個欄位(有欄位$D$10,1,$E$10指定)為一組答案，只要有一個儲存格錯誤就算整組全錯，全部答對就算得滿分5分(有欄位$F$7指定)。 IF(SUMPRODUCT((OFFSET($H2,0,$D$10,1,$E$10-$D$10+1)=OFFSET($H$3,0,$D$10,1,$E$10-$D$10+1))*$F$7)=($E$10-$D$10+1)*$F$7,$F$7,0)
A1 為 8 C1 為 函數區 ，如 IF 或其他函數 C1內容當A1小於40 ， 呈現的數字會是14.808 當A1大於40，假設現在A1數字為45，則呈現的數字為14.745 請問C1的函數，要如何寫?
儲存格C1：=IF(A1<40,14.808,14.745)