Excel在财务日常工作中的应用ppt课件

1 Excel在日常财务工作中的应用 2 3 4 提纲 5 第一部分基本操作技巧 6 当排序含有公式的数据列表时 应该谨慎行事 简单排序步骤 1 选择参与排序的数据区域 注 一般不包括字段名行 但包括所有列 2 单击工具栏中的升序或降序按钮 一 排序 练习 对 report xls 进行操作 对年龄进行排序 7 复杂排序步骤 1 选择参与排序的数据区域 2 数据 排序 3 选择关键字和排序方向 最多可以选择3个关键字段进行排序 一 排序 8 一 排序 9 筛选指只显示满足条件的数据记录 有自动筛选和高级筛选两种方法 操作步骤 1 光标位于数据列表上 2 数据 筛选 自动筛选 3 单击筛选按钮 取值或选取自定义后给出筛选条件 4 取消筛选 数据 筛选 自动筛选 二 筛选 自动筛选 筛选按钮 10 练习 利用自定义筛选方式选择年龄 40岁 姓张的观测 并将其存为新的Excel文件 二 筛选 自动筛选 11 二 筛选 高级筛选 高级筛选能够提供以下功能 1 可以指定更复杂的筛选条件 2 可以指定计算的筛选条件 3 可以从另一个地址中摘录适合条件的记录副本 操作 数据 筛选 高级筛选 步骤 一 为高级筛选建立条件区域 至少有两行组成 在第一行中必须包括包含有数据清单中的一些或全部字段名称 条件区域的另一行必须由筛选条件构成 二 在筛选过程中使用多个条件条件在同一行中 为 AND 操作 条件在不同行中 为 OR 操作 选择不重复的记录 会隐藏所有符合指定条件的行的副本 如果没有指定条件区域 此选项会隐藏数据列表中的所有重复的行的副本 12 二 筛选 高级筛选 使用计算条件进行筛选 条件区域为B1 B2 这个公式计算 发病数 列的平均值 并将它与 发病数 列的第一个单元格中的数据进行比较 注意 必须使用新的字段标题 而不使用列表中的字段标题 如果计算公式涉及到数据列表外的值 要使用绝对引用而不是相对引用 使用数据列表中首行数据创建计算条件公式 不是字段名称 使用相对引用而不是绝对引用 条件区域 13 二 筛选 14 操作 1 按某关键字段进行排序 分类 2 数据 分类汇总 3 对 分类汇总 对话框进行如下操作 分类字段 汇总方式 选定汇总项 可有多个 替换当前分类汇总 每组数据分页 不分页 汇总结果显示在数据下方 三 分类汇总 15 练习 对 exp3 xls 操作 求各年的发病数平均值 操作 1 先对数据按年份进行排序 2 数据 分类汇总 3 取消分类汇总 数据 分类汇总 选择 全部删除 三 分类汇总 16 四 行列转置 17 第二部分常用函数 18 常用函数 二 函数 19 VLOOKUP函数应用 帮忙寻找配对 只要两个表有共同的一列信息 比如姓名 身份证号码 就可以用VLOOKUP公式通过这个桥梁在一张表上寻找另一张表上的信息 Vlookup 第一个表共同信息列的单元格 第二个表的查找范围 第几列 false 日常应用于 对数 个人所得税申报 网银批量录入例 渠道的工资表和社保缴费表的校对 20 VLOOKUP函数应用 帮忙寻找配对 1 Vlookup是从左向右寻找信息 因此两个表共同的信息列一定要在寻找的信息的左方 比如上例姓名一定是在工资的左方 2 选中查找的范围一定要从第二个表的共同信息列开始选起 并加绝对引用 即单元格前加 符号3 第几列的定义是从第二个表的共同信息列数起 至寻找信息列止 21 第三部分数据透视表 22 数据透视表与数据透视图 有机地综合了前面介绍的排序 筛选 分类汇总 等数据分析的优点 可方便地调整分类汇总的方式 灵活地以多种不同方式展示数据的特征 计算均数标准差 分类汇总 建立列联表 计算百分比 建立新的数据子集 绘制统计图形等均可利用这一工具完成 因此 该工具是最常用 功能最全的Excel数据分析工具之一 掌握这一功能 可以帮助用户解决许多数据分析问题 Excel的数据透视表和数据透视图 23 Excel的数据透视表 数据透视表的创建 源数据如右边的截图所示 24 步骤一 在存放数据的工作表中 选取任一单元格 以便Excel自动确定用户想用的数据范围 单击 数据 数据透视表和数据透视图 弹出 数据透视表和数据透视图向导 对话框 Excel的数据透视表 数据透视表的创建 25 步骤二 单击 下一步 按钮 弹出如下截图 步骤三 确定Excel已正确识别数据的单元格区域 单击 下一步 按钮 可得下面截图 该步骤指定数据透视表显示的位置 本例按默认选定新建工作表 Excel的数据透视表 数据透视表的创建 26 步骤四 单击完成键 Excel自动创建一张新的工作表 在该工作表中显示了创建的数据透视表雏形 此外 数据透视表工具栏以及数据透视表字段列表也显示在工作表界面 Excel的数据透视表 数据透视表的创建 27 数据区 数据透视表的雏形中分位4个数据区 数据字段区 此处提示 请将数据项拖至此处 为了计数或求频数 一般将个体编号变量拖入此处 为了计算定量数据描述性统计量 如平均值 标准差等 应将定量数据 如年龄 身高 体重 发病数 拖入此处 行字段 区 此处提示 请将行字段拖至此处 该区域应拖入分类变量 该变量的分组类别排成一列 每个类别占一行 列字段 区 此处提示 请将列字段拖至此处 该区域也应拖入分类变量 该变量的分组类别排成一行 每个类别占一列 页字段 区 此处提示 请将页字段拖至此处 如果将分类变量放在此处 选择这一变量的某一类别 则产生一个数据透视表 选择这一变量的另一类别 则覆盖前次产生的表格 产生一个新的数据透视表 这样可查看在某一变量的不同值时 数据透视分析的结果 选取 全部 时 则忽略该变量的作用 这一区域的作用相当于统计上所说的分层 即拖入的变量分层后参看各层的数据分析结果 Excel的数据透视表 数据透视表的结构及其工具栏 28 数据透视表工具栏 设置报告格式 图表向导 隐含明细数据 显示明细数据 更新数据 汇总包含隐含项 始终显示项目 字段变量设置 显示字段列表 编辑与组织数据透视表创建了数据透视表雏形后 应根据分析要求 设置数据透视表的版式 该步骤也是数据透视表的最关键的一步 Excel的数据透视表 数据透视表的结构及其工具栏 29 例 查看2002年各月四种疾病的发病情况 Excel的数据透视表 数据透视表的结构及其工具栏 30 数据透视图 有两种方法可以创建数据透视图 方法一 在数据透视表的工具栏中单击 图表向导 图标 方法二 直接利用 数据透视表和数据透视图向导 对话框创建 这里我们用第一种方法 利用刚才创建的数据透视表创建数据透视图 31 Excel的数据透视图 创建数据透视图 32 如何消除缩位后的计算误差 微软Office技巧大赛获奖作品 有时我们输入的数字是小数点后两位数 但是在精度要求上只要一位 缩位后显示没问题 但其计算结果却是有误差的 解决方法是 单击 工具 选项 重新计算 选中 以显示精度为准 这样计算结果就没有误差了 事实上并不是计算上有误差 而是显示设置的四舍五入 采用本技巧提供的方法 可以解决显示中的问题 但同时会改变数值的精度 在使用前Excel会给你一个警告 实际运用总结如下 保留小数后2位数更加简单的方法 应用于涉及金额发放 社保缴纳等 1 选择所需设置的数据然后菜单栏先选格式 单元格 数值 小数位2位 2 在 工具 选项 在 重新计算 标签里勾选 以显示精度为准 这样做后合计数也没差值 其他数值也不用变 33 同时进行多个单元格的运算 微软Office技巧大赛获奖作品 如果我们现在有多个单元格的数据要和一个数据进行加减乘除运算 那么一个一个运算显然比较麻烦 其实利用 选择性粘贴 功能就可以实现同时运算 下面我们一起来看一个实例 我们要将C1 C4 C5 D3 E11单元格数据都加上25 那么可以这样做 首先在一个空白的单元格中输入25 选中这个单元格后点击鼠标右键选择 复制 然后按住Ctrl键依次点击C1 C4 C5 D3 E11单元格 将这些单元格选中 接下来点击鼠标右键选择 选择性粘贴 在 选择性粘贴 对话框中勾选 运算 框内的 加 选项 点击 确定 现在我们可以看到 这些单元格中的数据都同时被加上了25 34 快速选定Excel区域 微软Office技巧大赛获奖作品 在Excel中 要想在工作簿中快速选定某块区域 只需单击想选定的区域的左上角单元格 同时按住Shift键不放 再单击想选定的区域的右下角单元格即可 另外 按住Ctrl键再用鼠标可任意选定多个不相邻的区域 事实上选定区域和顺序无关 只需首先选定矩形区域的一个角的单元格 在按下Shift的同时单击对角单元格 35 快速进行单元格之间的切换 微软Office技巧大赛获奖作品 在Excel中 可以用以下方法实现在一个区域内的快速输入而不用鼠标来进行单元格之间的切换 方法如下 用鼠标圈定一定区域后 按Tab键可使目标单元格向后移 按 Shift Tab 可向前移 这样就可以在键盘上连续输入一组数据而不需用鼠标 从而提高输入速度 提示 事实上 采用此方法最大的好处是 在一行的最后一个单元格 继续按Tab 则可以转到下一行开始的单元格 在选定区域最后一行的最后一个单元格继续按Tab则会回到选定区域第一行第一个单元格 同样用Enter可以按列输入数据 36 隐藏单元格中的所有值 微软Office技巧大赛获奖作品 有时候 我们需要将单元格中所有值隐藏起来 这时你可以选择包含要隐藏值的单元格 单击 格式 单元格 命令 选择 数字 选项卡 在 分类 列表中选择 自定义 然后将 类型 框中已有的代码删除 键入 3个分号 即可 提示 你也许奇怪为什么会这样 其实单元格数字的自定义格式是由正数 负数 零和文本4个部分组成 这4个部分用3个分号分隔 哪个部分空 相应的内容就不会在单元格中显示 现在都空了 当然就都不显示了 37 彻底隐藏单元格 在很多时候我们会将一些行或列的内容隐藏起来不让别人查看 但是如果用户复制包含隐藏行或列的区域 这样隐藏的内容就会在粘贴的新的工作表中出现 降低了隐藏效果 如果要想避免这种情况 我们可以先选中要复制的区域 然后单击 编辑 定位 命令 在打开的对话框中点击 定位条件 按钮 在定位条件对话框中选中 可见单元格 选项 这样就可以避免上面所发生的情况了 38 使用自定义序列排序 微软Office技巧大赛获奖作品 在Excel排序对话框中选择主要关键字后单击选项 可以选择自定义序列作为排序次序 使排序方便快捷且更易于控制 顺便说一下 排序可以选择按列或按行 如果以