學不完．教不停．用不盡

在 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天，即會套用較淡的灰色。