4.1　公式和函数基础

在开始介绍特定的函数之前，需要先了解公式和函数的一些基本概念和知识，它们是深入学习公式和函数的基础。

4.1.1　公式的组成

Excel中的公式由等号、常量、运算符、单元格引用、函数、定义的名称等内容组成，在一个公式中可以包含这些内容中的部分或全部。Excel中的任何公式都必须以等号开头，然后输入公式包含的其他内容。

常量就是字面量，它是一个值，可以是文本、数值或日期，例如Excel、666、2020年10月1日。单元格引用就是单元格地址，可以是单个单元格地址，也可以是单元格区域的地址，例如A1、A2:B6。函数通常是指Excel内置函数，例如SUM、LEFT、LOOKUP。名称是用户在Excel中创建的，可以将名称看作是命名的公式，因此在公式中包含的内容也可以出现在名称中。使用名称可以简化公式的输入量，并使公式易于理解。

运算符用于连接公式中的各个部分，并执行不同类型的计算，例如“+”运算符用于计算两个数字的和，“*”运算符用于计算两个数字的积。不同类型的运算符具有不同的运算次序，将这种次序称为运算符的优先级。

4.1.2　运算符及其优先级

Excel中的运算符包括算术运算符、文本连接运算符、比较运算符、引用运算符4种类型。表4-1列出了按优先级从高到低的顺序排列的运算符，即引用运算符的优先级最高，比较运算符的优先级最低。

表4-1　Excel中的运算符及其说明

如果一个公式中包含多个不同类型的运算符，Excel将按照这些运算符的优先级对公式中的各个部分进行计算；如果一个公式包含多个具有相同优先级的运算符，Excel将按照运算符在公式中出现的位置，从左到右对各部分进行计算。

例如，下面公式的计算结果为11，由于“*”和“/”这两个运算符的优先级高于“+”运算符，因此先计算10×3，再将得到的结果30除以6，最后将得到的结果5加6，最终结果为11。

     =6+10*3/6

如果想要先计算低优先级的加法，即6+10部分，则可以使用圆括号提升运算符的优先级，使低优先级的运算符先进行计算。下面的公式将6+10放到一对圆括号中，使“+”运算符先于“*”和“/”运算符进行计算，因此该公式的计算结果为8，即6+10=16，16*3=48，48/6=8。

     =(6+10)*3/6

提示：当公式中包含嵌套的圆括号时，即一对圆括号位于另一对圆括号的内部。在这种情况下，嵌套圆括号的计算顺序是从最内层的圆括号逐级向外层圆括号进行计算。

4.1.3　输入和修改公式

输入公式与输入普通数据的方法类似，可以参考第2.1.2节中的内容。输入公式时可以在“输入”“编辑”和“点”3种模式之间随意切换。输入公式中的所有内容后，按Enter键结束输入，将得出计算结果。

如果要输入新的公式代替单元格中的现有公式，只需选择包含公式的单元格，然后输入新的公式并按Enter键。如果要修改公式中的部分内容，则应先选择包含公式的单元格，然后使用以下方法进入“编辑”模式：

·　按F2键。

·　双击单元格。

·　单击编辑栏。

修改公式后，按Enter键保存修改结果。如果在修改时按Esc键，则会放弃当前做的所有修改并退出“编辑”模式。

4.1.4　移动和复制公式

用户可以将单元格中的公式移动或复制到其他位置，方法类似于移动和复制普通数据。填充数据的方法也同样适用于公式，通过拖动包含公式的单元格右下角的填充柄，可以在一行或一列中复制公式。也可以双击填充柄，将公式快速复制到与相邻的行或列中最后一个连续数据相同的位置上。

如果在复制的公式中包含单元格引用，那么单元格引用的类型将影响复制后的公式。Excel中的单元格引用类型分为相对引用、绝对引用、混合引用3种，通过单元格地址中是否包含“$”符号，可以从外观上区分3种单元格引用类型。

