合并汇总大量数据表的一般方法

李森

摘要：系统的、规范的数据库是大数据分析的基础，但在实际中，很多政府机构和企业都没有建立数据库的条件，大量数据以Excel表格的形式存放在管理人员的电脑上，查询、汇总极为不便。如何整理规范统计数据是一个常见的、典型的棘手问题，在整理大量历史数据的时候，表结构的变化给数据使用者造成了很大的麻烦，传统的Excel函数、VBA编程等方法面对这种情况也是束手无策，这个时候我们可以使用微软的商务软件PowerBI，从分析袁结构入手，方便地进行数据的汇总和整理工作。

关键词：数据汇总;商务智能;大数据分析;合并历史数据;表结构;逆透视;规范化

中图分类号：TP3-05 文献标识码：A

文章编号：1009-3044（2019）33-0257-04

在工作中我们经常会遇到合并多个数据表的情况，比如多个历史时期数据表的合并，多个分公司报表的汇总，当数量不多时，我们可以依次打开各个数据表，手动复制粘贴至一张表中，但如果涉及的数据表太多时，这个方法就行不通了，或者说过于烦琐，比如说有时候刚整理好各月工资，又需要整理各月獎金，那么之前进行的所有步骤又需要再做一遍了。当这样的重复性工作我们做了好多年之后，就应该考虑其他的方法了，我们需要在不打开文件的情况下提取其中的数据，把人从烦琐的数据整理工作中解放出来。

那么怎么办呢？过去我曾尝试通过VBA编程提取数据，对于结构完全一致的报表这个方法是行得通的，但是这里有一个问题，由于制表人不同或者表结构变化等原因，每张表中的同一个数据可能并不在同一个位置，比如说有的表里工资在单元格B2，有的表里可能在B3，或者C2，或者其他什么地方，有时候发给分公司的工整的报表，收上来的时候却变得花花绿绿的，有的甚至面目全非，填表人会随意地删行、加行、删列、加列、调整行列顺序、合并单元格、修改单元格格式，等等，不一而足，原因是他那里有特殊情况，或者为了方便填报等，但是无规矩不成方圆，随意更改表结构方便了填表人，却给数据整理工作带来了大麻烦，需要汇总整理的数据表必须严格限定报表格式，最好是在下发报表的时候就锁定表结构，但是我们不能指望每一个报表设计者都会锁定操作，或者数据表的汇总者和设计者不是同一个人，汇总人收到各张报表的时候发现表结构已经不一致了，这个时候，VBA也是无可奈何的。

这时候，我们可以用Excel插件power query解决这个问题。PQ是微软公司的商务智能Power BI工具之一，它和powerpivot构成了PBI的核心，它的操作原理和语法类似于SQL语言和OLEDB数据连接，但使用起来更加灵活简单，是面向普通的数据工作者的大众化BII具，不需要编写太多复杂烦琐的代码，就可以轻松实现数据汇总分析。PQ是一种入门级的大数据分析工具，我认为以后会是数据工作者必备的技能之一，就像现在的Excel一样基础。

在这篇文章中我以一个例子通俗易懂地介绍一下用PQ进行数据表合并的一般思路和操作过程。

1问题的提出

假如我们需要整理北京市各市区社会消费品零售额的历史数据，现在有2007年2月-2019年6月的100多张数据表f数据来源：北京市统计局），我们对比一下第一期和最后一期报表的表结构，如图1所示。

总结一下2019年6月期报表中表结构的几处变化：

1.1从行来看

1）表头由两行变为一行;

2）取消了四大功能区;

3）“亦庄开发区”更名为“北京经济技术开发区”;

4）崇文区、宣武区分别合并到东城区、西城区;

5）密云、延庆两县撤县设区;

6）门头沟区位置提前;

7）市区名称前加上了空格。

1.2从列来看

第2、3列数据互换位置，从“本月、累计、本月增速、累计增速”变成了“本月、本月增速、累计、累计增速”。

由于表结构的变化，所有数据的位置均发生了改变，用VBA取固定位置的数据显然不可取，而用PQ，可以分步实现数据汇总。

2第一步：获取数据源

首先需要整理出“数据源表”，这张表里列出了各个数据表的基本情况，需要说明几点：

1）一共有138个excel文件，统一存放在“根目录＼数据源”文件夹中，根目录根据存放路径自动调整，比如我现在用的根目录是：

PQ会自动从“F：＼20190810合并数据表＼数据源＼200702.xl-sx”“F：＼20190810合并数据表＼数据源＼200703.xlsx”等文件中取数，而下载压缩包后可解压存在任一地址，比如放在“D：＼P0练习/合并数据表”中，PQ就会自动从“D：＼PQ练习＼合并数据表＼数据源＼200702.xlsx”“D：＼PQ练习＼合并数据表＼数据源＼200703.x1-sx”等文件中取数，不用手动修改。

2）在十多年的时间里，报表结构经过了多次变化，二维表的表结构包括主栏（甲栏）、宾栏（乙栏、表头）两个方面，数据源表中列出了每一个excel文件中报表的甲栏版本和宾栏版本，这需要用户自己去总结，PQ就是根据文件位置和甲栏、宾栏版本去各个文件中的特定单元格取数。

3第二步：获取表头行数

从2013年2月开始，数据表的表头由两行变为了一行，先总结出各甲栏版本的表头行数，然后让各excel文件自动查找对应的表头行数信息，实现原理类似于excel工作表函数vlookup。

这一步是为了便于理解接下来的处理过程，当PQ熟练以后可省略这一步。

4第三步：批处理表格——二维表转一维表

