2012年3月8日 星期四

Excel-表格重整

在 Excel 中有一個資料表(如下圖左),如果想要轉換為下圖右的型式,該如何處理?

【準備工作】

選取儲存格A1:C27,按一下 Ctrl+Shfit+F3 鍵,勾選「頂端列」,定義名稱:項目、日期、時段。

 

【輸入公式】

儲存格F2:=IFERROR(INDEX(項目,SUMPRODUCT((日期=$E2)*(時段=F$1)*ROW(項目))-1),"")

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

SUMPRODUCT((日期=$E2)*(時段=F$1)*ROW(項目)):取得符合指定日期和時段的列號。

INDEX(項目,SUMPRODUCT((日期=$E2)*(時段=F$1)*ROW(項目))-1):透過 INDEX 函數,指定上式之列號減1(不含標題),來查得項目名稱。

最後再透過 IFERROR 函數,將查表產生的錯誤訊息,以空字串取代。

 

【補充說明】

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

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

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

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

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

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

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

 

INDEXhttp://office.microsoft.com/zh-tw/excel-help/HP010342608.aspx

INDEX:傳回表格或範圍內的某個值或值的參照。

語法:INDEX(array, row_num, [column_num])

Array:儲存格範圍或陣列常數。

Row_num:選取陣列中傳回值的列。

Column_num:選取陣列中傳回值的欄。

 

沒有留言:

張貼留言

檢視其他文章

好康東東