浅谈EXCEL软件在审计实务中的运用

1、excel在审计实务中的运用【摘要】本文以审计实务为背景，通过介绍excel与word软件的衔接、共享工作簿、公式 函数和随机数发生器的运用，以实现提高审计工作效率、解决实际困难的冃的，达到事半功倍的 效果，对于目前的审计实务工作具有一定的参考应用价值。【关键词】excel软件；审计实务运用；公式函数；随机数发牛器谈到excel软件，大家可能都i分熟悉，因为它是审计丁作的好帮手，其使用频率远远超过 了其他办公类软件。随着审计工作电算化程度的不断提高，无纸化的办公模式必将成为未来的发 展趋势。但仅就目前而言，我们在口常审计工作中经常使用的excel软件功能通常还局限在加减 乘除的简单运算，常使用

2、的也仅是sum、average. if等一些较为简单的公式函数。一、excel与word软件的超衔接在出具审计报告时，若需修改word版财务会计报告附注，每位审计工作者一定十分头疼。手工修改既繁琐又容易岀错。不但要花费大量时间，还增加了校対的工作量。那么，是否能够在 excel审定数据确定后，就自动生成word版的财务会计报告附注呢？笔者认为，通过运用excel 的自动运算功能來避免手工计算的错谋，同时，通过excel与word软件z间建立数据衔接引用， 可大幅度地简化财务会计报告附注的修改过程，提高审计的工作效率。其实，口 microsoft office 2002版开始，已增加了 exce

3、l与word软件的数据衔接功能。当在word报告附注中粘贴excel 数据表格时，其右下脚会出现选择性粘贴菜单按钮，只需选中“保留源格式并衔接到excel”即可。 如图1所示。运用该方法制作的表格，当被选中时，背景色呈灰色。若单击鼠标右键，列示的菜单条中会 增加“更新衔接''的功能。通过该“更新衔接''功能，就能实现excel与word的数据更新衔接，如 图2所示。系统的默认衔接状态是“口动衔接''到excel,当word文件中衔接至excel的表格较多时，通 常打开该文件速度会较慢。上市公司的财务会计报告附注表单信息量往往较大，这一点就显得尤

4、为明显。所以，笔者建议使用“手动衔接”设置（单击鼠标右键，弹出如图2的菜单条，选中“衔 接的工作表 对象j“衔接”），弹出“衔接”菜单界面，如图3所示。我们在“所选衔接的更新方式"屮将默认的“自动更新"变更为“手动更新”方式。这样word文 档与excel文件并非吋刻保持数据更新，不必占用“宝贵”的内存，可提高文档的操作运行速度。熟练掌握该方法后，除了财务会计报告附注外，尽职调查、资产评佔等业务，凡是在word 文档中需要摘抄excel数据的工作都将有其用武之地。目前，实务中出具审计报告的大致工作 流程是：编制审计底稿一介并报表一编写审计报告。报告附注小有大量数据，通过采用

5、上述衔接 方法后可大幅度地缩短工作时间。同时，word文档与excel文件进行衔接的准备工作，可不必 安排在审计完成阶段进行，完全可以提而进行准备。通过对现有工作流程的再造，将原先摘抄数 据的工作时间前置，不仅提高了审计工作效率，还为紧张的年审工作争取了宝贵的时间。ywmv 八» ahmoif .* wmmmii w 71bbbi<1 :«，* <u4 1j4.hluksi824w» 、 i pma"*4kblulm*v*1 魂f>.k u< 用""瓷”人费二、共享工作簿对审定单体报表进行合并工作，是每位审计项

