审计常用函数

绝对引用和相引用

在使用EXCEL 函数时，我们常要引用某个单元格的数据。这时，我们就需要了解绝对引用和相对引用的区别和作用。 定义：

相对引用，随着引用单元格的位置变化，被引用单元格位置也是在变化的是相对引用；

绝对引用（$），随着引用单元格位置的变化，被引用单元格位置不变化的就是绝对引用（$）。 区别：相对引用和绝对引用的区别在于当引用单元格被复制到其他地方时，被引用单元格的位置变与不变的区别。 例子：

如下表（表1）所示，在单元格“A2”中存放着美元汇率信息，那么我们可将表1中的美元价格转换为人民币价格，即：对于材料A ，我们可以将单元格“C6”与单元格“A2”相乘得出材料A 的人民币价格。我们在“D6”中绝对引用单元格“A2”，相对引用单元格“C6”。在“D6”中输入“=C6*$A$2”，然后将单元格“D6”复制到剩余两个需要求人民币价格的单元格上，就可以很方便地求出结果了。 表1

连字符“&”

在实际运用EXCEL 进行审计的时候，我们为了能在两个数据库之间找一个合适的比较标准，有时需要将两个或以上的单元格连接起来。这时，我们可以用字符“&”将两个或以上的单元格连接起来。 例子：

我们想统计一下美元采购价格为12的材料A 的采购数量。这时，我们可以将单元格“A6”与单元格“C6”连接起来再分类汇总即可(如下表2) 。 表2

注意：用连字符“&”计算出的结果是文本型字符，也就是文本格式，不能用来加、减、乘、除等数学运算。如果文本型字符是数字，那么我们可以用函数value( )将其转换为数值性字符，然后才能进行数学运算。（函数value( )的用法见下面） value( )

在审计的过程中，我们也经常需要导出ERP 数据库里的数据到EXCEL 表格中进行处理。但在转换的过程中，有些软件不能自动把ERP 数据库里的数值型字符转换为数值型字符，只能是文本型字符，结果造成我们对数据进行处理时遇到很大的困难。如果遇到这种情况，我们可以用函数value( )来进行转换。 语法：value(text)

说明：“text ”是文本型字符，可以是直接输入文本，如：value(“134”) ；也可以引用其他单元格，如：value(E6)。我们在审计工作中常用后者。函数value( )得到的结果是数值型字符，主要用于将代表数字的字符串转换为数字。 例子：

我们先把A 列设置文本格式，然后再用函数value （）把它转换为数值。具体操作见表3 表3

去除空格键函数-trim( )

我们在导出ERP 数据库中的数据时，由于ERP 数据库中规定了字符的长度，所以在导出数据时，会造成有些字符后面带有空格键字符，影响我们数据统计的准确性。为此，我们需要掌握一个可以除去文本以外空格键字符的函数。 语法：trim(text)

说明：trim( ) 函数可把文本前后两边的空格键去掉（注：不能去掉文本中间的空格键）。函数的使用方法和函数value （）一样。 取字符串长度函数-len( )

我们介绍这个函数是为了配合下面截取字符串函数的使用而特别提出的。 语法：len(text)

说明：这个函数返回的数值是字符串的个数。函数的使用方法和函数value （）一样。 截取字符串函数-right( ),left( ),mid( )

我们从ERP 里导出数据之后，数据录入员所录入的数据不一定和我们所要的一模一样，但其中可能包含了我们所要的信息，这样，我们就需要把其中的信息提取出来。我们可以用截取字符串函数来帮助我们完成工作。 语法：

左截取字符串函数：left(text, number ) 右截取字符串函数：right(text, number )

中间截取字符串函数：mid(text, start_num, number ) 说明：

Text 是指函数操作的对象，也就是包含所要提取字符的文本 Number 是要提取字符的数量

Start_num 是指开始提取字符的起始位置

但在实际操作中，常将right （）函数或left （）函数与len （）函数结合起来使用，达到快速提取我们需要的信息的目的。在表

4中，我们假定A 列中前面的是分公司代码，后面是采购单号。我们现在要把所有的采购单号取出来分析，可以这样处理： 表4

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），把“物料信息表”中的的物料名称和单位相应地取到“物料进仓明细表”中。 表5

小提示：在公式中引用其他单元格时，可以直接将光标移动到目标单元格或用光标选取引用范围，再输入分格符“，”即可。

