2013年6月2日 星期日

Excel-將直式的報表轉換成矩型的報表(SUMPRODUCT)

在 Excel 的工作表中,有人想要將一個經年累記錄而成的直式報表轉換成矩型的報表(參考下圖),以方便呈現(資料表不會因長而不易顯示)或是製作統計圖表等。

或許你會認為以剪貼方式也很快可以重組資料,但是如果資料更多時將會不利於處理,使用公式來處理可以一勞永逸。而本例將不以相關查表函數來處理,而是要藉用 SUMPRODUCT 函數來執行查表的結果。

【準備工作】

選取資料範圍,按一下 Ctrl+Shift+Enter 鍵,勾選「頂端列」,定義名稱:年份、月份、收益。

 

【輸入公式】

儲存格F2:=SUMPRODUCT((年份=F$1)*(月份=$E2)*收益)

選取儲存格F2,貼至儲存格F2:I13。

本題因為是以年份和月份來組成資料,所以:

(年份=F$1)*(月份=$E2):找出在年份資料中符合儲存格F1且在月份資料中符合儲存格E2的 True/False 陣列。(其中的「*」運算,相當於邏輯 AND 運算。)

再於 SUMPRODUCT 函數中,找出對應的「收益」資料陣列。(每組年份和月份項目的條件都只會對應至一個收益項目)

 

【補充資料】

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

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

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

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

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

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

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

 

沒有留言:

張貼留言

檢視其他文章

好康東東