Excel在污染源普查工作中数据查重的应用

2018-11-13付立苹

资源节约与环保 2018年10期

姚磊付立苹

（昆明市环境科学研究院云南昆明 650032）

引言

污染源普查是重大的国情调查，是环境保护的基础性工作,对掌握各类污染源的数量、行业和地区分布情况，了解主要污染物产排和处理情况，建立健全重点污染源档案，准确判断我国当前环境形势，制定实施有针对性的经济社会发展和环境保护政策、规划，加快推进生态文明建设具有重要意义[1]。

污染源普查工作中，数据的采集和对数据的处理贯通整个工作的所有环节，是该项工作的重中之重。其中，在数据处理的过程中，会多次涉及到数据查重的问题。例如，获得来自于不同职能部门工业企业原始名录后，需要进行汇总并查重比对，剔除重复的企业形成基础名录。查重工作处理得当，能够为后续的数据处理带来便利，避免重复调查和重复统计，节约有限的人力资源。

本文根据Excel 2016（Excel 2007或以上版本均可）提供的功能，提出几种数据查重处理的方法，并对几种方法的优劣进行探讨，以期污普工作者在处理数据查重问题时能够找到一种适合自己的最佳方法。

现以第二次污染源普查在建立工业企业基础名录中的查重比对的技术要求为例，详细介绍各种方法。根据要求，用来比对的字段包括统一社会信用代码，组织机构代码以及企业名称。不同来源的数据查重比对，只要其中一数据源的某条记录中以上3个字段中有一项的值和另一数据源的对应字段的某条记录值是相同的（不含空值），则认为分别来自两个数据源中的这两条记录是重复的，即这两条记录都是指向同一工业企业。

1 Excel查重方法

下述所有的方法在应用前均须把两组不同来源的数据根据字段对应的原则复制到同一工作表中，并新建一列字段标注数据来源以方便比对。本文实例中使用了12条记录。

1.1 快速删除重复值法[2]

选中表格区域中的任意一个单元格，如A7单元格。单击“数据”选项卡中的“删除重复值”按钮，打开“删除重复值”对话框。根据污普查重比对技术要求，只单独勾选“企业名称”字段，同时勾选“数据包含标题”复选框，单击“确定”后根据提示完成操作。再次执行“删除重复值”的操作，将“删除重复值”对话框中的字段单独勾选为“统一社会信用代码”，操作完毕后，接着对“组织机构代码”字段进行“删除重复值”操作。

1.2 高级筛选法[2]

选中表格区域中的任意一个单元格，如A7单元格。单击“数据”选项卡中的“高级”按钮，弹出“高级筛选”对话框。点选“在原有区域显示筛选结果”，将光标定位到“列表区域”框内，将原有内容修改为“$C$1:$C$13”（即企业名称字段对应的所有数据集），然后勾选“选择不重复的记录”复选框，最后单击“确定”按钮。将筛选结果复制拷贝到另一新建的工作表中，接着对“统一社会信用代码”应用高级筛选，完成后再将二次筛选结果复制到另一新建工作表中，然后对“组织机构代码”也应用高级筛选。

1.3 函数公式法

1.3.1 COUNTIF函数法[3]

分别选中E2、F2、G2单元格，然后分别输入函数公式：=COUNTIF(A$2:A2,A2&"*")、=COUNTIF(B$2:B2,B2)、=COUNTIF(C$2:C2,C2)，在 H2 单元格内输入=IF((E2＞1)+(F2＞1)+(G2＞1),"重复","")，选定E2:H2,向下拖动至第13行，则H列内显示“重复”值其所对应的条目属于重复企业条目。

1.3.2 SUM函数数组求和法

分别选中E8、F8、G8单元格，然后分别输入函数公式：{=IF(A8="",0,SUM((A8=$A$2:$A$7)*1))}、{=IF(B8="",0,SUM((B8=$B$2:$B$7)*1))}、{=IF(C8="",0,SUM((C8=$C$2:$C$7)*1))}，在 H8 单元格内输入=IF((E8＞0)+(F8＞0)+(G8＞0),"重复","")，选定 E8:H8,向下拖动至第 13 行，则H列内显示“重复”值其所对应的条目属于重复企业条目。

1.3.3 IF函数综合排序法

