學不完．教不停．用不盡

在 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的平均計算。