Excel公式的使用.ppt

第6章公式和函数的使用 6 1使用公式6 2课堂练习 计算月考成绩总分6 3公式中的引用设置6 4公式中的错误和审核6 5使用函数6 6课堂练习 计算水 电费合计金额 6 1使用公式 公式是对工作表中数据进行计算的表达式 函数是Excel预先定义好的用来执行某些计算 分析功能的封装好的表达式 即用户只需按要求为函数指定参数 就能获得预期结果 而不必知道其内部是如何实现的 利用公式可对同一工作表的各单元格 同一工作簿中不同工作表的单元格 甚至其他工作簿的工作表中单元格的数值进行加 减 乘 除 乘方等各种运算 公式必须以等号 开头 后面跟表达式 表达式由运算符和参与运算的操作数组成 运算符可以是算术运算符 比较操作符 文本连接符和引用操作符 操作数可以是常量 单元格地址和函数等 6 1 1公式中的运算符 运算符是用来对公式中的元素进行运算而规定的特殊符号 Excel2007包含4种类型的运算符 算术运算符 比较运算符 文本运算符和引用运算符 1 算术运算符 算术运算符有6个 如下表所示 其作用是完成基本的数学运算 并产生数字结果 2 比较运算符 比较运算符有6个 如下表所示 它们的作用是可以比较两个值 结果为一个逻辑值 不是 TRUE 真 就是 FALSE 假 3 文本运算符 文本运算符只有1个 如下表所示 使用文本连接符 可加入或连接一个或更多字符串以产生一长文本 例如 2008年 北京奥运会 就产生 2008年北京奥运会 4 引用运算符 引用运算符有3个 如下表所示 可以将单元格区域进行合并计算 6 1 2公式中的运算顺序 通常情况下 Excel根据公式中运算符的特定顺序从左到右计算公式 如果公式中同时用到了多个运算符 Excel将按一定的顺序 优先级由高到低 进行运算 如下表所示 另外 相同优先级的运算符 将从左到右进行计算 若要更改求值的顺序 可以将公式中要先计算的部分用括号括起来 6 1 3创建和编辑公式 1 创建公式 要创建公式 可以直接在单元格中输入 也可以在编辑栏中输入 直接在单元格中输入 对于简单的公式 我们可以直接在单元格中输入 首先单击需输入公式的单元格 接着输入 等号 然后输入公式内容 最后单击编辑栏上的 输入 按钮 或按 Enter 键结束 在编辑栏中输入 单击要输入公式的单元格 然后单击编辑栏 在编辑栏中输入等号 输入操作数和运算符 输入完毕 按下 Enter 键或单击编辑栏上的 输入 按钮 2 编辑公式 要修改公式 可单击含有公式的单元格 然后在编辑栏中进行修改 修改完毕按 Enter 键即可 要删除公式 可单击含有公式的单元格 然后按 Delete 键 如下图所示 6 1 4移动和复制公式 1 移动公式 要移动公式 最简单的方法就是 选中包含公式的单元格 将鼠标指针移到单元格的边框线上 当鼠标指针变成十字箭头形状时 按住鼠标左键不放 将其拖到目标单元格后释放鼠标即可 2 复制公式 使用 填充柄 在Excel中 当我们想将某个单元格中的公式复制到同列 行 中相邻的单元格时 可以通过拖动 填充柄 来快速完成 方法是 按住鼠标左键向下 也可以是上 左或右 据实际情况而定 拖动要复制的公式的单元格右下角的填充柄 到目标位置后释放鼠标即可 利用 选择性粘贴 复制含有公式的单元格 此单元格包含格式 然后选择目标单元格 单击 粘贴 按钮下方的三角按钮 在展开的列表中选择 选择性粘贴 项 在打开的对话框中选中 公式 单选钮 然后单击 确定 即可 6 2课堂练习 计算学生成绩总分 下面通过制作如下图所示的第一次月考成绩表 来熟悉一下公式的输入及使用填充柄复制公式的方法 操作步骤如下 1打开文件 第一次月考成绩 然后单击I2单元格 输入等号 然后输入C2 D2 E2 F2 G2 H2 单击编辑栏上的 输入 按钮 得到第一个学生的成绩总分 2按住鼠标左键向下拖动I2单元格右下角的填充柄 至I13单元格后释放鼠标 得到其他学生的成绩总分 最后将工作簿另存 6 3公式中的引用设置 引用的作用在于标识工作表中的单元格或单元格区域 并指明公式中所使用的数据的位置 通过单元格引用 可以在一个公式中使用工作表不同部分的数据 或者在多个公式中使用同一个单元格的数值 还可以引用同一个工作簿中不同工作表中的单元格 甚至其他工作簿中的数据 当公式中引用的单元格数值发生变化时 公式会自动更新单元格的内容 6 3 1引用单元格或单元格区域 公式大都使用单元格地址引用一个或多个单元格 要在公式中引用单元格 只需单击要在其中输入公式的单元格 接着输入等号 然后直接输入该单元格地址或单击该单元格 最后按 Enter 键即可 若要引用单元格区域 则输入区域左上角单元格的地址 冒号 和区域右下角单元格地址 例如 A2 E2表示单元格A2到单元格E2的区域 下表列出了在公式中使用地址引用单元格或单元格区域示例 6 3 2相对引用 绝对引用和混合引用 1 相对引用 相对引用指的是单元格的相对地址 其引用形式为直接用列标和行号表示单元格 例如A1 如果公式所在单元格的位置改变 引用也随之改变 默认情况下 公式使用相对引用 在复制公式时 Excel将自动调整复制公式的引用 以便引用相对于当前公式位置的其他单元格 2 绝对引用 绝对引用指的是单元格的精确地址 与包含公式的单元格位置无关 其引用形式为在列标和行号的前面都加上 号 例如 已知快乐书店三个分店同种书籍一天的销售量 现在要计算各分店的销售总额 操作步骤如下图所示 含有绝对引用的公式 3 混合引用 引用中既包含绝对引用又包含相对引用的称为混合引用 如A 1或 A1等 用于表示列变行不变或列不变行变的引用 如果公式所在单元格的位置改变 则相对引用改变 而绝对引用不变 下图所示单元格D2中的公式为 B2 C2 当将其复制到D3单元格中时 公式变为 B3 C3 6 3 3引用不同工作表中的单元格或单元格区域 在同一工作簿中 不同工作表中的单元格可以相互引用 它的表示方法为 工作表名称 单元格地址 如 Sheet3 E6 6 3 4不同工作簿间单元格的引用 除了可以在同一工作表 同一工作簿的不同工作表中引用单元格外 还可以在当前工作表中引用不同工作簿中的单元格 它的表示方法为 工作簿名称 xlsx 工作表名称 单元格地址 6 4公式中的错误和审核 6 4 1公式中返回的错误值 1 错误值 含义 输入到单元格中的数据太长或单元格公式所产生的结果太大 使结果在单元格中显示不下 或是对日期和时间格式的单元格做减法 出现了负值 解决办法 增加列的宽度 使结果能够完全显示 如果是由日期或时间相减产生了负值引起的 可以改变单元格的格式 比如改为文本格式 3 错误值 VALUE 含义 输入引用文本项的数学公式 如果使用了不正确的参数或运算符 或者当执行自动更正公式功能时不能更正公式 都将产生错误信息 VALUE 解决办法 应确认公式或函数所需的运算符或参数正确 并且公式引用的单元格中包含有效的数值 例如 单元格C4中有一个数字或逻辑值 而单元格D4包含文本 则在计算公式 C4 D4时 系统不能将文本转换为正确的数据类型 因而返回错误值 VALUE 2 错误值 DIV 0 含义 试图除以0 这个错误的产生通常有下面几种情况 除数为0 在公式中除数使用了空单元格或是包含零值单元格的单元格引用 解决办法 修改单元格引用 或者在用作除数的单元格中输入不为零的值 4 错误值 REF 含义 删除了被公式引用的单元格范围 解决办法 恢复被引用的单元格范围 或是重新设定引用范围 5 错误值 N A含义 无信息可用于所要执行的计算 在建立模型时 用户可以在单元格中输入 N A 以表明正在等待数据 任何引用含有 N A值的单元格都将返回 N A 解决办法 在等待数据的单元格内填上数据 6 错误值 NUM 含义 提供了无效的参数给工作表函数 或是公式的结果太大或太小而无法在工作表中表示 解决办法 确认函数中使用的参数类型正确 如果是公式结果太大或太小 就要修改公式 7 错误值 NAME 含义 在公式中使用了Excel不能识别的文本 例如 可能是输错了名称 或是输入了一个已删除的名称 另外 如果没有将文字串括在双引号中 也会产生此错误值 解决办法 如果是使用了不存在的名称而产生这类错误 应确认使用的名称确实存在 如果是函数名拼写错误 改正过来就可以了 将文字串括在双引号中 确认公式中使用的所有区域引用都使用了冒号 如SUM C1 C10 8 错误值 NULL 含义 在公式中的两个范围之间插入一个空格以表示交叉点 但这两个范围没有公共单元格 例如 输入 SUM A1 A10C1 C10 就会产生这种情况 解决办法 改正区域运算符使之正确 更改引用使之相交 例如 在两个区域之间用逗号隔开 SUM A1 A10 C1 C10 6 4 2公式审核 Excel2007提供了公式审核功能 通过它可以将任意单元格中的数据来源和计算结果显示出来 使用户明白计算的方式 1 查找公式中引用的单元格 要查找公式中引用的单元格 只需单击含有公式的单元格 即可在编辑栏中显示引用的单元格 2 追踪为公式提供数据的单元格 要显示公式中引用的单元格 可单击含有公式的单元格 然后单击 公式 选项卡上 公式审核 组中的 追踪引用单元格 按钮 此时 公式所引用的单元格就会有追踪箭头指向公式所在的单元格 在代表数据流向的箭头上 每一个引用的单元格上都会出现一个蓝色的圆点 3 追踪导致公式错误的单元格 Excel会自动对输入的公式进行检查 当发生错误时 单元格的左上角会出现一个绿色的小三角 单击该单元格 会在该单元格左侧出现 按钮 单击按钮 会弹出快捷菜单 提供解决此错误的途径 要追踪导致公式错误的单元格 可单击 公式审核 组中的 错误检查 按钮右侧的三角按钮 在展开的列表中单击 追踪错误 项 即可标识出产生错误的单元格 4 追踪产生循环引用的单元格 当某个公式直接或间接引用了该公式所在的单元格时 称作循环引用 当打开的工作簿中含有循环引用时 Excel会显示警告提示框 当按 Enter 键时 会弹出如下左图所示的对话框 如果确定要进行循环引用 则单击 确定 按钮 结果如下右图所示 此时 追踪从属单元格 列表中的 循环引用 项变为可用 并显示出循环引用的单元格 单元格A5中的值依赖于单元格A2 而A2单元格中的值又依赖于A5 它们之间形成了间接的循环引用 6 5使用函数 函数是预先定义好的表达式 它必须包含在公式中 每个函数都由函数名和变量组成 其中函数名表示将执行的操作 变量表示函数将作用的值的单元格地址 通常是一个单元格区域 也可以是更为复杂的内容 在公式中合理地使用函数 可以完成如求和 逻辑判断 财务分析等众多数据处理功能 6 5 1函数的分类 财务函数 可以进行一般的财务计算 例如 确定贷款的支付额 投资的未来值或净现值 以及债券或息票的价值 时间和日期函数 可以在公式中分析和处理日期值和时间值 数学和三角函数 可以处理简单和复杂的数学计算 统计函数 用于对数据进行统计分析 查找和引用函数 在工作表中查找特定的数值或引用的单元格 逻辑函数 可以进行真假值判断 或者进行复合检验 信息函数 用于确定存储在单元格中的数据的类型 工程函数 用于工程分析 多维数据集函数 主要用于返回多维数据集的重要性能指标 属性 层次结构中的成员或组等 数据库函数 分析工作表中的数值是否符合特定条件 文本函数 可以在公式中处理文字串 6 5 2函数的使用方法 使用函数时 应首先确认已在单元格中输入了 号 即已进入公式编辑状态 接下来可输入函数名称 再紧跟着一对括号 括号内为一个或多个参数 参数之间要用逗号来分隔 1 手工输入函数 如果用户能记住函数的名称 参数 可直接在单元格中输入函数 方法如下 单击要输入函数的单元格 然后依次输入等号 函数名 左括号 具体参数和右括号 最后单击编辑栏中的 输入 按钮或按 Enter 键 此时在输入函数的单元格中将显示公式的运算结果 2 使用函数向导 如果不能确定函数的拼写或参数 可以使用函数向导插入函数 下面使用 插入函数 来来统计 第一次月考成绩 成绩统计 工作表中 高等数学 成绩大于85的个数 各科目成绩大于等于90的个数 操作步骤如下 选择单元格后单击 插入函数 按钮 选择函数后单击 确定 按钮 输入第二个函数参数 选择第一个函数参数 输入第二个函数参数 在 公式 选项卡的 函数库 中 系统分类列出了各种函数 其中 单击各分类函数按钮可打开函数列表 将光标指向某个函数并稍等片刻 可查看函数帮助 如下图所示 要使用某个函数 只要单击选择该函数就可以了 6 5 3获取函数帮助 要获取某个函数的帮助 可先打开 插入函数 对话框 然后选择函数 最后单击对话框左下角的 有关该函数的帮助 链接 在打开的对话框中拖动滚动条进行查看即可 6 6课堂练习 计算水 电费合计金额 下面我们首先计算每个用户应付的电费和水费合计 然后再计算所有应收的合计金额 效果如下图所示 操作步骤如下 1打开素材文件 二月水电费 绝对引用 单击K5单元格 输入公式 sum F5 J5 然后按 Enter 键 2向下拖动K5单元格右下角的填充柄 复制公式 得到其他用户的费用合计 3单击K13单元格 然后单击 开始 选项卡上 编辑 组中的 求和 按钮 4显示求和的单元格区域 确认后按 Enter 键 即可得到所有应收的合计金额