项目十二使用Excel公式和函数.ppt

项目十二使用Excel公式和函数 Excel强大的计算功能主要依赖于其公式和函数 利用公式和函数可以对表格中的数据进行各种计算和处理 从而提高我们在制作复杂表格时的工作效率及计算准确率 知识目标了解公式和函数的概念 以及公式中的运算符 掌握公式的创建 移动 复制与修改方法 了解审核公式的方法 以及公式中错误提示的含义 掌握函数的使用方法 掌握设置单元格引用的方法 能力目标能够使用公式对工作表数据进行计算操作 能够将公式复制或移动到工作表的其他地方 以便快捷计算工作表数据 能够正确地使用函数对工作表数据进行计算 分析和管理 能够在公式中正确设置单元格引用 本项目任务列表 任务一使用公式任务二使用函数任务三单元格引用 任务一使用公式 一 认识公式和函数二 公式中的运算符三 公式的创建 移动 复制与修改四 公式中的错误与审核 利用公式和函数可以对表格中的数据进行各种计算和处理 本任务中我们首先认识一下公式和函数 以及了解一下公式中的运算符 然后学习公式的创建 移动 符合和修改方法 最后了解一下审核公式的方法 一 认识公式和函数 要输入公式必需先输入 然后再在其后输入表达式 否则Excel会将输入的内容作为文本型数据处理 表达式由运算符和参与运算的操作数组成 运算符可以是算术运算符 比较运算符 文本运算符和引用运算符 操作数可以是常量 单元格引用和函数等 函数是预先定义好的表达式 它必须包含在公式中 每个函数都由函数名和参数组成 其中函数名表示将执行的操作 如求平均值函数AVERAGE 参数表示函数将作用的值的单元格地址 通常是一个单元格区域 如A2 B7单元格区域 也可以是更为复杂的内容 在公式中合理地使用函数 可以完成诸如求和 逻辑判断和财务分析等众多数据处理功能 二 公式中的运算符 运算符是用来对公式中的元素进行运算而规定的特殊符号 Excel包含4种类型的运算符 算术运算符 比较运算符 文本运算符和引用运算符 算术运算符算术运算符有6个 其作用是完成基本的数学运算 并产生数字结果 比较运算符比较运算符有6个 它们的作用是比较两个值 并得出一个逻辑值 即 TRUE 真 或 FALSE 假 文本运算符使用文本运算符 与号 可将两个或多个文本值串起来产生一个连续的文本值 例如 输入 祝你 快乐 开心 会生成 祝你快乐 开心 引用运算符引用运算符有3个 它们的作用是将单元格区域进行合并计算 三 公式的创建 移动 复制与修改 1 创建公式 要创建公式 可以直接在单元格中输入 也可以在编辑栏中输入 输入方法与输入普通数据相似 也可在输入等号后单击要引用的单元格 然后输入运算符 再单击要引用的单元格 引用的单元格周围会出现不同颜色的边框线 它与单元格地址的颜色一致 便于用户查看 2 移动与复制公式 移动和复制公式的操作与移动 复制单元格内容的操作方法是一样的 所不同的是 移动公式时 公式内的单元格引用不会更改 而复制公式时 单元格引用会根据所用引用类型而变化 即系统会自动改变公式中引用的单元格地址 利用填充柄复制公式 3 修改或删除公式 要修改公式 可单击含有公式的单元格 然后在编辑栏中进行修改 或双击单元格后直接在单元格中进行修改 修改完毕按 Enter 键确认 删除公式是指将单元格中应用的公式删除 而保留公式的运算结果 四 公式中的错误与审核 1 公式错误代码 在使用公式时 经常会遇到公式的返回值为一段代码的情况 如 VALUE 等 了解这些代码的含义 用户就可以知道在公式使用过程中出现了什么样的错误 下面是一些常见的错误代码产生的原因 输入到单元格中的数值或公式计算结果太长 单元格容纳不下 增加单元格宽度可以解决这个问题 另外 日期运算结果为负值也会出现这种情况 此时可以改变单元格的格式 比如改为文本格式 DIV 0 除数引用了零值单元格或空单元格 N A 公式中没有可用数值 或缺少函数参数 NAME 公式中引用了无法识别的名称 或删除了公式正在使用的名称 例如 函数的名称拼写错误 使用了没有被定义的区域或单元格名称 引用文本时没有加引号等 NULL 使用了不正确的区域运算符或引用的单元格区域的交集为空 例如 输入公式 A1 B4C1 D4 因为这两个单元格区域交集为空 所以回车后返回值为 NULL NUM 公式产生的结果数字太大或太小 Excel无法表示出来 例如 输入公式 10 309 由于运算结果太大 公式返回错误 或在需要数字参数的函数中使用了无法接受的参数 例如 在输入开平方的公式 SQRT 时 引用了负值的单元格或直接使用了负值 RFF 公式引用的单元格被删除 并且系统无法自动调整 或链接的数据不可用 VALUE 当公式需要数字或逻辑值时 却输入了文本 为需要单个值 而不是区域 的运算符或函数提供了区域引用 输入数组公式后 没有按 Ctrl Shift Enter 组合键确认 2 公式审核 组 在使用公式的过程中 有时可能会因人为疏忽 或是表达式的设置错误 导致计算结果发生错误 使用Excel提供的审核功能可以方便地检查公式 分析数据流向和来源 纠正错误 把握公式和值的关联关系等 在 公式 选项卡上的 公式审核 组中可以看到审核按钮 3 查找与公式相关的单元格 追踪引用单元格 追踪从属单元格 4 追踪导致公式错误的单元格 任务实施 下面通过计算工作表中的数据 练习公式的使用方法 一 计算2011春季成绩表数据 拖动填充柄计算其他学生总分 打开素材文件 输入公式并利用填充柄复制公式进行计算 计算平均分 打开素材文件 输入公式并复制 计算出 证件统计表 中各年份的毕业生人数和证件的合计 输入公式并向下复制 二 计算毕业生取得证书数 输入公式并向右复制 任务二使用函数 一 常用函数讲解二 函数应用方法 本任务中 我们先了解一下常用函数的类型 然后学习函数的使用方法 一 常用函数讲解 二 函数应用方法 使用函数时 应首先确认已在单元格中输入了 号 即已进入公式编辑状态 接下来可输入函数名称 再紧跟着一对括号 括号内为一个或多个参数 参数之间要用逗号来分隔 用户可以在单元格中手工输入函数 也可以使用函数向导输入函数 1 直接输入函数 手工输入一般用于参数比较单一 简单的函数 即用户能记住函数的名称 参数等 此时可直接在单元格中输入函数 2 使用向导输入函数 如果不能确定函数的拼写或参数 可以使用函数向导输入函数 在工作表中选择要进行计算的单元格区域 任务实施 下面大家通过计算工作表中的数据 练习公式和函数的使用方法 一 计算彩电销售统计表数据 输入公式并向下复制 输入公式并向下复制 输入公式并向右复制 输入公式并向下复制 假设机试和笔试成绩都大于等于60 则在 考试结果 列的相应单元格中显示 合格 否则为 不合格 假设笔试或机试成绩小于60 则在 不及格科目 列的相应单元格显示科目名称 笔试 或 机试 机试和笔试成绩均大于等于60 则显示 无 下面利用If和AND函数计算 考绩情况表 中的 考试结果 和 不及格科目 项 二 分析考试结果数据 输入公式并向下复制 输入公式并向下复制 三 计算工资表数据 从2011年9月1日起 个人所得税是月收入超过3500起征 个人所得税 税前工资 总工资 社保 免征额 税率 速算扣除数其中税率如下表所示 下面利用公式和函数计算出 工资表 中的 应发合计 个人所得税 扣款合计 和 实发合计 值 输入公式并向下复制计算 应发合计 输入公式并向下复制 计算 个人所得税 输入公式并向下复制 计算 扣款合计 输入公式并向下复制 计算 实发工资 四 计算成绩表数据并设置级别 下面计算成绩表中的平均分 然后以 平均分 为依据判断成绩的优 良 中与不及格 拖动填充柄向下复制公式 输入公式并向下复制 任务三单元格引用 一 相同或不同工作簿 工作表中的引用二 相对引用 绝对引用和混合引用 引用的作用是通过标识工作表中的单元格或单元格区域 来指明公式中所使用的数据的位置 通过单元格的引用 可以在一个公式中使用工作表不同部分的数据 或者在多个公式中使用一个单元格中的数据 还可以引用同一个工作簿中不同工作表中的单元格 甚至还可以引用不同工作簿中的数据 当公式中引用的单元格数值发生变化时 公式会自动更新其所在单元格内容 即更新其计算结果 一 相同或不同工作簿 工作表中的引用 引用不同工作表间的单元格在同一工作簿中 不同工作表中的单元格可以相互引用 它的表示方法为 工作表名称 单元格或单元格区域地址 如 Sheet2 F8 F16引用不同工作簿中的单元格在当前工作表中引用不同工作簿中的单元格的表示方法为 工作簿名称 xlsx 工作表名称 单元格 或单元格区域 地址 二 相对引用 绝对引用和混合引用 Excel提供了相对引用 绝对引用和混合引用三种引用类型 用户可以根据实际情况选择引用的类型 1 相对引用 相对引用指的是单元格的相对地址 其引用形式为直接用列标和行号表示单元格 例如B5 或用引用运算符表示单元格区域 如B5 D15 如果公式所在单元格的位置改变 引用也随之改变 默认情况下 公式使用相对引用 如前面讲解的复制公式就是如此 引用单元格区域时 应先输入单元格区域起始位置的单元格地址 然后输入引用运算符 再输入单元格区域结束位置的单元格地址 2 绝对引用 绝对引用是指引用单元格的精确地址 与包含公式的单元格位置无关 其引用形式为在列标和行号的前面都加上 符号 不论将公式复制或移动到什么位置 引用的单元格地址的行和列都不会改变 向下复制输入公式时 绝对引用不变 引用中既包含绝对引用又包含相对引用的称为混合引用 如A 1或 A1等 用于表示列变行不变或列不变行变的引用 如果公式所在单元格的位置改变 则相对引用改变 而绝对引用不变 编辑公式时 输入单元格地址后 按 F4 键可在绝对引用 相对引用和混合引用之间切换 3 混合引用 任务实施 下面分别计算表格和某超市商品促销折算表中的数据 练习单元格引用在实际工作中的用法 一 使用公式计算表格数据 根据F