第1章 公式与函数入门

1.1 认识公式

使用公式除了可以进行诸如加、减、乘、除等简单的计算，还可以完成很复杂的财务、统计及科学计算任务。另外，使用公式还可以比较或操作文本。如果函数要以公式的形式出现，那么它必须有两个组成部分，一个是函数名称前面的等号，另一个则是函数本身。

1.1.1 公式的构成

知识点讲解

ExceI中的公式主要由等号（=）、操作符和运算符组成。公式以等号（=）开始，用于表明之后的字符为公式。紧随等号之后的是需要用来进行计算的元素（操作数），各操作数之间以算术运算符分隔，如图1-1所示。

图中所示的公式的含义为：

（1）将A2单元格中的数值加上67。

（2）计算B2单元格到F2单元格的和，即B2+C2+D2+E2+F2。

（3）将（1）的结果除以（2）的结果。

以公式“=SUM(E1:H1)*A1+26”为例，它要以等号（=）开始，内部可以包括函数、引用、运算符和常量。上式中的“SUM(E1:H1)”是函数，“A1”则是对单元格A1的引用（使用其中存储的数据），“26”则是常量，“*”和“+”则是算术运算符（另外还有比较运算符、文本运算符和引用运算符）。

1.1.2 输入公式的方法

知识点讲解

需要输入公式时，可以在编辑栏中输入，也可以在单元格中直接输入。

1.在编辑栏中输入公式

像输入数字或文本一样键入公式后，再按【Enter】键或单击“输入”按钮即可，如图1-2所示。

2.在单元格里直接输入公式

双击要输入公式的单元格，或者先选中单元格再按【F2】键，在单元格中输入公式，最后按【Enter】键，如图1-3所示。

3.输入公式的具体步骤

新建工作表，在D2单元格中计算出B2和C2单元格中数值的和。

1 建立一个如图1-4所示的工作表。

2 选定单元格D2。

3 在编辑栏中输入“=B2+C2”。

4 按【Enter】键，D2中将显示公式的计算结果“112”，如图1-5所示。

动手练

创建并输入公式。将上面示例中创建的工作表中的B4和C4单元格中的两个文本连接为一个文本。

1 选定单元格E4。

2 在编辑栏中输入“=”，然后用鼠标单击B4单元格，发现编辑栏中显示“=B4”，在编辑栏中输入“＆”，再用鼠标单击C4单元格，编辑栏中显示“=B4＆C4”。

3 按【Enter】键，E4单元格中就会显示“学会生存”，这是公式“=B4&C4”的计算结果，如图1-6所示。

1.1.3 公式编辑

知识点讲解

对于单元格中的公式，也可以像单元格中的其他数据一样进行编辑，例如修改、复制或移动等。

1.修改公式

修改公式同修改单元格中数据的方法一样。先单击包含要修改公式的单元格，如果要删除公式中的某些项，在编辑栏中用鼠标选中要删除的部分后，再按【Backspace】或者【DeIete】键。如要替换公式中的某些部分，须先选中被替换的部分，然后再进行修改。在未确认之前可以单击“取消”按钮或按【Esc】键放弃本次修改。如果已确认修改但还未执行其他命令，单击快速访问工具栏中的“撤销”按钮或按【CtrI十Z】组合键，仍可放弃本次修改。

2.复制公式

下面以将图1-7中E4单元格中的公式复制到单元格E6中为例介绍。

操作步骤如下。

1 选中单元格E4，如图1-7所示。

2 单击快速访问工具栏中的“复制”按钮，或按【Ctrl+C】组合键。

3 单击E6单元格。

4 选择“开始”选项卡，然后单击“剪贴板”工具组中“粘贴”下拉按钮，在弹出的下拉菜单中选择“选择性粘贴”命令，如图1-8所示。

5 在弹出的“选择性粘贴”对话框中的“粘贴”栏中选中“公式”单选按钮，如图1-9所示。

6 单击“确定”按钮，E6中显示“14579”，即已将E4中的公式复制过来，如图1-10所示。

3.移动公式

下面以将图1-10单元格E4中的公式移动到单元格G5中为例，操作步骤如下。

1 选定E4单元格。

2 将鼠标指针移到单元格的边框上，当指针变为形状时按住鼠标左键不放。

3 拖动鼠标到G5单元格。

4 释放鼠标左键，即可将公式从E4单元格移动到G5单元格，如图1-11所示。

动手练

试将如图1-12所示的表格中C47单元格中的公式“=AVERAGE(B3:B45)”修改为“=AVERAGE(C3:C45)”，然后将公式移动到B48单元格。最终效果如图1-13所示。

1.2 认识函数

ExceI提供的函数其实是一些预定义的公式，它们使用一些称为参数的特定数值，按特定的顺序或结构进行计算。用户可以直接用它们对某个区域内的数值进行一系列运算，如分析和处理日期值和时间值、确定贷款的支付额、确定单元格中的数据类型、计算平均值、排序显示和运算文本数据等。例如，使用SUM函数对单元格或单元格区域进行加法运算。

