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