excel將多個工作表資料內容合併到一個工作表

2019-12-07 07:44:10

在分析和匯總多個表格資料，比如說多個子公司財務報表或同一公司不同月份的銷售資料時，經常需要將多個工作表資料內容合併顯示到一個工作表中，方便檢視。本例介紹利用INDIRECT函數實現將多個工作表資料顯示到一個工作表中。

首先，先來看一下INDIRECT函數的用法。INDIRECT函數的作用是：將表示單元格參照的文字轉換為實際單元格的參照。具體範例見下圖說明：

上面的例子是返回同工作表中的內容，INDIRECT函數對不同工作簿、不同工作表的文字地址也是同樣適用的，如下圖範例：

注意：對部分工作表名稱參照時需要在工作表名稱前後加上英文狀態的單引號，比如說以數位開頭的工作表名稱，實際單元格參照類似：='1月份報表'!H18。為了避免錯誤，可以將所有工作表名稱前後都加上單引號。

下面來介紹一下要合併的資料。我們有12張工作表，每個工作表是一個月份的資料。其中A列是客戶名稱，B列和C列分別是兩個產品的銷售數量。每個表格裡A列的客戶名稱是一樣的（可以類比財務報表的固定科目）。

首先，建立一個彙總表，把客戶名稱複製到彙總表的A列。

從B1開始，將工作表名稱順序填充在彙總表第一行。

注意：工作表名稱可以用其他Excel小外掛取得，轉置貼上到B1開始的單元格區域。

我們先來匯總產品一各月的銷售數量。

雙擊B2單元格，輸入公式：=INDIRECT("'"&B$1&"'!B"&ROW(A2))，返回1月份A客戶產品一的銷售數量，我們可以檢視原始資料表驗證一下。

注意：上面INDIRECT函數生成單元格地址參照部分用了公式，B1代表月份，!B代表取每個表B列資料。row(A2)先返回A2單元格的行數2，然後下拉會變成3,4……等，也就可以順序取得每個工作表其他客戶的銷售資料。

將B2單元格公式右拉，下拉填充滿資料區域就可以方便的檢視各月份各客戶產品一的銷售金額了。我們也可以檢視原始資料表驗證一下。

如果要檢視產品二的資料，可以修改B2的公式，將!B改成!C，右拉下拉即可。也可以用查詢替換的方法一次性完成該轉換。

易錯點提示：這種替換時不要只將B替換成C，因為公式中包含的所有字母B都會被替換成C，導致發生不可預期的錯誤。比如說ISBLANK函數的B替換成C後，excel就無法識別並運算了。

替換完成後，也可以檢視一下原始資料表產品二的銷售量，看是否正確返回了結果。