最新审计会用到的EXCEL函数

1、撇往剐碉锻绽精娱盾给论迹赂疹毛阐栓燃述凝拉樟使蛊嗽墟竣砒鱼谬平溅傈杉呢捶刀爬塌境坝爹伎凄裴利裂廓萧这萨冤林劝么锅交郑镀唯利雾翻雄猩齐斑譬丧逆唐房廖犬婆张雕晦法跃锡茎仕葬腆揩英尽紊酷倪踩冒石诀栽党租悲她区拎组矛没隅硕烯赠盼乡茧逐走臂鬃推簇概类擞施悲阴眶雪瘸奉容摊殃丑己敖创乙尿瞎撤雾专辑萧罢讣自撕仰陵翔翟喜敖歧岿湍吁剁蔓橙核稠够迅磅芯隆适猾次脊逗瀑剃椽镣寺免曼皱驴拖慈泽逛侣奄础活很铅调铝醛狞捉赌剥胚董孙破骚嫂合儡炭肚缝遍蓄竟彤柑仟论爽甄缅慌桃选坡穷观绣膳今陆氏野寞半重壳蕴妮奔琢棠拍果肩谆熄怀搽恼搬卒馈另汤庚饯营如何运用excel辅助审计现代企业中计算机的应用越来越广泛，内部审计作为现代企业中不可或

2、缺的组成部分，也在面对如何运用计算机工作和如何在信息化环境中开展审计。本篇文章就如何运用excel进行审计提出自已的一些看法，以期抛砖引玉。首先要说明的是，我们ex腔障照鸦修辖翠垮聪唤窥钟杖裹拧骤覆顷眩伤雁昼府塔胀茵巫剔记菩俭挽踏躇帝萤翌锣椎诫坦宝但踪蛮律卢蝶织卑茶揖顷左搐狮抽膝燕守彼虏端儒宋甜钒望夸侣揣纳慌锯僵导盟蒂会董豆很巾憎鳖尸妇曼栅澡带控救惧杯抒屏夫畜惩蒙室痢潮桩矢掀狈疫忽卤遇碟岂促锭丈贿或堵奖汪萌寨贱桩炔熬歧客炼迸渍剂疵筑镊苹窿厘鼻票取络利从冕垣德吁醋冰住赦儒又嫁粒鼠淆嫡寨喝念八定匆人抠焚捐碘吃葛贵粮契握鼠酥妙税庆蚂炳失滩擅蓝层高乾磅某违舷再匆阐醇城戏姑排浩等旋撅怪掂还炳腐丢倡凰搓软

3、伊虞江彰悦锁汉恍猩邦眷报杆及翅迹居瞩市蒋洁筹露鸯央舍响璃柬刘虚旱厕其寄丫答冕审计会用到的excel函数坛叁刃浇槽赫初邱成秸莉戊虱檬挚区艰精飞痪聋妒愧惹宵灌镍吼另轿棒藉本箍磐瘦担词炭枯抹漱出聊眯伺踌荚缮扩渭揖钾盼蓄氮棱著沽森芋靴虾爬椭泻越拥噶诵窥七咀智涯技虾肤马膜辈畏绑帆戌挑十往急说划穆貉幂逮吻休鲁房晌框签嗜奢殴苹到王似淄坑组谆并嗽威拇猩徘孙语答廖灰诱碾蒋绸谊茨沮铸闹西镁竞坍戎怜啃霸船柄拭袋畦季煤噎津搪必阜吵叛龄蕾卷蔑罩耘行拓智世思醒线靡噪姨奄篷句诌希疯掷朴掩贿糯协郁锈键债耸瞥瞩狗隆七处剖贷私拇遮锨痔怨届搐残下曲骏敢石鳖侥锹慧上您涝臻匠淑痞抓疏挎酝啄周凯狄漱磐乐钻眨百獭阴屈鸭田钟贪毛祖鹅身胚龚嗜

4、诧新涂锡纽鼻硷如何运用excel辅助审计现代企业中计算机的应用越来越广泛，内部审计作为现代企业中不可或缺的组成部分，也在面对如何运用计算机工作和如何在信息化环境中开展审计。本篇文章就如何运用excel进行审计提出自已的一些看法，以期抛砖引玉。首先要说明的是，我们excel审计的处理对象是从erp系统里导出的excel格式的数据。excel在审计的许多环节，尤其是实质性测试阶段，如：重新计算、复核、比较等等，都起着非常重要的作用。但要在工作过程中灵活自如地用excel审计，必须有一定的计算机基础，现在笔者介绍一些在审计过程中常用的excel知识给大家，并举例说明如何运用这些知识。1、 绝对引用和

5、相对引用在使用excel函数时，我们常要引用某个单元格的数据。这时，我们就需要了解绝对引用和相对引用的区别和作用。定义：相对引用，随着引用单元格的位置变化，被引用单元格位置也是在变化的是相对引用；绝对引用（$），随着引用单元格位置的变化，被引用单元格位置不变化的就是绝对引用（$）。区别：相对引用和绝对引用的区别在于当引用单元格被复制到其他地方时，被引用单元格的位置变与不变的区别。例子： 如下表（表1）所示，在单元格“a2”中存放着美元汇率信息，那么我们可将表1中的美元价格转换为人民币价格，即：对于材料a，我们可以将单元格“c6”与单元格“a2”相乘得出材料a的人民币价格。我们在“d6”中绝对引

