| 本帖最后由 張文洲 于 2016-9-1 11:20 編輯 最近看到關(guān)于用Power Query（以下簡(jiǎn)稱PQ）合并同一文件夾下Excel工作簿某固定名稱工作表的示例，得到啟發(fā)，研究了用PQ合并文件夾（含子文件夾）下所有Excel工作表所有工作表的方法，特和大家分享。 關(guān)于什么是PQ，PQ的基礎(chǔ)應(yīng)用知識(shí)，之前我已經(jīng)寫了兩篇帖子，有興趣的學(xué)友可以看看 快來學(xué)習(xí) Power Query 吧，屌爆了的Excel加載項(xiàng) 一步綜合介紹 Power Query 使用方法 更多視頻：  不需要使用VBA和SQL語句，就能快速合并工作簿，用PQ實(shí)現(xiàn)的步驟如下： 一、模擬數(shù)據(jù)背景 我桌面有一個(gè)文件夾“父文件夾”，里面有兩個(gè)工作簿 車間1和車間2，還有一個(gè)子文件夾，子文件夾里面同樣有兩個(gè)工作簿，車間3和車間4。四個(gè)工作簿里面各有5張工作表，工作表名稱分別為1月、2月、3月、4月、5月。每張工作表內(nèi)容是一樣的，兩列，名稱和數(shù)量。 為便于理解，以下是截圖。    二、查看PO查詢語句，構(gòu)建自定義函數(shù) 1、PO選項(xiàng)卡-從文件-從文件夾   2、導(dǎo)入文件夾及文件目錄信息以后，看到里面有路徑及文件名稱的信息，這是之后要用到的。 3、可以看到父文件夾下所有工作簿信息都已經(jīng)列示出來，我們單擊第一列標(biāo)題左邊擴(kuò)展按鈕， 4.這里顯示了第一個(gè)工作簿5張工作表名稱，第二列，當(dāng)我們單擊某一格，會(huì)立即顯示該月份的數(shù)據(jù)。這里我們需要 所有工作表的數(shù)據(jù)，因此先點(diǎn)data標(biāo)題右邊擴(kuò)展按鈕 去掉最下面的默認(rèn)√，點(diǎn)確定 看看，這里已經(jīng)顯示了車間1月份1到月份5的全部數(shù)據(jù)，包括標(biāo)題行。我們且用篩選去掉標(biāo)題行 篩選第二列=名稱的所有行 然后切換到視圖選項(xiàng)卡，點(diǎn)高級(jí)編輯器 在編輯器里面看到一串代碼，不懂不要緊，只看到里面唯一的自定義內(nèi)容就是文件路徑（注意路徑最末尾沒有\(zhòng)分隔符），我們且在這段代碼首位各加一句，然后把路徑各一個(gè)自定義變慢名稱 這里實(shí)際上是構(gòu)建了一個(gè)名為 combdata 的自定義函數(shù)，有一個(gè)參數(shù)folderpath 我們點(diǎn)完成，并給這個(gè)查詢定義一個(gè)熟悉的名稱Combdata 切換到開始選項(xiàng)卡，點(diǎn)關(guān)閉并上載 如此，右邊已經(jīng)建立了一個(gè)自定義函數(shù) 三、應(yīng)用自定義函數(shù)提取數(shù)據(jù) 接下來新建一個(gè)連接，從文件夾導(dǎo)入數(shù)據(jù)，注意到路徑列，最后又一個(gè)\符號(hào)，我們需要去掉。 PQ里面有兩個(gè)函數(shù)，一個(gè)是 Text.Length ，相當(dāng)于工作表中 len函數(shù)，另一個(gè) Text.Start ，相當(dāng)于 工作表 left函數(shù)。 特別要強(qiáng)調(diào)的是，PQ函數(shù)嚴(yán)格區(qū)分大小寫，不同于工作表函數(shù) 我們只需要用 Text.Start([Folder Path],Text.Length([Folder Path])-1) 就可以去掉最末尾的 \ 符號(hào)了 這個(gè)公式結(jié)果就可以供前面設(shè)計(jì)的自定義函數(shù) Combdata作為參數(shù)使用了 我們添加一個(gè)自定義列，設(shè)置公式為 =Combdata(Text.Start([Folder Path],Text.Length([Folder Path])-1)) 看看，多出了一列自定義列，內(nèi)容顯示的都table類型哦 同樣，我們擴(kuò)展最后一列看看效果 這里已經(jīng)將4個(gè)工作簿20張工作表全部合并起來了。我們刪除不要的列，就可以加載到工作表了，右鍵可以刷新 以上介紹完畢 需要注意的細(xì)節(jié)： 1、以上為了合并文件夾（含子文件夾）全部工作表所有工作表的數(shù)據(jù)，因此第一次構(gòu)建函數(shù)時(shí)用的是從文件夾導(dǎo)入數(shù)據(jù)，在此基礎(chǔ)上更改查詢代碼。 如果只是合并某特定文件夾（不含子文件夾）或者某特定工作表（如所有工作簿的第一張工作表1月），那么可以使用從工作簿導(dǎo)入數(shù)據(jù)的方式，在此基礎(chǔ)上修改代碼構(gòu)建函數(shù)，相對(duì)也簡(jiǎn)單的多。 2、PQ中的函數(shù)，比工作表函數(shù)豐富多了，也嚴(yán)格區(qū)分大小寫。如Text.Length 中兩個(gè)大寫字母不能小寫，其他小寫字母也不能大寫。 3、構(gòu)建PQ自定義函數(shù)以后，一定記得將查詢名稱重新定義一下，默認(rèn)的是查詢1，我上面改成了Combdata,注意后面自定義列中調(diào)用這個(gè)函數(shù)時(shí)用的是這個(gè)名稱而不是代碼中的 combdata ,當(dāng)然也可以不重新定義，直接用 查詢1(參數(shù))的形式調(diào)用。 4、關(guān)于PQ函數(shù)和語法的有關(guān)知識(shí)在哪里可以找得到呢？我在前面第一個(gè)基礎(chǔ)帖中已經(jīng)介紹了， 5、俗話說，舉一反三，上面介紹了合并Excel工作簿的方法，那么類似于Text格式文件或者網(wǎng)頁表格，是不是也可以用類似的方法來實(shí)現(xiàn)呢？ 6、聽說新一版的office預(yù)覽版已經(jīng)發(fā)布了，PQ不在作為Excel的外置插件，而是繼承在Excel程序本身，不知道是否屬實(shí)。不過從PQ的函數(shù)規(guī)范和語法規(guī)范來看，微軟還是在這一塊投入了不少精力。 就PQ函數(shù)而言，其無論是從數(shù)量還是功能來看，都比工作表函數(shù)豐富且強(qiáng)大的多，很多原來我們需要用VBA自定義函數(shù)實(shí)現(xiàn)的功能，這里面500多個(gè)函數(shù)，相信會(huì)讓你驚喜不斷。7、載入到Excel工作表以后，請(qǐng)?jiān)诒韺傩灾腥∠催x自動(dòng)調(diào)整列寬，這樣會(huì)是刷新效率提高 |
聯(lián)系客服
