2024年2月23日发(作者：)

点击翻页点击翻页第7章 引用、公式和函数通过本章，你应当学会：（1）引用单元格。（2）使用公式。（3）使用函数。（4）审核计算结果。在制作表格时，经常会对表格中的数据进行加、减、乘、除等计算操作，此时使用Excel的公式可方便地完成这些操作，对于较为复杂的运算，如求多个单元格数据的平均值、最小值和最大值等，则需要使用Excel的函数进行计算，本章将介绍Excel中公式和函数的使用方法。点击翻页7.1 引用单元格在编辑公式之前，应掌握引用单元格的知识，因为在使用公式时，需要对单元格地址进行引用。引用的作用在于标识某个单元格或单元格区域，并指明公式中所使用的数据地址。引用单元格分为：相对引用、绝对引用和混合引用3种。7.1.1 相对引用相对引用是相对于公式单元格位于某一位置处的单元格引用。当公式所在的单元格位置改变时，其引用的单元格地址也随之改变。当复制相对引用单元格的公式时，被粘贴公式中的引用将自动更新，并指向与当前公式位置相对应的单元格。1.在公式中的相对引用在图7-1-1所示的表格中，J3单元格包含公式“=C3+D3+E3+F3+G3+H3+I3”，表示在J3单元格中引用C3、D3、E3、F3、G3、H3和I3中的数据，并将这7个数据相加。图7-1-12.相对引用的特点83点击翻页

点击翻页Excel 2007中文版实用教程点击翻页使用拖动法将J3单元格中的公式复制到单元格J4中后，则J4单元格中的公式自动改变为“=C4+D4+E4+F4+G4+H4+I4”，这就是相对引用的特点，如图7-1-2所示。图7-1-2在工作表中使用拖动法计算某一列单元格的结果时，需要使用相对引用。图7-1-1中J3单元格中有公式“=C3+D3+E3+F3+G3+H3+I3”，此时只需使用拖动法即可将J4～J8单元格的结果计算出来。在默认情况下，表格公式中的单元格使用相对引用。7.1.2 绝对引用如果不希望在复制单元格的公式时，引用的单元格地址发生改变，则应使用绝对引用。绝对引用是指把公式复制或填入到新位置后，公式中的单元格地址保持不变。1.在公式中的绝对引用在引用单元格的列标和行号之前分别加入符号“$”便为绝对引用，图7-1-3所示的表格中，J3单元格包含公式“=$C$3+$D$3+$E$3+$F$3+$G$3+$H$3+$I$3”，这就是绝对引用。图7-1-32.绝对引用的特点使用拖动法将J3单元格中的公式复制到J4单元格中，此时可以看到J4单元格公式中的单元格地址并没有发生改变，其公式仍为“=$C$3+$D$3+$E$3+$F$3+$G$3+$H$3+$I$3”，这就是绝对引用的特点。如图7-1-4所示。84点击翻页点击翻页

点击翻页点击翻页第7章 引用、公式和函数图 7-1-4点击翻页使用复制和粘贴功能时，公式中绝对引用的单元格地址不改变，相对引用的单元格地址将会发生改变。使用剪切和粘贴功能时，公式中单元格的绝对引用和相对引用地址都不会发生 改变。7.1.3 混合引用混合引用是指在引用一个单元格的地址中，既有绝对单元格地址，又有相对单元格地址。如果公式所在单元格的位置改变，则相对引用的单元格地址改变，而绝对引用的单元格地址 不变。1.在公式中的混合引用在图7-1-5所示的表格中，J3单元格中包含公式“=$C$3+D3+E3+F3+G3+H3+I3”，这就是混合引用的一种。图7-1-52.混合引用的特点使用拖动法将J3单元格中的公式复制到单元格J4中后，公式中只有对C3单元格引用的地址没有发生改变，而在前面没有添加“$”符号的单元格地址都发生了变化，如图7-1-6所示。85点击翻页

点击翻页Excel 2007中文版实用教程点击翻页图7-1-6在编辑栏中选择公式后，利用F4键可以进行相对引用与绝对引用的切换。按一次F4键转换成绝对引用，连续按两次F4键转换为不同的混合引用，再按一次F4键可还原为相对引用。

