贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

在一年的開始,很多人開始要對去年的報表加以統計了。常有人問到這類的例題,在 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。

創作者介紹

學不完.教不停.用不盡

vincent 發表在 痞客邦 留言(2) 人氣()


留言列表 (2)

發表留言
  • 徐誌銘
  • 大師你好:
    請教如下圖如何取得名稱a每週一~每週日各別加總呢?
    感謝!

    項數 名稱 週一 週二 週三 週四 週五 週六 週日 週一 週二 週三 週四 週五 週六
    項數 名稱 1/1 1/2 1/3 1/4 1/5 1/6 1/7 1/8 1/9 1/10 1/11 1/12 1/13
    1 a 12 13 5 8 9 10 11 11 8 3 2 1 4
    2 b
    3 c
    4 d
    5 e
  • 請參考:http://isvincent.pixnet.net/blog/post/47337006

    vincent 於 2017/06/23 11:21 回覆

  • 訪客
  • 感謝解答,謝謝。
  • 謝謝你光臨我的網站。

    vincent 於 2017/06/24 14:32 回覆

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