1.2.1 函数的作用

ExceI函数即是预先定义，执行计算和分析等处理数据任务的特殊公式。以常用的求和函数SUM为例，语法结构是“SUM(number1,number2,...)”。其中“SUM”称为函数名称，一个函数只有唯一的一个名称，它决定了函数的功能和用途。函数名称后紧跟左括号，接着是用逗号分隔的称为参数的内容，最后用一个右括号表示函数结束。按照函数的来源，ExceI函数可以分为内置函数和扩展函数两大类。

函数与公式既有区别又互相联系。如果说前者是ExceI预先定义好的特殊公式，后者就是由用户自行设计，对工作表进行计算和处理的公式。

参数是函数中最复杂的组成部分，它规定了函数的运算对象、顺序和结构等，使得用户可以对某个单元格或区域进行处理，如分析存款利息、确定成绩名次和计算三角函数值等。

函数是否可以是多重的呢？也就是说一个函数是否可以是另一个函数的参数呢？当然可以，这就是嵌套函数的含义。所谓嵌套函数，就是指在某些情况下，用户可能需要将某函数作为另一函数的参数使用。例如图1-14所示的公式嵌套使用了AVERAGE函数，将结果与50相比较。这个公式的含义是：如果单元格F2到F5的平均值大于50，则求G2到G5的和，否则显示数值“0”。

使用公式和函数是ExceI 2007中处理数据的最基本的方法。公式是对工作表中的数值执行计算的等式。函数是预先编写的公式，可以对一个或多个值执行运算，并返回一个或多个值。在用公式进行很长或复杂的计算时，函数可以简化和缩短工作表中的公式，甚至还可以实现智能判断，提高工作效率。

1.简化公式

函数可以使一些复杂的公式更易于使用，使复杂的数学表达式输入更加简化。比如对一个班学生的各科成绩求平均值时，使用公式计算量十分庞大，而使用函数就会非常简单。

2.取代特殊运算

在日常的工作中，有一些计算使用的公式非常复杂，而且如果用户不具有特定的专业知识，还无法表达，这时就可以使用现有的函数进行特殊运算。比如，按揭贷款的等额分期偿还额的计算必须用到高等数学的知识，而使用PMT函数就相当简单。

3.实现智能判断

利用函数还可以实现智能判断，根据指定的公式的真假返回不同的值。比如在学校评定成绩等级时，人工逻辑很不方便，这时就可以使用函数来进行自动化处理。

4.提高工作效率

在学校经常会遇到按总分进行排位的问题，当然，用户可以使用“排序”按钮来完成，但是这样做比较麻烦，如果使用排位函数RANK就比较方便，尤其在记录很多、相同分数很多的情况下，不用RANK函数，工作量将不可想像。

1.2.2 函数的结构

知识点讲解

在学习ExceI函数之前，我们需要了解函数的结构。如图1-15所示，函数的结构以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆括号。如果函数以公式的形式出现，则需要在函数名称前面键入等号（=）。

1.2.3 函数的类型

知识点讲解

ExceI函数一共有11类，分别是数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数，以及用户自定义函数。

1.数据库函数

当需要分析数据清单中的数值是否符合特定条件时，可以使用数据库工作表函数。例如，在一个包含销售信息的数据清单中，可以计算出所有销售数值大于1000且小于2500的行或记录的总数。Microsoft ExceI共有12个工作表函数，用于对存储在数据清单或数据库中的数据进行分析，这些函数的统一名称为Dfunctions，也称为D函数，每个函数均有三个相同的参数：database，fieId和criteria。这些参数指向数据库函数所使用的工作表区域。其中参数database为工作表中包含数据清单的区域，参数fieId为需要汇总的列的标志，参数criteria为工作表上包含指定条件的区域。

2.日期与时间函数

通过日期与时间函数，可以在公式中分析和处理日期值和时间值。

3.工程函数

工程工作表函数用于工程分析。这类函数中的大多数可分为三种类型：对复数进行处理的函数、在不同的进制系统（如十进制系统、十六进制系统、八进制系统和二进制系统）间进行数值转换的函数，以及在不同的度量系统中进行数值转换的函数。

4.财务函数

使用财务函数可以进行一般的财务计算，如确定贷款的支付额、投资的未来值或净现值，以及债券或息票的价值。财务函数中常见的参数如下：

未来值 (fv)——在所有付款发生后的投资或贷款的价值。

期间数 (nper)——投资的总支付期间数。

付款 (pmt)——对于一项投资或贷款的定期支付数额。

现值 (pv)——在投资期初的投资或贷款的价值。例如，贷款的现值为所借入的本金数额。

利率 (rate)——投资或贷款的利率或贴现率。

类型 (type)——付款期间内进行支付的间隔，如在月初或月末。

5.信息函数

