Excel公式和函数的使用

《Excel公式和函数的使用》由会员分享，可在线阅读，更多相关《Excel公式和函数的使用（48页珍藏版）》请在金锄头文库上搜索。

1、第6章 公式和函数的使用6.1 使用公式 6.2 课堂练习计算月考成绩总分 6.3 公式中的引用设置 6.4 公式中的错误和审核 6.5 使用函数 6.6 课堂练习计算水、电费合计 金额6.1 使用公式 公式是对工作表中数据进行计算的表达式，函数是 Excel预先定义好的用来执行某些计算、分析功能的封装 好的表达式，即用户只需按要求为函数指定参数，就能 获得预期结果，而不必知道其内部是如何实现的。利用公式可对同一工作表的各单元格，同一工作簿 中不同工作表的单元格，甚至其他工作簿的工作表中单 元格的数值进行加、减、乘、除、乘方等各种运算。公式必须以等号“=”开头，后面跟表达式。表达式 由运算符和

2、参与运算的操作数组成。运算符可以是算术 运算符、比较操作符、文本连接符和引用操作符；操作 数可以是常量、单元格地址和函数等。6.1.1 6.1.1 公式中的运算符公式中的运算符 运算符是用来对公式中的元素进行运算而规定的特殊 符号。Excel 2007包含4种类型的运算符：算术运算符、 比较运算符、文本运算符和引用运算符。 1 1算术运算符算术运算符 算术运算符有6个，如下表所示，其作用是完成基本 的数学运算，并产生数字结果。 2 2比较运算符比较运算符 比较运算符有6个，如下表所示。它们的作用是可以 比较两个值，结果为一个逻辑值，不是“TRUE（真）”， 就是“FALSE（假）”。 3 3文

3、本运算符文本运算符 文本运算符只有1个，如下表所示，使用文本连接 符（）可加入或连接一个或更多字符串以产生一长文 本。例如：“2008年”“北京奥运会”就产生“2008年 北京奥运会”。 4 4引用运算符引用运算符 引用运算符有3个，如下表所示，可以将单元格区 域进行合并计算。 6.1.2 6.1.2 公式中的运算顺序公式中的运算顺序 通常情况下，Excel根 据公式中运算符的特定顺 序从左到右计算公式。如 果公式中同时用到了多个 运算符，Excel将按一定 的顺序（优先级由高到低 ）进行运算，如下表所示 。另外，相同优先级的运 算符，将从左到右进行计 算。若要更改求值的顺序 ，可以将公式中要

4、先计算 的部分用括号括起来。 6.1.3 6.1.3 创建和编辑公式创建和编辑公式 1 1创建公式创建公式 要创建公式，可以直接在单元格中输入，也可以在编 辑栏中输入。 u直接在单元格中输入 对对于简单简单 的公式，我们们可以直接在单单元格中输输入： 首先单击单击 需输输入公式的单单元格，接着输输入=（等号）， 然后输输入公式内容，最后单击编辑栏单击编辑栏 上的“输输入”按钮钮 ，或按【Enter】键结束。 u在编辑栏中输入 单击要输入公式的单元格，然后单击编辑栏，在编辑 栏中输入等号“=”，输入操作数和运算符，输入完毕，按 下【Enter】键或单击编辑栏上的“输入”按钮。 2 2编辑公式编辑

5、公式 要修改公式，可单击含有公式的单元格，然后在编辑栏中 进行修改，修改完毕按【Enter】键即可。要删除公式，可单 击含有公式的单元格，然后按【Delete】键，如下图所示。 6.1.4 6.1.4 移动和复制公式移动和复制公式 1 1移动公式移动公式 要移动动公式，最简单简单 的方法就是：选选中包含公式的 单单元格，将鼠标标指针针移到单单元格的边边框线线上，当鼠标标指 针变针变 成十字箭头头形状 时，按住鼠标左键不放，将其拖 到目标单元格后释放鼠标即可。 2 2复制公式复制公式 u使用“填充柄” 在Excel中，当我们想将某个单元格中的公式复制到同列 （行）中相邻的单元格时，可以通过拖动“

