幾百個業(yè)務人員的數(shù)據(jù)反饋，幾十家分公司的每日匯總，各渠道數(shù)據(jù)的匯總，部門內(nèi)部數(shù)據(jù)的匯總等等，相信在很多沒有完善CRM數(shù)據(jù)系統(tǒng)的公司，可能每天都都需要有人去做這樣的事情?？傊?，非高效沒意義的工作，能自動化、智能化的代替，我們則會想方設法去代替。今天就用Excel中的PowerBI系列插件幫我們?nèi)ネ瓿蛇@樣的工作。
所以，今天的學習目標就分為以下兩方面：
完成上百甚至上千上萬份同格式的文件的匯總合并
結(jié)合PowerPivot完成上百萬數(shù)據(jù)的透視分析
如果覺得以上內(nèi)容對您有所幫助，請接著往下看。
合并上百份報表的操作思路：
第一步：將要合并的文件放到待合并文件夾
先放入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、現(xiàn)在就看到數(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)勢：復制原始數(shù)據(jù)10份，在待合并文件夾。
10、我們需要重新做一遍剛才的工作嗎？NO!NO!NO!我們只需要刷新下數(shù)據(jù)源即可。
以下方法學習一種即可：
刷新方法1：數(shù)據(jù)標簽下邊的全部刷新功能。
刷新方法2：在當前表格里右鍵，點擊刷新。
刷新方法3：在右側(cè)查詢界面，右鍵刷新，如果沒有看到右側(cè)的工作表查詢的話，點擊數(shù)據(jù)標簽下的顯示查詢按鈕即可。
11、點擊刷新，我們只需等待不到2s鐘的時間，100份報表就匯總完成了。（有興趣的同學也可以測試下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標簽點擊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è)務需求提供了更多的可能。
袁雷
雷公子
互聯(lián)網(wǎng)搜索引擎營銷經(jīng)理
數(shù)據(jù)分析展現(xiàn)狂熱愛好者
公眾號：
簡快Excel之PowerBI建模分析
聯(lián)系客服