用户可以使用信息工作表函数确定存储在单元格中的数据的类型。信息函数包含一组称为IS的工作表函数，在单元格满足条件时返回TRUE。例如，如果单元格包含一个偶数值，ISEVEN工作表函数返回TRUE。如果需要确定某个单元格区域中是否存在空白单元格，可以使用COUNTBLANK工作表函数对单元格区域中的空白单元格进行计数，或者使用ISBLANK工作表函数确定区域中的某个单元格是否为空。

6.逻辑函数

使用逻辑函数可以进行真假值判断，或者进行复合检验。例如，可以使用IF函数确定条件为真还是假，并因此返回不同的数值。

7.查找和引用函数

当需要在数据清单或表格中查找特定数值，或者需要查找某一单元格的引用时，可以使用查询和引用工作表函数。例如，如果需要在表格中查找与第一列中的值相匹配的数值，可以使用VLOOKUP工作表函数。如果需要确定数据清单中数值的位置，可以使用MATCH工作表函数。

8.数学和三角函数

通过数学和三角函数，可以处理简单的计算，例如对数字取整、计算单元格区域中的数值总和或复杂计算。

9.统计函数

统计工作表函数用于对数据区域进行统计分析。例如，统计工作表函数可以提供由一组给定值绘制出的直线的相关信息，如直线的斜率和y轴截距，或构成直线的实际点数值等。

10.文本函数

通过文本函数，可以在公式中处理文字串。例如，可以改变大小写或确定文字串的长度，可以将日期插入文字串或连接在文字串上。

11.用户自定义函数

如果要在公式或计算中使用特别复杂的计算，而工作表函数又无法满足需要，则需要创建用户自定义函数。这些函数，称为用户自定义函数，可以通过使用VisuaI Basic for AppIications功能来创建。

12.多重条件函数

ExceI 2007中增加了AVERAGEIF，AVERAGEIFS，SUMIFS，COUNTIFS和IFERROR五个函数，它们都可以在一定范围内根据条件自行计算。特别是多重条件函数AVERAGEIFS，COUNTIFS和SUMIFS给我们的工作带来了极大的方便。例如，在年级段总成绩表中计算各班各科的平均分、及格率和优秀率时，就不用先按班级排序，分班后再计算平均分和“两率”了。

1.2.4 函数参数

知识点讲解

函数右边括号中的部分称为参数，假如一个函数可以使用多个参数，那么参数与参数之间使用半角逗号进行分隔。

参数可以是常量（数字和文本）、逻辑值（如TRUE或FALSE）、数组、错误值（如#N/A）或单元格引用（如D1:F1），甚至可以是另一个或几个函数等。参数的类型和位置必须满足函数语法的要求，否则将返回错误信息。

ExceI函数的参数主要有以下几种。

1.常量

常量是直接输入到单元格或公式中的数字或文本，或由名称所代表的数字或文本值，例如数字“220”、日期“2006-9-2”和文本“数学”都是常量。但是公式或由公式计算出的结果都不是常量，因为只要公式的参数发生了变化，它自身或计算出来的结果就会发生变化。

2.逻辑值

逻辑值是比较特殊的一类参数，它只有TRUE（真）或FALSE（假）两种类型。

例如，在公式“=IF(A3=0,"0",A2/A3)”中，“A3=0”就是一个可以返回TRUE（真）或FALSE（假）两种结果的参数。当“A3=0”为TRUE（真）时，在公式所在单元格中填入“0”，否则在单元格中填入“A2/A3”的计算结果。

3.数组

数组用于可产生多个结果，或可以对存放在行和列中的一组参数进行计算的公式。ExceI中有常量和区域两类数组。前者放在“{}”（按下【CtrI+Shift+Enter】组合键自动生成）内部，而且内部各列的数值要用逗号“,”隔开，各行的数值要用分号“;”隔开。假如你要表示第1行中的50，72，83和第2行中的10，56，40，就应该建立一个2行3列的常量数组“{50,72,83;10,56,40}”。

区域数组是一个矩形的单元格区域，该区域中的单元格共用一个公式。例如，公式“=TREND(B1:B3,A1:A3)”作为数组公式使用时，它所引用的矩形单元格区域“B1:B3,A1:A3”就是一个区域数组。

4.错误值

使用错误值作为参数的函数主要是信息函数。例如，ERROR.TYPE函数就是以错误值作为参数的，语法结构为“ERROR.TYPE(error_vaI)”，如果其中的参数是“#NUM!”，则返回数值6。

5.单元格引用

单元格引用是函数中最常见的参数，引用的目的在于标识工作表单元格或单元格区域，并指明公式或函数所使用的数据的位置，便于它们使用工作表各处的数据，或者在多个函数中使用同一个单元格的数据。另外，还可以引用同一工作簿不同工作表的单元格，甚至引用其他工作簿中的数据。

根据公式所在单元格的位置发生变化时，单元格引用的变化情况，我们可以将引用分为相对引用、绝对引用和混合引用三种类型。以存放在F2单元格中的公式“=SUM(A2:E2)”为例，当公式从F2单元格复制到F3单元格后，公式中的引用也会变化为“=SUM(A3:E3)”。若公式自F列向下继续复制，行标每增加1，公式中的标也自动加1。

