审计工作中常用EXCLE函数

审计中常用函数公式在审计工作中常用函数及实用技巧，此文件对于初学者很有帮助。常用函数公式有VALUE、LEFT、RIGHT、LEN和FIND、MID、SUMIF、VLOOKUP、CONCATENATE（类似&）、IF、ROUND、TRIM、SUBTOTAL、连字符“&”CONCATENATE在实际运用EXCEL进行审计工作的时候，我们为了能在两个数据库之间找一个合适的比较标准，有时需要将两个或以上的单元格连接起来。这时，我们可以用字符“&”将两个或以上的单元格连接起来。例子：我们想统计一下美元采购价格为12的材料A的采购数量。这时，我们可以将单元格“A6”与单元格“C6”连接起来再分类汇总即可(如下表2)。表2ABCDEF1美元汇率28.127345材料币别价格人民币价格采购数量6材料AUSD12.00=C6*$A$21,200.00=A6&"---"&C67材料BUSD15.00=C7*$A$21,300.00=A7&"---"&C78材料CUSD11.00=C8*$A$21,320.00=A8&"---"&C89材料AUSD12.00=C9*$A$21,100.00=A9&"---"&C9注意：用连字符“&”计算出的结果是文本型字符，也就是文本格式，不能用来加、减、乘、除等数学运算。如果文本型字符是数字，那么我们可以用函数value()将其转换为数值型字符，然后才能进行数学运算。（函数value()的用法见下面）2、CONCATENATE函数功能：将多个文本字符串合并成一个。实务中，不同的工作簿之间并非时刻存在唯一的关键字符串（如上例为“客户名称”）。那么，我们就需要将不同单元格内的信息进行合并，使其生成唯一的一个字符串。例如：在编制服装企业存货账龄分析表时，由于获取的明细清单内各件衣服的类别、款式、颜色、尺寸均不具有唯一性特点，如下“表四”所示：为了使用VLOOKUP函数，我们需要自己构建一个唯一性的字符串。在本例中，我们可先在首列中插入一列，标题可称作为“品名”，然后使用CONCATENATE函数，创建唯一性的字符串，公式介绍如下：ABCDE1品名类别款式颜色尺寸2女装/休闲服/红/中号女装休闲服红中号公式：=CONCATENATE(text1,text2,text3,text4,…,text29,text30)该函数，共可合并30个不同单元格内的字符串，在本例中的运用如下：=CONCATENATE(B2,"/",C2,"/",D2,"/",E2)（其中“/”，是为了便于检查的需要，不用也可）注：常用语连接时间日期。3、value()在审计实务中，我们常碰到某些财务软件导出的财务数据是文本型字符串，无法进行计算，如果遇到这种情况，使用VALUE函数，可以将该文本型字符串转换成数字型语法：=value(text)功能：将代表数字的文本字符串转换成数字说明：TEXT，表示需要转换的文本型单元格位置，比如我们需要将A1单元格中的文本型字符串转换成数值型，则公式＝VALUE(A1)即可。

