审计工作中常用EXCLE函数整理

1、精品文档你我共享审计中常用函数公式在审计工作中常用函数及实用技巧， 此文件对于初学者很有帮助。常用函数公式有VALUE LEFT、RIGHT LEN和 FIND、MID SUMIF VLOOKUPCONCATENAT类似 &）、IF、ROUNDTRIM SUBTOTAL1、连字符“ & CONCATENATE在实际运用EXCEL进行审计工作的时候，我们为了能在两个数据库之间找一个合适的比较标准，有时需要将两个或以上的单元格连接起来。这时，我们可以用字符“&”将两个或以上的单元格连接起来。例子：我们想统计一下美元采购价格为 12的材料A的采购数量。这时，我们可以将单元格“A6”与单元格“ C6连

2、接起来再分类汇总即可 （如下表2）。表2A1BC1DEF1美兀汇率8.1272345材料币别价格人民币价格采购数量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（）将其转换为数值型字符，然后才能进行数学运算。（

3、函数value（）的用法见下面）2、CONCATENATE数功能：将多个文本字符串合并成一个。实务中，不同的工作簿之间并非时刻存在唯一的关键字符串（如上例为“客户名称”）。那么,我们就需要将不同单元格内的信息进行合并，使其生成唯一的一个字符串。例如：在编制服装企业存货账龄分析表时，由于获取的明细清单内各件衣服的类别、款式、颜 色、尺寸均不具有唯一性特点，如下“表四”所示：为了使用VLOOKU函数，我们需要自己构建一个唯一性的字符串。在本例中，我们可先在首列 中插入一列，标题可称作为“品名”，然后使用CONCATENA函数，创建唯一性的字符串，公式 介绍如下：ABCDE1品名类别款式颜色尺寸2女

4、装/休闲服/红/中号女装休闲服红中号公式： =CONCATENATE(text1,text2,text3,text4, ，text29,text30)该函数，共可合并 30个不同单元格内的字符串，在本例中的运用如下：=CONCATENATE(B2,/,C2,/,D2,/,E2)(其中“ /”，是为了便于检查的需要，不用也可)注：常用语连接时间日期。3、value()在审计实务中，我们常碰到某些财务软件导出的财务数据是文本型字符串，无法进行计算，如果遇到这种情况，使用VALUE函数，可以将该文本型字符串转换成数字型语法：=value(text)功能：将代表数字的文本字符串转换成数字说明：TEXT

5、表示需要转换的文本型单元格位置，比如我们需要将 A1单元格中的文本型字符串转换成数值型，则公式= VALUE(A1)即可。(注：EXCEL中存在个小BUG当我们选中文本型字符串范围后，如果按CTRH F,查找“.”替换为“ ”的话，可将原先带小数点的文本型字符串全部转换为数值型字符串，可以大大地简 化操作步骤。但是，当文本型字符串所代表的数字信息系整数时(即不存在小数点时)，该方 法则不适用。)注：原数据乘1或选中单元格，点击叹号“转换为数值”4、去除空格键函数-trim()我们在导出ERP数据库中的数据时，由于ERP数据库中规定了字符的长度，所以在导出数据时，会造成有些字符后面带有空格键字符

6、，影响我们数据统计的准确性。为此，我们需要掌 握一个可以除去文本以外空格键字符的函数。语法:trim(text)说明：trim()函数可把文本前后两边的空格键去掉(注：不能去掉文本中间的空格键)。函数的使用方法和函数 value () 一样。5、取字符串或数值长度函数 -le n()我们介绍这个函数是为了配合下面截取字符串函数的使用而特别提出的。语法：len(text)说明：这个函数返回的数值是字符串的个数。函数的使用方法和函数value () 一样。6、将数值转换为按指定数字格式表示的文本-TEXT()语法：TEXT (value , format_text )Value为数值、计算结果为数

