Excel多工作表汇总,多工作薄汇总,学会一个M函数就够了
数据汇总你会这么做?相信大多数人都会异口同声的回答到:复制粘贴,那如果有1000个表格呢?你还要复制粘贴吗?
今天跟大家分享一个函数,轻松搞定数据汇总。它就是:Excel.Workbook这个函数是一个M函数,需要在PowerQurey 中才能使用,也就是需要Excel的版本最低在2016版,它能自动识别表头,动态更新数据非常的方便,下面就让我们来学习下吧
一、Excel.Workbook
Excel.Workbook:返回工作薄的内容,他获取的结果是一个表格
语法:= Excel.Workbook(workbook,useheaders,delaytypes)
第一参数:workbook,工作薄所在路径,参数类型为binary,也就是一个二进制文件
第二参数:useheaders,是否要将表格的第一行作为标题,可选参数,参数类型为逻辑值,true表示将第一行用作标题,默认为false
第三参数:delaytypes,延迟类别,可选参数,参数类型为逻辑值,参数不用管它,直接保持默认即可
以上就是这个函数的参数,如果你觉得听得云里雾里的,只需要记得第一参数是路径,第二参数是将表格中的第一行用作标题即可,下面就让我们来看下它是如何实现数据汇总的
二、汇总一个Excel中的所有sheet
1.导入数据
首先我们新建一个Excel,然后点击【数据】→【获取数据】→【来自文件】→【从工作薄】在跳出的对话框中我们找到需要汇总的Excel工作薄点击导入,当看到导航器的界面后,点击Excel名称,随后直接点击转换数据,这样的话就会进入power query的编辑界面
2.汇总数据
进入这个界面后我们可以在编辑栏栏中看到Excel.Workbook这个函数,我们可以看到他的第二参数为:null,这样的话它是不会自动匹配表头的
随后我们点击下方的几个table看一下,会发现它们表头是column1到column9不是我们需要的表头,我们需要的表头都是在表格中的第一行,这个时候可以将Excel.Workbook的第二参数更改为true将每个表格的第一行作为标题,这样的话再点击看下,每个table都有标题了
随后我们仅保留mane和date这两列数据,将其余的都删掉,然后点击date字段上的左右箭头来深化数据,至此就把数据都汇总在一起了,它是可以自动匹配表头的,最后只需点击关闭并上载至,将数据加载到Excel即可
二、汇总多个Excel文件
1,加载数据
想要汇总多个Excel文件,我们就需要将这些Excel文件都放在一个文件夹中,随后新建一个Excel然后点击【数据】功能组→找到【获取数据】→【来自文件】→【从文件夹】找到对应的文件夹后点击确定,当看到导航器的界面之后我们直接点击【转换数据】这样的话就会进入powerquery的编辑界面
2.删除无用列
进入后我们仅仅保留Content与Name这两列数据,将其余的数据全部删除掉即可,我们点击Content这一列中的Binary就能在左下角看到一个Excel文件,我们需要的汇总的数据就在这里
3.添加自定义列
我们添加一个自定义列,利用Excel.Workbook这个函数来获取Binary中的所有数据,首先点击【添加列】选择【自定义列】在里面输入公式=Excel.Workbook([Content],true),然后点击确定即可,这样的话在后面就会多出一个自定义列,这个公式如果你不想设置的话直接复制我这个也是可以的,随后我们点击自定义右边的左右箭头来深化数据,在这里仅仅选择name(工作表名)和date(数据)这两项,然后点击确定即可
4.汇总数据
我们将Content这一列删除掉,最后只需要在date这个字段中点击左右箭头来深化数据,这样的话就把数据都汇总完成,同样的也在是主页中选择关闭并上载至将数据加载到excel即可
以上就是今天分享的方法,在这里主要是使用了Excel.Workbook这个M函数来汇总的,它是可以实现自动匹配表头,更改数据源一键刷新(点击表格,点击鼠标右键找到刷新即可)
怎么样?你学会了吗?我是Excel从零到一,关注我,持续分享更多Excel技巧