• 技术文章 >专题 >excel

    实用Excel技巧分享:利用Power Query合并文件夹里的工作簿

    青灯夜游青灯夜游2022-11-14 19:54:56转载387

    手机EXCEL下载(内含百种各行模版):点击查看

    之前给大家介绍了利用EXCEL的新功能Power Query实现汇总工作簿里的工作表,但Power Query的功能远远不止于此,今天就给大家介绍个更高级的合并技巧:利用Power Query合并文件夹里的工作簿。

    如下图,在桌面“销售”文件夹里放有四个地区的销售数据。每个工作簿里的标题名都是一致的,顺序可以不一样。每个工作簿里城市这一列的值就是工作簿的名称,方便后续看合并效果。

    Excel一键生成报表教程:powerquery合并文件夹

    Power Query

    操作如下:

    关闭文件夹里的文件,新建工作簿,点击数据选项卡下,[获取和转换]组里“新建查询”---“从文件”---“从文件夹”。

    Excel工作表合并

    把文件夹路径输入进去,也可以通过浏览选择文件夹所在位置,点击确定。

    这个界面把文件夹的所有工作簿都列出来了,点击编辑。

    进入了Power Query编辑器界面。上方是菜单栏,中间是表格区域也是最后要返回到工作表的数据,右侧查询设置窗口显示的是Power Query的操作记录。

    之前介绍过,“Content”这一列绿色字体代表这个单元格里包含了一个文件。点击单元格可以预览里面的内容。(注意:预览单元格里面的内容时应该把鼠标放在单元格内空白处,不要放在文字上面,点击文字会直接打开单元格里的文件)由于文件从文件夹直接提取过来都是binary格式,所以下方预览窗格出现的是二进制格式的工作簿。

    Binary作为二进制文件是无法在表格显示的,那我们要做的就是把它变成table格式然后把数据展开到表里。先把其他不需要的列删除。

    选择“Content”这一列,点击开始选项卡下,[管理列]组里“删除列”—“删除其他列”。

    这样除了“Content”这列之外的其他列都被删除了。

    那怎么把二进制文件转换成普通的表格呢,需要用到Power Query的专用编程语言—M语言。这里给大家介绍一个常用的函数。

    点击添加列选项下的[常规]组里的“自定义列”。

    在自定义列窗口,“自定义列公式”里输入 =Excel.Workbook([Content],true),其中“[Content]”可以点击右侧可用列里的“Content”,再点击右下角插入即可(注意:公式的大小写千万不能错)。

    公式解析:

    Excel.Workbook

    功能:从 Excel 工作簿返回工作表的记录。

    参数:Excel.Workbook(workbook as binary, optional useHeaders as nullable logical, optionaldelayTypes as nullable logical) as table

    这个函数返回一个table,第一参数workbook是binary格式,第二参数是可选参数逻辑值,true表示把原来表格的标题作为新表格的标题,默认是false表示用新列名代替原来工作表的标题。第三参数不用管。

    这里我们还是采用表格原先的标题,所以填true。这样就省去了后续还要提升第一行为标题的步骤。

    新列就添加成功了,预览其中一个单元格,下方显示的是就是一个表格样式的工作簿了。这样的就可以直接扩展到表里了。

    “Data”这一列显示的就是Table格式的表格,包含了表格里的数据,这里我们只需要提取这一列就可以了。点击自定义列右上方扩展按钮,选择扩展列“Data”,不要勾选“使用原始列名作为前缀”。

    列名就变成了“Data”。这时我们再预览“Data”里的数据,下方出现的就是表格里面的原始数据。再把下方数据全部提取出来。

    同样点击自定义列右上方扩展按钮,选择扩展所有列,不要勾选“使用原始列名作为前缀”。

    这样我们就通过逐层钻取获得了工作表里的数据。

    最后把“Content”这一列删除。选择“Content”这一列,右键删除即可。

    最后把这个表上载到表格就可以了。

    点击开始选项卡下,[关闭]组里“关闭并上载”。

    这样数据就汇总到工作表了。

    当点击“城市”这一列的筛选按钮,看到四个工作簿里的数据都在表里。

    那当文件夹多了一个工作簿会如何?在这个文件夹尝试放一个新的工作簿“西安”。

    回到刚才做数据统计的表格里,点击数据选项卡下的[连接]组里的“全部刷新”。

    城市这一列就多了“西安”,代表这个新工作簿的数据就被添加进来了。

    小结:Power Query合并文件夹,只要每个工作表里的标题相同就可以进行合并汇总,这种方法不管文件夹有多少工作簿都能进行合并。并且任何数据变动都能通过全部刷新一键更新。

    Power Query作为EXCEL数据分析的利器,通过简单的图形化操作,结合自带的M语言并通过操作记录器,帮助我们把更多数据进行统一操作,快速完成数据的处理和优化。而且它跟VBA相比上手快、易操作,图形化操作就能满足我们大部分的需求。大家赶紧学起来吧!

    相关学习推荐:excel教程

    以上就是实用Excel技巧分享:利用Power Query合并文件夹里的工作簿的详细内容,更多请关注php中文网其它相关文章!

    声明:本文转载于:部落窝教育,如有侵犯,请联系admin@php.cn删除

    前端(VUE)零基础到就业课程:点击学习

    清晰的学习路线+老师随时辅导答疑

    自己动手写 PHP MVC 框架:点击学习

    快速了解MVC架构、了解框架底层运行原理

    手机EXCEL
    专题推荐:Excel
    上一篇:Excel中COUNTIF的两种高能用法更新 下一篇:自己动手写 PHP MVC 框架(40节精讲/巨细/新人进阶必看)

    相关文章推荐

    • ❤️‍🔥共22门课程,总价3725元,会员免费学• ❤️‍🔥接口自动化测试不想写代码?• 实例解析Excel:把混合在同一单元格中的姓名拆分成一列• Excel函数学习之聊聊countif()的使用方法(案例详解)• Excel案例分享:怎么用Excel做库存电子台账?(出入库台账汇总)• Excel图表学习之实际和目标对比的柱状图• Excel函数学习之聊聊N()--转换为数值的函数• Excel中COUNTIF的两种高能用法更新
    1/1

    PHP中文网