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

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

2、还局限在加减乘除的简单运算，常使用的也仅是SUM、AVERAGE、IF等一些较为简单的公式函数。一、Excel与Word软件的超衔接 在出具审计报告时，若需修改word版财务会计报告附注，每位审计工作者一定十分头疼。手工修改既繁琐又容易出错。不但要花费大量时间，还增加了校对的工作量。那么，是否能够在Excel审定数据确定后，就自动生成Word版的财务会计报告附注呢？笔者认为，通过运用Excel的自动运算功能来避免手工计算的错误，同时，通过Excel与Word软件之间建立数据衔接引用，可大幅度地简化财务会计报告附注的修改过程，提高审计的工作效率。其实，自 Microsoft Office 200

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

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

5、xcel文件进行衔接的准备工作，可不必安排在审计完成阶段进行，完全可以提前进行准备。通过对现有工作流程的再造，将原先摘抄数据的工作时间前置，不仅提高了审计工作效率，还为紧张的年审工作争取了宝贵的时间。 二、共享工作簿 对审定单体报表进行合并工作，是每位审计项目负责人都十分熟悉的。当母公司的下属子公司较多时，合并工作往往需要多位审计员一起分工配合完成。实务中，通常按照各人所分配的工作分头进行，当某人需要修改部分内容时，往往需要更新所有人手中的Excel文件。当分工的人员较多时，项目负责人需要对每位审计员手中的文档更新工作进行时刻监控，否则就容易造成不同的更新内容存储在不同的文件中，经多次修改后容

6、易出现混乱的现象，最后甚至项目负责人都难以区分哪份文档系“最终稿”。 为防止更新内容混乱，解决上述问题的办法通常是采用串联式的工序分配方法。但是，审计工作通常存在一定的时间限制要求，所以，实务中大家往往只能采用并联式的工序分配方法，即“分头进行、同时开工”。那么，是否有避免并联式作业产生混乱情况的办法？ 笔者向大家介绍Excel软件中的“共享工作簿”功能。由于并联式作业系“分头进行、同时开工”，我们可让合并审计组成员连接在一个局域网中（当然，随着电脑配置的不断提高，无限网卡也已成为大多数电脑的基本配置，构建一个无线局域网已不再是难事）。由项目负责人打开一个Excel合并报表附注文件，然后单击菜

7、单栏中的“审阅”“共享工作簿”，并在弹出的“共享工作簿”菜单界面中，选中“允许多用户同时编辑，同时允许工作簿合并”单选框。这时，局域网内的其他成员就可以同时编辑该合并文件了。由于，所有的更新内容系保存在一个相同的Excel文件中，电脑将累计保存局域网中每位审计员对该文件的信息修改内容，以保证该文件永远是“最终稿”。当不同的审计员对同一单元格内容进行修改时，该单元格右上方将出现最近次修改者名字和修改时间，以提示审计员对需重复修改信息的确认，以保证修改内容的“最终性”。 三、Excel软件的公式函数 如果您浏览过Excel的函数菜单，是否曾惊愕于其庞大的函数功能？其实，我们只需掌握其中部分函数的运

8、用方法，就足以满足日常审计工作中的大多数需求。除了SUM、AVERAGE、IF等常用函数外，笔者向大家介绍几个较实用的函数，掌握后可达到事半功倍的效果。 （一）VALUE函数 1. 用途。 VALUE函数的用途是将代表数字的文本字符串转换成数字。实务中，我们常遇到从某些财务软件引出的财务数据信息系文本型字符串，如：Orical软件。虽然其导出后的数据表示的系数值信息，但由于是文本型字符串，所以无法直接进行运算，给审计工作带来了诸多不便。通过使用VALUE函数，可以将该文本型字符串转换成数值型。 2. 函数语法VALUE（text）。 其中：text为带引号的文本或对需要进行文本转换的单元格的引

9、用。比如需要将A1单元格中的文本型字符串转换成数值型，则公式“VALUE(A1)”即可。 值得介绍的是，笔者发现在Excel中存在个小BUG。当我们选中文本型字符串单元格后，如果按CTRLF，查找“. ”替换为“. ”的话，即可将原先带小数点的文本型字符串转换为数值型字符串，大大地简化操作步骤。但是，当文本型字符串所代表的数值信息系整数时（即无小数点时），该方法则不适用。此外，运用菜单栏“数据”“分列”的功能也可以达到该效果。 （二）截取字符串函数-right( ),left( ),mid( )我们从ERP里导出数据之后，数据录入员所录入的数据不一定和我们所要的一模一样，但其中可能包含了我们所

10、要的信息，这样，我们就需要把其中的信息提取出来。我们可以用截取字符串函数来帮助我们完成工作。语法：左截取字符串函数：left(text, number )右截取字符串函数：right(text, number )中间截取字符串函数：mid(text, start_num, number )说明：Text是指函数操作的对象，也就是包含所要提取字符的文本Number是要提取字符的数量Start_num 是指开始提取字符的起始位置但在实际操作中，常将right（）函数或left（）函数与len（）函数结合起来使用，达到快速提取我们需要的信息的目的。在表4中，我们假定A列中前面的是分公司代码，后面是采

11、购单号。我们现在要把所有的采购单号取出来分析，可以这样处理：表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)（三）VLOOKUP和CONCATENATE函数 1. VLOOKUP 函数。 用途：搜索表区域内首列满足条件的数值，确定待检索单元格在区域中的行序号，再进一步返回选定单元格

12、的值。 语法：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），把“物料信息表”中

13、的的物料名称和单位相应地取到“物料进仓明细表”中。表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,fal

14、se)=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。2CONCATE

