数据汇总你会这么做?相信大多数人都会异口同声地回答到:复制粘贴,那如果有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的编辑界面

excel多个工作簿汇总(excel汇总多个工作表到一个工作簿)(1)

2.汇总数据

进入这个界面后我们可以在编辑栏栏中看到Excel.Workbook这个函数,我们可以看到他的第二参数为:null,这样的话它是不会自动匹配表头的

随后我们点击下方的几个table看一下,会发现它们表头是column1到column9不是我们需要的表头,我们需要的表头都是在表格中的第一行,这个时候可以将Excel.Workbook的第二参数更改为true将每个表格的第一行作为标题,这样的话再点击看下,每个table都有标题了

excel多个工作簿汇总(excel汇总多个工作表到一个工作簿)(2)

随后我们仅保留mane和date这两列数据,将其余的都删掉,然后点击date字段上的左右箭头来深化数据,至此就把数据都汇总在一起了,它是可以自动匹配表头的,最后只需点击关闭并上载至,将数据加载到Excel即可

excel多个工作簿汇总(excel汇总多个工作表到一个工作簿)(3)

二、汇总多个Excel文件

1,加载数据

想要汇总多个Excel文件,我们就需要将这些Excel文件都放在一个文件夹中,随后新建一个Excel然后点击【数据】功能组→找到【获取数据】→【来自文件】→【从文件夹】找到对应的文件夹后点击确定,当看到导航器的界面之后我们直接点击【转换数据】这样的话就会进入powerquery的编辑界面

excel多个工作簿汇总(excel汇总多个工作表到一个工作簿)(4)

2.删除无用列

进入后我们仅仅保留Content与Name这两列数据,将其余的数据全部删除掉即可,我们点击Content这一列中的Binary就能在左下角看到一个Excel文件,我们需要的汇总的数据就在这里

excel多个工作簿汇总(excel汇总多个工作表到一个工作簿)(5)

3.添加自定义列

我们添加一个自定义列,利用Excel.Workbook这个函数来获取Binary中的所有数据,首先点击【添加列】选择【自定义列】在里面输入公式=Excel.Workbook([Content],true),然后点击确定即可,这样的话在后面就会多出一个自定义列,这个公式如果你不想设置的话直接复制我这个也是可以的,随后我们点击自定义右边的左右箭头来深化数据,在这里仅仅选择name(工作表名)和date(数据)这两项,然后点击确定即可

excel多个工作簿汇总(excel汇总多个工作表到一个工作簿)(6)

4.汇总数据

我们将Content这一列删除掉,最后只需要在date这个字段中点击左右箭头来深化数据,这样的话就把数据都汇总完成,同样的也在是主页中选择关闭并上载至将数据加载到excel即可

excel多个工作簿汇总(excel汇总多个工作表到一个工作簿)(7)