1.1 公式基础知识

简单地说，公式是指以等号“=”开头，用运算符将多个元素连接起来的数学表达式。一个公式的最大字符允许长度，对于Excel 2003来说是1024个字符，对于Excel 2007来说是8192个字符。

在Excel中，凡是在单元格中先输入等号（=）然后再输入其他数据，系统就自动将其判为公式。例如，若在某单元格中输入了“=100”，尽管该单元格显示出的数据为100，但它的真正面目并不是数字100，而是一个公式，其计算结果是100。

从本质上来说，函数也是公式，因此，如果要在单元格中使用函数进行计算，就必须先输入等号，然后再输入函数名称及相应的参数。例如，要计算单元格区域B2:B30的数据之和，必须输入公式“=SUM（B2:B30）”，而不能仅仅输入“SUM（B2:B30）”。

1.1.1 Excel公式的构成

输入到Excel单元格中的计算公式可由以下几种基本元素组成。

● 等号（=）：任何公式前面必须是以等号（=）开头。

● 运算符：运算符是将多个参与计算的元素连接起来的运算符号，Excel 公式的运算符包括引用运算符、算术运算符、文本运算符和比较运算符。

● 常数或字符串：常数是指值永远不变的数据，如10.02，2000等；字符串是指用双引号引起来的文本，如"47838"，"日期"等。

● 数组：在公式中还可以使用数组以创建更加复杂的公式。例如，公式“=MMULT （{1,2,3;4,5,6},{8;9;11}）”的功能是计算两个矩阵{1,2,3;4,5,6}和{8;9;11}的乘积，得到一个新的矩阵，这里在函数 MMULT中，就使用了常量数组{1,2,3;4,5,6}和{8;9;11}。

● 单元格引用：单元格引用是指以单元格地址或名称来代表单元格中的数据进行计算。例如，公式“=A1+B2+200”的含义是将单元格A1的数据和B2的数据，以及常数200相加；公式“=SUM（销售量）”的含义是利用函数SUM对名称“销售量”所代表的单元格区域进行加总计算。

● 工作表函数和它们的参数：公式的元素可以是函数，例如公式“=SUM（A1:A10）”就使用了函数SUM。

● 括号：括号主要用于控制公式中各元素运算的先后顺序。要注意区别函数中的括号，函数中的括号是函数的不可分割的一部分。

1.1.2 公式的运算符

Excel 公式的运算符有引用运算符、算术运算符、文本运算符和比较运算符。下面简要介绍这些公式运算符的含义及其使用方法。

1.引用运算符

引用运算符用于对单元格区域进行合并计算。常见的引用运算符有冒号（:）、逗号（,）和空格。

● 冒号（:）是区域运算符，用于对两个引用单元格之间所有单元格进行引用，如A1:B10表示以A1为左上角、B10为右下角的连续单元格区域；A:A表示整个A列；5:5表示第5行。

● 逗号（,）是联合运算符，用于将多个引用合并。例如，公式“=SUM（A2:A3,A5:D5,E2）”用于计算单元格区域A2:A3和A5:D5，以及单元格E2的数据总和。

● 空格是交叉运算符，用于对两个单元格区域的交叉单元格进行引用，例如，公式“=B5:C5 C5:D5”的结果为返回C5单元格的数据。

2.算术运算符

算术运算符用于完成基本的算术运算，按运算的先后顺序，算术运算符有负号（-）、百分号（%）、幂（^）、乘（*）、除（/）、加（+）、减（-）。

例如，公式“=A1*B1+C1”的含义是将单元格A1和B1数据相乘后再加上单元格C1数据。公式“=-A1”的含义是将单元格A1的数据转变为负数后输入到含有该公式的单元格。

3.文本运算符

文本运算符用于将两个或多个值连接起来产生一个连续的文本值，文本运算符主要是文本连接运算符“&”。例如，公式“=A1&A2&A3”的含义是将单元格A1、A2、A3的数据连接起来组成一个新的文本。

4.比较运算符

比较运算符用于比较两个值，并返回逻辑值TRUE（真）或FLASE（假）。比较运算符包括等于（=）、小于（＜）、小于等于（＜=）、大于（＞）、大于等于（＞=）、不等于（＜＞）。当用这些运算符比较两个值时，计算结果为逻辑值TRUE或 FALSE。

例如，公式“=A1=A2”的含义是比较单元格A1和A2的值，如果A1的值等于A2的值，将返回TRUE，否则将返回FALSE。注意这个公式的左边第一个等号是公式的等号，而第二个等号是比较运算符。

