巧借标准名称校正简称

武汉市审计局  董惠林   王飞

数据整理是开展数据分析的必要步骤，审计组取得的数据，有时质量不是很高，常见的就是企业名称、单位名称和项目名称等，从不同的渠道获取的数据，有的是全称，有的是简称，甚至同一单位简称还不一样，名称不一致为下一步数据统计、分析和比对增加了不少的麻烦，有没有办法对数据进行快速方便的进行整理？答案是肯定的。

本文以常见的企业名称为例，借助审计机关已有的企业标准名称数据，快速将不规整的企业名称数据标准化，以供后期使用。

本例中的数据，“企业标准名称”来源于工商系统数据库导出数据，“简称”为审计组取得的数据，“建议标准名称”为函数生成的数据。本例企业名称均为示例，与实际无关。

示例数据如图一

一、简用函数批量处理常规数据

在D2中输入：

=VLOOKUP("*"&D2&"*",$B$2:$B$26,1,0)

公式介绍：

在B2:B26的范围内查找包含D2关键字的单元格，即“简称”，找到后返回B1:B26第一列的内容，即“标准企业名称”。函数中的“*”代表任意字符，也就是说在B2:B26内查找D2文本前后任意字符的内容，最后一个参数0代表模糊匹配。注意事项，$B$2:$B$26标准企业名称所在区域，根据实际修改。简单使用Excel函数，即可解决大部分数据。。

二、套用函数精细处理复杂数据

上例在处理中，Excel的vlookup函数，查找只返回一个符合条件的结果，实际中可能会有多个匹配结果，对于这类问题，需要更复杂的函数嵌套，这里给出一个解决方案，供读者参考借鉴，还是上面的示例数据，

在D2中输入=INDEX($B:$B,SMALL(IF(MMULT(-ISERR(FIND(MID($D2,COLUMN($A:$Z),1),$B$2:$B24)),$A$1:$A$26+1)=0,ROW($2:24),""),COLUMN(B:B)))

(数组公式，输入后按Ctrl+Shift+Enter三键结束)，向右拖动公式，所有满足条例的结果都显示出来

公式介绍：

用MID+COLUMN函数对简称进行1个字1个字分割；

用FIND函数在“标准企业名称”查找所有“简称”的字，成功返回位置（数字），不成功返回错误值。然后用ERROR转换成TRUE或FALSE，错误的变成TRUE、正确的变成FALSE。用-（负号）再把TRUE转换成-1，把FALSE转换为0；

IF 返回0所在行；

MMULT函数的作用是数组的分行求和。B2:B26+1是生成{1,1,1...} 26个1组成的数组。求和的结果，全匹配的行求和结果为0，其他为负值；

用SAMLL函数先返回第一个匹配行，向右拖到公式逐个返回匹配结果；

用INDEX函数返回B列的全称。

本例使用多层嵌套的Excel函数，精细化解决复杂数据。

三、借用函数人工输入异常数据

对于个别异常数据，如上例，假设简称为“当代地产公司”，企业的标准名称为“武汉当代地产开发有限公司”，上述模糊查找也不会找到，这时需要人工输入，在人工输入时，我们也可以设置公式，根据输入内容不断查找匹配结果，可以简化输入量，公式与上述相同，读者可以自己尝试。借力Excel函数辅助人工输入，省时省力，解决个别异常数据

至此，借助标准名称，巧用Excel函数，首先批量处理大规模数据，根据需求精细化处理复杂数据，最后人工输入少量关键字的方式处理个别差异数据。方法简单易学，极大的减轻了审计的工作量，提高了审计工作的效率，取得了较好的效果。处理不规则的名称在审计应用较多，比如单位内部部门台账与财务账比对，预算指标与国库支付比对等等，经常会出现名称不一致的情况，具有较多的借鉴意义。

一般来说，上述第一步即可解决实际问题，偶尔遇到一些处理效果不太好的数据，可以尝试更精细的处理方法，当然也就需要花费更多的时间。本例中多个函数嵌套、特别是数组函数，使用相对困难，需要一些Excel知识储备。当然，Excel处理数据的功能有限，为了数据处理好地效果，可以使用一些算法，比如字符串匹配的KMP算法、向量的相似度算法等，这些都需要相应编程技术，学习曲线较高，适合更多、更复杂的数据处理，需要根据实际情况进行选择。

