2016年6月15日 星期三

Excel-指定部分的欄位計算平均(SUMPRODUCT,COUNTIF)

又要接近學期末了,又有老師問到在 Excel 建立的學生平時考成績,如果只想採計某幾次來計算平均,該如何處理?
如下圖,在小考成績記錄表中有 8 次小考,如果只想採計其中 6 次(有『V』記號的欄位)來平均,要如何設計公式?
Excel-指定部分的欄位計算平均(SUMPRODUCT,COUNTIF)

【公式設計與解析】
儲存格J2:=SUMPRODUCT(B3:I3*($B$1:$I$1="V"))/COUNTIF($B$1:$I$1,"V")
(1) ($B$1:$I$1="V")
在儲存格陣列(B1:I1)中判斷內容是否為『V』,傳回 TRUE/FALSE 陣列。
(2) B3:I3*($B$1:$I$1="V")
將儲存格B3:I3乘以第(1)式傳回的 TRUE/FALSE 陣列,其中的『*』運算會將 TRUE/FALSE 陣列,轉換為 1/0 陣列。
(3) SUMPRODUCT(B3:I3*($B$1:$I$1="V"))
透過 SUMPRODUCT 函數計算『B3:I3』和『$B$1:$I$1="V"傳回值』的『乘積和』。
(4) COUNTIF($B$1:$I$1,"V")
利用 COUNTIF 函數計算在儲存格B1:I1中內容是『V』的個數。
(5) 將第(3)式除第(4)式,即可求得平均數。

沒有留言:

張貼留言

好康東東