2013年9月24日 星期二

Excel-依日期統計數量(SUMPRODUCT,LARGE)

在使用的資料中,有一個常見的例子(參考下圖),其中有二個欄位,一是日期,一是內容。其中日期是由最新到最舊排列,有些日期有重覆,有些日期不在清單上。

根據這個資料表,要來求下圖中的四種統計數量:

(一) 根據距今天的日數來統計累計的筆數

儲存格E2:=SUMPRODUCT((日期>TODAY()-100*ROW(A1))*1)

ROW(A1):向下複製後,可以產生ROW(A1)=1、ROW(A2)=2、…、ROW(A10)=10。

TODAY()-100*ROW(A1):距離今天的指定日數(100、200、300、…)。

日期>TODAY()-100*ROW(A1):產生日期大於距離今天的指定日數的日期陣列。

透過 SUMPRODUCT 函數統計上式陣列的日期個數,透過「*1」,將其轉換為 1/0 陣列。

複製儲存格E2,貼至儲存格E2:E12。

將兩個相鄰日期的累積筆數相減,即是兩個日期區間的筆數。

 

(二) 根據最近的筆數來找出對應的日期

儲存格E15=LARGE(日期,D15)

因為日期已經由大到小排序,所以可以運用 LARGE 函數即可找出指定日數(100、200、300、…)的日期。

複製儲存格E15,貼至儲存格E15:E26。

將兩個相鄰日期相減,即是兩個日期區間的筆數。

 

(三) 依年度統計筆數

儲存格I2:=SUMPRODUCT((YEAR(日期)=H2)*1)

透過 YEAR 函數,找出日期中合於指定年度的日期的條件陣列(TRUE/FALSE),透過「*1」,將其轉換為 1/0 陣列。

透過 SUMPRODUCT 函數合計上式之 1/0 陣列。

複製儲存格I2,貼至儲存格I2:I7。

 

(四) 依星期統計筆數

儲存格I10:=SUMPRODUCT((WEEKDAY(日期,1)=ROW(A1))*1)

透過 WEEKDAY 函數,找出日期中合於指定星期的條件陣列(TRUE/FALSE),透過「*1」,將其轉換為 1/0 陣列。

其中 WEEKDAY 函數中參數的意義如下:

透過 SUMPRODUCT 函數合計上式之 1/0 陣列。

複製儲存格I10,貼至儲存格I10:I6。

沒有留言:

張貼留言

好康東東