（注：EXCEL中存在个小BUG，当我们选中文本型字符串范围后，如果按CTRL＋F，查找“.”替换为“.”的话，可将原先带小数点的文本型字符串全部转换为数值型字符串，可以大大地简化操作步骤。但是，当文本型字符串所代表的数字信息系整数时（即不存在小数点时），该方法则不适用。）注：原数据乘1或选中单元格，点击叹号“转换为数值”去除空格键函数-trim()我们在导出ERP数据库中的数据时，由于ERP数据库中规定了字符的长度，所以在导出数据时，会造成有些字符后面带有空格键字符，影响我们数据统计的准确性。为此，我们需要掌握一个可以除去文本以外空格键字符的函数。语法：trim(text)说明：trim()函数可把文本前后两边的空格键去掉（注：不能去掉文本中间的空格键）。函数的使用方法和函数value（）一样。取字符串或数值长度函数-len()我们介绍这个函数是为了配合下面截取字符串函数的使用而特别提出的。104MAX105MIN106PRODUCT107STDEV108STDEVP109SUM110VAR111VARP注意：101到111仅在Office2003、2007及更新的版本中能用。处理隐藏数据时使用。9、VLOOKUP()语法：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），把“物料信息表”中的物料名称和单位相应地取到“物料进仓明细表”中。表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-3120．0010N002=vlookup(a10,$a$2:$c$5,2,false)=vlookup(a10,$a$2:$c$5,3,false)2005-10-1412．0011N001=vlookup(a11,$a$2:$c$5,2,false)=vlookup(a11,$a$2:$c$5,3,false)2005-10-2124．0012N003=vlookup(a12,$a$2:$c$5,2,false)=vlookup(a12,$a$2:$c$5,3,false)2005-11-1225．0013N002=vlookup(a13,$a$2:$c$5,2,false)=vlookup(a13,$a$2:$c$5,3,false)2005-12-219．50小提示：在公式中引用其他单元格时，可以直接将光标移动到目标单元格或用光标选取引用范围，再输入分格符“，”即可。另外，要改变单元格的引用方式，在输入完单元格按F4。table_array区域可以定义成名称,使用名称来表达.10、sumif()语法：SUMIF(range,criteria,sum_range)说明：range：为用于条件判断的范围；criteria：用于判断的标准；sum_range：实际求和的范围。我们在运用该公式求和时要注意，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-3120．0010N002材料B公斤2005-10-1412．0011N001材料A张2005-10-2124．0012N003材料C公斤2005-11-1225．0013N002材料B公斤2005-12-219．50注:常用于往来双挂账。11、其他的一些函数我们在实际运用EXCEL审计的过程中，还常常用到month(),year()、now()等函数。这些函数简单实用，常常和其他函数组合起来使用。宏所谓宏，就是用VBA（VisualBaseApplication）语言编写的一段程序。如果我们在审计的过程中，能够用运用宏来辅助审计工作，那将会大大地提高我们的工作效率。VBA语言是VB语言的一个分支，如果我们有一种数据库计算机语言作为基础，那么学好VBA语言并不难。注：不常用，太繁琐。使用技巧绝对引用和相引用在使用EXCEL函数时，我们常要引用某个单元格的数据。这时，我们就需要了解绝对引用和相对引用的区别和作用。定义：相对引用，随着引用单元格的位置变化，被引用单元格位置也是在变化的是相对引用；绝对引用（$），随着引用单元格位置的变化，被引用单元格位置不变化的就是绝对引用（$）。区别：相对引用和绝对引用的区别在于当引用单元格被复制到其他地方时，被引用单元格的位置变与不变的区别。例子：如下表（表1）所示，在单元格“A2”中存放着美元汇率信息，那么我们可将表1中的美元价格转换为人民币价格，即：对于材料A，我们可以将单元格“C6”与单元格“A2”相乘得出材料A的人民币价格。我们在“D6”中绝对引用单元格“A2”，相对引用单元格“C6”。在“D6”中输入“=C6*$A$2”，然后将单元格“D6”表1ABCDE1美元汇率28.127345材料币别价格人民币价格采购数量6材料AUSD12=C6*$A$212007材料BUSD15=C7*$A$213008材料CUSD11=C8*$A$213209材料AUSD12=C9*$A$21100Excel在审计往来账款账龄时的运用往来账款(应收账款、其他应收款、应付账款等)的账龄审计，是财务会计报表的基础审计工作，企业往来款项的明细账户动辄成百上千，账龄审计颇为繁琐。而利用Excel进行往来账款账龄审计却非常简单方便。利用Excel进行账龄审计，需要企业提供往来款项、分客户明细账户编制的“年初数、本年累计借方发生额、本年累计贷方发生额、年末余额”格式的会计数据资料。目前越来越多的企业采用会计软件进行核算，该资料可以利用会计软件提供的“数据导出”功能导出为Excel格式获得。将获取资料置于一个Excel工作簿内。运用Excel的数据排序功能将年末余额为负数的数据(需要报表重分类)剔除后，就可以在A、B、C、D单元格中设定函数公式。一是账龄1年以内的函数为：A＝IF(本年贷方发生额＞＝年初余额，年末余额，本年借方发生额)。二是接着利用Excel的数据排序功能，筛选出(年末余额一账龄1年以内金额)＞0的明细账户，获取其上年“年初数、本年累计借方发生额、本年累计贷方发生额、年末余额”格式的会计数据资料。在上表“单位名称”列左侧增加列，用以添加上年数据。账龄1～2年的函数为：B＝IF(本年贷方发生额十上年贷方发生额)＞：上年度年初余额，年末余额一账龄1年以内的金额A，上年度借方发生额)。三是筛选出(年末余额一账龄)1年以内金额一账龄1～2年的金额)＞0的明细账户，获取其更前一个年度的数据。账龄2～3年的函数为：C=IF((本年贷方发生额＋上年贷方发生额＋更上一年的贷方发生额)＞＝更上一个年度年初余额，年末余额—账龄1年以内的金额A-账龄1-2年的金额B，更上一个年度的借方发生额)。四是账龄3年以上的函数为：n：年末余额一账龄1年以内的金额A——账龄1-2年的金额B——账龄2--3年的金额C。输入完毕后，模板将自动计算年末账龄，方便准确。注：审计年度新增往来单位可能穿插在内，与上一年度在顺序上不匹配，就会需要到SUMIF。所以此方法更为繁琐，更简便的方法为：用鼎信诺取连续三年的数据，系统自动生成账龄。用时目测1小时6个往来。Excel在审计固定资产折旧时的运用Excel为注册会计师提供了年数总和法(SYD)、双倍余额递减法(DDB)、直线法(SLN)等多种计算折旧方法。笔者以年数总和法为例予以介绍。例如，甲公司的一项固定资产原值为200000元，预计残值为20000元，使用年限为5年，采用年数总和法计算折旧。为审计该项折旧计提是否正确，可用以下方法：打开Excel，点击工具条上的“FX”函数按钮，弹出“粘贴函数”对话框，其中分左右两栏：左栏为函数分类，右栏为函数名。在函数分类栏“常用函数”对应的“函数名”中选定SYD，然后单击“确定”按钮，就可弹出这就对话框，其中有四项参数：cost,salvage,life,per。在cost中输入成本200000，在salvage中输入残值20000，在life中输入使用年限5，在per中输入1，就可求得第一年的折旧额为60000元。依次把per中的数值换为2，3，4，5，就可求出以后各年的折旧额，非常方便准确。(SLN\DDB)同理。注：此为年折旧。Excel在审计相互引用数据的运用由于会计报表各科目之间的勾稽关系，一个科目金额的变动常常会引起其他相关科目的连锁变动。这种情况常常导致修改审计工作底稿，一方面增加了工作量，另一方面也使工作底稿容易变得凌乱不堪，费时费力。Execl在这方面也发挥了不小的作用。例如，甲公司某年度营业收入为100000元，营业税税率为5％，城建税税率为5％，教育费附加费4％，则营业税金及附加计算如下：营业税为100000×5％＝5000(元)，城建税为5000×5％＝250(元)，教育费附加为5000×4％＝200(元)。假设“营业收入”工作底稿和“营业税金及附加”工作底稿都用Excel编制。当甲公司某年度营业收入审定数为150000元时，相应的营业税金及附加也要重新计算，工作量随之加大，此时可在Excel中通过建立“链接”的办法来解决这一问题。具体如下：在营业收入工作底稿中，将营业收入100000元改为150