作者简介:法叔,excel精英培训论坛版主,Excel函数精讲班讲师,微信公众号法叔office创始人。
年底,各部门报过来的数据,如何合并到一个文件里?
过去,我们只能使用VBA或编写SQL语句。
现在,我们只需点击几次鼠标,书写一个公式。
6个工作簿,数据结构都是一致的,我们需要把她们合并到一个工作簿里。
【数据>新建查询>从文件>从文件夹】。
【浏览】,找到需要合并的文件夹。
文件夹下每一个工作簿都被合并在一起。首列“内容”显示【Binary】,是二进制数据的意思。
最后一列显示这些工作簿的地址。中间几列分别表示工作簿名称、后缀名、访问时间、修改时间、创建时间和文件属性。
点击【编辑】,进入【查询编辑器】,中间那几列无用,所以右键单击【列标签>删除列】。
如果此时直接点击二进制首列的"展开按钮",会出现错误提示。
这是因为,二进制数据无法直接提取。我们需要书写一条公式。
在【查询编辑器】点击【添加列>添加自定义列】。
在【添加自定义列】对话框,保留默认的【新列名】,在【自定义公式】列表框录入公式:
=Exel.Workbook([Content],true)
注意,公式函数严格区分大小写(首字母大写)。
函数的第一个参数是需要转换的二进制字段,这个字段可以在右侧列表框双击选择,不必手工录入。
函数的第二个参数是逻辑值,如果原数据有标题行,这里应该添写true。
点击【确定】后,【查询编辑器】新增一列,数据类型显示为【Table】,右侧的【应用步骤】列表显示了刚刚进行的步骤。
随便选择【Table】数据的一个单元格,下方预览区会显示这个表的结构。
点击新增列标签右侧“展开按钮”,选择【扩展】。
每一个【Table】表会按列方向展开。其中Data数据类型仍然显示【Table】。
我们再次点击【Table】数据列标签右侧的“展开按钮”。
展开的数据已经将文件夹下所有工作簿合并在一起。
删除一些不需要的列。
只留有效数据列,点击【开始>关闭并上载】返回Excel。
所有数据都已经合并到一个工作簿中。
得到的合并数据实际上是一个【查询】,右键单击可以【刷新】数据。
当文件夹下原工作簿内容变更,合并工作簿只要【刷新】一次,即可更新数据。
展开【Table】数据时,如果选择【聚合】,得到的数据会将同类项求和或计数。
怎么样,是不是比VBA要简单的多啊。
本文来自微信公众号:法叔office,经作者授权发布
联系客服