學不完．教不停．用不盡

當你在 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,""),""))}