2012年3月14日 星期三

Excel-重組表格(SUMPRODUCT,INDIRECT)

在 Excel 中有一個依組別和男/女所記錄的數值資料表(如下圖左),如何轉換成下圖右的格式呢?

【準備工作】

選取儲存格A1:C21,按一下 Ctrl+Shfit+F3 鍵,勾選「頂端列」,定義名稱:組別、性別、數值。

【公式-使用SUMPRODUCT】

儲存格F2:=SUMPRODUCT((組別=$E2)*(項目=F$1)*數值)

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

找出符合組別中合於儲存格E2並且項目中合於儲存格F1的 True/Fasle 陣列,再乘以數值陣列,即可得答案。

 

【公式-使用INDORECT】

儲存格F2:=INDIRECT("C" & ROW(1:1)*2)

使用 INDIRECT 函數,得到C欄中第2,4,6, … 的儲存格內容。

儲存格G2:=INDIRECT("C" & ROW(2:2)*2-1)

使用 INDIRECT 函數,得到C欄中第3,5,7, … 的儲存格內容。

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

 

【補充資料】

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

INDIRECThttp://office.microsoft.com/zh-tw/excel-help/HP010342609.aspx

INDIRECT:傳回文字串所指定的參照位址。

語法:INDIRECT(ref_text,[a1])

ref_text:單一儲存格的參照位址,其中包含A1欄名列號表示法、R1C1欄名列號表示法、定義為參照位址的名稱,或定義為字串的儲存格參照位址。

a1:指定ref_text儲存格中所包含參照位址類型的邏輯值。

 

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

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

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

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

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

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

沒有留言:

張貼留言

好康東東