7.1.4 引用其他工作表/工作簿中的单元格在表格中除了可以引用本工作表单元格中的数据外，还可以引用其他工作表和工作簿中的单元格数据。1.引用其他工作表中的数据如要引用同一个工作簿中的其他工作表单元格中的数据，一般格式为：工作表名称！单元格地址。图7-1-7所示的M3单元格中包含公式“=J3+Sheet2!J3”，表示将当前工作表J3单元格中的数据与Sheet2工作表J3单元格中的数据相加。图7-1-72.引用其他工作簿的单元格若要引用其他工作簿的单元格数据，一般格式为：’工作簿存储地址[工作簿名称]工作表名称’！单元格地址。图7-1-8所示表格中，M4单元格中包含公式“=J4+’E:[学生成绩单.xlsx]Sheet1’!J4”，表示将当前工作表J4单元格中的数据与E盘下的“学生成绩单”工作簿中的Sheet1工作表J4单元格中的数据相加。86点击翻页点击翻页

点击翻页点击翻页第7章 引用、公式和函数图7-1-87.2 使 用 公 式使用公式计算数据是工作表处理过程中经常涉及到的内容。在Excel中不仅可以输入公式计算表格中的数据，还可以复制公式，从而快速计算出其他单元格中的数据，如果输入的公式有错，还可以对该公式进行修改，下面介绍Excel的这些基本操作。7.2.1 输入公式在单元格中输入公式的操作与输入文本类似，不同的是在输入一个公式时总是以一个“＝”作为开头，然后才是公式的表达式。输入公式，操作步骤如下：（1）打开“学生成绩表”工作簿，选择要存放计算结果的单元格，这里选择J3单元格，如图7-2-1所示。图7-2-1（2）直接输入“=C3+D3+E3+F3+G3+H3+I3”，公式显示在编辑栏中，如图7-2-2所示。图7-2-287点击翻页点击翻页

点击翻页Excel 2007中文版实用教程点击翻页（3）按Enter键即可在J3单元格中显示计算结果，如图7-2-3所示。图7-2-3在单元格中输入公式时，输入“＝”后，既可以直接输入用于计算的单元格地址，也可以选择用于计算的单元格。被输入的单元格地址或被选择的单元格以彩色的边框显示，方便确认输入是否有误，在得出结果后，彩色的边框将自动消失。7.2.2 使用填充法快速复制公式当需要在工作表的同列单元格中输入类似的公式时，如果采用逐个输入公式的方法，则输入的速度非常慢，而使用填充法复制公式就会节约很多时间。使用填充法快速复制公式，操作步骤如下：（1）打开“学生成绩表”工作簿，选择有公式的单元格J3，如图7-2-4所示。图7-2-4（2）将鼠标指针移到该单元格的右下角，此时鼠标指针变为形状，如图7-2-5所示。图7-2-588点击翻页点击翻页

点击翻页元格区域中复制公式，并计算出相应的结果，如图7-2-6所示。点击翻页第7章 引用、公式和函数（3）按住鼠标左键不放向下拖动，当拖动到J8单元格时释放鼠标左键，即可在J4:J8单图7-2-6点击翻页除了使用填充法快速复制公式外，还有一种复制公式的方法：选择有公式的单元格后，按“Ctrl+C”组合键，选择要粘贴的单元格或单元格区域，再按“Ctrl+V”组合键，即可完成复制公式的操作。使用填充法快速复制公式是制作表格过程中经常使用的操作，通常该方法可以同时计算出多个单元格的结果。7.2.3 修改公式在单元格中输入公式后，如果发现输入有误，可以修改该公式。修改公式的方法与修改单元格中数据的方法类似。修改公式，操作步骤如下：（1）打开“学生成绩表”工作簿，双击需要修改的单元格，这里双击J3单元格，此时被引用的单元格以彩色边框显示，如图7-2-7所示。图7-2-7（2）将鼠标光标定位到需要修改的单元格地址处，按鼠标左键拖动将其选择，这里选择E4，如图7-2-8所示。89点击翻页

