幾百個業(yè)務人員的數(shù)據(jù)反饋雳殊，幾十家分公司的每日匯總橘沥，各渠道數(shù)據(jù)的匯總，部門內(nèi)部數(shù)據(jù)的匯總等等夯秃，相信在很多沒有完善CRM數(shù)據(jù)系統(tǒng)的公司座咆，可能每天都都需要有人去做這樣的事情〔滞荩總之介陶，非高效沒意義的工作我們能自動化智能化的代替我們則會想方設法去代替。今天我們就用Excel中的PowerBI系列插件幫我們?nèi)ネ瓿蛇@樣的工作色建。
所以哺呜，今天我們的學習目標就分為以下兩方面：
完成上百甚至上千上萬份同格式的文件的匯總合并
結(jié)合PowerPivot完成上百萬數(shù)據(jù)的透視分析
如果覺得以上內(nèi)容對你有所幫助，我們就接著往下看：
PS:關于PowerQuery的簡單介紹可以關注我們【簡快Excel】查看歷史文章箕戳，有詳細介紹某残。
合并上百份報表的操作思路：
操作步驟：
第一步 將要合并的文件放到我們待合并文件夾
我們先放入10份文件測試，完成后再給大家演示100份文件的合并
第二步 我們在文件夾外新建一個Excel文件匯總數(shù)據(jù)使用
第三步 新建查詢開始鏈接我們的源數(shù)據(jù)
我們點擊數(shù)據(jù)標簽下邊的【新建查詢】→【從文件】→【從文件夾】陵吸，如下圖：
完成后點擊瀏覽找到我們的文件夾路徑點擊確定玻墅，也可以復制我們的路徑直接點擊確定即可。
然后我們點擊編輯壮虫，即可進入PowerQuery界面澳厢。
第四步 PowerQuery編輯獲取數(shù)據(jù)
從第三步的表里我們看到了這幾列包含了我們的文件名，后綴囚似，創(chuàng)建日期剩拢，修改日期，訪問日期谆构，以及他的文件夾路徑等屬性裸扶，就是沒有直接看到我們表里的數(shù)據(jù)框都，其實我們表里的數(shù)據(jù)是被以二進制的方式存儲在【Content】列了搬素。我們僅僅需要一個函數(shù)就能獲取其中的內(nèi)容呵晨，我們具體操作下。
1熬尺、添加列里摸屠，點擊自定義列，輸入=Excel.Workbook([Content],true)：
2粱哼、然后季二，我們刪除我們不需要的干擾列，我們按住ctrl選擇我們的表名稱[Name]列和[自定義]列
3揭措、展開自定義列胯舷，對Kind列篩選，讓其等于Sheet,避免表格中有表格引用绊含，或者區(qū)域名稱導致匯總出現(xiàn)重復或者部分錯誤桑嘶，這塊我們就能看到我們具體的每個工作簿中工作表的名稱，每個工作表的內(nèi)容將被存儲在了[Data]列躬充，我們隨便點擊一個單元格便能看到里邊的數(shù)據(jù)逃顶。
4、我們點擊[Data]列的展開按鈕充甚，去掉使用原始列名作為前綴的勾選以政，點擊確定。
5伴找、我們就看到數(shù)據(jù)已經(jīng)被合并好了盈蛮，沒有用的列我們刪除即可：
6、接著我們修改下數(shù)據(jù)類型即可技矮。
7眉反、我們點擊關閉并上載下拉功能，關閉并上載至穆役，按鈕,我們簡單解釋一下寸五，上邊的加載到表，就是加載到我們的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)勢劲适，我們復制原始數(shù)據(jù)10份，在待合并文件夾厢蒜。
10霞势、我們需要重新做一遍剛才的工作嗎烹植？NO!NO!NO!我們只需要刷新下我么的數(shù)據(jù)源即可，以下方法學習一種即可
刷新方法1：數(shù)據(jù)標簽下邊的全部刷新功能
刷新方法2：在當前表格里右鍵愕贡，點擊刷新
刷新方法3：在右側(cè)查詢界面草雕，右鍵刷新，如果沒有看到右側(cè)的工作表查詢的話固以，點擊數(shù)據(jù)標簽下的顯示查詢按鈕即可
11墩虹、點擊刷新，我們等待不到2s鐘的時間憨琳，100份報表就幫我們匯總完成了诫钓，有興趣的同學也可以測試下1000份。
關于PowerQuery文件夾的合并就介紹這么多篙螟，如果原始數(shù)據(jù)非Excel格式的話而是Csv的話尖坤，把Excel.Workbook替換為Csv.Document([Content],[Delimiter=”,”, Encoding=936]),其他操作都一樣。
接下來我們探索下如何利用這個方法實現(xiàn)千萬級別的數(shù)據(jù)透視分析
1闲擦、我們修改原始文件慢味，中兩個表的數(shù)據(jù)量到92萬行,保存關閉
2、直接刷新墅冷，我們看到表格會報錯纯路，因為我們可以看到右側(cè)加載了185萬行數(shù)，Excel文件里直接放不下了寞忿，好了我們看下怎么處理吧驰唬。
3、我們在我們右側(cè)我們的查詢上點擊右鍵腔彰，選擇【加載到】叫编，然后如下圖所示選擇滚躯，點擊確定时迫。
4、我們點擊PowerPivot下的管理按鈕骗露，查看下我們的數(shù)據(jù)杯拐，如果沒有PowerPivot標簽點擊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分析的能力做了一個很大的突破墩衙。
其實關于Excel中商務智能系列套件對我們未來的很多分析業(yè)務需求提供了更多的可能，本文所用示例文件可以加群198086726獲取甲抖，更多文章歡迎關注【簡快Excel】（PowerBIPro）如果本文對你有幫助漆改，歡迎點贊轉(zhuǎn)發(fā)。
本文作者：雷公子准谚，如需轉(zhuǎn)載請聯(lián)系授權