需要注意的是，在对英文字符串利用上述比较运算符进行比较时，是不区分字母大小写的。例如，假设在单元格A1中的数据为字母“a”，在单元格A2中的数据为字母“A”，那么公式“=A1=A2”的运算结果会是TRUE。

1.1.3 引用单元格的方式

在Excel表格中要对数据进行计算，既可以在公式中输入数值，也可以输入某数值所在的单元格地址，还可以输入单元格的名称，这种通过输入单元格地址或单元格名称来获取单元格中的数据的方式称为单元格的引用。

例如，公式“=B5*D6+A4”的含义是，把单元格B5的数据与单元格D6的数据相乘后再加上单元格A4的数据，这里的公式就对单元格B5、D6、A4进行了引用。

公式“=SUM（销售额）”的含义是对名称为“销售额”代表的单元格区域进行加总计算，这里就是引用了名称为“销售额”代表的单元格区域。

如果仅需要在一个单元格中输入公式，并不需要对公式进行复制，那么在公式中以什么方式对单元格进行引用是无关紧要的。但是，如果在某个单元格中输入完公式以后，还想要进一步将该单元格中的公式复制到其他单元格或单元格区域，那么就必须首先了解公式中对单元格的引用采用的是什么引用方式，以免得到的公式不是所希望得到的结果。

根据公式所在单元格与被引用的单元格之间的位置关系，可以将公式中引用单元格的方式分为以下四种。

1.相对引用

相对引用是指对单元格的引用会随公式所在位置的变化而改变，公式的值将会依据更改后单元格地址的值重新计算。相对引用的表现形式是用列标和行号直接表示单元格地址，如A2、B5等，这样表示的单元格地址称为相对地址。在这种引用方式下，当某个单元格的公式被复制到另一个单元格时，原单元格内公式中的地址在新的单元格中就要发生变化，但其引用的单元格地址之间的相对位置间距保持不变。

在默认情况下，输入新公式时采用相对引用方式。

2.绝对引用

绝对引用是指公式中的单元格或单元格区域地址不随着公式位置的改变而发生改变，无论含有公式的单元格处在什么位置，公式中所引用的单元格位置都是其在工作表中的确切位置。绝对引用的表现形式是，在表示单元格的列标和行号前加“$”符号，这样表示的单元格地址称为绝对地址。绝对引用方式的特点是在将含有公式的单元格复制到新的单元格时，公式中引用的单元格地址始终保持不变。

例如，在某单元格中输入了公式“=SUM（$A$1:$A$10）”，无论将该公式复制到何处，函数SUM计算的区域总是单元格区域A1:A10，即对该区域的数据进行加总计算。

3.混合引用

混合引用是指列固定不变而行不固定，或行固定不变而列不固定的单元格引用方式，即混合引用包括绝对列和相对行或者绝对行和相对列两种引用情况。绝对引用列采用$A1、$B1等形式，也就是对于列采用绝对引用方式，而对于行采用相对引用方式；绝对引用行采用A$1、B$1等形式，也就是对于行采用绝对引用方式，而对于列采用相对引用方式。

如果公式所在单元格的位置改变，则相对引用将改变，而绝对引用将不变。如果多行或多列地复制或填充公式，相对引用将自动调整，而绝对引用将不做调整。

例如，假设单元格A2的公式是“=A$1”，那么当将单元格A2复制到单元格B3时，单元格B3的计算公式就会调整为“=B$1”。

在需要快速输入大量公式，而这些公式中总是引用某个固定的行或固定的列时，混合引用方式会是非常有用的。

4.三维引用

三维引用是指在一个工作簿中，从公式所在的工作表以外的其他工作表中引用单元格，或者从公式所在的工作簿以外的其他工作簿中引用单元格。

当从同一工作簿的其他工作表中引用单元格时，应在引用工作表的名称后面加一个惊叹号（！），然后再接单元格地址，例如“=Sheet2!B5”。

如果工作表名称是数字或者是以数字开头的字符串，在引用该工作表单元格时，必须用单引号将工作表名引起来，然后在后面跟一个惊叹号（！），最后是单元格地址。例如，若要分别引用工作表“22”和工作表“1月”中单元格A1的数据，则公式分别如下所示：

='22'!A1

='1月'!A1

当从其他工作簿的某个工作表中引用单元格时，需要先用方括号将其他工作簿的名称括起来，然后是某个工作表名称及惊叹号，最后是单元格地址，例如“=[Book2.xls] Sheet1!$B$2”。

如果要引用的某个工作簿关闭了，那么就必须加上该工作簿的具体路径，引用的格式应为：

