2011年9月15日 星期四

Excel-統計指定日期前10筆資料

在 Excel 的一個資料表中,含有日期和相關數據(如下圖左),如果想要統計指定日期(含)之前10筆的統計數據(例如:總和),該如何計算?

先為儲存格範圍建立名稱,選取日期、姓名、數量等資料範圍,按一下 Ctrl+Shift+F3 鍵,勾選[頂端列]選項。如此分別定義了日期、姓名、數量等三個名稱的範圍。

儲存格F3:=SUM(OFFSET(A2,MATCH(E3,日期,0)-1,2,-10,))

MATCH(E3,日期,0):求出儲存格E3的資料在日期中的第幾列,本例為日期資料的第17列。

OFFSET(A2,MATCH(E3,日期,0)-1,2,-10,):本例=OFFSET(A2,17-1,2,-10,),結果為儲存格C9:C18。

透過SUM(C9:C18)即可求得總和。

但是,如果所選日期之前的日數不足10個時,公式即會出錯,例如:

儲存格F4:=SUM(OFFSET(A2,MATCH(E4,日期,0)-1,2,-10,))

會出現 #REF! 的錯誤訊息,因為位址參照範圍不正確。

公式修正如下:

儲存格F5:=SUM(OFFSET(A2,MATCH(E5,日期,0)-1,2,IF(MATCH(E5,日期,0)<10,-MATCH(E5,日期,0),-10)))

IF(MATCH(E5,日期,0)<10,-MATCH(E5,日期,0),-10):判斷如果 MATCH(E5,日期,0) 比10小者,取 MATCH(E5,日期,0) 的值,否則取 10。

如此,便可動態的統計任何日期之前的10筆資料了!

沒有留言:

張貼留言

檢視其他文章

好康東東