点击翻页Excel 2007中文版实用教程点击翻页图7-2-8（3）直接输入正确的单元格地址，如图7-2-9所示。图7-2-9（4）单击编辑栏中的“输入”按钮7-2-10所示。即可完成修改公式的操作并计算出正确结果，如图图7-2-10选择需要修改公式的单元格后，将鼠标光标定位在编辑栏中，然后按照修改数据的方法也可以修改公式。修改公式时，选择需要修改的单元格地址后，按Delete键将其删除，然后再单击正确的单元格即可完成修改。90点击翻页点击翻页

点击翻页7.2.4 显示公式点击翻页第7章 引用、公式和函数默认情况下，单元格中只显示公式计算的结果，而公式本身则只显示在编辑栏中。为了方便检查公式的正确性，可以设置在单元格中显示公式。显示公式，操作步骤如下：（1）打开“学生成绩表”工作簿，选择“公式”选项卡，单击“公式审核”组中的“显示公式”按钮，如图7-2-11所示。点击翻页图7-2-11（2）此时工作表中有公式的单元格都将显示出公式，如图7-2-12所示。图7-2-127.2.5 隐藏公式如果不希望他人看到自己使用的计算公式，可以将单元格中的公式隐藏起来。如果公式被隐藏，即使选择了该单元格，公式也不会显示在编辑栏中。隐藏公式，操作步骤如下：（1）打开“学生成绩表”工作簿，选择要隐藏公式的单元格或者单元格区域，这里选择

J3:J8单元格区域，如图7-2-13所示。图7-2-1391点击翻页

点击翻页Excel 2007中文版实用教程点击翻页（2）单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”命令，如图7-2-14所示。图7-2-14（3）打开“设置单元格格式”对话框，切换到“保护”选项卡，勾选“隐藏”复选框，然后单击“确定”按钮，如图7-2-15所示。图7-2-15（4）选择“审阅”选项卡，单击“更改”组中的“保护工作表”按钮，如图7-2-16所示。图7-2-16（5）在弹出的“保护工作表”对话框的“取消工作表保护时使用的密码”文本框中输入密码，单击“确定”按钮，如图7-2-17所示，在打开的“确认密码”对话框中再次输入密码，单击“确定”按钮，如图7-2-18所示。92点击翻页点击翻页

点击翻页点击翻页第7章 引用、公式和函数图7-2-17图7-2-18（6）此时选择J3:J8单元格区域中任意单元格后，编辑栏中都不会显示出公式了，如图7-2-19所示。点击翻页是指在编辑栏中不显示公式。图7-2-19显示公式和隐藏公式并不是相反的过程，显示公式是指在单元格中显示公式，而隐藏公式7.2.6 删除公式在Excel中，可以只删除单元格中的公式，而不删除其计算结果。删除公式，操作步骤如下：（1）打开“学生成绩表”工作簿，选择要删除公式的单元格，这里选择J5单元格，如图7-2-20所示。图7-2-2093点击翻页

点击翻页Excel 2007中文版实用教程点击翻页（2）在J5单元格上单击鼠标右键，在弹出的快捷菜单中选择“复制”命令，如图7-2-21所示。图7-2-21（3）选择“开始”选项卡，单击“剪贴板”组中“粘贴”按钮下方的下拉按钮，在弹出的菜单中选择“选择性粘贴”命令，如图7-2-22所示。图7-2-22（4）打开“选择性粘贴”对话框，选中“数值”单选按钮，单击“确定”按钮，如图7-2-23所示。图7-2-23（5）此时删除了工作表中J5单元格中的公式，但保留结果，如图7-2-24所示。图7-2-2494点击翻页点击翻页

点击翻页点击翻页第7章 引用、公式和函数如果要删除单元格中的计算结果和公式，只需选择该单元格，然后按Delete键即可。7.2.7 使用“求和”按钮计算在Excel中经常需要对多个单元格中的数据进行求和，此时可以单击“开始”选项卡“编辑”组中的“求和”按钮使用“求和”按钮如图7-2-25所示。对工作表中所选择的单元格区域快速求和。计算，操作步骤如下：（1）打开“学生成绩表”工作簿，选择要存放求和结果的单元格，这里选择J10单元格，点击翻页图7-2-25（2）单击“开始”选项卡“编辑”组中的“求和”按钮，系统自动选择该列J10单元格上方所有包含数据的单元格，并在J10单元格中显示引用范围与计算公式，如图7-2-26所示。图7-2-26（3）单击编辑栏中的“输入”按钮使用“求和”按钮，求和结果便自动显示在J10单元格中，如7-2-27所示。只能计算相邻单元格中的数值的和。95点击翻页