另外，要改变单元格的引用方式，在输入完单元格按F4。 sumif( )

语法：SUMIF (range , criteria , sum_range) 说明：

range ：为用于条件判断的范围； criteria ：用于判断的标准；

sum_range：实际求和的范围。

我们在运用该公式求和时要注意，range 和sum_range是一一对应的关系，如果他们的对应关系错了，求出的结果也不一定正确。我们还是以表5中的“物料进仓明细表”为例子，用sumif （）分类汇总物料出仓数量，见表6 表6

其他的一些函数

我们在实际运用EXCEL 审计的过程中，还常常用到month( ), year( )等函数。这些函数简单实用，常常和其他函数组合起来使用。

EXCEL 里如何快速填充空白的表格

选定区域-F5定位-定位条件-空值 输入=A3 CTRL+ENTER

一、Excel 在分析性测试、复核中的运用 注册会计师在分析审计风险确定重点审计领域、重要性水平和重大异常经济业务事项时，常常要对被审计单位的会计报表进行分析性测试和复核。在执行具体审计程序时，也常常要对本期数和上期数、本期各月数发生额(或余额) 进行对比分析，以查明有无重大变化和异常情况。如果用手工操作，不但计算量大且易出错，而使用Excel 则方便快捷不易出错。如表1所示，用Excel 编制出的甲公司某年度销售收入和销售成本对比分析表。假设用A-J 代表列数，用a －m 代表行数，本年数和上年数从被审计单位明细账中取得，那么先

在Ca 单元格中输入公式“＝Ba ／Aa”，然后将光标移到Ca 单元格的右下角，当出现“＋”形状时按下鼠标的左键，向下拖到Cm 单元格时松开，则上年各月的销售成本率计算结果就会自动出现在各单元格中；同理可计算出本年各月的销售成本率。再在Ga 单元格中输入公式“＝Da －Aa”，用同样的方法既可求出本年与上年的变动额。至于1-12月份合计数可用工具跳上的自动求和按钮“∑”求出。这样做可大大减轻工作量。同时根据计算结果可以发现，被审计单位在本年与上年的经营环境和营销策略未发生重大变化的情况下，本年的销售收入却比上年有较大增加，而12月份表现最为异常，因此将12月份的销售收入作为重点审计领域。

二、Excel 在审计往来账款账龄时的运用 往来账款(应收账款、其他应收款、应付账款等) 的账龄审计，是财务会计报表的基础审计工作，企业往来款项的明细账户动辄成百上千，账龄审计颇为繁琐。而利用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 。输入完毕后，模板将自动计算年末账龄，方便准确。

三、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，就可求出以后各年的折旧额，非常方便准确。 四、Excel 在审计相互引用数据的运用 由于会计报表各科目之间的勾稽关系，一个科目金额的变动常常会引起其他相关科目的连锁变动。这种情况常常导致修改审计工作底稿，一方面增加了工作量，另一方面也使工作底稿容易变得凌乱不堪，费时费力。Execl 在这方面也发挥了不小的作用。例如，甲公司某年度营业收入为100000元，营业税税率为5％，城建税税率为5％，教育费附加费4％，则营业税金及附加计算如下：营业税为100000×5％＝5000(元) ，城建税为5000×5％＝250(元) ，教育费附加为5000×4％＝200(元) 。假设“营业收入”工作底稿和“营业税金及附加”工作底稿都用Excel 编制。当甲公司某年度营业收入审定数为150000元时，相应的营业税金及附加也要重新计算，工作量随之加大，此时可在Excel 中通过建立“链接”的办法来解决这一问题。具体如下：在营业收入工作底稿中，将营业收入100000元改为150000元后，点击“复制”按钮，然后打开“营业税金及附加”所在的表格，选定“营业税金”中作为被乘数的营业收入100000元所

在的单元格，再右击鼠标，选定选择性粘贴，选择粘贴数值，则营业税金变为7500元。相应的将7500元所在的单元格，点击“复制”按钮，再到城建税和教育费附加计算公式中作为被乘数5000元所在的单元格，右击鼠标，选定选择性粘贴，选择粘贴数值，则计算结果自动变为375元和300元。那么，以后无论营业收入如何变动，只要修改营业收入数值，相应的营业税金及附加计算结果也会自动修改，与手工计算相比就轻松得多。