2011年1月7日 星期五

Excel-利用陣列求分組平均

在 Excel 中要根據一個資料表(如下圖左),要求得各個區間的最大值和平均,該如何處理呢?

如果以陣列公式來完成:

儲存格G3:{=MAX(IF(($B$2:$B$768>(ROW(1:1)-1)*200)*($B$2:$B$768<=ROW(1:1)*200),$C$2:$C$768,))}    [陣列公式,輸入完成按Ctrl+Shift+Enter鍵]

其意義如下:

($B$2:$B$768>(ROW(1:1)-1)*200)*($B$2:$B$768<=ROW(1:1)*200)

表示取得在B欄大於0(其中ROW(1:1)=1)且B欄小於等於200(其中ROW(2:2)=2)的值

其間:(…)*(…)的「*」為邏輯AND的意思。

公式=MAX(合於條件的B欄陣列對應C欄的陣列),取得合於條件的最大值。

複製儲存格G3至儲存格G3:G16,ROW(1:1)=1 –> ROW(2:2)=2 –> ROW(3:3)=3。

依此類推:

儲存格H3:{=MAX(IF(($B$2:$B$768>(ROW(1:1)-1)*200)*($B$2:$B$768<=ROW(1:1)*200),$D$2:$D$768,))}    [陣列公式,輸入完成按Ctrl+Shift+Enter鍵]

另外:

儲存格I3:{=AVERAGE(IF($B$2:$B$768>(ROW(1:1)-1)*200,IF($B$2:$B$768<=ROW(1:1)*200,$C$2:$C$768,""),""))}    [陣列公式,輸入完成按Ctrl+Shift+Enter鍵]

儲存格I3的條件判斷和儲存格G3的表示方式不同,但是意義是相同的,都可以求得AND的邏輯運算結果。

儲存格J3:{=AVERAGE(IF($B$2:$B$768>(ROW(1:1)-1)*200,IF($B$2:$B$768<=ROW(1:1)*200,$D$2:$D$768,""),""))}    [陣列公式,輸入完成按Ctrl+Shift+Enter鍵]

特別提醒,儲存格I3和儲存格J3在執行AVERAGE的陣列公式時,不可以將其中的「""」改成0或是空白,其計算的結果將會出錯。

因為儲存格0或是空白都會被在計算平均時列入平均,所以要將不符合條件的值以「""」表示。文字並不會被列入AVERAGE的平均計算。

沒有留言:

張貼留言

檢視其他文章

好康東東