1、第5章 公式与函数的使用,Excel强大的计算功能主要依赖于公式和函数，利用公式和函数可以对表格中的数据进行各种计算和处理操作，从而提高我们在制作复杂表格时的工作效率及计算准确率。,本章内容提要,使用公式 公式中的应用设置 公式中的错误和审核使用函数数组公式及其应用,5.1 使用公式,公式是工作表中用于对单元格数据进行各种运算的等式，它必须以等号“=”开头。一个完整的公式，通常由运算符和操作数组成。运算符可以是算术运算符、比较运算符、文本运算符和引用运算符；操作数可以是常量、单元格地址和函数等。,5.1.1 公式中的运算符,运算符是用来对公式中的元素进行运算而规定的特殊符号。,1算术运算符,算
2、术运算符的作用是完成基本的数学运算，并产生数字结果。,2比较运算符,比较运算符的作用是可以比较两个值，结果为一个逻辑值，不是“TRUE（真）”，就是“FALSE（假）”。,3文本运算符,使用文本运算符“”（与号）可将两个或多个文本值串起来产生一个连续的文本值。例如：输入“2010年”“上海世博会”会生成“2010年上海世博会”。,4引用运算符,引用运算符可以将单元格区域进行合并计算。,5.1.2 运算符的优先级,若要更改求值的顺序，可以将公式中要先计算的部分用括号括起来,5.1.3 创建和编辑公式,1创建公式,对于简单的公式，我们可以直接在单元格中输入：首先单击需输入公式的单元格，接着输入=（
3、等号），然后输入公式内容，最后单击编辑栏上的“输入”按钮 或按【Enter】键结束。,若要在编辑栏中输入公式，可单击要输入公式的单元格，然后单击编辑栏，依次在编辑栏中输入等号“=”，操作数和运算符，输入完毕，按下【Enter】键或单击编辑栏上的“输入”按钮 。,2编辑公式,要修改公式，可单击含有公式的单元格，然后在编辑栏中进行修改，修改完毕按【Enter】键即可。,5.1.4 移动和复制公式,1移动公式,要移动公式，最简单的方法就是：选中包含公式的单元格，将鼠标指针移到单元格的边框线上，当鼠标指针变成十字箭头 形状时，按住鼠标左键不放，将其拖到目标单元格后释放鼠标即可。,2复制公式,在Exce
4、l中，复制公式可以使用填充柄，也可以使用复制、粘贴命令。在复制公式的过程中，一般情况下，系统会自动地改变公式中引用的单元格地址。 当我们想将某个单元格中的公式复制到同列（行）中相邻的单元格时，可以通过拖动“填充柄”来快速完成。方法是：按住鼠标左键向下（也可以是上、左或右，据实际情况而定）拖动要复制公式的单元格右下角的填充柄，到目标位置后释放鼠标即可。,利用填充柄复制公式,5.1.5 自动求和功能,当对一行或一列数据进行求和时，首先要在该行的右侧或该列的下方选定一个空白单元格，然后单击“常用”工具栏中的自动求和按钮 ，最后按一下回车键，结果就会出现在所选定的单元格中。,5.2 公式中的引用设置,
5、引用的作用是通过标识工作表中的单元格或单元格区域，来指明公式中所使用的数据的位置。,5.2.1 相对引用、绝对引用和混合引用,1相对引用,相对引用指的是单元格的相对地址，其引用形式为直接用列标和行号表示单元格，例如B5，或用引用运算符表示单元格区域，如B5：D15。如果公式所在单元格的位置改变，引用也随之改变。默认情况下，公式使用相对引用。,引用单元格区域时，应先输入单元格区域起始位置的单元格地址，然后输入引用运算符，再输入单元格区域结束位置的单元格地址。,2绝对引用,绝对引用指引用单元格的精确地址，与包含公式的单元格位置无关，其引用形式为在列标和行号的前面都加上“$”号。则不管将公式复制或移
6、动到什么位置，引用的单元格地址的行和列都不会改变。,3混合引用,引用中既包含绝对引用又包含相对引用的称为混合引用，如A$1或$A1等，用于表示列变行不变或列不变行变的引用。 如果公式所在单元格的位置改变，则相对引用改变，而绝对引用不变。,5.2.2 引用不同工作表间的单元格,在同一工作簿中，不同工作表中的单元格可以相互引用，它的表示方法为：“工作表名称!单元格或单元格区域地址”。,5.2.3 引用不同工作簿间的单元格,在当前工作表中引用不同工作簿中的单元格的表示方法为：,5.3 公式中的错误和审核,5.3.1 公式中返回的错误代码含义,（1）错误值： 含义：输入到单元格中的数据太长或单元格公式
7、所产生的结果太大，使结果在单元格中显示不下，或是对日期和时间格式的单元格做减法，出现了负值。 解决办法：增加列的宽度，使结果能够完全显示。如果是由日期或时间相减产生了负值引起的，可以改变单元格的格式，比如改为文本格式。,（2）错误值：DIV/0! 含义：试图除以0。这个错误的产生通常有下面几种情况：除数为0、在公式中除数使用了空单元格或是包含零值单元格的单元格引用。 解决办法：修改单元格引用，或者在用作除数的单元格中输入不为零的值。（3）错误值：VALUE! 含义：输入引用文本项的数学公式。如果使用了不正确的参数或运算符，或者当执行自动更正公式功能时不能更正公式，都将产生错误信息VALUE!。
8、 解决办法：应确认公式或函数所需的运算符或参数正确，并且公式引用的单元格中包含有效的数值。例如，单元格C4中有一个数字或逻辑值，而单元格D4包含文本，则在计算公式=C4D4时，系统不能将文本转换为正确的数据类型，因而返回错误值VALUE!。,（4）错误值：REF! 含义：删除了被公式引用的单元格范围。 解决办法：恢复被引用的单元格范围，或是重新设定引用范围。（5）错误值：N/A 含义：无信息可用于所要执行的计算。在建立模型时，用户可以在单元格中输入#N/A，以表明正在等待数据。任何引用含有#N/A值的单元格都将返回#N/A。 解决办法：在等待数据的单元格内填上数据。（6）错误值：NUM! 含义
9、：提供了无效的参数给工作表函数，或是公式的结果太大或太小而无法在工作表中表示。 解决办法：确认函数中使用的参数类型正确。如果是公式结果太大或太小，就要修改公式。,（7）错误值：NAME? 含义：在公式中使用了Excel不能识别的文本。例如，可能是输错了名称，或是输入了一个已删除的名称。另外，如果没有将文字串括在双引号中，也会产生此错误值。 解决办法：如果是使用了不存在的名称而产生这类错误，应确认使用的名称确实存在；如果是函数名拼写错误，改正过来就可以了；将文本串括在双引号中；确认公式中使用的所有区域引用都使用了冒号（:），如SUM（C1:C10）。,（8）错误值：NULL！ 含义：在公式中的两
10、个范围之间插入一个空格以表示交叉点，但这两个范围没有公共单元格。例如，输入“=SUM(A1:A10 C1:C10)”，就会产生这种情况。 解决办法：改正区域运算符使之正确；更改引用使之相交。例如，在两个区域之间用逗号隔开“=SUM(A1:A10，C1:C10)”。,5.3.2 公式审核,1追踪单元格,追踪引用单元格 Excel可以追踪公式中引用的单元格（即指明所选单元格中的数据是由哪几个单元格中的数据通过公式计算得出的），并以箭头的形式标识引用的单元格。,追踪从属单元格 从属单元格是指使用所选单元格值的单元格，从属单元格通常包含公式或常数。,2取消追踪,要取消所有追踪箭头，可选择“工具”“公式
11、审核”“取消所有追踪箭头”菜单或单击“公式审核”工具栏中的“取消所有追踪箭头”按钮 。,3显示公式,要显示单元格中的公式，可单击工作表中的任意单元格，然后选择“工具”“公式审核”“公式审核模式”菜单，单元格中显示计算的公式。,再次选择该菜单，可在单元格中显示计算结果,4追踪错误,Excel 2003会自动对输入的公式进行检查，若发生错误，在单元格的左上角会出现一个绿色的小三角。此时选中含有错误值的单元格，然后选择“工具”“公式审核”“追踪错误”菜单，即可标识出产生错误的单元格,若单击含有错误的单元格，会在该单元格左侧出现 按钮。单击 按钮，会弹出快捷菜单，提供解决此错误的途径。,5逐步求值,在
12、Excel中，可以使用“公式求值”对话框验证所选单元格中公式的运算过程，从而找出发生错误的原因。,单击“求值”按钮，在“求值”编辑框中即可显示带下划线的表达式结果，并且结果也用下划线显示。多次单击“求值”按钮，“求值”编辑框中将逐步显示计算结果,6监视公式,使用Excel 2003的“监视窗口” 工具能将另一工作表内与编辑内容相关联的数据，开启成一个可移动的小窗口，若更改现有的工作表内容，监视窗口中关联的内容也会跟着改正。,打开监视窗口,添加第一个监视点,添加第二个监视点,修改数值后的监视窗口,删除监视点,5.4 使用函数,函数是预先定义好的表达式，它必须包含在公式中。每个函数都由函数名和参数
13、组成，其中函数名表示将执行的操作，参数表示函数将作用的值的单元格地址，通常是一个单元格区域，也可以是更为复杂的内容。在公式中合理地使用函数，可以完成如求和、逻辑判断、财务分析等众多数据处理功能。,5.4.1 函数的分类,常用的函数类型和使用范例,5.4.2 函数的使用方法,使用函数时，应首先确认已在单元格中输入了“=”号，即已进入公式编辑状态。接下来可输入函数名称，再紧跟着一对括号，括号内为一个或多个参数，参数之间要用逗号来分隔。,1手工输入函数,单击要输入函数的单元格，然后依次输入等号、函数名、左括号、具体参数和右括号，最后单击编辑栏中的“输入”按钮或按【Enter】键，此时在输入函数的单元
14、格中将显示运算结果。,2函数向导输入函数,如果不能确定函数的拼写或参数，可以使用函数向导输入函数。,5.5 数组公式及其应用,数组公式可以对两组或两组以上的数据（两个或两个以上的单元格区域）同时进行计算。在数组公式中使用的数据称为数组参数，数组参数可以是一个区域数组，也可以是常量数组。,5.5.1 数组公式的建立方法,首先选定用来存放结果的单元格或单元格区域（如果数组公式将返回一个结果，单击需要输入数组公式的单元格；如果数组公式将返回多个结果，则要选定需输入数组公式的单元格区域），然后输入公式，最后按【Ctrl+Shift+Enter】组合键生成数组公式。,1用数组公式计算两个数据区域的乘积,
15、2用数组公式计算多列数据之和,5.5.2 使用数组公式的规则,输入数组公式时，首先选择用来保存计算结果的单元格区域，如果将产生多个计算结果，必须选择一个与计算结果所需大小和形状都相同的单元格区域。数组公式输入完成后，按下【Ctrl+Shift+Enter】组合键，这时在编辑栏中可以看见公式的两边自动加上了花括号，表示该公式是一个数组公式。,不要自己键入花括号，否则，Excel认为输入的是一个正文标签。,在数组公式所涉及的区域中，不能编辑、清除或移动单个单元格，也不能插入或删除其中任何一个单元格，即数组公式所涉及的单元格区域只能作为一个整体进行操作。,要编辑或清除数组，需要选择整个数组并激活编辑栏，然后在编辑栏中修改数组公式或删除数组公式，操作完成后，按下【Ctrl+Shift+Enter】组合键即可。,要把数组公式移到另一个位置，需要先选中整个数组公式所在的区域，然后把整个区域拖放到目标位置，也可通过“编辑”“剪切”和“粘贴”菜单进行。,综合实例制作工资表,本实例主要练习一下公式和函数的使用方法。首先利用数组公计算“应发工资”和“保险扣款”（也可利用公式算）列数据，最后利用IF函数计算“所得税”。,具体操作步骤见视频5-1,
