2017年3月13日 星期一

Excel-取前幾項最大值平均(處理不足項)(SUMPRODUCT)

網友問到:如下圖的 Excel 資料表中,假設想要由 12 項中取前 8 個較優的成績來平均,但是有些資料項不足 8 個,該如何處理?
參考下圖,其中不足 8 項的部分,應該只採計有數值的項目來平均。
Excel-取前幾項最大值平均(處理不足項)(SUMPRODUCT)

【公式設計與解析】
(1) 公式:=IF(COUNT(A2:L2)<8 p="">
利用 COUNT 函數來判斷儲存格A2:L2中的數值個數,並且判斷數值個數若小於 8,則傳項目個數,若大於 8,則傳回 8。
(2) 公式:=SUMPRODUCT((A2:L2>=LARGE(A2:L2,公式(1)))*A2:L2)
利用 LARGE(A2:L2,公式(1)) 取得第 8 大的數值,若項目數小於 8,則最得這些項目的最小值。
((A2:L2>=LARGE(A2:L2,公式(1))) 用以判斷儲存格A2:L2中有那些是大於第 8 項者,傳回 TRUE/FALSE 陣列。
其中『*』運算子相當於執行邏輯 AND 運算,可以將 TRUE/FALSE 陣列轉換為 1/0 陣列。最後在 SUMPRODUCT 函數中取得這些項目的『總和』。
(3) 公式:=SUMPRODUCT((A2:L2>=LARGE(A2:L2,公式(1)))*1)
在 SUMPRODUCT 函數中取得這些項目的『項數』。
(4) 儲存格M2:=公式(1)/公式(2)
將取得的項目總和/項目個數,即可求得平均數。
複製儲存格M2,貼至儲存格M2:M18。

【完整公式】
儲存格M2:
=SUMPRODUCT((A2:L2>=LARGE(A2:L2,IF(COUNT(A2:L2)<8 br="" style="letter-spacing: 0px;">8)))*A2:L2)/SUMPRODUCT((A2:L2>=LARGE(A2:L2,IF(COUNT(A2:L2)<8 br="" style="letter-spacing: 0px;">(A2:L2),8)))*1)

沒有留言:

張貼留言

檢視其他文章

好康東東