贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

廣告贊助

網友問到:如下圖的 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)

文章標籤
創作者介紹

學不完.教不停.用不盡

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


留言列表 (3)

發表留言
  • 訪客
  • 請問 要如何合併字串?
    如下圖所示
    100.11的A B C D有不同數值都且有分開
    可以像是最下面 合併成一矩陣嗎?
    因為數量太多 結合篩選無法達成困擾好久
    wwt0207gmail .com
    謝謝



    <img src="webkit-fake-url://de48c342-5df5-4690-b8e3-b2cdeb9774b7/imagepng">
  • 訪客
  • 請問 要如何合併字串?
    如下圖所示
    100.11的A B C D有不同數值都且有分開
    可以像是最下面 合併成一矩陣嗎?
    因為數量太多 結合篩選無法達成困擾好久
    wwt0207gmail .com
    謝謝



    <img src="webkit-fake-url://de48c342-5df5-4690-b8e3-b2cdeb9774b7/imagepng">
  • 訪客
  • 您好
    我是wwt0207gmail .com
    我怕截圖沒成功
    A. B. C. D. E
    1.1. 2. 5
    1.1. 7. 9
    1.1. 12
    1.2. 9. 3. S. I
    1.2. 5s


    想合併成下面

    A. B. C. D. E
    1.1. 2. 5 7 12 9
    1.2. 9. 3. S. 5s I


    謝謝







  • 請參考:http://isvincent.pixnet.net/blog/post/47115064

    vincent 於 2017/03/16 11:48 回覆

找更多相關文章與討論

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