7、字值的公式，或对包含数字值的单元格的引用。Format_text数字格式。为“单元格格式”对话框中“数字”选项卡上“分类”框中的文本形式的说明 Format_text 不能包含星号(*)。通过“格式”菜单调用“单元格”命令，然后在“数字”选项卡上设置单元格的格式，只会更改单元格的格式而不会影响其中的数值。使用函数TEXT可以将数值转换为带格式的文本，而其结果将不再作为数字参与计算。示例1. 创建空白工作簿或工作表。2. 请在“帮助”主题中选取示例。不要选取行或列标题。从帮助中选取示例。3. 按 Ctrl+C。4. 在工作表中，选中单元格A1，再按Ctrl+V。5. 若要在查看结果和查看返回结果

8、的公式之间切换，请按Ctrl+ (重音符)，或在“工具”菜单上，指向“公式审核”，再单击“公式审核模式”。AB销售人员销售Buchanan28001Dodsworth40%2公式说明(结果)3=A2 & sold & TEXT(B2, $0.00)&将上面内容合并为一个短语(Bucha nan soldworth of un its.$2800.00 worth of un its.)=A3& sold &TEXT(B3,0%)& of the将上面内容合并为一个短语(Dodsworth soldtotal sales.40% of the total sales.)7、截取字符串函数-rig

9、ht( ),left( ),mid()我们从ERP里导出数据之后，数据录入员所录入的数据不一定和我们所要的一模一样，但其中可能包含了我们所要的信息，这样，我们就需要把其中的信息提取出来。我们可以用截取 字符串函数来帮助我们完成工作。语法：左截取字符串函数：left(text, n umber )右截取字符串函数：right(text, nu mber )中间截取字符串函数：mid(text, start_ num, nu mber )说明：Text是指函数操作的对象，也就是包含所要提取字符的文本Number是要提取字符的数量Start_ num是指开始提取字符的起始位置但在实际操作中，常将ri

10、ght ()函数或left ()函数与len ()函数结合起来使用，达到快速提取我们需要的信息的目的。在表4中，我们假定 A列中前面的是分公司代码，后面是采购单号。我们现在要把所有的采购单号取出来分析，可以这样处理：日常中最常见到的是在分类汇总中，返回列表或数据库中的分类汇总。通常，使用“数据”菜单中的“分类汇总”命令可以容易地创建带有分类汇总的列表。一旦创建了分类汇总，就可以通过编辑SUBTOTAL函数对该列表进行修改。语法：SUBTOTAL（function_num,ref1,ref2,）Fun ction_num 为1到11 （包含隐藏值）或 101到111 （忽略隐藏值）之间的数字，

11、指定使用何种函数在列表中进行 分类汇总计算。ref1refn参数为要对其进行分类汇总计算 的第1至29个命名区域或引用。必须是对单元格区域的引用。Fun ction_num（包含隐藏值）为1到11之间的自然数，用来指定分类汇总计算使用的Fun ction_num（忽略隐藏值） 为101到111之间的自然数值相当于函数101AVERAGE102COUNT103COUNTA函数值相当于函数1AVERAGE2COUNT3COUNTA4MAX5MIN6PRODUCT7STDEV8STDEVP9SUM10VAR11VARP104MAX105MIN106PRODUCT107STDEV108STDEVP10

12、9SUM110VAR111VARP注意：101到111仅在Office 2003、2007及更新的版本中能用。处理隐藏数据时使用。9、VLOOKUP）语法：VLOOKUP（lookup_value,table_array,col_ in dex_ nu m,ra nge_lookup）说明：lookup_value :指需要在table_array区域中第一列查找的值；table_array :指需要在其中查找数据的表格；col_index_num :指在table_array 区域中对应匹配值所返回的值所在的列数；range_lookup :这是一个逻辑值（ture 或false ）,如果填

