搜索
83点击翻页点击翻页点击翻页点击翻页第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.相对引用的特点Excel2007中文版实用教程84点击翻页点击翻页点击翻页点击翻页图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所示。使用拖动法将J3单元格中的公式复制到单元格J4中后，则J4单元格中的公式自动改变为“=C4+D4+E4+F4+G4+H4+I4”，这就是相对引用的特点，如图7-1-2所示。第7章引用、公式和函数85点击翻页点击翻页点击翻页点击翻页图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所示。Excel2007中文版实用教程86点击翻页点击翻页点击翻页点击翻页图7-1-6在编辑栏中选择公式后，利用F4键可以进行相对引用与绝对引用的切换。按一次F4键转换成绝对引用，连续按两次F4键转换为不同的混合引用，再按一次F4键可还原为相对引用。7.1.4引用其他工作表/工作簿中的单元格在表格中除了可以引用本工作表单元格中的数据外，还可以引用其他工作表和工作簿中的单元格数据。1.引用其他工作表中的数据如要引用同一个工作簿中的其他工作表单元格中的数据，一般格式为：工作表名称！单元格地址。图7-1-7所示的M3单元格中包含公式“=J3+Sheet2!J3”，表示将当前工作表J3单元格中的数据与Sheet2工作表J3单元格中的数据相加。2.引用其他工作簿的单元格若要引用其他工作簿的单元格数据，一般格式为：’工作簿存储地址[工作簿名称]工作表名称’！单元格地址。图7-1-8所示表格中，M4单元格中包含公式“=J4+’E:\[学生成绩单.xlsx]Sheet1’!J4”，表示将当前工作表J4单元格中的数据与E盘下的“学生成绩单”工作簿中的Sheet1工作表J4单元格中的数据相加。图7-1-7第7章引用、公式和函数87点击翻页点击翻页点击翻页点击翻页7.2使用公式使用公式计算数据是工作表处理过程中经常涉及到的内容。在Excel中不仅可以输入公式计算表格中的数据，还可以复制公式，从而快速计算出其他单元格中的数据，如果输入的公式有错，还可以对该公式进行修改，下面介绍Excel的这些基本操作。7.2.1输入公式在单元格中输入公式的操作与输入文本类似，不同的是在输入一个公式时总是以一个“＝”作为开头，然后才是公式的表达式。输入公式，操作步骤如下：（1）打开“学生成绩表”工作簿，选择要存放计算结果的单元格，这里选择J3单元格，如图7-2-1所示。（2）直接输入“=C3+D3+E3+F3+G3+H3+I3”，公式显示在编辑栏中，如图7-2-2所示。图7-2-1图7-2-2图7-1-8Excel2007中文版实用教程88点击翻页点击翻页点击翻页点击翻页（3）按Enter键即可在J3单元格中显示计算结果，如图7-2-3所示。图7-2-3在单元格中输入公式时，输入“＝”后，既可以直接输入用于计算的单元格地址，也可以选择用于计算的单元格。被输入的单元格地址或被选择的单元格以彩色的边框显示，方便确认输入是否有误，在得出结果后，彩色的边框将自动消失。7.2.2使用填充法快速复制公式当需要在工作表的同列单元格中输入类似的公式时，如果采用逐个输入公式的方法，则输入的速度非常慢，而使用填充法复制公式就会节约很多时间。使用填充法快速复制公式，操作步骤如下：（1）打开“学生成绩表”工作簿，选择有公式的单元格J3，如图7-2-4所示。（2）将鼠标指针移到该单元格的右下角，此时鼠标指针变为形状，如图7-2-5所示。图7-2-5图7-2-4第7章引用、公式和函数89点击翻页点击翻页点击翻页点击翻页（3）按住鼠标左键不放向下拖动，当拖动到J8单元格时释放鼠标左键，即可在J4:J8单元格区域中复制公式，并计算出相应的结果，如图7-2-6所示。除了使用填充法快速复制公式外，还有一种复制公式的方法：选择有公式的单元格后，按“Ctrl+C”组合键，选择要粘贴的单元格或单元格区域，再按“Ctrl+V”组合键，即可完成复制公式的操作。使用填充法快速复制公式是制作表格过程中经常使用的操作，通常该方法可以同时计算出多个单元格的结果。7.2.3修改公式在单元格中输入公式后，如果发现输入有误，可以修改该公式。修改公式的方法与修改单元格中数据的方法类似。修改公式，操作步骤如下：（1）打开“学生成绩表”工作簿，双击需要修改的单元格，这里双击J3单元格，此时被引用的单元格以彩色边框显示，如图7-2-7所示。图7-2-6（2）将鼠标光标定位到需要修改的单元格地址处，按鼠标左键拖动将其选择，这里选择E4，如图7-2-8所示。图7-2-7Excel2007中文版实用教程90点击翻页点击翻页点击翻页点击翻页（3）直接输入正确的单元格地址，如图7-2-9所示。图7-2-8（4）单击编辑栏中的“输入”按钮即可完成修改公式的操作并计算出正确结果，如图7-2-10所示。选择需要修改公式的单元格后，将鼠标光标定位在编辑栏中，然后按照修改数据的方法也可以修改公式。修改公式时，选择需要修改的单元格地址后，按Delete键将其删除，然后再单击正确的单元格即可完成修改。图7-2-10图7-2-9第7章引用、公式和函数91点击翻页点击翻页点击翻页点击翻页（2）此时工作表中有公式的单元格都将显示出公式，如图7-2-12所示。图7-2-11图7-2-127.2.4显示公式默认情况下，单元格中只显示公式计算的结果，而公式本身则只显示在编辑栏中。为了方便检查公式的正确性，可以设置在单元格中显示公式。显示公式，操作步骤如下：（1）打开“学生成绩表”工作簿，选择“公式”选项卡，单击“公式审核”组中的“显示公式”按钮，如图7-2-11所示。7.2.5隐藏公式如果不希望他人看到自己使用的计算公式，可以将单元格中的公式隐藏起来。如果公式被隐藏，即使选择了该单元格，公式也不会显示在编辑栏中。隐藏公式，操作步骤如下：（1）打开“学生成绩表”工作簿，选择要隐藏公式的单元格或者单元格区域，这里选择J3:J8单元格区域，如图7-2-13所示。图7-2-13Excel2007中文版实用教程92点击翻页点击翻页点击翻页点击翻页图7-2-14图7-2-15（2）单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”命令，如图7-2-14所示。（3）打开“设置单元格格式”对话框，切换到“保护”选项卡，勾选“隐藏”复选框，然后单击“确定”按钮，如图7-2-15所示。图7-2-16（5）在弹出的“保护工作表”对话框的“取消工作表保护时使用的密码”文本框中输入密码，单击“确定”按钮，如图7-2-17所示，在打开的“确认密码”对话框中再次输入密码，单击“确定”按钮，如图7-2-18所示。（4）选择“审阅”选项卡，单击“更改”组中的“保护工作表”按钮，如图7-2-16所示。第7章引用、公式和函数93点击翻页点击翻页点击翻页点击翻页图7-2-17图7-2-18图7-2-19（6）此时选择J3:J8单元格区域中任意单元格后，编辑栏中都不会显示出公式了，如图7-2-19所示。显示公式和隐藏公式并不是相反的过程，显示公式是指在单元格中显示公式，而隐藏公式是指在编辑栏中不显示公式。7.2.6删除公式在Excel中，可以只删除单元格中的公式，而不删除其计算结果。删除公式，操作步骤如下：（1）打开“学生成绩表”工作簿，选择要删除公式的单元格，这里选择J5单元格，如图7-2-20所示。图7-2-20Excel2007中文版实用教程94点击翻页点击翻页点击翻页点击翻页图7-2-21图7-2-22图7-2-23图7-2-24（2）在J5单元格上单击鼠标右键，在弹出的快