审计会用到的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”复制到剩余两个需要求人民币价格的单元格上，就可以很方便地求出结果了。
表1

　A
　B
　C
D
　E
　1
美元汇率

　2
8.127

　3

　4

　5
材料
币别
价格
人民币价格
采购数量
　6
材料A
USD
12
=C6*$A$2
1200
　7
材料B
USD
15
=C7A$2
1300
　8
材料C
USD
11
=C8*$A$2
1320
9
材料A
USD
12
=C9*$A$2
1100

2、 连字符“&”
在实际运用EXCEL进行审计的时候，我们为了能在两个数据库之间找一个合适的比较标准，有时需要将两个或以上的单元格连接起来。这时，我们可以用字符“&”将两个或以上的单元格连接起来。
例子：
我们想统计一下美元采购价格为12的材料A的采购数量。这时，我们可以将单元格“A6”与单元格“C6”连接起来再分类汇总即可(如下表2)。
表2

　A
　B
　C
D
　E
F
　1
美元汇率

　2
8.127

　3

　4

　5
材料
币别
价格
人民币价格
采购数量

　6
材料A
USD
12.00
=C6*$A$2
1,200.00
=A6&"---"&C6
　7
材料B
USD
15.00
=C7*$A$2
1,300.00
=A7&"---"&C7
　8
材料C
USD
11.00
=C8*$A$2
1,320.00
=A8&"---"&C8
9
材料A
USD
12.00
=C9*$A$2
1,100.00
=A9&"---"&C9
注意：用连字符“&”计算出的结果是文本型字符，也就是文本格式，不能用来加、减、乘、除等数学运算。如果文本型字符是数字，那么我们可以用函数value( )将其转换为数值性字符，然后才能进行数学运算。（函数value( )的用法见下面）

3、 value( )
在审计的过程中，我们也经常需要导出ERP数据库里的数据到EXCEL表格中进行处理。但在转换的过程中，有些软件不能自动把ERP数据库里的数值型字符转换为数值型字符，只能是文本型字符，结果造成我们对数据进行处理时遇到很大的困难。如果遇到这种情况，我们可以用函数value( )来进行转换
语法：value(text)
说明：“text”是文本型字符，可以是直接输入文本，如：value(“134”)；也可以引用其他单元格，如：value(E6)。我们在审计工作中常用后者。函数value( )得到的结果是数值型字符，主要用于将代表数字的字符串转换为数字。
例子：
我们先把A列设置文本格式，然后再用函数value（）把它转换为数值。具体操作见表3
表3

　A
　B
　C
D
　E
F
　1
12
=value(a1)

　2
10
=value(a2)

　3
11　
=value(a3)

4
=sum(a1:a4)　
=sum(b1:b4)　

　5

4、 去除空格键函数-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列中前面的是分公司代码，后面是采购单号。我们现在要把所有的采购单号取出来分析，可以这样处理：
表4

　A
　B
　C
D
　E
F
　1
A1 PO0512112
=right(a1,len(a1)-3)

　2
A1 PO0512001
=right(a2,len(a2)-3)

　3
B1 PO200411010A
=right(a3,len(a3)-3)

4
B1 PO200512121
=right(a4,len(a4)-3)

　5
C1 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），把“物料信息表”中的的物料名称和单位相应地取到“物料进仓明细表”中。
表5

　A
　B
　C
D
　E
1
材料信息表

2
物料编号
物料名称
单位

3
N001
材料A
张

4
N002
材料B
公斤

5
N003
材料C
公斤

6

7
物料进仓明细表
8
物料编号
物料名称
单位
进仓时间
进仓数量
9
N001
=vlookup(a9,$a$2:$c$5,2,false)
=vlookup(a9,$a$2:$c$5,3,false)
2005-9-31
20．00
10
N002
=vlookup(a10,$a$2:$c$5,2,false)
=vlookup(a10,$a$2:$c$5,3,false)
2005-10-14
12．00
11

N001
=vlookup(a11,$a$2:$c$5,2,false)
=vlookup(a11,$a$2:$c$5,3,false)
2005-10-21
24．00
12
N003
=vlookup(a12,$a$2:$c$5,2,false)
=vlookup(a12,$a$2:$c$5,3,false)
2005-11-12
25．00
13
N002
=vlookup(a13,$a$2:$c$5,2,false)
=vlookup(a13,$a$2:$c$5,3,false)
2005-12-21
9．50
小提示：在公式中引用其他单元格时，可以直接将光标移动到目标单元格或用光标选取引用范围，再输入分格符“，”即可。
另外，要改变单元格的引用方式，在输入完单元格按F4。

8、 sumif( )
语法：SUMIF(range, criteria, sum_range)
说明：
range：为用于条件判断的范围；
criteria：用于判断的标准；
sum_range：实际求和的范围。
我们在运用该公式求和时要注意，range和sum_range是一一对应的关系，如果他们的对应关系错了，求出的结果也不一定正确。我们还是以表5中的“物料进仓明细表”为例子，用sumif（）分类汇总物料出仓数量，见表6
表6

　A
　B
　C
D
　E
1
材料信息表

2
物料编号
物料名称
单位
进仓总数

3
N001
材料A
张
=sumif($a$8:$a$13,a3,$e$8:$e$13)

4
N002
材料B
公斤
=sumif($a$8:$a$13,a4,$e$8:$e$13)

5
N003
材料C
公斤
=sumif($a$8:$a$13,a5,$e$8:$e$13)

6

7
物料进仓明细表
8
物料编号
物料名称
单位
进仓时间
进仓数量
9
N001
材料A
张
2005-9-31
20．00
10
N002
材料B
公斤
2005-10-14
12．00
11
N001
材料A
张
2005-10-21
24．00
12
N003
材料C
公斤
2005-11-12
25．00
13
N002
材料B
公斤
2005-12-21
9．50

9、 其他的一些函数
我们在实际运用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.Delete
Loop
ActiveCell.Offset(1, 0).Select
Loop
End Sub

*自定义函数——tax( )
a、 新建工作表
b、 打开VBA编辑器
c、 插入一个模块
d、 编辑代码
Public Function tax(base As Double, free_amt As Integer) As Double
Select 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 – 1375
Case Is <= 60000
tax = (base – free_amt) * 0.3 – 3375
Case Is <= 80000
tax = (base – free_amt) * 0.35 – 6375
Case Is <= 100000
tax = (base – free_amt) * 0.4 – 10375
Case Is > 100000
tax = (base – free_amt) * 0.45 – 15375
End Select
End Function
e、 保存代码
f、 工作表另存为“加载宏（*.xla）“
g、 选“工具”-“加载宏”-“浏览”，点tax.xla后确定，再确定即可在EXCEL载入自定义函数。