前言
最近实务上连续遇到两个类似的案例，合并为一个其实就是超大数据量的表格合并加透视的问题，比如从业务系统内导出一个月的数据就有30万行以上，Excel文档的大小在50M左右，需要合并透视整年或数年的数据。
手工粘贴再透视的话，最主要的问题就是Excel不支持100万行以上的数据，所以你连享受Excel文档卡死的资格都没有。
碰到这种问题用SQL当然好，数据量完全不在话下，各种汇总、查询的速度都非常快，不过对于看到英文字母就头晕的同学来说就不太友好了，虽然学习起来并不难，但依旧有一定门槛，常用的查询代码一般也要摸3天才能摸熟。话说回来，虽然我学了这玩意儿，但是在实务中真正用到的次数却屈指可数，导致我每次使用的时候都要回头把之前学的东西再捡一遍，也许是自己涉及这个数据量级的项目不多吧。
简介
其实MS对于Excel数据量的限制也早有了解决方案，那就是Power Query和Power Pivot。
在Excel2010时，MS推出了Power Query的插件（可以在官网下载），使得Excel处理数据的能力大幅提升，在Excel2016中直接内置了Power Query，放在数据选项卡中：
Power Query可以理解为一个查询数据的软件，这个名字可以类比微软自家的其他软件，比如我们熟知的PPT叫"PowerPoint"。
Power Pivot可以理解为透视表软件，它比Excel内置的透视表强大了很多，一般被用来建立数据模型，对于我们来说就把他当做超级透视表就行了。
其实Power Pivot就是微软把自家数据库SQL Server的分析服务引擎拆出来装到了Excel中。
顺便提一句：MrExcel.com创始人Bill这样说:Power Pivot是Excel在20年的改进中最棒的改进。
所以说，你学这个就够了，懂伐？
在Excel的COM加载项中，把Power Pivot调出来：
上面大概介绍了一下PQ和PP，可能你会觉得有点绕，很复杂的样子，其实这个东西用起来真的很简单，在试用了几次Power Pivot后，我觉得对于财审人员日常的工作来说，基本上可以不用去学SQL了，因为用PQ用起来不需要学什么代码，最多就几个函数稍微记一下，其他的都靠点，并且上载进Excel后，数据透视什么就跟平时普通的透视操作一样，大幅降低了学习成本。
如果你手里有Office Excel2016的话，下载案例花几分钟跟着我的步骤走一遍试试吧，以后碰到几百万行的表也不怕了。
案例
需求
下面我以一个40-50万数据的例子，来简单的演示一下怎样用PQ：
这里有两个月的数据，我通过复制，把数据拉到47万行和57万行，单个文档的大小在25-30M。
需求：将其合并，再一起透视。
先直接看动图吧，真的很简单，之前手工几小时都搞不定的东西，用这个只需要2分钟就能搞定。
1.载入数据并调整数据类型：
2.上载至Excel并数据透视：
01
使用power query载入数据
1.在数据选项卡中，依次点击【从文件】-【从文件夹】
2.选好了文件夹路径后，可以看到要导入的文档都进来了，确认没问题后，点击右下角的【编辑】↓
3.进入编辑界面后，我们看到文档的信息都被导入进来了↓
4.表格中的数据都以二进制的形式储存在第一列"content"中，所以我们需要用一个函数把他们提取出来：
5.依次点击选项卡【添加列】-【自定义列】
输入函数"=Excel.Workbook([content],true)"，注意大小写，大家直接复制就行。
这个函数的两个参数：
1.[content]就是要解析的列名。
2.如果每个文档的第一行是标题行，就写true，他会把所有的文档标题行放在第一行并去重。
一般来说，我们直接复制这个函数就行了。
如果是csv的文件，函数为：Csv.Document([Content],[Delimiter=",", Encoding=936])
6.输入好函数后，点击确定，可以看到我们的自定义列，点击右上角的展开按钮。
7.在新出来的列中，找到Data列，继续点击右上角的展开按钮，我们需要的数据就全部合并且加载进来了。
8.加载完成后，会自动选中展开的数据，点击鼠标右键-删除其他列，留下我们需要的列即可。
02
转换数据类型，上载至Excel
1.加载进来后，数据类型一般会自动变成文本，如果我们不进行数据类型的转换的话，在后面的透视过程中是不能求和的，因此，在这里把小数、日期都直接转换好↓
2.转换数据类型后，将PQ编辑器中的数据上载至Excel中：
如果数据量大的话，尽量不要直接点关闭并上载，否则所有数据直接灌入Excel就爆炸了，这个按钮我建议只有汇总多个相同格式的工作簿且单个工作簿的数据不多的情况下使用。
在弹出的选项框中，选中【仅创建连接】并勾选【将此数据添加到数据模型】
这样的话，所有的数据就没有直接添加进Excel中，Excel仅作为呈现数据的界面，查询等操作都在PQ的引擎中进行。
加载完成后，在Excel的右侧会显示加载状态。
03
数据透视
前面已经把所有的文档通过PQ上载进Excel中了，到了数据透视这一步就是所有人都熟悉的：插入透视表-选择字段并进行透视了。
这里唯一要说的就是勾上【使用此工作簿的数据模型】
之后的操作就完全和透视表完全一样了。
04
刷新数据源
之前我们导入了7、8月的表，如果我们又获取了9-12月表，那么直接把文档放在同一文件夹下，在Excel的右侧点击刷新，就能把新增的表按上述步骤自动过进来，再也不用手动进行设置了。
这篇文章我用最简单的案例把PQ全部流程都走了一遍，在实际情况中大家应该会碰到一些别的坑，比如有些数据我们是不需要的，那么在PQ编辑器中上载至Excel之前，筛选需要的内容即可，上载的内容为筛选后的内容。
这个功能熟练之后，以后同一个格式的汇总，无论是多工作表还是多工作簿的都不在话下了，更多的技巧可以去知乎或者相关的论坛看看。
- END -