选中C1单元格，对字段“企业名称”进行“降序”排序。在E3单元格处输入：=If(C3=C2,row(),0),向下拖动至第13行，则E列内显示值大于0的单元格其所对应的条目属于重复企业条目。该方法的进阶：可将不同数据源的重复数据并排显示，不同重复项对应不同的筛选值，操作如下：完成E列赋值操作后，选择E3:E13，复制，原有位置选择性粘贴→数值，再次选择E3:E13，右击单元格“F2”→粘贴，将值粘贴于F列内，E、F列两列数据挫位排列。在G1单元格内输入字段“筛选值”，G2单元格内输入公式：=E2+F2，并向下拖动至G13，选择G2:G13,复制，右击单元格“G2”，点击选择性粘贴→数值，将值粘贴于G列内，然后对G列进行降序排序，则可以将来自不同数据源的重复数据并排显示，方便数据间的比对。在比对完“企业名称”删除重复数据后，可重复以上步骤分别对“统一社会信用代码”和“组织机构代码”进行重复值比对剔除。

2 各种查重方法的比较

上述各种查重方法均有其优缺点。快速删除重复值法操作步骤少，在所有的查重方法中最为快捷，非常适合对Excel不熟练的新手操作，但因为是直接删除数据，删除的数据是哪些并不能一目了然的呈现出来，同时还须做好原始数据的备份；高级筛选法操作步骤相对繁琐，但操作浅显易懂，也适合新手应用，而且在筛选出不重复值的同时还保留了原始数据，但不能直观显示重复值是哪些条记录；函数公式法的应用需要对Excel函数有一定的了解，清楚函数公式的逻辑所在，并能够通过公式生成的值来辨别对应的条目是否属于重复值。但函数公式法能够把原始记录中的重复与非重复数据非常清晰的呈现出来。函数公式法中(1)、(2)相对来说步骤较少，可以对“企业名称”、“统一社会信用代码”、“组织机构代码”3个字段同时进行查重，且筛选出的重复值仅属于其中一个数据源，剔除重复值相对便捷；但要注意COUNTIF函数在计算长字符串时，会将15位后面出现的任何数字都视为0，若两个字符数相等的长字符串字符数多于15位且前面15位也都相等，COUNTIF会视为等值，由于统一社会信用代码为18位字符串，故以COUNTIF进行查重时须在公式中添加“&"*"”,使COUNTIF函数识别超过15位的字符串;函数公式法(3)操作步骤相对较多，且排序后应用“IF”函数筛选出的重复值可能来源于两个数据源中，若要筛选出某个数据源中的重复值，则要通过“进阶”操作得出筛选值，再通过自定义排序（“数据来源”字段作为主要关键字、“筛选值”字段作为次要关键字）显示出来。但该方法的优点是可以通过“进阶”操作将来自两个数据源的重复值并排显示，且不同重复项其对应筛选值也不一样，在后期清查建库过程中需要精确比对剔除重复数据时，此法反而相对便捷。在应用函数公式法时须注意，公式不能直接用来排序或筛选，必须将公式进行选择性粘贴生成数值后才能进行排序或筛选。

3 讨论

在污染源普查实际工作中，从不同部门获取的名录数据是相当繁杂的，一是数据的条目数量通常上千，所列的字段不局限于企业名称、统代码及组代码，还有诸如联系地址、行业类别等字段，且来自不同部门的数据所列字段也不一样；二是数据的录入不规范，例如原始企业名录库中存在极少数重复企业，部分企业缺少统代码和组代码信息，部分数据录入时存在首/尾空格录入问题，在查重比对分析中会引起漏查，另外部分统代码和组代码是纯数字，在录入时格式若不设置好会以科学计数法显示，还有企业名称录入时，全称中带有XX省、XX市、“有限责任公司”的，“省”、“市”、“责任”等字眼被省略，从而在比对查重时同一企业会被计算机识别为不同企业。因此，当拿到企业名录数据后进行查重比对前，需要对数据进行前处理。

本文在此建议数据前处理按以下步骤进行：①规范表格样式，确定表格所需字段及排列顺序；②空格替换，将所有单元格内的空格替换为空值，但数据本身就需要空格的不能替换；③将科学计数法显示的数据变更成数字串显示；④填充空值，填充值须简单、识别度高且与所在字段对应的其他数据不重复，如a1、a2…a3…；⑤剔除各原始数据源内的重复值。

按以上步骤执行完数据前处理就可以进行查重比对分析了。针对不同数据源企业名称录入不统一的问题，可以在执行完第一次重复值剔除后，复制并拷贝企业名称字段列，在拷贝列中将“XX 省”、“YY 市”、“XX”、“YY”等替换为空值，将“有限责任公司”、“有限公司”替换为“公司”，并按IF函数综合排序(进阶)法进行查重，查重结果须结合原始企业名录进行比对，逐一剔除。