新學期,老師陸續給學生們考試,產生了許多的小考成績,老師也問到了各種的成績採計方式,以下列舉使用 Excel 除了一般的平均(AVERAGE)之外,可能會用到的方式。

(1) 計算加權平均

儲存格L4:=SUMPRODUCT(B4:K4*$B$2:$K$2)/SUMPRODUCT($B$2:$K$2)

SUMPRODUCT(B4:K4*$B$2:$K$2):計算各次成績乘以加權數的和。

SUMPRODUCT($B$2:$K$2):計算各次加權數的和。

將以上二式相除,即為加權平均。複製儲存格L4,貼至儲存格L4:L19。

 

(2) 計算採計項平均

儲存格L4:=SUMPRODUCT((UPPER($B$1:$K$1)="V")*B4:K4)/SUMPRODUCT(
(UPPER($B$1:$K$1)="V")*1)

(UPPER($B$1:$K$1)="V"):取儲存格B1:K1,經 UPPER 函數改為大寫字,並判斷是不否為「V」,傳回 TRUE/FALSE 陣列。

(UPPER($B$1:$K$1)="V")*B4:K4:其中「*」運算,會將 TRUE/FALSE 陣列,轉換為 1/0 陣列。

SUMPRODUCT((UPPER($B$1:$K$1)="V")*1):計算儲存格B1:K1中的「V」個數。

只要將要採計的項目加上「V」,即會併入平均計算。

 

(3) 前6高分項平均

儲存格L4:{=AVERAGE(LARGE(B4:K4,ROW($1:$6)))}

這是陣列公式,輸入完要按 Ctrl+Shift+Enter 鍵,會自動產生「{}」。

ROW($1:$6):在陣列公式中會產生 ROW(1:1)=1、ROW(2:2)=2、...、ROW(6:6)=6。

LARGE(B4:K4,ROW($1:$6)):在陣列公式中取出儲存格B4:K4中前6個最大值。

如果想改變計算前幾名,只要更改ROW(1:N),其中使用絶對位址「$」的地方要注意!

【延伸閱讀】

 Excel-和成績、分數的相關文章

 Excel-列出指定次數最佳成績的平均

 Excel-利用Google表單讓學生依評分量表實施分組互評並計算分數

 Excel-依類別成績代碼轉換為分數(SUMPRODUCT)

 Google Classroom-使用評分量表對學生作業評分

 Google表單設計測驗的單選題、複選題、多選題

 Excel-設計Google表單測驗多選題並在Excel中自動帶入結果得到分數

 Excel-設計Google表單測驗多選題並以Excel計算分數

 Excel-取用Google表單的線上測驗結果自行計算分數(SUMPRODUCT)

 用Google表單來設計線上測驗卷並且評分,還可以獲得測驗結果的統計分析

 Google Classroom 的問題和測驗作業功能

 實施遠距教學多元評量讓同儕互評(Google表單+Excel)

arrow
arrow
    全站熱搜

    vincent 發表在 痞客邦 留言(2) 人氣()