='工作簿保存文件夹路径\[工作簿名.xls]工作表名'!单元格地址

注意这里引用的具体写法是用单引号将包括工作簿路径、工作簿名及工作表名称在内的字符串括起来，然后在后面跟一个惊叹号（！），最后是单元格地址，例如：

='C:\TEMP\[Book2.xls]Sheet1'!$B$2

需要注意的是，三维引用不能用于数组公式，也不能与交叉引用运算符（即空格）一起使用。

1.1.4 快速复制公式

复制和移动公式是运用Excel过程中最常见的操作之一，尤其是在需要输入大量计算公式的场合。复制和移动公式有很多种方法和小窍门，用户可以根据自己的喜好和实际情况采用某种方法。

复制公式的基本方法是，在一个单元格中输入公式以后，将鼠标指针对准该单元格右下角的黑色小方块，按住左键不放，然后向下、向右、向上或者向左拖曳鼠标，从而完成在其他单元格中输入相应计算公式的工作。

除了上面介绍的复制公式的基本方法以外，还可以利用一些小技巧来实现对公式的快速复制，例如双击法、快速填充法、快速复制法等。下面简要介绍这几种方法。

1.双击法

在某个单元格中输入完公式以后，如果要将该单元格的公式向下填充复制，可在选取该单元格后，将鼠标指针对准该单元格右下角的黑色小方块，双击左键，这样就可以迅速得到复制的公式。

不过，这种方法只能快速向下复制公式，无法向上、向左或向右快速复制公式。而且这种方法也不适用于数据区域中间有空行的场合，如果数据区域中间有空行，复制公式的过程就会停止在空行处。此外，输入公式的单元格所在列必须与数据区域紧密相邻。

2.快速填充法

如果要在包含有很多行和很多列的单元格区域中输入相同的公式，若首先在一个单元格中输入公式，然后采用上述的通过拖曳鼠标的方法来复制公式可能就会显得比较笨拙。在这种情况下，可以先选取包括第一个单元格在内的要输入公式的全部单元格区域，再输入计算公式，按【F2】键，使公式处于编辑状态，最后再按【Ctrl+Enter】组合键，即可快速得到所有的计算公式。

3.快速复制法

对于要在很大的单元格区域中输入公式的情况，除了可以利用上述的“快速填充法”输入公式以外，还可以采用快速复制法来得到全部公式，具体方法是，首先在该单元格区域的一个单元格中输入公式，然后按【Ctrl+C】组合键，再选取要输入公式的其他所有单元格或整个单元格区域，按【Ctrl+V】组合键，也可迅速得到所有的计算公式。

4.原样复制完整公式或公式的一部分

在一般情况下，将某个单元格中的公式复制到其他单元格或单元格区域时，所得到的公式中引用的单元格往往会发生变化，除非原来的公式中引用单元格采用的是绝对引用方式。但有时用户可能希望将某个单元格中的公式本身（也就是公式表达式字符串）复制到其他的单元格或单元格区域中，而不改变公式中对单元格的引用，这种情况下则需要采用特殊的复制方法。

若要把公式完整地从一个单元格复制到另外一个单元格，但不改变公式中单元格的引用位置，最基本的方法是将公式作为文本进行复制，具体操作步骤如下。

1 选择要复制公式的单元格，然后按【F2】键，使单元格中的公式处于编辑状态，此时光标会出现在单元格公式的最后。

2 按【Shift+Home】组合键，选择整个公式文本。

3 按【Ctrl+C】组合键，将选取的公式文本复制到剪切板。

4 按【Enter】键或者【Esc】键退出编辑状态。

5 单击目标单元格，按【Ctrl+V】组合键。

除了上述介绍的在单元格内选取公式文本的方法外，还可以在公式编辑栏中选取公式文本，即单击要复制公式的单元格，然后在公式编辑栏中用鼠标选择公式文本，再执行复制和粘贴操作。

另外一种复制公式本身的方法是先将单元格公式前面的等号删除，然后再将该单元格复制到其他单元格，最后在单元格的公式字符串前面加上等号。

利用上述介绍的方法，也可以仅复制公式文本的一部分，这种情况下只要在单元格内或者公式编辑栏中选取公式的一部分，然后再进行复制粘贴就可以了。

1.1.5 将公式转换为数值

在利用公式对数据进行计算和处理以后，如果不再需要公式，而仅仅需要公式的运算结果，可以将公式转换为值，这样可以防止发生当意外时把公式所引用的单元格中的数据删除后造成公式返回结果出错的情况。