6、目负责人都十分熟悉的。当母公司的下属子公 司较多时，合并工作往往需要多位审计员一起分工配合完成。实务中，通常按照各人所分配的丁 作分头进行，当某人需要修改部分内容时，往往需要更新所冇人手屮的excel文件。当分工的人 员较多时，项h负责人需要对每位审计员手屮的文档更新工作进行时刻监控，否则就容易造成不 同的更新内容存储在不同的文件中，经多次修改后容易出现混乱的现象，最后甚至项h负责人都 难以区分哪份文档系“最终稿s为防止更新内容混乱，解决上述问题的办法通常是采用串联式的工序分配方法。但是，审计 工作通常存在一定的时间限制耍求，所以，实务中人家往往只能采用并联式的工序分配方法，即 “分头进行、同

7、时开工那么，是否有避免并联式作业产生混乱情况的办法？笔者向人家介绍excel软件中的“共享工作簿"功能。由丁并联式作业系“分头进行、同时开 工”，我们可让合并审计组成员连接在一个局域网中(当然，随着电脑配置的不断提高，无限网 卡也已成为大多数电脑的基本配置，构建一个无线局域网已不再是难事)。山项目负责人打开一 个excel合并报表附注文件,然后单击菜单栏中的“审阅”洪享工作簿.”，并在弹出的“共享工 作簿"菜单界面中，选中“允许多用户同时编辑，同时允许工作簿合并''单选框。这时，局域网 内的莫他成员就可以同时编辑该合并文件了。由于，所有的更新内容系保存在一个

8、相同的excel 文件中，电脑将累计保存局域网中每位审计员对该文件的信息修改内容，以保证该文件永远是“最 终稿"。当不同的审计员对同一单元格内容进行修改时，该单元格右上方将出现最近次修改者名 字和修改时间，以提示审计员对需重复修改信息的确认，以保证修改内容的“最终性三、excel软件的公式函数如果您浏览过excel的函数菜单，是否曾惊愕于其庞大的函数功能？其实，我们只需学握其 中部分函数的运用方法，就足以满足日常'审计工作中的大多数需求。除了 sum、average. if 等常用函数外，笔者向人家介绍儿个较实用的函数，掌握后可达到事半功倍的效果。(一)value 函数1.

9、用途。value函数的用途是将代表数字的文本字符串转换成数字。实务中,我们常遇到从某些财务 软件引出的财务数据信息系文本型字符串，如：orical软件。虽然其导出后的数据表示的系数值 信息，但由丁是文本型字符串，所以无法直接进行运算，给审计工作带來了诸多不便。通过使川 value函数，可以将该文本型字符串转换成数值型。2. 函数语法 value (text)。其中：text为带引号的文本或对需要进行文本转换的单元格的引用。比如需要将a1单元格 中的文木型字符串转换成数值型，则公式“=v alue(al)”即可。值得介绍的是，笔者发现在excel中存在个小bugo当我们选中文本型字符串单元格后，

10、 如果按ctrl+f,查找替换为“”的话，即可将原先带小数点的文本型字符串转换为数值型 字符串，大大地简化操作步骤。但是，当文本型字符串所代表的数值信息系整数时(即无小数 点时)，该方法则不适用。此外，运用菜单栏“数据分列”的功能也可以达到该效果。(二)截取字符串函数right( ),left( ),mid()我们从erp里导出数据之后，数据录入员所录入的数据不一定和我们所要的-模-样，但其屮 可能包含了我们所要的信息，这样，我们就需要把其小的信息提取出來。我们可以用截取字符串 函数來帮助我们完成工作。语法：左截収字符串函数：lcfl(text, number )右截取字符吊函数：right(

11、text, number)屮间截取字符串函数：mid(text, start num, number )说明：text是指函数操作的对彖，也就是包含所要提取字符的文木number是要捉取字符的数量start num是指开始提取字符的起始位置但在实际操作中，常将right ()两数或left ()函数与len ()函数结合起來使用，达到快速提 収我们需要的信息的忖的。在表4中，我们假定a列中両而的是分公司代码，后而是采购单号。 我们现在要把所有的采购单号取hi來分析，可以这样处理：abcdef1al p00512112=right(al ,len(a 1 )-3)2al p00512001=ri

