审计会用到的EXCEL函数.doc

如何运用EXCEL辅助审计现代企业中计算机的应用越来越广泛，内部审计作为现代企业中不可或缺的组成部分，也在面对如何运用计算机工作和如何在信息化环境中开展审计。本篇文章就如何运用EXCEL进行审计提出自已的一些看法，以期抛砖引玉。首先要说明的是，我们EXCEL审计的处理对象是从ERP系统里导出的EXCEL格式的数据。EXCEL在审计的许多环节，尤其是实质性测试阶段，如：重新计算、复核、比较等等，都起着非常重要的作用。但要在工作过程中灵活自如地用EXCEL审计，必须有一定的计算机基础，现在笔者介绍一些在审计过程中常用的EXCEL知识给大家，并举例说明如何运用这些知识。1、 绝对引用和相对引用在使用EXCEL函数时，我们常要引用某个单元格的数据。这时，我们就需要了解绝对引用和相对引用的区别和作用。定义：相对引用，随着引用单元格的位置变化，被引用单元格位置也是在变化的是相对引用；绝对引用（$），随着引用单元格位置的变化，被引用单元格位置不变化的就是绝对引用（$）。区别：相对引用和绝对引用的区别在于当引用单元格被复制到其他地方时，被引用单元格的位置变与不变的区别。例子： 如下表（表1）所示，在单元格“A2”中存放着美元汇率信息，那么我们可将表1中的美元价格转换为人民币价格，即：对于材料A，我们可以将单元格“C6”与单元格“A2”相乘得出材料A的人民币价格。我们在“D6”中绝对引用单元格“A2”，相对引用单元格“C6”。在“D6”中输入“=C6*$A$2”，然后将单元格“D6”复制到剩余两个需要求人民币价格的单元格上，就可以很方便地求出结果了。表1ABCDE1美元汇率28.127345材料币别价格人民币价格采购数量6材料AUSD12=C6*$A$212007材料BUSD15=C7*$A$213008材料CUSD11=C8*$A$213209材料AUSD12=C9*$A$211002、 连字符“&”在实际运用EXCEL进行审计的时候，我们为了能在两个数据库之间找一个合适的比较标准，有时需要将两个或以上的单元格连接起来。这时，我们可以用字符“&”将两个或以上的单元格连接起来。例子：我们想统计一下美元采购价格为12的材料A的采购数量。这时，我们可以将单元格“A6”与单元格“C6”连接起来再分类汇总即可(如下表2)。表2ABCDE F1美元汇率28.127345材料币别价格人民币价格采购数量6材料AUSD12.00=C6*$A$21,200.00=A6&-&C67材料BUSD15.00=C7*$A$21,300.00=A7&-&C78材料CUSD11.00=C8*$A$21,320.00=A8&-&C89材料AUSD12.00=C9*$A$21,100.00=A9&-&C9注意：用连字符“&”计算出的结果是文本型字符，也就是文本格式，不能用来加、减、乘、除等数学运算。如果文本型字符是数字，那么我们可以用函数value( )将其转换为数值性字符，然后才能进行数学运算。（函数value( )的用法见下面）3、 value( )在审计的过程中，我们也经常需要导出ERP数据库里的数据到EXCEL表格中进行处理。但在转换的过程中，有些软件不能自动把ERP数据库里的数值型字符转换为数值型字符，只能是文本型字符，结果造成我们对数据进行处理时遇到很大的困难。如果遇到这种情况，我们可以用函数value( )来进行转换。语法：value(text)说明：“text”是文本型字符，可以是直接输入文本，如：value(“134”)；也可以引用其他单元格，如：value(E6)。我们在审计工作中常用后者。函数value( )得到的结果是数值型字符，主要用于将代表数字的字符串转换为数字。例子： 我们先把A列设置文本格式，然后再用函数value（）把它转换为数值。具体操作见表3表3ABCDE F112=value(a1)210=value(a2)311=value(a3) 4=sum(a1:a4)=sum(b1:b4)54、 去除空格键函数-trim( )我们在导出ERP数据库中的数据时，由于ERP数据库中规定了字符的长度，所以在导出数据时，会造成有些字符后面带有空格键字符，影响我们数据统计的准确性。为此，我们需要掌握一个可以除去文本以外空格键字符的函数。语法：trim(text)说明：trim( )函数可把文本前后两边的空格键去掉（注：不能去掉文本中间的空格键）。函数的使用方法和函数value（）一样。5、 取字符串长度函数-len( )我们介绍这个函数是为了配合下面截取字符串函数的使用而特别提出的。语法：len(text)说明：这个函数返回的数值是字符串的个数。函数的使用方法和函数value（）一样。6、 截取字符串函数-right( ),left( ),mid( )我们从ERP里导出数据之后，数据录入员所录入的数据不一定和我们所要的一模一样，但其中可能包含了我们所要的信息，这样，我们就需要把其中的信息提取出来。我们可以用截取字符串函数来帮助我们完成工作。语法：左截取字符串函数：left(text, number )右截取字符串函数：right(text, number )中间截取字符串函数：mid(text, start_num, number )说明：Text是指函数操作的对象，也就是包含所要提取字符的文本Number是要提取字符的数量Start_num 是指开始提取字符的起始位置但在实际操作中，常将right（）函数或left（）函数与len（）函数结合起来使用，达到快速提取我们需要的信息的目的。在表4中，我们假定A列中前面的是分公司代码，后面是采购单号。我们现在要把所有的采购单号取出来分析，可以这样处理：表4ABCDE F1A1 PO0512112=right(a1,len(a1)-3)2A1 PO0512001=right(a2,len(a2)-3)3B1 PO200411010A=right(a3,len(a3)-3) 4B1 PO200512121=right(a4,len(a4)-3)5C1 PO200503141C=right(a5,len(a5)-3)7、 vlookup( )语法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)说明：lookup_value：指需要在table_array区域中第一列查找的值；table_array：指需要在其中查找数据的表格；col_index_num：指在table_array区域中对应匹配值所返回的值所在的列数；range_lookup：这是一个逻辑值（ture或false）,如果填ture是近似匹配，而false则是精确匹配。这个函数的主要用途是将存放在另外一张表格的信息相对应地提取到一张表格上。我们举个简单的例子（见表5），把“物料信息表”中的的物料名称和单位相应地取到“物料进仓明细表”中。表5ABCDE1材料信息表2物料编号物料名称单位3N001材料A张4N002材料B公斤5N003材料C公斤67物料进仓明细表8物料编号物料名称单位进仓时间进仓数量9N001=vlookup(a9,$a$2:$c$5,2,false)=vlookup(a9,$a$2:$c$5,3,false)2005-9-31200010N002=vlookup(a10,$a$2:$c$5,2,false)=vlookup(a10,$a$2:$c$5,3,false)2005-10-14120011N001=vlookup(a11,$a$2:$c$5,2,false)=vlookup(a11,$a$2:$c$5,3,false)2005-10-21240012N003=vlookup(a12,$a$2:$c$5,2,false)=vlookup(a12,$a$2:$c$5,3,false)2005-11-12250013N002=vlookup(a13,$a$2:$c$5,2,false)=vlookup(a13,$a$2:$c$5,3,false)2005-12-21950小提示：在公式中引用其他单元格时，可以直接将光标移动到目标单元格或用光标选取引用范围，再输入分格符“，”即可。 另外，要改变单元格的引用方式，在输入完单元格按F4。8、 sumif( )语法：SUMIF(range, criteria, sum_range)说明：range：为用于条件判断的范围；criteria：用于判断的标准；sum_range：实际求和的范围。我们在运用该公式求和时要注意，range和sum_range是一一对应的关系，如果他们的对应关系错了，求出的结果也不一定正确。我们还是以表5中的“物料进仓明细表”为例子，用sumif（）分类汇总物料出仓数量，见表6表6ABCDE1材料信息表2物料编号物料名称单位进仓总数3N001材料A张=sumif($a$8:$a$13,a3,$e$8:$e$13)4N002材料B公斤=sumif($a$8:$a$13,a4,$e$8:$e$13)5N003材料C公斤=sumif($a$8:$a$13,a5,$e$8:$e$13)67物料进仓明细表8物料编号物料名称单位进仓时间进仓数量9N001材料A张2005-9-31200010N002材料B公斤2005-10-14120011N001材料A张2005-10-21240012N003材料C公斤2005-11-12250013N002材料B公斤2005-12-219509、 其他的一些函数我们在实际运用EXCEL审计的过程中，还常常用到month( ), year( )等函数。这些函数简单实用，常常和其他函数组合起来使用。10、 宏所谓宏，就是用VBA（Visual Base Application）语言编写的一段程序。如果我们在审计的过程中，能够用运用宏来辅助审计工作，那将会大大地提高我们的工作效率。VBA语言是VB语言的一个分支，如果我们有一种数据库计算机语言作为基础，那么学好VBA语言并不难。笔者在实际工作中，常常用到一个删除重复信息的宏，另外，还编了一个计算个人所得税的函数。现将其代码写出来，以供有兴趣学习宏的朋友参考。*删除重复信息的宏Sub dele_row（）Do While ActiveCell.Value “”Do While ActiveCell.Value = ActiveCell.Offset(-1, 0).Value Selection.EntireRow.DeleteLoopActiveCell.Offset(1, 0).SelectLoopEnd Sub*自定义函数tax( )a、 新建工作表b、 打开VBA编辑器c、 插入一个模块d、 编辑代码Public Function tax(base As Double, free_amt As Integer) As DoubleSelect Case (base free_amt) Case Is = 0 tax = 0 Case Is = 500 tax = (base free_amt) * 0.05 Case Is = 2000 tax = (base free_amt) * 0.1 25 Case Is = 5000 tax = (base free_amt) * 0.15 125 Case Is = 20000 tax = (base free_amt) * 0.2 375 Case Is = 40000 tax = (base free_amt) * 0.25