贊助廠商

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

搜尋本部落格文章資料

網友又問到之前時常被問到的類似問題:如何在 Excel 的資料表中將每個人有 12 個成績中挑出最佳幾個來平均?

以下圖為例:每個人有 12 個成績,如果要挑出最佳的前 8 個成績來平均,該如何處理?

【設計公式】

儲存格N1:{=AVERAGE(LARGE(B2:M2,ROW($1:$8)))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。

複製儲存格N1,往下各列貼上。

ROW($1:$8):在陣列公式中代表陣列 { 1, 2, 3, 4, 5, 6, 7, 8 }。如果你要取幾個來平均,只要改變 8 的數值即可。

LARGE(B2:M2,ROW($1:$8)):在陣列公式中代表取出儲存格B2:M2中的前 8 大的數值。

最後再以 AVERAGE 函數加以平均,即為所求。

如果你不想使用陣列公式,而改用 SUMPRODUCT 函數,則公式如下:

儲存格N2:=SUMPRODUCT(((B2:M2)>=LARGE(B2:M2,8))*B2:M2)/
SUMPRODUCT(((B2:M2)>=LARGE(B2:M2,8))*1)

注意到這些成績中,有幾筆是第 8 大的成績有重覆的現象。如果使用這個公式,則會取到 9 筆成績來計算平均。而使用陣列公式時,不會有這個現象,請讀者自行斟酌來使用。

創作者介紹
創作者 vincent 的頭像
vincent

學不完.教不停.用不盡

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


留言列表 (3)

發表留言
  • 小蘋果
  • 大大~~
    真的很謝謝你~~
    感恩~
  • 悄悄話

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