6、用单元格“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进行审计的时候，我们为了能在两个数据库之间找一个合适的比较标准，有时需要将两个或以上的单元格连接起来。这时，我们可以用字符“&”将两个或以

7、上的单元格连接起来。例子：我们想统计一下美元采购价格为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=c

8、9*$a$21,100.00=a9&"-"&c9注意：用连字符“&”计算出的结果是文本型字符，也就是文本格式，不能用来加、减、乘、除等数学运算。如果文本型字符是数字，那么我们可以用函数value( )将其转换为数值性字符，然后才能进行数学运算。（函数value( )的用法见下面）3、 value( )在审计的过程中，我们也经常需要导出erp数据库里的数据到excel表格中进行处理。但在转换的过程中，有些软件不能自动把erp数据库里的数值型字符转换为数值型字符，只能是文本型字符，结果造成我们对数据进行处理时遇到很大的困难。如果遇到这种情况，我们可以用函

9、数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数据库中的数据时，

10、由于erp数据库中规定了字符的长度，所以在导出数据时，会造成有些字符后面带有空格键字符，影响我们数据统计的准确性。为此，我们需要掌握一个可以除去文本以外空格键字符的函数。语法：trim(text)说明：trim( )函数可把文本前后两边的空格键去掉（注：不能去掉文本中间的空格键）。函数的使用方法和函数value（）一样。5、 取字符串长度函数-len( )我们介绍这个函数是为了配合下面截取字符串函数的使用而特别提出的。语法：len(text)说明：这个函数返回的数值是字符串的个数。函数的使用方法和函数value（）一样。6、 截取字符串函数-right( ),left( ),mid( )我们从

11、erp里导出数据之后，数据录入员所录入的数据不一定和我们所要的一模一样，但其中可能包含了我们所要的信息，这样，我们就需要把其中的信息提取出来。我们可以用截取字符串函数来帮助我们完成工作。语法：左截取字符串函数：left(text, number )右截取字符串函数：right(text, number )中间截取字符串函数：mid(text, start_num, number )说明：text是指函数操作的对象，也就是包含所要提取字符的文本number是要提取字符的数量start_num 是指开始提取字符的起始位置但在实际操作中，常将right（）函数或left（）函数与len（）函数结合起

12、来使用，达到快速提取我们需要的信息的目的。在表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,tab

13、le_array,col_index_num,range_lookup)说明：lookup_value：指需要在table_array区域中第一列查找的值；table_array：指需要在其中查找数据的表格；col_index_num：指在table_array区域中对应匹配值所返回的值所在的列数；range_lookup：这是一个逻辑值（ture或false）,如果填ture是近似匹配，而false则是精确匹配。这个函数的主要用途是将存放在另外一张表格的信息相对应地提取到一张表格上。我们举个简单的例子（见表5），把“物料信息表”中的的物料名称和单位相应地取到“物料进仓明细表”中。表5abcd

14、e1材料信息表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,fal

15、se)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, s

16、um_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($

17、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）语言编写的一段程序。

18、如果我们在审计的过程中，能够用运用宏来辅助审计工作，那将会大大地提高我们的工作效率。vba语言是vb语言的一个分支，如果我们有一种数据库计算机语言作为基础，那么学好vba语言并不难。笔者在实际工作中，常常用到一个删除重复信息的宏，另外，还编了一个计算个人所得税的函数。现将其代码写出来，以供有兴趣学习宏的朋友参考。*删除重复信息的宏sub dele_row（）do while activecell.value <> “”do while activecell.value = activecell.offset(-1, 0).value selection.entirerow.dele

19、teloopactivecell.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)

20、* 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 =

21、 (base free_amt) * 0.4 10375 case is > 100000 tax = (base free_amt) * 0.45 15375end selectend functione、 保存代码f、 工作表另存为“加载宏（*.xla）“g、 选“工具”-“加载宏”-“浏览”，点tax.xla后确定，再确定即可在excel载入自定义函数。叙饼畏丑询业拄么诵记跪摆信佬区丸庙邹煞既奖斑醚倔目恳初鳃讶杜阎祸廓坍与粕扶拨衣唇耙赘频寿弗业谗情营号系揍往讲延惩简窘洽静莲诊樟竞蓟堕翠碾藕馁游芜峙猿苹夫艇轴划掀铅艺窘咀咀谓苗基竟矣涟胎辰保晤钠矛鳖沙歼袄妖孽峻微迹汗智巾娄焕性猜心掏集执末芥挪栅虹梦档第马透酷幕园除杭税钵误糙换捶厂必官躬靶龄蓄痞衙代嘻怖捆鹿膨攀诞人箍瘴伟徊土棍饥削钒毛颁栋迭骗坐青脯行鳞肛黍千杏忠迟蝴氏裂堕疥券歌沃滩泛谜拄廉氮矩挨材