在审计调查项目中,如何快速汇总上百个甚至上千个调查对象反馈的Excel表格?如何保证填报的数据格式统一规范?此工作代表了原始数据的真实与完整,是奠定整个审计调查项目效率和质量的基石。审计组往往需要花费大量时间用于审核回收的调查表,而将上百个Excel表格正确地合并为数据库表,同样是一项繁重的工作。 近几年,随着人工智能技术和机器学习理论的发展,针对以上问题,审计人员可以尝试实现这样一种程序:它可以适应所有常规审计调查项目的调查表,只需要审计人员给出调查表的正确样本,程序会自动对本次的调查表进行学习,根据学习的结果自动审核并汇总调查对象反馈的大量表格。目前,要实现这样的程序,最困难的并非技术,而是审计人员如何从全新的思维层面来提供审计思路。 学习Excel调查表样本的思路 (一)思路和算法 提供正确样本:将若干已通过审核的表格(即正确样本)保存到samples文件夹下。也可以是审计人员自行制作的样本表格,只要保证所有填制的单元格都是正确的格式即可。 程序将样本矢量化:程序读取samples目录下的所有Excel文件,并将文件矢量化。所谓矢量化就是将文件转化为便于程序分析的格式,即内存中的数组列表(下文称为“矢量化数组vals”)。为了便于理解,以表格形式示意如下:
程序学习正确样本:该步骤是整个程序的核心算法。其中包含的模型具有可扩展性,允许后期不断加入新的审核模型,以提高审核的准确度。 (二)已开发的模型及设计思路 1.工作表检测模型 模型目标:检测每个样本Excel文件中sheet的数目是否一样、sheet的名称是否一致。如果数目或名称存在不一致,就提醒样本有问题。 算法:按sheet名分组,统计不重复的Excel文件名个数。同一个分组内的文件名个数的标准差为零,且其平均数等于所提供样本Excel文件的个数,即检测通过。 2.表类型识别模型 模型目标:识别目标sheet是固定表还是多行表。所谓固定表就是行数与列数均固定不变;多行表即列数固定、行数不固定,调查对象可自主添加行。 算法:计算每个sheet包含的数据行数,按sheet名分组,统计每组行数的标准差,如果标准差为零,则判断为固定表,否则判断为多行表。 3.字符串成分分析模型 模型目标:检测每个Excel文件的每个单元格的字符成分,即获取字符串长度、中文字符个数、英文字符个数和特殊符号个数。 算法:利用字符串处理函数,针对每个单元格的值进行统计,获取所需的数值,并保存到矢量化数组vals中。 4.字符串特殊类型分析模型 模型目标:检测每一个Excel文件的每个单元格,匹配“日期”“金额”“身份证号”“电话号码”等常见的特殊格式,并进行类型标注。 算法:编辑常见数据格式的正则表形成字典(后期可扩充正则表,不断丰富可识别的特殊类型)。根据字典对照单元格中的字符串,判断特殊格式,并保存到矢量化数组vals中。 审核调查对象填报Excel表格的思路 (一)思路和算法 读取Excel表格:将所有回收的表格放在同一个目录targets下。调用前文样本的矢量化方法,以同样的步骤,将审核目标表进行矢量化,保存在内存数组(下文称为“矢量化数组vals_targets”)中。 审核目标表:这个步骤也是程序的核心算法,与样本分析中的模块相匹配,同样具有可扩展性。 (二)已开发的模型及设计思路 1.允许空值审核模型 模型目标:从样本中判断某单元格(多行表则是某列)是否允许空值,审核目标表,不符合样本的生成错误信息写入报告文本。 算法:迭代矢量化数组vals中的单元格,如果单元格在所有样本中的值都不为空,则标注为不允许空值。读取矢量化数组vals_targets中对应的单元格,检测是否为空,如存在空值,将提示信息写入报告文本。 2.字串长度审核模型 模型目标:分析单元格(多行表则是列)的值在所有样本中是否长度固定。如果长度固定,则审核目标表,将不符合固定长度的单元格信息写入报告。 算法:按矢量化数组vals中的单元格(或多行表的列)分组,如果某单元格(或列)分组中所有的字符串长度的标准差为零,则判断该单元格(或列)为长度固定,返回标准长度值。读取矢量化数组vals_targets中对应单元格的长度值,判断是否相等。如不相等,将错误提示写入报告文本。 3.字符串成分审核模型 模型目标:分析单元格(多行表则是列)的值当中包含的中文、英文、符号的个数是否固定,占比是否固定。如果固定,则审核目标表,字符成分不符合标准的,写入报告文本。 算法:迭代矢量化数组vals中某单元格(多行表为列)的中文、英文、数字、符号长度数据,如果在所有样本中,某项长度恒定或占比恒定,则判断为固定成分,返回固定成分值。读取矢量化数组vals_ta rgets中对应的单元格,检测成分是否符合固定成分,如不符合,将错误提示写入报告文本。