表格程序两种数据加工的方法

刘可

摘 要 普通用户加工数据时使用EXCEL等表格软件的比率远高于数据库管理软件，表格软件通过其内置的丰富函数功能也能达到与数据库查询语言相同的目标。本文是作者通过工作实践总结的两个较典型的数据处理案例：一是数据筛选计数，二是对多表进行合并或比对。也可推广适用于多种数据表的处理。

【关键词】表格 计数 合并

使用SQL查询语言的用户能够方便的生成符合其需求的数据集，但多数普通用户并不能熟练掌握，处理数据常用工具以EXCEL或WPS表格多见。事实上，通过简单的函数组合技巧，表格应用也能快速的筛选并呈现用户期望的数据集合。本文结合作者工作实践，介绍两种筛选数据的方法。

1 一对多关系的筛选计数

在个人外汇业务监管中，监管者需要探查境外机构或个人将资金分拆汇入国内多个个人账户的情形。从数据库导出的表格文件来看，即是要查找相同付款人字段的多条记录。从关系数据库角度看，即通过付款人字段进行分组，将收款人按计数进行汇总。表格程序中的处理思路，首先按照付款人字段进行表格排序。接着增加相同付款人标志列，此列使用公式形如=IF（OR（LEFT（B2，N）=LEFT（B1，N），LEFT（B2，N）=LEFT（B3，N）），LEFT（B2，N），”--”）。其含义如下：若该行付款人字段值与相邻（上或下）的值相同，则该标志列的值即为付款人字段关键字，否则用特定符号定义该字段值。即若n行和n+1行付款人字段内容相同，则该标志列赋值为付款人名称关键字字段内容。这里使用LEFT函数，原因在于实际发生业务中，该字段提交内容为文本，校验约束不严格，所以这里提取前N位字符作为判断付款人名称的实际关键字，N的值可以根据实际情况自行设置。

接着对工作表执行插入“数据透视表”，将“对方付款人名称”字段先后添加到行区域和数据区域（以WPS2013为工具描述），即生成所有付款人名称相同的计数内容，最后再将计数列按降序排列，就能直观得出具有分拆行为的境外机构或个人的实际分拆数量（结果见表1），并依此作为进一步开展个人外汇业务现场监管工作的数据基础，做到有的放矢。

2 两表连接实现表格合并或数据比对功能

在业务系统中导出的基本数据表格通常无法适应用户意愿，如导出表的收入和支出一般是两张表，而在较多情况下用户希望得到收入与支出的复合表；或是对于两个不同数据来源的业务数据，如用户希望经过对比发现两表中一致或不一致的记录，获取多来源数据对比核查的线索。从关系数据库的角度分析，即是通过多表连接来比较表格的内容，而在表格程序中可以通过以下方法来实现。

从两种表的特性分析，收入表中有的机构在支出表中不一定存在，反之亦然。现以收入表为基准，如收入表中的机构在支出表中存在，将支出金额字段加入到收入表中；如收入表中的机构在支出表中不存在，则显示支出为0，支出金额字段如下所示：=IF（ISNA（VLOOKUP（A3，Sheet1！$A$3：$A$1021，1，FALSE）），0，INDIRECT（"Sheet1！D"&MATCH（A3，Sheet1！$A$3：$A$1021，0）+2））。最外层IF函数的含义为，如收入表当前记录表示机构在支出表中不存在，则支出金额字段赋值为0；如存在则在支出表中找到相同机构记录，并将其交易金额字段值赋值给收入表的“支出金额”字段。VLOOKUP函数，用来在支出表的“组织机构代码”字段中查询是否存在收入表当前记录所代表的机构信息；INDIRECT函数是用来将支出金额字段作相应定位之后赋值到收入表相应字段中；MATCH函数用来返回支出表“交易金额”字段中查找到的对应的行值。

将以上公式复制到每个“支出金额”单元格中，可通过MATCH函数得到收入表中某机构在支出表中的位置，值为#N/A的表示该机构在支出表中不存在。“支出金额”表示该机构在同一时间段的支出总额，值为0的即表示支出表中不存在该机构的情形。再用相同的方法，以支出表为基准做一次收入金额的插入操作。最后将两个表格粘贴到一个新表中，通过在新列插入形如=COUNTIF（A$3：A3，A3）的函数，将辅助列值为2的记录全部删除，从而去除重复记录，所得结果见表2。

以上方法亦可用在数据核查工作中，用于比对不同数据来源的表间记录的异同，从而挖掘出因数据源不统一造成的难以发现的违规线索。

以上两种方法以及其中涉及的EXCEL或WPS表格函数对于数据集操作虽常见但功能强大，经过适当的逻辑组合可以达到与SQL查询语言同样的数据加工效果。将会对提高工作效率与业务创新起到极大帮助，值得经常使用表格软件的用户深入探索与掌握。

参考文献

[1]李兴勇.外汇监管中国呢业务数据的筛查技巧[J].金融时代科技，2011（11）：79.

作者单位

国家外汇管理局安徽省分局 安徽省合肥市 230091endprint