如果将上述公式改为“=SUM($A$3:$E$3)”，则无论将公式复制到何处，其引用的始终是A3:E3区域。

混合引用有“绝对列和相对行”与“绝对行和相对列”两种形式。前者如“=SUM($A3:$E3)”，后者如“=SUM(A$3:E$3)”。

上面的几个实例引用的都是同一工作表中的数据，如果要分析同一工作簿中多张工作表上的数据，就要使用三维引用。假如公式放在工作表Sheet1的C6单元格，要引用工作表Sheet2的A1:A6和Sheet3的B2:B9区域进行求和运算，则公式中的引用形式为“=SUM(Sheet2!A1:A6,Sheet3! B2:B9)”。也就是说三维引用中不仅包含单元格或区域引用，还包括在前面加上“!”的工作表名称。

假如需要引用的数据来自另一个工作簿，如工作簿Book1中的SUM函数要绝对引用工作簿Book2中的数据，则公式为“=SUM([Book2]Sheet1! $A$1: $A$8,[Book2]Sheet2! $B$1: $B$9)”，也就是在原来单元格引用的前面加上“[Book2] Sheet1!”。放在中括号里面的是工作簿名称，带“!”的则是其中的工作表名称。跨工作簿引用单元格或区域时，引用对象的前面必须用“!”作为工作表分隔符，再用中括号作为工作簿分隔符。不过三维引用的要受到较多的限制，例如不能使用数组公式等。

上面介绍的是ExceI默认的引用方式，称为“A1引用样式”。如果需要计算处在宏内的行和列，必须使用“R1C1引用样式”。在这种引用样式中，ExceI使用“R”加“行标”和“C”加“列标”的方法指示单元格位置。启用或关闭R1C1引用样式必须打开“ExceI选项”对话框，单击左侧列表框中的“公式”选项，然后在右侧的“使用公式”栏中选中“R1C1引用样式”复选框，单击“确定”按钮，如图1-16所示。由于这种引用样式很少使用，限于篇幅，本文就不做进一步介绍了。

6.嵌套函数

除了上面介绍的情况外，函数也可以是嵌套的，即一个函数是另一个函数的参数。例如“=IF(OR(RIGHTB(E2,1)="1"，RIGHTB(E2,1)="3"，RIGHTB(E2,1)="5"，RIGHTB(E2,1)="7"，RIGHTB(E2,1)="9"),"男","女")”。其中公式中的IF函数使用了嵌套的RIGHTB函数，并将后者返回的结果作为IF的逻辑判断依据。

7.名称和标志

为了更加直观地标识单元格或单元格区域，我们可以给它们赋予一个名称，从而在公式或函数中直接引用。

例如，B2:B46区域存放着学生的物理成绩，求解平均分的公式一般是“=AVERAGE(B2:B46)”。在将B2:B46区域命名为“物理分数”以后，该公式就可以变为“=AVERAGE(物理分数)”，从而使公式变得更加直观。

需要特别说明的是，创建好的名称可以被所有工作表引用，而且引用时不需要在名称前面添加工作表名（这就是使用名称的主要优点），因此名称引用实际上是一种绝对引用。公式引用列标志时的限制较多，它只能在当前数据列的下方引用，不能跨越工作表引用，但是引用列标志的公式在一定条件下可以复制。从本质上讲，名称和标志都是单元格引用的一种方式。因为它们不是文本，使用时名称和标志都不能添加引号。

1.2.5 插入函数

知识点讲解

ExceI公式输入其实可以归结为函数输入的问题，常用的输入函数的方法如下。

1.使用“插入函数”对话框输入函数

通过插入的方式输入函数是ExceI函数应用中经常使用的方法。利用“插入函数”对话框可以帮助用户一步一步地输入一个复杂的函数，并且避免在输入过程中产生输入错误，操作步骤如下。

1 选定要输入函数的单元格。

2 在编辑栏左侧单击“插入函数”按钮，打开“插入函数”对话框，如图1-17所示。

3 从“或选择类别”下拉列表中选择要输入函数的分类，例如选择“统计”选项，然后再从“选择函数”列表框中选择所需要的函数，例如选择求平均数函数“AVERAGE”，如图1-18所示。

4 单击“确定”按钮，会看到所选函数已经出现在单元格中。

5 根据需要输入参数（或者单击“折叠对话框”按钮，在工作表中选择包含要计算平均数的数值的单元格），返回对话框后，单击“确定”按钮，即可完成函数的输入，如图1-19所示。

2.手工输入函数和公式

手工输入函数的方法是：首先选中存放计算结果的单元格，然后使用鼠标单击ExceI编辑栏，按照公式的组成顺序依次输入各个部分，公式输入完毕后，单击编辑栏左侧的“输入”（即对钩标记）按钮（或按【Enter】键）即可。

动手练

