您当前的位置:首页 > 经验交流

巧用Excel函数进行简单数据比对分析

发布日期:2022-09-14 16:52   作者:寿县审计局 张敏    来源:市审计局    阅读: 次   字体:[ 大 ] [ 中 ] [ 小 ]

SQL 、Oracle数据库等都是审计中常用的数据筛选分析工具,但对于部分非计算机专业或未参加过计算机审计培训的审计人员,想要熟练的掌握使用这些数据库进行数据筛选分析,仍有一定的难度。为了方便审计人员进行数据审计,下面将介绍Excel中的两种函数,可以简单的对一些小型数据比对分析。

一、使用工具和适用范围

使用工具:Excel表格。

适用范围:少量表格数据,使用单个关键条件,一对一进行比对。

二、具体操作

(一)准备工作

1. 在“Sheet1”放上数据一(表1), 在“Sheet2”放上数据二(表2)。

2.先去掉(表1和表2)空格,避免比对错误:点击开始→查找和选择→替换→在“查找内容处”按一下空格键→点击“全部替换”(两个表格都要操作)。当完全去掉空格时,会出现下图:

(二)使用VLOOKUP函数。

1.在需要比对数据(表2)的右边空白处,点击公式—fx(插入函数),选中VLOOKUP(如果常用函数中找不到请选择“全部”)如下图:

2.点击确定。弹出函数参数窗口:

①第一行选中(表2)要比对的一列身份证号码(以身份证号码为例);

②第二行选中(表1)要一起比对的身份证号码;

③第三行填“1”;

④第四行填“0”;

如下图:

3.点击确定。出现“N/A”(表示在另外一个Excel表格中不存在)或一串同样的身份证号码(也就是两边存在这个号码)。然后将鼠标移到结果“N/A”或身份证号码的表格右下角,当变成实心“十”字时,双击左键即可显示下面所有数据的比对结果。如下图:

4.点击筛选。取消“N/A”选项,所得结果为与表1相同身份证号的表2中的具体数据。如下图:

(三)使用COUNTIF函数。

1.在需要比对数据(表2)的右边空白处,对应的单元格中输入“=IF(COUNTIF())”,如下图:

2.将鼠标放在“=IF(COUNTIF())”中间的小括号中,点击表1,在出现的表1界面中选中所需要进行比对的单元格的数据(以身份证号码为例),即“=IF(COUNTIF(表1!D$3:D1574))”【“D$3”表示从D3开始锁定】,如下图:

3.回到表2的公式,在后面输入逗号,然后在逗号后面输入需要比对数据的单元格以及逗号,包含与不包含,即

输入“=IF(COUNTIF(表1!D$3:D1574,D3&"*"),"包含","不包含")”【上述,""等符号均是在大写或英文输入法格式下输入;且因为COUNTIF函数只判断前15位,故使用&"*"表示连字符在原有基础上连接通配符】,如下图:

4.按回车,显示为包含的表示表2中的数据是表1中的,显示为不包含的就不是,再从这一行拉下填充,全部数据就可筛选完毕。

5.点击筛选。取消“不包含”选项,所得结果为与表1相同身份证号的表2中的具体数据。如下图:

分享到: