电子表格公式与函数的使用课件.ppt

1、公式与函数的使用公式与函数的使用 Excel强大的计算功能主要依赖于强大的计算功能主要依赖于公式和函数，利用公式和函数可以对公式和函数，利用公式和函数可以对表格中的数据进行各种计算和处理操表格中的数据进行各种计算和处理操作，从而提高我们在制作复杂表格时作，从而提高我们在制作复杂表格时的工作效率及计算准确率。的工作效率及计算准确率。本章内容提要本章内容提要 v使用公式v公式中的引用设置v公式中的错误和审核v使用函数v数组公式及其应用 5.1 使用公式使用公式 公式是对工作表中数据进行计算的表达式。利用公式可对同一工作表的各单元格、同一工作簿中不同工作表的单元格，以及不同工作簿的工作表中单元格的数

2、值进行加、减、乘、除、乘方等各种运算。要输入公式必需先输入“=”，然后再在后面输入表达式，否则Excel会将输入的内容作为文本型数据处理。表达式由运算符和参与运算的操作数组成。运算符可以是算术运算符、比较运算符、文本运算符和引用运算符；操作数可以是常量、单元格引用和函数等 5.1.1 公式中的运算符公式中的运算符 1算术运算符算术运算符 算术运算符的作用是完成基本的数学运算，并产生数字结果。2比较运算符比较运算符 比较运算符的作用是比较两个值，并得出一个逻辑值，即“TRUE（真）”或“FALSE（假）”。3文本运算符文本运算符 使用文本运算符“”（与号）可将两个或多个文本值串起来产生一个连续的

3、文本值。例如：输入“祝你”“快乐、开心！”会生成“祝你快乐、开心！”。4引用运算符引用运算符 引用运算符的作用是将单元格区域进行合并计算。5.1.2 运算符的优先级运算符的优先级 通常情况下，如果公式中只用了一种运算符，Excel会根据运算符的特定顺序从左到右计算公式；如果公式中同时用到了多个运算符，Excel将按一定优先级由高到低进行运算。另外，相同优先级的运算符，将从左到右进行计算。5.1.3 创建和编辑公式创建和编辑公式 1创建公式创建公式 要创建公式，可以直接在单元格中输入，也可以在编辑栏中输入，输入方法与输入普通数据相似。2修改和删除公式修改和删除公式 u修改公式修改公式 单击含有公

4、式的单元格，然后在编辑栏中进行修改，或双击单元格后直接在单元格中进行修改，修改完毕按【Enter】键确认。u删除公式删除公式 删除公式是指将单元格中应用的公式删除，而保留公式的运算结果。5.1.4 移动和复制公式移动和复制公式 1移动公式移动公式 要移动公式，最简单的方法就是：选中包含公式的单元格，将鼠标指针移到单元格的边框线上，当鼠标指针变成十字箭头形状 时，按住鼠标左键不放，将其拖到目标单元格后释放鼠标即可。此外，或者选中要移动公式的单元格，按【Ctrl+X】组合键或单击“开始”选项卡上“剪贴板”组中的“剪切”按钮 ，再单击目标单元格，然后按【Ctrl+V】组合键或单击“剪贴板”组中的“粘

5、贴”按钮 ，也可移动公式。2复制公式复制公式 在Excel中，复制公式可以使用填充柄，也可以使用复制、粘贴命令。在复制公式的过程中，一般情况下系统会自动地改变公式中引用的单元格地址。5.2 公式中的引用设置公式中的引用设置 5.2.1 相对引用、绝对引用和混合引用相对引用、绝对引用和混合引用 1相对引用相对引用 相对引用指的是单元格的相对地址，其引用形式为直接用列标和行号表示单元格，或用引用运算符表示单元格区域。如果公式所在单元格的位置改变，引用也随之改变。默认情况下，公式使用相对引用。复制公式，相对引用发生改变2绝对引用绝对引用 绝对引用是指引用单元格的精确地址，与包含公式的单元格位置无关，

6、其引用形式为在列标和行号的前面都加上“$”号。复制公式，绝对引用不变3混合引用混合引用 引用中既包含绝对引用又包含相对引用的称为混合引用，如A$1或$A1等，用于表示列变行不变或列不变行变的引用。如果公式所在单元格的位置改变，则相对引用改变，而绝对引用不变。5.2.2 引用不同工作表间的单元格引用不同工作表间的单元格 在同一工作簿中，不同工作表中的单元格可以相互引用，它的表示方法为：“工作表名称!单元格或单元格区域地址”。如：Sheet2!F8:F16 两张不同的工作表引用“Sheet2”工作表中C4单元格内容5.2.3 引用不同工作簿间的单元格引用不同工作簿间的单元格 在当前工作表中引用不同