下面练习在如图1-20所示的表格的D3单元格中插入求和函数SUM，然后选择单元格区域B3:C3进行求和。

操作步骤如下。

1 选定D3单元格。

2 输入“=SUM()”。

3 将光标插入点定位在小括号中，拖动鼠标选择B3:C3单元格区域。

4 按【Enter】键求和。

1.3 认识运算符

在ExceI中，运算符用于对指定操作数或单元格引用数据执行某种运算，在公式中起着重要的作用。

1.3.1 运算符的类型

知识点讲解

ExceI 2007包含四种类型的运算符：算术运算符、比较运算符、文本运算符和引用运算符。

1.算术运算符

算术运算符是ExceI 2007提供的4种运算符之一。若要完成单元格内数据的数学运算（如加法、减法和乘法等）、合并以及生成数值结果，可以使用算术运算符，ExceI 2007中算术运算符的种类和含义如表1-1所示。

2.比较运算符

使用比较运算符可以比较两个值。当用比较运算符比较两个值时，结果为逻辑值TRUE或FALSE。ExceI 2007中比较运算符的种类和含义如表1-2所示。

3.文本运算符

使用与号（&）可以连接一个或多个文本字符串，生成一段文本，这个与号（&）就是文本运算符，文本运算符的种类和含义如表1-3所示。

4.引用运算符

在公式或函数中，需要引用其他单元格中的数据进行计算时，可以使用引用运算符。ExceI 2007中引用运算符的种类和含义如表1-4所示。

1.3.2 运算符的优先级

知识点讲解

公式一般按特定顺序进行计算，执行计算的顺序会影响公式的返回值。ExceI 2007按照公式中每个运算符的特定顺序从左到右计算公式。

如果一个公式中有若干个运算符，ExceI 2007将按各种运算符的不同优先级依次进行计算。如果一个公式中的若干个运算符具有相同的优先级（例如，一个公式中既有乘号又有除号），ExceI 2007将从左到右进行计算。ExceI 2007运算符的优先级如表1-5所示。

1.3.3 嵌套括号

知识点讲解

因为运算符有优先级，所以若要更改求值的顺序，可以将公式中要先计算的部分用括号括起来。

例如，在公式“=5+2*3”中，优先计算“2*3”，再计算“5+6”，得出结果“11”。

但是，如果用括号将该公式更改为“ =（5+2）*3”，则优先计算的是“5+2”，再计算“7*3”，得出结果“21”。

1.4 公式中的单元格引用

引用的作用在于使用工作表中的单元格或单元格区域，指明公式中所使用的数据的位置。默认情况下，ExceI使用A1引用类型，这种引用类型使用字母标志列、数字标志行，这些字母和数字被称为列标题和行标题。

1.4.1 相对引用

知识点讲解

使用鼠标拖动复制含有公式的单元格，使用的就是单元格区域的相对引用。

相对引用是对相对于公式单元格位于某一位置的单元格的引用。使用相对引用生成其他的公式时，对单元格或单元格区域的引用通常是根据它们与源公式单元格的相对位置来确定的，当把使用了相对引用的公式移动到某个位置时，单元格区域的引用也会发生相应的变化，因此，当复制使用了相对引用的公式时，被粘贴公式中的引用将被更新，指向与目标位置相对应的其他单元格。

例如，E3单元格中的公式为“=SUM(A3:D3)”，当将求和公式从E3单元格复制到E4单元格时，E4单元格的公式变为“=SUM(A4:D4)”，如图1-21所示。公式从E3移动到E4时，引用区域也相应地从A3:D3变为A4:D4区域。

1.4.2 绝对引用

知识点讲解

与相对引用相比，绝对引用只是在单元格引用中加上符号“$”。绝对引用不会随着单元格的移动而发生变化。绝对引用是指将公式复制或者填入到新位置后，公式中引用的单元格地址保持固定不变，这样，公式的计算结果也不会发生变化。

ExceI中，绝对引用是通过对单元格地址的“冻结”来实现的。如果上例中E3单元格中的公式为“=SUM($A$3:$D$3)”，当将求和公式从E3单元格复制到E4单元格时，E4单元格中的公式还是“=SUM($A$3:$D$3)”，公式从E3移动到E4时，引用区域不会发生任何变化，如图1-22所示。

1.4.3 混合引用

知识点讲解

与相对引用和绝对引用相比，混合引用就是具有绝对的列和相对的行，或者绝对的行和相对的列。绝对引用列采用$A1和$B1等形式；绝对引用行采用A$1和B$1等形式。如果公式所在单元格的位置改变，则相对引用改变，而绝对引用不变，即多行或多列地复制公式，相对引用自动调整，而绝对引用不做调整。

例如，将一个混合引用从A2复制到B3，单元格中的公式就会由“=A$1”调整为“=B$1”，如图1-23所示。

1.5 定义与使用名称

名称是代替表格中的单元格、单元格区域、公式或常量的单词或字符串。名称易于理解，即是一种有意义的简略表示法，方便用户引用单元格、常量、公式或单元格区域等。定义了一个名称之后，在公式中就可以使用它了。若使用了没有定义的名称，系统将返回错误值。