点击翻页Excel 2007中文版实用教程点击翻页图7-2-27单击“开始”选项卡“编辑”组中的“求和”按钮击编辑栏中的“输入”按钮后，系统自动选择该列中有数值的单元格，用户也可直接选择自己要求和的单元格区域，选择的单元格区域周围出现闪烁的边框，然后单，即可计算出所选单元格区域的数值和。7.3 使 用 函 数函数是Excel中预定义的公式，它通过使用一些成为参数的特定数值并按特定的顺序或结构执行运算。利用函数可方便地执行复杂的计算，从而提高工作效率。在工作表中修改、复制以及删除函数的方法与使用公式时的相应操作方法相同，下面介绍插入和嵌套函数的方法。7.3.1 插入函数单击编辑栏中的“插入函数”按钮插入函数，操作步骤如下：（1）打开“学生成绩表”工作簿，选择要插入函数的C9单元格，如图7-3-1所示。，通过打开的“插入函数”对话框可以选择使用的函数类型，下面以使用AVERAGE函数求平均值为例讲解插入函数的方法。图7-3-196点击翻页点击翻页

点击翻页（2）单击编辑栏中的“插入函数”按钮，打开“插入函数”对话框，在“选择函数”列表中选择“AVERAGE”函数，单击“确定”按钮，如图7-3-2所示。点击翻页第7章 引用、公式和函数图7-3-2（3）在打开的“函数参数”对话框中，“Number1”文本框中默认引用C3:C8单元格区域，点击翻页单击“确定”按钮关闭“函数参数”对话框”，如图7-3-3所示。图7-3-3（4）求出的平均值即显示在C9单元格中，如图7-3-4所示。图7-3-4在打开的“函数参数”对话框中，如果要引用的区域与“Number1”文本框中默认引用的区域不一致，可以删除单元格区域，单击其右侧的按钮重新选择需要引用的单元格区域。如在“插入函数”对话框的“选择函数”列表框中没有所需的函数，则需要在“或选择类别”下拉列表框中选择所需函数类别，然后再在“选择函数”列表框中查找所需的函数。97点击翻页

点击翻页Excel 2007中文版实用教程点击翻页7.3.2 嵌套函数嵌套函数就是将某一函数或公式作为另一个函数的参数使用。嵌套函数，操作步骤如下：（1）打开“伊美服装销售情况表”工作簿，选择E15单元格，如图7-3-5所示。图7-3-5（2）单击编辑栏中的“插入函数”按钮，打开“插入函数”对话框，在“选择函数”列表中选择“SUM”函数类型，单击“确定”按钮，如图7-3-6所示。图7-3-6（3）删除“Number1”文本框中的单元格地址，在表格中单击C4单元格，在“Number1”文本框中输入“*”号，再在表格中单击E4单元格，“Number1”文本框中单元格地址引用完成。依照相同的方法输入“Number2”～“Number10”文本框中的内容，单击“确定”按钮，如图7-3-7所示。图7-3-798点击翻页点击翻页

点击翻页（4）求出的和值即显示在E15单元格中，如图7-3-8所示。点击翻页第7章 引用、公式和函数图7-3-8点击翻页7.3.3 常用函数应用举例Excel 2007中包括7种类型的上百个具体函数，每个函数的应用各不相同。下面讲解几个日常使用比较频繁的函数，如SUM函数、AVERAGE函数、IF函数和MAX函数。1.使用求和函数SUM求和函数表示对所选单元格或单元格区域中的数据进行加法运算，其语法结构为SUM（number1，number2，...）。使用求和函数SUM，操作步骤如下：（1）打开“员工业绩表”工作簿，选择D10单元格，如图7-3-9所示。图7-3-9（2）单击“插入函数”按钮，在弹出的“插入函数”对话框中选择SUM函数，单击“确定”按钮，如图7-3-10所示。图7-3-1099点击翻页

