2011年1月23日 星期日

Excel-以日期的年和月來統計分析

接續前一篇文章:Excel-將多個工作表資料組合成一個資料表

http://isvincent.blogspot.com/2011/01/excel_22.html

如果要對這個資料表,以發表時的年和月來統計分析發表的篇數,該如何處理?

產生報表:

儲存格G2:=SUMPRODUCT(--(MONTH(INDIRECT("A2:A" & COUNTA($A:$A)))=ROW(1:1)),--(YEAR(INDIRECT("A2:A" & COUNTA($A:$A)))=(COLUMN(A:A)+2007)))

複製儲存格G2到儲存格G2:J13。

因為資料來源會不斷的增加,所以無法以固定的絶對位址(例如:$A2:$A863)表示,因此要藉助 INDIRECT 函數。公式中的 INDIRECT("A2:A" & COUNTA($A:$A)) 表示儲存格A2到A欄最後一個儲存格。

原公式=SUMPRODUCT(--(MONTH(A欄有資料的儲存格範圍))=ROW(1:1)),--(YEAR(A欄有資料的儲存格範圍))=(COLUMN(A:A)+2007)))

因為ROW(1:1)=1向下複製時,會產生ROW(2:2)=2 –> ROW(3:3)=3 –> …,所以可以代表1到12月。

同理COLUMN(A:A)=1向右複製時,會產生COLUMN(B:B)=2 –> COLUMN(C:C)=3 –> …。再加上2007,即可產生2008~2012。

此公式,當資料來源增加或是報表中的年份增加,都只要將公式向下或向左複製即可。

沒有留言:

張貼留言

好康東東