1.5.1 定义单元格名称

若需要经常引用某些单元格中的数据，而每次都输入这些单元格的地址，就会很麻烦。此时，可以为这些需要经常引用的单元格起一个名称，在引用时，使用这个名称，就可以免去一个一个地输入单元格地址的麻烦。

知识点讲解

要定义单元格名称，需要先单击“公式”选项卡，然后在“定义的名称”工具组中完成，步骤如下。

1 单击“公式”选项卡，然后单击“定义的名称”工具组中的“定义名称”按钮，打开“新建名称”对话框。

2 在“名称”文本框中输入定义的名称（如“toto”），然后将光标定位到“引用位置”文本框中，按住【Ctrl】键依次选中需要定义名称的单元格（或使用鼠标拖动选中需要定义名称的单元格区域），如图1-24所示。

3 单击“确定”按钮。

以后需要引用定义了名称的单元格时，直接使用定义的名称即可。

ExceI对定义的名称的字符和长度均有一定的要求。

有效字符：名称中的第一个字符必须是字母、下划线或反斜杠（\）。名称中的其余字符可以是字母、数字、句点和下划线等。但是字母“C”、“c”、“R”或“r”不能用来定义名称，因为当在“名称”或“引用位置”文本框中输入这些字母时，会将它们用做当前选定的单元格的行或列的简略表示法。

名称不能与单元格引用相同，不允许使用空格。

名称长度：一个名称最多可以包含255个字符。

区分大小写：名称可以包含大写字母和小写字母（ExceI在名称中不区分大写字母和小写字母）。

1.5.2 使用定义的名称

知识点讲解

定义了某个名称之后，在公式中就可以比较方便地利用它来代替烦琐的单元格区域引用。引用这些单元格时只需使用它们的名称即可。

下面以定义了B3:D9单元格区域的名称为“toto”，在F3单元格中对B3:D9单元格区域数据进行求和为例进行介绍，操作步骤如下。

1 选中F3单元格。

2 在编辑栏输入“=SUM(toto)”。

3 按下【Enter】键，在F3单元格中就会显示出B3:D9单元格区域数据的和，如图1-25所示。

动手练

学习了定义单元格名称和使用单元格名称的方法，下面我们就来动手定义一个单元格名称，并在公式中使用这个名称进行计算。

首先在如图1-26所示的表格中为B3:B9单元格区域定义一个名称“yw”。

然后使用AVERAGE函数和这个名称进行计算，即可求得语文科目的平均成绩（B3:B9），如图1-27所示。

1.5.3 编辑定义的名称

知识点讲解

名称定义后，有时为了更加方便地使用名称，需要更改名称或引用位置。这些操作可通过“名称管理器”对话框进行。

动手练

编辑定义的名称，操作步骤如下。

1 单击“公式”选项卡，然后单击“定义的名称”工具组中的“名称管理器”按钮，打开“名称管理器”对话框，如图1-28所示。

2 在“名称管理器”对话框的列表框中选中需要更改的定义名称项，单击“编辑”按钮，打开“编辑名称”对话框。

3 在“名称”文本框和“引用位置”文本框中根据需要进行设置，然后单击“确定”按钮，如图1-29所示。

1.5.4 使用动态名称

知识点讲解

在使用定义的名称时，若为某一列定义了一个名称，并希望在列中单元格数据发生变化时，定义的名称引用的数据也随之变化，且不引用空单元格，此时可以使用动态名称。

动手练

下面以B列为例，练习如何使用动态名称。

1 单击“公式”选项卡，然后单击“定义的名称”工具组中的“定义名称”按钮，打开“新建名称”对话框。

2 在“名称”文本框中输入定义的名称（如“ALIE”等），然后在“引用位置”文本框中输入“=OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$B:$B),1)”，如图1-30所示。

3 单击“确定”按钮。

1.6 认识公式与函数中的错误

ExceI中，经常会显示一些错误信息，如#N/A!、#VALUE!和#DIV/0!等，出现这些错误的原因有很多，最主要的是由于公式不能计算出正确的结果。

1.6.1 Excel中的常见错误

在ExceI中输入公式后，有时不能正确地计算出结果，会在单元格内显示一个错误信息，这些错误的产生，有的是因公式本身产生的，有的不是。出现错误时，ExceI通常会提示一些错误值，不同的错误值有着不同的含义，每个错误值都有不同的原因和解决方法。

1.####错误

此错误表示列不够宽，或者使用了负日期或时间。

当列宽不足以显示内容时，可以通过以下几种办法纠正：

调整列宽，或直接双击列标题右侧的边界。

缩小内容以适应列宽。

更改单元格的数字格式，使数字适合现在的单元格宽度。例如，可以减少小数点后的小数位数。

当日期和时间为负数时，可以通过以下几种方法纠正：

如果使用的是1900日期系统，那么日期和时间必须为正值。

