利用查詢編輯器動(dòng)態(tài)合并多工作薄

王志軍

職場(chǎng)實(shí)戰(zhàn)中，經(jīng)常會(huì)涉及多個(gè)工作簿數(shù)據(jù)的匯總或分析。如圖1所示，這是某集團(tuán)下屬公司的銷(xiāo)售數(shù)據(jù)，類(lèi)似的銷(xiāo)售數(shù)據(jù)還有許多，如果每次手工復(fù)制、粘貼之后再進(jìn)行后續(xù)操作，工作量可想而知。而且如果源數(shù)據(jù)發(fā)生了變化，上述操作勢(shì)必需要重新執(zhí)行一次。

由于源數(shù)據(jù)的表結(jié)構(gòu)相同，我們可以利用查詢編輯器完成多工作簿的動(dòng)態(tài)合并。最主要的準(zhǔn)備工作是將分布不同工作簿、不同工作表的數(shù)據(jù)匯總到同一工作表內(nèi)。這里以Excel 365版本為例進(jìn)行說(shuō)明，具體操作如下。

第1步：載入文件夾

新建一個(gè)空白工作簿，切換到“數(shù)據(jù)”選項(xiàng)卡，在“獲取和轉(zhuǎn)換數(shù)據(jù)”功能組下依次選擇“獲取數(shù)據(jù)→來(lái)自文件→從文件夾”，按照提示載入相應(yīng)的文件夾。如圖2所示，點(diǎn)擊右下角的“轉(zhuǎn)換數(shù)據(jù)”按鈕，此時(shí)會(huì)自動(dòng)打開(kāi)Power Query編輯器。如果是Excel 2016/2019版本，請(qǐng)點(diǎn)擊“編輯”按鈕。

如果只需要合并各個(gè)工作簿中某幾個(gè)工作表的數(shù)據(jù)，可以依次選擇“組合→合并并轉(zhuǎn)換數(shù)據(jù)”，如圖3所示，在這里選擇相應(yīng)的工作表轉(zhuǎn)換數(shù)據(jù)即可。

第2步：添加自定義列

進(jìn)入Power Query編輯器之后，選擇最左側(cè)的兩列，單擊右鍵，從快捷菜單選擇“刪除其他列”并執(zhí)行。切換到“添加列”選項(xiàng)卡，在“常規(guī)”功能組單擊“自定義列”按鈕，此時(shí)會(huì)打開(kāi)“自定義列”對(duì)話框，在這里輸入任意列名，例如“data”，在公式編輯框中輸入如下公式：

=Excel.Workbook（[Content]，true）

上述公式中，Excel.Workbook函數(shù)的作用是從Excel工作簿返回各工作表的記錄，第一個(gè)參數(shù)是需要解析的字段，第二個(gè)參數(shù)使用true，表示指定將表格中的第一行作為字段標(biāo)題。要強(qiáng)調(diào)的是，這里的公式要注意字母的大小寫(xiě)，否則會(huì)報(bào)錯(cuò)。

第3步：

右擊剛才添加的自定義列“data”，從快捷菜單選擇“刪除其他列”。單擊“data”列標(biāo)右側(cè)的展開(kāi)按鈕，默認(rèn)設(shè)置下直接點(diǎn)擊“確定”按鈕。右擊“data”列選擇“刪除其他列”，此時(shí)可以看到如圖4所示的合并效果。

接下來(lái)的操作就簡(jiǎn)單多了，返回“主頁(yè)”選項(xiàng)卡，單擊“關(guān)閉并上載”按鈕，即可將該文件夾下所有工作簿中各個(gè)工作表的數(shù)據(jù)都匯總到一起，后續(xù)的統(tǒng)計(jì)、分析等相關(guān)操作就簡(jiǎn)單多了。如果在文件夾中添加了新的工作簿，我們只需要在匯總表格中單擊右鍵刷新就可以了。

補(bǔ)充：如果是Excel2016以下的版本，可以從微軟官方下載安裝Power Query編輯器插件，也可以下載安裝“易用寶”插件。