2012年2月15日 星期三

Excel-摘要表格(INDIRECT+ADDRESS+ROW+陣列公式)

有網友問到,在 Excel 中如下圖左的一個資料表,要重新摘要成下圖右的資料表,該如何處理?

 

【準備工作】

為了便於公式說明,請選取儲存格A1:B9,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:專案、姓名。

 

【設計】

儲存格E2:{=IFERROR(INDIRECT(ADDRESS(SMALL(IF(專案=E$1,ROW(專案)),ROW(1:1)),2)),"")}

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

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

IF(專案=E$1,ROW(專案)):取得專案中符合儲存格E1內容的列號陣列。

本例結果為:{ 2, False, False, 5, False, False, False, 9 }

SMALL(IF(專案=E$1,ROW(專案)),ROW(1:1)):取得上式陣列中的第1小值(本例為2),若往下複製時,ROW(1:1)會變為ROW(2:2),則可以取得第2小值(本例為5),依此類推。

接著透過 ADDRESS(列號,2) 來取得儲存格的位址,再透過 INDIRECT 函數,將該位址轉換為儲存格內容。

因為複製儲存格的過程可能產生錯誤訊息,利用 IFERROR 函數,將錯誤值以空字串取代。

 

【修改】

如果你想省略 ADDRESS 函數,可以將公式修改如下:

儲存格E2:{=IFERROR(INDIRECT("B"&SMALL(IF(專案=E$1,ROW(專案)),ROW(1:1))),"")}

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

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

 

【補充資料】

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

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

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

語法:INDIRECT(ref_text,[a1])

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

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

 

ADDRESShttp://office.microsoft.com/zh-tw/excel-help/HP010342163.aspx

ADDRESS:在已知指定列和欄號下,取得工作表中儲存格的位址。

語法:ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

row_num:指定要用在儲存格參照中之列號的數值。

column_num:指定要用在儲存格參照中之欄號的數值。

abs_num:可省略。指定要傳回之參照類型的數值。傳回此參照類型如下:

1或省略:絕對儲存格參照;2:列:絕對;欄:相對;3:列:相對;欄:絕對;4:相對參照

沒有留言:

張貼留言

好康東東