如果对日期和时间进行减法运算，应确保建立的公式是正确的。

如果公式是正确的，但结果仍然是负值，可以通过将相应单元格的格式设置为非日期或时间格式来显示该值。

2.#VALUE!错误

此错误表示使用的参数或操作数的类型不正确，可能包含以下一种或几种错误：

当公式中需要输入数字或逻辑值（例如TURE或FALSE）时，却输入了文本。

输入或编辑数组公式，没有按【CtrI+Shift+Enter】组合键，而是按了【Enter】键。

将单元格引用、公式或函数作为数组常量输入。

为需要单个值（而不是区域）的运算符或函数提供区域。

在某个矩阵工作表函数中使用了无效的矩阵。

运行的宏程序所输入的函数返回“#VALUE！”。

3.#DIV/0!错误

这种错误表示除数使用了零（0），具体表现在：

输入的公式中包含明显的除以零的计算，如“=5/0”。

使用了对空白单元格或包含零作为除数的单元格引用。

运行的宏中使用了返回“#DIV/0!”的函数或公式。

4.#N/A错误

当数值对函数或公式不可用时，将出现此错误，具体表现在：

缺少数据，在其位置输入了“#N/A”或“NA()”。

为HLOOKUP，LOOKUP，MATCH或VLOOKUP工作表函数的Iookup_vaIue参数赋予了不正确的值。

在未排序的表中使用了VLOOKUP，HLOOKUP或MATCH工作表函数来查找值。

数组公式中使用的参数的行数或列数与包含数组公式的区域的行数或列数不一致。

内置或自定义工作表函数中省略了一个或多个必需参数。

使用的自定义工作表函数不可用。

运行的宏程序所输入的函数返回“#N/A”。

5.#NAME？错误

当ExceI 2007无法识别公式中的文本时，将出现此错误，具体表现在：

使用了EUROCONVERT函数，而没有加载“欧元转换工具”宏。

使用了不存在的名称。

名称拼写错误。

函数名称拼写错误。

在公式中输入文本时没有使用双引号。

区域引用中漏掉了冒号。

引用的另一张工作表未使用的单引号引起。

打开调用用户自定义函数（UDP）的工作簿。

6.#REF!错误

当单元格引用无效时，会出现此错误，具体表现在：

删除了其他公式所引用的单元格，或将已移动的单元格粘贴到了其他公式所引用的单元格上。

使用的对象链接和嵌入链接所指向的程序未运行。

链接到了不可用的动态数据交换（DDE）主题，如“系统”。

运行的宏程序所输入的函数返回“#REF!”。

7.#NUM!错误

如果公式或函数中使用了无效的数值，则会出现此错误，具体表现在：

在需要数字参数的函数中使用了无法接受的参数。

使用了进行迭代的工作表函数（如IRR或RATE），且函数无法得到结果。

输入的公式所得出的数字太大或太小，无法在ExceI 2007中表示。

8.#NULL!错误

如果指定了两个并不相交的区域的交点，则会出现错误，具体表现在：

使用了不正确的区域运算符。

区域不相交。

1.6.2 设置检测规则

知识点讲解

在ExceI 2007中，可以按照用户的需要设置错误检查规则，步骤如下。

1 单击“Office”按钮，在弹出的“Office”菜单中单击“Excel选项”按钮，打开“Excel选项”对话框。

2 单击左侧的“公式”选项卡，在右侧的“错误检查规则”栏中选中相应的复选框，即可改变错误检查规则，如图1-31所示。

这些复选框的具体含义如下。

【所含公式导致错误的单元格】：选中该复选框，则当公式未使用它应该使用的语法、参数或数据类型时，将出现#DIV/0！、#N/A、#NAME？、#NULL！、#NUM！、#REF！和#VALUE！等错误。

【表中不一致的计算列公式】：选中该复选框，则当计算列公式不一致时，将出现错误值。

【包含以两位数表示的年份的单元格】：选中该复选框，则当用两位数表示年份时，将出现错误值。

【文本格式的数字或者前面有撇号的数字】：单元格包含存储为文本的数字（这些数字通常来自通过其他方式导入的数据）。

【与区域中的其他公式不一致的公式】：公式与区域中其他公式的模式不匹配。许多情况下，相邻公式的差别只在于各自的引用不同。

【遗漏了区域中的单元格的公式】：公式可能包括错误引用。如果公式引用了一个单元格区域，并且用户向该区域的底部或右侧添加了单元格，引用可能不再正确。公式并不总是自动更新其引用以包含新的单元格。此规则将公式中的引用与相邻单元格进行比较，如果相邻单元格包含多个数字（不是空单元格），则提示该错误。

【包含公式的未锁定单元格】：未锁定单元格，取消对公式的保护。默认情况下，所有单元格均被锁定以对公式进行保护。当公式受到保护时，如果不取消保护，就无法对其进行修改。对包含公式的单元格进行保护可以防止这些单元格被更改，并且有助于避免将来出错。

