1、天健会计师事务所 Pan-China Certified Public Accountants,EXCEL在审计中的运用孙 剑 2012年8月,EXCEL在审计中的运用,第一篇、部分公式和函数基础应用第二篇、使用EXCEL的高级功能第三篇、EXCEL的基本功能第四篇、其他功能,部分公式和函数基础应用,1.1 一分钱的困惑四舍五入公式的运用,审计中的运用：坏账的计提，税费、外币折算的计算等注意：（四舍五入到万元，可以用输入=ROUND(number,-4) ）,round含义：round函数的语法为“round（number，num_digits）”，其中“number” 为需要四舍五入的数字或
2、运算公式（其计算结果必须是数字）。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 （
3、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 为一个日期，它
4、代表时间段内的最后一个日期或结束日期。 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含义：在表格或数值数组的首列查找指定的数值，并由此返
5、回表格或数组中该数值所在行中指定列处的数值。 公式： 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，则返回近似匹配值。两者有什么区别呢？前者表示的是
6、完整寻找，找不到就传回错误值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函数，这也是一个常用的函数的，后面有机会再跟大家详细讲解。
7、它的语法是if(条件判断式，结果1，结果2)。如果条件判断式是对的，就执行结果1，否则就执行结果2。,VLOOKUP的缺陷解决方案：1、查找的相同的字段不能有重复值，如果有重复字段，会返回第一个查找的值。（条件格式的应用）2、查找条件和查找范围的首列或首行的数字格式必须保持一致，才能正确返回结果。（查找和替换的应用）3、只能按照按照列来查找。（HLOOKUP公式的运用）4、验算和复核。,部分公式和函数基础应用,1.4 统计和求和函数的应用 1.4.1 sumif的运用 公式：SUMIF(range,criteria,sum_range) range 为用于条件判断的单元格区域。 criteri
8、a 为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。sum_range 是需要求和的实际单元格。使用技巧：criteria，条件可以表示为 32、“32“、“32“ 或 “apples“。条件还可以使用通配符：问号 (?) 和星号 (*)，如需要求和的条件为第二个数字为2的，可表示为“?2*“,从而简化公式设置。问号匹配任意单个字符；星号匹配任意一串字符。如果要查找实际的问号或星号，请在该字符前键入波形符 () countif的运用,1.4.2 countif的运用计算个数公式： countif(range, criteria) range 为用于条件判断的单元格区域。 c
9、riteria 为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。应用：求真空单元格单个数:=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/预计的折旧年限
10、100% （2）年数总和法 年折旧率=（预计使用年限已使用年限）/（预计使用年限预计使用年限+12100%,首先，应该思考，计算当期折旧4种情况？ 1、本年需要计提12个月的折旧； 2、本年折旧月数少于12个月折旧完毕的固定资产； 3、新购入少于12个月的固定资产折旧； 4、本年已经提足折旧，折旧月数为0的折旧。效 果,结合If函数，dated函数,理清逻辑关系，通过画图：,A月,开始使用日期,期末,A月使用月数,A月-12月使用月数,本年折旧期间为0,A月-12月使用月数,本年折旧期间为 (12-A月+使用月数),A月使用月数,A月12,本年折旧期数为12个月,A月12,本年折旧期间为 A
11、月,期末时点已全部提满折旧,与折旧相关的函数,平均年限法案例：公式：本期折旧额=（1残值率）*本年折旧月数*原值/预计使用月数100% 双倍余额递减法 ？思路：第一步 计算每项固定资产的在各个期间的折旧第二步 判断本年应该取得相应月数对应的折旧,1.5.2货币时间价值函数,融资租赁函数的运用： PMT公式的应用 每期还款：PMT（rate，nper，pv， fv ， type ）每期利息：摊余成本*合同月利率 或（公式 IPMT）每期摊余成本：上期摊余成本 本金,第二篇、使用EXCEL的高级功能,作用：高级功能能极大的加强Excel处理电子表格数据的能力，更加轻松地应对工作2.1 条件格式2.
12、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 选定单元格区域
13、A2：F1000（范围大小可根据用户必须使用的行数多少而调整），保持活动单元格在A2，单击菜单栏的“格式”-“条件格式”。步骤2 在“条件格式”对话框中的“条件1（1）”下拉列表框中的选择“公式”，在右侧的文本框中输入“ =$A2” “,步骤3 单击“格式”按钮，在弹出的“单元格格式”对话框中选择“边框”选项卡，单击“预置”下的“外边框”图标。,EXCEL的高级功能,2.2 我合并的子公司和分公司这么多，合并报表列数好长，不清晰怎么办？分级显示的运用,审计中主要应用：1、在合并报表中运用，能够清晰反映合并报表、合并附注关系；2、在公司提供的按地区或种类采购、销售表可以清晰在同一张表格中显示明细
14、、汇总。,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
15、以万为单位显示数值,3.1凸显工作簿中重要工作表？工作表标签颜色、显示、隐藏3.2怎么在查看数据时保留相应的表头？工作表冻结、拆分、并排窗口,3.3 报告和附注中公司名称未全部修改？怎么底稿编制人全部一次性签名？查找和替换功能的应用3.4 复核底稿时候，复核意见怎么在底稿上凸显？底稿中重要的说明怎么更加清晰明了的反映？批注的插入、显示、隐藏（审阅、审核的应用）,复核人员怎么快速知道哪些地方修订了呢？,3.5添加自己常用的工具栏小工具：表单的运用,3.6表格中重要的信息不想让其他人知道，怎么操作？ excel的隐藏功能3.6.1工作表隐藏,3.6.3单元格数据暂时消失小技巧方式一：将单元格数字格
16、式设置为“;”（三个半角的分号）；方式二：将单元格的背景和字体颜色设置为相同的颜色以实现浑然一体的效果；缺陷：在全选工作表的时候，数据会显现,3.7图表功能步骤1：选中数据区域；步骤2：点击“插入”中的图表那栏,3.8整体数值除以万元 步骤1：选定一个空白单元格，填入数值10000，并复制步骤2：选定需要除以万元的区域举一反三,第四篇、其他功能,4.1链接和超链接的应用4.2对数据区的保护4.3打印区域的设置、排版4.4将EXCEL表格数据嵌入到word文档中4.5对输入内容的提示与输入错误的反馈,4.1一张表格需要引用链接到另外一张表格中去，怎么办？ 引用链接和超链接的应用在同一个工作簿的工
17、作表引用 在一个工作表中引用其他文件,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的提示信息。这时候的信息出现在你选中单元格的时候。在标题上输入【亲！】在输入信息栏输入【请输入正确的数据】，点击确定完成。,谢谢大家！孙 剑,
