學不完．教不停．用不盡

在 Excel 中取得一個每日資料記錄表，資料已有2008年、2009年、2010年、2011年(如下圖左)，現在希望利用這些資料，分年度計算各月中所有同一日的平均(如下圖中)及每週同一星期幾的平均(如下圖右)。

(1) 設計年度選取之下拉式清單

為了方便使用，所以在儲存格G1中設定資料驗證，其儲存格允許設定為清單，而資料來源輸入：「2008,2009,2010,2011」。

(2) 設定資料的「名稱」

1. 選取儲存格A2:D694。

2.  按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」選項。

如此便設定了「日期、星期、金額、數量」等四個名稱。

(3) 計算數量平均、金額平均、數量平均、金額平均

儲存格G3：{=AVERAGE(IF(YEAR(日期)=$G$1,IF(DAY(日期)=ROW(1:1),數量,FALSE),FALSE))}

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵。(以下公式之做法相同)。

ROW(1:1)往下複製時會變成ROW(2:2)=2，ROW(3:3)=3，ROW(4:4)=4，…

IF(DAY(日期)=ROW(1:1),數量,FALSE)可以找出日期是1者的陣列。

IF(YEAR(日期)=$G$1,IF(DAY(日期)=ROW(1:1),數量,FALSE),FALSE)公式相當於：

找出「日期的年數=儲存格G1 AND 日期的日數=1」者的數量陣列，再經由AVERAGE函數計算平均值。

儲存格H3：{=AVERAGE(IF(YEAR(日期)=$G$1,IF(DAY(日期)=ROW(1:1),金額,FALSE),FALSE))}

同儲存格G3之原理。

複製儲存格G3:H3，往下至儲存格G3:H33。

儲存格K3：{=AVERAGE(IF(YEAR(日期)=$G$1,IF(WEEKDAY(日期,2)=ROW(1:1),數量,FALSE),FALSE))}

IF(WEEKDAY(日期,2)=ROW(1:1),數量,FALSE)可以找出日期的星期數是1者的數量陣列，再經由AVERAGE函數計算平均值。其中的WEEKDAY函數的參數2，是指星期一傳回1、星期二傳回2、…。

儲存格L3：{=AVERAGE(IF(YEAR(日期)=$G$1,IF(WEEKDAY(日期,2)=ROW(1:1),金額,FALSE),FALSE))}

同儲存格K3之原理。

複製儲存格K3:L3，往下至儲存格K9:L9。

詳細函數說明，請參閱微軟網站：

WEEKDAY：http://office.microsoft.com/zh-tw/excel-help/HP010343015.aspx