不用VBA，不用SQL语句，且看 POWER QUERY 快速合并多个Excel工作簿

张文洲

最近看到关于用Power Query（以下简称PQ）合并同一文件夹下Excel工作簿某固定名称工作表的示例，得到启发，研究了用PQ合并文件夹（含子文件夹）下所有Excel工作表所有工作表的方法，特和大家分享。

关于什么是PQ，PQ的基础应用知识，之前我已经写了两篇帖子，有兴趣的学友可以看看
快来学习 Power Query 吧，屌爆了的Excel加载项

一步综合介绍 Power Query 使用方法
更多视频： 更多视频操作教程.rar (170 Bytes, 下载次数: 733)

2016-9-1 11:20 上传

点击文件名下载附件

不需要使用VBA和SQL语句，就能快速合并工作簿，用PQ实现的步骤如下：

一、模拟数据背景
    我桌面有一个文件夹“父文件夹”，里面有两个工作簿 车间1和车间2，还有一个子文件夹，子文件夹里面同样有两个工作簿，车间3和车间4。四个工作簿里面各有5张工作表，工作表名称分别为1月、2月、3月、4月、5月。每张工作表内容是一样的，两列，名称和数量。
为便于理解，以下是截图。






二、查看PO查询语句，构建自定义函数
1、PO选项卡-从文件-从文件夹







2、导入文件夹及文件目录信息以后，看到里面有路径及文件名称的信息，这是之后要用到的。

3、可以看到父文件夹下所有工作簿信息都已经列示出来，我们单击第一列标题左边扩展按钮，


4.这里显示了第一个工作簿5张工作表名称，第二列，当我们单击某一格，会立即显示该月份的数据。这里我们需要
所有工作表的数据，因此先点data标题右边扩展按钮

去掉最下面的默认√，点确定

看看，这里已经显示了车间1月份1到月份5的全部数据，包括标题行。我们且用筛选去掉标题行

筛选第二列=名称的所有行


然后切换到视图选项卡，点高级编辑器


在编辑器里面看到一串代码，不懂不要紧，只看到里面唯一的自定义内容就是文件路径（注意路径最末尾没有\分隔符），我们且在这段代码首位各加一句，然后把路径各一个自定义变慢名称


这里实际上是构建了一个名为 combdata 的自定义函数，有一个参数folderpath

我们点完成，并给这个查询定义一个熟悉的名称Combdata



切换到开始选项卡，点关闭并上载



如此，右边已经建立了一个自定义函数



三、应用自定义函数提取数据

接下来新建一个连接，从文件夹导入数据，注意到路径列，最后又一个\符号，我们需要去掉。
PQ里面有两个函数，一个是 Text.Length ，相当于工作表中 len函数，另一个 Text.Start ，相当于 工作表 left函数。
特别要强调的是，PQ函数严格区分大小写，不同于工作表函数
我们只需要用
Text.Start([Folder Path],Text.Length([Folder Path])-1)  就可以去掉最末尾的 \ 符号了
这个公式结果就可以供前面设计的自定义函数 Combdata作为参数使用了
我们添加一个自定义列，设置公式为
=Combdata(Text.Start([Folder Path],Text.Length([Folder Path])-1))







看看，多出了一列自定义列，内容显示的都table类型哦




同样，我们扩展最后一列看看效果



这里已经将4个工作簿20张工作表全部合并起来了。我们删除不要的列，就可以加载到工作表了，右键可以刷新










以上介绍完毕

需要注意的细节：
1、以上为了合并文件夹（含子文件夹）全部工作表所有工作表的数据，因此第一次构建函数时用的是从文件夹导入数据，在此基础上更改查询代码。
如果只是合并某特定文件夹（不含子文件夹）或者某特定工作表（如所有工作簿的第一张工作表1月），那么可以使用从工作簿导入数据的方式，在此基础上修改代码构建函数，相对也简单的多。
2、PQ中的函数，比工作表函数丰富多了，也严格区分大小写。如Text.Length  中两个大写字母不能小写，其他小写字母也不能大写。
3、构建PQ自定义函数以后，一定记得将查询名称重新定义一下，默认的是查询1，我上面改成了Combdata,注意后面自定义列中调用这个函数时用的是这个名称而不是代码中的 combdata  ,当然也可以不重新定义，直接用 查询1(参数)的形式调用。
4、关于PQ函数和语法的有关知识在哪里可以找得到呢？我在前面第一个基础帖中已经介绍了，


5、俗话说，举一反三，上面介绍了合并Excel工作簿的方法，那么类似于Text格式文件或者网页表格，是不是也可以用类似的方法来实现呢？
6、听说新一版的office预览版已经发布了，PQ不在作为Excel的外置插件，而是继承在Excel程序本身，不知道是否属实。不过从PQ的函数规范和语法规范来看，微软还是在这一块投入了不少精力。
就PQ函数而言，其无论是从数量还是功能来看，都比工作表函数丰富且强大的多，很多原来我们需要用VBA自定义函数实现的功能，这里面500多个函数，相信会让你惊喜不断。7、载入到Excel工作表以后，请在表属性中取消勾选自动调整列宽，这样会是刷新效率提高

cc1027cc

不明觉厉。我怎么感觉比VBA更复杂。
也许是不熟悉的原因吧

张文洲

cc1027cc 发表于 2015-3-11 14:11
不明觉厉。我怎么感觉比VBA更复杂。
也许是不熟悉的原因吧

这个比VBA简单多了，单纯的SQL查询语句也得与VBA结合才能遍历所有工作簿和工作表

wswfzwswfz

张文洲 发表于 2015-3-11 14:17
这个比VBA简单多了，单纯的SQL查询语句也得与VBA结合才能遍历所有工作簿和工作表

以后有空了学习下
呵呵。只知道POWER POVIOT和 MICRO QUERY
这个难道是两者的结合
嘿嘿

sayhi95

高大上的好东西，要学习啊！

Kelidai

方法比能力重要。

张文洲

用类似的原理合并网页列表多页数据也可以实现。我这里合并了本论坛某板块前5页的数据

yepulu

真的高大上。

lizhiman2005

请问，每个工作簿的工作表名称是否可以不一致？

看见星光

老师辛苦，学习啦{:soso_e183:}