EXCEL在审计中的运用课件

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月,EXCEL,在审计中的运用,第一篇、部分公式和函数基础应用,第二篇、使用,EXCEL,的高级功能,第三篇、,EXCEL,的基本功能,第四篇、其他功能,部分公式和函数基础应用,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.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.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.3,怎么把相同的信息相互引用,查找和定位的运用,VLOOKUP,含义：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组中该数值所在行中指定列处的数值。,公式：,VLOOKUP(lookup_value,table_array,col_index_num,range_lookup),lookup_value,：,要查找的值,table_array,：,要查找的区域,col,_index_num,：,返回数据在区域的第几列数,range_lookup,：是否,精确匹配,(TRUE,（或不填）,/FALSE),审计中的运用举例,：,特别注意,：,最后一个参数,range_lookup,是个逻辑值，我们常常输入一个,0,字,(,或者,False),将返回精确匹配值,；其实也可以输入一个,1,字，或者,true,，,则返回近似匹配值,。,两者有什么区别呢？前者表示的是完整寻找，找不到就传回错误值,N/A,；后者先是找一模一样的，找不到再去找很接近的值，还找不到也只好传回错误值,N/A,。,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,的缺陷解决方案：,1,、查找的相同的字段不能有重复值，如果有重复字段，会返回第一个查找的值。（条件格式的应用）,2,、查找条件和查找范围的首列或首行的数字格式必须保持一致，才能正确返回结果。（查找和替换的应用）,3,、只能按照按照列来查找。（,HLOOKUP,公式的运用）,4,、验算和复核。,部分公式和函数基础应用,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.2 countif,的运用,计算个数,公式：,countif,(range, criteria),range,为用于条件判断的单元格区域。,criteria,为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。,应用：,求真空单元格单个数,:=COUNTIF(,数据区,=),大于,E5,单元格的值,=COUNTIF(,数据区,&$E$5),两个字符,=COUNTIF(data,?),包含,D3,单元格的内容,=COUNTIF(D3,*&D3&*),第,2,字是,D3,单元格的内容,=COUNTIF(D3,?&D3&*),部分公式和函数基础应用,1.5,财务金融计算,1.5.1,审计中如何测算的折旧,平均年限法,年,折旧率,=,（,1,预计净,残值,率）,/,预计使用年限,100%,加速折旧法,（,1,）,双倍余额递减法,年折旧率,=2/,预计的折旧年限,100%,（,2,）年数总和法,年折旧率,=,（预计使用年限已使用年限）,/,（预计使用年限,预计使用年限,+12100%,首先，应该思考，计算当期折旧,4,种情况？,1,、本年需要计提,12,个月的折旧；,2,、本年折旧月数少于,12,个月折旧完毕的固定资产；,3,、新购入少于,12,个月的固定资产折旧；,4,、本年已经提足折旧，折旧月数为,0,的折旧。,效 果,结合,If,函数，,dated,函数,理清逻辑关系，通过画图：,A月,开始使用日期,期末,A月使用月数,A月-12月使用月数,本年折旧期间为0,A月-12月使用月数,本年折旧期间为 (12-A月+使用月数),A月12,本年折旧期数为12个月,A月12,本年折旧期间为 A 月,期末时点已全部提满折旧,与折旧相关的函数,平均年限法案例：,公式：本期折旧额,=,（,1,残值率）*本年折旧月数*原值,/,预计使用月数,100%,双倍余额递减法 ？,思路：第一步,计算每项固定资产的在各个期间的折旧,第二步,判断本年应该取得相应月数对应的折旧,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),融资租赁函数的运用：,PMT,公式的应用,每期还款：,PMT,（,rate,，,nper,，,pv,，, fv,，, type,）,每期利息：摊余成本*合同月利率 或（公式,IPMT,）,每期摊余成本：上期摊余成本,本金,第二篇、使用,EXCEL,的高级功能,作用：高级功能能极大的加强,Excel,处理电子表格数据的能力，更加轻松地应对工作,2.1,条件格式,2.2,分级显示,2.3,数据透视表,EXCEL,的高级功能,2.1,怎么让报表中的,0,都不见？,条件格式的运用,2.1.1,怎么让报表中的,0,都不见？,数值为,0,的颜色为白色,2.1.2,怎么查找出相同单位明细挂在不同科目（往来科目对冲）,查找重复文本和数值,2.1.3,怎么设置区间数值的预警？,突出符合区间的数值,2.1.4,怎么查找明细账中出特别的字词？如诉讼、罚金,突出符合包含特别词汇的单元格,2.1.5,怎么突出高于平均值的每月发生的费用？,突出符合高于平均值的单元格,2.1.6,怎么在一系类的数据中知道使每个数据和平均值的关系？,条件格式图表集,2.1.7,自动填充表格线框的小技巧,条件格式中新建规则,步骤,1,选定单元格区域,A2,：,F1000,（范围大小可根据用户必须使用的行数多少而调整），保持活动单元格在,A2,，单击菜单栏的“格式”,-“,条件格式”。,步骤,2,在“条件格式”对话框中的“条件,1,（,1,）”下拉列表框中的选择“公式”，在右侧的文本框中输入“,=$A2”,步骤,3,单击“格式”按钮，在弹出的“单元格格式”对话框中选择“边框”选项卡，单击“预置”下的“外边框”图标。,EXCEL,的高级功能,2.2,我合并的子公司和分公司这么多，合并报表列数好长，不清晰怎么办？,分级显示,的运用,审计中主要应用：,1,、在合并报表中运用，能够清晰反映合并报表、合并附注关系；,2,、在公司提供的按地区或种类采购、销售表可以清晰在同一张表格中显示明细、汇总。,EXCEL,的高级功能,2.3,使用数据透视表分析数据,2.3,数据透视表,数据透视表是交互式报表，可快速合并和比较大量数据，有机地综合了数据排序、筛选、分类汇总等数据分析的有点，可方便地调整分类汇总的方式，灵活地以多种不同方式展示数据的特征。,？,怎么创建数据透视表？,步骤,1,：选择数据源类型,步骤,2,：选择数据源区域,步骤,3,：指定数据透视表位置,数据透视表的刷新,第三篇、,EXCEL,的基本功能,3.1,工作表标签颜色、显示、隐藏,P32,3.2,冻结、拆分、并排窗口,3.3,查找和替换功能的应用,3.4,批注的显示和隐藏,3.5,添加自己常用的工具栏,3.6,单元格的隐藏和保护功能,3.7,图表功能,3.8,以万为单位显示数值,3.1,凸显工作簿中重要工作表？,工作表标签颜色、显示、隐藏,3.2,怎么在查看数据时保留相应的表头？,工作表冻结、拆分、并排窗口,3.3,报告和附注中公司名称未全部修改？,怎么底稿编制人全部一次性签名？,查找和替换功能的应用,3.4,复核底稿时候，复核意见怎么在底稿上凸显？,底稿中重要的说明怎么更加清晰明了的反映？,批注的,插入,、,显示,、,隐藏,（审阅、审核的应用）,复核人员怎么快速知道哪些地方修订了呢？,3.5,添加自己常用的工具栏,小工具：表单的运用,3.6,表格中重要的信息不想让其他人知道，怎么操作？,excel,的隐藏功能,3.6.1,工作表隐藏,3.6.3,单元格数据暂时消失小技巧,方式一：将单元格数字格式设置为“,;,”（三个半角的分号）；,方式二：将单元格的背景和字体颜色设置为相同的颜色以实现浑然一体的效果；,缺陷：在全选工作表的时候，数据会显现,3.7,图表功能,步骤,1,：选中数据区域；,步骤,2,：点击“插入”中的图表那栏,3.8,整体数值除以万元,步骤,1,：选定一个空白单元格，填入数值,10000,，并复制,步骤,2,：选定需要除以万元的区域,举一反三,第四篇、其他功能,4.1,链接和超链接的应用,4.2,对数据区的保护,4.3,打印区域的设置、排版,4.4,将,EXCEL,表格数据嵌入到,word,文档中,4.5,对输入内容的提示与输入错误的反馈,4.1,一张表格需要引用链接到另外一张表格中去，怎么办？,引用链接和超链接的应用,在同一个工作簿的工作表引用 在一个工作表中引用其他文件,4.2,对数据区的保护,1,、全部编辑比较直接的方式,2,、工作表中数据区的保护,步骤,1,：选定保护的单元格,步骤,2,：,4.3,打印区域的设置、排版,4.3.1,打印表头及页眉页脚,4.3.2,如何添加“样稿”？,4.4,将,EXCEL,表格数据嵌入到,word,文档中,页眉中“插入图片”,4.4,将,EXCEL,表格数据嵌入到,word,文档中？,找到“对象”,4.5,对输入内容的提示与输入错误的反馈,通过数据有效性的设置，对单元格输入内容进行提示，在输入错误后，可以有不同形式的应对处理,步骤,1,：点击这一列你想要设定提示信息的数据，点击列头的位置，就可以选中它。,步骤,2,：在菜单栏上面点击,【,数据,】,选项下的,【,数据有效性,】,步骤,3,选择数据有效性的样式，有三种样式可选,【,警告,】【,停止,】【,信息,】,，选择一种你觉得合适的即可。然后填写提示信息的标题和错误信息。,步骤,4,接下来点击,【,输入信息,】,选项，我们来设定信息输入的时候，,excel,的提示信息。这时候的信息出现在你选中单元格的时候。在标题上输入,【,亲！,】,在输入信息栏输入,【,请输入正确的数据,】,，点击确定完成。,