如果同时在单元格地址的行号和列标的左侧添加“$”符号，则该单元格的引用类型是绝对引用，例如$A$1。如果在单元格地址的行号和列标的左侧都没有“$”符号，则该单元格的引用类型是相对引用，例如A1。如果只在单元格地址的行号的左侧添加“$”符号，则该单元格的引用类型是混合引用，即列相对引用、行绝对引用，例如A$1。如果只在单元格地址的列标的左侧添加“$”符号，则该单元格的引用类型也是混合引用，即列绝对引用、行相对引用，例如$A1。

用户可以在单元格地址中通过手动输入“$”符号改变单元格的引用类型。更简单的方法是在单元格或编辑栏中选中单元格地址，通过反复按F4键在各个引用类型之间切换。如果A1单元格最初为相对引用，使用下面的方法将在不同的引用类型之间切换：

·　按1次F4键，将相对引用转换为绝对引用，即A1→$A$1。

·　按2次F4键：将相对引用转换为行绝对引用、列相对引用，即A1→A$1。

·　按3次F4键：将相对引用转换为行相对引用、列绝对引用，即A1→$A1。

·　按4次F4键：单元格的引用类型恢复为最初的相对引用。

在将公式从一个单元格复制到另一个单元格时，公式中的绝对引用单元格地址不会改变，而相对引用单元格地址则会根据公式复制到目标单元格与原始单元格之间的相对位置，自动调整复制公式后的单元格地址。

例如，如果B1单元格中的公式为“=A1+6”，将公式复制到C3单元格后，公式变为“=B3+6”，原来的A1自动变为B3，如图4-1所示。这是因为公式由B1复制到C3，相当于从B1向下移动2行，向右移动1列，从而到达C3。由于公式中的A1是相对引用，因此该单元格也要向下移动2行，向右移动1列，最终到达B3。

图4-1　相对引用对复制公式的影响

如果单元格的引用类型是混合引用，则在复制公式时，只改变相对引用的部分，绝对引用的部分保持不变。继续使用上面的示例进行说明，如果B1单元格中的公式为“=A$1+6”，将该公式复制到C3单元格后，公式将变为“=B$1+6”，如图4-2所示。由于原来的A$1是行绝对引用、列相对引用，因此复制后只改变列的位置。

图4-2　混合引用对复制公式的影响

4.1.5　更改公式的计算方式

在修改公式中的内容后，按Enter键将得到最新的计算结果。如果工作表中包含使用随机数函数的公式，则在编辑其他单元格并结束编辑后，随机数函数的值会自动更新。这是因为Excel的计算方式默认为“自动”。

如果工作表中包含大量的公式，这种自动重算功能将会严重影响Excel的整体性能。此时，可以将计算方式改为“手动”，只需在功能区的“公式”选项卡中单击“计算选项”按钮，然后在弹出的菜单中选择“手动”，如图4-3所示。

提示：如果将计算方式设置为“除模拟运算表外，自动重算”，则在Excel重新计算公式时会自动忽略模拟运算表的相关公式。

将计算方式设置为“手动”后，如果工作表中存在任何未计算的公式，则会在状态栏中显示“计算”，此时可以使用以下方法对公式执行计算：

·　在功能区的“公式”选项卡中单击“开始计算”按钮，或按F9键，将重新计算所有打开工作簿中的所有工作表的未计算的公式，如图4-4所示。

·　在功能区的“公式”选项卡中单击“计算工作表”按钮，或按Shift+F9快捷键，将重新计算当前工作表中的公式。

·　按Ctrl+Alt+F9快捷键，将重新计算所有打开工作簿中所有工作表的公式，包括已计算和未计算的所有公式。

·　按Ctrl+Shift+Alt+F9快捷键，将重新检查相关的公式，并重新计算所有打开工作簿中所有工作表的公式，无论这些公式是否需要重新计算。

图4-3　更改公式的计算方式

