2011年5月24日 星期二

Excel-求取最近幾天和最近幾筆的運算

在 Excel 中取得一個日期和數量的資料表,如何根據某個日期求取最近幾天(往前推算)和最近幾筆(往前推算)的數量總和呢(參考下圖)?並且要在儲存格範圍中標示這些被選出來運算的日期。

在計算前,先將B欄的日期資料部分定義名稱為:日期,將C欄中數量資料部分定義為數量。

(1) 最近天數

儲存格H2:{=SUM(IF(日期<=F2,IF(日期>F2-G2,數量,FALSE),FALSE))}

這是陣列公式,輸入完成請按 Ctrl+Shift+Enter 鍵。

IF(日期<=F2,IF(日期>F2-G2,數量,FALSE),FALSE):求取小於指定日期且大於指定日期+數目的日期之間,所對照的數量陣列。

再透過SUM函數加總這些數量陣列值。

接著要將儲存格依選取的日期標示出不同格式的範圍。選取儲存格A2:A25,輸入以下設定公式:

=AND($B2<=$F$2,$B2>=($F$2-$G$2))

將格式設定為較深的紅色。

(2) 最近筆數

儲存格H3:=SUM(OFFSET(C2,MATCH(F3,日期)-1,,-G3,))

MATCH(F3,日期)-1:找出指定日期位於B欄的那個位置。

在OFFSET函數中將指定的數目X(-1),即往前推算儲存格範圍。

利用SUM函數將OFFSET所得的數量之儲存格範圍加總。

接著要將儲存格依選取的日期標示出不同格式的範圍。選取儲存格A2:A25,輸入以下設定公式:

=AND(ROW(A2)<=MATCH($F$3,日期)+1,ROW(A2)>MATCH($F$3,日期)+1-$G$3)

將格式設定為較深的綠色。

練習用數據由下取用(複製後,在儲存格A1貼上):

項次 日期 數量
1 2011/01/01 6
2 2011/01/02 19
3 2011/01/05 11
4 2011/01/07 3
5 2011/01/11 1
6 2011/01/15 11
7 2011/01/19 3
8 2011/01/20 13
9 2011/01/21 5
10 2011/01/25 17
11 2011/01/27 17
12 2011/01/29 2
13 2011/02/01 4
14 2011/02/04 3
15 2011/02/07 15
16 2011/02/11 9
17 2011/02/14 2
18 2011/02/17 19
19 2011/02/18 15
20 2011/02/19 19
21 2011/02/22 15
22 2011/02/24 20
23 2011/02/27 11
24 2011/03/01 20

沒有留言:

張貼留言

檢視其他文章

好康東東