2011年5月8日 星期日

Excel-計算每月各日及星期各天的平均

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

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

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

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

1. 選取儲存格A2:D694。

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

image

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

(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

WEEKDAY:傳回符合日期的星期。給定的日預設為介於1(星期日)7(星期六) 之間的整數。

語法:WEEKDAY(serial_number,[return_type])

Serial_number:要找的日期的代表序列值。

Return_type:決定傳回值類型的數字。

 

RETURN_TYPE

傳回的數字

1 或省略

數字 1 (星期日) 7 (星期六)

2

數字 1 (星期一) 7 (星期日)

3

數字 0 (星期一) 6 (星期六)

11

數字 1 (星期一) 7 (星期日)

12

數字 1 (星期二) 7 (星期一)

13

數字 1 (星期三) 7 (星期二)

14

數字 1 (星期四) 7 (星期三)

15

數字 1 (星期五) 7 (星期四)

16

數字 1 (星期六) 7 (星期五)

17

數字 1 (星期日) 7 (星期六)

 

沒有留言:

張貼留言

檢視其他文章

好康東東