7、工作簿中的单元格的表示方法为：工作簿名称.xlsx工作表名称！单元格（或单元格区域）地址 5.3 公式中的错误和审核公式中的错误和审核 5.3.1 公式中返回的错误代码含义及解决方法公式中返回的错误代码含义及解决方法（1）错误值：）错误值：含义：输入到单元格中的数据太长或公式产生的结果太长，单元格容纳不下；也可能是对日期和时间格式的单元格做减法，出现了负值。解决办法：增加列的宽度，使结果能够完全显示。如果是由日期或时间相减产生了负值引起的，可以改变单元格的格式，比如改为文本格式。（2）错误值：）错误值：DIV/0!含义：试图除以0。这个错误的产生通常有两种情况：除数为0；在公式中除数使用了空单

8、元格或是包含零值单元格的单元格引用。解决办法：修改单元格引用，或者在用作除数的单元格中输入不为零的值。（3）错误值：）错误值：VALUE!含义：输入引用文本项的数学公式。如果使用了不正确的参数或运算符，或者当执行自动更正公式功能时不能更正公式，都将产生错误信息VALUE!。解决办法：应确认公式或函数所需的运算符或参数正确，并且公式引用的单元格中包含有效的数值。（4）错误值：）错误值：REF!含义：删除了被公式引用的单元格范围。解决办法：恢复被引用的单元格范围，或是重新设定引用范围。（5）错误值：）错误值：N/A 含义：无信息可用于所要执行的计算。在建立模型时，用户可以在单元格中输入#N/A，以

9、表明正在等待数据。任何引用含有#N/A值的单元格都将返回#N/A。解决办法：在等待数据的单元格内填上数据。（6）错误值：）错误值：NUM!含义：提供了无效的参数给工作表函数，或是公式的结果太大或太小而无法在工作表中表示。解决办法：确认函数中使用的参数类型正确。如果是公式结果太大或太小，就要修改公式。（7）错误值：）错误值：NAME?含义：在公式中使用了Excel不能识别的文本。例如，可能是输错了名称，或是输入了一个已删除的名称。另外，如果没有将文字串括在双引号中，也会产生此错误值。解决办法：如果是使用了不存在的名称而产生这类错误，应确认使用的名称确实存在；如果是函数名拼写错误，改正过来就可以了

10、；将文本串括在双引号中；确认公式中使用的所有区域引用都使用了冒号（:）。（8）错误值：）错误值：NULL！含义：在公式中的两个范围之间插入一个空格以表示交叉点，但这两个范围没有公共单元格。解决办法：改正区域运算符使之正确；更改引用使之相交。5.3.2 公式审核公式审核 1追踪引用单元格与从属单元格追踪引用单元格与从属单元格 引用单元格是指被其他单元格的公式引用的单元格；从属单元格是指该单元格中含有公式且公式中引用了其他单元格。引用单元格提供数据，而从属单元格使用数据。u追踪引用单元格追踪引用单元格 Excel可以追踪公式中的引用单元格（即指明所选单元格中的数据是由哪几个单元格中的数据通过公式计

11、算得出的），并以箭头的形式标识引用的单元格。u追踪从属单元格追踪从属单元格 单击一次“追踪从属单元格”按钮效果 单击两次“追踪从属单元格”按钮效果 2取消追踪取消追踪 移去追踪引用单元格箭头 移去从属单元格箭头 移去所有箭头 3显示公式显示公式 4错误检查错误检查 Excel会自动对输入的公式进行检查，当发生错误时，单元格的左上角会出现一个绿色的小三角 。此时选中含有错误值的单元格，然后单击“公式审核”组中的“错误检查”按钮右侧的三角按钮，在展开的列表中选择“追踪错误”项，即可标识出产生错误的单元格。5公式求值公式求值 通过单击“公式审核”组中的“公式求值”按钮，可在打开的对话框中按照公式的执