12、ght(a2,len(a2)-3)3bl p0200411010a=right(a3jen(a3)-3)4bl p0200512121=right(a4,len(a4)-3)5cl p0200503141c=right(a5,lcn(a5)-3)(三)vlookup 和 concatenate 函数1. vlookup 函数。用途：搜索表区域内首列满足条件的数值，确定待检索单元格在区域中的行序号，再进一 步返回选定单元格的值。语i去：vlookup(lookup_value?table_aitay,col_index_num,range_lookup)说明：lookup value：指需要在t

13、able array区域屮第一列査找的值；table_array：指需要在其中查找数据的表格；col index num：指在table array区域中对应匹配值所返冋的值所在的列数；range_lookup：这是一个逻辑值(urc或false)，如果填ture是近似匹配，而false则是精确匹配。这个函数的主要用途是将存放在另外一张表格的信息相对应地提取到一张表格上。我们举个简单的例了(见表5),把“物料信息表”屮的的物料名称和单位相应地取到“物料进仓明细表”屮。abcde1材料信息表2物料编号物料名称单位3n001材料a张4n002材料b公斤5n003材料c公斤67物料进仓明细表8物料编

14、号物料名称单位进仓时间进仓数量9n001=vlookup(a9,$a$2: $c$5,2,false)=vlookup(a9,sa$2:$c$5,3,false)2005-9-3120. 0010n002=vlook up(a 10,$as2:$c$5,2,false)=vlookup(al 0,$a$2:$c$5,3,fhlse)2005-10-1412. 00hn001=vlookup(a 1 l,$a$2:$c$5,2,false)=vlookup(a 11 ,$a$2:$c$5,3,false)2005-10-2124. 0012n003=vlook up(a 12,$as2:$c$5

15、,2,false)=vlookup(a 12,$a$2:$c$5,3,fa】se)2005-11-1225. 0013n002=vlookup(a 13,$as2:$c$5,2,false)=vlookup(al3,$a$2:$c$5,3,false)2005-12-219. 50小提示:在公式屮引用其他单元格吋，可以百接将光标移动到冃标单元格或用光标选取引用范围, 再输入分格符“，”即可。另外，要改变单元格的引用方式，在输入完单元格按f4。2. concatenate 函数。用途：将多个文本字符串合并成一个。上述笔者已介绍了 vlookup函数的运用，但在实务中不同的工作表之间并非均存在唯一

16、 性的关键字符串(如上例为“客户”)。那么，我们就需要将不同单元格内的信息进行合并，使 其生成唯一的一个字符串。例如:在编制服装企业存货账龄分析表时，获取的明细清单内各件 衣服的类别、款式、颜色、尺寸均不具有唯一性特点，可见表5。为了使川vlookup函数，我们需耍口己构建一个唯一性的字符串。在本例中，我们可先 在首列中插入一列，标题可称作为“品名"，然后使用concatenate两数，构建唯一性的字符 串。concatenate函数运用如f:函数语法：concatenate(text 1 ,text2,.)。作用：将几个文本字符串合并为一个文本字符串。其中,textl, text2

17、,.为1到30个将要 合并成单个文本项的文本项。这些文本项可以是文本字符串、数字或对单个单元格的引用。本例 单元格a2的公式函数为“=concatenate(b2,“/”,c2,”/”,d2,“/”,e2)”。其中，护是为了以后筛 选查找方便，不用也可。语法：sumif(rangc, criteria, sum range)说明：range:为川丁条件判断的范围；criteria：用于判断的标准;sum range :实际求和的范围。我们在运用该公式求和时要注意,range和sum range是对应的关系，如果他们的对应关系错了，求出的结果也不一定正确。我们还是以表5中的“物料进仓明细表”为例

