學不完．教不停．用不盡

在 Excel 中有一個每天會增加三筆資料的報表(如下圖左)，如何根據不同產品，產生依星期和月份統計的報表(累計下載和平均檢閱)(參考下圖右)？

因為每天都會增加三筆資料，所以資料的範圍並不固定。

【準備工作】

首先要定四個名稱，以便簡化公式的複雜度。由於每天都有三筆資料加入，所以資料的範圍不固定，因此以OFFSET函數來定義資料範圍。在名稱管理員中做以下的定義：

(1) 產品：=OFFSET(工作表1!$A$2,,,COUNTA(工作表1!$A:$A)-1,)

(2) 日期：=OFFSET(工作表1!$B$2,,,COUNTA(工作表1!$A:$A)-1,)

(3) 檢閱數：=OFFSET(工作表1!$C$2,,,COUNTA(工作表1!$A:$A)-1,)

(4) 下載數：=OFFSET(工作表1!$D$2,,,COUNTA(工作表1!$A:$A)-1,)

公式中以 COUNTA 函數來抓取目前在 A 欄中有多少筆資料。

【計算累計下載 - 使用 SUMPRODUCT 函數】

儲存格G2：=SUMPRODUCT(--(產品=G$10),--(WEEKDAY(日期,2)=ROW(1:1)),下載數)

複製儲存格G2，貼至儲存格G2:I8。

其中 WEEKDAY 函數中的參數 2，乃定義數字 1(星期一) 至 7(星期日)：

ROW(1:1)=1，往下複製時會自動變為ROW(2:2)=2 –> ROW(3:3)=3 –> … –> ROW(7:7)=7。

利用 SUMPRODUCT 函數取得「符合產品名稱的 True/False陣列、符合星期幾的 True/False陣列、下載數」來運算乘積和。

SUMPRODUCT 函數中使用「--」，是為了將 True/False 陣列轉換為 1/0 陣列，才能以數值計算。

SUMPRODUCT(--(產品=G$10),--(WEEKDAY(日期,2)=ROW(1:1)),下載數) 也可以寫成：

SUMPRODUCT((產品=G$10)*(WEEKDAY(日期,2)=ROW(1:1))*下載數)

【計算累計下載 - 使用陣列公式】

儲存格G2：{=SUM(IF(產品=G$10,IF(WEEKDAY(日期,2)=ROW(1:1),下載數,FALSE),FALSE))}

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵。

複製儲存格G2，貼至儲存格G2:I8。

IF 函數中的 FALSE 參數在此例中使用「0」或「空白」所得的結果是一樣的。

由以上「計算累計下載」的例子，來自行練習「計算平均檢閱」。

【計算平均檢閱 - 使用 SUMPRODUCT 函數】

儲存格G11：=SUMPRODUCT((產品=G$10)*(MONTH(日期)=ROW(9:9))*檢閱數)/SUMPRODUCT((產品=G$10)*(MONTH(日期)=ROW(9:9)))

複製儲存格G11，貼至儲存格G11:I13。

公式中透過 MONTH 函數來求得日期代表的月份，因為題目要求9,10,11月，所以判斷其等於ROW(9:9)=9。往下複製時可以產生10,11。

【計算平均檢閱 - 使用陣列公式】

儲存格G11：{=AVERAGE(IF(產品=G$10,IF(MONTH(日期)=ROW(9:9),檢閱數,FALSE),FALSE))}

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵。

複製儲存格G11，貼至儲存格G11:I13。

【補充說明】

相關函數說明，請參閱微軟網站。

SUMPRODUCT：http://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx