Excel一鍵生成報(bào)表教程:powerquery合并文件夾
?
作者:夏雪來(lái)源:部落窩教育發(fā)布時(shí)間:2018-08-27 14:02:51點(diǎn)擊:15561
小編有話說(shuō):之前小編給大家推送了Power Query合并工作簿的教程,相信看完的人都會(huì)感嘆它的強(qiáng)大。有很多小伙伴在問(wèn)如何合并文件夾,其實(shí)同樣可以使用Power Query,非??旖荩⑶矣行鹿ぷ鞑炯尤氲轿募A時(shí),還可以實(shí)時(shí)刷新,你一定不敢相信吧?下面我們來(lái)看看!
之前給大家介紹了利用EXCEL的新功能Power Query實(shí)現(xiàn)匯總工作簿里的工作表,但Power Query的功能遠(yuǎn)遠(yuǎn)不止于此,今天就給大家介紹個(gè)更高級(jí)的合并技巧:利用Power Query合并文件夾里的工作簿。
如下圖,在桌面“銷售”文件夾里放有四個(gè)地區(qū)的銷售數(shù)據(jù)。每個(gè)工作簿里的標(biāo)題名都是一致的,順序可以不一樣。每個(gè)工作簿里城市這一列的值就是工作簿的名稱,方便后續(xù)看合并效果。
操作如下:
關(guān)閉文件夾里的文件,新建工作簿,點(diǎn)擊數(shù)據(jù)選項(xiàng)卡下,[獲取和轉(zhuǎn)換]組里“新建查詢”---“從文件”---“從文件夾”。
把文件夾路徑輸入進(jìn)去,也可以通過(guò)瀏覽選擇文件夾所在位置,點(diǎn)擊確定。
這個(gè)界面把文件夾的所有工作簿都列出來(lái)了,點(diǎn)擊編輯。
進(jìn)入了Power Query編輯器界面。上方是菜單欄,中間是表格區(qū)域也是最后要返回到工作表的數(shù)據(jù),右側(cè)查詢?cè)O(shè)置窗口顯示的是Power Query的操作記錄。
之前介紹過(guò),“Content”這一列綠色字體代表這個(gè)單元格里包含了一個(gè)文件。點(diǎn)擊單元格可以預(yù)覽里面的內(nèi)容。(注意:預(yù)覽單元格里面的內(nèi)容時(shí)應(yīng)該把鼠標(biāo)放在單元格內(nèi)空白處,不要放在文字上面,點(diǎn)擊文字會(huì)直接打開單元格里的文件)由于文件從文件夾直接提取過(guò)來(lái)都是binary格式,所以下方預(yù)覽窗格出現(xiàn)的是二進(jìn)制格式的工作簿。
Binary作為二進(jìn)制文件是無(wú)法在表格顯示的,那我們要做的就是把它變成table格式然后把數(shù)據(jù)展開到表里。先把其他不需要的列刪除。
選擇“Content”這一列,點(diǎn)擊開始選項(xiàng)卡下,[管理列]組里“刪除列”—“刪除其他列”。
這樣除了“Content”這列之外的其他列都被刪除了。
那怎么把二進(jìn)制文件轉(zhuǎn)換成普通的表格呢,需要用到Power Query的專用編程語(yǔ)言—M語(yǔ)言。這里給大家介紹一個(gè)常用的函數(shù)。
點(diǎn)擊添加列選項(xiàng)下的[常規(guī)]組里的“自定義列”。
在自定義列窗口,“自定義列公式”里輸入 =Excel.Workbook([Content],true),其中“[Content]”可以點(diǎn)擊右側(cè)可用列里的“Content”,再點(diǎn)擊右下角插入即可(注意:公式的大小寫千萬(wàn)不能錯(cuò))。
公式解析:
Excel.Workbook
功能:從 Excel 工作簿返回工作表的記錄。
參數(shù):Excel.Workbook(workbook as binary, optional useHeaders as nullable logical, optional delayTypes as nullable logical) as table
這個(gè)函數(shù)返回一個(gè)table,第一參數(shù)workbook是binary格式,第二參數(shù)是可選參數(shù)邏輯值,true表示把原來(lái)表格的標(biāo)題作為新表格的標(biāo)題,默認(rèn)是false表示用新列名代替原來(lái)工作表的標(biāo)題。第三參數(shù)不用管。
這里我們還是采用表格原先的標(biāo)題,所以填true。這樣就省去了后續(xù)還要提升第一行為標(biāo)題的步驟。
新列就添加成功了,預(yù)覽其中一個(gè)單元格,下方顯示的是就是一個(gè)表格樣式的工作簿了。這樣的就可以直接擴(kuò)展到表里了。
“Data”這一列顯示的就是Table格式的表格,包含了表格里的數(shù)據(jù),這里我們只需要提取這一列就可以了。點(diǎn)擊自定義列右上方擴(kuò)展按鈕,選擇擴(kuò)展列“Data”,不要勾選“使用原始列名作為前綴”。
列名就變成了“Data”。這時(shí)我們?cè)兕A(yù)覽“Data”里的數(shù)據(jù),下方出現(xiàn)的就是表格里面的原始數(shù)據(jù)。再把下方數(shù)據(jù)全部提取出來(lái)。
同樣點(diǎn)擊自定義列右上方擴(kuò)展按鈕,選擇擴(kuò)展所有列,不要勾選“使用原始列名作為前綴”。
這樣我們就通過(guò)逐層鉆取獲得了工作表里的數(shù)據(jù)。
最后把“Content”這一列刪除。選擇“Content”這一列,右鍵刪除即可。
最后把這個(gè)表上載到表格就可以了。
點(diǎn)擊開始選項(xiàng)卡下,[關(guān)閉]組里“關(guān)閉并上載”。
這樣數(shù)據(jù)就匯總到工作表了。
當(dāng)點(diǎn)擊“城市”這一列的篩選按鈕,看到四個(gè)工作簿里的數(shù)據(jù)都在表里。
那當(dāng)文件夾多了一個(gè)工作簿會(huì)如何?在這個(gè)文件夾嘗試放一個(gè)新的工作簿“西安”。
回到剛才做數(shù)據(jù)統(tǒng)計(jì)的表格里,點(diǎn)擊數(shù)據(jù)選項(xiàng)卡下的[連接]組里的“全部刷新”。
城市這一列就多了“西安”,代表這個(gè)新工作簿的數(shù)據(jù)就被添加進(jìn)來(lái)了。
小結(jié):Power Query合并文件夾,只要每個(gè)工作表里的標(biāo)題相同就可以進(jìn)行合并匯總,這種方法不管文件夾有多少工作簿都能進(jìn)行合并。并且任何數(shù)據(jù)變動(dòng)都能通過(guò)全部刷新一鍵更新。
Power Query作為EXCEL數(shù)據(jù)分析的利器,通過(guò)簡(jiǎn)單的圖形化操作,結(jié)合自帶的M語(yǔ)言并通過(guò)操作記錄器,幫助我們把更多數(shù)據(jù)進(jìn)行統(tǒng)一操作,快速完成數(shù)據(jù)的處理和優(yōu)化。而且它跟VBA相比上手快、易操作,圖形化操作就能滿足我們大部分的需求。大家趕緊學(xué)起來(lái)吧!
本文配套的練習(xí)課件請(qǐng)加入QQ群:264539405下載。
如果您因工作所需使用到Excel,不妨關(guān)注部落窩教育的《一周Excel直通車》視頻課或《Excel極速貫通班》直播課系統(tǒng)學(xué)習(xí)。
如果您想要隨時(shí)隨地學(xué)習(xí)excel,掃下方二維碼,可關(guān)注公眾號(hào),每日為您推送優(yōu)質(zhì)excel教程:
Excel教程相關(guān)推薦:
《countif函數(shù)的使用方法以及countif函數(shù)查重復(fù)等5個(gè)案例分享》最熱教程
- 像綠皮火車一樣長(zhǎng)像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問(wèn)題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說(shuō)是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請(qǐng)收好!
最新教程
- 一個(gè)公式搞定空行的動(dòng)態(tài)插入
- 多功能銷售日歷模板制作
- 用正則函數(shù)快速拆分單元格中的同類數(shù)據(jù)
- 用免費(fèi)AI將圖片批量轉(zhuǎn)表格
- 先入先出出入庫(kù)模板中出庫(kù)批次的自動(dòng)匹配
- 用SUBSTITUTE替換法為產(chǎn)品找到最匹配的類別
- SHEETSNAME,一鍵獲得所有工作表名稱完成目錄制作
- 延遲退休落地,快速查詢你上幾年才退休!
- 快速將PDF電子發(fā)票數(shù)據(jù)提取到發(fā)票明細(xì)登記表的方法
- 提取、查找、替換數(shù)據(jù)的王者——REGEXP正則函數(shù)