12、行顺序逐步观察公式的运算结果，从而找到错误的根源。6监视窗口监视窗口 使用公式对工作表中的数据进行运算时，可以使用Excel的“监视窗口”查看工作表中的公式和重要数据，及数据变化情况。它能将工作表中与编辑内容相关联的数据，以监视点的形式添加到“监视窗口”中，若更改工作表内容，“监视窗口”中关联的内容也会随之改变。添加监视点 监视窗口“值”栏中数值发生变化修改单元格中的数据5.4 使用函数使用函数 函数是预先定义好的表达式，它必须包含在公式中。每个函数都由函数名和参数组成，其中函数名表示将执行的操作，参数表示函数将作用的值的单元格地址，通常是一个单元格区域，也可以是更为复杂的内容。在公式中合理地

13、使用函数，可以完成诸如求和、逻辑判断和财务分析等众多数据处理功能。5.4.1 函数的分类函数的分类 5.4.2 函数的使用方法函数的使用方法 1手工输入函数手工输入函数 使用函数时，应首先确认已在单元格中输入了“=”号，即已进入公式编辑状态。接下来可输入函数名称，再紧跟着一对括号，括号内为一个或多个参数，参数之间要用逗号来分隔。用户可以在单元格中手工输入函数，也可以使用函数向导输入函数。如果用户能记住函数的名称、参数，可直接在单元格中输入函数。2使用函数向导输入函数使用函数向导输入函数 如果不能确定函数的拼写或参数，可以使用函数向导输入函数。5.5 数组公式及其应用数组公式及其应用 数组公式可

14、以对两组或两组以上的数据（两个或两个以上的单元格区域）同时进行计算。在数组公式中使用的数据称为数组参数，数组参数可以是一个区域数组，也可以是常量数组。5.5.1 数组公式的建立方法数组公式的建立方法 输入数组公式首先必须选择用来存放结果的单元格区域（也可以是一个单元格），然后输入公式，最后按【CtrlShiftEnter】组合键锁定数组公式，Excel将在公式两边自动加上花括号“”。1用数组公式计算两个数据区域的乘积用数组公式计算两个数据区域的乘积 当需要计算两个相同矩形区域对应单元格的数据之积时，可以用数组公式一次性计算出所有的乘积值，并保存在另一个大小相同的矩形区域中。2用数组公式计算多列

15、数据之和用数组公式计算多列数据之和 当需要把多个对应列或行的数据相加，并得出对应的和值所组成的一列或一行数据时，可以用一个数组公式完成。3用数组公式同时对多个数据区域进行相同的计算用数组公式同时对多个数据区域进行相同的计算 在公式中用数组作为参数时，所有的数组必须是同维的，即有相同的行和列。如果数组参数或数组区域的维数不匹配，Excel会自动扩展该参数。5.5.2 使用数组公式的规则使用数组公式的规则 u输入数组公式时，首先选择用来保存计算结果的单元格区域，如果计算公式将产生多个计算结果，必须选择一个与计算结果所需大小和形状都相同的单元格区域。u数组公式输入完成后，按下【Ctrl+Shift+

16、Enter】组合键，这时在公式编辑栏中可以看见公式的两边自动加上了花括号，表示该公式是一个数组公式。u在数组公式所涉及的区域中，不能编辑、清除或移动单个单元格，也不能插入或删除其中任何一个单元格，即数组公式所涉及的单元格区域只能作为一个整体进行操作。u要编辑或清除数组，需要选择整个数组并激活编辑栏，然后在编辑栏中修改数组公式或删除数组公式，操作完成后，按下【Ctrl+Shift+Enter】组合键即可。u要把数组公式移到另一个位置，需要先选中整个数组公式所在的范围，然后把整个区域拖放到目标位置，也可通过“编辑”“剪切”和“粘贴”菜单进行。综合实例综合实例统计月考成绩表统计月考成绩表 先用公式（

17、也可用数组公式）计算出总分，然后用数组公式计算二（1）班男、女生人数，双优和双及人数。先用公式（也可用数组公式）计算出总分，然后用数组公式计算二（1）班男、女生人数，双优和双及人数。计算总分 计算二（1）班男、女生数 计算二双优人数 计算二双及人数 本章小结本章小结 学完本章内容，用户应重点掌握以下知识。u掌握在单元格中输入公式及利用填充柄复制公式的方法。u掌握相对引用、绝对引用和混合引用的概念，以便在创建公式时正确设置单元格引用。u掌握使用“公式审核”组中的工具检验公式正确性的方法。u了解Excel提供给的常用函数类型，掌握使用直接输入法和“插入函数”按钮输入函数的方法。u掌握数组公式的创建方法、使用规则及其应用。公式和函数是Excel最基本、最重要的应用工具，是Excel的核心，因此，应对公式和函数熟练掌握，才能在实际应用中得心应手。