15、NATE 函数。 用途：将多个文本字符串合并成一个。 上述笔者已介绍了VLOOKUP函数的运用，但在实务中不同的工作表之间并非均存在唯一性的关键字符串（如上例为“客户”）。那么，我们就需要将不同单元格内的信息进行合并，使其生成唯一的一个字符串。例如：在编制服装企业存货账龄分析表时，获取的明细清单内各件衣服的类别、款式、颜色、尺寸均不具有唯一性特点，可见表5。 为了使用VLOOKUP函数，我们需要自己构建一个唯一性的字符串。在本例中，我们可先在首列中插入一列，标题可称作为“品名”，然后使用CONCATENATE函数，构建唯一性的字符串。CONCATENATE函数运用如下： 函数语法：CONCAT

16、ENATE(text1,text2,)。 作用：将几个文本字符串合并为一个文本字符串。其中，text1, text2, . 为 1 到 30 个将要合并成单个文本项的文本项。这些文本项可以是文本字符串、数字或对单个单元格的引用。本例单元格A2的公式函数为“ =CONCATENATE(B2,/,C2,/,D2,/,E2) ”。其中，“/”是为了以后筛选查找方便，不用也可。 (四)sumif( )语法：SUMIF(range, criteria, sum_range)说明：range：为用于条件判断的范围；criteria：用于判断的标准；sum_range：实际求和的范围。我们在运用该公式求和时

17、要注意，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-3120001

18、0N002材料B公斤2005-10-14120011N001材料A张2005-10-21240012N003材料C公斤2005-11-12250013N002材料B公斤2005-12-21950（五）去除空格键函数-trim( )我们在导出ERP数据库中的数据时，由于ERP数据库中规定了字符的长度，所以在导出数据时，会造成有些字符后面带有空格键字符，影响我们数据统计的准确性。为此，我们需要掌握一个可以除去文本以外空格键字符的函数。语法：trim(text)说明：trim( )函数可把文本前后两边的空格键去掉（注：不能去掉文本中间的空格键）。函数的使用方法和函数value（）一样。（六）绝对引用

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

20、引用单元格“A2”，相对引用单元格“C6”。在“D6”中输入“=C6*$A$2”，然后将单元格“D6”复制到剩余两个需要求人民币价格的单元格上，就可以很方便地求出结果了。表7ABCDE1美元汇率28.127345材料币别价格人民币价格采购数量6材料AUSD12=C6*$A$212007材料BUSD15=C7*$A$213008材料CUSD11=C8*$A$213209材料AUSD12=C9*$A$21100四、Excel软件的“随机数发生器” 实务中，审计员通常会选择那些金额较大，或者发生频繁的交易作为抽样样本，并习惯以这些特定项目的偏差或错报来推断总体。按照2006年度颁布的中国注册会计师审

21、计准则第1314号审计抽样和其他选取测试项目的方法的相关规定，对特定项目实施审计程序的结果不能推断至整个总体，选取特定项目并不构成审计抽样。审计抽样包括统计抽样和非统计抽样。若使用统计抽样，则必须通过随机选取样本。那么，如何才能做到随机抽样呢？CPA审计教材中就曾提到过“随机数表”；“四大”会计师事务所也曾专门开发随机数发生器软件，用于审计程序的随机抽样。那么，我们是否能够利用Excel软件来制作一张“随机数表”，并使该表能够满足审计三级复核的要求呢？笔者给大家介绍一下Excel软件中的“随机数发生器”的运用。 假设有一份存货产成品明细清单，样本规模为1 000项，我们已为每项产成品按其顺序赋

22、予了1至1 000的序号。现我们需随机抽取其中20个样本量并执行审计程序。我们通过选择菜单栏“工具”“数据分析”（如果您没有找到“数据分析”选择项，可能您的Excel中尚未安装该功能模块。请选择“工具”“加载宏”，并安装“分析工具库”即可）。在“数据分析”菜单界面中，选择“随机数发生器”，如图4所示。 选中“随机数发生器”项目后，单击确定按钮后，将出现如7图5的界面。 变量个数：表示在指定输出表中数值列的个数。即，我们需要的随机数的组数，审计实务中一般需要1组即可。 随机数个数：在此输入要查看的数据点个数。即在1 000个样本规模中，需要抽取的样本个数，本例我们需抽取20个随机样本。 分布：在

23、下拉菜单中选择用于创建随机数的分布方法。如：均匀、正态、柏努利、二项式、泊松、模式、离散。在实务中，我们一般只需要在样本清单中产生随机序列号码即可，所以通常选择“均匀分布”。均匀分布，系以下限和上限来表征。其变量是通过对区域中的所有数值进行等概率抽取而得到的。 参数：在此输入用于表征选定分布的数值。因本例的样本规模为1 000个，所以输入1至1000即可。 随机数基数：在此输入用来构造随机数的可选数值，可在以后重新使用该数值来生成相同的随机数。通过随机数基数，审计底稿内核老师可据此产生相同的随机数，以复核审计员所抽取样本正确与否。此外，因随机数基数不同，将产生不同的随机数。审计人员可能会以此来逃避对某些抽样样本的审计工作，而选择其他较为方便的样本实施审计程序。为预防该情况的发生，随机数基数一般应由项目负责人确定。 输出区域、新工作表组、新工作簿：按照审计员要求，可将产生的随机数组列示在相应的EXCEL单元格中。 通过上述操作步骤，Excel会在审计员制订的输出区域内生成相应的随机数组。经适当整理后，如图6所示。 然后，审计员可结合运用本文介绍的VLOOKUP函数，快捷地制作出一份存货产成品随机抽样清单。