有老師想要知道計算每次考試後前三名的平均成績,在 Excel 中,該用什麼公式?以下的例子就來找尋前三名的平均成績,並且算出多個名次為一組的各組平均成績。

參考下圖,要用人工方式找出名次分組再計算分組,是有點不容易!不過藉由陣列觀念可以輕鬆達到。

【準備工作】

選取儲存格B2:B21,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:分數。

【輸入公式】

(1) 計算前三名的平均

儲存格F1:=AVERAGE(LARGE(分數,{1,2,3}))

LARGE(分數,{1,2,3}):在 LARGE 函數中使用 {1,2,3} 陣列,表示要取成績最大的前三名。再透過 AVERAGE 函數求得此三者的平均。

其中使用常數陣列 {1,2,3},所以輸入完公式不需要按 Ctrl+Shift+Enter 鍵。

 

(2) 計算分次分組的各組平均

本例以 5 個名次為一組來分組。

儲存格F3:{=AVERAGE(LARGE(分數,ROW($1:$5)+(ROW(1:1)-1)*5))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

複製儲存格F3,貼至儲存格F3:F6。

ROW($1:$5)+(ROW(1:1)-1)*5:取得 1~5、6~10、11~15、16~20 的數字陣列。

透過 LARGE 函數取得各名次分組的分數陣列,再由 AVERAGE 函數將各組分數加以平均。

 

【延伸練習】

你也來練習求前 4 名的平均分數和以 4 個名次為一組,分組的平均分數之公式該如何處理?

【延伸閱讀】

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

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

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

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

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

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

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

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

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

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

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

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

    全站熱搜

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