2011年11月22日 星期二

Excel-依月份和星期摘要銷售額(SUMPRODUCT)

在 Excel 中根據一個日期和項目所構成的銷售額資料表(參考下圖左),現在要依月份和星期幾來摘要統計表(參考下圖右),該如何處理?這個題目要使用 SUMPRODUCT、MONTH、WEEKDAY、ROW 等函數。

 

【準備工作】

首先,建立名稱,讓後續的公式更簡捷。

選取資料範圍(本例為儲存格A1:C26),按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,建立三個名稱:日期、項目、銷售額。

 

【依月份統計】

儲存格F2:=SUMPRODUCT((MONTH(日期)=ROW(1:1))*(項目=F$15)*銷售額)

複製儲存格F2,貼至儲存格F2:J13。

ROW(1:1) 往下複製時,ROW(1:1)=1 → ROW(2:2)=2 → … → ROW(12:12)=12。

配合 MONTH 函數求得的月份:1~12。

 

【依星期幾統計】

儲存格F16:=SUMPRODUCT((WEEKDAY(日期,1)=ROW(1:1))*(項目=F$15)*銷售額)

複製儲存格F16,貼至儲存格F16:J22。

WEEKDAY(日期,1) 中的參數1,表示數字 1 (星期一) 至數字 7 (星期日)。其他參數參考下圖:

 

【延伸學習】

(1) 如果上述之兩個報表,只要統計次數而非總和,該如何處理?

儲存格F2:=SUMPRODUCT((MONTH(日期)=ROW(1:1))*(項目=F$15))

儲存格F16:=SUMPRODUCT((WEEKDAY(日期,1)=ROW(1:1))*(項目=F$15))

 

(2) 如果想要以陣列公式來統計這些資料,該如何處理?

儲存格F2:{=SUM(IF(MONTH(日期)=ROW(1:1),IF(項目=F$15,銷售額,FALSE),FALSE))}

儲存格F16:{=SUM(IF(WEEKDAY(日期,1)=ROW(1:1),IF(項目=F$15,銷售額,FALSE),FALSE))}

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

 

【補充資料】

相關函數說明,請參考微軟網站:

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:決定傳回值類型的數字。

 

沒有留言:

張貼留言

好康東東