6、填充柄”来快速完 成。方法是：按住鼠标左键向下（也可以是上、左或右，据 实际情况而定）拖动要复制的公式的单元格右下角的填充柄 ，到目标位置后释放鼠标即可。 u利用“选择性粘贴” 复制含有公式的单元格（此单元格包含格式），然后 选择目标单元格，单击“粘贴”按钮下方的三角按钮，在展 开的列表中选择“选择性粘贴”项 ，在打开的对话框中选中 “公式”单选钮，然后单击“确定”即可。6.2 课堂练习计算学生成绩总分 下面通过制作如下图所示的第一次月考成绩表，来熟悉一 下公式的输入及使用填充柄复制公式的方法。操作步骤如下： 1 打开文件“第一次月考成绩”，然后单击I2单元格， 输入等号“=”，然后输入C2+

7、D2+E2+F2+G2+H2，单击 编辑栏上的“输入”按钮，得到第一个学生的成绩总分。 2 按住鼠标左键向下拖动I2单元格右下角的填充柄 ，至I13单元格后释放鼠标，得到其他学生的成绩总分 。最后将工作簿另存。 6.3 公式中的引用设置 引用的作用在于标识工作表中的单元格或单元 格区域，并指明公式中所使用的数据的位置。通过 单元格引用，可以在一个公式中使用工作表不同部 分的数据，或者在多个公式中使用同一个单元格的 数值，还可以引用同一个工作簿中不同工作表中的 单元格，甚至其他工作簿中的数据。当公式中引用 的单元格数值发生变化时，公式会自动更新单元格 的内容。 6.3.1 6.3.1 引用单元格

8、或单元格区域引用单元格或单元格区域 公式大都使用单元格地址引用一个或多个单元格。要在公式中引用单元格，只需单击要在其中输入公式 的单元格，接着输入等号（=），然后直接输入该单元格 地址或单击该单元格，最后按【Enter】键即可。若要引用单元格区域，则输入区域左上角单元格的地址 、冒号（：）和区域右下角单元格地址，例如：A2:E2表示 单元格A2到单元格E2的区域。下表列出了在公式中使用地址引用单元格或单元格区域 示例。 6.3.2 相对引用、绝对引用和混合引用 1 1相对引用相对引用 相对引用指的是单元格的相对地址，其引用形式为直接 用列标和行号表示单元格，例如A1。如果公式所在单元格 的位置

9、改变，引用也随之改变。默认情况下，公式使用相对 引用。 在复制公式时，Excel将自动调整复制公式的引用，以 便引用相对于当前公式位置的其他单元格。 2 2绝对引用绝对引用 绝对引用指的是单元格的精确地址，与包含公式的单元格位 置无关，其引用形式为在列标和行号的前面都加上“$”号。例如， 已知快乐书店三个分店同种书籍一天的销售量，现在要计算各分 店的销售总额，操作步骤如下图所示。含有绝对引 用的公式3 3混合引用混合引用 引用中既包含绝对引用又包含相对引用的称为混合 引用，如A$1或$A1等，用于表示列变行不变或列不变 行变的引用。如果公式所在单元格的位置改变，则相对引用改变 ，而绝对引用不变

10、。下图所示单元格D2中的公式为： =$B2*$C2，当将其复制到D3单元格中时，公式变为 ：=$B3*$C3。 6.3.3 6.3.3 引用不同工作表中的单元格或单元格区域引用不同工作表中的单元格或单元格区域 在同一工作簿中，不同工作表中的单元格可以相互引 用，它的表示方法为：“工作表名称!单元格地址”。如： Sheet3!E6 同一工作 簿的两张 工作表Sheet1工作表 的B2单元格引 用了Sheet2工 作表中的C4单 元格 输入工作表名称 和单元格地址按Enter键6.3.4 6.3.4 不同工作簿间单元格的引用不同工作簿间单元格的引用 除了可以在同一工作表、同一工作簿的不同工作表中引

11、用单元格外，还 可以在当前工作表中引用不同工作簿中的单元格，它的表示方法为： 工作簿名称.xlsx工作表名称！单元格地址两张样 表工作 表这样比较两年 的总收入情况 就方便了选择单元格 后输入等号单击工作表标签 后单击单元格按Enter键6.4 公式中的错误和审核 6.4.1 6.4.1 公式中返回的错误值公式中返回的错误值 （1）错误值：含义：输入到单元格中的数据太长或单元格公式所产生 的结果太大，使结果在单元格中显示不下，或是对日期和时 间格式的单元格做减法，出现了负值。解决办法：增加列的宽度，使结果能够完全显示。如果 是由日期或时间相减产生了负值引起的，可以改变单元格的 格式，比如改为文