图4-4　单击“开始计算”按钮

4.1.6　在公式中输入函数及其参数

Excel提供了内置函数，用于执行不同类型的计算，表4-2列出了Excel中的函数类别及其说明。为了使函数的名称可以准确地描述函数的功能，从Excel 2010开始微软公司修改了Excel早期版本中的一些函数名称，并改进了一些函数的性能和计算精度。后来的Excel版本仍然沿用Excel 2010的函数命名方式。

表4-2　Excel中的函数类别及其说明

为了保持与Excel早期版本的兼容性，在Excel 2010及更高版本的Excel中保留了重命名前的函数，可以在功能区的“公式”选项卡中单击“其他函数”按钮，然后在弹出的菜单中选择“兼容性”命令，在打开的下拉列表中可以找到这些函数，如图4-5所示。

图4-5　兼容性函数

重命名后的函数名称通常是在原有函数名称中间的某个位置添加半角句点（.），有的函数会在其原有名称的结尾添加包含半角句点在内的扩展名。例如，NORMSDIST是Excel 2003中的标准正态累积分布函数，在Excel 2010及更高版本的Excel中将该函数重命名为NORM.S.DIST。

在关闭一些工作簿时，可能会显示用户是否保存工作簿的提示信息。即使在打开工作簿后未进行任何修改，关闭工作簿时仍然会显示这类提示信息。出现这种情况通常是由于在工作簿中使用了易失性函数。在工作表的任意一个单元格中输入或编辑数据，甚至只是打开工作簿这样的简单操作，工作表中的易失性函数都会自动重新计算。此时关闭工作簿，Excel会认为工作簿处于未保存状态，因此会显示是否保存的提示信息。常见的易失性函数有TODAY、NOW、RAND、RANDBETWEEN、OFFSET、INDIRECT、CELL等。

下面的操作不会触发易失性函数的自动重算：

·　将计算方式设置为“手动计算”。

·　设置单元格格式或其他显示方面的选项。

·　输入或编辑单元格时，按Esc键取消本次输入或编辑操作。

·　使用除鼠标双击外的其他方法调整单元格的行高和列宽。

无论在Excel中使用哪个函数，首先都需要掌握在公式中输入函数的基本方法，有以下几种：

·　手动输入函数。

·　使用功能区中的函数命令。

·　使用“插入函数”对话框。

1．手动输入函数

如果知道要使用的函数的完整拼写，则可以直接在公式中输入函数。当用户在公式中输入函数的前几个字母时，Excel将显示与用户输入相匹配的函数列表。用户可以滚动鼠标滚轮或使用键盘上的方向键选择所需的函数，然后按Tab键将该函数添加到公式中，如图4-6所示。

将函数添加到公式后，Excel将自动在函数名的右侧添加一个左圆括号，并在函数名的下方以粗体格式显示当前需要输入的参数信息，方括号包围的参数是可选参数，如图4-7所示。输入函数的所有参数后，需要输入一个右圆括号作为函数的结束标志。

图4-6　输入函数时自动显示匹配的函数列表

图4-7　将函数输入到公式中

提示：无论用户在输入函数时使用的是大写字母还是小写字母，只要输入拼写正确的函数名，按下Enter键后，函数名会自动转换为大写字母形式。

2．使用功能区中的函数命令

在功能区的“公式”选项卡的“函数库”组中，每一类函数都作为一个按钮显示在该组中。单击这些按钮，可以在弹出的菜单中选择特定类别的函数。如图4-8所示为从“文本”函数类别中选择的LEFT函数，当鼠标指针指向某个函数时，将自动显示关于该函数的功能及其包含的参数的简要说明。

选择一个函数后，将打开“函数参数”对话框，其中显示了函数包含的各个参数，用户需要在相应的文本框中输入参数的值，可以单击文本框右侧的按钮在工作表中选择单元格或区域，每个参数的值显示在文本框的右侧，下方显示使用当前函数对各个参数计算后的结果，如图4-9所示。输入参数值后，单击“确定”按钮，即可将包含参数的函数添加到公式中。