18、子，用sumif o分类汇总物料出仓数量，见表6abcde1材料信息表2物料编号物料名称单位进仓总数3n001材料a张=sumif($a$8:$a$13,a3,$e$&$e$13)4n002材料b公斤=sumif($as8:$a$ 13,a4,$e$8:se$ 13)5n003材料c公斤=sumif($a$&$a$ 13,a5,$e$ 8:se$ 13)67物料进仓明细表8物料编号物料名称单位进仓时间进仓数量9n001材料a张2005-9-3120. 0010n002材料b公斤2005-10-1412. 0011n001材料a张2005-10-2124. 0012n003材料c

19、公斤2005-11-1225. 0013n002材料b公斤2005-12-219. 50(五)去除空格键函数-trim()我们在导iii erp数据库中的数据时，由terp数据库中规定了字符的长度，所以在导出数 据时，会造成有些字符厉面带有空格键字符，影响我们数据统计的准确件。为此，我们需要拿握 一个可以除去文木以外空格键字符的函数。语法：trim(tcxt)说明：trim()函数可把文木前后两边的空格键去掉(注；不能去掉文本中间的空格键)。函数的 使用方法和函数value () 一样。(六)绝对引用和相鲨引用在使用excel函数时，我们常要引用某个单元格的数据。这时，我们就需要了解绝对引用

20、和相对引用的区别和作川。定义：相对引用,随着引用单元格的位置变化,被引用单元格盲卓.也是在变化的是相对引用；绝对引用($),随着引出单元格住注的变化，被引用单元格住弐不变化的就是绝对引用 ($)。区别：相对引用和绝对引用的区別在于当迦迤務被复制到其他地方时，被引用单兀格輪 位置变与不变的区別。例子：如下表(表1)所示，在单元格“a2”中存放着美元汇率信息，那么我们可将表1中的美 元价格转换为人民币价格，即：对于材料a,我们可以将单元格“c6”与单-元格“a2”相乘得 出材料a的人民币价格。我们在“d6”屮绝对引用单元格“a2”，相对引用单元格“c6”。在“d6” 屮输入“=c6*$a$2”，然

21、后将单元格“d6”复制到剩余两个需要求人民币价格的单元格上，就 可以很方便地求出结果了。表7abcde1美元汇率28.127345材料币别价格人民币价格采购数量6材料ausd12二 c6*$a$212007材料busd15二 c7*$a$213008材料cusd11=c8*$a$213209材料ausd12=c9*$a$21100四、excel软件的“随机数发生器”实务中，审计员通常会选择那些金额较大，或者发生频繁的交易作为抽样样本，并习惯以这 些特定项口的偏差或错报来推断总体。按照2006年度颁布的中国注册会计师审计准则第1314 号审计抽样和其他选取测试项目的方法的和关规定，对特定项目实施

22、审计程序的结果不能 推断至整个总体，选取特定项h并不构成审计抽样。审计抽样包括统计抽样和非统计抽样。若使 用统计抽样，则必须通过随机选取样木。那么，如何才能做到随机抽样呢？ cpa审计教材中就曾 提到过“随机数表”；“四大”会计师事务所也曾专门开发随机数发生器软件，用于审计程序的随机 抽样。那么，我们是否能够利用excel软件来制作一张“随机数表”，并使该表能够满足审计三级 复核的要求呢？笔者给大家介绍一下excel软件中的“随机数发生器”的运用。虽 4 vlookup je tt trrt 眾abd1户2007-12-312006-12-312纵三i2 34s no352 00rose3123 123004 2-af t5 00mike4干五4 257 132.0075 7s3 00peter&赵六12 314.001 235.00lllva 构班嗔一放半讣用abcd1e1尺寸2女装/休闲红，中号女装中勺假设有一份存货产成品明细清单，样本规模为1 000项，我们已为每项产成品按其顺用赋予 了 1至1 000的序号。现我们需随机抽取其中20个样木量并执行审计程序。我们通过选择菜单 栏“工具,数据分析,，（如果您没冇找到“数据分析”选择项，门j能您的exce