九九歸一 Excel跨表數(shù)據(jù)輕松取

愚人

n個工作表歸集到1個工作表

平時很多報表數(shù)據(jù)我們都是建立在多個工作表中的，比如很多公司的津貼都是按月發(fā)放，每月建立一個工作表，現(xiàn)在需要對公司員工季度發(fā)放的津貼做個統(tǒng)計，也就是要將1月、2月、3月每個員工的津貼數(shù)據(jù)匯總到“1季度匯總”工作表C列對應的單元格（圖1）。

對于這類需要將多個工作表數(shù)據(jù)歸集到一個表中的數(shù)據(jù)，現(xiàn)在可以借助SUM函數(shù)直接引用合并。切換到“1季度匯總”工作表，在C2輸入公式“=SUM（*！C2）”回車，此時公式會自動變?yōu)?SUM（1月：3月 ！C2），向下填充即可實現(xiàn)每個員工1月→3月津貼數(shù)據(jù)的匯總統(tǒng)計（圖2）。

使用SUM函數(shù)可以很方便地對多個工作表的數(shù)據(jù)進行統(tǒng)計，但是這里需要注意的是匯總工作表的結(jié)構(gòu)要和原始數(shù)據(jù)結(jié)構(gòu)一致（比如上表中姓名順序要一致），否則統(tǒng)計數(shù)據(jù)會出錯。顯然在進行一些特殊匯總時，SUM函數(shù)使用起來有一定的局限性。比如在一張倉庫盤點表中，盤點的產(chǎn)品按照大類分為多個工作表，現(xiàn)在需要對其中某幾個產(chǎn)品進行抽查，需要將指定抽查產(chǎn)品的盤點數(shù)據(jù)從多個工作表中提取并歸集在抽查表中（圖3）。

對于這類特定數(shù)據(jù)的匯總可以使用Vlookup嵌套INDIRECT函數(shù)實現(xiàn)歸集。切換到“抽查匯總”表，按提示輸入需要抽查的產(chǎn)品名稱，在B2輸入公式“=VLOO KUP（B2，INDIRECT（$A2&"！A1：C5"），2，F(xiàn)ALSE）”，這樣向下填充即可獲取抽查結(jié)果了（圖4）。

公式解釋：

B2：將匯總表輸入的B2值作為VLOOKUP搜索內(nèi)容，即搜索的是報表中具體產(chǎn)品的名稱。

“I N D I R E CT（$ A 2 &" ！ A1： C 5 " ）”：使用INDIRECT函數(shù)對工作表進行引用，引用的工作表是A2輸入名稱即抽查倉庫的名稱，注意這里的名稱要和工作表的名稱一致，否則引用會出錯。A1：C5則是引用范圍，注意由于每個工作表起始范圍數(shù)據(jù)可能不同，這里要使用工作表的最大范圍，假設(shè)本例最大范圍為“易耗”報表的A1：C18，則這里引用范圍設(shè)置為A1：C18。因為使用相對引用，所以公式填充后會自動根據(jù)A2輸入名稱去查找對應的工作表。

“2，F(xiàn)ALSH”：表示如果找到了相匹配的值，則返回對應工作表中第2列的對應內(nèi)容，本例為各產(chǎn)品盤點表中庫存數(shù)值，F(xiàn)ALSE表示查找時要求完全匹配，而不是模糊匹配。

n個工作簿歸集到1個工作表

除了分類工作表外，在日常的工作中還需要對已經(jīng)保存的多個工作簿文件進行歸集。比如上述倉庫盤點的例子，假設(shè)每個子表內(nèi)容是不同的倉管員盤點，并且各自建立了不同的工作簿文件，現(xiàn)在需要將每個倉庫盤點的數(shù)據(jù)歸集在“倉庫總表.xls”中。

如果需要合并的工作簿不多，可以直接利用Excel的連接功能實現(xiàn)。首先將需要合并的工作簿文件全部保存在一個目錄中。新建一個匯總表文件，打開后點擊“數(shù)據(jù)→連接”，在打開的工作表連接中點擊“添加”，依次將需要合并的工作簿文件導入連接（圖5）。

定位到A1單元格，點擊“數(shù)據(jù)→現(xiàn)有連接”，依次點擊上述添加的連接文件，然后在打開的窗口中顯示方式選擇“表”，現(xiàn)有工作表輸入“=$A$1”，點擊“確定”即可導入工作簿的數(shù)據(jù)（圖6）。

操作同上，繼續(xù)導入其他工作簿的數(shù)據(jù)，完成后就可以在匯總表中導入所有工作簿的數(shù)據(jù)了（圖7）。

如果需要導入的工作簿很多，那么可以借助VBA腳本快速實現(xiàn)。先到https：//share.weiyun.com/5f096 3791afbeaabd784381fb373f521下載所需的代碼文件，按Alt+F11打開VBA窗口，按提示輸入獲得的代碼并保存。返回匯總表窗口，點擊“開發(fā)工具→宏→合并工作表→執(zhí)行”，執(zhí)行這個宏后，在打開的瀏覽窗口選中全部需要導入的工作簿文件即可完成匯總（圖8）。