2015年1月18日 星期日

Excel-分星期、分月份統計(SUMPRODUCT,WEEKDEY,MONTH,陣列公式)

在一年的開始,很多人開始要對去年的報表加以統計了。常有人問到這類的例題,在 Excel 中有一個每天的數值記錄產生的報表(參考下圖左),如何分星期、分月份來統計:天數、總和、平均、最大值、最小值等呢?(參考下圖右)

 

【準備工作】

選取所有基本資料,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、星期、數值。

 

【公式設計】

一、分星期統計

1. 計算天數

儲存格F2:=SUMPRODUCT((WEEKDAY(日期,2)=ROW(1:1))*1)

在 WEEKDAY 函數選擇參數2,乃對照星期一到星期日為 1 到 7。

而 ROW(1:1)=1,往下複製時會產生 ROW(2:2)=2、ROW(3:3)=3、...。

WEEKDAY(日期,2)=ROW(1:1):判斷日期中符合星期一者(數字1),傳回 TRUE/FALSE 陣列。

(WEEKDAY(日期,2)=ROW(1:1))*1:透過「*1」運算,將TRUE/FALSE 陣列轉換為 1/0 陣列,才可以在 SUMPRODUCT 函數中計算「乘積和」,這個結果即為幾個 TRUE 的個數,也就是天數。

2. 計算總和

儲存格G2:=SUMPRODUCT((WEEKDAY(日期,2)=ROW(1:1))*數值)

有了「1.計算天數」的概念,把「*1」改成「*數值」,則讓 SUMPRODUCT 函數取出符合者的「數值」來執行「乘積和」計算,這個結果即為符合條件者的總和。

3. 計算平均

儲存格H2:=SUMPRODUCT((WEEKDAY(日期,2)=ROW(1:1))*數值)/
SUMPRODUCT((WEEKDAY(日期,2)=ROW(1:1))*1)

計算平均即為將「總和/天數」,即為所求。

4. 計算最大值

儲存格I2:{=MAX(IF(WEEKDAY(日期,2)=ROW(1:1),數值,FALSE))}

WEEKDAY(日期,2)=ROW(1:1),數值,FALSE):在 IF的條件中,取得符合星期幾相符的「數值」陣列,不符合者傳回 FALSE,再由 MAX 函數在取得的數值中找出最大值。

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。

5. 計算最小值

儲存格J2:{=MIN(IF(WEEKDAY(日期,2)=ROW(1:1),數值,FALSE))}

原理同4.計算最大值,將 MAX 函數改為 MIN 函數。

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。

複製儲存格F2:J2,貼至儲存格F2:J8。

 

二、分月份統計

原理同:一、分星期統計,改用 MONTH(日期)來取得每個日期的月份,仿照上述的公式即可求得各部分的結果。

1. 計算天數

儲存格F11:=SUMPRODUCT((MONTH(日期)=ROW(1:1))*1)

2. 計算總和

儲存格G11:=SUMPRODUCT((MONTH(日期)=ROW(1:1))*數值)

3. 計算平均

儲存格H11:=SUMPRODUCT((MONTH(日期)=ROW(1:1))*數值)/SUMPRODUCT((MONTH(日期)=ROW(1:1))*1)

4. 計算最大值

儲存格I11:{=MAX(IF(MONTH(日期)=ROW(1:1),數值))}

5. 計算最小值

儲存格J1:{=MAX(IF(MONTH(日期)=ROW(1:1),數值))}

複製儲存格F11:J11,貼至儲存格F22:J22。

沒有留言:

張貼留言

好康東東