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