Click to edit Master title style,Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,*,*,单击此处编辑母版标题样式,*,*,天健会计师事务所,Pan-ChinaCertifiedPublicAccountants,EXCEL在审计中的运用,2012年8月,天健会计师事务所,1,EXCEL,在审计中的运用,第一篇、部分公式和函数基础应用,第二篇、使用,EXCEL,的高级功能,第三篇、,EXCEL,的基本功能,第四篇、其他功能,EXCEL在审计中的运用,2,部分公式和函数基础应用,1.1,一分钱的困惑,四舍五入公式的运用,审计中的运用：,坏账的计提，税费,、,外币折算的计算等,注意：（四舍五入到万元，可以用输入,=ROUND(number,-4),）,round,含义：,round,函数的语法为“,round,（,number,，,num_digits,）”，其中“,number,”为需要四舍五入的数字或运算公式（其计算结果必须是数字）。,num_digits,指定四舍五入的位数，如果,num_digits,大于,0,，则四舍五入到指定的小数位，,例如,round,（,2.15,，,1,）等于,2.2,；如果,num_digits,等于,0,，则将数字四舍五入到整数，例如,round,（,315.68,，,0,）等于,316,；如果,num_digits,小于,0,，则在小数点左侧的指定位数进行四舍五入，例如,round,（,21.5,，,-1,）等于,20,例如：,excel,体现,0.00+0.00+0.00=0.01,实际,0.003+0.004+0.004=0.011,部分公式和函数基础应用1.1 一分钱的困惑四舍五入公式的,3,1.2,日期和时间的计算公式,1.2.1,利用生成指定日期,DATE,函数,：,DATE,（,year,month,day,）,审计中的运用举例：,小技巧：,DATE,（,year+n,month+n,day+n,）,1.2.2,从特定日期中提取年份、月份和日期,提取函数：,提取年函数,year,（,year,month,day,）,提取月函数,month,（,year,month,day,）,提取日函数,day,（,year,month,day,）,审计中运用：借款利息测算、折旧测算,1.2 日期和时间的计算公式1.2.1利用生成指定日期1.,4,1.2.3,计算日期相差天数,DATEDIF,含义：,DATEDIF(start_date,end_date,unit),Start_date,为一个日期，它代表时间段内的第一个日期或起始日期。,End_date,为一个日期，它代表时间段内的最后一个日期或结束日期。,Unit,为所需信息的返回类型。,Y,时间段中的整年数。,M,时间段中的整月数。,D,时间段中的天数。,MD start_date,与,end_date,日期中天数的差。忽略日期中的月和年。,YM start_date,与,end_date,日期中月数的差。忽略日期中的日和年。,YD start_date,与,end_date,日期中天数的差。忽略日期中的年。,审计中的运用举例：,1.2-,例,2,1.2.3计算日期相差天数 DATEDIF含义：DATED,5,部分公式和函数基础应用,1.3,怎么把相同的信息相互引用,查找和定位的运用,VLOOKUP,含义：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组中该数值所在行中指定列处的数值。,公式：,VLOOKUP(lookup_value,table_array,col_index_num,range_lookup),lookup_value,：,要查找的值,table_array,：,要查找的区域,col,_index_num,：,返回数据在区域的第几列数,range_lookup,：是否,精确匹配,(TRUE,（或不填）,/FALSE),部分公式和函数基础应用1.3 怎么把相同的信息相互引用,6,审计中的运用举例,：,特别注意,：,最后一个参数,range_lookup,是个逻辑值，我们常常输入一个,0,字,(,或者,False),将返回精确匹配值,；其实也可以输入一个,1,字，或者,true,，,则返回近似匹配值,。,两者有什么区别呢？前者表示的是完整寻找，找不到就传回错误值,N/A,；后者先是找一模一样的，找不到再去找很接近的值，还找不到也只好传回错误值,N/A,。,审计中的运用举例：,7,VLOOKUP,的错误值处理：,如果找不到数据，函数总会传回一个这样的错误值,#N/A,，这错误值其实也很有用的。比方说，如果我们想这样来作处理：如果找到的话，就传回相应的值，如果找不到的话，我就自动设定它的值等于,0,，那函数就可以写成这样：,=if(iserror(vlookup(1,2,3,0),0,vlookup(1,2,3,0),iserror,函数。它的语法是,iserror(value),，即判断括号内的值是否为错误值。,if,函数，这也是一个常用的函数的，后面有机会再跟大家详细讲解。它的语法是,if(,条件判断式，结果,1,，结果,2),。如果条件判断式是对的，就执行结果,1,，否则就执行结果,2,。,VLOOKUP的错误值处理：,8,VLOOKUP,的缺陷解决方案：,1,、查找的相同的字段不能有重复值，如果有重复字段，会返回第一个查找的值。（条件格式的应用）,2,、查找条件和查找范围的首列或首行的数字格式必须保持一致，才能正确返回结果。（查找和替换的应用）,3,、只能按照按照列来查找。（,HLOOKUP,公式的运用）,4,、验算和复核。,VLOOKUP的缺陷解决方案：,9,部分公式和函数基础应用,1.4,统计和求和函数的应用,1.4.1 sumif,的运用,公式：,SUMIF(range,criteria,sum_range),range,为用于条件判断的单元格区域。,criteria,为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。,sum_range,是需要求和的实际单元格。,使用技巧：,criteria,，,条件可以表示为,32,、,32,、,32,或,apples,。条件还可以使用通配符：问号,(?),和星号,(*),，如需要求和的条件为第二个数字为,2,的，可表示为,?2*,从而简化公式设置。问号匹配任意单个字符；星号匹配任意一串字符。如果要查找实际的问号或星号，请在该字符前键入波形符,(),countif,的运用,部分公式和函数基础应用1.4 统计和求和函数的应用,10,1.4.2 countif,的运用,计算个数,公式：,countif,(range,criteria),range,为用于条件判断的单元格区域。,criteria,为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。,应用：,求真空单元格单个数,:=COUNTIF(,数据区,=),大于,E5,单元格的值,=COUNTIF(,数据区,&$E$5),两个字符,=COUNTIF(data,?),包含,D3,单元格的内容,=COUNTIF(D3,*&D3&*),第,2,字是,D3,单元格的内容,=COUNTIF(D3,?&D3&*),1.4.2 countif的运用计算个数,11,部分公式和函数基础应用,1.5,财务金融计算,1.5.1,审计中如何测算的折旧,平均年限法,年,折旧率,=,（,1,预计净,残值,率）,/,预计使用年限,100%,加速折旧法,（,1,）,双倍余额递减法,年折旧率,=2/,预计的折旧年限,100%,（,2,）年数总和法,年折旧率,=,（预计使用年限已使用年限）,/,（预计使用年限,预计使用年限,+12100%,部分公式和函数基础应用1.5 财务金融计算,12,首先，应该思考，计算当期折旧,4,种情况？,1,、本年需要计提,12,个月的折旧；,2,、本年折旧月数少于,12,个月折旧完毕的固定资产；,3,、新购入少于,12,个月的固定资产折旧；,4,、本年已经提足折旧，折旧月数为,0,的折旧。,效 果,结合,If,函数，,dated,函数,首先，应该思考，计算当期折旧4种情况？结合If函数，date,13,理清逻辑关系，通过画图：,A月,开始使用日期,期末,A月使用月数,A月-12月使用月数,本年折旧期间为0,A月-12月使用月数,本年折旧期间为 (12-A月+使用月数),A月12,本年折旧期数为12个月,A月使用月,14,与折旧相关的函数,与折旧相关的函数,15,平均年限法案例：,公式：本期折旧额,=,（,1,残值率）*本年折旧月数*原值,/,预计使用月数,100%,双倍余额递减法？,思路：第一步,计算每项固定资产的在各个期间的折旧,第二步,判断本年应该取得相应月数对应的折旧,平均年限法案例：,16,1.5.2,货币时间价值函数,函数名称,函数功能,函数公式,PV,计算现值的函数,PV,（,rate,，,nper,，,pmt,，,fv,，,type,）,NPV,计算净现值的函数,NPV,（,rate,，,value1,，,value2,，,）,FV,计算终值的函数,FV,（,rate,，,nper,，,pmt,，,pv,，,type,）,RATE,计算贴现率的函数,RATE,（,nper,，,pmt,，,pv,，,fv,，,type,，,guess,）,PMT,基于固定利率及等额分期付款方式，返回贷款每期付款额,PMT,（,rate,，,nper,，,pv,，,fv,，,type,）,IPMT,基于固定利率及等额分期付款方式，返回给定期数内对投资的利息偿还额,IPMT(rate,per,nper,pv,fv,type),1.5.2货币时间价值函数函数名称函数功能函数公式PV计算现,17,融资租赁函数的运用：,PMT,公式的应用,每期还款：,PMT,（,rate,，,nper,，,pv,，,fv,，,type,）,每期利息：摊余成本*合同月利率 或（公式,IPMT,）,每期摊余成本：上期摊余成本,本金,融资租赁函数的运用：,18,第二篇、使用,EXCEL,的高级功能,作用：高级功能能极大的加强,Excel,处理电子表格数据的能力，更加轻松地应对工作,2.1,条件格式,2.2,分级显示,2.3,数据透视表,第二篇、使用EXCEL的高级功能作用：高级功能能极大的加强,19,EXCEL,的高级功能,2.1,怎么让报表中的,0,都不见？,条件格式的运用,EXCEL的高级功能2.1 怎么让报表中的0都不见？,20,2.1.1,怎么让报表中的,0,都不见？,数值为,0,的颜色为白色,2.1.2,怎么查找出相同单位明细挂在不同科目（往来科目对冲）,查找重复文本和数值,2.1.3,怎么设置区间数值的预警？,突出符合区间的数值,2.1.4,怎么查找明细账中出特别的字词？如诉讼、罚金,突出符合包含特别词汇的单元格,2.1.5,怎么突出高于平均值的每月发生的费用？,突出符合高于平均值的单元格,2.1.6,怎么在一系类的数据中知道使每个数据和平均值的关系？,条件格式图表集,2.1.1 怎么让报表中的0都不见？数值为0的颜色为白,21,2.1.7,自动填充表格线框的小技巧,条件格式中新建规则,步骤,1,选定单元格区域,A2,：,F1000,（范围大小可根据用户必须使用的行数多少而调整），保持活动单元格在,A2,，单击菜单栏的“格式”,-“,条件格式”。,步骤,2,在“条件格式”对话框中的“条件,1,（,1,）”下拉列表框中的选择“公式”，在右侧的文本框中输入“,=$A2”,步骤,3,单击“格式”按钮，在弹出的“单元格格式”对话框中选择“边框”选项卡，单击“预置”下的“外边框”图标。,2.1.7自动填充表格线框的小技巧条件格式中新建规则,22,EXCEL,