审计会用到的EXCEL函数.docx
《审计会用到的EXCEL函数.docx》由会员分享，可在线阅读，更多相关《审计会用到的EXCEL函数.docx（10页珍藏版）》请在冰豆网上搜索。
审计会用到的EXCEL函数
如何运用ＥXCEＬ辅助审计
现代企业中计算机的应用越来越广泛,内部审计作为现代企业中不可或缺的组成部分，也在面对如何运用计算机工作和如何在信息化环境中开展审计。
本篇文章就如何运用ＥＸＣEＬ进行审计提出自已的一些看法，以期抛砖引玉。
首先要说明的是，我们ＥＸＣEＬ审计的处理对象是从ＥRＰ系统里导出的EＸCEL格式的数据。
ＥXＣEＬ在审计的许多环节，尤其是实质性测试阶段,如：
重新计算、复核、比较等等,都起着非常重要的作用。
但要在工作过程中灵活自如地用EＸＣＥL审计，必须有一定的计算机基础，现在笔者介绍一些在审计过程中常用的EXCEL知识给大家，并举例说明如何运用这些知识。
1、绝对引用和相对引用
在使用EＸＣＥL函数时，我们常要引用某个单元格的数据。
这时,我们就需要了解绝对引用和相对引用的区别和作用。
定义:
相对引用,随着引用单元格的位置变化,被引用单元格位置也是在变化的是相对引用；
绝对引用（$），随着引用单元格位置的变化，被引用单元格位置不变化的就是绝对引用（$）。
区别:
相对引用和绝对引用的区别在于当引用单元格被复制到其他地方时，被引用单元格的位置变与不变的区别。
例子:
如下表（表1）所示，在单元格“Ａ２”中存放着美元汇率信息，那么我们可将表1中的美元价格转换为人民币价格,即:
对于材料A，我们可以将单元格“Ｃ6”与单元格“Ａ2”相乘得出材料A的人民币价格。
我们在“Ｄ6”中绝对引用单元格“Ａ2”，相对引用单元格“C６”。
在“D6”中输入“=C6*$A$２”，然后将单元格“Ｄ6”复制到剩余两个需要求人民币价格的单元格上，就可以很方便地求出结果了。
表１
A
B
Ｃ
Ｄ
E
1
美元汇率
２
8.１2７
3
4
5
材料
币别
价格
人民币价格
采购数量
6
材料Ａ
ＵSD
１２
=C6*$A$2
12００
７
材料Ｂ
USD
15
=C7*$A$2
13０0
8
材料C
UＳD
11
＝C8*$Ａ$2
1320
9
材料Ａ
USＤ
１2
＝C9*$A＄2
11０0
2、连字符“&”
在实际运用EXCＥL进行审计的时候,我们为了能在两个数据库之间找一个合适的比较标准，有时需要将两个或以上的单元格连接起来。
这时,我们可以用字符“&”将两个或以上的单元格连接起来。
例子：
我们想统计一下美元采购价格为12的材料A的采购数量。
这时，我们可以将单元格“Ａ6”与单元格“C6”连接起来再分类汇总即可（如下表２）。
表2
A
B
C
D
E
F
1
美元汇率
２
８.127
3
4
5
材料
币别
价格
人民币价格
采购数量
６
材料A
USD
1２．00
=C6*$A$2
1,20０.０0
=A6&＂--－"&C6
7
材料Ｂ
ＵSD
1５.00
=C7*$Ａ$2
1，300.00
=Ａ7＆"-－-"&C7
8
材料Ｃ
USD
11.00
=C8*＄A$2
１,3２0.0０
=A8&＂---"&C８
９
材料Ａ
UＳD
1２.００
=Ｃ９*＄Ａ＄2
1,1００.０0
=A9&＂－－-＂＆Ｃ９
注意:
用连字符“&”计算出的结果是文本型字符,也就是文本格式，不能用来加、减、乘、除等数学运算。
如果文本型字符是数字,那么我们可以用函数value（）将其转换为数值性字符，然后才能进行数学运算。
（函数ｖａlue（ ）的用法见下面）
3、value（）
在审计的过程中，我们也经常需要导出ERP数据库里的数据到ＥＸCＥL表格中进行处理。
但在转换的过程中，有些软件不能自动把ERP数据库里的数值型字符转换为数值型字符，只能是文本型字符，结果造成我们对数据进行处理时遇到很大的困难。
如果遇到这种情况，我们可以用函数vａlue（）来进行转换。
语法：
vａlｕe（texｔ）
说明:
“ｔeｘt”是文本型字符,可以是直接输入文本，如：
vaｌue（“13４”）；也可以引用其他单元格,如:
value（E６）。
我们在审计工作中常用后者。
函数ｖalue（）得到的结果是数值型字符，主要用于将代表数字的字符串转换为数字。
例子：
我们先把A列设置文本格式，然后再用函数vａｌｕe（）把它转换为数值。
具体操作见表3
表3
A
B
Ｃ
Ｄ
E
F
1
12
=value（a1）
2
1０
=vａlue（ａ2）
3
１１
=ｖaｌｕe（ａ3）
4
=sｕm（a1:
a4）
＝sum（b１：
b4）
5
4、去除空格键函数-trｉm（）
我们在导出ＥRP数据库中的数据时,由于ＥRP数据库中规定了字符的长度，所以在导出数据时，会造成有些字符后面带有空格键字符,影响我们数据统计的准确性。
为此,我们需要掌握一个可以除去文本以外空格键字符的函数。
语法:
tｒim（tｅxｔ）
说明:
ｔriｍ（）函数可把文本前后两边的空格键去掉（注：
不能去掉文本中间的空格键）。
函数的使用方法和函数valuｅ（）一样。
5、取字符串长度函数-len（）
我们介绍这个函数是为了配合下面截取字符串函数的使用而特别提出的。
语法：
ｌeｎ（ｔｅxｔ）
说明:
这个函数返回的数值是字符串的个数。
函数的使用方法和函数vａlue（）一样。
6、截取字符串函数－riｇht（ ）,ｌｅfｔ（），mid（）
我们从ERP里导出数据之后，数据录入员所录入的数据不一定和我们所要的一模一样,但其中可能包含了我们所要的信息，这样,我们就需要把其中的信息提取出来。
我们可以用截取字符串函数来帮助我们完成工作。
语法：
左截取字符串函数:
lefｔ（text,numbｅr）
右截取字符串函数：
ｒｉｇht（ｔｅxt,number）
中间截取字符串函数:
mid（text,ｓtａｒt＿ｎｕm,nuｍｂer ）
说明:
Ｔeｘt是指函数操作的对象,也就是包含所要提取字符的文本
Nｕmbeｒ是要提取字符的数量
Start_num是指开始提取字符的起始位置
但在实际操作中，常将rigｈｔ（）函数或lefｔ（）函数与ｌen（）函数结合起来使用，达到快速提取我们需要的信息的目的。
在表４中,我们假定A列中前面的是分公司代码,后面是采购单号。
我们现在要把所有的采购单号取出来分析,可以这样处理：
表4
A
B
Ｃ
Ｄ
E
F
1
Ａ１PO0５12１12
=ｒight（a1,len（a１）-3）
２
A1PO051２001
＝ｒight（a２，ｌen（a２）-3）
3
B１PO２0041101０Ａ
=ｒiｇｈt（a3,len（a3）-3）
4
B1PO２005121２１
=righｔ（a4,leｎ（a４）-3）
5
Ｃ1 ＰＯ２005031４1C
=ｒｉght（a5，len（a5）-3）
7、vｌookup（）
语法：
VLOOKUP（lookup_vａlue，tabｌe＿aｒray,ｃｏl_index＿num,ｒangｅ_lookup）
说明:
lｏokｕp＿vａｌue：
指需要在tablｅ_array区域中第一列查找的值;
taｂle_arrａy：
指需要在其中查找数据的表格;
coｌ＿indｅx_num:
指在table_array区域中对应匹配值所返回的值所在的列数;
ｒange_looｋｕp：
这是一个逻辑值（ture或falｓｅ）,如果填tuｒe是近似匹配,而ｆａlse则是精确匹配。
这个函数的主要用途是将存放在另外一张表格的信息相对应地提取到一张表格上。
我们举个简单的例子（见表5）,把“物料信息表”中的的物料名称和单位相应地取到“物料进仓明细表”中。
表5
A
Ｂ
C
D
E
１
材料信息表
2
物料编号
物料名称
单位
3
N00１
材料A
张
4
N002
材料B
公斤
5
N00３
材料Ｃ
公斤
６
7
物料进仓明细表
8
物料编号
物料名称
单位
进仓时间
进仓数量
9
N001
=vlookuｐ（a9,$ａ＄2:
$c$5,2,fａlｓe）
＝vｌooｋup（a9，＄a＄２:
$c$5,3，fａlｓe）
２005-９-31
20.00
10
N00２
＝ｖloｏｋuｐ（a１0,$ａ＄2：
$c$５,2,ｆａlse）
=vｌooｋup（ａ1０,$a$2:
$c$5，3,falsｅ）
2005-10－1４
12．0０
11
N0０1
＝vlｏokup（a１1，＄a＄２:
$c$5,2,fａlｓe）
=vloｏkｕp（a１1,$ａ$2:
$c$5,3,false）
2０05-10-2１
24．０0
12
Ｎ00３
=vｌｏｏkup（ａ1２，$a$2:
＄c＄5,2,fａlse）
=vｌｏokup（a12，$a$2:
＄c$5,３，false）
2005－１1－12
２5.０0
１3
N002
=vｌookup（ａ１３,$ａ$2:
$ｃ$5,２，false）
＝ｖｌooｋｕｐ（ａ１3，＄a$2：
$c$5，3，falｓｅ）
２０05-１２－21
９.50
小提示:
在公式中引用其他单元格时,可以直接将光标移动到目标单元格或用光标选取引用范围，再输入分格符“，”即可。
另外，要改变单元格的引用方式,在输入完单元格按F4。
8、sumif（）
语法:
SUMIF（ｒａnｇｅ,cｒiｔeｒｉa，sum_range）
说明：
raｎge:
为用于条件判断的范围;
criteria：
用于判断的标准；
ｓｕm＿range:
实际求和的范围。
我们在运用该公式求和时要注意，rａngｅ和ｓuｍ_raｎｇｅ是一一对应的关系，如果他们的对应关系错了,求出的结果也不一定正确。
我们还是以表5中的“物料进仓明细表”为例子,用sｕmif（）分类汇总物料出仓数量，见表6
表6
A
B
C
D
Ｅ
1
材料信息表
2
物料编号
物料名称
单位
进仓总数
３
N0０1
材料Ａ
张
=ｓumｉf（$a＄８:
$a$13，a3，$ｅ＄8:
$e＄１3）
4
N０02
材料B
公斤
=ｓumｉｆ（$a$８:
＄a$１3,a4,$e$8:
$e＄13）
5
Ｎ0０３
材料C
公斤
=sumiｆ（$a$8:
$a$13,a5,＄ｅ$8：
＄e$１3）
6
7
物料进仓明细表
8
物料编号
物料名称
单位
进仓时间
进仓数量
9
N００１
材料Ａ
张
20０5-9-3１
20.０0
10
N002
材料B
公斤
2005-10-14
12.0０
11
N００1
材料A
张
2０0５-１0-21
24．0０
12
N00３
材料C
公斤
２005－１１-12
２5.００
13
N002
材料B
公斤
2００5-1２-21
9.50
9、其他的一些函数
我们在实际运用EXCＥL审计的过程中,还常常用到month（）,year（）等函数。
这些函数简单实用，常常和其他函数组合起来使用。
10、宏
所谓宏,就是用VBＡ（ＶiｓuaｌＢase Apｐlicａｔｉon）语言编写的一段程序。
如果我们在审计的过程中，能够用运用宏来辅助审计工作,那将会大大地提高我们的工作效率。
VBＡ语言是ＶＢ语言的一个分支，如果我们有一种数据库计算机语言作为基础，那么学好VＢA语言并不难。
笔者在实际工作中，常常用到一个删除重复信息的宏,另外,还编了一个计算个人所得税的函数。
现将其代码写出来,以供有兴趣学习宏的朋友参考。
*删除重复信息的宏
Ｓub deｌe_roｗ（）
DoＷｈileＡcｔｉｖeCell.Ｖalue <>“”
DoWｈiｌeActｉvｅCell．Vaｌue =ActiveＣell.Offset（-1,０）.Vaｌuｅ
Selectiｏn.ＥntireRoｗ.Delete
Lｏop
AｃtiveCell．Offset（1，0）.Seleｃｔ
Ｌooｐ
EndSｕb
＊自定义函数——tax（）
a、新建工作表
b、打开VBA编辑器
c、插入一个模块
d、编辑代码
PubｌicＦunctiｏntax（baseAsDoｕbｌe,fｒｅe_ａmｔAｓInteｇｅr）ＡｓDoublｅ
Sｅlect Case（base –frｅｅ_amt）
ＣaseＩs<=0
tax= ０
ＣａｓeIs＜=5０0
ｔａｘ=（ｂａsｅ–ｆreｅ＿ａｍt）*0.０５
CaseIs <＝２0０0
tax=（ｂase –ｆｒeｅ＿amt） *0.１– 25
CaｓｅＩs<= ５000
tax＝（basｅ –free＿amt）* 0.15–125
CaｓｅＩs<＝２0000
tax＝ （base– fｒｅe_aｍｔ）＊ 0.２– 37５
CaｓeIs<=４０0０0
tａｘ=（ｂase– free_amt）＊0.25– 137５
ＣaseIs<=600００
taｘ = （baｓe–ｆｒee_ａｍｔ）*0.3 –3375
Ｃaｓe Is <=80000
taｘ＝ （ｂaｓｅ–free_aｍt）*0.35 –６375
Cａse Ｉｓ<=100000
taｘ＝ （base– free_aｍt）＊0．4–10375
CaseIs> 100０00
tａx ＝（ｂaｓe –free_aｍt）*０.45–1537５
End Sｅlecｔ
ＥndＦuncｔｉoｎ
e、保存代码
f、工作表另存为“加载宏（*.xla）“
g、选“工具”－“加载宏”-“浏览”，点ｔａｘ.xla后确定，再确定即可在EXCEL载入自定义函数。
升级会员 