摘 要 普通用户加工数据时使用EXCEL等表格软件的比率远高于数据库管理软件，表格软件通过其内置的丰富函数功能也能达到与数据库查询语言相同的目标。本文是作者通过工作实践总结的两个较典型的数据处理案例：一是数据筛选计数，二是对多表进行合并或比对。也可推广适用于多种数据表的处理。

【关键词】表格 计数 合并

使用SQL查询语言的用户能够方便的生成符合其需求的数据集，但多数普通用户并不能熟练掌握，处理数据常用工具以EXCEL或WPS表格多见。事实上，通过简单的函数组合技巧，表格应用也能快速的筛选并呈现用户期望的数据集合。本文结合作者工作实践，介绍两种筛选数据的方法。

1 一对多关系的筛选计数

在个人外汇业务监管中，监管者需要探查境外机构或个人将资金分拆汇入国内多个个人账户的情形。从数据库导出的表格文件来看，即是要查找相同付款人字段的多条记录。从关系数据库角度看，即通过付款人字段进行分组，将收款人按计数进行汇总。表格程序中的处理思路，首先按照付款人字段进行表格排序。接着增加相同付款人标志列，此列使用公式形如=IF（OR（LEFT（B2，N）=LEFT（B1，N），LEFT（B2，N）=LEFT（B3，N）），LEFT（B2，N），”--”）。其含义如下：若该行付款人字段值与相邻（上或下）的值相同，则该标志列的值即为付款人字段关键字，否则用特定符号定义该字段值。即若n行和n+1行付款人字段内容相同，则该标志列赋值为付款人名称关键字字段内容。这里使用LEFT函数，原因在于实际发生业务中，该字段提交内容为文本，校验约束不严格，所以这里提取前N位字符作为判断付款人名称的实际关键字，N的值可以根据实际情况自行设置。

接着对工作表执行插入“数据透视表”，将“对方付款人名称”字段先后添加到行区域和数据区域（以WPS2013为工具描述），即生成所有付款人名称相同的计数内容，最后再将计数列按降序排列，就能直观得出具有分拆行为的境外机构或个人的实际分拆数量（结果见表1），并依此作为进一步开展个人外汇业务现场监管工作的数据基础，做到有的放矢。

2 两表连接实现表格合并或数据比对功能

在业务系统中导出的基本数据表格通常无法适应用户意愿，如导出表的收入和支出一般是两张表，而在较多情况下用户希望得到收入与支出的复合表；或是对于两个不同数据来源的业务数据，如用户希望经过对比发现两表中一致或不一致的记录，获取多来源数据对比核查的线索。从关系数据库的角度分析，即是通过多表连接来比较表格的内容，而在表格程序中可以通过以下方法来实现。

从两种表的特性分析，收入表中有的机构在支出表中不一定存在，反之亦然。现以收入表为基准，如收入表中的机构在支出表中存在，将支出金额字段加入到收入表中；如收入表中的机构在支出表中不存在，则显示支出为0，支出金额字段如下所示：=IF（ISNA（VLOOKUP（A3，Sheet1！$A$3：$A$1021，1，FALSE）），0，INDIRECT（"Sheet1！D"&MATCH（A3，Sheet1！$A$3：$A$1021，0）+2））。最外层IF函数的含义为，如收入表当前记录表示机构在支出表中不存在，则支出金额字段赋值为0；如存在则在支出表中找到相同机构记录，并将其交易金额字段值赋值给收入表的“支出金额”字段。VLOOKUP函数，用来在支出表的“组织机构代码”字段中查询是否存在收入表当前记录所代表的机构信息；INDIRECT函数是用来将支出金额字段作相应定位之后赋值到收入表相应字段中；MATCH函数用来返回支出表“交易金额”字段中查找到的对应的行值。

将以上公式复制到每个“支出金额”单元格中，可通过MATCH函数得到收入表中某机构在支出表中的位置，值为#N/A的表示该机构在支出表中不存在。“支出金额”表示该机构在同一时间段的支出总额，值为0的即表示支出表中不存在该机构的情形。再用相同的方法，以支出表为基准做一次收入金额的插入操作。最后将两个表格粘贴到一个新表中，通过在新列插入形如=COUNTIF（A$3：A3，A3）的函数，将辅助列值为2的记录全部删除，从而去除重复记录，所得结果见表2。

以上方法亦可用在数据核查工作中，用于比对不同数据来源的表间记录的异同，从而挖掘出因数据源不统一造成的难以发现的违规线索。

以上两种方法以及其中涉及的EXCEL或WPS表格函数对于数据集操作虽常见但功能强大，经过适当的逻辑组合可以达到与SQL查询语言同样的数据加工效果。将会对提高工作效率与业务创新起到极大帮助，值得经常使用表格软件的用户深入探索与掌握。

参考文献

[1]李兴勇.外汇监管中国呢业务数据的筛查技巧[J].金融时代科技，2011（11）：79.

作者单位

国家外汇管理局安徽省分局 安徽省合肥市 230091endprint

