计算机辅助审计常用的数据分析模型

王光伟　邬华琼　苏莉民 王一然

[摘 要] 计算机辅助审计的数据分析技术主要是依据数据分析模型进行，本文以卫生计生系统中的医院业务数据、财务数据为背景，重点对系统对账审计、重号查找审计、断号查找审计、班福（Benford）定律审计这四种审计方法，以SQL数据库技术和Excel函数的运用介绍了计算机辅助审计的数据分析模型。

[关键词] 计算机辅助审计；数据分析；审计技术模型

doi ： 10 . 3969 / j . issn . 1673 - 0194 . 2016. 05. 024

[中图分类号] F239.45 [文献标识码] A [文章编号] 1673 - 0194（2016）05- 0042- 02

0 引 言

常用的计算机辅助审计技术可以分为两类：一类是验证程序/系统的CAATs，即面向系统的CAATs；另一类是用于分析电子数据的CAATs，即面向数据的CAATs。数据分析是计算机信息化审计的主要审计方法。数据分析不仅是一种技术和方法，更是一种思维方式的运用和体现。审计署绩效报告（2014年度）明确推广“总体分析、发现疑点、分散核实、系统研究”的数字化审计方式，有效提高了审计质量和效率。

计算机辅助审计的数据分析技术，主要是依据数据分析模型进行数据挖掘。通过对业务系统数据进行采集、清洗、整理、分析，从而发现疑点和线索，在审计实践中，笔者探讨了计算机辅助审计常用的系统对账审计模型、重号查找模型、断号查找模型、班福（Benford）定律分析模型的应用，从中找到了审计的技巧和捷径，现以卫生计生系统中的医院业务数据、财务数据为背景，重点对这四种审计方法模型进行介绍。

1 系统对账审计模型

由于医院财务系统与业务系统、业务系统之间是相对独立的，财务数据与业务数据存放于不同的数据库表，二者并不完全一致，如放射登记系统和收费系统、病人自助缴费系统和收费系统之间的数据，这需要对业务系统之间的数据定期对账。可是，在实际工作中，存在财务数据与业务数据对某一具体指标的核对存在一定的难度，然而，通过建立系统对账审计模型，就能轻松地对不同系统之间的数据进行查验复核。

建立系统对账审计模型，即通过相关的业务数据表，通过各表主键联接从而得到审计所需的分析字段，判断审计疑点是否存在，这种系统对账审计模型最关键的是找准各表之间存在的关联字段。在实际工作中，主要有SQL查询法和Excel函数查找法。

1.1 SQL查询法

假设在同一审计期间，门诊收入表（mzsr）有sj_no、name、收费项目、income_mz等字段，病人自助系统有自助缴费记录表（zzjf），有mz_id，card_no、zzjfe等字段。两表共同的关键字段是门诊号即sj_no和mz_id。

Select sj_no，mz_id，income_mz，zzjfe from mzsr，zzjf where sj_no=mz_id and income_mz<>zzjfe

1.2 Excel函数查找法

格式：INDEX（参数1，MATCH（参数2， 参数3，0））

门诊收费表有门诊号、姓名、收费项目、金额等字段，病人自助系统有自助缴费记录表，有门诊号，姓名、医保卡号或银行卡号、金额等字段。两表共同的关键字段是“门诊号”。参数1为收费金额单元格范围引用，参数2为当前表“门诊号”单元格，参数3为门诊号单元格范围引用。其中MATCH（参数2， 参数3，0）是返回当前表“门诊号”在需查找收费金额的收费表门诊号单元格范围引用的位置序号， INDEX（参数1，“找出的位置序号”）是返回需查找金额的收费表收费金额单元格范围引用相应单元格的数值（Value）。

如以门诊收费表为当前表，自助缴费记录表为需查找缴费金额的表，没有找到表示门诊收费表有收费数据，自助缴费记录表没有收费数据，可能存在自助缴费失败，金额不一致也表示部分自助缴费失败。主要技术是关联两表字段“门诊号”查找收费金额表，比较是否存在门诊收费表有收费数据，而自助缴费记录表没有收费数据，分析结果可能存在自助缴费失败，金额不一致也表示部分自助缴费失败。并对查询结果与相应的财务数据核对，进一步判断审计疑点。

2 建立重号查找模型

医院财务系统、业务系统生成的数据，有很多是连续型数据，如门诊收据号、住院收据号、记账凭证号、门诊号、住院号等。建立重号查找模型即按照连续型数据既不能重号也不能缺号的规定。手工审计方式主要采用抽查的方式进行检查，在计算机辅助审计中，主要是采用SQL查询语句或Excel数据分析工具，可以对全部的连续型数据进行重号、断号查验，找出重号或断号后，再以纸质凭证进行查验。

2.1 SQL统计函数查询法

设门诊收入数据库表mzsr中有收据号sj_no为连续型数据，利用SQL语句可找出表中重号的收据号，其主要统计函数count（sj_no），执行SQL语句后，数据记录数大于1的结果就有可能存在重复的收据号；如果设立收据号条件大于1，就可以查询出重复收据的次数。

