EXCEL函数在审计中应用.doc

作为一名审计工作者，谈到EXCEL软件可能大家都十分熟悉，因为它是我们审计工作中得力的好帮手，其使用率也已远远的超过了WORD文字处理软件。随着审计工作电算化程度的不断提高，无纸化的工作底稿必将成为未来的发展趋势。但是，仅就目前大家在日常工作中使用EXCEL软件的功能来说，还仍局限在加减乘除的简单运算功能，常使用的函数无非是SUM、AVERAGE、IF等一些较简单的函数。笔者在本文中将给同志们介绍一些大家可能不太熟悉，但是，一旦掌握后将会大幅度提高我们审计工作效率的EXCEL功能。同时，笔者也期望能够抛砖引玉，大家能一起来发掘和交流自己的一些使用经验或小窍门，以求在保证审计质量的前提下，更好地提高本所审计工作效率。一、EXCEL中宏的运用谈到宏，大家既熟悉又陌生。熟悉，是因为在打开某些EXCEL文档时，电脑会出现安全警告，并提示我们对该宏的安全性进行选择确认。由于大多数朋友对于宏并不了解，以及网络上“宏病毒”的负面影响，所以，往往一谈到宏，大家联想到的往往是“病毒”。其实，非也。Excel软件中的宏是指：“如果经常在Microsoft Excel中重复某项任务，那么可以用宏自动执行该任务。宏是一系列命令和函数，存储于Visual Basic模块中，并且在需要执行该项任务时可随时运行。”可见宏其实是多项操作任务的集合命令，与DOS中的BAT批处理命令类似。由于它是对多项操作任务的集合，所以，在我们的日常工作中有着广泛的实用性，比如：选择性粘贴、报表区域打印等等。这些多步骤的操作，看似简单，但在你不知不觉中，日积月累地花费了我们很多时间。俗话说：“时间就是生命”，在紧张的年审工作时就显得更加突出。时间是宝贵的，所以，我们要让它使在“刀刃”上。其实，宏的操作并不复杂。EXCEL设计者也已经考虑到大多的使用者对于VB命令可能并不熟悉，所以，已在菜单栏中设计了录制宏的功能（在菜单栏“工具”“宏”“录制新宏”）。它就好比一部摄像机，当你在打某套“组合拳”时，电脑会在后台自动记录并翻译成其识别的VB程序。当宏录制完毕后，你可以给其取一个便于识别的名字。然后，通过添加控件按钮，实现宏命令与控件之间的衔接。（具体操作步骤由于篇幅限制，大家可以登陆本所内部培训网站下载课件“excel在实务中的运用”，有详细的操作演示录像）。二、EXCEL数据与WORD的超衔接在出具上市公司年审报告时，频繁地修改word版会计报表附注，是让每位审计工作者最头疼的一件事了。手工修改，既繁琐又容易出错，不但花费了我们大量时间，也增加了校对老师的工作量。我曾梦想，EXCEL能否在合并哈达表和合并附注数据确定后，就自动生成WORD版的会计报表附注呢？EXCEL强劲的自动运算功能能使我们摆脱人为计算的错误，同时，由于数据的超衔接引用，可大幅度地简化报告附注的修改步骤，加快我们的工作效率。现在，梦想已成为现实。Microsoft Office软件从其2002版开始，已增加了EXCEL与word软件的超衔接功能。当我们在WORD报告附注中粘贴EXCEL数据表格时，其右下脚会出现选择性粘贴菜单按钮，我们只要选中“保留源格式并衔接到EXCEL”即可。见图1：通过该方法制作的表格，当被选中时背景色呈灰色。单击鼠标右键时，列示的菜单条中会增加“更新衔接”的功能，通过该“更新衔接”功能，我们就能实现WORD与EXCEL数据的刷新衔接。如图2：系统的默认衔接状态是“自动衔接”到Excel，这往往使打开该WORD文件速度较慢，当上市公司报告附注表格较多时就尤为明显。所以，笔者建议使用“手动衔接”设置（单击鼠标右键，弹出如图2的菜单条，选中“衔接的工作表对象”“衔接”），弹出“衔接”菜单界面，如图3所示：我们在“所选衔接的更新方式”中将“自动更新”选择为“手动更新”方式，这样WORD文档就并非时时与EXCEL文件同步数据刷新，可不必占用我们“宝贵”的内存，提高了文档的操作速度。当我们熟练掌握上述功能后，除了年审报告附注之外，尽职调查、资产评估等业务，凡是在word文档中需要摘抄EXCEL数据的工作都将有其用武之地。我们出具年审报告的常规工作流程：编制底稿合并报表出具报告附注。报告附注中的大量数据，通过上述衔接方法更新后可大幅度地缩短年审工作时间。同时，将WORD文档与EXCEL文件的超衔接准备工作也可以在我们需要的任何时候进行（甚至可以在淡季）。那么，通过审计工作流程的再造，将大幅度提高我们年审时的工作效率和准确率。三、共享工作簿合并报表，是每位项目负责人十分熟悉的工作。当母公司的下属公司较多时，合并工作往往需要几位审计员一起分工配合完成。实务操作中，由于系按照各人所分配的工作分头进行，当某人需要修改部分内容时，往往需要更新所有人手中的EXCEL文件。当分工人数较多时，需要项目负责人对每位审计员手中的更新文档进行时时监控，否则就容易发生不同的更新内容存储在不同的文件中，经反复修改汇总后出现混乱的情况，最后甚至连项目负责人都难以区分哪份文档系“最终稿”。为防止更新内容混乱，解决上述问题的最好办法是按串连式的工序分配方式。但是，由于年审工作时间的限制，所以，实务中大家往往只能采用并连式的工序分配方法，即“分头进行、同时开工”。那么，是否有避免并连式作业产生混乱情况的好办法呢？我给大家介绍EXCEL软件中的“共享工作簿”功能。由于并连式作业，系“分头进行、同时开工”，我们可让合并小组成员连接在一个局域网中（当然，随着电脑配置的不断提高，无限网卡也已成为大多数电脑的基本配置，构建一个无线局域网已不再是难事）。由项目负责人打开一个Excel合并报表附注文件，然后单击菜单栏中的“工具”“共享工作簿”，并在弹出的“共享工作簿”菜单界面中，选中“允许多用户同时编辑，同时允许工作簿合并”单选框。这时，局域网内的其他成员就可以同时编辑该合并文件了。由于，所有的更新内容系保存在一个相同的EXCEL文件中，电脑将累计保存局域网中每位操作者对该文件的修改信息，以保证该文件永远系“最终稿”。当不同审计员，对同一单元格内的内容修改时，该单元格右上方将出现最近次修改者名字和修改时间，已提示审计员对所需再修改内容确认，以保证修改内容的“最终性”。四、EXCEL中的审计实用函数“28规律”就如同“黄金分割”一样，具有“数字魔力”。做股票，大多时候是2个赚钱8个亏钱的，EXCEL的功能也是如此，就曾有人统计过，只要您学会了20的方法，就可以实现80的功能。如果您浏览过EXCEL函数菜单，是否曾被其惊人的数量而吓倒呢？其实，我们只需掌握其中部分函数的使用方法，就可以满足审计工作中的大多数需求。除了SUM、AVERAGE、IF等常用函数外，让我再给您介绍几个较实用的函数：1.VALUE函数功能：将代表数字的文本字符串转换成数字。实务中，我们常碰到某些财务软件导出的财务数据系文本型字符串，如：ORICAL软件。虽然，导出后的数据表示的系数字信息，但由于是文本型字符串而无法进行算术运算，所以，给我们的审计工作带来了诸多不便。通过使用VALUE函数，可以将该文本型字符串转换成数字型，其函数公式为：公式：=VALUE（TEXT）其中：TEXT，表示需要转换的文本型单元格位置，比如我们需要将A1单元格中的文本型字符串转换成数值型，则公式VALUE(A1)即可。（注：EXCEL中存在个小BUG，当我们选中文本型字符串范围后，如果按CTRLF，查找“.”替换为“.”的话，可将原先带小数点的文本型字符串全部转换为数值型字符串，可以大大地简化操作步骤。但是，当文本型字符串所代表的数字信息系整数时（即不存在小数点时），该方法则不适用。）2.LEFT、RIGHT、LEN和FIND函数功能：提取会计账簿摘要栏内填写的数量信息。实务中，很多企业的财务人员有在三栏式账簿摘要栏内填写存货数量的习惯。当审计员欲获取数量金额式的存货账簿时，企业往往提供类似的账簿，“可远观，而不可亵玩焉”，让人哭笑不得。在此，我介绍一套“组合拳”给大家，可以方便地提取类似会计账簿中的数量信息，生成数量金额式的电子账簿以方便存货审计员“大显身手”。具体介绍如下：LEFT:公式=(text，num_chars)RIGHT:公式=(text，num_chars)作用：从一个文本字符串的第一个字符开始返回指定个数的字符。其中，text表示要提取字符的字符串位置；num_chars表示，需要提取的字符数，忽略时为1。LEFT和RIGHT函数的公式一致，区别在于一个从左开始提取字符串，一个从右开始提取字符串。LEN:公式=(text)作用：返回文本字符串中字符个数。text表示要计算长度的文本字符串；包括空格。FIND:公式=(Find_text,Within_text,Start_num)作用：返回一个字符串在另一个字符串中出现的起始位置（区分大小写）。其中：“Find_text”表示要查找的字符串；“Within_text”表示要在其中进行搜索的字符串。“Start_num”表示起始搜索位置，在Within_text中第一个字符的位置为1，忽略时，Start_num=1。具体举例如下：月份日期类型凭证号流水号摘要借方金额贷方金额329转492网络终端购光端机:8M 120,000608,547.01 329转541产品制造:光端机100,000497,094.02515转171产品制造票到:8M光端机4,000,0001,091,299.15-上表系某公司的部分存货明细账簿，我们发现财务已将数量记录在摘要栏内，但是，由于每笔交易的数量位数不同，所以我们无法直接用RIGHT函数提取摘要栏内的数量信息。假设，我们需要提取第一笔摘要栏“网络终端购光端机:8M 120,000”中的数量信息“120,000”，并假设该字符串在EXCEL文档的F7单元格中。则具体组合函数如下：公式：=RIGHT(F7,LEN(F7)-FIND( ,F7,1)分解介绍如下：LEN(F7)测量该字符串长度，结果为19（包括空格）；FIND( ,F7,1)查找空格在该字符串中所处位置，结果为12；则RIGHT(F7，7)通过LEN和FIND函数组合运用，得出需要在F7单元格中从右返回7个字符，结果为“120,000”。当然，由于系在文本型字符串中引用字符，得到的结果也是文本型的数字信息，我们还需要运用上述介绍的VALUE函数将其转换成数值型字符串。同理，第二、三笔的交易均可由电脑自动测量需要返回的字符个数。在熟练掌握该些函数后，你还可以提取处于中间位置的数量信息。（提示：在上述公式基础上再加上LEFT函数即可）。3.VLOOKUP和CONCATENATE函数 VLOOKUP函数功能：搜索表区域首列满足条件的元素，确定待检索单元格在区域中的行序号，再进一步返回选定单元格的值。实务中，该函数的运用率很高，我们经常碰到所需的不同信息被存储在不同的工作表中。比如，按照先进先出法编制应收账款账龄明细表时，某客户2005年度往来增减变动额在某个工作表内，其04年往来变动额在另外一个工作表内，如果需要测算5年账龄，则需要收集5个工作表。由于该客户在不同工作表内的行次并不相同，我们为查找该客户在不同年度内的增减变动信息，需要人工搜索其在不同工作表内的行号位置。当公司客户明细较多时，查找的工作量十分巨大。为此，我给大家介绍一下VLOOKUP函数，它可便捷地完成该审计程序。(表一）AB1客户200512312张三12，345.003李四123,123.004王五4,257,132.005赵六12,314.00（表二）ABC1客户20041231销售业务员2赵六1,235.00LILY3王五75,758.00PETER4张三53,252.00ROSE5李四4,241,565.00MIKE显见，上述“表一”含有各客户2005年度末应收款余额数据，而“表二”含有客户2004年度末余额数据和销售业务员的信息。但是，由于客户所处行次并不一致，所以人工粘贴的方法并不可行（客户数量较多时，尤为明显）。VLOOKUP函数运用如下：公式(Lookup_value，Table_array，Col_index_num，Range_lookup)其中：Lookup_value：需要在数据表首列进行搜索的值。本例中，系客户名称。Table_array：需要搜索数据的信息表。本例中，系“表二”中含有2004年末余额数据和销售员信息。Col_index_num：满足条件的单元格在数组区域Table_array中的列序号。本例中，由于“表一”中C2单元格需要返回“表二”中2004年度余额数据，故系“表二”中的第2列信息，故填写“2”。同理，“表一”中D2单元格需要返回“表二”中销售员信息，故系“表二”中的第3列信息，故填写“3”。Range_lookup：在查找时，是否需要精确匹配。如果为FALSE,则大致匹配，如果为TRUE或忽略，则精确匹配（并区分全/半角）。故实务中，一般选择大致匹配，即“FALSE”。具体操作步骤如下：首先，在“表一”工作表的C1和D1单元格内分别粘贴“20041231”和“销售业务员”字符串，作为行标题。然后，在“表一”C2单元格内使用VLOOKUP函数，具体公式如下：=VLOOKUP($A2,表二!$A$1:$C$5,2,FALSE)在D2单元格内使用函数公式如下：=VLOOKUP($A2,表二!$A$1:$C$5,3,FALSE)公式内的具体解释已在上面详细描述，其中使用“$”符号，是为了进行绝对引用和相对引用单元格信息，以便于鼠标往下拖拉，使电脑自动生成C列和D列内的其他行次单元格信息。在“表一”的基础上经略作整理后，即可得到如“表三”所示的结果：（表三）ABCD1客户2005123120041231销售业务员2张三12,345.0053,252.00ROSE3李四123,123.004,241,565.00MIKE4王五4,257,132.0075,758.00PETER5赵六12,314.001,235.00LILY CONCATENATE函数功能：将多个文本字符串合并成一个。上述我已向大家介绍了VLOOKUP函数的运用，但是实务中，不同的工作簿之间并非时刻存在唯一的关键字符串（如上例为“客户名称”）。那么，我们就需要将不同单元格内的信息进行合并，使其生成唯一的一个字符串。例如：在编制服装企业存货账龄分析表时，由于获取的明细清单内各件衣服的类别、款式、颜色、尺寸均不具有唯一性特点，如下“表四”所示：为了使用VLOOKUP函数，我们需要自己构建一个唯一性的字符串。在本例中，我们可先在首列中插入一列，标题可称作为“品名”，然后使用CONCATENATE函数，创建唯一性的字符串，公式介绍如下：ABCDE1品名类别款式颜色尺寸2女装/休闲服/红/中号女装休闲服红中号公式：=CONCATENATE(text1,text2,text3,text4,text29,text30)该函数，共可合并30个不同单元格内的字符串，在本例中的运用如下：=CONCATENATE(B2,/,C2,/,D2,/,E2)（其中“/”，是为了便于检查的需要，不用也可）五、EXCEL中的“随机数发生器”实务中不知曾几何时，将独立审计准则要求的随机抽样变成了人为的“随意”抽样。按照统计学观点，人为主观因素由于会受到多方因素的干扰，而无法做到客观的随意抽样。实务中也确实如此，我们往往会刻意选择那些金额较大，或者发生频繁的凭证号码作为抽样样本。依据新的审计准则1314号审计抽样和其他选取测试项目的方法中规定：“第十七条根据对被审计单位的了解、评估的重大错报风险以及所测试总体的特征等，注册会计师可以确定从总体中选取特定项目进行测试。选取的特定项目可能包括：（一）大额或关键项目；（二）超过某一金额的全部项目；（三）被用于获取某些信息的项目；（四）被用于测试控制活动的项目。根据判断选取特定项目，容易产生非抽样风险。第十八条选取特定项目实施检查，通常是获取审计证据的有效手段，但并不构成审计抽样。对按照这种方法所选取的项目实施审计程序的结果，不能推断至整个总体。第十九条在对某类交易或账户余额使用审计抽样时，注册会计师可以使用统计抽样方法，也可以使用非统计抽样方法。统计抽样是指同时具备下列特征的抽样方法：（一）随机选取样本；（二）运用概率论评价样本结果，包括计量抽样风险。不同时具备上述两个特征的抽样方法为非统计抽样。”可见，审计准则要求我们审计人员按照统计方法实施“随机”抽样。目前我们通常实施的特定项目的所谓“随意”抽样结果，并不构成审计抽样，且不能被用于推断至整个总体。但是，如何真正地做到“随机”抽样呢？CPA审计教材中就曾提到过“随机数表”；目前的“四大”会计师事务所也分别出资开发了随机数发生器软件，用于审计程序“随机”抽样。那么，我们是否能够利用EXCEL来制作一张“随机数表”，并使该表能够满足复核人员的复核要求呢？让我来给大家介绍一下EXCEL软件中的“随机数发生器”的运用：假设我们目前有一份存货产成品明细清单，样本规模为1000项。同时，我们已对每项产成品按其顺序赋予了1至1000的序号。然后，我们选择菜单栏“工具”“数据分析”（如果您没有“数据分析”选择项，说明您的EXCEL中尚未安装统计功能模块，请选择“工具”“加载宏”，并安装“分析工具库”即