2012年8月31日 星期五

Excel-依日期分組小計(SUMPRODUCT、WEEKDAY)

在 Excel 中有一個資料表(如下圖左),分別由類別 A, B, C 組成的日期、時間、瀏覽量和留言量的數值資料。如果要建構如下圖右的資料表,該如何處理?

【準備工作】

先選取 A 欄到 E 欄中的有資料的儲存格,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,建立名稱:日期、時間、瀏覽量、留言量。

 

(1) 計算留言小計

儲存格H2:=SUMPRODUCT((WEEKDAY(日期,1)=ROW(1:1))*(類別=H$10)*留言量)

WEEKDAY(日期,1)=ROW(1:1):取得日期中是星期一的陣列。

複製該儲存格,往下各列貼上時,ROW(1:1)=1,會變為 ROW(2:2)=2、ROW(3:3)=3、…。

所以在 WEEKDAY 函數中採用參數「1」,可以配合數字 1 為星期日到數字 7 為星期六。

複製儲存格H2,貼至儲存格H2:J8

 

(2) 計算瀏覽平均

儲存格H11:=SUMPRODUCT((MONTH(日期)=ROW(1:1))*(類別=H$10)*瀏覽量)/SUMPRODUCT((MONTH(日期)=ROW(1:1))*(類別=H$10))

SUMPRODUCT((MONTH(日期)=ROW(1:1))*(類別=H$10)):取得月份和類別符合的個數。

SUMPRODUCT((MONTH(日期)=ROW(1:1))*(類別=H$10)*瀏覽量):取得月份和類別符合的瀏覽量總和。

利用此二式取得平均值。

複製儲存格H11,貼至儲存格H11:J18

 

【補充資料】

關於函數的詳細說明,請參考微軟網站的說明:

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

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

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

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

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

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

 

WEEKDAYhttp://office.microsoft.com/zh-tw/excel-help/HP010343015.aspx

WEEKDAY:傳回符合日期的星期。給定的日預設為介於1(星期日)7(星期六)之間的整數。

語法:WEEKDAY(serial_number,[return_type])

serial_number:要找的日期的代表序列值。

return_type:決定傳回值類型的數字。

 

沒有留言:

張貼留言

好康東東