2012年1月18日 星期三

Excel-計算部分名次的平均(陣列+LARGE+ROW)

在 Excel 的一個處理成績的資料表中(如下圖左),如果要求取前三名分數的平均,即使已經知道每個分數的名次,也不見得是件容易的事。或許你可以試試「陣列公式」!不用先求名次也能計算。

【準備工作】

選取儲存格B1:B26,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」選項,建立「分數」之名稱。

【說明】

(1) 求取前3名的平均

儲存格E2:=AVERAGE(LARGE(分數,{1,2,3}))

先以 LARGE(分數,{1,2,3}) 找出最大的三個數,再以 AVERAGE 函數求平均。

(2) 求取後4名的平均

儲存格E3:=AVERAGE(SMALL(分數,{1,2,3,4}))

先以 SMALL(分數,{1,2,3,4}) 找出最小的四個數,再以 AVERAGE 函數求平均。

(3) 求取前10名的平均

儲存格E4:{=AVERAGE(LARGE(分數,ROW(1:10)))}

這是陣列公式,輸入完成要 Ctrl+Shift+Enter 鍵。

因為要求前十名,如果以(1)的方式,則公式中要輸入 {1,2,3,4,5,6,7,8,9,10},十分不方便,而且容易出錯。

所以改用陣列公式的方式,並且以 ROW(1:10) 來取代 {1,2,3,4,5,6,7,8,9,10}。

(4) 求取第11名至第15名的平均

儲存格E5:{=AVERAGE(LARGE(分數,ROW(11:15)))}

這是陣列公式,輸入完成要 Ctrl+Shift+Enter 鍵。

有了(3)的觀念,則可以套用 ROW(11:15) 來取代 {11,12,13,14,15}

沒有留言:

張貼留言

好康東東