2014年10月3日 星期五

Excel-各種成績計算(SUMPRODUCT,陣列)

網友根據:Excel-各種成績計算(SUMPRODUCT,AVERAGE,LARGE,陣列公式)這篇文章中所列公式,建議要避免使用陣列公式(不要使用 Ctrl+Shift+Enter 鍵)。而是以陣列常數直接做為參數。

(1) 前6高分項平均成績

儲存格L2:=AVERAGE(LARGE(B4:K4,{1,2,3,4,5,6}))

原公式為{=AVERAGE(LARGE(B4:K4,ROW($1:$6)))}

這兩個公式是異曲同工,結果會相同。但是如果你要使用的項目是{1,2,3, ... , 98,99,100}時,在陣列公式中使用 ROW($1:$100) 或許會較方便。

(2) 採計項次的平均成績

原公式:=SUMPRODUCT((UPPER($B$1:$K$1)="V")*B4:K4)/SUMPRODUCT(
(UPPER($B$1:$K$1)="V")*1)

改成

儲存格M2:=SUMPRODUCT(B4:K4,{0,1,0,0,1,0,1,1,1,1})/6

其中{0,1,0,0,1,0,1,1,1,1}陣列中,「1」代表有勾選,「0」代表沒有勾選。這樣可以減化公式長度,但也失去了方便性。原來只要在儲存格中輸入「V」,即代表要勾選,所以在新增/減少的操作上較為便利。否則,只要變動一次,就得修改公式一次。

使用 SUMPRODUCT 函數、使用{0,1,0,0,1,0,1,1,1,1}、在陣列公式中使用 ROW($1:$6) 等,其實都在使用陣列的觀念。要使用的公式,有些要建立第一個儲存格後複製到多個儲存格中使用,有些在第一次建立公式後,日後常要常異動公式中的儲存格,所以各種公式寫法完全依照需求所定。Excel 好玩又有用的地方就在這裡!

沒有留言:

張貼留言

檢視其他文章

好康東東