巧借标准名称校正简称

武汉市审计局  董惠林   王飞

数据整理是开展数据分析的必要步骤，审计组取得的数据，有时质量不是很高，常见的就是企业名称、单位名称和项目名称等，从不同的渠道获取的数据，有的是全称，有的是简称，甚至同一单位简称还不一样，名称不一致为下一步数据统计、分析和比对增加了不少的麻烦，有没有办法对数据进行快速方便的进行整理？答案是肯定的。

本文以常见的企业名称为例，借助审计机关已有的企业标准名称数据，快速将不规整的企业名称数据标准化，以供后期使用。

本例中的数据，“企业标准名称”来源于工商系统数据库导出数据，“简称”为审计组取得的数据，“建议标准名称”为函数生成的数据。本例企业名称均为示例，与实际无关。

示例数据如图一

一、简用函数批量处理常规数据

在D2中输入：

=VLOOKUP("*"&D2&"*",$B$2:$B$26,1,0)

公式介绍：

在B2:B26的范围内查找包含D2关键字的单元格，即“简称”，找到后返回B1:B26第一列的内容，即“标准企业名称”。函数中的“*”代表任意字符，也就是说在B2:B26内查找D2文本前后任意字符的内容，最后一个参数0代表模糊匹配。注意事项，$B$2:$B$26标准企业名称所在区域，根据实际修改。简单使用Excel函数，即可解决大部分数据。。

二、套用函数精细处理复杂数据

上例在处理中，Excel的vlookup函数，查找只返回一个符合条件的结果，实际中可能会有多个匹配结果，对于这类问题，需要更复杂的函数嵌套，这里给出一个解决方案，供读者参考借鉴，还是上面的示例数据，

在D2中输入=INDEX($B:$B,SMALL(IF(MMULT(-ISERR(FIND(MID($D2,COLUMN($A:$Z),1),$B$2:$B24)),$A$1:$A$26+1)=0,ROW($2:24),""),COLUMN(B:B)))

(数组公式，输入后按Ctrl+Shift+Enter三键结束)，向右拖动公式，所有满足条例的结果都显示出来

公式介绍：

用MID+COLUMN函数对简称进行1个字1个字分割；

用FIND函数在“标准企业名称”查找所有“简称”的字，成功返回位置（数字），不成功返回错误值。然后用ERROR转换成TRUE或FALSE，错误的变成TRUE、正确的变成FALSE。用-（负号）再把TRUE转换成-1，把FALSE转换为0；

IF 返回0所在行；

MMULT函数的作用是数组的分行求和。B2:B26+1是生成{1,1,1...} 26个1组成的数组。求和的结果，全匹配的行求和结果为0，其他为负值；

用SAMLL函数先返回第一个匹配行，向右拖到公式逐个返回匹配结果；

用INDEX函数返回B列的全称。

本例使用多层嵌套的Excel函数，精细化解决复杂数据。

三、借用函数人工输入异常数据

对于个别异常数据，如上例，假设简称为“当代地产公司”，企业的标准名称为“武汉当代地产开发有限公司”，上述模糊查找也不会找到，这时需要人工输入，在人工输入时，我们也可以设置公式，根据输入内容不断查找匹配结果，可以简化输入量，公式与上述相同，读者可以自己尝试。借力Excel函数辅助人工输入，省时省力，解决个别异常数据

至此，借助标准名称，巧用Excel函数，首先批量处理大规模数据，根据需求精细化处理复杂数据，最后人工输入少量关键字的方式处理个别差异数据。方法简单易学，极大的减轻了审计的工作量，提高了审计工作的效率，取得了较好的效果。处理不规则的名称在审计应用较多，比如单位内部部门台账与财务账比对，预算指标与国库支付比对等等，经常会出现名称不一致的情况，具有较多的借鉴意义。

一般来说，上述第一步即可解决实际问题，偶尔遇到一些处理效果不太好的数据，可以尝试更精细的处理方法，当然也就需要花费更多的时间。本例中多个函数嵌套、特别是数组函数，使用相对困难，需要一些Excel知识储备。当然，Excel处理数据的功能有限，为了数据处理好地效果，可以使用一些算法，比如字符串匹配的KMP算法、向量的相似度算法等，这些都需要相应编程技术，学习曲线较高，适合更多、更复杂的数据处理，需要根据实际情况进行选择。

图片说明

图片说明