2011年4月17日 星期日

Excel-計算前幾名的數字總和

當你在 Excel 中取得一個資料表(如下圖),如果想要根據其中幾名來計算其總和或是平均,該如何處理呢?你將用到陣列公式和藉助LARGE函數或RANK函數等。

假設共有50筆資料(儲存格B2:B51,以名稱:DATA取代),而以下全為陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

(1) 計算前5名的和

儲存格F2:{=SUM(LARGE(DATA,{1,2,3,4,5}))}

以LARGE函數,可以指定第1,2,3,4,5較大的數,所以以陣列{1,2,3,4,5}套入公式是非常明暸的做法。

但是如果所需的名次多,這個方法就較不方便了。

所以改用INDIRECT函數,例如:以INDIRECT("1:5")代入ROW函數,即可產生陣列{1,2,3,4,5}。

儲存格F2:{=SUM(LARGE(DATA,ROW(INDIRECT("1:5"))))}

因為前5名和前5個較大的數字,會有一些的差距,因為相同名次者可能會有重覆者。

假設已經在C欄中將名次算出,輸入公式:

儲存格F2:{=SUM(IF(C2:C51<=5,DATA,))}

此公式只將名次小於等於5者執行和的運算。

如果你不想藉助C欄的輔助,也可以直接使用B欄中的DATA資料運算。

儲存格F2:{=SUM(IF(RANK(DATA,DATA)<=5,DATA,))}

(2) 計算前20名的平均

和(1)原理相同,只要將和(SUM)的公式改為平均(AVERAGE),將小於等於5改成小於等於20即可。

儲存格F3:{=AVERAGE(LARGE(DATA,ROW(INDIRECT("1:20"))))}

儲存格F3:{=AVERAGE(IF(C2:C51<=20,DATA,""))}

特別提醒,公式中的「""」不要以「0」或是空白,否則在計算平均時會將其視為0,並列入平均,而產生錯誤。

儲存格F3:{=AVERAGE(IF(RANK(DATA,DATA)<=20,DATA,""))}

(3) 計算第31-40名的平均

儲存格F4:{=AVERAGE(LARGE(DATA,ROW(INDIRECT("31:40"))))}

儲存格F4:{=AVERAGE(IF(C2:C51>=31,IF(C2:C51<=40,DATA,""),""))}

公式中的IF(條件一,IF(條件二,XXX,XXX),XXX),此形式等同於(條件一 AND 條件二)的邏輯。

儲存格F4:{=AVERAGE(IF(RANK(DATA,DATA)>=31,IF(RANK(DATA,DATA)<=40,DATA,""),""))}

沒有留言:

張貼留言

好康東東