所以,今天的学习目标就分为以下两方面:
如果觉得以上内容对您有所帮助,请接着往下看。 合并上百份报表的操作思路: 第一步:将要合并的文件放到待合并文件夹 先放入10份文件测试,完成后再给大家演示100份文件的合并。 第二步:在文件夹外新建一个Excel文件汇总数据使用 第三步:新建查询开始链接源数据 点击数据标签下边的【新建查询】→【从文件】→【从文件夹】,如下图: 完成后,点击浏览找到文件夹路径,点击确定,也可以复制路径直接点击确定即可。 然后点击编辑,即可进入PowerQuery界面。 第四步:PowerQuery编辑获取数据 从第三步的表里我们看到了这几列包含了文件名,后缀,创建日期,修改日期,访问日期,以及文件夹路径等属性,就是没有直接看到表里的数据,其实表里的数据是被以二进制的方式存储在【Content】列了。仅仅需要一个函数就能获取其中的内容,我们具体操作下。 1、添加列里,点击自定义列,输入=Excel.Workbook([Content],true): 2、删除不需要的干扰列,按住ctrl选择表名称[Name]列和[自定义]列。 3、展开自定义列,对Kind列筛选,让其等于Sheet,避免表格中有表格引用,或者区域名称导致汇总出现重复或者部分错误,这块儿我们就能看到具体的每个工作簿中工作表的名称,每个工作表的内容将被存储在了[Data]列,随便点击一个单元格便能看到里边的数据。 4、点击[Data]列的展开按钮,去掉使用原始列名作为前缀的勾选,点击确定。 5、现在就看到数据已经被合并好了,没有用的列删除即可。 6、接着修改下数据类型即可。 7、点击关闭并上载下拉功能,关闭并上载至,按钮,简单解释一下:上边的加载到表,就是加载到EXCEL界面,数据量在100万以内的选择这个没有任何问题。下边的【将此数据添加到数据模型】勾选后可以将查询获取的汇总数据加载到PowerPivot中,PoewrPivot配合仅创建连接表(即不直接存储数据到Excel表格区域)。 8、先选择加载到表,看下效果:可以看到10个表460行的数据就被加载完成了。 9、探索下100 的表格的合并效果以及PowerQuery的优势:复制原始数据10份,在待合并文件夹。 10、我们需要重新做一遍刚才的工作吗?NO!NO!NO!我们只需要刷新下数据源即可。 以下方法学习一种即可: 刷新方法1:数据标签下边的全部刷新功能。 刷新方法2:在当前表格里右键,点击刷新。 刷新方法3:在右侧查询界面,右键刷新,如果没有看到右侧的工作表查询的话,点击数据标签下的显示查询按钮即可。 11、点击刷新,我们只需等待不到2s钟的时间,100份报表就汇总完成了。(有兴趣的同学也可以测试下1000份) 关于PowerQuery文件夹的合并就介绍这么多。如果原始数据非Excel格式的话而是Csv的话,把Excel.Workbook替换为Csv.Document([Content],[Delimiter=”,”, Encoding=936]),其他操作都一样。 接下来,我们探索下如何利用这个方法实现千万级别的数据透视分析。 1、修改原始文件中两个表的数据量到92万行,保存关闭。 2、直接刷新,我们看到表格会报错。可以看到右侧加载了185万行数,Excel文件里直接放不下了。好了,来看下怎么处理吧。 3、在右侧我们的查询上点击右键,选择【加载到】,然后如下图所示选择,点击确定。 4、点击PowerPivot下的管理按钮,查看下我们的数据,如果没有PowerPivot标签点击Ecxel的【COM加载项】勾选下即可,注意需要13或者16专业增强版以上才有此功能。 5、185万的数据就被存储在Excel的PowerPivot中了,接下来点击插入透视表,选择使用此工作簿的数据模型为源数据即可。 6、我们简单拖拽看下效果:一个185万行数据产生的透视表就被做出来了。 总结: 1、快速合并文件夹文件,我们仅需一个函数Excel.Workbook配合可视化的一些操作即可完成。
袁雷 雷公子 互联网搜索引擎营销经理 数据分析展现狂热爱好者 公众号: 简快Excel之PowerBI建模分析 |
|