点击翻页Excel 2007中文版实用教程点击翻页（3）在打开的“函数参数”对话框中，“Number1”文本框中默认引用D3:D9单元格区域，单击“确定”按钮，如图7-3-11所示。图7-3-11（4）求出的和值即可显示在D10单元格中，如图7-3-12所示。图7-3-122.使用平均值函数AVERAGE平均值函数的原理是将所选单元格区域中的数据相加，然后除以单元格个数，返回作为结果的算术平均值，其语法结构为：AVERAGE（number1，number2，...）。使用平均值函数AVERAGE，操作步骤如下：（1）打开“员工业绩表”工作簿，选择D11单元格，如图7-3-13所示。图7-3-13100点击翻页点击翻页

点击翻页（2）单击“插入函数”按钮，在弹出的“插入函数”对话框中选择AVERAGE函数，单击“确定”按钮，如图7-3-14所示。点击翻页第7章 引用、公式和函数图7-3-14（3）在打开的“函数参数”对话框中，在“Number1”文本框中输入D3:D9，设定计算点击翻页平均值的单元格区域，单击“确定”按钮，如图7-3-15所示。图7-3-15（4）求出的平均值即显示在D11单元格中，如图7-3-16所示。图7-3-163.使用条件函数IF条件函数可以实现真假值的判断，它根据逻辑计算的真假值返回两种结果。该函数的语法结构为：IF（logical_test，value_if_true，value_if_false）。其中，logical_test表示计算结果为true或false的任意值或表达式；value_if_true表示当logical_test为true时返回的值；value_if_false表示当logical_test为false时返回的值。使用条件函数IF，操作步骤如下：101点击翻页

点击翻页Excel 2007中文版实用教程点击翻页（1）打开“员工业绩表”工作簿，使用条件函数IF设置业绩大于平均值的员工可以获得年终奖，选择F3单元格，如图7-3-17所示。图7-3-17（2）单击“插入函数”按钮定”按钮，如图7-3-18所示。，在弹出的“插入函数”对话框中选择IF函数，单击“确图7-3-18（3）在打开的“函数参数”对话框中，在“logical_test”文本框中输入“D3＞$D1$1”，在“value_if_true”文本框中输入“是”，“value_if_false”文本框中输入“否”，单击“确定”按钮，如图7-3-19所示。图7-3-19（4）此时即可在F3单元格中显示该员工是否能够获得年终奖，如图7-3-20所示。图7-3-20102点击翻页点击翻页

点击翻页（5）最后通过混合引用功能，复制条件函数至F4:F9单元格区域，结果如图7-3-21

所示。点击翻页第7章 引用、公式和函数图7-3-214.使用最大值函数MAX最大值函数可以将选择的单元格区域中的最大值返回到需要保存结果的单元格中，其语法结构为：MAX（number1，number2，...）。使用最大值函数MAX，操作步骤如下：（1）打开“员工业绩表”工作簿，使用最大值函数MAX求业绩最大值，选择D12单元格，如图7-3-22所示。点击翻页图7-3-22（2）单击“插入函数”按钮，在弹出的“插入函数”对话框中选择MAX函数，单击“确定”按钮，如图7-3-23所示。图7-3-23（3）在打开的“函数参数”对话框中，在“Number1”文本框中输入D3:D9，设定获取最大值的单元格区域，单击“确定”按钮，如图7-3-24所示。103点击翻页

点击翻页Excel 2007中文版实用教程点击翻页图7-3-24（4）求出的最大值即显示在D12单元格中，如图7-3-25所示。图7-3-257.4 审核计算结果在大型的工作表中，用户要正确查找到公式的错误是很困难的，此时可以使用Excel提供的审核工作表功能，这样可以很容易地检查工作表的公式与单元格之间的相互关系，找出错误所在，以便进行相关的修改。7.4.1 检查引用单元格和从属单元格引用单元格是指被其他单元格的公式引用的单元格；从属单元格是指该单元格中含有公式且公式中引用了其他单元格；引用单元格提供数据，而从属单元格使用数据。检查引用单元格和从属单元格，操作步骤如下：（1）打开“员工业绩表”工作簿，选择D12单元格，如图7-4-1所示。图7-4-1104点击翻页点击翻页

