2015年9月26日 星期六

Excel-使用小計功能產生不同時期的各科平均及各科加權平均(SUMPRODUCT)

這是回應學校老師的一個練習範例。參考下圖,這是一個常見的成績表,如果想要計算每個區間中的各科平均該如何處理?如果計算各科平均時,想要依某個加權來計算總平均,又該如何處理?
先觀察上圖,每個區間的次數並不相同,在此要自動產生各個科目在這個區間的「平均」,並且計算各科的「加權平均」。(如下圖,還能以群組概念檢視資料)
參考以下的做法:
1. 選取儲存格A1:G20。
2. 選取[資料/大網]功能表中的「小計」。
3. 在[小計]對話框中設定:
(1) 分組小計欄位:區間(指第一次期中考、第二次期中考、期末考)
(2) 使用函數:平均值(相當於使用 AVERAGE 函數)
(3) 新增小計位置:國文、英文、數學、社會、自然(指這個個科目都要計算平均)
(4) 按下[確定]按鈕後,即可看到自動建立群組且依區間計算每個科目的平均值:
(5) 按一下第2層的「-」按鈕(此為摺疊,按下後會轉為「+」,此為展開按鈕)。
(6) 按著 Ctrl 鍵,分別選取各個平均值的各科平均分數。
(7) 設定各科平均的小數點位數為1位,並設定一個色彩。
(8) 計算加權平均
假設國文、英文、數學、社會、自然的加權分別為4、4、4、3、3。
儲存格H2:=SUMPRODUCT(C2:G2,{4,4,4,3,3})/SUM({4,4,4,3,3})
複製儲存格H2,貼至儲存格H2:H24。
最後,稍微調整一下格式設定,美化之後即可使用群組功能來檢視資料了。

【延伸學習】
(1)
公式儲存格H2:=SUMPRODUCT(C2:G2,{4,4,4,3,3})/SUM({4,4,4,3,3}),其中 {4,4,4,3,3} 是一種陣列的表示法。
(2)
注意喔!自動產生的小計(平均值),並不是使用你熟悉的 AVERAGE 函數,而是使用SUBTOTAL 函數。值得你進一步研究。

沒有留言:

張貼留言

檢視其他文章

好康東東