12、本格式。（3）错误值：VALUE!含义：输入引用文本项的数学公式。如果使用了不正确的 参数或运算符，或者当执行自动更正公式功能时不能更正公式 ，都将产生错误信息VALUE!。解决办法：应确认公式或函数所需的运算符或参数正确， 并且公式引用的单元格中包含有效的数值。例如，单元格C4 中有一个数字或逻辑值，而单元格D4包含文本，则在计算公 式=C4D4时，系统不能将文本转换为正确的数据类型，因 而返回错误值VALUE!。（2）错误值：DIV/0!含义：试图除以0。这个错误的产生通常有下面几种情况 ：除数为0、在公式中除数使用了空单元格或是包含零值单元 格的单元格引用。解决办法：修改单元格引用，或者

13、在用作除数的单元格 中输入不为零的值。 （4）错误值：REF!含义：删除了被公式引用的单元格范围。解决办法：恢复被引用的单元格范围，或是重新设定引 用范围。（5）错误值：N/A含义：无信息可用于所要执行的计算。在建立模型时， 用户可以在单元格中输入#N/A，以表明正在等待数据。任 何引用含有#N/A值的单元格都将返回#N/A。解决办法：在等待数据的单元格内填上数据。（6）错误值：NUM!含义：提供了无效的参数给工作表函数，或是公式的结 果太大或太小而无法在工作表中表示。解决办法：确认函数中使用的参数类型正确。如果是公 式结果太大或太小，就要修改公式。 （7）错误值：NAME?含义：在公式中使用

14、了Excel不能识别的文本。例如，可能 是输错了名称，或是输入了一个已删除的名称。另外，如果没 有将文字串括在双引号中，也会产生此错误值。解决办法：如果是使用了不存在的名称而产生这类错误， 应确认使用的名称确实存在；如果是函数名拼写错误，改正过 来就可以了；将文字串括在双引号中；确认公式中使用的所有 区域引用都使用了冒号（:），如SUM（C1:C10）。 （8）错误值：NULL！含义：在公式中的两个范围之间插入一个空格以表示交 叉点，但这两个范围没有公共单元格。例如，输入 “=SUM(A1:A10 C1:C10)”，就会产生这种情况。解决办法：改正区域运算符使之正确；更改引用使之相 交。例如，

15、在两个区域之间用逗号隔开“=SUM(A1:A10， C1:C10)”。 6.4.2 公式审核 Excel 2007提供了公式审核功能，通过它可以将任意 单元格中的数据来源和计算结果显示出来，使用户明白计 算的方式。 1 1查找公式中引用的单元格查找公式中引用的单元格 要查找公式中引用的单元格，只需单击含有公式的单 元格，即可在编辑栏中显示引用的单元格。 2 2追踪为公式提供数据的单元格追踪为公式提供数据的单元格 要显示公式中引用的单元格，可单击含有公式的单元 格，然后单击“公式”选项卡上“公式审核”组中的“追踪引用 单元格”按钮 ，此时，公式所引用的单元格就会有追踪 箭头指向公式所在的单元格，

16、在代表数据流向的箭头上， 每一个引用的单元格上都会出现一个蓝色的圆点。 3 3追踪导致公式错误的单元格追踪导致公式错误的单元格 Excel会自动对输入的公式进行检查，当发生错误时， 单元格的左上角会出现一个绿色的小三角 ，单击该单元 格，会在该单元格左侧出现 ，按钮。单击 按钮，会弹 出快捷菜单，提供解决此错误的途径。 要追踪导致公式错误的单元格，可单击“公式审核”组 中的“错误检查”按钮 右侧的三角按钮 ，在展开的列表 中单击“追踪错误”项，即可标识出产生错误的单元格 。 4 4追踪产生循环引用的单元格追踪产生循环引用的单元格 当某个公式直接或间接引用了该公式所在的单元格时，称作循环 引用。当打开的工作簿中含有循环引用时，Excel会显示警告提示框。 当按【Enter】键时，会弹出如下左图所示的对话框。如果确定要进 行循