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

审计中常用函数公式在审计工作中常用函数及实用技巧，此文献对于初学者很有协助常用函数公式有VALUE、LEFT、RIGHT、LEN和FIND 、MID、SUMIF、VLOOKUP、CONCATENATE（类似&）、IF、ROUND、TRIM、SUBTOTAL、1、 连字符“&” CONCATENATE在实际运用EXCEL进行审计工作的时候，我们为了能在两个数据库之间找一种合适的比较原则，有时需要将两个或以上的单元格连接起来这时，我们可以用字符“&”将两个或以上的单元格连接起来例子：我们想记录一下美元采购价格为12的材料A的采购数量这时，我们可以将单元格“A6”与单元格“C6”连接起来再分类汇总即可(如下表2)表2　　A　B　CD　E F　1美元汇率　　　　28.127　　　　3　　　　　4　　　　　5材料币别价格人民币价格采购数量　6材料AUSD12.00=C6*$A$21,200.00=A6&"---"&C6　7材料BUSD15.00=C7*$A$21,300.00=A7&"---"&C7　8材料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或选中单元格，点击叹号“转换为数值”4、 清除空格键函数-trim( )我们在导出ERP数据库中的数据时，由于ERP数据库中规定了字符的长度，因此在导出数据时，会导致有些字符背面带有空格键字符，影响我们数据记录的精确性为此，我们需要掌握一种可以除去文本以外空格键字符的函数语法：trim(text)阐明：trim( )函数可把文本前后两边的空格键去掉（注：不能去掉文本中间的空格键）。

函数的使用措施和函数value（）同样5、 取字符串或数值长度函数-len( )我们简介这个函数是为了配合下面截取字符串函数的使用而特别提出的语法：len(text)阐明：这个函数返回的数值是字符串的个数函数的使用措施和函数value（）同样6、 将数值转换为按指定数字格式表达的文本-TEXT()语法: TEXT(value,format_text)Value  为数值、计算成果为数字值的公式，或对涉及数字值的单元格的引用Format_text    为“单元格格式”对话框中“数字”选项卡上“分类”框中的文本形式的数字格式阐明· Format_text 不能涉及星号 (*)· 通过“格式”菜单调用“单元格”命令，然后在“数字”选项卡上设立单元格的格式，只会更改单元格的格式而不会影响其中的数值使用函数 TEXT 可以将数值转换为带格式的文本，而其成果将不再作为数字参与计算示例1. 创立空白工作簿或工作表 2. 请在“协助”主题中选用示例不要选用行或列标题 从协助中选用示例3. 按 Ctrl+C 4. 在工作表中，选中单元格 A1，再按 Ctrl+V 5. 若要在查当作果和查看返回成果的公式之间切换，请按 Ctrl+`（重音符），或在“工具”菜单上，指向“公式审核”，再单击“公式审核模式”。

123AB销售人员销售Buchanan2800Dodsworth40%公式阐明（成果）=A2&" sold "&TEXT(B2, "$0.00")&" worth of units."将上面内容合并为一种短语 (Buchanan sold $2800.00 worth of units.)=A3&" sold "&TEXT(B3,"0%")&" of the total sales."将上面内容合并为一种短语 (Dodsworth sold 40% of the total sales.)7、 截取字符串函数-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　　A　B　CD　E F　1A1 PO0512112=right(a1,len(a1)-3)　2A1 PO051=right(a2,len(a2)-3)　3B1 POA=right(a3,len(a3)-3) 4B1 PO=right(a4,len(a4)-3)　5C1 POC=right(a5,len(a5)-3)8、SUBTOTAL函数平常中最常用到的是在分类汇总中， 返回列表或数据库中的分类汇总一般，使用“数据”菜单中的“分类汇总”命令可以容易地创立带有分类汇总的列表一旦创立了分类汇总，就可以通过编辑 SUBTOTAL 函数对该列表进行修改语法:SUBTOTAL(function_num,ref1,ref2, ...)Function_num 为 1 到 11（涉及隐藏值）或 101 到 111（忽视隐藏值）之间的数字，指定使用何种函数在列表中进行分类汇总计算ref1……refn参数为要对其进行分类汇总计算的第1至29个命名区域或引用必须是对单元格区域的引用Function_num （涉及隐藏值）为1到11之间的自然数，用来指定分类汇总计算使用的函数值相称于函数1AVERAGE2COUNT3COUNTA4MAX5MIN6PRODUCT7STDEV8STDEVP9SUM10VAR11VARPFunction_num （忽视隐藏值） 为101到111之间的自然数值相称于函数101AVERAGE102COUNT103COUNTA104MAX105MIN106PRODUCT107STDEV108STDEVP109SUM110VAR111VARP注意：101到111仅在Office 、及更新的版本中能用。

解决隐藏数据时使用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），把“物料信息表”中的物料名称和单位相应地取到“物料进仓明细表”中表5　A　B　CD　E1材料信息表　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)-9-3120．0010N002=vlookup(a10,$a$2:$c$5,2,false)=vlookup(a10,$a$2:$c$5,3,false)-10-1412．0011N001=vlookup(a11,$a$2:$c$5,2,false)=vlookup(a11,$a$2:$c$5,3,false)-10-2124．0012N003=vlookup(a12,$a$2:$c$5,2,false)=vlookup(a12,$a$2:$c$5,3,false)-11-1225．0013N00。