2014年9月21日 星期日

Excel-在增加的日期數列中計算最近和過去7天的小計(OFFSET,COUNT)

有網友問到:如何在一個 Excel 工作表的日期/金額數列中,要計算最近 7 天和過去 7 天的小計來比較,而日期會每天不斷的增加。

參考下圖,日期每天會增加一筆(不固定),而要取出數列中最下的 7 筆資料,和最後數來第 8 到第 14 筆資料來做比較。

【準備工作】

選取所有預定會增資料的範圍,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、金額。

【輸入公式】

(1) 最近 7 天金額小計

儲存格E2:=SUM(OFFSET(A2,COUNT(日期)-1,1,-7,1))

COUNT(日期):在日期陣列中計算已輸入日期的數量。

OFFSET(A2,COUNT(日期)-1,1,-7,1):利用 OFFSET 函數,找出最近 7 天(第 1 至 7 天)的位址。

(2) 過去 7 天金額小計

儲存格E3:=SUM(OFFSET(A2,COUNT(日期)-8,1,-7,1))

OFFSET(A2,COUNT(日期)-8,1,-7,1):利用 OFFSET 函數,找出最近 7 天(第 8 至 14 天)的位址。

(3) 設定上升/下降圖示

選取儲存格E2,設定其格式化的條件,如下圖所示:

當儲存格E2>儲存格E3時,就顯示綠色上升圖示,否則顯示紅色下降圖示。

而在儲存格E3中,為了配合儲存格E2的圖示位置,所以在格式化的條件中設定了「無儲存格圖示」:

 

【自行練習】

依據上列公式的描述,你可以自行設計最近 10天 和過去 10 天的金額小計:

儲存格E6:=SUM(OFFSET(A2,COUNT(日期)-1,1,-10,1))

儲存格E7:=SUM(OFFSET(A2,COUNT(日期)-11,1,-10,1))

沒有留言:

張貼留言

檢視其他文章

好康東東