網友問到:如下圖的 Excel 資料表中,假設想要由 12 項中取前 8 個較優的成績來平均,但是有些資料項不足 8 個,該如何處理?

參考下圖,其中不足 8 項的部分,應該只採計有數值的項目來平均。

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

 

【公式設計與解析】

(1) 公式:=IF(COUNT(A2:L2)<8,COUNT(A2:L2),8)

利用 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,COUNT(A2:L2),
8)))*A2:L2)/SUMPRODUCT((A2:L2>=LARGE(A2:L2,IF(COUNT(A2:L2)<8,COUNT
(A2:L2),8)))*1)

arrow
arrow
    文章標籤
    EXCEL SUMPRODUCT
    全站熱搜

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