EXCEL函数在审计中应用15页word资料.docx
《EXCEL函数在审计中应用15页word资料.docx》由会员分享，可在线阅读，更多相关《EXCEL函数在审计中应用15页word资料.docx（13页珍藏版）》请在冰豆网上搜索。
EXCEL函数在审计中应用15页word资料
作为一名审计工作者，谈到EXCEL软件可能大家都十分熟悉，因为它是我们审计工作中得力的好帮手，其使用率也已远远的超过了WORD文字处理软件。
随着审计工作电算化程度的不断提高，无纸化的工作底稿必将成为未来的发展趋势。
但是，仅就目前大家在日常工作中使用EXCEL软件的功能来说，还仍局限在加减乘除的简单运算功能，常使用的函数无非是SUM、AVERAGE、IF等一些较简单的函数。
笔者在本文中将给同志们介绍一些大家可能不太熟悉，但是，一旦掌握后将会大幅度提高我们审计工作效率的EXCEL功能。
同时，笔者也期望能够抛砖引玉，大家能一起来发掘和交流自己的一些使用经验或小窍门，以求在保证审计质量的前提下，更好地提高本所审计工作效率。
一、EXCEL中宏的运用
谈到宏，大家既熟悉又陌生。
熟悉，是因为在打开某些EXCEL文档时，电脑会出现安全警告，并提示我们对该宏的安全性进行选择确认。
由于大多数朋友对于宏并不了解，以及网络上“宏病毒”的负面影响，所以，往往一谈到宏，大家联想到的往往是“病毒”。
其实，非也。
Excel软件中的宏是指：
“如果经常在MicrosoftExcel中重复某项任务，那么可以用宏自动执行该任务。
宏是一系列命令和函数，存储于VisualBasic模块中，并且在需要执行该项任务时可随时运行。
”可见宏其实是多项操作任务的集合命令，与DOS中的BAT批处理命令类似。
由于它是对多项操作任务的集合，所以，在我们的日常工作中有着广泛的实用性，比如：
选择性粘贴、报表区域打印等等。
这些多步骤的操作，看似简单，但在你不知不觉中，日积月累地花费了我们很多时间。
俗话说：
“时间就是生命”，在紧张的年审工作时就显得更加突出。
时间是宝贵的，所以，我们要让它使在“刀刃”上。
其实，宏的操作并不复杂。
EXCEL设计者也已经考虑到大多的使用者对于VB命令可能并不熟悉，所以，已在菜单栏中设计了录制宏的功能（在菜单栏“工具”－>“宏”－>“录制新宏”）。
它就好比一部摄像机，当你在打某套“组合拳”时，电脑会在后台自动记录并翻译成其识别的VB程序。
当宏录制完毕后，你可以给其取一个便于识别的名字。
然后，通过添加控件按钮，实现宏命令与控件之间的衔接。
（具体操作步骤由于篇幅限制，大家可以登陆本所内部培训网站下载课件“excel在实务中的运用”，有详细的操作演示录像）。
二、EXCEL数据与WORD的超衔接
在出具上市公司年审报告时，频繁地修改word版会计报表附注，是让每位审计工作者最头疼的一件事了。
手工修改，既繁琐又容易出错，不但花费了我们大量时间，也增加了校对老师的工作量。
我曾梦想，EXCEL能否在合并哈达表和合并附注数据确定后，就自动生成WORD版的会计报表附注呢？
EXCEL强劲的自动运算功能能使我们摆脱人为计算的错误，同时，由于数据的超衔接引用，可大幅度地简化报告附注的修改步骤，加快我们的工作效率。
现在，梦想已成为现实。
MicrosoftOffice软件从其2019版开始，已增加了EXCEL与word软件的超衔接功能。
当我们在WORD报告附注中粘贴EXCEL数据表格时，其右下脚会出现选择性粘贴菜单按钮，我们只要选中“保留源格式并衔接到EXCEL”即可。
见图1：
通过该方法制作的表格，当被选中时背景色呈灰色。
单击鼠标右键时，列示的菜单条中会增加“更新衔接”的功能，通过该“更新衔接”功能，我们就能实现WORD与EXCEL数据的刷新衔接。
如图2：
系统的默认衔接状态是“自动衔接”到Excel，这往往使打开该WORD文件速度较慢，当上市公司报告附注表格较多时就尤为明显。
所以，笔者建议使用“手动衔接”设置（单击鼠标右键，弹出如图2的菜单条，选中“衔接的工作表对象”－>“衔接…”），弹出“衔接”菜单界面，如图3所示：
我们在“所选衔接的更新方式”中将“自动更新”选择为“手动更新”方式，这样WORD文档就并非时时与EXCEL文件同步数据刷新，可不必占用我们“宝贵”的内存，提高了文档的操作速度。
当我们熟练掌握上述功能后，除了年审报告附注之外，尽职调查、资产评估等业务，凡是在word文档中需要摘抄EXCEL数据的工作都将有其用武之地。
我们出具年审报告的常规工作流程：
编制底稿>合并报表>出具报告附注。
报告附注中的大量数据，通过上述衔接方法更新后可大幅度地缩短年审工作时间。
同时，将WORD文档与EXCEL文件的超衔接准备工作也可以在我们需要的任何时候进行（甚至可以在淡季）。
那么，通过审计工作流程的再造，将大幅度提高我们年审时的工作效率和准确率。
三、共享工作簿
合并报表，是每位项目负责人十分熟悉的工作。
当母公司的下属公司较多时，合并工作往往需要几位审计员一起分工配合完成。
实务操作中，由于系按照各人所分配的工作分头进行，当某人需要修改部分内容时，往往需要更新所有人手中的EXCEL文件。
当分工人数较多时，需要项目负责人对每位审计员手中的更新文档进行时时监控，否则就容易发生不同的更新内容存储在不同的文件中，经反复修改汇总后出现混乱的情况，最后甚至连项目负责人都难以区分哪份文档系“最终稿”。
为防止更新内容混乱，解决上述问题的最好办法是按串连式的工序分配方式。
但是，由于年审工作时间的限制，所以，实务中大家往往只能采用并连式的工序分配方法，即“分头进行、同时开工”。
那么，是否有避免并连式作业产生混乱情况的好办法呢？
我给大家介绍EXCEL软件中的“共享工作簿”功能。
由于并连式作业，系“分头进行、同时开工”，我们可让合并小组成员连接在一个局域网中（当然，随着电脑配置的不断提高，无限网卡也已成为大多数电脑的基本配置，构建一个无线局域网已不再是难事）。
由项目负责人打开一个Excel合并报表附注文件，然后单击菜单栏中的“工具”>“共享工作簿…”，并在弹出的“共享工作簿…”菜单界面中，选中“允许多用户同时编辑，同时允许工作簿合并”单选框。
这时，局域网内的其他成员就可以同时编辑该合并文件了。
由于，所有的更新内容系保存在一个相同的EXCEL文件中，电脑将累计保存局域网中每位操作者对该文件的修改信息，以保证该文件永远系“最终稿”。
当不同审计员，对同一单元格内的内容修改时，该单元格右上方将出现最近次修改者名字和修改时间，已提示审计员对所需再修改内容确认，以保证修改内容的“最终性”。
四、EXCEL中的审计实用函数
“2－8规律”就如同“黄金分割”一样，具有“数字魔力”。
做股票，大多时候是2个赚钱8个亏钱的，EXCEL的功能也是如此，就曾有人统计过，只要您学会了20％的方法，就可以实现80％的功能。
如果您浏览过EXCEL函数菜单，是否曾被其惊人的数量而吓倒呢？
其实，我们只需掌握其中部分函数的使用方法，就可以满足审计工作中的大多数需求。
除了SUM、AVERAGE、IF等常用函数外，让我再给您介绍几个较实用的函数：
1.VALUE函数
功能：
将代表数字的文本字符串转换成数字。
实务中，我们常碰到某些财务软件导出的财务数据系文本型字符串，如：
ORICAL软件。
虽然，导出后的数据表示的系数字信息，但由于是文本型字符串而无法进行算术运算，所以，给我们的审计工作带来了诸多不便。
通过使用VALUE函数，可以将该文本型字符串转换成数字型，其函数公式为：
公式：
=VALUE（TEXT）
其中：
TEXT，表示需要转换的文本型单元格位置，比如我们需要将A1单元格中的文本型字符串转换成数值型，则公式＝VALUE（A1）即可。
（注：
EXCEL中存在个小BUG，当我们选中文本型字符串范围后，如果按CTRL＋F，查找“.”替换为“.”的话，可将原先带小数点的文本型字符串全部转换为数值型字符串，可以大大地简化操作步骤。
但是，当文本型字符串所代表的数字信息系整数时（即不存在小数点时），该方法则不适用。
）
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网络终端购光端机:
8M120,000[]608,547.013[]29[]转[]54[]1[]产品制造:
光端机100,000[]497,094.025[]15[]转[]17[]1[]产品制造票到:
8M光端机
4,000,000[]1,091,299.15-
上表系某公司的部分存货明细账簿，我们发现财务已将数量记录在摘要栏内，但是，由于每笔交易的数量位数不同，所以我们无法直接用RIGHT函数提取摘要栏内的数量信息。
假设，我们需要提取第一笔摘要栏“网络终端购光端机:
8M120,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函数
功能：
搜索表区域首列满足条件的元素，确定待检索单元格在区域中的行序号，再进一步返回选定单元格的值。
实务中，该函数的运用率很高，我们经常碰到所需的不同信息被存储在不同的工作表中。
比如，按照先进先出法编制应收账款账龄明细表时，某客户2019年度往来增减变动额在某个工作表内，其04年往来变动额在另外一个工作表内，如果需要测算5年账龄，则需要收集5个工作表。
由于该客户在不同工作表内的行次并不相同，我们为查找该客户在不同年度内的增减变动信息，需要人工搜索其在不同工作表内的行号位置。
当公司客户明细较多时，查找的工作量十分巨大。
为此，我给大家介绍一下VLOOKUP函数，它可便捷地完成该审计程序。
（表一）
AB1客户2019—12—312张三12，345.003李四123,123.004王五4,257,132.005赵六12,314.00（表二）
ABC1客户2019—12—31销售业务员2赵六1,235.00LILY3王五75,758.00PETER4张三53,252.00ROSE5李四4,241,565.00MIKE显见，上述“表一”含有各客户2019年度末应收款余额数据，而“表二”含有客户2019年度末余额数据和销售业务员的信息。
但是，由于客户所处行次并不一致，所以人工粘贴的方法并不可行（客户数量较多时，尤为明显）。
VLOOKUP函数运用如下：
公式＝（Lookup_value，Table_array，Col_index_num，Range_lookup）
其中：
Lookup_value：
需要在数据表首列进行搜索的值。
本例中，系客户名称。
Table_array：
需要搜索数据的信息表。
本例中，系“表二”中含有2019年末余额数据和销售员信息。
Col_index_num：
满足条件的单元格在数组区域Table_array中的列序号。
本例中，由于“表一”中C2单元格需要返回“表二”中2019年度余额数据，故系“表二”中的第2列信息，故填写“2”。
同理，“表一”中D2单元格需要返回“表二”中销售员信息，故系“表二”中的第3列信息，故填写“3”。
Range_lookup：
在查找时，是否需要精确匹配。
如果为FALSE,则大致匹配，如果为TRUE或忽略，则精确匹配（并区分全/半角）。
故实务中，一般选择大致匹配，即“FALSE”。
具体操作步骤如下：
首先，在“表一”工作表的C1和D1单元格内分别粘贴“2019—12—31”和“销售业务员”字符串，作为行标题。
然后，在“表一”C2单元格内使用VLOOKUP函数，具体公式如下：
=VLOOKUP（$A2,表二!
$A$1:
$C$5,2,FALSE）
在D2单元格内使用函数公式如下：
=VLOOKUP（$A2,表二!
$A$1:
$C$5,3,FALSE）
公式内的具体解释已在上面详细描述，其中使用“$”符号，是为了进行绝对引用和相对引用单元格信息，以便于鼠标往下拖拉，使电脑自动生成C列和D列内的其他行次单元格信息。
在“表一”的基础上经略作整理后，即可得到如“表三”所示的结果：
（表三）
ABCD1客户2019—12—312019—12—31销售业务员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中尚未安装统计功能模块，请选择“工具”－>“加载宏…”，并安装“分析工具库”即可）。
在“数据分析”菜单界面中，选择“随机发生器”，如图4：
选中“随机发生器”项目后，单击确定按钮后，将出现如图5界面：
变量个数：
表示在指定输出表中数值列的个数。
即，我们需要的随机数的组数，审计实务中往往仅需要1组即可。
随机数个数：
在此输入要查看的数据点个数。
即在1000个样本范围中，你需要抽取的样本个数，假设我们拟抽取20个作为随机样本。
分布：
在下拉菜单中选择用于创建随机数的分布方法。
如：
均匀、正态、柏努利、二项式、泊松、模式、离散。
在审计实务操作中，往往只需要在数据清单中随机产生序列号码即可，所以可选择“均匀分布”。
均匀分布，系以下限和上限来表征。
其变量是通过对区域中的所有数值进行等概率抽取而得到的。
参数：
在此输入用于表征选定分布的数值。
范例中由于存在1000个样本序号规模，所以输入1至1000即可。
随机数基数：
在此输入用来构造随机数的可选数值，可在以后重新使用该数值来生成相同的随机数。
即审计员可以随意设置随机数基数，目的使审计复核老师可以使用审计员在底稿中提供的“随机数基数”产生相同的随机数组值，以验证审计员所提供的抽样随机数的真实性。
输出区域、新工作表组、新工作簿：
按照审计员要求，可将产生的随机数组列示在相应的EXCEL单元格中。
经上述操作步骤后，可得到下列随机数组，见图6：
您也可以按照上述操作步骤，验证一下是否可以得到相同的“随机数组”。
然后，通过本文已介绍的VLOOKUP函数，审计员将能很快地制作出一份完整的“随机抽样清单”。
古人云：
“工欲善其事，必先利其器”。
在既定的审计程序目标和有限的审计工作时间内，如果审计员想不断地提高工作效率，就需要我们不断地去完善和尝试新的工作方式。
EXCEL软件，它能够带给我们的功能是十分强大的。
我相信就本文所介绍的内容还仅仅只是其“冰山一角”，我真诚地期盼能够有更多的同事一道来探讨和分享EXCEL软件所带给我们的无穷“乐趣”。
升级会员 