EXCEL财务运用技巧PPT课件.ppt

学习概要 了解EXCEL的基础及应用知识学习EXCEL的函数知识掌握EXCEL在会计信息系统中的应用 1 EXCEL认识 窗口界面标题栏菜单栏工具栏名称框及编辑栏工作表区域滚动条和按钮状态栏等 2 EXCEL认识 工作对象 工作簿工作表单元格单元格操作 按住ctrl或者shift可以同时及连续选定工作表和单元格 插入 合并 拆分单元格 3 录入技巧输入文本 不是数字 公式 日期 时间 逻辑值 可直接输入 如身份证号码作为文本输入 前面需要加上单引号 否则默认为数字型 输入特殊字符选择 插入 特殊符号 输入数字直接输入 一般忽略数字前面的正号 英文状态下的 作为小数点 4 输入日期和时间可以直接输入 同时在一个单元格输入日期和时间需要用空格隔开 也可以通过函数输入 或者按住ctrl 显示当天日期 按住ctrl shift 输入系统当天时间 分行显示文本 需要按住ALT键同时按回车键输入真分数时 可进行格式设置 也可输入 0 再输入空格和这个真分数 快速填充数据 利用填充柄 5 数据的编辑查找替换数据定义数据输入的有效性 应用一 下拉菜单输入的实现例1 直接自定义序列有时候我们在各列各行中都输入同样的几个值 比如说 输入学生的等级时我们只输入四个值 优秀 良好 合格 不合格 我们希望Excel2000单元格能够象下拉框一样 让输入者在下拉菜单中选择就可以实现输入 6 先选择要实现效果的行或列 再点击 数据 有效性 打开 数据有效性 对话框 选择 设置 选项卡 在 允许 下拉菜单中选择 序列 在 数据来源 中输入 优秀 良好 合格 不合格 注意要用英文输入状态下的逗号分隔 选上 忽略空值 和 提供下拉菜单 两个复选框 点击 输入信息 选项卡 选上 选定单元格显示输入信息 在 输入信息 中输入 请在这里选择 7 例2 利用表内数据作为序列源 有时候序列值较多 直接在表内打印区域外把序列定义好 然后引用 先在同一工作表内的打印区域外要定义序列填好 假设在在Z1 Z8 如 单亲家庭 残疾家庭 残疾学生 特困 低收人 突发事件 孤儿 军烈属 等 然后选择要实现效果的列 资助原因 点击 数据 有效性 打开 数据有效性 对话框 选择 设置 选项卡 在 允许 下拉菜单中选择 序列 来源 栏点击右侧的展开按钮 有一个红箭头 用鼠标拖动滚动条 选中序列区域Z1 Z8 如果记得 可以直接输入 Z 1 Z 8 选上 忽略空值 和 提供下拉菜单 两个复选框 点击 输入信息 选项卡 选上 选定单元格显示输入信息 在 输入信息 中输入 请在这里选择 8 例3 横跨两个工作表来制作下拉菜单用INDIRECT函数实现跨工作表在例2中 选择来源一步把输入 Z 1 Z 8换成 INDIRECT 表二 Z 1 Z 8 就可实现横跨两个工作表来制作下拉菜单 总账中可以应用在哪个方面 凭证中科目的引用 9 应用三 数据唯一性检验员工的身份证号码应该是唯一的 为了防止重复输入 可以用 数据有效性 来提示 选中需要建立输入身份证号码的单元格区域 如B2至B14列 执行 数据 有效性 命令 打开 数据有效性 对话框 在 设置 标签下 按 允许 右侧的下拉按钮 在随后弹出的快捷菜单中 选择 自定义 选项 然后在下面 公式 方框中输入公式 COUNTIF B B B2 1 确定返回 以后在上述单元格中输入了重复的身份证号码时 系统会弹出提示对话框 并拒绝接受输入的号码 10 单元格引用与数据链接引用 相对引用 绝对引用和混合引用链接 同一工作簿不同工作表间链接 sheet1 A5不同工作簿的工作表间链接 工作簿1 xls sheet1 A5 11 高级应用数据排序和筛选 方法 步骤首先我们选中相关内容后 在数据中点击筛选 点击自动筛选 然后我们可以看到 每行都会出现三角形选项按键 点击 会依次显出来升序排列 将序排列 全部 自定义 前十个等 12 13 之后自己可以根据不同需要选择不同选项 其中自定义选项又另有乾坤 点中后会弹出一对话框 名为自定义自动筛选方式 第一行即为显示行 接着是两个输入框 其中第一个输入框又为选项框 分等于 不等于 大于 大于或等于 小与 小于或等于 始于 并非起始于 止于 并非结束与 包含 不包含 自己可根据自己所需的关系函数进行选择 14 15 16 EXCEL函数与公式 求和 17 EXCEL函数与公式 函数选择 平均数函数见上表 18 SUM和SUBTOTAL函数 企业数据分析中不可缺少的要用到求和公式 大家所熟知的便是SUM函数 但是sum函数并非求和的万能式 19 上述表格中小计和合计的部分已经用sum公式求出结果了 下面大家尝试一下将A组小计那一行删掉 结果会怎么样 20 21 总计一栏中出现了 REF 的字样 在Excel中出现这个字样的时候说明其公式参照的单元格不存在 这是sum求和公式一个弱点 另外 总计中的公式是一个一个选择相加的 现在只有A B C D四组数量不多所以在求总计的时候还没什么感觉 但是当小计超过20个甚至100个的时候 一个一个相加显然不是明智之举 此时subtotal函数将显示出重要的作用 下面我们来简单学习一下subtotal函数subtotal函数是一种分类汇总函数 不仅仅是简单的求和公式 其公式构造如下 SUBTOTAL function num ref1 ref2 Function num为1到11 包含隐藏值 或101到111 忽略隐藏值 之间的数字 指定使用何种函数在列表中进行分类汇总计算 22 Function num 包含隐藏值 为1到11之间的自然数 用来指定分类汇总计算使用的函数1 AVERAGE2 2COUNT3 3COUNTA4 MAX5 MIN6 PRODUCT7 STDEV8 STDEVP9 SUM10 VAR11 VARP 23 Function num 忽略隐藏值 函数101AVERAGE102COUNT103COUNTA104MAX105MIN106PRODUCT107STDEV108STDEVP109SUM110VAR111VARP 24 当function num为从1到11的常数时 SUBTOTAL函数将包括通过 格式 菜单的 行 子菜单下面的 隐藏 命令所隐藏的行中的值 当您要分类汇总列表中的隐藏和非隐藏值时 请使用这些常数 当function num为从101到111的常数时 SUBTOTAL函数将忽略通过 格式 菜单的 行 子菜单下面的 隐藏 命令所隐藏的行中的值 当您只分类汇总列表中的非隐藏数字时 使用这些常数 25 26 通过上述剖析图可以看出来 小计中的公式没什么太大区别 但是总计中是直接选择了所有的区域 红色框框 而并非一个一个进行的选择 27 28 上面简单的将两个求和函数sum和subtotal进行了比较 下面还有几点希望大家注意一下 习惯用subtotal函数的人一般在使用subtotal函数单元格的上面一行保持一行空行 为了方便以后添加 并且函数自动相加 前提是在最初设置subtotal函数的时候就把那行空行包含在内并且以后每次添加都是在空行的上面添加行 这样就不用每次添加后对函数再进行调整 很多数据分析的时候sum和subtotal是结合使用的 单调的使用任何一种都有可能耗费操作者的时间 29 自行设定公式选中单元格中输入 号 然后进行加减乘除四则运算的编写如 D7单元格的值是D4 D5的和减去D6 设置如下 在D7单元格中输入 D4 D5 D6 30 SUMIF函数 sumif函数的用法是根据指定条件对若干单元格 区域或引用求和 sumif函数语法是 SUMIF range criteria sum range sumif函数的参数如下 第一个参数 Range为条件区域 用于条件判断的单元格区域 第二个参数 Criteria是求和条件 由数字 逻辑表达式等组成的判定条件 第三个参数 Sum range为实际求和区域 需要求和的单元格 区域或引用 当省略第三个参数时 则条件区域就是实际求和区域 criteria参数中使用通配符 包括问号 和星号 问号匹配任意单个字符 星号匹配任意一串字符 如果要查找实际的问号或星号 请在该字符前键入波形符 31 选中F3单元格 输入公式 SUMIF B2 B19 E3 C2 C19 输入公式后 按键盘上ctrl shift enter组合键 32 2020 1 27 33 IF函数IF 逻辑表达式 成立时的返还值 不成立时的返还值 注意 一般可以嵌套使用 即 IF 逻辑表达式 成立时的返还值 IF 逻辑表达式 成立时的返还值 不成立时的返还值 34 数据透视 数据透视 功能能够将筛选 排序和分类汇总等操作依次完成 并生成汇总表格 是Excel强大数据处理能力的具体体现 数据透视是一种可以快速汇总大量数据的交互式方法 使用数据透视表可以深入分析数值数据 并且可以回答一些预计不到的数据问题 35 处理数据 假设存在一个包含销售数字的Excel工作表 它展示了数千行有关两个国家 地区的销售人员及其每天销售量的数据 有大量数据需要处理 这些数据逐行列出并分成多列 您如何从工作表中获取信息并了解所有数据的意义 您可以使用数据透视表 它们能够将数据转换为短小简洁的报表 并准确地告诉您需要了解的内容 36 查看源数据 在开始使用透视数据表之前 观察一下Excel工作表 以确保它为报表做好了准备 当创建数据透视表时 源数据的每列都将成为一个字段并且可在报表中使用 字段概括了源数据中的多行信息 37 查看源数据 报表字段的的名称来自源数据中的列标题 因此 确保具有源数据中工作表第一行各列的名称 标题下的其余各行应该包含同一列中的类似项 例如 文本应该位于一列 数字在另一列 而日期又在另一列 换句话说 包含数字的列不应该包含文本 等等 38 查看源数据 最后 要用于数据透视表的数据应该不包含空列 最好不要包含空行 例如 应该删除用于将数据块相互隔开的空白行 39 开始 此处介绍如何开始使用数据透视表 使用此处显示的 创建数据透视表 对话框 准备好数据后 请单击数据中的任意位置 在 插入 选项卡上的 表 组中 单击 数据透视表 然后再次单击 数据透视表 将打开 创建数据透视表 对话框 40 开始 此处介绍如何开始使用数据透视表 可以使用此处显示的 创建数据透视表 对话框 已选中 选择一个表或区域 选项 表 区域 框显示所选数据的范围 该范围可根据需要进行修改 单击 确定 41 这是在关闭 创建数据透视表 对话框后 在新工作表中看到的内容 其中一侧是为数据透视表准备的布局区域 另一侧是 数据透视表字段列表 该列表显示来自源数据的列标题 如前所述 每个标题都是一个字段 国家 地区 销售人员 等 42 通过将 数据透视表字段列表 中显示的任何字段移动到布局区域 可创建数据透视表 为此 请选中字段名称旁边的复选框 或者右键单击字段名称 然后选择要将该字段移动到的位置 43 构建数据透视表 现在已做好构建数据透视表的准备 为报表选择的字段取决于要了解的内容 第一个问题 每人的销售量是多少 要得到此答案 需要有关销售人员及其销售量的数据 因此 请在 数据透视表字段列表 中 选中 销售人员 和 订购量 字段旁边的复选框 然后 Excel会将每个字段放置到默认的布局区域 动画 右键单击鼠标 然后单击 播放 44 按国家 地区查看销售量 现在 了解了每位销售人员的销售量 但源数据展示了销售人员在加拿大和美国这两个国家的相关数据 因此 可能要问的另一个问题是 每位销售人员在每个国家的销售量是多少 要得到答案 可以将 国家 地区 字段作为 报表筛选 添加到数据透视表中 可以使用报表筛选来重点关注报表数据的子集 通常是产品线 时间跨度或地理区域 45 通过将 国家 地区 字段用作报表筛选 可以查看加拿大或美国各自的报表 也可以查看这两个国家的总销售量 该动画说明如何将 国家 地区 字段添加为报表筛选 右键单击 数据透视表字段列表 中的 国家 地区 字段 然后单击 添加到报表筛选 并且从该处进行添加 动画 右键单击鼠标 然后单击 播放 46 按日期查看销售量 由于原始的源数据具有 订单日期 列信息 因此在 数据透视表字段列表 中有一个 订单日期 字段 这意味着可以按日期查找每位销售人员的销售量 动画 右键单击鼠标 然后单击 播放 47 对报表进行透视 虽然数据透视表已经回答了的问题 但是需要执行一些操作以便阅读整个报表 必须向下滚动页面才能查看所有数据 因此 可以对报表进行透视以获取其他更易于阅读的视图 对报表进行透视时 可以转置字段的垂直或水平视图 将行移动到列区域或将列移动到行区域 动画 右键单击鼠标 然后单击 播放 48 数据透视表在总账中的应用 49 VLOOKUP lookup value table array col index num range lookup Lookup value为需要在数据表第一列中进行查找的数值 Table array为需要在其中查找数据的数据表 使用对区域或区域名称的引用 col index num为table array中待返回的匹配值的列序号 col index num为1时 返回table array第一列的数值 col index num为2时 返回table array第二列的数值 以此类推 如果col index num小于1 函数返回错误值 VALUE 如果col index num大于table array的列数 函数返回错误值 REF Range lookup为一逻辑值 指明函数VLOOKUP查找时是精确匹配 还是近似匹配 如果为true或省略 则返回近似匹配值 也就是说 如果找不到精确匹配值 则返回小于lookup value的最大数值 如果range lookup为false或0 函数VLOOKUP将查找精确匹配值 如果找不到 则返回错误值 N A 50 科目汇总表 VLOOKUP B9 透视汇总表 A 7 B 16 2 0 需要在数据表中查找的值 B9需要在其中查找数据的数据表 透视汇总表 A 7 B 16table array中待返回的匹配值的列序号 2即返回第2列的值指明函数VLOOKUP查找时是精确匹配 还是近似匹配 0即精确匹配 51 MAX 函数MIN 函数MONTH 函数YEAR 函数DAY 函数取区域内最大值与最小值函数转换日期序列数中的月份 年份及第几日 52 两个日期间所差天数的计算 不精确采用 DAYS360 开始日期 结束日期 trueorfalse 系统默认每月30天 一年360天计算 false或者忽略为美国算法 true为欧洲算法 注 还有个函数DATEDIF 也是解决2日期差天数的 53 1 应纳税所得额 IF W5 3500 W5 3500 0 2 税率 IF X5 0 0 IF X5 1500 3 IF X5 4500 10 IF X5 9000 20 IF X5 35000 25 3 所得税 ROUND IF X5 0 X5 0 IF X5 1500 X5 3 IF X5 4500 X5 10 105 IF X5 9000 X5 20 555 IF X5 35000 X5 25 1005 2 工资计算 工资 XLS 54 固定资产折旧函数 1 直线折旧法 又被称为平均年限法 是根据固定资产的原值 预计净残值以及预计清理费用 按照预计使用年限平均计算折旧的一种方法 用直线折旧法计算的每个月份和年份的折旧数额都是相等的 其计算公式如下 年折旧率 固定资产 净残值 使用年限月折旧率 年折旧率 12月折旧额 固定资产原价 月折旧率 55 2 直接折旧函数 SLN函数函数格式 SLN cost salvage life 函数功能 用来返回某一项资产在一个期间中的线性折旧值 Cost 为资产原值 Salvage 为资产在折旧期末的价值 有时也称为资产残值 Life 为折旧期限 有时也称作资产的使用寿命 56 3 双倍余额递减法 双倍余额递减法是在不考虑固定资产残值的情况下 根据每期期初固定资产账面余额和双倍的直线法折旧率计算固定资产折旧的一种方法 此种计算方法以加速的比率计算折旧 折旧在第一阶段是最高的 在后继阶段中会减少 其计算公式为 年折旧率 2 预计的使用年限 100 年折旧额 固定资产原值 预计净残值 年折旧率月折旧率 年折旧率 12月折旧额 固定资产年初账面余额 月折旧率 57 4 双倍余额递减函数 DDB函数函数格式为 DDB cost salvage life period factor 函数功能 计算一项资产在给定的期间内的折旧值 Cost 为资产原值 Salvage 为资产在折旧期末的价值 有时也称为资产残值 此值可以是0 Life 为折旧期限 有时也称作资产的使用寿命 Period 为需要计算折旧值的期间 Period必须使用与life相同的单位 Factor 为余额递