24周年

财税实务 高薪就业 学历教育
APP下载
APP下载新用户扫码下载
立享专属优惠
安卓版本:8.6.82 苹果版本:8.6.82
开发者:北京东大正保科技有限公司
应用涉及权限:查看权限>
APP隐私政策:查看政策>

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

来源: 张宇 编辑: 2009/03/03 09:15:46  字体:

  【摘要】 本文以审计实务为背景,通过介绍Excel与Word软件的衔接、共享工作簿、公式函数和随机数发生器的运用,以实现提高审计工作效率、解决实际困难的目的,达到事半功倍的效果,对于目前的审计实务工作具有一定的参考应用价值。

  【关键词】 Excel软件;审计实务运用;公式函数;随机数发生器

  谈到Excel软件,大家可能都十分熟悉,因为它是审计工作的好帮手,其使用频率远远超过了其他办公类软件。随着审计工作电算化程度的不断提高,无纸化的办公模式必将成为未来的发展趋势。但仅就目前而言,我们在日常审计工作中经常使用的Excel软件功能通常还局限在加减乘除的简单运算,常使用的也仅是SUM、AVERAGE、IF等一些较为简单的公式函数。笔者将在本文中介绍一些平时使用率相对较少,而一旦掌握后将大幅提高审计工作效率的Excel功能。

  一、Excel与Word软件的超衔接

  在出具审计报告时,若需修改word版财务会计报告附注,每位审计工作者一定十分头疼。手工修改既繁琐又容易出错。不但要花费大量时间,还增加了校对的工作量。那么,是否能够在Excel审定数据确定后,就自动生成Word版的财务会计报告附注呢?笔者认为,通过运用Excel的自动运算功能来避免手工计算的错误,同时,通过Excel与Word软件之间建立数据衔接引用,可大幅度地简化财务会计报告附注的修改过程,提高审计的工作效率。其实,自 Microsoft Office 2002版开始,已增加了Excel与Word软件的数据衔接功能。当在Word报告附注中粘贴Excel数据表格时,其右下脚会出现选择性粘贴菜单按钮,只需选中“保留源格式并衔接到Excel”即可。如图1所示。

  运用该方法制作的表格,当被选中时,背景色呈灰色。若单击鼠标右键,列示的菜单条中会增加“更新衔接”的功能。通过该“更新衔接”功能,就能实现Excel与Word的数据更新衔接,如图2所示。

  系统的默认衔接状态是“自动衔接”到Excel,当Word文件中衔接至Excel的表格较多时,通常打开该文件速度会较慢。上市公司的财务会计报告附注表单信息量往往较大,这一点就显得尤为明显。所以,笔者建议使用“手动衔接”设置(单击鼠标右键,弹出如图2的菜单条,选中“衔接的 工作表 对象”→“衔接…”),弹出“衔接”菜单界面,如图3所示。

  我们在“所选衔接的更新方式”中将默认的“自动更新”变更为“手动更新”方式。这样Word文档与Excel文件并非时刻保持数据更新,不必占用 “宝贵”的内存,可提高文档的操作运行速度。

  熟练掌握该方法后,除了财务会计报告附注外,尽职调查、资产评估等业务,凡是在Word文档中需要摘抄EXCEL数据的工作都将有其用武之地。目前,实务中出具审计报告的大致工作流程是:编制审计底稿→合并报表→编写审计报告。报告附注中有大量数据,通过采用上述衔接方法后可大幅度地缩短工作时间。同时,Word文档与Excel文件进行衔接的准备工作,可不必安排在审计完成阶段进行,完全可以提前进行准备。通过对现有工作流程的再造,将原先摘抄数据的工作时间前置,不仅提高了审计工作效率,还为紧张的年审工作争取了宝贵的时间。

  二、共享工作簿

  对审定单体报表进行合并工作,是每位审计项目负责人都十分熟悉的。当母公司的下属子公司较多时,合并工作往往需要多位审计员一起分工配合完成。实务中,通常按照各人所分配的工作分头进行,当某人需要修改部分内容时,往往需要更新所有人手中的Excel文件。当分工的人员较多时,项目负责人需要对每位审计员手中的文档更新工作进行时刻监控,否则就容易造成不同的更新内容存储在不同的文件中,经多次修改后容易出现混乱的现象,最后甚至项目负责人都难以区分哪份文档系“最终稿”。

  为防止更新内容混乱,解决上述问题的办法通常是采用串联式的工序分配方法。但是,审计工作通常存在一定的时间限制要求,所以,实务中大家往往只能采用并联式的工序分配方法,即“分头进行、同时开工”。那么,是否有避免并联式作业产生混乱情况的办法?

  笔者向大家介绍Excel软件中的“共享工作簿”功能。由于并联式作业系“分头进行、同时开工”,我们可让合并审计组成员连接在一个局域网中(当然,随着电脑配置的不断提高,无限网卡也已成为大多数电脑的基本配置,构建一个无线局域网已不再是难事)。由项目负责人打开一个Excel合并报表附注文件,然后单击菜单栏中的“工具”→“共享工作簿…”,并在弹出的“共享工作簿…”菜单界面中,选中“允许多用户同时编辑,同时允许工作簿合并”单选框。这时,局域网内的其他成员就可以同时编辑该合并文件了。由于,所有的更新内容系保存在一个相同的Excel文件中,电脑将累计保存局域网中每位审计员对该文件的信息修改内容,以保证该文件永远是“最终稿”。当不同的审计员对同一单元格内容进行修改时,该单元格右上方将出现最近次修改者名字和修改时间,以提示审计员对需重复修改信息的确认,以保证修改内容的“最终性”。

  三、Excel软件的公式函数

  如果您浏览过Excel的函数菜单,是否曾惊愕于其庞大的函数功能?其实,我们只需掌握其中部分函数的运用方法,就足以满足日常审计工作中的大多数需求。除了SUM、AVERAGE、IF等常用函数外,笔者向大家介绍几个较实用的函数,掌握后可达到事半功倍的效果。

  (一)VALUE函数

  1. 用途。

  VALUE函数的用途是将代表数字的文本字符串转换成数字。实务中,我们常遇到从某些财务软件引出的财务数据信息系文本型字符串,如:Orical软件。虽然其导出后的数据表示的系数值信息,但由于是文本型字符串,所以无法直接进行运算,给审计工作带来了诸多不便。通过使用VALUE函数,可以将该文本型字符串转换成数值型。

  2. 函数语法VALUE(text)。

  其中:text为带引号的文本或对需要进行文本转换的单元格的引用。比如需要将A1单元格中的文本型字符串转换成数值型,则公式“=VALUE(A1)”即可。

  值得介绍的是,笔者发现在Excel中存在个小BUG。当我们选中文本型字符串单元格后,如果按CTRL+F,查找“. ”替换为“. ”的话,即可将原先带小数点的文本型字符串转换为数值型字符串,大大地简化操作步骤。但是,当文本型字符串所代表的数值信息系整数时(即无小数点时),该方法则不适用。此外,运用菜单栏“数据”→“分列”的功能也可以达到该效果。

  (二)LEFT、RIGHT、LEN和FIND函数

  LEFT、RIGHT、LEN和FIND函数在这里的用途是提取会计账簿摘要栏内填写的数量信息。在审计实务中,常会遇见财务人员在三栏式账簿摘要栏内填写存货数量的情况。当审计员拟取得数量金额式的存货账簿时,客户却提供这样的“数量金额”式账簿,“可远观,而不可亵玩焉”,让人哭笑不得。在此,笔者介绍一套“组合拳”给大家,可方便地提取类似账簿中的数量信息,生成数量金额式的电子账簿,使审计员可大显身手。具体运用如下。

  1. 函数语法LEFT(text,num_chars)。

  作用:基于所指定的字符数返回文本字符串中的第一个或前几个字符。

  2. 函数语法RIGHT(text,num_chars)。

  作用:根据所指定的字符数返回文本字符串中最后一个或多个字符。其中,text表示要提取字符的字符串位置;num_chars表示,需要提取的字符数,忽略时为1。LEFT和RIGHT函数的运用基本一致,区别在于一个从左开始提取字符串,一个从右开始提取字符串。

  3. 函数语法LEN(text)。

  作用:返回文本字符串中字符个数。其中,text表示要查找其长度的文本,空格将作为字符进行计数。

  4. 函数语法FIND(find_text,within_text,start_num)。

  作用:查找其他文本字符串 (within_text) 内的文本字符串 (find_text),并从 within_text 的首字符开始返回 find_text 的起始位置编号。其中,“find_text”表示要查找的字符串;“within_text”表示要在其中进行搜索的字符串。“start_num”表示起始搜索位置,在within_text中第一个字符的位置为1,忽略时,start_num=1。

  下面用示例说明操作。表1系某公司的部分存货明细账,财务已将数量记录在摘要栏内。

  但是,由于每笔交易数量的位数不同,所以我们无法直接用RIGHT函数提取摘要栏内的数量信息。假设我们需要提取第一笔摘要栏“网络终端购光端机:8M 120 000 ”中的数量信息“120 000”,则复合函数公式为“ =RIGHT(F2,LEN(F2)-FIND(" ",F2,1)) ”。拆分各公式后具体介绍如下:

  LEN(F2):测量该字符串长度,结果为19(包括空格);

  FIND(" ",F2,1):查找空格在该字符串中所处位置,结果为12;

  RIGHT(F2,7):通过LEN和FIND函数组合运用,得出需要在F7单元格中从右返回7个字符(即19-12=7),结果为“120 000”。

  因为我们系在文本型字符串中引用字符,所以得到的结果也是文本型的数值信息。我们可通过运用上述介绍的VALUE函数将其转换成数值型字符串。同理,第二、三笔的交易均可通过编写的复合函数由电脑自动完成。熟练掌握后,同理还可以提取处于中间位置的数量信息(提示:在上述公式基础上再嵌套LEFT函数即可)。

  (三)VLOOKUP和CONCATENATE函数

  1. VLOOKUP 函数

  用途:搜索表区域内首列满足条件的数值,确定待检索单元格在区域中的行序号,再进一步返回选定单元格的值。

  实务中,该函数的运用率很高,我们经常碰到所需的信息被存储在不同的工作表中。比如,按照先进先出法编制应收账款账龄明细表时,客户2007年度的往来增减变动明细在某个工作表内,而2006年度的往来增减变动明细却在另外一个工作表内。如果需要分析5年账龄,则需要收集5个工作表。由于各客户在各工作表内所处的行次并不完全相同,为查找该客户在不同年度内的增减变动信息,需要审计员在不同的工作表内人工查找行号位置。尤其当客户明细较多时,查找工作量将非常巨大,并不十分经济。为此,通过运用VLOOKUP函数便可快捷地实现该目标。有关客户资料见表2、表3。

  表2为“2007年客户明细余额”工作表,其中包含公司2007年度末各客户应收账款余额数据。而表3为“2006年客户明细余额”工作表,其中包含该些客户的2006年末余额数据和相应的销售业务员信息。但是,由于客户在两个工作表中所处行次并不相同,所以人工查找粘贴的方法并不经济(客户数量较多时,尤为明显)。具体运用如下。

  (1)函数语法VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

  作用:在表格或数值数组的首列查找指定的数值,并由此返回表格或数组当前行中指定列处的数值。其中:

  lookup_value: 需要在数据表首列进行搜索的值。本例中,系A列客户的名称。

  table_array:需要搜索数据的信息表。本例中为“2006年客户明细余额”工作表中A2至C5单元格范围。

  col_index_num:满足条件的单元格在数组区域table_array中的列序号。在本例中,需在“2007年客户明细余额”工作表C列中返回“2006年客户明细余额”工作表中的B列2006年末余额信息,故属表3中的第2列信息,应填写“2”。同理,表“2007年客户明细余额”工作表中D列需返回“2006年客户明细余额”工作表C列中的销售员信息,故属表3中的第3列信息,应填写“3”。

  range_lookup:在查找时,是否需要精确匹配。如果为FALSE,则大致匹配,如果为TRUE或忽略,则精确匹配(并区分全/半角)。在实务中,一般选择大致匹配,即“FALSE”。

  (2)具体操作程序。

  首先,在“2007年客户明细余额”工作表的C1和D1单元格内分别粘贴“2006-12-31”和“销售业务员”字符串,作为行标题。然后,在“2007年客户明细余额”工作表C2单元格内使用VLOOKUP函数,公式为“=VLOOKUP($A2,2006年客户明细余额!$A$2:$C$5,2,FALSE)”,D2单元格的函数公式为“=VLOOKUP($A2,2006年客户明细余额!$A$2:$C$5,3,FALSE)”。其中“$”符号是为了绝对引用和相对引用单元格信息,以便通过鼠标的单元格拖放功能,让电脑自动生成C列和D列内其他行次的单元格信息。在表2和表3的基础上,经上述操作并略作整理,便可得到表4所示结果。

  2.CONCATENATE 函数。

  用途:将多个文本字符串合并成一个。

  上述笔者已介绍了VLOOKUP函数的运用,但在实务中不同的工作表之间并非均存在唯一性的关键字符串(如上例为“客户”)。那么,我们就需要将不同单元格内的信息进行合并,使其生成唯一的一个字符串。例如:在编制服装企业存货账龄分析表时,获取的明细清单内各件衣服的类别、款式、颜色、尺寸均不具有唯一性特点,可见表5。

  为了使用VLOOKUP函数,我们需要自己构建一个唯一性的字符串。在本例中,我们可先在首列中插入一列,标题可称作为“品名”,然后使用CONCATENATE函数,构建唯一性的字符串。CONCATENATE函数运用如下:

  函数语法:CONCATENATE(text1,text2,…)。

  作用:将几个文本字符串合并为一个文本字符串。其中,text1, text2, ... 为 1 到 30 个将要合并成单个文本项的文本项。这些文本项可以是文本字符串、数字或对单个单元格的引用。本例单元格A2的公式函数为“ =CONCATENATE(B2,"/",C2,"/",D2,"/",E2) ”。其中,“/”是为了以后筛选查找方便,不用也可。

  四、Excel软件的“随机数发生器”

  实务中,审计员通常会选择那些金额较大,或者发生频繁的交易作为抽样样本,并习惯以这些特定项目的偏差或错报来推断总体。按照2006年度颁布的《中国注册会计师审计准则第1314号——审计抽样和其他选取测试项目的方法》的相关规定,对特定项目实施审计程序的结果不能推断至整个总体,选取特定项目并不构成审计抽样。审计抽样包括统计抽样和非统计抽样。若使用统计抽样,则必须通过随机选取样本。那么,如何才能做到随机抽样呢?CPA审计教材中就曾提到过“随机数表”;“四大”会计师事务所也曾专门开发随机数发生器软件,用于审计程序的随机抽样。那么,我们是否能够利用Excel软件来制作一张“随机数表”,并使该表能够满足审计三级复核的要求呢?笔者给大家介绍一下Excel软件中的“随机数发生器”的运用

  假设有一份存货产成品明细清单,样本规模为1 000项,我们已为每项产成品按其顺序赋予了1至1 000的序号。现我们需随机抽取其中20个样本量并执行审计程序。我们通过选择菜单栏“工具”→“数据分析”(如果您没有找到“数据分析”选择项,可能您的Excel中尚未安装该功能模块。请选择“工具”→“加载宏…”,并安装“分析工具库”即可)。在“数据分析”菜单界面中,选择“随机数发生器”,如图4所示。

 

  选中“随机数发生器”项目后,单击确定按钮后,将出现如7图5的界面。

  变量个数:表示在指定输出表中数值列的个数。即,我们需要的随机数的组数,审计实务中一般需要1组即可。

  随机数个数:在此输入要查看的数据点个数。即在1 000个样本规模中,需要抽取的样本个数,本例我们需抽取20个随机样本。

  分布:在下拉菜单中选择用于创建随机数的分布方法。如:均匀、正态、柏努利、二项式、泊松、模式、离散。在实务中,我们一般只需要在样本清单中产生随机序列号码即可,所以通常选择“均匀分布”。均匀分布,系以下限和上限来表征。其变量是通过对区域中的所有数值进行等概率抽取而得到的。

  参数:在此输入用于表征选定分布的数值。因本例的样本规模为1 000个,所以输入1至1000即可。

  随机数基数:在此输入用来构造随机数的可选数值,可在以后重新使用该数值来生成相同的随机数。通过随机数基数,审计底稿内核老师可据此产生相同的随机数,以复核审计员所抽取样本正确与否。此外,因随机数基数不同,将产生不同的随机数。审计人员可能会以此来逃避对某些抽样样本的审计工作,而选择其他较为方便的样本实施审计程序。为预防该情况的发生,随机数基数一般应由项目负责人确定。

  输出区域、新工作表组、新工作簿:按照审计员要求,可将产生的随机数组列示在相应的EXCEL单元格中。

  通过上述操作步骤,Excel会在审计员制订的输出区域内生成相应的随机数组。经适当整理后,如图6所示。

  然后,审计员可结合运用本文介绍的VLOOKUP函数,快捷地制作出一份存货产成品随机抽样清单。

  古人云:“工欲善其事,必先利其器”。在既定的审计程序目标和有限的审计工作时间内,若想不断地提高工作效率,就需要我们不断地去完善和探索新的工作方式。Excel软件具备强大的表格处理功能,笔者相信,本文所介绍的内容还仅仅是其“冰山一角”,随着我们对Excel软件运用的不断深入,其必将使审计工作事半功倍,增添无穷的乐趣。

责任编辑:冠

实务学习指南

回到顶部
折叠
网站地图

Copyright © 2000 - www.chinaacc.com All Rights Reserved. 北京东大正保科技有限公司 版权所有

京ICP证030467号 京ICP证030467号-1 出版物经营许可证 京公网安备 11010802023314号

正保会计网校