在上學期末時發現,很多網友查詢了擇優計算成績的做法,應該都一些辛苦的老師為一大堆的學生成績所苦。平時就很認真的記錄,期末要給學生最有利的成績。

通常,老師會在許多的成績中擇優或勾選或劃記不列入以計算平均。

Excel-如何擇優或勾選或劃記不列入以計算平均

【設計與解析】

1. 擇優N次計算平均成績(本例:10)

儲存格Q5 公式:=AVERAGE(LARGE(B5:P5,ROW(1:10)))

(1) ROW(1:10):產生 1~10 的陣列(1,2,3,…,10)

(2) LARGE(B5:P5,ROW(1:10)):以 LARGE 函數取出前10大者。

(3) 最後透過 AVERAGE 函數計算平均值。

 

2. 勾選 V 計算平均成績

儲存格Q14 公式:=AVERAGE(FILTER(B14:P14,$B$12:$P$12="V"))

(1) 在 FILLTER 函數中以條件:$B$12:$P$12="V" 進行篩選。

(2) 再透過 AVERAGE 函數計算平均值。

也可以改用 SUMPRODUCT 函數:

公式:=SUMPRODUCT(B14:P14*($B$12:$P$12="V"))/COUNTIF($B$12:$P$12,"V")

(1) SUMPRODUCT(B14:P14*($B$12:$P$12="V")):計算有 V 者的成績總和。

(2) COUNTIF($B$12:$P$12,"V"):計覺有 V 者的個數。

(3) 第(1)式/第(2)式即為平均。

 

3. 畫記 X 不列入計算平均成績

儲存格Q14 公式:=AVERAGE(FILTER(B24:P24,$B$22:$P$22<>"X"))

(1) 在 FILLTER 函數中以條件:$B$22:$P$22<>"X" 進行篩選。

(2) 再透過 AVERAGE 函數計算平均值。

 

【參考資料】

 FILTER 函數參考微軟提供的說明網頁:FILTER 函數
 SUMPRODUCT 函數參考微軟提供的說明網頁:SUMPRODUCT 函數

學不完.教不停.用不盡文章列表

arrow
arrow
    文章標籤
    Excel
    全站熱搜
    創作者介紹
    創作者 vincent 的頭像
    vincent

    學不完.教不停.用不盡

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