利用vba操作excel文件

利用vba操作excel文件

1利用vba功能进行数据匹配由于操作的调整，需要从excel文件中选择相对应的数据进行匹配。由于数据采集录入的时间不一、格式不一,需要将数据格式逐一清理,再按要求进行数据匹配。由于数据量大,匹配条件多,人工操作非常复杂、麻烦,工作强度大。利用VBA语句可以将工作迅速高效地完成。需要完成的工作是将Excel文档“表1”与“表2”中的“当事人姓名”、“当事人身份证号码”、“担保人姓名”、“担保人身份证号码”等数据进行逐一比较。如果能发现两份文档中相对应的4栏数据完全相同,就可以认为数据匹配成功。将匹配成功的数据写入到名为“匹配结果”的Excel文档中,在各自的原文件中将已经匹配的数据删除。2排序数据格式2.1单元格格式由于需要匹配的数据都是文本格式,将Excel文档“表1”、“表2”与“匹配结果”中的单元格格式设定为“文本格式”。2.2生成“放空”由于没有统一规范,导致在采集数据时任意输入“空格”。利用Excel文档中的“替换”功能将“”替换为“”来清除空格(即将“空格”替换为无)。2.3x大小的修改代码文档中的新身份证号码的校验码“X”大小写不一。利用Excel文档中的“替换”功能将“x”替换为“X”即可。2.4“heret2”文件的录入检查“表1”中“当事人身份证号码”和“担保人身份证号码”的长度,把不符合位数为15位或18位的数据写入到“表1”的“Sheet2”中,并将原文件中相应的数据删除。利用循环语句逐一检查身份证号码的位数是否符合规定。采用同样的方法检查“表2”中“当事人身份证号码”和“担保人身份证号码”的长度,把不符合位数为15位或18位的数据写入到“表2”的“Sheet2”中,将原文件中相应的数据删除。2.5数据重复认定两份Excel文档中都存在着重复的数据,分别对两份文档中的数据进行清理,相同的数据只需要留存一份,将多余的重复数据清除。如果“当事人姓名”、“当事人身份证号码”、“担保人姓名”、“担保人身份证号码”完全相同,就可以认定为数据重复。将文档中的数据按“当事人姓名”、“当事人身份证号码”、“担保人姓名”分别为第一顺序,第二顺序,第三顺序将数据排序(将相同的数据排列在相邻的行上)。利用VBA语言中的循环语句逐行与下一行比较判断“当事人姓名”、“当事人身份证号码”、“担保人姓名”、“担保人身份证号码”栏的数据是否完全相同。若完全相同,则将下一行中的重复数据删除。清除“表1”中的重复数据:采用同样的方法处理“表2”中的重复数据。3比较匹配数据将两份Excel文档中的重复数据清除后,接下来解决同一人新旧身份证号码格式不一的问题。3.1旧身份号码校验码的生成在Excel文档的数据中,同一人的身份证号码格式有可能不统一,存在着新旧身份证号码之别。同一人的新旧身份证号码的主要区别在于身份证号码的出生日期码和校验码不同。当同一人的身份证号码格式不一时,可以利用旧身份证号码生成新身份证号码,将格式不一的身份证号码统一为新身份证号码。先判断身份证号码是否为旧身份证号码(15位),然后在旧身份证号码中右起第9、第10位数字之间插入“19”(文档中所涉及的当事人和担保人都出生在20世纪),并根据新身份证校验码规范生成新身份证校验码,由此得到新身份证号码。在VBA语句中,处理字符串的函数有“Len”,“Left”,“Right”,“Mid”等。先利用字符串长度函数“Len”来判断身份证号码的长度。若长度为15位,则利用函数“Left”,“Right”选择旧身份证号码的左边6位数字,右边9位数字,在旧身份证号码中右起第9、第10位数字之间插入“19”。然后根据生成的17位数字编码计算出身份证号码的校验码。“公民身份号码”的校验码采用ISO7064;1983,MOD11-2校验码系统。公民身份号码中各个位置上的号码字符值应满足下列公式的校验:3.2两组数据匹配的验证在准备工作完成后,将Excel文档“表1”、“表2”、“匹配结果”打开,在“表1”中应用VBA语句开始比较匹配数据。利用循环语句,将“表1”、“表2”中的“当事人姓名”(a列)、“当事人身份证号码”(b列)、“担保人姓名”(c列)、“担保人身份证号码”(d列)逐一比较。如果4栏信息完全一致,就认为两组数据匹配成功。将“表1”、“表2”中相应的4栏数据依次写入“匹配结果”中,并在“表1”、“表2”中将已经匹配的数据删除。4文件检匹配机构利用VBA语句,在Excel2003中将有着大量数