網友問到:在 Excel 中,若要將多個工作表(例如以下的A,B,C工作表)轉換為一個工作表(合併ABC),該如何處理?

A工作表

Excel-多個工作表資料集合在一個工作表(OFFSET,MATCH,INDIRECT)

B工作表

Excel-多個工作表資料集合在一個工作表(OFFSET,MATCH,INDIRECT)

C工作表

Excel-多個工作表資料集合在一個工作表(OFFSET,MATCH,INDIRECT)

轉換成下表=A+B+C

Excel-多個工作表資料集合在一個工作表(OFFSET,MATCH,INDIRECT)

 

【公式設計與解析】

因為每個工作表的資料數不一樣多,要直接轉換並不容易。因此,先將每個工作表的資料集合在第 11 列。

Excel-多個工作表資料集合在一個工作表(OFFSET,MATCH,INDIRECT)

工作表A的儲存格B11:=OFFSET($A$3,MATCH("V",B3:B6,0)-1,0)

複製儲存格B11,貼至儲存格B11:AF11。

MATCH("V",B3:B6,0):利用 MATCH 函數找出每一欄中"V"的位置,並傳回第幾個的一個數值。

OFFSET($A$3,MATCH("V",B3:B6,0)-1,0):將上式傳回值代入 OFFSET 函數,找出A3:A5中對應的儲存格內容。

同理,工作表A的儲存格B11:=OFFSET($A$3,MATCH("V",B3:B5,0)-1,0)

Excel-多個工作表資料集合在一個工作表(OFFSET,MATCH,INDIRECT)

同理,工作表A的儲存格B11:=OFFSET($A$3,MATCH("V",B3:B7,0)-1,0)

Excel-多個工作表資料集合在一個工作表(OFFSET,MATCH,INDIRECT)

因為 A,B,C 三個工作表的格式都是一致的,所以可以利用公式來轉換多列為多欄。

儲存格C2:=INDIRECT(C$1&"!"&ADDRESS(11,COLUMN($A:$A)+ROW(1:1)))

複製儲存格C2,貼至儲存格C2:E32。

(1) COLUMN($A:$A)+ROW(1:1)

COLUMN($A:$A)=1,當公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→ ...。本式傳回 2,3,4, ...。

(2) ADDRESS(11,COLUMN($A:$A)+ROW(1:1))

產生 ADDRESS(11,2),當公式向下複製時公式變為:ADDRESS(11,2)→ADDRESS(11,3)→ADDRESS(11,4)→ ...,即產生儲存格B11→儲存格C11→儲存格D11→ ... 。

(3) INDIRECT(C$1&"!"&ADDRESS(11,COLUMN($A:$A)+ROW(1:1)))

C$1&"!"&ADDRESS(11,COLUMN($A:$A)+ROW(1:1)):產生『A!B11』,當公式向下複製時,產生字串:A!B11→A!C11→A!D11→ ...。當公式向右複製時產生字串:A!B11→B!B11→C!B11。

將上式利用 INDIRECT 函數將字串轉為實際儲存格位址,就可傳回儲存格的內容了。

Excel-多個工作表資料集合在一個工作表(OFFSET,MATCH,INDIRECT)

arrow
arrow
    全站熱搜

    vincent 發表在 痞客邦 留言(2) 人氣()