点击翻页7-4-2所示。点击翻页第7章 引用、公式和函数（2）选择“公式”选项卡，单击“公式审核”组中的“追踪引用单元格”按钮，如图图7-4-2（3）此时系统使用蓝色箭头显示D12单元格引用D3:D9单元格区域，如图7-4-3所示。（4）单击“公式审核”组中的“移去箭 头”点击翻页按钮，移去引用单元格的追踪箭头。图7-4-3（5）选择D3单元格，单击“公式审核”组中的“追踪从属单元格”按钮，如图7-4-4

所示。图7-4-4（6）此时系统使用蓝色箭头显示D3单元格从属于D10、D11、D12、E3和F3单元格，如图7-4-5所示。图7-4-57.4.2 常见的计算错误在Excel中使用公式计算数据会出现一些错误，出现错误的原因有多种，如在需要数字的公式中使用了文本，删除了被公式引用的单元格、单元格的宽度不够显示结果等，下面列出105点击翻页

点击翻页Excel 2007中文版实用教程点击翻页l中常见的一些错误。1.＃＃＃＃错误如果单元格中所含的数字、日期或者时间比单元格宽或者单元格的日期时间公式产生了一个负值，就会显示＃＃＃＃，如图7-4-6所示。图7-4-62.＃VALUE!错误当使用的参数或操作的数据类型错误时，或者当公式自动更正功能不能更正公式时，将显示＃VALUE!，如图7-4-7所示。图7-4-73.＃DIV/0！当公式被0除时，将会显示＃DIV/0！，如图7-4-8所示。图7-4-84.＃NAME?错误在公式中使用Excel不能识别的文本时将显示＃NAME？，如图7-4-9所示。图7-4-9106点击翻页点击翻页

点击翻页5.＃REF!错误当单元格引用无效时将显示＃REF!，如图7-4-10所示。点击翻页第7章 引用、公式和函数图7-4-106.＃N/A错误点击翻页在函数或公式中没有可用数值。如果工作表中某些单元格暂时没有数值，可在这些单元格中输入“＃N/A”。公式在引用这些单元格时，将不进行数值计算，而是返回“＃N/A”，如图7-4-11所示。图7-4-117.其他错误（1）#NUM!错误公式或函数中某个数值有问题。（2）＃NULL!错误试图为两个并不相交的区域指定交叉点。在单元格中输入公式时，应注意以下几点，以避免计算错误。（1）公式所有的括号必须成对出现。（2）在引用单元格区域时，第一个单元格与最后一个单元格之间用半角的英文冒号间隔。（3）在引用当前工作簿中其他工作表单元格区域时一定要包含工作表名称。（4）函数中输入的参数必须符合要求。7.5 小 结本章主要介绍了Excel公式和函数的操作方法。通过本章的学习，读者应掌握如何输入和编辑公式、复制公式以及如何发现并纠正一些常见公式的计算错误，对常用的函数也要有一定的了解和应用能力。107点击翻页

点击翻页Excel 2007中文版实用教程点击翻页7.6 练 习填空题（1）引用单元格分为：（2）相对引用是指键即可。（4）使用“求和”按钮（5）表的信息。只能计算单元格中的数值的和。是Excel预定义的内置公式，可以进行数学、文本、逻辑的运算或查找工作、和3种。；混合引用是指。；绝对引用是指（3）如果要删除单元格中的计算结果和公式，只需选择要删除其内容的单元格，然后按简答题（1）相对引用、绝对引用以及混合引用的区别是什么？（2）引用单元格和从属单元格的区别是什么？上机练习（1）创建一个新工作簿，如图7-6-1所示，应用公式对该工作表计算应发工资、所得税、实发工资、总计、平均工资等。图7-6-1计算公式：应发工资＝基本工资+奖金+福利工资实发工资＝应发工资-扣水电费-所得税所得税的计算公式：应发工资-2000≤0时 税率为00<应发工资-2000≤500时 税率为0.5%应发工资-2000>500时 税率为1％所得税＝（应发工资－2000）×税率（2）利用各种函数对公式进行计算。108点击翻页点击翻页