2011年11月27日 星期日

Excel-統計持續增加的資料(OFFSET+SUMPRODUCT+陣列)

在 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。

 

【補充說明】

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

SUMPRODUCThttp://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx

SUMPRODUCT:傳回各陣列中所有對應元素乘積的總和。

語法:SUMPRODUCT(array1, [array2], [array3], ...)

array1:要求對應元素乘積和的第一個陣列引數。

array2, array3,...:要求對應元素乘積和的第 2 個到第 255 個陣列引數。

註:各陣列必須有相同的維度(相同的列數,相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。

沒有留言:

張貼留言

檢視其他文章

好康東東