步骤一：查询重复收据号记录

select distinct sj_no，count（sj_no） from mzsr group by sj_no

步骤二：查询重复收据的次数

select sj_no，sl from （select sj_no，count（sj_no） as sl from mzsr group by sj_no） as chtable where sl > 1

2.2 Excel数据透视表法

在Excel环境下，查找重号最简单的方法是利用Excel提供的数据透视表向导功能生成数据透视表。其操作过程如下：假设Sheet表名为“重号”， A列为门诊收据号数据，A1为“门诊收据号”，通过“数据”菜单（Excel2003）下“数据透视表和数据透视图”可启动数据透视表向导功能，在数据源区域对话框中输入“重号！￥A：￥A”，在布局对话框中，拖动字段“门诊收据号”到“行（R）”，同时拖动字段“门诊收据号”到“行（D），字段汇总方式设置为“计数”，汇总结果中大于1的就是找出的门诊收据重号，与上述SQL语句中第一种结果完全相同。

3 建立断号查找模型

所谓断号查询，一般是指在一组或多组连续的流水号码中找出中间缺少的号码。在审计过程中，建立断号查找模型的思路是：断号就是在当前数据库的流水号中不存在的号码，同时这些号码又可以理解成当前数据库流水号中某一个号码加1后得不到的号码。这里主要介绍SQL断号查找验证法和Excel断号查找验证法。

3.1 SQL断号查找验证法

假设有了上述门诊收据号数据库表，下面SQL语句可找出表中断号的收据号。本文测试表中断号为8、17—19，得到结果 “8—8”或“17—19”。

SELECT sj_no1， （select min（sj_no）-1 from mzsr where sj_no> sj_no1） assj_no2 FROM （SELECT sj_no+1 AS sj_no1 FROM mzsr WHERE sj_no+1 not in （select sj_no from mzsr） and sj_no <（select max（sj_no） from mzsr）） AS dh。

3.2 Excel断号查找验证法

在Excel环境下，对断号筛选步骤如下：设A列为门诊收据号，数据从A2开始，在B3单元格输入公式：IF（A3-A2-1>=1，A2+1&"-"&A3-1，""），使用填充柄填充至所有收据号区域，即可找出单个断号或连续的几个断号，本文测试表中断号为8、17—19，得到结果同SQL语句查询结果。

4 班福（Benford）定律分析模型

Benford定律是指一个没有人为规则限制生成的一组数据数列，数列的首位数字n之和遵循lg（1+1/n）的规律，数字1出现的频率为lg（1+1/1）的绝对数30.103%，数字2出现的频率为lg（1+1/2）的绝对数17.609%，等等。因此，根据班福（Benford）定律“人们处理的数值数据中，首位出现较小数字的可能性比出现较大数字的可能性大”建立审计分析模型。如，门诊收入、住院收入等利用SQL或Excel查询数字出现的频率，从查询结果中会发现，如果有人为的故意则破坏了这个定律，审计人员必须以此为线索做深入调查，以获取证据。

4.1 SQL查询首位数字出现的频率

设门诊收入数据库表mzsr中有门诊收入字段income_mz，下面SQL语句可分析表中收入数据首位数字1-9每个数字出现的频率。

select bfsz，count（bfsz）/（select count（income_mz） from mzsr）*100 as bfs from （select substing（income_mz，1，1） as bfsz from mzsr） as bf group by bfsz

上述语句可统计出数据系列首位数字1-9每个数字出现的频率。

4.2 Excel运用COUNTA（）函数功能

在Excel环境下，设A列为门诊收入，收入数据从A2至A10 000，在B2单元格输入公式：Ieft（A2，1），填充至B10 000；在C2至C10输入1-9，在D2单元格输入公式：COUNTIF（ B 2： B 10 000，C2），填充至D10；在D11单元格输入公式：COUNTA（￥B 2： B 10 000） ，在F2单元格输入公式：D2/ D 11，填充至F11。则F2至F10为收入数据首位数字1-9每个数字出现的频率。

以上探讨了卫生计生系统常用的几个数据分析模型，增强了审计准确度，节约了审计时间，提高了审计工作效率。SQL语句模型适合有数据库基础审计人员使用，Excel分析模型适合普通审计人员使用。其中，系统对账模型属于验证业务数据真实性的审计分析方法，重号分析、断号分析、班福定律分析属于发现线索的审计分析方法，大数据时代的审计将更是数据分析模型广泛使用的审计时代。

主要参考文献

[1]陈伟，张金城，QIU Ro-Bin.计算机辅助审计技术（CAATs）研究综述[J]. 计算机科学， 2007（10）：290-294.

[2]刘斌.内部审计信息化中辅助审计系统领域探索——数据编网定向审计模型的构建及应用 [J].中国内部审计， 2014（6）：65-70.