2016年4月28日 星期四

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

網友問到:在 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)

沒有留言:

張貼留言

檢視其他文章

好康東東