1. 完成上百甚至上千上万份同格式的文件的汇总合并

2. 结合 PowerPivot 完成上百万数据的透视分析

如果觉得以上内容对你有所帮助，我们就接着往下看：

PS: 关于 PowerQuery 的简单介绍可以关注我们【简快 Excel】查看历史文章，有详细介绍。

合并上百份报表的操作思路：

操作步骤：

第一步 将要合并的文件放到我们待合并文件夹

image

我们先放入 10 份文件测试，完成后再给大家演示 100 份文件的合并

第二步 我们在文件夹外新建一个 Excel 文件汇总数据使用

第三步 新建查询开始链接我们的源数据

我们点击数据标签下边的【新建查询】→【从文件】→【从文件夹】，如下图：

image

完成后点击浏览找到我们的文件夹路径点击确定，也可以复制我们的路径直接点击确定即可。

image

然后我们点击编辑，即可进入 PowerQuery 界面。

第四步 PowerQuery 编辑获取数据

从第三步的表里我们看到了这几列包含了我们的文件名，后缀，创建日期，修改日期，访问日期，以及他的文件夹路径等属性，就是没有直接看到我们表里的数据，其实我们表里的数据是被以二进制的方式存储在【Content】列了。我们仅仅需要一个函数就能获取其中的内容，我们具体操作下。

1、添加列里，点击自定义列，输入=Excel.Workbook([Content],true)：

image

注意：公式需要区分大写

2、然后，我们删除我们不需要的干扰列，我们按住 ctrl 选择我们的表名称 [Name] 列和 [自定义] 列

image

3、展开自定义列，对 Kind 列筛选，让其等于 Sheet, 避免表格中有表格引用，或者区域名称导致汇总出现重复或者部分错误，这块我们就能看到我们具体的每个工作簿中工作表的名称，每个工作表的内容将被存储在了 [Data] 列，我们随便点击一个单元格便能看到里边的数据。

image

4、我们点击 [Data] 列的展开按钮，去掉使用原始列名作为前缀的勾选，点击确定。

image

5、我们就看到数据已经被合并好了，没有用的列我们删除即可：

image

6、接着我们修改下数据类型即可。

7、我们点击关闭并上载下拉功能，关闭并上载至，按钮, 我们简单解释一下，上边的加载到表，就是加载到我们的 EXCEL 界面，数据量在 100 万以内的选择这个没有任何问题。下边的【将此数据添加到数据模型】勾选后可以将查询获取的汇总数据加载到 PowerPivot 中，PoewrPivot 配合仅创建连接表（即不直接存储数据到 Excel 表格区域）

image

8、我们先选择加载到表，看下效果，我们看到 10 个表 460 行的数据就被我们加载完成了：

image

9、我们探索下 100 + 的表格的合并效果以及我们 PowerQuery 的优势，我们复制原始数据 10 份，在待合并文件夹。

image

10、我们需要重新做一遍刚才的工作吗？NO!NO!NO! 我们只需要刷新下我么的数据源即可，以下方法学习一种即可

刷新方法 1：数据标签下边的全部刷新功能

image

刷新方法 2：在当前表格里右键，点击刷新

image

刷新方法 3：在右侧查询界面，右键刷新，如果没有看到右侧的工作表查询的话，点击数据标签下的显示查询按钮即可

image

11、点击刷新，我们等待不到 2s 钟的时间，100 份报表就帮我们汇总完成了，有兴趣的同学也可以测试下 1000 份。

image

关于 PowerQuery 文件夹的合并就介绍这么多，如果原始数据非 Excel 格式的话而是 Csv 的话，把 Excel.Workbook 替换为 Csv.Document([Content],[Delimiter=”,”, Encoding=936]), 其他操作都一样。

接下来我们探索下如何利用这个方法实现千万级别的数据透视分析

1、我们修改原始文件，中两个表的数据量到 92 万行, 保存关闭

image

2、直接刷新，我们看到表格会报错，因为我们可以看到右侧加载了 185 万行数，Excel 文件里直接放不下了，好了我们看下怎么处理吧。

image

3、我们在我们右侧我们的查询上点击右键，选择【加载到】，然后如下图所示选择，点击确定。

image

4、我们点击 PowerPivot 下的管理按钮，查看下我们的数据，如果没有 PowerPivot 标签点击 Ecxel 的【COM 加载项】勾选下即可，注意需要 13 或者 16 专业增强版以上才有此功能：

image image

5、我们 185 万的数据就被存储在 Excel 的 PowerPivot 中的，接下来我们点击插入透视表，选择使用此工作簿的数据模型为源数据即可。

image

6、我们简单拖拽看下效果，一个 185 万行数据产生的透视表就被我们做出来了。

image

总结：

1、快速合并文件夹文件，我们看到我们仅需一个函数 Excel.Workbook 配合可视化的一些操作即可完成。
2、后续只需要一键刷新即可完成汇总。
3、通过 Excel 插件 PowerPivot 的配合我们完成了 185 万行数据的透视表的创建，对传统 Excel 分析的能力做了一个很大的突破。

其实关于 Excel 中商务智能系列套件对我们未来的很多分析业务需求提供了更多的可能，本文所用示例文件可以加群 198086726 获取，更多文章欢迎关注【简快 Excel】（PowerBIPro）如果本文对你有帮助，欢迎点赞转发。
文章转载自https://zhuanlan.zhihu.com/p/27342107