图4-8　在功能区中选择要使用的函数

图4-9　设置函数的参数值

3．使用“插入函数”对话框

单击编辑栏左侧的按钮，打开“插入函数”对话框，在“搜索函数”文本框中输入关于计算目的或函数功能的描述信息，然后单击“转到”按钮，Excel将显示与输入内容相匹配的函数，如图4-10所示。

图4-10　通过输入描述信息找到匹配的函数

在“选择函数”列表框中选择所需的函数，然后单击“确定”按钮，在打开的“函数参数”对话框中输入参数的值即可。

在前面介绍输入函数时，都涉及了函数的参数。每个函数由函数名、一对圆括号以及位于圆括号中的一个或多个参数组成，各个参数之间使用半角逗号分隔，形式如下：

     函数名(参数1,参数2,…,参数n）

参数为函数提供要计算的数据，用户需要根据函数语法中的参数位置，依次输入相应类型的数据，才能使函数正确计算并得出结果，否则将返回错误值或根本无法计算。在输入不包含参数的函数时，需要输入函数名和一对圆括号。

参数的值可以有多种形式，包括以常量形式输入的数值或文本、单元格引用、数组、名称或函数。将一个函数作为另一个函数的参数形式称为嵌套函数。

在为某些函数指定参数值时，并非必须提供函数语法中列出的所有参数，这是因为参数分为必选参数和可选参考两种。

·　必选参数：必须指定必选参数的值。

·　可选参数：可以忽略可选参数的值。在单元格输入函数时显示的函数语法中，使用方括号标记的参数就是可选参数，如图4-11所示。例如，SUM函数最多可以包含255个参数，只有第一个参数是必选参数，其他参数都是可选参数，因此可以只指定第一个参数的值，而省略其他254个参数。

图4-11　使用方括号标记可选参数

对于包含可选参数的函数，如果在可选参数之后还有参数，则在不指定前一个可选参数而直接指定其后的可选参数时，必须保留前一个可选参数的逗号占位符。例如，OFFSET函数包含5个参数，前3个参数是必选参数，后两个参数是可选参数，当不指定该函数的第4个参数而指定第5个参数时，必须保留第4个参数与第5个参数之间的半角逗号，此时Excel自动为第4个参数指定默认值，通常为0。

4.1.7　在公式中引用其他工作表或工作簿中的数据

公式中引用的数据可以来自于公式所在的工作表，也可以来自于公式所在的工作簿中的其他工作表，甚至是其他工作簿，对于后两种情况，需要使用特定的格式在公式中输入所引用的数据。此外，在公式中还可以引用多个工作表中的相同区域。

1．在公式中引用其他工作表的数据

如果要在公式中引用同一个工作簿的其他工作表的数据，则需要在单元格地址的左侧添加工作表名称和一个半角感叹号，格式如下：

     =工作表名称!单元格地址

例如，在Sheet2工作表的A1单元格中包含数值100，如图4-12所示。如果要在该工作簿的Sheet1工作表的A1单元格中输入一个公式，计算Sheet2工作表的A1单元格中的值与6的乘积，则需要在Sheet1工作表的A1单元格中输入以下公式，如图4-13所示。

图4-12　Sheet2工作表中的数据

图4-13　Sheet1工作表中的公式

     =Sheet2!A1*6

注意：如果工作表的名称以数字开头，或其中包含空格、特殊字符（例如$、%、#等），则必须使用一对单引号将工作表名称包围起来，例如“='Sheet 2'!A1*6”。以后如果修改工作表的名称，公式中工作表名称会同步更新。

2．在公式中引用其他工作簿的数据

如果要在公式中引用其他工作簿中的数据，则需要在单元格地址的左侧添加使用方括号括起的工作簿名称、工作表名称和一个半角感叹号，格式如下：

     =[工作簿名称]工作表名称!单元格地址

如果工作簿名称或工作表名称以数字开头，或其中包含空格、特殊字符，则需要使用一对单引号同时将工作簿名称和工作表名称包围起来，格式如下：

     ='[工作簿名称]工作表名称'!单元格地址

如果公式中引用的数据所在的工作簿已经被打开，则只需按照上面的格式输入工作簿的名称，否则必须在公式中输入工作簿的完整路径。为了简化输入，通常在打开工作簿的情况下创建这类公式，关闭工作簿后，其路径会被自动添加到公式中。

下面的公式引用“销售数据”工作簿Sheet2工作表中的A1单元格的数据，并计算它与5的乘积，如图4-14所示。

图4-14　在公式中引用其他工作簿中的数据

     =[销售数据.xlsx]Sheet2!A1*5

3．在公式中引用多个工作表的相同区域

如果要在公式中引用多个相邻工作表的相同区域的数据，则可以使用工作表的三维引用，以简化对每一个工作表的单独引用，格式如下：

     起始位置的工作表名称:结束位置的工作表名称!单元格地址

下面的公式是计算Sheet1、Sheet2和Sheet3三个工作表A1:A6单元格区域中的数值总和：

     =SUM(Sheet1:Sheet3!A1:A6)

如果不使用三维引用，则需要在公式中重复引用每一个工作表中的单元格区域：

     =SUM(Sheet1!A1:A6,Sheet2!A1:A6,Sheet3!A1:A6)

下面列出的函数支持工作表的三维引用：

SUM、AVERAGE、AVERAGEA、COUNT、COUNTA、MAX、MAXA、MIN、MINA、PRODUCT、STDEV.P、STDEV.S、STDEVA、STDEVPA、VAR.P、VAR.S、VARA和VARPA。

如果改变公式中引用的多个工作表的起始工作表或结束工作表，或在引用的多个工作表的范围内添加或删除工作表，Excel将自动调整公式中引用的多个工作表的范围及其中包含的工作表。

技巧：如果要引用除了当前工作表之外的其他所有工作表，则可以在公式中使用通配符“*”，形式如下：

     =SUM('*'!A1:A6)

4.1.8　创建数组公式

Excel中的数组是指排列在一行、一列或多行多列中的一组数据的集合。数组中的每一个数据称为数组元素，数组元素的数据类型可以是Excel支持的任意数据类型。数组的维数是指数组具有的维度个数，维度是指数组的行、列方向。按数组的维数，可以将Excel中的数组分为以下两类：

·　一维数组：数组中的元素排列在一行或一列中。数组元素排列在一行的数组是水平数组（或横向数组），数组元素排列在一列的数组是垂直数组（或纵向数组）。

·　二维数组：数组中的元素排列在多行多列中。

数组的尺寸是指数组各行各列的元素个数。一行N列的一维水平数组尺寸为1×N，一列N行的一维垂直数组尺寸为N×1，M行N列的二维数组尺寸为M×N。

按数组的存在形式，可以将Excel中的数组分为以下3类：

·　常量数组：常量数组是直接在公式中输入数组元素，并使用一对花括号将这些元素包围起来。如果数组元素是文本型数据，则需要使用半角双引号包围每一个数组元素。

·　区域数组：区域数组是公式中引用的单元格区域，例如“=SUM（A1:B6）”公式中的A1:B6就是区域数组。

·　内存数组：内存数组是在公式的计算过程中，由中间步骤返回的多个结果临时构成的数组，它存在于内存中，通常作为一个整体继续参与下一步计算。

无论哪种类型的数组，数组中的元素都遵循以下格式：水平数组中的各个元素之间使用半角逗号分隔，垂直数组中的各个元素之间使用半角分号分隔。

如图4-15所示，A1:F1单元格区域中包含一个一维水平的常量数组：

     ={1,2,3,4,5,6}

如图4-16所示，A1:A6单元格区域中包含一个一维垂直的常量数组：

     ={"A";"B";"C";"D";"E";"F"}

图4-15　一维水平数组

图4-16　一维垂直数组

在输入上面两个常量数组时，需要先选择与数组方向及元素个数完全一致的单元格区域，然后输入数组公式并按Ctrl+Shift+Enter快捷键，Excel会自动添加一对花括号将整个公式包围起来。

根据数组公式占据的单元格数量，可以将数组公式分为单个单元格数组公式和多个单元格数组公式（或称为多单元格数组公式）。如果要修改多单元格数组公式，则需要选择数组公式占据的整个单元格区域，然后按F2键，在“编辑”模式下修改数组公式，修改完成后按Ctrl+Shift+Enter快捷键。删除多单元格数组公式的方法与此类似，需要选择数组公式占据的整个单元格区域，然后按Delete键。无法单独修改或删除多单元格数组公式中的部分单元格。

如图4-17所示，使用下面的数组公式计算所有商品的销售额。按照常规方法需要两步，首先分别计算每种商品的销售额，然后将各个商品的销售额汇总求和。使用数组公式可以一步完成，简化了计算的中间步骤。

     {=SUM(B2:B11*C2:C11)}

图4-17　使用数组公式可以简化计算步骤

4.1.9　处理公式错误

当单元格中的公式出现无法被Excel识别时，将在单元格中显示错误值，它们以“#”符号开头，每个错误值表示特定类型的错误。表4-3列出了Excel中的7种错误值及其说明。

表4-3　Excel中的7种错误值及其说明

除了表4-3列出的7种错误值外，另一种经常出现的错误是单元格自动被“#”符号填满，出现该错误有以下两个原因：

·　单元格的列宽太小，导致无法完全显示其中的内容。

·　使用1900日期系统时在单元格中输入了负的日期或时间。

当Excel检测到单元格中的错误时，将在该单元格的左上角显示一个绿色的三角，单击这个单元格将显示按钮，单击该按钮将弹出如图4-18所示的菜单，其中包含用于检查和处理错误的相关命令。

菜单顶部的文字说明了错误的类型，例如此处的“数字错误”，菜单中的其他命令的功能如下：

·　关于此错误的帮助：打开“帮助”窗口并显示相关错误的帮助主题。

·　显示计算步骤：通过分步计算检查发生错误的位置。

·　忽略错误：保留单元格中的当前值并忽略错误。

·　在编辑栏中编辑：进入单元格的“编辑”模式，在编辑栏中可以修改单元格中的内容。

·　错误检查选项：打开“Excel选项”对话框中的“公式”选项卡，在该选项卡中设置错误的检查规则，只有选中“允许后台错误检查”复选框，才会启用Excel错误检查功能，如图4-19所示。

图4-18　包含用于检查和处理错误的相关命令

图4-19　设置错误检查选项

如果公式比较复杂，则在查找出错原因时可能比较费时。使用Excel中的分步计算功能，可以将复杂的计算过程分解为单步计算，提高错误排查的效率。选择公式所在的单元格，然后在功能区的“公式”选项卡中单击“公式求值”按钮，打开“公式求值”对话框，如图4-20所示。单击“求值”按钮将得出下画线部分的计算结果，如图4-21所示。继续单击“求值”按钮，依次计算公式中的其他部分，直到得出整个公式的最终结果。完成整个公式的计算后，可以单击“重新启动”按钮重新对公式执行分步计算。

图4-20　“公式求值”对话框

图4-21　计算公式中的每个部分

在“公式求值”对话框中还有“步入”和“步出”两个按钮。当公式中包含多个计算项且其中含有单元格引用时，“步入”按钮将变为可用状态，单击该按钮会显示分步计算中当前下画线部分的值。如果下画线部分包含公式，则会显示具体的公式。单击“步出”按钮将从步入的下画线部分返回到整个公式中。