【引用空单元格的公式】：公式含有对空白单元格的引用，这样可能会导致意想不到的结果。

【表中输入的无效数据】：表中存在无效数据错误。

1.6.3 追踪单元格

在ExceI 2007中，当公式使用引用单元格或从属单元格时（特别是使用交叉引用关系很复杂的公式时），检查其准确性或查找错误的根源会很困难。

知识点讲解

为了方便公式检查，可以使用“追踪引用单元格”和“追踪从属单元格”命令，以图形方式显示或追踪这些单元格与包含追踪箭头的公式之间的关系。单元格追踪器是一项分析数据流向和纠正错误的重要工具，可用来分析公式中用到的数据来源。

1.在同一工作表中追踪单元格

如果公式或函数的引用单元格或从属单元格在同一工作表中，追踪单元格就比较方便。

使用单元格追踪器可以方便地查看公式的数据来源。下面以在“土方工程结算表”工作表中追踪土方值的计算公式的来源为例进行介绍，操作如下。

1 打开“土方工程结算表”，如图1-32所示。

2 选择F3单元格，单击“公式”选项卡。

3 单击“公式审核”工具组中的“追踪引用单元格”按钮，引用单元格的追踪箭头如图1-33所示。

4 蓝色圆点表示当前选中的单元格的引用单元格，蓝色箭头表示当前选中的单元格是从属单元格，只要双击箭头就可选择箭头的另一端的单元格。

2.在不同工作表中追踪单元格

如果公式或函数的引用单元格和从属单元格不在同一工作表中，追踪单元格、显示其关系要通过“定位”对话框来完成。

如果引用单元格或从属单元格在另一个工作表或工作簿也可以显示其关系。下面以在“奖金发放表”工作表中查看金额与各科分数之间的关系为例，操作步骤如下。

1 打开“奖金发放表”工作表，如图1-34所示。

2 选择B4单元格，单击“公式”选项卡。

3 单击“公式审核”工具组中的“追踪引用单元格”按钮，单元格之间的关系箭头如图1-35所示。

4 双击箭头，打开“定位”对话框，如图1-36所示。

5 在“定位”列表框中双击要查看的引用，引用单元格显示如图1-37所示。

如果对箭头所指向的公式进行了修改，或者插入或删除了行或列、删除或移动了单元格，则所有追踪箭头都将消失。如果要在执行上述任意更改后重现追踪箭头，则必须在工作表中再次使用追踪命令。如果希望跟踪原始箭头，需要在更改前将工作表连同显示的追踪箭头一起打印出来。

动手练

如图1-38所示的表格中的B10单元格中的公式引用的是同一工作表中的单元格；B11单元格中引用的是其他工作表中的单元格。现在分别追踪这两个公式中引用的单元格。

首先追踪B10单元格中公式引用的单元格，效果如图1-39所示。

接着，单击“公式审核”工具组中的“追踪引用单元格”按钮，追踪B11单元格中公式引用的单元格。然后双击单元格之间的关系箭头，打开“定位”对话框，如图1-40所示。

在“定位”列表框中双击要查看的引用，引用单元格就显示出来了，如图1-41所示。

疑难解答

问 需要大量在单元格中使用同一公式时，有没有能够一次性完成而不需要重复输入公式的方法呢？

答 首先选定所有需要使用同一公式的单元格或单元格区域，在编辑栏键入公式，然后在按住【Ctrl】键的同时按【Enter】键，就可以一次性在选定的所有单元格中输入同一公式了。

问 如何学习和解读公式？

答 有以下几种方法：

多看函数帮助。各个函数帮助里面有函数的基本用法和一些要点，以及对数据排序和引用类型等的要求。

拆分理解函数。函数的参数之间有逗号隔开，这些逗号就是公式的关节。先把长公式拆分开，逐个看明白了之后再拼凑起来，就容易理解了。

善用【F9】键。选中公式的一部分然后按【F9】键，可单独测试公式的这一部分。【F9】键尤其对数组公式有非常有用。

善用公式审核。公式审核与【F9】键的功能基本相同，能一步步地看公式运行的结果。

注意定义名称。使用定义名称，公式会显得简洁、直观。按下【CtrI+F3】组合键可以查看定义名称，如果名称是对单元格区域的引用，则选中名称后，单击对话框右下角的“折叠对话框”按钮，会在表格中的相应区域出现虚线选择框。

问 如何使用数组公式？

答 Excel中数组公式非常有用，使用它可以建立产生多值或对一组值而不是单个值进行操作的公式。要输入数组公式，首先必须选择用来存放结果的单元格区域，在编辑栏输入公式，然后按【Ctrl＋Shift＋Enter】组合键锁定数组公式，Excel将在公式两边自动加上括号（{}），注意，不要自己键入括号，否则，Excel会认为输入的是一个正文标签。要编辑或清除数组公式，需选择数组区域并且激活编辑栏，公式两边的括号会消失，然后编辑或清除公式，最后按【Ctrl＋Shift＋Enter】组合键即可。