13、ture 是近似匹配，而 false则是精确匹配。这个函数的主要用途是将存放在另外一张表格的信息相对应地提取到一张表格上。我们举个简单的例子（见表5）,把“物料信息表”中的物料名称和单位相应地取到“物料进仓明细表”中。ABCDE1材料信息表2物料编号物料名称单位3N001材料A张4N002材料B公斤5N003J材料C公斤67物料进仓明细表8物料编号物料名称单位进仓时间进仓数量9N001=vlookup(a9,$a$2:$c$5,2,f alse)=vlookup(a9,$a$2:$c$5,3,false)2005-9-3120. 0010N002=vlookup(a10,$a$2:$c$5,2

14、, 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-1

15、2-219. 50小提示：在公式中引用其他单元格时，可以直接将光标移动到目标单元格或用光标选取引用范 围，再输入分格符“，”即可。另外，要改变单元格的引用方式，在输入完单元格按F4。table_array区域可以定义成名称,使用名称来表达10、sumif()语法：SUMIF(range, criteria, sum_range)说明：range :为用于条件判断的范围；criteria :用于判断的标准； sum_ra nge：头际求和的范围。我们在运用该公式求和时要注意，range和sum_range是一一对应的关系，如果他们的对应关系错了，求出的结果也不一定正确。我们还是以表5中的“物料进

16、仓明细表”为例子，用sumif ()分类汇总物料出仓数量，见表 6ABCDE_11材料信息表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. 0C10N002材料B公斤2005-10-1412. 0C11N001材料A张2005-10-2124. 0C12N003材料C

17、公斤2005-11-1225. 0C13N002材料B公斤2005-12-219. 5C注：常用于往来双挂账。11、其他的一些函数我们在实际运用 EXCEL审计的过程中，还常常用到month( ), year( )、now()等函数。这些函数简单实用，常常和其他函数组合起来使用。宏所谓宏，就是用 VBA( Visual Base Applicati on)语言编写的一段程序。如果我们在审计的过程中，能够用运用宏来辅助审计工作，那将会大大地提高我们的工作效率。VBA语言是VB语言的一个分支，如果我们有一种数据库计算机语言作为基础，那么学好VBA语言并不难。注：不常用，太繁琐。使用技巧绝对引用和相

18、引用在使用EXCEL函数时，我们常要引用某个单元格的数据。这时，我们就需要了解绝对引用和相对引用的区别和作用。定义：相对引用，随着 引用单元格的位置变化，被引用单元格位置也是在变化的是相对引用;绝对引用($)，随着引用单元格位置的变化, 被引用单元格 位置不变化的就是绝对引用（$ ）。区别：相对引用和绝对引用的区别在于当引用单元格被复制到其他地方时,被引用单元格的位置变与不变的区别。例子：如下表（表1）所示，在单元格“ A2中存放着美元汇率信息，那么我们可将表 1中的美 元价格转换为人民币价格，即：对于材料A我们可以将单元格“ C6与单元格“ A2”相乘得出材料A的人民币价格。我们在“ D6中

19、绝对引用单元格“ A2”，相对引用单元格“C6。在“D6 中输入“ =C6*$A$2”，然后将单元格“ D6复制到剩余两个需要求人民币价格的单元格上，就可以很方便地求出结果了。表1ABC_1D-jE1美兀汇率28.127345材料币别价格人民币价格采购数量6材料AUSD12=C6*$A$212007材料BUSD15=C7*$A$213008材料CUSD11=C8*$A$213209材料AUSD12=C9*$A$21100Excel在审计往来账款账龄时的运用往来账款（应收账款、其他应收款、应付账款等）的账龄审计，是财务会计报表的基础审计工作，企业往来款项的明细账户动辄成百上千，账龄审计颇为繁琐。

20、而利用Excel进行往来账款账龄审计却非常简单方便。利用Excel进行账龄审计，需要企业提供往来款项、分客户明细账户编制的“年初数、本年累计借方发生额、本年累计贷方发生额、年末余额”格式的会计 数据资料。目前越来越多的企业采用会计软件进行核算，该资料可以利用会计软件提供的“数 据导出”功能导出为Excel格式获得。将获取资料置于一个Excel工作簿内。运用 Excel的数据排序功能将年末余额为负数的数据（需要报表重分类）剔除后，就可以在 A、B C、D单元格中设定函数公式。一是账龄1年以内的函数为：A= IF（本年贷方发生额 =年初余额，年末余额，本年借方发生额）。二是接着利用 Excel的数

21、据排序功能，筛选出（年末余额一账龄1年以内金额）0的明细账户，获取其上年“年初数、本年累计借方发生额、本年累计贷方发生额、年末精品文档你我共享余额”格式的会计数据资料。在上表“单位名称”列左侧增加列，用以添加上年数据。账龄12年的函数为：B= IF（本年贷方发生额十上年贷方发生额）:上年度年初余额，年末余额一账龄1年以内的金额 A,上年度借方发生额）。三是筛选出（年末余额一账龄）1年以内金额一账龄12年的金额） 0的明细账户，获取其更前一个年度的数据。 账龄23年的函数为：C=IF（本 年贷方发生额+上年贷方发生额+更上一年的贷方发生额）=更上一个年度年初余额， 年末余额一账龄1年以内的金额

22、A-账龄1-2年的金额B,更上一个年度的借方发生额 ）。四是账龄3年以上的函数为：n：年末余额一账龄1年以内的金额 A账龄1-2年的金额B账龄2-3年的金额C。输入完毕后，模板将自动计算年末账龄，方便准确。注：审计年度新增往来单位可 能穿插在内，与上一年度在顺序上不匹配，就会需要到SUMIF。所以此方法更为繁琐，更简便的方法为：用鼎信诺取连续三年的数据，系统自动生成账龄。用时目测1小时6个往来。Excel在审计固定资产折旧时的运用Excel为注册会计师提供了年数总和法（SYD）、双倍余额递减法（DDB）、直线法（SLN）等多种计算折旧方法。笔者以年数总和法为例予以介绍。例如，甲公司的一项固定资

23、产原值为 200000元，预计残值为20000元，使用年限为5年，采用年数总和法计算折旧。为审计该项折 旧计提是否正确，可用以下方法：打开Excel，点击工具条上的“ FX”函数按钮，弹出“粘贴函数”对话框，其中分左右两栏：左栏为函数分类，右栏为函数名。在函数分类栏“常用函数” 对应的“函数名”中选定 SYD然后单击“确定”按钮，就可弹出这就对话框，其中有四项参数：cost,salvage,life,per 。在 cost 中输入成本 200000,在 salvage 中输入残值 20000，在 life中输入使用年限5，在per中输入1,就可求得第一年的折旧额为60000元。依次把per中的

24、数值换为2, 3, 4, 5，就可求出以后各年的折旧额，非常方便准确。（SLNDDB）同理。注：此为年折旧。Excel在审计相互引用数据的运用由于会计报表各科目之间的勾稽关系，一个科目金额的变动常常会引起其他相关科目的连锁变动。这种情况常常导致修改审计工作底稿，一方面增加了工作量，另一方面也使工作底稿容易变得凌乱不堪，费时费力。Execl在这方面也发挥了不小的作用。例如，甲公司某年度营业收入为100000元，营业税税率为 5%，城建税税率为 5%,教育费附加费 4%,则营精品文档你我共享业税金及附加计算如下：营业税为100000 X 5%= 5000（元），城建税为5000 X 5 %= 250（元），教育费附加为5000X 4%= 200（元）。假设“营业收入”工作底稿和“营业税金及附加”工作底 稿都用Excel编制。当甲公司某年度营业收入审定数为150000元时，相应的营业税金及附加也要重新计算，工作量随之加大，此时可在Excel中通过建立“链接”的办法来解决这一问题。具体如下：在营业收入工作底