2014年10月1日 星期三

Excel-各種成績計算(SUMPRODUCT,AVERAGE,LARGE,陣列公式)

新學期,老師陸續給學生們考試,產生了許多的小考成績,老師也問到了各種的成績採計方式,以下列舉使用 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),其中使用絶對位址「$」的地方要注意!

沒有留言:

張貼留言

好康東東