在日常工作中，不可避免的需要對(duì)相同字段的表格進(jìn)行合并，一般這個(gè)時(shí)候要么手肝要么就使用VBA代碼。但并不是所有人都對(duì)VBA熟悉，今天叫教會(huì)大家一種在Excel中對(duì)文件批量合并的方法。
關(guān)于在Excel中如何使用Power Query，請(qǐng)看我的另一篇文章，此處不再累贅。傳送門-認(rèn)識(shí)Power Query并了解基本功能
如果我們有N個(gè)字段相同的表格需要合并，那么可以使用Excel中的插件， Power Query進(jìn)行合并，而只需要掌握一個(gè)M函數(shù)“Excel.Workbook"函數(shù)即可。此處要求需要合并的數(shù)據(jù)字段相同。
第一步
打開數(shù)據(jù)菜單—新建查詢—從文件—從文件夾，選擇文件所在文件夾位置，點(diǎn)擊確定。請(qǐng)注意該方式是從文件夾加載，因此選擇的是文件夾地址，不是選擇某個(gè)文件。
-
點(diǎn)擊選擇文件所在的文件夾路徑
-
點(diǎn)擊編輯
-
點(diǎn)擊編輯后自動(dòng)打開Power Query編輯界面
第二步
-
由于我把合并的文件和源文件放在一個(gè)路徑下，因此通過篩選，取消掉該文件及其緩存文件或其他不需要的數(shù)據(jù)。在PQ中，篩選和在Excel中不同，Excel對(duì)數(shù)據(jù)篩選后依然保留數(shù)據(jù)的可用性，僅起到對(duì)篩選數(shù)據(jù)顯示，而在PQ中篩選后數(shù)據(jù)將不再保留，其操作步驟在右側(cè)應(yīng)用步驟保留操作記錄，如果操作有誤，可以對(duì)其再次編輯。你可以理解為你錄制了一個(gè)復(fù)雜的宏，而且這個(gè)宏是可以隨意編輯任何一個(gè)步驟的，而這些操作點(diǎn)點(diǎn)按鈕就可以完成。
-
右側(cè)保留操作步驟，后面的小齒輪按鈕點(diǎn)擊可編輯操作步驟。
第三步
- 使用Excel.Workbook函數(shù)展開數(shù)據(jù)，點(diǎn)擊添加列菜單，點(diǎn)擊自定義列
- 填入自定義公式：
= Excel.Workbook([Content])//嚴(yán)格區(qū)分大小寫，且無提示和自動(dòng)補(bǔ)全功能
其中[Content]，是通過雙擊右側(cè)的可用列中的Content自動(dòng)填入的。也可以直接將代碼復(fù)制進(jìn)去，因?yàn)槊看魏喜⒉煌谋砀瘢摬襟E是一樣的。
-
點(diǎn)擊確定，會(huì)新增一個(gè)自定義列，且在右側(cè)應(yīng)用步驟會(huì)多一步剛剛自定義添加列的操作步驟。
-
點(diǎn)擊自定義列右側(cè)的雙向外展箭頭，在彈出的菜單中，取消所有打勾選項(xiàng)，僅選擇Date，點(diǎn)擊確定。
-
你沒有看錯(cuò)，需要再次點(diǎn)擊展開，而此時(shí)剛剛叫自定義的列，現(xiàn)在叫Date。
-
但是先別著急，我們得把旁邊不需要的列全部刪除，為了方便后續(xù)編輯，建議使用開始菜單-選擇列-取消除了Date之外的全部選項(xiàng)，點(diǎn)擊確定，確定后我們發(fā)現(xiàn)就剩一列了，右側(cè)又增加了對(duì)應(yīng)的操作步驟。
-
再次點(diǎn)擊展開按鈕，展開數(shù)據(jù)，取消使用原始列名作為前綴的選項(xiàng)，點(diǎn)擊確定。
-
由于我展開的數(shù)據(jù)沒有標(biāo)題，需要第一行提升為標(biāo)題。
點(diǎn)擊將第一行用作標(biāo)題后，右側(cè)自動(dòng)新增了一個(gè)更改的數(shù)據(jù)類型步驟，該操作步驟是系統(tǒng)自動(dòng)識(shí)別并調(diào)整，如果你不懂建議刪除，否者會(huì)對(duì)后續(xù)步驟造成影響，刪除后也可以手動(dòng)調(diào)節(jié)數(shù)據(jù)格式。
-
請(qǐng)注意刪除前后數(shù)據(jù)的變化
刪除更改的類型-前
刪除更改的類型-后 -
因?yàn)槭嵌啾砗喜ⅲ兄貜?fù)的標(biāo)題，我們需要將其篩選掉。找一個(gè)分類較少的列，點(diǎn)擊右側(cè)的篩選按鈕，將標(biāo)題取消篩選。如果數(shù)據(jù)較多，無法顯示全部可以點(diǎn)擊右側(cè)的加載更多顯示全部分類。
-
取消重復(fù)的標(biāo)題選項(xiàng)，確定完成操作。
第四步
- 合并后需要將數(shù)據(jù)返回到Excel中進(jìn)行使用，若有更加復(fù)雜的需求可導(dǎo)入的模型當(dāng)中進(jìn)行使用。此處我們將合并后的數(shù)據(jù)返回到Excel中，點(diǎn)擊開始菜單下的關(guān)閉并上載。若直接點(diǎn)擊則會(huì)調(diào)整到Excel中，新建一個(gè)Excel工作表來顯示數(shù)據(jù)，若選擇關(guān)閉并上載至則會(huì)彈出選擇按鈕，選擇數(shù)據(jù)存放的位置。
- 關(guān)閉并上載
如果你對(duì)這部分不熟悉，直接點(diǎn)擊關(guān)閉并上載最簡單
-
關(guān)閉并上載至 根據(jù)自己的需要選擇存放的位置
關(guān)閉并上載至 -
可以選擇自己需要的位置，該步驟操作有人會(huì)報(bào)錯(cuò)，具體不再解釋，可以多嘗試幾種方式，如果搞不定可以用第一種方式直接關(guān)閉并上載。
-
我們返回的數(shù)據(jù)會(huì)被Excel自動(dòng)調(diào)整數(shù)據(jù)類型，下單時(shí)間列我們看到變成了數(shù)字，此處選中下單時(shí)間列，將數(shù)據(jù)格式調(diào)整為日期，也可也在剛剛PQ中在返回?cái)?shù)據(jù)到Excel之前給該列指定一個(gè)數(shù)據(jù)格式。
調(diào)整數(shù)據(jù)類型
總結(jié)：
一般來說文件的合并需要VBA代碼才能操作，而使用該方式我們雖然第一次麻煩一些，但是后續(xù)如果數(shù)據(jù)有變動(dòng)或者新增、刪除等操作，僅需在返回的數(shù)據(jù)上右擊選擇“刷新”即可自動(dòng)把我們剛剛的步驟全部計(jì)算一遍，分分鐘完成工作，是不是很爽呢？
該操作是一個(gè)數(shù)據(jù)加載的入門教程，如果我們搭建復(fù)雜的數(shù)據(jù)分析模型，類似這樣的數(shù)據(jù)加載和處理會(huì)非常多，而操作也非常復(fù)雜。
但是Power Query中很多工具欄按鈕與Excel很類似，可以通過點(diǎn)擊查看其功能，這將有助于你很快掌握基礎(chǔ)的使用方法。
