學不完．教不停．用不盡

在 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貼上)：