2011年5月1日 星期日

Excel-自動產生月份數列

在 Excel 中取得一個資料表,其依日期填入數量(如下圖左)。現在,希望建立一個摘要表,其中只要最近12個月的小計,該如何處理?

(1) 取得年和月的數列

儲存格E2:=YEAR(EDATE(TODAY(),ROW()-13))

因為第13列要放本月的資料,所以透過EDATE函數,以ROW()-13求取之前的月份,再取出年的部分。

儲存格F2:=MONTH(EDATE(TODAY(),ROW()-13))

同上原理,取出月的部分。

如此,每個月看到的報表都不一樣,其中第13列必定為今年的這個月(本月),其餘上向遞減(上個月)。

(2) 計算小計

因為資料會不斷的輸入,所以有資料的儲存格範圍會一直改變,所以先定義兩個資料範圍名稱:

DATA1:=OFFSET($A$2,,,COUNTA($A:$A)-1,)

DATA2:=OFFSET($B$2,,,COUNTA($B:$B)-1,)

其中利用COUNTA函數取得有資料的列數,再以OFFSET函數取得儲存格範圍。

儲存格G2:=SUMPRODUCT((YEAR(DATA1)=E2)*(MONTH(DATA1)=F2)*DATA2)

將DATA1和DATA2代入SUMPRODUCT函數,即可取得該月的小計。

(3) 將非報表所需的日期色彩淡化

因為第2列所列出的年和月已經計算出來,所以可以使用格式化條件來將非報表所需的日期色彩淡化。

先選取A欄和B欄(因為無法確定資料範圍),在格式化條件中輸入公式:=$A1<DATE($E$2:$F$2,1),文字色彩設定為較淡的灰色。即只要小於第2列所指年月的第1天,即會套用較淡的灰色。

沒有留言:

張貼留言

好康東東