完成上百甚至上千上萬份同格式的文件的匯總合并
結(jié)合 PowerPivot 完成上百萬數(shù)據(jù)的透視分析
如果覺得以上內(nèi)容對你有所幫助，我們就接著往下看：
PS: 關(guān)于 PowerQuery 的簡單介紹可以關(guān)注我們【簡快 Excel】查看歷史文章，有詳細(xì)介紹街立。
合并上百份報表的操作思路：
操作步驟：
第一步 將要合并的文件放到我們待合并文件夾
我們先放入 10 份文件測試行楞，完成后再給大家演示 100 份文件的合并
第二步 我們在文件夾外新建一個 Excel 文件匯總數(shù)據(jù)使用
第三步 新建查詢開始鏈接我們的源數(shù)據(jù)
我們點擊數(shù)據(jù)標(biāo)簽下邊的【新建查詢】→【從文件】→【從文件夾】磨澡，如下圖：
完成后點擊瀏覽找到我們的文件夾路徑點擊確定岛都，也可以復(fù)制我們的路徑直接點擊確定即可蚤认。
然后我們點擊編輯凹蜈，即可進(jìn)入 PowerQuery 界面限寞。
第四步 PowerQuery 編輯獲取數(shù)據(jù)
從第三步的表里我們看到了這幾列包含了我們的文件名，后綴仰坦，創(chuàng)建日期履植，修改日期，訪問日期悄晃，以及他的文件夾路徑等屬性玫霎，就是沒有直接看到我們表里的數(shù)據(jù)凿滤，其實我們表里的數(shù)據(jù)是被以二進(jìn)制的方式存儲在【Content】列了。我們僅僅需要一個函數(shù)就能獲取其中的內(nèi)容庶近，我們具體操作下翁脆。
1、添加列里鼻种，點擊自定義列反番，輸入=Excel.Workbook([Content],true)：
注意：公式需要區(qū)分大寫
2、然后叉钥，我們刪除我們不需要的干擾列罢缸，我們按住 ctrl 選擇我們的表名稱 [Name] 列和 [自定義] 列
3、展開自定義列投队，對 Kind 列篩選枫疆，讓其等于 Sheet, 避免表格中有表格引用，或者區(qū)域名稱導(dǎo)致匯總出現(xiàn)重復(fù)或者部分錯誤蛾洛，這塊我們就能看到我們具體的每個工作簿中工作表的名稱养铸，每個工作表的內(nèi)容將被存儲在了 [Data] 列，我們隨便點擊一個單元格便能看到里邊的數(shù)據(jù)轧膘。
4钞螟、我們點擊 [Data] 列的展開按鈕，去掉使用原始列名作為前綴的勾選谎碍，點擊確定鳞滨。
5、我們就看到數(shù)據(jù)已經(jīng)被合并好了蟆淀，沒有用的列我們刪除即可：
6拯啦、接著我們修改下數(shù)據(jù)類型即可。
7熔任、我們點擊關(guān)閉并上載下拉功能褒链，關(guān)閉并上載至，按鈕, 我們簡單解釋一下疑苔，上邊的加載到表甫匹，就是加載到我們的 EXCEL 界面，數(shù)據(jù)量在 100 萬以內(nèi)的選擇這個沒有任何問題惦费。下邊的【將此數(shù)據(jù)添加到數(shù)據(jù)模型】勾選后可以將查詢獲取的匯總數(shù)據(jù)加載到 PowerPivot 中兵迅，PoewrPivot 配合僅創(chuàng)建連接表（即不直接存儲數(shù)據(jù)到 Excel 表格區(qū)域）
8、我們先選擇加載到表薪贫，看下效果恍箭，我們看到 10 個表 460 行的數(shù)據(jù)就被我們加載完成了：
9、我們探索下 100 + 的表格的合并效果以及我們 PowerQuery 的優(yōu)勢瞧省，我們復(fù)制原始數(shù)據(jù) 10 份扯夭，在待合并文件夾鳍贾。
10、我們需要重新做一遍剛才的工作嗎交洗？NO!NO!NO! 我們只需要刷新下我么的數(shù)據(jù)源即可贾漏，以下方法學(xué)習(xí)一種即可
刷新方法 1：數(shù)據(jù)標(biāo)簽下邊的全部刷新功能
刷新方法 2：在當(dāng)前表格里右鍵，點擊刷新
刷新方法 3：在右側(cè)查詢界面藕筋，右鍵刷新，如果沒有看到右側(cè)的工作表查詢的話梳码，點擊數(shù)據(jù)標(biāo)簽下的顯示查詢按鈕即可
11隐圾、點擊刷新，我們等待不到 2s 鐘的時間掰茶，100 份報表就幫我們匯總完成了暇藏，有興趣的同學(xué)也可以測試下 1000 份。
關(guān)于 PowerQuery 文件夾的合并就介紹這么多濒蒋，如果原始數(shù)據(jù)非 Excel 格式的話而是 Csv 的話盐碱，把 Excel.Workbook 替換為 Csv.Document([Content],[Delimiter=”,”, Encoding=936]), 其他操作都一樣。
接下來我們探索下如何利用這個方法實現(xiàn)千萬級別的數(shù)據(jù)透視分析
1沪伙、我們修改原始文件瓮顽，中兩個表的數(shù)據(jù)量到 92 萬行, 保存關(guān)閉
2、直接刷新围橡，我們看到表格會報錯暖混，因為我們可以看到右側(cè)加載了 185 萬行數(shù)，Excel 文件里直接放不下了翁授，好了我們看下怎么處理吧拣播。
3、我們在我們右側(cè)我們的查詢上點擊右鍵收擦，選擇【加載到】贮配，然后如下圖所示選擇，點擊確定塞赂。
4泪勒、我們點擊 PowerPivot 下的管理按鈕，查看下我們的數(shù)據(jù)减途，如果沒有 PowerPivot 標(biāo)簽點擊 Ecxel 的【COM 加載項】勾選下即可酣藻，注意需要 13 或者 16 專業(yè)增強版以上才有此功能：
5、我們 185 萬的數(shù)據(jù)就被存儲在 Excel 的 PowerPivot 中的鳍置，接下來我們點擊插入透視表辽剧，選擇使用此工作簿的數(shù)據(jù)模型為源數(shù)據(jù)即可。
6税产、我們簡單拖拽看下效果怕轿，一個 185 萬行數(shù)據(jù)產(chǎn)生的透視表就被我們做出來了偷崩。
總結(jié)：
1、快速合并文件夾文件撞羽，我們看到我們僅需一個函數(shù) Excel.Workbook 配合可視化的一些操作即可完成阐斜。
2、后續(xù)只需要一鍵刷新即可完成匯總诀紊。
3谒出、通過 Excel 插件 PowerPivot 的配合我們完成了 185 萬行數(shù)據(jù)的透視表的創(chuàng)建，對傳統(tǒng) Excel 分析的能力做了一個很大的突破邻奠。
其實關(guān)于 Excel 中商務(wù)智能系列套件對我們未來的很多分析業(yè)務(wù)需求提供了更多的可能笤喳，本文所用示例文件可以加群 198086726 獲取，更多文章歡迎關(guān)注【簡快 Excel】（PowerBIPro）如果本文對你有幫助碌宴，歡迎點贊轉(zhuǎn)發(fā)杀狡。
文章轉(zhuǎn)載自https://zhuanlan.zhihu.com/p/27342107