摘 要 普通用户加工数据时使用EXCEL等表格软件的比率远高于数据库管理软件，表格软件通过其内置的丰富函数功能也能达到与数据库查询语言相同的目标。本文是作者通过工作实践总结的两个较典型的数据处理案例：一是数据筛选计数，二是对多表进行合并或比对。也可推广适用于多种数据表的处理。

【关键词】表格 计数 合并

使用SQL查询语言的用户能够方便的生成符合其需求的数据集，但多数普通用户并不能熟练掌握，处理数据常用工具以EXCEL或WPS表格多见。事实上，通过简单的函数组合技巧，表格应用也能快速的筛选并呈现用户期望的数据集合。本文结合作者工作实践，介绍两种筛选数据的方法。

1 一对多关系的筛选计数

在个人外汇业务监管中，监管者需要探查境外机构或个人将资金分拆汇入国内多个个人账户的情形。从数据库导出的表格文件来看，即是要查找相同付款人字段的多条记录。从关系数据库角度看，即通过付款人字段进行分组，将收款人按计数进行汇总。表格程序中的处理思路，首先按照付款人字段进行表格排序。接着增加相同付款人标志列，此列使用公式形如=IF（OR（LEFT（B2，N）=LEFT（B1，N），LEFT（B2，N）=LEFT（B3，N）），LEFT（B2，N），”--”）。其含义如下：若该行付款人字段值与相邻（上或下）的值相同，则该标志列的值即为付款人字段关键字，否则用特定符号定义该字段值。即若n行和n+1行付款人字段内容相同，则该标志列赋值为付款人名称关键字字段内容。这里使用LEFT函数，原因在于实际发生业务中，该字段提交内容为文本，校验约束不严格，所以这里提取前N位字符作为判断付款人名称的实际关键字，N的值可以根据实际情况自行设置。

接着对工作表执行插入“数据透视表”，将“对方付款人名称”字段先后添加到行区域和数据区域（以WPS2013为工具描述），即生成所有付款人名称相同的计数内容，最后再将计数列按降序排列，就能直观得出具有分拆行为的境外机构或个人的实际分拆数量（结果见表1），并依此作为进一步开展个人外汇业务现场监管工作的数据基础，做到有的放矢。

2 两表连接实现表格合并或数据比对功能

在业务系统中导出的基本数据表格通常无法适应用户意愿，如导出表的收入和支出一般是两张表，而在较多情况下用户希望得到收入与支出的复合表；或是对于两个不同数据来源的业务数据，如用户希望经过对比发现两表中一致或不一致的记录，获取多来源数据对比核查的线索。从关系数据库的角度分析，即是通过多表连接来比较表格的内容，而在表格程序中可以通过以下方法来实现。

从两种表的特性分析，收入表中有的机构在支出表中不一定存在，反之亦然。现以收入表为基准，如收入表中的机构在支出表中存在，将支出金额字段加入到收入表中；如收入表中的机构在支出表中不存在，则显示支出为0，支出金额字段如下所示：=IF（ISNA（VLOOKUP（A3，Sheet1！$A$3：$A$1021，1，FALSE）），0，INDIRECT（"Sheet1！D"&MATCH（A3，Sheet1！$A$3：$A$1021，0）+2））。最外层IF函数的含义为，如收入表当前记录表示机构在支出表中不存在，则支出金额字段赋值为0；如存在则在支出表中找到相同机构记录，并将其交易金额字段值赋值给收入表的“支出金额”字段。VLOOKUP函数，用来在支出表的“组织机构代码”字段中查询是否存在收入表当前记录所代表的机构信息；INDIRECT函数是用来将支出金额字段作相应定位之后赋值到收入表相应字段中；MATCH函数用来返回支出表“交易金额”字段中查找到的对应的行值。

将以上公式复制到每个“支出金额”单元格中，可通过MATCH函数得到收入表中某机构在支出表中的位置，值为#N/A的表示该机构在支出表中不存在。“支出金额”表示该机构在同一时间段的支出总额，值为0的即表示支出表中不存在该机构的情形。再用相同的方法，以支出表为基准做一次收入金额的插入操作。最后将两个表格粘贴到一个新表中，通过在新列插入形如=COUNTIF（A$3：A3，A3）的函数，将辅助列值为2的记录全部删除，从而去除重复记录，所得结果见表2。

以上方法亦可用在数据核查工作中，用于比对不同数据来源的表间记录的异同，从而挖掘出因数据源不统一造成的难以发现的违规线索。

以上两种方法以及其中涉及的EXCEL或WPS表格函数对于数据集操作虽常见但功能强大，经过适当的逻辑组合可以达到与SQL查询语言同样的数据加工效果。将会对提高工作效率与业务创新起到极大帮助，值得经常使用表格软件的用户深入探索与掌握。

参考文献

[1]李兴勇.外汇监管中国呢业务数据的筛查技巧[J].金融时代科技，2011（11）：79.

作者单位

国家外汇管理局安徽省分局 安徽省合肥市 230091endprint