将公式的运算结果转换为不变的数据，可以采用选择性粘贴的方法。

例如，图1-1中的A列单元格中的数据是包含邮政编码和地址信息在内的原始数据，B2:C6单元格区域中的数据是通过使用函数LEFT和函数MID将A列的邮政编码与通信地址分离后的数据，即B列和C列的数据都是公式的计算结果。

由于在一般情况下，邮政编码和通信地址是不会随意改变的，因此可以将这些公式的值转换为不变的数据，具体方法和步骤如下所述（仅以图1-1所示的数据为例）。

1 选取单元格区域B2:C6，按【Ctrl+C】组合键。

2 单击鼠标右键，执行快捷菜单中的“选择性粘贴”命令，打开“选择性粘贴”对话框，在“粘贴”选项中选择“数值”单选框，如图1-2所示。

3 单击“确定”按钮，即可将公式的值转换为不变的数据，如图1-3所示。

4 如果不再需要A列数据，可将其删除。

1.1.6 显示、隐藏和保护公式

在输入完毕所有的公式并检查无误后，有必要将建立好的公式保护起来，以免不小心损坏公式。此外，用户有时也可能要查看各单元格中已经建立的公式，或者希望将计算公式隐藏起来，不随意让别人看到。下面就分别介绍隐藏、显示和保护公式的基本方法和技巧。

1.显示公式和显示计算结果

在默认的情况下，公式输入完毕以后，在单元格中只会显示计算的结果，而所建立的公式只能在公式编辑栏中看到。实际上，用户可以根据需要设置在单元格中显示计算结果或显示公式的状态，实现这一目的最简单的方法是使用【Ctrl+`】组合键进行切换，即按一次【Ctrl+`】组合键，会在含有公式的单元格中显示输入的公式，再次按该组合键，则会回到显示计算结果的状态。

图1-4是根据图1-1中的数据，按一次【Ctrl+`】组合键以后显示出的计算公式。

2.隐藏公式

已经建立好的公式还可以隐藏起来，在这种情况下，不仅在单元格里看不到公式，在公式编辑栏里也看不到公式。要达到这样的目的，需要首先选择公式所在单元格，然后执行“格式”菜单中的“单元格格式”命令，在“单元格格式”对话框的“保护”选项卡中选定“隐藏”复选框，如图1-5所示，最好还要再对工作表实施保护。保护工作表的具体方法将在下面详细介绍。

3.保护公式

当用户辛辛苦苦地在工作表的一些单元格中输入好计算公式以后，往往需要将含有公式的单元格保护起来，但对于其他没有公式的单元格不进行保护，如果需要保密的话，还可以将公式隐藏起来，使任何人都看不见单元格中的公式。

保护并隐藏公式的具体步骤如下所述。

1 选择要实施保护的工作表。

2 按【Ctrl+A】组合键，或单击工作表左上角的“全选”按钮，选择整个工作表。

3 打开“单元格格式”对话框，在“保护”选项卡中取消“锁定”复选框，如图1-6所示。最后单击“确定”按钮。

这一步的操作是为了解除工作表全部单元格的锁定。否则，在执行保护工作表命令以后就会保护工作表的全部单元格。

4 选择要保护的含有计算公式的单元格区域，可以按【F5】键，打开“定位”对话框，如图1-7所示，单击该对话框上的“定位条件”按钮，打开“定位条件”对话框，选择“公式”单选框，如图1-8所示，再单击“确定”按钮即可。

5 选择所有的含有公式的单元格以后，再次打开“单元格格式”对话框，在“保护”选项卡中重新选择“锁定”复选框。如果要隐藏计算公式，则需要再选择“隐藏”复选框，如图1-9所示。然后单击“确定”按钮，关闭“单元格格式”对话框。

6 对于Excel 2003，单击“工具”→“保护”→“保护工作表”命令，打开“保护工作表”对话框。若不仅需要隐藏和保护含有公式的单元格，而且也不允许任何人选择这些单元格，可在“保护工作表”对话框中取消“选定锁定单元格”复选框，如图1-10所示。

对于Excel 2007，保护工作表需要在“审阅”选项卡的“更改”功能组中进行，如图1-11所示，单击“保护工作表”按钮，即可打开“保护工作表”对话框。

7输入保护密码，并进行有关设置，最后单击“确定”按钮。

上述步骤完成以后，就会对含有公式的所有单元格进行保护，并且也隐藏了计算公式，任何用户都将无法操作这些单元格，也看不见这些单元格中的计算公式，但其他的单元格还是可以进行正常操作的。