實用Excel技巧分享：利用Power Query合併資料夾裡的工作簿

之前先介紹了利用EXCEL的新功能Power Query實作彙整工作簿裡的工作表，但Power Query的功能遠遠不止於此，今天就給大家介紹個更進階的合併技巧：利用Power Query合併資料夾裡的工作簿。

如下圖，在桌面「銷售」資料夾裡放有四個地區的銷售資料。每個工作簿裡的標題名都是一致的，順序可以不一樣。每個工作簿裡城市這一列的數值就是工作簿的名稱，方便後續看合併效果。

操作如下：

#關閉資料夾裡的文件，新建工作簿，點選資料標籤下，[取得和轉換]群組裡「新建查詢」---「從檔案」---「從資料夾」。

把資料夾路徑輸入進去，也可以透過瀏覽選擇資料夾所在位置，點選確定。

這個介面把資料夾的所有工作簿都列出來了，點選編輯。

進入了Power Query編輯器介面。上方是選單欄，中間是表格區域也是最後要回到工作表的數據，右側查詢設定視窗顯示的是Power Query的操作記錄。

之前介紹過，「Content」這一列綠色字體代表這個單元格里包含了一個檔案。點選儲存格可以預覽裡面的內容。 （

注意：預覽單元格裡面的內容時應該把滑鼠放在單元格內空白處，不要放在文字上面，點擊文字會直接打開單元格里的文件）由於文件從文件夾直接提取過來都是binary格式，所以下方預覽窗格出現的是二進位格式的工作簿。

Binary作為二進位檔案是無法在表格中顯示的，那我們要做的就是把它變成table格式然後把資料展開到表裡。先把其他不需要的欄位刪除。

選擇「Content」這一列，點選開始選項卡下，[管理列]群組裡「刪除列」—「刪除其他列」。

######這樣除了「Content」這列之外的其他欄位都被刪除了。 ###

那怎麼把二進位檔案轉換成普通的表格呢，需要用到Power Query的專用程式語言—M語言。這裡要跟大家介紹一個常用的函數。

點選新增列選項下的[常規]群組裡的「自訂列」。

在自訂列窗口，「自訂列公式」裡輸入=Excel.Workbook([Content],true)，其中「[ Content]”可以點擊右側可用列裡的“Content”，再點擊右下角插入即可（注意：公式的大小寫千萬不能錯）。

#公式解析：

Excel.Workbook

功能：從Excel 工作簿傳回工作表的記錄。

參數：Excel.Workbook(workbook as binary, optional useHeaders as nullable logical, optionaldelayTypes as nullable logical) as table

這個函數傳回一個table，第一個參數workbook是binary格式，第二參數是可選參數邏輯值，true表示將原來表格的標題當作新表格的標題，預設是false表示用新列名取代原來工作表的標題。第三參數不用管。

這裡我們還是採用表格原先的標題，所以填true。這樣就省去了後續還要提升第一行為標題的步驟。

新列就新增成功了，預覽其中一個儲存格，下方顯示的就是一個表格樣式的工作簿了。這樣的就可以直接擴展到表裡了。

「Data」這一列顯示的就是Table格式的表格，包含了表格裡的數據，這裡我們只需要提取這一列就可以了。點選自訂列右上方擴充按鈕，選擇擴充列“Data”，不要勾選“使用原始列名作為前綴”。

#列名就變成了「Data」。這時我們再預覽「Data」裡的數據，下方出現的就是表格裡面的原始數據。再把下方資料全部提取出來。

同樣點擊自訂列右上方擴充按鈕，選擇擴充所有列，不要勾選「使用原始列名作為前綴」。

#這樣我們就透過逐層鑽取得到了工作表裡的資料。

最後把「Content」這一列刪除。選擇“Content”這一列，右鍵刪除即可。

最後把這個表格載到表格就可以了。

點選開始標籤下，[關閉]群組裡「關閉並上載」。

這樣資料就匯總到工作表了。

當點擊「城市」這一列的篩選按鈕，看到四個工作簿裡的資料都在表裡。

那當資料夾多了一個工作簿會如何？在這個資料夾試著放一個新的工作簿「西安」。

#回到剛才做資料統計的表格裡，點選資料標籤下的[連線]群組裡的「全部刷新」。

#城市這一列就多了“西安”，代表這個新工作簿的數據就被加進來了。

小結：Power Query合併資料夾，只要每個工作表裡的標題相同就可以進行合併匯總，這種方法不管資料夾有多少工作簿都能合併。並且任何資料變動都能透過全部刷新一鍵更新。

Power Query作為EXCEL資料分析的利器，透過簡單的圖形化操作，結合自帶的M語言並透過操作記錄器，幫助我們把更多資料進行統一操作，快速完成資料的處理和最佳化.而且它跟VBA相比上手快、容易操作，圖形化操作就能滿足我們大部分的需求。大家趕緊學起來吧！

相關學習推薦：excel教學

以上是實用Excel技巧分享：利用Power Query合併資料夾裡的工作簿的詳細內容。更多資訊請關注PHP中文網其他相關文章！