EXCEL在审计中的运用.ppt

天健会计师事务所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 1sumif的运用公式 SUMIF range criteria sum range range为用于条件判断的单元格区域 criteria为确定哪些单元格将被相加求和的条件 其形式可以为数字 表达式或文本 sum range是需要求和的实际单元格 使用技巧 criteria 条件可以表示为32 32 32 或 apples 条件还可以使用通配符 问号 和星号 如需要求和的条件为第二个数字为2的 可表示为 2 从而简化公式设置 问号匹配任意单个字符 星号匹配任意一串字符 如果要查找实际的问号或星号 请在该字符前键入波形符 countif的运用 1 4 2countif的运用 计算个数公式 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 年数总和法年折旧率 预计使用年限 已使用年限 预计使用年限 预计使用年限 1 2 100 首先 应该思考 计算当期折旧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月 期末时点已全部提满折旧 与折旧相关的函数 平均年限法案例 公式 本期折旧额 1 残值率 本年折旧月数 原值 预计使用月数 100 双倍余额递减法 思路 第一步 计算每项固定资产的在各个期间的折旧第二步 判断本年应该取得相应月数对应的折旧 1 5 2货币时间价值函数 融资租赁函数的运用 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工作表标签颜色 显示 隐藏P323 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的提示信息 这时候的信息出现在你选中单元格的时候 在标题上输入 亲 在输入信息栏输入 请输入正确的数据 点击确定完成 谢谢大家 孙剑