2015年1月18日 星期日

Excel-只挑最佳的幾個成績計算排名(LARGE,陣列公式)

網友又問到之前時常被問到的類似問題:如何在 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 筆成績來計算平均。而使用陣列公式時,不會有這個現象,請讀者自行斟酌來使用。

沒有留言:

張貼留言

檢視其他文章

好康東東