这是数据汇总的最关键一步：维度转换，要想把不同结构的数据表汇总在一起，所需要做的不是简单的表格的拼接（追加），因为不同时期的报表中各行、各列意义不同，比如下面这两张表拼接在一起就出错了，因为后一张表的第2、3列数据互换了位置，这时候需要先把二维表统一转化为一维表，再进行各张表行、列的规范化。

首先需要去掉表结构区域，仅保留数据区域。2007年2月报表需要去掉第一列和前两行，2019年6月报表需要去掉第一列和第一行，这时候步骤二中的表头行数就派上了用场（不过在熟练以后其实这一步可省略，不影响结果）。

二维表转一维表所用的方法叫作“逆透视”，也就是把多行、多列的数据转化为一列，道理很简单，把横向放置的数据转置，竖向排列在一起就可以了，excel中进行此项操作较麻烦，在PQ中可以很方便地进行逆透视操作。

PQ中可以对多个数据表进行批量操作，批量逆透视之后的结果是：

这时候就可以对不同时期的报表进行拼接（追加）了。

5第四步：展开（拼接）报表

这一步很简单，把规范化之后的二维表拼接在一起就可以了。

但是对于表中的每个数据我们还不知道是什么意思，所以下一步需要进行甲栏（行号）和宾栏（列号）的转换（规范化）。

6第五步：规范甲栏（行号）

规范甲栏和宾栏首先需要有规范依据，以甲栏为例，需要整理出各个甲栏版本间的对应关系，经整理发现甲栏版本共有6次调整，共有7个版本，每一次调整都有一定的原因：

需要注意的是，整理表结构需要以最新一版为准，比如密云县、延庆县已撤县设区，所以历史汇总数据中统一称密云区、延庆区，同理不再保留老东城、老西城、崇文、宣武区数据，因为人们已经不再关注这些数据了，如果需要的话可以另行添加。

对甲栏规范表进行二维表转一维表操作，可得到如下报表：

以此为依据可以对第四步展开的报表中的行号进行规范化整理，比如甲栏版本1-5的第3行是北京市数据，而版本6和7的第2行是北京市数据，版本2的第28行是东城区数据，版本7的第3行是东城区数据，版本1的第4行是“首都功能核心区”数据，最新版中已不再保留，所以在转化结果就顺便把它去掉了。

甲栏规范化之后，甲栏版本列也就没用了，可以删除。规范化过程用到的方法叫作“合并查询”，类似于excel工作表函数vlookup，但好处是可以设置多个查询依据（多列），其实就是关系型数据库中所说的主键，只有同时指定“甲栏版本”和“行号”才能确定唯一的“市区”，可以理解为一个二元函数，f（x1，x2）由x1、x2共同决定的，两个条件缺一不可。

7第六步：规范宾栏（列号）

同理可对宾栏进行规范化转换，转换后的结果是：

需要注意的是，宾栏中包含着4个属性，比如2019年6月報表的第2列数据，它表示：2019年当年（属性3）的2月当月（属性2）的社会消费品零售额（属性1）的绝对值（属性4）数据，而第4列表示：2019年当年（属性3）的2月累计（属性2）的社会消费品零售额（属性1）的绝对值（屙陛4）数据。

对于数据的某些属性，我们在特定情况下可以省略说明，比如这个例子中的所有数据都是关于“社会消费品零售额”这个指标的，用户都知道，所以这个属性可以省略，可以把文件名命名为“社会消费品零售额历史数据”就可以了，用户就知道这个文件里面的所有数据都是关于这个指标的，这就是所谓数据的“上下文context”。

但是你不能把文件命名为“海淀区社零额”，因为这里面有非海淀区的数据，但是可以命名为“北京市各区数据”“北京市各区历史数据”等，从这里也可以看出，数据表的文件名就是其中所有数据的一个或几个共同的属性名。

在甲栏和宾栏的规范化操作中，包含着模式分解和数据清洗的过程，这都是数据整理和数据库搭建必不可少的环节。

8第七步：单位转换

在对甲栏、宾栏规范化转换以后其实数据汇总表已经基本做好了，但还有一个重要的问题需要解决：整理各期数据表发现有的时期数据单位是万元，有的时期是亿元，必须统一起来。

规则很简单，对于“增速”数据不用处理，“绝对值”数据中最近5年的年末数据因为比较大，当期报表中使用了亿元单位，不用处理，其他时期的绝对值都是万元单位，统一乘以0.0001转换为亿元单位。转换之后的结果是：

9数据分析

一维表是数据分析的基础，当得到历史数据的一维表之后，就可以进行各类透视，然后可进行相应数据分析，透视操作用PQ或数据透视表都可以，比如对“度量”透视得到如下结果：继续透视“数期”得到：透视“市区”得到：

可以看出，我们平时看到的大部分数据表都是一种数据透视表，透视表是对抽象事物和数据的特征的高度概括和呈现，通过序列数据的对比，使人们很容易抓住同类事物的本质和特征，例如，通过时间序列数据对比可总结出事物的变化规律，通过空间序列数据对比可概括出事物分布规律，通过时空数据对比可抓住事物的薄弱点、突破口、差距所在，更好地促进整体发展。

在数据分析报告中，透视表比单纯的文字描述效果要好得多，很多所谓的数据分析报告就是对透视表的文字描述，比如某某指标实现了多少、增长了多少、占比是多少之类，几大段乱七八糟的文字可能还不如一张表讲得清楚，因此说一表胜千言，而进一步讲，数据图又比数据表的表现能力更好，因此还有人说文不如表，表不如图，确实如此。在数据透视表的基础上，可以在excel中制作数据图，而用微软PBI的组件powerview、百度ECharts等工具可实现更多更丰富的数据图表展现形式。

【通联编辑：李雅琪】