2.2 公式
本节介绍公式的一些基本概念和语法，然后详细介绍如何建立、修改、移动和复制公式，以及公式的引用，公式的错误与审核，用数组计算等内容。
2.2.1 公式概述
公式主要用于计算。可以说，没有公式的Excel就没有使用价值。使用公式可以进行简单的计算，如加、减、乘、除等；也可以完成很复杂的计算，如财务、统计和科学计算等；还可以使用公式进行比较或者操作文本和字符串。工作表中需要计算结果时，使用公式是最好的选择。
简单地说，公式就是一个等式，或者说是连续的一组数据和运算符组成的序列。
考察以下公式：
=10*2/3+4
=SUM（Al:A3）
=B5&C6
第一个公式是用户所熟悉的，只是等号左边是省略的单元格。第二、第三个公式可能以前没有接触过。在Excel中，公式有其本身的特点，并且有自己的规定，或者叫做语法。
在工作表单元格中输入公式以后，公式的结果就会显示在工作表中。要想查看产生结果的公式，只需选中该单元格，公式就会出现在公式栏中。要在单元格中编辑公式，双击该单元格或者按F2键。
操作实例2-1查看工作表中所有的公式内容
（1）选择“工具”|“选项”命令，打开“选项”对话框。
（2）将“选项”对话框切换到“视图”选项卡，选中“公式”复选框。
（3）单击“确定”按钮完成操作。
这时公式就出现在单元格中。在显示公式与结果之间切换的快捷键是“Ctrl+`”。一般用户都不想显示工作表中的公式，但调试工作表时查看工作表的公式是很有用的。
下面介绍公式中的运算符和公式的运算顺序。
1．运算符
在Excel中，运算符可以分为4类：算术运算符、比较运算符、文本运算符和引用运算符。
用户通过算术运算符可以完成基本的数学运算，如加、减、乘、除、乘方和求百分数等，如表2-1所示的是Excel中所有的算术运算符。
表2-1 Excel中的算术运算符
比较运算符用于比较两个数值，并产生逻辑值TRUE和FALSE，如表2-2所示的是Excel中所有的比较运算符。
表2-2 Excel中的比较运算符
文本运算符可以将一个或者多个文本连接为一个组合文本。文本运算符只有一个：&，其含义是将两个文本值连接或串联起来产生一个连续的文本值，如CLASS&ROOM的结果是CLASSROOM。
引用运算符可以将单元格区域合并运算，如表2-3所示的是Excel中所有的引用运算符。
表2-3 Excel中的引用运算符
2．运算顺序
当公式中既有加法，又有乘法、除法，还有乘方，Excel是怎样确定其运算先后顺序的呢？这就需要理解运算符的运算顺序，也就是运算符的优先级。对于同级运算，则按照由等号开始从左到右进行运算；对于不同级的运算符，则按照运算符的优先级进行运算。如表2-4所示的是常用运算符的运算优先级。
表2-4 公式中运算符的优先级
3．文本运算
文本运算符（&）用于连接字符串，例如，公式=“我爱”&“伟大的”&“中国”的结果是“我爱伟大的中国”。当然，文本运算符还可以连接数字，例如，公式=12&34的结果是1234字符串。
注意：
当用&来连接数字时，数字串两边的双引号可以没有，但对于连接一般的字母、字符串和文本时，双引号不可去掉，否则公式将返回错误值。
4．比较运算
比较运算符可以对两个数字或者两个字符串进行比较，以产生逻辑值TRUE或FALSE。例如，公式=200<400的结果是TRUE；而公式=100>400的结果则是FALSE。
注意：
用比较运算符对字符串进行比较的时候，Excel会先将字符串转化成内部的ASCII码，然后再作比较。因此公式=“AB”>“BC”的结果是FALSE。
5．数值转换
在公式中，每个运算符都需要特定类型的数值与之对应。如果输入数值的类型与所需的类型不同，Excel有时可以对这个数值进行转换。下面举几个例子来说明公式中数值的转换。
例如，公式=“1”+“2”，产生的结果是3。这是因为使用（+）时，Excel会认为公式中运算项为数值。虽然公式中的引号说明“1”和“2”是文本型数字，但Excel会自动将文本数字转换成数值。又例如，公式=“1”+“$2.00”，结果也是3，其原因与此相同。又例如，使用函数的公式=SQRT（“9”），则公式也会先将字符“9”转换成数值9，然后再计算SQRT（）函数，即对9开方（有关函数的使用参看本章后面的章节），得到结果为3。
例如，公式="A"&TRUE，产生的结果是ATRUE。这是因为需要文本时，Excel会自动将数值和逻辑型值转换成文本。
6．日期和时间
在Excel中，不仅可以对数字或者字符进行运算，同样可以对日期进行运算。Excel会将日期存储为一系列的序列数，而将时间存储为小数，因为时间可以被看成日期的一部分。
用户可以用一个日期减去另外一个日期来计算两个日期的差值。例如，公式="98/10/1" -"97/8/1"的结果为426。即1998年10月1日和1997年8月1日之间相差426天。
同样可以对日期进行其他的混合运算，例如，公式="98/10/1"-"97/8/1"/"98/10/1"的结果为36068.01。
提示：
当在Excel中输入日期，并且年份输入为两位数时，Excel会将在00～29之间的输入数解释为2003～2029年，而将在30～99之间的输入数解释为1930～1999年。例如，对于10/10/1, Excel会认为这个日期为2010年10月1日，而将95/10/1认为是1995年10月1日。
7．语法
所谓公式的语法，就是公式中元素的结构或者顺序。Excel中的公式遵循一个特定的语法：最前面是等号“=”，后面是参与运算的元素和运算符。元素可以是常量数值、单元格引用、标志名称以及工作表函数。
2.2.2 公式的基本操作
公式的运用在Excel中占有很重要的地位。下面介绍公式的一些基本常用操作。
1．建立公式
公式的建立在前面的一些例子中都提到过，这一节将正式介绍怎样通过键盘和公式选项板来创建公式。
（1）输入公式
用键盘创建公式的操作步骤如下：
[1] 选择要输入公式的单元格。
[2] 先输入等号“=”，然后输入计算表达式；如果使用的是函数向导向单元格输入公式，Excel会自动在公式前面插入等号。
[3] 按Enter键完成公式的输入。
注意：
如果在某一区域内输入同一个公式，单个输入显然太慢了。这时可以选中该单元格区域，输入所需要的公式，然后按Ctrl+Enter键，则Excel会自动将所有单元格都粘贴上该输入公式。这不仅对公式有效，而且对其他文本和字符都有效。
（2）公式选项板
如果创建含有函数的公式，那么公式选项板有助于输入工作表函数和公式。
要显示公式选项板，可以单击编辑栏中的按钮
，当在公式中输入函数时，公式选项板会显示函数的名称、函数中的每个参数、函数的当前结果和整个公式的结果等。“函数参数”对话框，如图2-74所示。
图2-74 “函数参数”对话框
下面以计算10、25、30的平均值为例来说明公式选项板的使用，这里要借助AVERAGE函数。
操作实例2-2平均值的计算
[1] 选中一个单元格。
[2] 在编辑栏中输入“=AVERAGE（ ）”，如图2-75所示，此时为了预览结果，可以用公式选项板。
图2-75 在编辑栏中输入函数公式
[3] 单击编辑栏中的
按钮，将会弹出公式选项板，并且公式选项板上会自动增加AVERAGE函数的使用选项。
[4] 在公式选项板的函数AVERAGE参数栏中分别输入10、25和30。
[5] 输入完毕后，计算结果将出现在公式选项板上，如图2-76所示。
图2-76 输入函数的公式选项板
[6] 单击“确定”按钮完成公式的输入，完整的公式将出现在编辑栏中，而计算结果就会显示在所选单元格上。
2．修改公式
如果发现某个公式有错误，就必须对该公式进行修改。对公式的修改非常简单，下面通过操作实例2-3进行介绍。
操作实例2-3学会修改公式
（1）单击包含需要修改公式的单元格。
（2）在编辑栏中对公式进行修改。如果需要修改公式中的函数，则替换或修改函数的参数。
3．公式的移动和复制
如果要将含有公式的单元格整个（包括格式、边框等）移动或者复制到另外的单元格或区域，可以按照前面章节介绍的移动和复制单元格的方法，也可以只粘贴单元格的公式。
如图2-77所示，在单元格A1中有一个公式“=40+50*3”，现在要将它移动或者复制到C3单元格，可以按照如下步骤进行操作。
图2-77 单元格中的公式
操作实例2-4单元格公式的粘贴
（1）单击A1单元格。
（2）单击“常用”工具栏中的“剪切”（进行移动操作）或者“复制”按钮（进行复制操作）。
（3）在C4单元格上单击鼠标右键，在弹出的快捷菜单中选择“选择性粘贴”命令，打开“选择性粘贴”对话框，如图2-78所示。
图2-78 “选择性粘贴”对话框
（4）在“选择性粘贴”对话框中选中“公式”单选按钮。
（5）单击“确定”按钮，完成移动或者复制操作。
2.2.3 公式的引用
每个单元格都有自己的行、列坐标位置，在Excel中将单元格行、列坐标位置称之为单元格引用。在公式中可以通过引用来代替单元格中的实际数值。在公式中不但可以引用本工作簿中任何一个工作表中任何单元格或单元格组的数据，也可以引用其他工作簿中的任何单元格或者单元格组的数据。
引用单元格数据以后，公式的运算值将随着被引用的单元格数据变化而变化。当被引用的单元格数据被修改后，公式的运算值将自动修改。
1．引用的类型
为满足用户的需要，Excel提供了3种不同的引用类型：相对引用、绝对引用和混合引用。在引用单元格数据时，要弄清这3种引用类型。
（1）绝对引用
绝对引用是指被引用的单元格与引用的单元格的位置关系是绝对的，无论将这个公式粘贴到哪个单元格，公式所引用的还是原来单元格的数据。绝对引用的单元格名称的行和列前都有符号“$”，例如，$A$l, $D$2等。
（2）相对引用
相对引用的格式是直接用单元格或者单元格区域名，而不加符号“$”，例如，A1, D2等。使用相对引用后，系统将会记住建立公式的单元格和被引用的单元格的相对位置关系，在粘贴这个公式时，新的公式单元格和被引用的单元格仍保持这种相对位置。
如图2-79所示的是包含4位学生成绩的成绩表。要计算4个人各科的平均分和总评成绩。
图2-79 成绩表
计算平均分的公式是：4人成绩的平均值。
计算总评成绩的公式是：总评成绩=语文*30%+数学*30%+英语*30%+体育*10%；各科在总评中所占比例已经列于第二行。
操作实例2-5绝对引用和相对引用
[1] 在单元格B9中输入公式“=AVERAGE（B5, B6, B7, B8）”；在这个公式中，对单元格B5～B8都使用了相对引用。
[2] 确定以后，可以在单元格B9中得到计算结果。下面将单元格B9的公式复制到C9、D9、E9、F9中，复制完成后，用户就会发现这些单元格中的公式与B9单元格的公式不同了。原来公式中的B分别被改为C、D、E和F。这就是相对引用。
[3] 接下来计算总评成绩。
在单元格F5中输入公式“=B5*$B$2+C5*$C$2+D5*$D$2+E5*$E$2”。
在这个公式中，对单元格B5～D5都使用了相对引用，而对B2～D2则采用了绝对引用。下面将单元格F5的公式复制到F6、F7和F8中，复制完成后，可以发现这些单元格中，公式相对引用的单元格名称变了，而绝对引用的单元格名称没有改变。这时可以按“Ctrl+`”快捷键（用来切换查看公式还是公式的结果的快捷键）来查看工作表的所有公式。
（3）混合引用
若符号$在数字之前，而字母前没有$，那么被应用的单元格的行位置是绝对的，列位置是相对的。反之，行的位置是相对的，而列的位置是绝对的。这就是混合引用，例如，$E3或者E$3。
2．引用同一工作簿中的单元格
在当前工作表中可以引用其他工作表中单元格的内容。例如，当前的工作表是Sheetl，如果要在A1单元格中引用工作表Sheet3中B6:B8的内容之和，有以下两种方法。
（1）直接输入。在Sheetl中选择A1单元格，输入“=SUM（Sheet3! B6:B8）”，然后按Enter键。
（2）用鼠标选择需要引用的单元格。在Sheetl中选择A1单元格，输入“=SUM（”；单击Sheet3工作表的标签；在Sheet3中选择B6:B8单元格，然后按Enter键。
注意：
当编辑栏中显示Sheetl中A1单元格的公式“=SUM（Sheet3! B6:B8”时，此公式还缺少一个“）”，这时可以在编辑栏中补上“）”，也可以直接按Enter键，Excel会自动加上一个“）”。
3．引用其他工作簿中的单元格
在当前工作表中可以引用其他工作簿中的单元格或者单元格区域的数据或者公式。例如，当前的工作簿是“工作簿2”，如果“工作簿”的Sheet l工作表中的A1单元格要引用“工作簿1”（文件存放的路径是“C:\My Documetns\工作簿1.xls”中的$B$3:$B$4单元格中的数据），可以按以下步骤进行操作。
（1）直接输入。在Sheet l中选择A1单元格，输入“= SUM（'C:\My Documetns\[工作簿1.xls]Sheet1'! $B$3:$B$4）”，然后按Enter键。
（2）选择需要引用的单元格。在Sheet l中选择A1单元格，输入“=SUM（”，打开“工作簿1”，在其中单击工作表Sheetl的标签，在Sheetl中选择$B$3:$B$4单元格，然后按Enter键，关闭“工作簿1”。
为了便于操作和观察，可以选择“窗口”|“重排窗口”命令，接着单击“确认”按钮或者按Enter键，使“工作簿1”和“工作簿2”同时显示在屏幕上，然后再进行上述的操作。
2.2.4 公式的错误与审核
审核公式对公式的正确性来说至关重要，它包括循环引用、公式返回的错误值、审核及检查等内容。
1．循环引用
使用公式时引用公式自身所在的单元格，这时公式将把它视为循环引用。所谓公式的循环引用，指的是公式直接或者间接引用该公式所在的单元格的数值。在计算循环引用的公式时，Excel必须使用前一次迭代的结果来计算循环引用中的每个单元格。而迭代的意思就是重复工作表直到满足特定的数值条件。如果不改变迭代的默认设置，Excel将在100次迭代以后或者两个相邻迭代得到的数值变化小于0.001时停止迭代运算。
在使用循环引用时，可以根据需要来设置迭代的次数和迭代的最大误差，在Excel中默认的迭代次数为100次。
操作实例2-6更改默认的迭代设置
其具体操作步骤如下：
（1）选择“工具”|“选项”命令，打开如图2-80所示的“选项”对话框。
图2-80 更改默认的迭代设置
（2）单击“重新计算”标签，打开“重新计算”选项卡。
（3）选中“迭代计算”复选框。
（4）根据需要在“最多迭代次数”文本框中和“最大误差”文本框中输入进行迭代运算时的最多迭代次数和最大误差。
（5）单击“确定”按钮完成操作。
2．公式返回的错误值
如果输入的公式不符合格式或者其他要求，就无法在Excel工作表的单元格中显示运算结果，该单元格中会显示错误值信息，如“#####! ”、“#DIV/01”、“#N/A”、“# NAME? ”、“#NULL! ”、“#NUM! ”、“#REF! ”、“#VALUE! ”。了解这些错误值信息的含义有助于用户修改单元格中的公式。如表2-5所示的是Excel中的错误值及其含义。
表2-5 错误值及其含义
3．审核及检查
Excel提供了公式审核功能，使用户可以跟踪选定范围中公式的引用或者从属单元格，也可以追踪错误。使用这些功能的操作方法为：选中需要审核的公式所在的单元格，然后选择“工具”|“公式审核”命令，如图2-81所示，弹出的子菜单中包含了审核公式功能的各种命令。
图2-81 “公式审核”子菜单
如果需要显示公式引用过的单元格，在图2-81的菜单中选择“追踪引用单元格”命令，或者单击“公式审核”工具栏中的“追踪引用单元格”按钮。这时公式所引用过的单元格就会有追踪箭头指向公式所在的单元格。取消该追踪箭头的方法是单击“公式审核”工具栏中的“移去追踪引用单元格箭头”按钮。
如果需要显示某单元格被哪些单元格的公式引用，可以在图2-81的菜单中选择“追踪从属单元格”命令，或者单击“公式审核”工具栏中的“追踪从属单元格”按钮。这时该单元格就会产生指向引用它的公式所在单元格的追踪箭头。在删除单元格之前，最好使用这种方法来检查该单元格是否已被其他公式所引用。单击“公式审核”工具栏中的“移去追踪从属单元格箭头”按钮可取消追踪箭头。
当单元格显示错误值时，选择“公式审核”子菜单中的“追踪错误”命令或者单击“公式审核”工具栏中的“追踪错误”按钮，即可追踪出产生错误的单元格。
要取消上述的所有追踪箭头，可以在“公式审核”子菜单中选择“取消所有追踪箭头”命令或者单击“公式审核”工具栏中的“取消所有追踪箭头”按钮。
要显示“审核”工具栏，可以在“公式审核”子菜单中选择“显示‘公式审核’工具栏”命令。要想隐藏“审核”工具栏，重新选择“公式审核”子菜单中的“显示‘公式审核’工具栏”命令即可。
2.2.5 数组计算
数组是一组公式或值的长方形范围，Excel视数组为一组。有些数组公式返回一组出现在很多单元格中的结果。数组是小空间进行大量计算的强有力的方法。它可以代替很多重复的公式。
1．输入数组公式
操作实例2-7输入数组公式
其具体操作步骤如下：
（1）选中需要输入数组公式的单元格或者单元格区域。
（2）输入公式的内容。
（3）按Shift + Ctrl + Enter快捷键结束输入。
输入数组公式其实是一个非常简单的操作过程，但要理解它并不容易。下面举例来帮助用户理解怎样建立数组公式。
如图2-82所示的内容，要在C列得到A列和B列1～4行相乘的结果，这当然可以在C1单元格输入公式“=Al*B1”，然后复制。现在要使用数组的方法得到这些结果，这时，A1～A4和B1～B4的数据就是数组的参数。其具体步骤如下：
图2-82 数组参数
选定C1～C4单元格区域（注意：4个单元格全部选中），然后在编辑栏中输入公式“=Al:A4*B1:B4”，按Shift + Ctrl + Enter快捷键结束输入，得到如图2-83所示的结果。
图2-83 返回多个结果
注意：
数组公式如果返回多个结果，当删除数组公式时，必须删除整个数组公式，即选中整个数组公式所在单元格区域然后再删除，不能只删除数组公式的一部分。
2．选中数组范围
通常，输入数组公式的范围，其大小与外形应该与作为输入数据的范围的大小和外形相同。如果存放结果的范围太小，就看不到所有的结果；如果范围太大，有些单元格中就会出现不必要的“#N/A”错误。因此，选择的数组公式的范围必须与数组参数的范围一致。
3．数组常量
在数组公式中，通常都使用单元格区域引用，也可以直接输入数值数组。直接输入的数值数组被称为数组常量。当不想在工作表中逐个单元格输入数值时，可以使用这种方法来建立数组常量。
可以使用以下的方法来建立数组中的数组常量：直接在公式中输入数值，并且用大括号“{}”括起来，需要注意的是，把不同列的数值用逗号“, ”分开，不同行的数值用分号“; ”分开。例如，如果要表示一行中的100、200、300和下一行中的400、500、600，应该输入一个2行3列的数组常量{100,200,300;400,500,600}。
在实际应用中，先选中一个2行3列的矩形区域，然后输入公式={100,200,300;400,500,600}，按Shift + Ctrl + Enter快捷键结束输入，则在这个2行3列的矩形区域即可一次得到所需要的数值，如图2-84所示。
图2-84 数组常量举例
数组常量有其输入的规范，因此，无论在单元格中输入数组常量还是直接在公式中输入数组常量，并非随便输入一个数值或者公式就可以了。
在Excel中，使用数组常量时应该注意以下规定。
数组常量中不能含有单元格引用，并且数组常量的列或者行的长度必须相等。
数组常量可以包括数字、文本、逻辑值FALSE和TRUE以及错误值，如“#NAME? ”。
数组常量中的数字可以是整数、小数或者科学记数公式。
在同一数组中可以有不同类型的数值，如{1,2, “A”, TRUE}。
数组常量中的数值不能是公式，必须是常量，并且不能含有$、（ ）或者%。
文本必须包含在双引号内，如“CLASSROOMS”。