几百个业务人员的数据反馈，几十家分公司的每日汇总，各渠道数据的汇总，部门内部数据的汇总等等，相信在很多没有完善CRM数据系统的公司，可能每天都都需要有人去做这样的事情。总之，非高效没意义的工作我们能自动化智能化的代替我们则会想方设法去代替。今天我们就用Excel中的PowerBI系列插件帮我们去完成这样的工作。
所以，今天我们的学习目标就分为以下两方面：
完成上百甚至上千上万份同格式的文件的汇总合并
结合PowerPivot完成上百万数据的透视分析
如果觉得以上内容对你有所帮助，我们就接着往下看：
PS:关于PowerQuery的简单介绍可以关注我们【简快Excel】查看历史文章，有详细介绍。
合并上百份报表的操作思路：
操作步骤：
第一步 将要合并的文件放到我们待合并文件夹
我们先放入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配合可视化的一些操作即可完成。2、后续只需要一键刷新即可完成汇总。3、通过Excel插件PowerPivot的配合我们完成了185万行数据的透视表的创建，对传统Excel分析的能力做了一个很大的突破。
其实关于Excel中商务智能系列套件对我们未来的很多分析业务需求提供了更多的可能，本文所用示例文件可以加群198086726获取，更多文章欢迎关注【简快Excel】（PowerBIPro）如果本文对你有帮助，欢迎点赞转发。
本文作者：雷公子，如需转载请联系授权
网友评论