2013年1月3日 星期四

Excel-樞紐分析表和SUMPRODUCT

在 Excel 中的樞紐分析表是一個非常好用的工具,很輕鬆就可以做到摘要的工作。以下圖為例,要依水果類別和等級類別來做加總的摘要表。

同事問到,如何以公式方式來做到相同結果?

 

這其實是個很好的練習題。

1. 先選取儲存格B2:D25,按一下 Ctrl+Shift+F3 鍵,定義名稱:等級、水果、數量。

2. 輸入公式:

 儲存格G2:=SUMPRODUCT((水果=$F2)*(等級=G$1)*數量)

3. 複製儲存格G2,貼至儲存格G2:I5。

4. 選取儲存格G2:J6,按一下 Alt  + = 鍵,自動產生小計和總計的公式。

 

【補充資料】

相關之詳細函數說明,請參閱微軟網站:

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

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

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

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

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

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

2 則留言:

  1. 4. 選取儲存格G2:J6,按一下 Ctrl+= 鍵,自動產生小計和總計的公式。

    關於第四點的部分,ctrl + = 沒有反應的樣子,不過按下Alt + = 才有類似的功能,想請問是不是打錯或是有版本上面的差異?

    回覆刪除
    回覆
    1. 謝謝您的提醒, 真的是打錯字, 應該為 Alt+= 才正確。

      刪除

檢視其他文章

好康東東