2011年12月28日 星期三

Excel-資料表摘要(INDIRECT+ADDRESS)

假設在 XXX、YYY、ZZZ 三個工作表中(如下三個圖),都有 AAA、BBB、CCC、DDD、EEE 五個人的資料,如果想要將每個工作表的小計(位於第8列中)列,整理成一個「小計」的摘要表,該如何處理?其中摘要表要以 XXX、YYY、ZZZ 為欄標題,AAA、BBB、CCC、DDD、EEE 為列標題。

XXX 工作表:

YYY 工作表:

ZZZ 工作表:

【分析】

觀察下圖與以上三圖:

由於三個工作表的結構完全一樣,所以我們特別將工作表名稱定摘要表的欄標題,將每個工作表的欄標題(AAA、BBB、CCC、DDD、EEE)當為列標題。

 

【解法】

儲存格B2:=INDIRECT(B$1&"!"&ADDRESS(8,1+ROW(1:1)))

複製儲存格B2,貼至儲存格B2:D6。

ADDRESS(8,1+ROW(1:1)):結果為$B$8。

INDIRECT(B$1&"!"&ADDRESS(8,1+ROW(1:1))):相當於位址「XXX!$B$8」

向右複製時,即可產生XXX!$B$8、YYY!$B$8、ZZZ!$B$8。

向下複製時,即可產生XXX!$C$8、YYY!$C$8、ZZZ!$C$8。

再向下複製時,可產生XXX!$D$8、YYY!$D$8、ZZZ!$D$8。

此方式,不管有幾個工作表或工作表中有幾個人的資料,都可以一個公式通用之。

 

【延伸思考】

1. 如果原來的題目,將摘要表要以 AAA、BBB、CCC、DDD、EEE 為欄標題,為 XXX、YYY、ZZZ 列標題,公式該如何設計。

2. 如果想要在摘要表中,僅列出想要的人名(例如:AAA、CCC、EEE),公式該如何處理?

 

【補充資料】

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

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

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

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

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

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

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

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

 

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

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

語法:INDIRECT(ref_text,[a1])

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

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

沒有留言:

張貼留言

好康東東