剛剛,我搞定了困擾多年的難題——Excel多表合并
作者|安偉星(星爺)
已授權轉載
本文為新書章節試讀內容
新書馬上上市
已經等不及了
多表合并歷來是困擾多數職場人士的難題,因為用到它的場景實在太多了:不同部門的數據、不同月份的數據、甚至不同公司的數據報表……分散在不同的工作表或者不同的工作簿中,他們具有相同的表頭,如何快速將他們合并在同一個工作表中?難道只能一個一個複製么?
如圖2-70所示,不同地區的銷售數據,分布在不同的工作表中,它們的特點是工作表的數據結構是相同的,如何能夠快速將其合并在同一個工作表中?
圖2- 70:分布在不同工作表中的銷量數據
Excel2016版本之前,解決這樣的問題,基本上只能通過VBA來完成,但是VBA相對來說門檻比較高,不適合大多數職場人士。
慶幸的是,Excel2016自帶的Power Query查詢工具允許用戶鏈接、合并多個數據源中的數據,我們可以靈活使用Power Query來實現Excel多表合并。
當然,由於Power Query功能異常強大,僅用它實現多表合并顯然有些「殺雞用牛刀」的感覺,但是對於不會VBA的同學,面對困擾已久的「多表合并」難題,這不失為一個好方法。
註:Excel2010~Excel2013版本,默認是沒有Power Query工具的。如果您使用的是Excel 2010~Excel2013版本,可以到微軟官網下載Power Query,這是一個免費插件,但是更早版本的Excel(如2003等)不支持這個插件。
—01—
同一個工作簿中的多表合并
案例1:如圖2-70所示,多個地區的銷售數據分布在不同的工作表中,這對後續的數據分析造成極大的不便,比如無法使用數據透視表,需要利用複雜的函數才能實現跨表求和等等,將不同表中的數據合并在同一個表中才是王道。
Step1:啟動Power Query是通過Excel【新建查詢】菜單完成的,它位於【數據】選項卡中的【獲取和轉化】分區。點擊【新建查詢】【從文件】【從工作簿】,如圖2-71所示,然後在彈出的「導入數據」選擇框中,選擇包含要合并工作表的Excel文件,點擊導入,如圖2-72所示。
圖2- 71:從工作簿中新建查詢
圖2- 72:將工作簿導入新建查詢中
Step2:選擇需要合并的工作表,並點擊【編輯】,從而對新建的查詢進行編輯,如圖2-73所示。
圖2- 73:選擇需要合并在一起的工作表
Step3:接下來需要新建一個「追加查詢」,將單個表合并起來,在查詢編輯界面,點擊【追加查詢】【追加查詢】,如圖2-74所示。
圖2- 74:新建追加查詢
在彈出的「追加查詢」對話框中,將剩餘的表格追加到右側「要追加的表」一欄中,然後點擊【確定】,如圖2-75所示。
圖2- 75:將剩餘的兩個表追加到當前查詢表中
Step4:追加查詢創建之後,會回到Power Query主界面,切換至【開始】選項卡【關閉】分區點擊【關閉並上載】下拉三角【關閉並上載至…】,如圖2-76所示,這一步的目的是將在Power Query中處理過的數據導入到Excel中。
圖2- 76:將Power Query中的查詢導入Excel
提示:在Power Query中創建的查詢表更像是資料庫結構,我們需要將它在導入到Excel中。
在接下來的「載入到」界面中,保持默認選項,直接點擊【載入】,即可將創建的「追加查詢」載入到Excel工作表中,完成之後,我們已將同一工作簿中的三個不同工作表合并在一起,如圖2-77所示。
圖2- 77:三個工作表合并在一起的狀態
之後,如果三個單獨的表中有更新,在總表中選擇點擊【數據】【連接】【全部刷新】即可獲取數據的最新狀態。
通過Power Query合并起來的工作表的另一個好處是,這是一個動態的合并關係,一旦原始表中的數據發生變化,只需要刷新即可完成新數據的合并,可謂以逸待勞!
—02—
不同工作簿中的多表合并
更複雜的情況是我們要合并的工作表並不在同一個工作簿中,而是分布在不同的Excel文件中。如圖2-78所示,有多個部門提交的數據,放置在同一個文件夾中,我們需要將這些Excel文件中的工作表合并在一起。
圖2- 78:分布在不同工作簿值中的數據
提示:應該注意的是,要匯總的這些文件,工作簿中的數據結構必須相同(列數相同、列標題相同)。
Step1:點擊【新建查詢】【從文件】【從文件夾】,如圖2-79所示,然後選擇數據所在的文件夾,並點擊【確定】。
圖2- 79:從文件夾新建查詢
然後直接點擊【編輯】,即可將所有Excel文件的信息載入至Power Query並處於編輯界面。
Step2:將工作簿載入到Power Query之後,可以看到,工作簿的所有屬性信息都在新建的查詢中,而這裡面很多信息都是不需要的,比如工作簿名、類型等,我們只需要用到工作簿中的內容,因此選擇【Content】列,並點擊【開始】【刪除列】【刪除其他列】,如圖2-80所示,這樣可以將無關列刪除。
圖2- 80:將無用的新系列刪除
Step3:接下來,需要把【Content】中的內容提取出來。切換到【添加列】選項卡點擊【自定義列】,如圖2-81所示。然後在彈出的【添加自定義列】窗口中輸入函數:
Excel.Workbook([Content],true),通過Excel.Workbook把Content 里的內容提取出來,如圖2-82所示。
圖2- 81:自定義添加列
圖2- 82:通過函數自定義添加列
提示:注意Excel.Workbook()的第二個參數,我們通過指定True,實現了默認情況下將第一行作為標題,省去了後面提升和篩選標題行的操作。
Step4:點擊新建的Test列右側的擴展按鈕,把所有欄位擴展出來,如圖2-83所示。
圖2- 83:擴展Test列中的所有內容
Step5:重複Step2中刪除其他列,選中Test.Data列,然後點擊【刪除其他列】,然後點選Test.Data的擴展按鈕,將Test.Data中的數據擴展出來,如圖2-84所示。
圖2- 84:將Test.Data值的數據擴展出來
經過兩輪的篩選,即可看到,目前所剩下的列已經是各工作簿中的數據表頭了。
Step6:點擊【確定】後即可得到最終的匯總結果。接下來,和2.7.1中 Step4相同的操作,將數據載入到Excel表中,可以看到,所有部門的數據均已正確合并到一起。
通過使用此方法,我們可以快速對同一文件夾下的同結構Excel進行合并。而且將來數據更新後我們只需要在合并後的Excel表中點擊【數據】【連接】【全部刷新】即可獲得最新數據合并結果,一勞永逸。
—03—
工欲善其事必先利其器
很多優秀的第三方Excel插件,可以更簡單地完成本節所講的功能。
第一款是Excel易用寶,它是是由Excel Home開發的一款Excel功能擴展工具軟體,可以讓繁瑣或難以實現的操作變得簡單可行,甚至能夠一鍵完成。
第二款是慧辦公插件,它同樣是一款功能豐富的插件,通過這個插件,可以很輕鬆實現一些複雜的操作,包括本節講的合并工作表,如圖2-85所示。
圖2- 85:慧辦公插件「傻瓜式」功能
這兩款插件都隨著隨書資源贈送給大家,放置的路徑是:圖書配套資源第2章 Excel這樣玩,秀出真技能2.7 困擾很多人的難題:Excel多表合并。
新書風采
以下是ExcelHome創始人、微軟全球最有價值專家周慶麟老師為新書《競爭力:玩轉職場Excel從此不加班》寫的推薦語(節選)
在這裡體驗Excel之美
轉載聲明:本文轉載自「精進Excel」,搜索「SeniorExcel」即可關注。
![](https://pic.pimg.tw/zzuyanan/1488615166-1259157397.png)
![](https://pic.pimg.tw/zzuyanan/1482887990-2595557020.jpg)
※超實用的3個Excel小技巧,學會後有一種飛起來的感覺
※掌握這些Word小技巧,小白秒變Word大神
TAG:雷哥office |
※Xbox One至今仍在困擾我們的9個問題
※困擾我三個月的bug解決了!
※Angelababy惹眾怒,但她的原因也困擾著很多女人啊!
※看了這些,困擾我20年的強迫症,終於好了!
※一台DS7,治好了困擾我多年的豪華車選擇困難症
※上學時候的6個「未解之謎」,是不是困擾了大家很多年呢
※你是不是個胖子?這個困擾你多年的問題終於有答案了!
※這些科學題有多難?困擾科學界上千年,或再給一萬年也解答不了?
※你是Win10高分屏用戶嗎?一個困擾人許久的問題解決了
※The Lancet:中國有將近1億成年人飽受慢性阻塞性肺病的困擾!
※為了解決母胎solo的困擾,我在做出了這些嘗試!
※NO.147 困擾你很久的大餅臉,就快要和你再見了
※私家推薦 | 為了解決母胎solo的困擾,我在做出了這些嘗試!
※困擾多年的腦鳴好了
※麗江之行多了一個好朋友,解決了我多年的困擾!
※困擾大家好多年的問題終於得到了解決
※剛剛,這個中國人一記神操作,竟解決了困擾全球百年的難題,全球震驚!而他究竟有多年輕,你可能想都想不到!
※解決一個困擾世人兩千年的問題要多久?19歲小伙一晚就做出來了
※防晒霜到底塗多少?困擾這麼多年的問題,一個勺子就能輕鬆搞定
※《我的世界》:這困擾了玩家好多年的4件事情,誰能來解釋一下?