2012年1月31日 星期二

Excel-計算各月的平均(陣列公式)

在 Excel 的工作表中有個日期和數量的報表,如果想要摘要依月份計算數量的平均,該如何處理?(參考下圖)

選取儲存格A1:B26,按一下 Ctrl+Shift+F3 鍵,定義名稱:日期、數量。

【使用陣列公式】

儲存格E2:{=AVERAGE(IF(MONTH(日期)=ROW(1:1),數量,FALSE))}

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

複製儲存格E2,貼至儲存格E2:E13。

MONTH(日期)=ROW(1:1):判斷日期中的月份是否為1(ROW(1:1)=1),而ROW(1:1)往下複製會變為ROW(2:2)=2,…。

IF 函數中的參數 FALSE,請勿以0或空白取代,否則所有的0都會列入 AVERAGE 函數來平均。

 

【使用SUMPRODUCT函數】

儲存格E2:=SUMPRODUCT((MONTH(日期)=ROW(1:1))*數量)/SUMPRODUCT(--(MONTH(日期)=ROW(1:1)))

複製儲存格E2,貼至儲存格E2:E13。

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

 

【補充說明】

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

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

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

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

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

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

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

3 則留言:

  1. 老師,我現在需要的是計算各月的數量總和,但是不知道該怎麼做變化才對?

    回覆刪除
  2. 火星人老師,你的教學我有試著操作一次成功了
    但是我實際需要的不是平均而是總和,但我對這部分完全沒接觸過,不知道該怎麼做才對,可以請你幫幫我嗎?

    回覆刪除
    回覆
    1. 請參閱 http://isvincent.blogspot.tw/2012/07/excel-sumproduct_25.html

      刪除

檢視其他文章

好康東東