ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 效率神器,一键搞定繁琐工作
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
让更多数据处理,一键完成 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
楼主: 张文洲

[分享] 不用VBA,不用SQL语句,且看 POWER QUERY 快速合并多个Excel工作簿

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2015-7-30 16:13 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖已被收录到知识树中,索引项:其他官方插件
试了一下,果然可行,不用VBA代码和sql语句就能实现多文件夹多工作簿多工作表的汇总,实在是太牛了!!学习

TA的精华主题

TA的得分主题

发表于 2015-8-4 15:19 | 显示全部楼层
let
    源 = Folder.Files("C:\Users\Administrator\Desktop\新建文件夹"),
    合并的二进制 = Binary.Combine(源[Content]),
    #"导入的 Excel" = Excel.Workbook(合并的二进制)   
in
    #"导入的 Excel"

#"导入的 Excel" = Excel.Workbook(合并的二进制)   这个步骤的时候报错了,错误为“DataFormat.Error: 无法将输入识别为有效的 Excel 文档。详细信息:
    Binary”  不知道什么原因,请问怎么解决这个问题呀

TA的精华主题

TA的得分主题

发表于 2015-8-5 21:26 | 显示全部楼层
function2046 发表于 2015-8-4 15:19
let
    源 = Folder.Files("C:\Users\Administrator\Desktop\新建文件夹"),
    合并的二进制 = Binary. ...

这个问题我也遇到了,我采用 比较笨的方法,把原数据的表复制粘贴到另一个新表,后来就成功了,现在也不知道是什么原因。

TA的精华主题

TA的得分主题

发表于 2015-9-6 00:52 | 显示全部楼层
本帖最后由 anthonii 于 2015-9-6 20:21 编辑

楼主,我试了多次,在你的第3步“3、可以看到父文件夹下所有工作簿信息都已经列示出来,我们单击第一列标题左边扩展按钮,”这个地方,点Content右边的扩展按钮是出不来的,会提示如下信息:
  1. DataFormat.Error: 外部表不是预期的格式。
  2. 详细信息:
  3.     97386d52-9f57-42b2-bbf1-dc825b53a653.XLS
复制代码
不知道是不是我们的query版本不一样,我现在用的是最新版本的

我想这个地方出不来是因为下面有多个xlsx文件,试着把其他三个筛选掉只保留一个就可以了,代码可以这样写:
  1. let
  2.     源 = Folder.Files("C:\Users\Administrator\Desktop\power query学习\父文件夹"),
  3.     筛选的行 = Table.SelectRows(源, each ([Name] = "车间1.xlsx")),
  4.     合并的二进制 = Binary.Combine(筛选的行[Content]),
  5.     #"导入的 Excel" = Excel.Workbook(合并的二进制)
  6. in
  7.     #"导入的 Excel"
复制代码
试验成功

我看到你的查询1代码是类似这样的:
  1. let
  2.     源 = Folder.Files("C:\Users\Administrator\Desktop\power query学习\父文件夹"),
  3.     合并的二进制 = Binary.Combine(源[Content]),
  4.     #"导入的 Excel" = Excel.Workbook(合并的二进制)
  5. in
  6.     #"导入的 Excel"
复制代码
我试了试还是不行,就是上面说的那个原因,除非把代码改为:
  1. let
  2.     源 = Folder.Files("C:\Users\Administrator\Desktop\power query学习\父文件夹"),
  3.     合并的二进制 = 源{[#"Folder Path"="C:\Users\Administrator\Desktop\power query学习\父文件夹",Name="车间1.xlsx"]}[Content],
  4.     #"导入的 Excel" = Excel.Workbook(合并的二进制)
  5. in
  6.     #"导入的 Excel"
复制代码
也就是说,还是需要指定"车间1.xlsx"这个文件

主要是我不理解Binary.Combine这个函数是什么意思,对于多个xlsx文件来说,我觉得这个函数可能是没法用的吧。

另外在第3步的时候,在Content列上点右键点“深化”,然后在List里点某个文件的Binary也行,这样代码就成了:
  1. let
  2.     源 = Folder.Files("C:\Users\Administrator\Desktop\power query学习\父文件夹"),
  3.     Content = 源[Content],
  4.     Content1 = Content{0},
  5.     #"导入的 Excel" = Excel.Workbook(Content1)
  6. in
  7.     #"导入的 Excel"
复制代码
其实仔细看,它还是指定了某个xlsx文件,所以我觉得,对于多个xlsx文件来说,直接点Content右边的扩展是不行的




评分

1

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2015-9-6 12:27 | 显示全部楼层
anthonii 发表于 2015-9-6 00:52
楼主,我试了多次,在你的第3步“3、可以看到父文件夹下所有工作簿信息都已经列示出来,我们单击第一列标题 ...

是的,我当时录图片的时候,没有仔细验证最后的结果。
需要同时指定路径和工作簿名,需要两个参数。

TA的精华主题

TA的得分主题

发表于 2015-9-6 20:48 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖最后由 anthonii 于 2015-9-6 22:52 编辑

又试了试,如果只是需要提取文件夹下所有工作薄里的所有数据的话,可以自定义列加入公式来解决,添加自定义列Custom,输入公式Excel.Workbook([Content]),可以看到这里的Custom全部都是Table,这里的Table指的是每个工作薄,一共有四个,每个里面包括Name,Data,Item,Kind,Hidden这几列,每一行是每张工作表,展开Data列,得到每张Table,这里的Table就是每个工作表了,一共有20个Table也就是20张工作表,每个Table有两列,Column1和Column2,每列里就是具体内容了,展开这两列,就得到了父级文件夹下所有工作薄里的所有工作表里的所有数据了,然后筛选去掉“名称”、“数量”这些字段名称的行就等到了想要的结果,最终代码是:
  1. let
  2.     源 = Folder.Files("C:\Users\Administrator\Desktop\power query学习\父文件夹"),
  3.     已添加自定义 = Table.AddColumn(源, "Custom", each Excel.Workbook([Content])),
  4.     #"展开的“Custom”" = Table.ExpandTableColumn(已添加自定义, "Custom", {"Data"}, {"Data"}),
  5.     #"展开的“Data”" = Table.ExpandTableColumn(#"展开的“Custom”", "Data", {"Column1", "Column2"}, {"Column1", "Column2"})
  6. in
  7.     #"展开的“Data”"
复制代码

TA的精华主题

TA的得分主题

发表于 2015-10-28 11:33 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
如果是合并某特定文件夹(不含子文件夹)下的几个工作簿,具体需要怎么操作?
和楼主介绍的特定文件夹(含子文件夹)感觉很不一样,自己也没有代码基础,求指教

TA的精华主题

TA的得分主题

发表于 2015-11-27 18:58 | 显示全部楼层
function2046 发表于 2015-7-28 15:00
在第三步的时候出现:“DataFormat.Error: 无法将输入识别为有效的 Excel 文档”提示是什么情况呀

请问是怎么解决的啊?我也出现了这个问题

TA的精华主题

TA的得分主题

发表于 2016-1-6 15:34 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2016-2-22 16:51 | 显示全部楼层
没有看太明白,假如将整个文件夹更换了位置,是否每次都需要把路径名称更改?
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-12-25 15:24 , Processed in 0.034034 second(s), 8 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表