第8章　用公式和函数计算数据

本章内容

·数据的计算是Excel的显著功能之一，在Excel中，不仅可以使用公式进行简单的数学运算，还可以使用函数进行复杂数据的计算。本章主要介绍有关使用公式和函数计算数据的相关知识和基本操作，为深入学习Excel的数据计算功能奠定基础。

要点导读

公式和函数的基础认知：在Excel中对数据进行各种计算操作时，会用到公式和函数。所以必须理解公式的概念和结构、函数的概念和结构及嵌套函数和函数的分类。

熟悉单元格的引用样式和类型：单元格的引用样式包括A1引用样式和R1C1引用样式，引用类型包括相对引用、绝对引用和混合引用。

使用公式和函数：在Excel中，公式和函数的使用方法与编辑操作都相似，对于公式和函数的复制与移动操作，与单元格数据的移动与复制操作基本相同。

公式和函数使用中的常见错误：Excel具备自动纠错的功能，但是对于某些比较复杂的错误类型，需要用户手动进行纠错。

8.1　公式和函数的基础认知

在Excel中对数据进行各种计算操作时，都必须按照一定的格式和规则进行，否则系统将无法顺利进行数据计算。下面将对公式和函数的一些基本知识进行介绍。

8.1.1　了解公式的概念和结构

在Excel中，公式是用于计算数据结果的等式，它总是以等号“=”开始，然后将各种计算数据使用不同的运算符连接起来，从而有目的地完成某种数据结果的计算。图8-1所示为一个简单公式的示例。

从上面的示例中可以看出，等号、单元格引用、常量、运算符等元素是构成公式的基本元素，在Excel中，对于公式中的A3、B3、100、D3等数据又统称为公式的参数。组成公式的各个元素的具体作用和要求如图8-2所示。

8.1.2　了解函数的概念和结构

在Excel中，通常所说的函数其实是指Excel的工作表函数，它是由系统事先将参数按照某种特定顺序和结构预定好，用于完成某些特殊计算和分析的功能模块，由于它也是以等号“=”开始且具有完成数据计算的功能，因此常常被人们称为特殊的公式。图8-3所示为一个简单函数的示例。

从上面的示例中可以看出，函数主要由函数名、括号、参数组成，其各个元素的具体作用和要求如图8-4所示。

提示：函数参数的不同情况

按参数的数量和使用方式不同，函数有不带参数、只有一个参数、参数数量固定、参数数量不固定和具有可选参数等几种情况。当函数名称后面不带任何参数时，仍然需要带一组空括号。

在Excel中，由于不同函数返回的数据的类型不同，因此，不同的函数其参数的类型也不相同。可指定为函数参数的类型有常量、数组、单元格引用、逻辑值、错误值等，其各自的含义如下。

◆常量：指在数据计算过程中值不会发生改变的量，如数字“123”、文本“张家”等。

◆数组：用来创建可生成多个结果，或者对行和列中排列的一组参数进行计算的单个参数。

◆单元格引用：与公式表达式中的单元格引用的含义相同。

◆逻辑值：包括真值（TRUE）和假值（FALSE）。

◆错误值：形如“#N/A”、“#NAME”等的值。

注意：Excel中的函数

在Excel中，系统提供了两种函数，一种是工作表函数，即平常所提到的函数，如SUM（）、MAX（）等；另一种函数是VBA函数，该函数只在VBA编辑器中使用，如CHOOSE（）、INPUT（）等。

8.1.3　理解嵌套函数

由于函数能够返回数据，因此可以将其作为另一个函数的参数，这就是所谓的函数嵌套，其结构示意如图8-5所示。

在Excel 2010中，默认情况下，函数的嵌套级别限制为7级以内，如果嵌套级别超过了7级，则函数将无法进行运算（除非将某个嵌套的函数使用名称来定义，有关知识请参考第7章）。

注意：嵌套函数的注意事项

在嵌套函数中，作为参数的函数，其函数返回的类型必须与参数的类型相同，即图示中函数B的函数返回值的类型必须与函数A中参数A2的数据类型相同，否则Excel将显示“#VALUE!”错误。

8.1.4　函数的分类

在Excel中，虽然系统内置了很多的工作表函数，但是不同的函数，其使用范围不相同，根据其功能划分，可以将工作表函数分为十大类，如图8-6所示。

8.2　熟悉单元格的引用样式和类型

单元格引用主要是指对工作表中的单元格或单元格区域进行引用，并通过单元格地址的行列标识告知Excel在何处查找指定的单元格。

8.2.1　A1引用样式和R1C1引用样式

Excel中可以设置两种引用样式，分别是A1引用样式和R1C1引用样式。在默认情况下，Excel使用A1引用样式，有关该样式的相关知识参见第2章。

R1C1引用样式是一种特殊的引用模式，它不仅可以标记单元格的绝对位置，还能标记单元格的相对位置。

◆R1C1引用样式的行号表示方法：在R1C1引用样式中，“R”用于控制单元格的行号，数字序号1表示第1行，数字序号2表示第2行……

◆R1C1引用样式的列标表示方法：在R1C1引用样式中，“C”用于控制单元格的列标，数字序号1表示第1列，数字序号2表示第2列……

图8-7所示为常规的A1引用样式用R1C1样式表示。

在Excel 2010中，要启用该引用样式，直接在“Excel选项”对话框中单击“公式”选项卡，然后在右侧的“使用公式”栏中勾选“R1C1引用样式”复选框，单击“确定”按钮即可，如图8-8所示。

8.2.2　单元格引用类型

所谓引用类型主要是指对工作表中单元格或者单元格区域进行引用的方式，在Excel中，按引用方式的不同可以将单元格的引用类型分为相对引用、绝对引用和混合引用3种。

1.相对引用

相对引用Excel默认的单元格引用类型，在该类型下把公式复制到新位置后，公式中单元格的地址相对于公式所在的位置会发生改变。

如图8-9所示，在D2单元格中使用公式“=A2*B2”计算数据结果，由于公式使用相对引用方式，复制公式后，在D3单元格中系统自动将公式中单元格的行号和列标同时进行相应的更改，得到公式“=A3*B3”，并计算结果。

2.绝对引用

在形态上，绝对引用的单元格行号和列标之前加入了“$”符号，将公式复制到新位置后，单元格地址保持不变。

如图8-10所示，在D2单元格中使用公式“=A2*$B$2”计算数据结果，由于公式使用的数据B采用的是绝对引用方式，复制公式后，在D3单元格中系统自动将公式中数据A单元格的行号和列标同时进行相应的更改，而数据B的单元格地址保持不变，从而得到公式“=A3*$B$2”，并计算结果。

3.混合引用

混合引用是指在一个单元格的地址引用中，既有相对引用，又有绝对引用。当公式中使用了混合引用后，在其他位置复制该公式后，单元格地址中的相对引用部分将发生改变，而绝对引用部分（单元格地址中包含了“$”）保持不变。

如图8-11所示，在B4单元格中使用公式“=B$1*$B2”计算数据结果，由于公式中数据A的列标和数据B的行号是绝对引用方式，复制公式后，在C4单元格中系统自动将公式中数据A的行号和数据B的列标同时进行相应的更改，保持公式中数据A的列标和数据B的行号不变，从而得到公式“=C$1*$B2”，并计算结果。

技巧：巧用【F4】键在各种引用之间进行转换

要将相对引用转换为绝对引用，直接在需转换的单元格列标和行号之前加入符号“$”，也可在公式的单元格地址前或地址后按【F4】键，如“D2”，第1次按【F4】键变为“$D$2”，第2次按【F4】键变为“D$2”，第3次按【F4】键变为“$D2”，第4次按【F4】键变为“D2”。

拓展学习　R1C1引用样式中的绝对引用

在R1C1引用样式中，绝对引用方式是用成对的中括号“[]”将单元格地址中绝对引用的行号或列标的数字序号进行标识。图8-12（左）所示为绝对引用R1C3单元格地址的列标，图8-12（右）所示为绝对引用R2C2单元格地址的行号。

此外，在当前行或者列中使用公式对单元格进行引用，则公式中第一个数据的行号或者列标的数字序号可以省略。

如图8-12（左）所示，R2C4单元格中的公式“=RC[-3]*R2C2”的数据A的单元格地址引用省略了行号。

拓展学习　R1C1引用样式在引用过程中单元格地址的数字序号问题

如果当前系统的单元格引用样式为R1C1引用样式，则单元格地址中行号和列标的数字序号1、2、3……可以分别指代第1行第1列、第2行第2列、第3行第3列……

但是在公式中引用单元格时，则当前选择的单元格就会成为中心单元格，其不同方向的单元格的行号和列标的数字序号将按其他方式进行标识。

◆中心单元格左侧列标和上方行号：选择某个中心单元格后，其左侧的列标和上方的行号的数字序号依次用-1、-2、-3……的顺序进行编号，在图8-13中，左图为中心单元格左侧列标的标识方式（同行省略了行号数字序号），右图为中心单元格上方的行号标识（同列省略了列标数字序号）。

◆中心单元格右侧列标和下方行号：选择某个中心单元格后，其右侧的列标和下方的行号的数字序号依次用1、2、3……的顺序进行编号，在图8-14中，左图为中心单元格右侧列标的标识方式（同行省略了行号数字序号），右图为中心单元格下方的行号标识方式（同列省略了列标数字序号）。

8.3　不同范围的单元格引用

根据单元格的引用范围不同，单元格引用分为同一工作簿中单元格的引用和不同工作簿中的单元格引用。

8.3.1　相同工作簿中的单元格引用

前面讲解的单元格的相对引用、绝对引用和混合引用是在相同工作簿的同一工作表中直接进行的。如果要在当前工作簿中引用不同工作表的单元格，其引用格式为：工作表名称!单元格地址。

图8-15所示为在“年度总销量”工作表中引用“上半年”工作表和“下半年”工作表中的F3单元格的值来计算“年度总销量”数据的效果。

8.3.2　不同工作簿中的单元格引用

如果当前工作簿中的某个数据需要使用其他工作簿中的数据，则会涉及不同工作簿中单元格的引用。在Excel中，如果要在不同工作簿中引用单元格，其引用格式为：=[工作簿名称.xlsx]工作表名称！单元格地址。

图8-16所示为在“产品销量统计表”工作簿的“Sheet1”工作表中引用“产品年度销售情况”工作簿中“上半年”工作表的F3单元格的值来计算“上半年总销量”数据的效果。

注意：不同工作簿的单元格引用注意事项

在引用不同工作簿中的单元格时，要确认需要操作的工作簿都是打开状态，如果被引用的工作簿是关闭状态，则只有使用“'工作簿存储地址[工作簿名称]工作表名称!单元格地址”格式才能完成不同工作簿中的单元格引用。

提示：不同范围的单元格地址引用情况

无论是在相同工作簿中引用单元格，还是在不同工作簿中引用单元格，其引用格式中的单元格地址引用可以是相对引用、绝对引用和混合引用，但默认情况下，在相同工作簿中的单元格引用都是相对引用，而在不同工作簿中的单元格引用为绝对引用。

8.4　公式的运算符与优先级概述

在公式计算过程中，运算符主要用于指定公式中各个元素之间执行的计算类型，它是影响数据计算结果的重要因素之一，本节将具体介绍公式中的各种运算符及其优先级的相关知识。

8.4.1　公式中的各种运算符

在Excel中，系统包括算术运算符、比较运算符、文本连接运算符和引用运算符4种类型的运算符，下面分别对其进行介绍。

◆算术运算符：算术运算符主要用于对数据进行各种数学运算，各种运算符的具体作用及其应用举例如表8-1所示。

续表

提示：“+”和“-”运算符的特殊用法

“+”和“－”运算符既可以连接两个操作数，也可以连接一个操作数，当只连接一个操作数时用于标识数据的正负情况，如“+9”表示正数9，“-9”表示负数9。

◆比较运算符：比较运算符主要用于比较两个不同数据的值，当等式成立，则结果返回逻辑值TRUE；当等式不成立，则结果返回逻辑值FALSE，各种比较运算符的具体作用及其应用举例如表8-2所示。

◆文本运算符：在Excel中，文本运算符只有一个，即和号（&），通常也被称为文本串联符或文本连接符，其具体作用和应用举例如表8-3所示。

◆引用运算符：引用运算符主要用于对指定的单元格区域进行合并计算，在Excel中，引用运算符只有两个，其具体作用和应用举例如表8-4所示。

8.4.2　公式运算的优先顺序

在Excel中，当公式中同时使用多个运算符时，系统将遵循从高到低的顺序进行计算。对于相同优先级的运算符，将遵循从左到右的原则进行计算。Excel中各运算符从高到低的优先顺序如图8-17所示。

拓展学习　公式中括号的使用

在Excel中，默认情况下，公式会自动按照运算符的优先级顺序进行计算，如果要手动更改公式的运算顺序，则可以使用括号来实现，在同一个括号中同样需要按照运算符的优先顺序进行计算。

如果将括号放到括号之内就形成了括号的嵌套，此时系统将按照先内后外的顺序优先处理最内部括号中的内容，然后依次向外扩展。

图8-18所示为同一表达式使用括号前后的计算结果对比，左侧的常规运算是按照“乘→除→加→减”的顺序进行计算，右侧的带括号运算是按照“（左括号的加，右括号的除→减）→乘”的顺序进行计算。

拓展学习　Excel中各种数据的大小关系

Excel的数据包括文本数据、数值数据、逻辑数据及错误值等，除错误值以外，其余3种类型的数据之间都存在着大小关系，在对数据进行比较运算时，首先需要了解这些大小关系。

在Excel中，各类数据的默认排列大小为：数值（负数<0<整数）<文本（英文字母）<逻辑值（FALSE<TRUE）。

例如，计算“=-（1+3）>6*4+7>TRUE<（2+4）*（9+2）>125<=FALSE”的数据结果，其具体的运行过程如图8-19所示。

8.5　使用公式

掌握公式的基础知识后，即可在工作表中使用公式对数据进行各种计算，以及根据实际情况对公式进行移动、复制、修改、隐藏和转换计算结果等操作。

8.5.1　输入公式的两种方法

在单元格中输入公式较为简单，常用的输入公式方法包括两种，一种是手动输入公式，另一种是通过选择单元格来输入公式。

1.手动输入公式

在Excel中，手动输入公式的方法与输入数据的方法相似，首先双击将文本插入点定位到目标单元格，或者选择目标单元格后，在编辑栏中单击将文本插入点定位到编辑栏中，输入“=”，然后在其后输入需要使用的公式，如图8-20所示，按【Enter】键计算出结果，并选择该单元格同列下方的单元格，如图8-21所示。

当输入公式后，还可以使用其他的方法确认输入的公式和计算结果，其具体操作如下。

◆按【Tab】键：输入公式后，按【Tab】键，在计算出结果的同时选择该单元格右侧的单元格，如图8-22所示。

◆按【Ctrl+Enter】组合键：输入公式后，按【Ctrl+Enter】组合键，在计算出结果后仍然保持当前单元格的选中状态，如图8-23所示，该方法用于同时查看数据结果和使用公式。

2.通过选择单元格输入公式

通过选择单元格输入公式的方法是：将文本插入点定位到目标单元格或者选择单元格后，输入“=”，选择需要参加计算的第一个单元格确认公式的第一个操作数，如图8-24所示，然后输入运算符，并选择第二个单元格确认第二个操作数，如图8-25所示，用相同的方法完成公式的输入，按【Ctrl+Enter】组合键计算出结果。

8.5.2　复制和移动公式

在使用公式计算数据的过程中，如果某些数据结果使用的公式相似，或者公式计算结果的位置错误，用户可以使用复制和移动公式的方法快速提高操作效率。

在Excel中，移动公式的方法和移动数据的操作相似，也是通过拖动单元格或者使用剪切功能来完成。

如果要复制公式，可以拖动包含公式的单元格的控制柄、选择“粘贴”下拉菜单或快捷菜单中的“公式”选项来完成，对于连续单元格中公式的复制，使用这两种操作比较快捷。如果需要复制的公式为不连续的单元格区域，则使用复制和粘贴功能来复制公式比较简洁。

下面以复制从“员工工资明细表”中分别引用员工的姓名和相应的个税的公式为例，讲解复制公式的操作。

Step 01　【通过控制柄复制公式】选择包含公式的单元格，将鼠标光标移动到控制柄上，按住鼠标左键不放进行拖动，即可快速为单元格区域复制公式，如图8-26所示。

Step 02　【使用快捷菜单复制公式】复制包含公式的单元格，选择目标单元格或单元格区域右击，在弹出的快捷菜单中选择“公式”选项，完成公式的复制操作，如图8-27所示。

提示：复制带单元格格式的公式

在复制公式的过程中，如果需要复制的公式的单元格设置了单元格格式，直接拖动控制柄，系统同时会复制单元格格式。如果不需要复制单元格格式，单击“粘贴选项”按钮右侧的下拉按钮，选中“不带格式填充”单选按钮即可，如图8-28所示。

技巧：使用选择性粘贴功能复制公式

在复制公式时，在“粘贴”下拉菜单中选择“选择性粘贴”命令，将打开“选择性粘贴”对话框，选中“公式”单选按钮，如图8-29所示，单击“确定”按钮后也可以完成复制公式的操作。

8.5.3　修改公式

在Excel中，可以对公式的部分进行修改，也可以对整个公式进行修改，如果只需修改公式中的某一部分，在单元格或者编辑栏中将其选中，然后重新输入新内容后，按【Ctrl+Enter】组合键即可，如图8-30所示。

如果整个公式都输入错误，直接在单元格中将其全部选择后，再重新输入新公式，按【Ctrl+Enter】组合键重新计算数据结果，如图8-31所示。

8.5.4　显示和隐藏公式

默认情况下，系统自动显示公式的计算结果而隐藏公式，如果要始终显示表格中的公式，可以设置公式的显示方式。

在Excel 2010中，在“公式”选项卡的“公式审核”组中单击“显示公式”按钮，将公式的计算结果转换为公式显示方式而隐藏计算结果，且各列单元格的宽度会自动增加，如图8-32所示。

如果要恢复公式计算结果的显示，只需再次单击“显示公式”按钮即可，且各列单元格的宽度会自动恢复到原始状态。

拓展学习　彻底隐藏公式

使用显示公式功能只能暂时地隐藏和显示公式，为了防止其他用户恶意修改公式，必须将其彻底隐藏。

在“字体”组中单击“对话框启动器”按钮，打开“设置单元格格式”对话框，单击“保护”选项卡，勾选“隐藏”复选框，如图8-33所示，单击“确定”按钮关闭该对话框。对工作表设置保护操作后，工作表中的公式被彻底隐藏。

8.5.5　将公式的计算结果转换为数值

由于公式结果与其引用的单元格是联动的关系，当单元格中的数据改变时，其对应的公式的计算结果将发生相应的变化，为了避免单元格中引用的公式结果不随引用单元格的值的改变而改变，可以将数据结果转换为数值。

选择包含公式计算结果的单元格或单元格区域，按【Ctrl+C】组合键复制数据，打开“选择性粘贴”对话框，选中“数值”单选按钮，单击“确定”按钮关闭对话框，完成公式结果转换为数值的操作，如图8-34所示，此时再选择计算结果单元格，在编辑栏中不会显示公式。

提示：在复制公式时完成公式数据结果的数值转换

上面介绍的是在完成复制操作后对公式结果进行转换，也可以在复制公式时对计算结果进行转换，其操作方法是：复制包含公式的单元格，选择该单元格和其他需要复制相同公式的单元格，然后在“选择性粘贴”对话框中使用“数值”方式粘贴数据，在复制公式的同时完成公式计算结果的数值转换。

8.6　使用函数

公式的常规输入和编辑操作在函数中也适用，如手动输入函数、复制函数、编辑函数、修改函数等，对于函数而言，它具有一些特殊的操作，下面将对其进行具体介绍。

8.6.1　使用函数库输入函数

在Excel中，使用函数库输入函数主要是通过“公式”选项卡的“函数库”组，如图8-35所示，或单击编辑栏中的“插入函数”按钮，在打开的“插入函数”对话框中实现函数的插入。

在“插入函数”对话框中选择需要的函数后，单击“确定”按钮，在打开的“函数参数”对话框中设置参数后，单击“确定”按钮完成函数的计算，如图8-36所示。

在“函数库”组中列出了各类函数，单击函数类别按钮右侧的下拉按钮，在弹出的下拉菜单中选择需要插入的函数即可。

例如，要插入求和函数SUM（）来计算使用员工的考核总分，其具体操作如下。

Step 01　【选择函数】选择目标单元格，单击“公式”选项卡，在“函数库”组中单击“数学和三角函数”按钮，选择“SUM”选项，如图8-37所示。

Step 02　【设置参数】打开“函数参数”对话框，在“Number1”文本框中，系统自动设置了需要计算数据的数据源单元格区域，单击“确定”按钮完成计算，如图8-38所示。

在“函数参数”对话框中，用户可以直接在对应的参数文本框中输入需要参加计算的单元格区域，也可以单击“折叠”按钮，在返回的工作表中手动选择单元格区域。

拓展学习　插入公式的快捷操作

在Excel 2010中，系统将常用的几个函数，如求和函数、平均值函数、计数函数、最大值函数和最小值函数归类在“自动求和”下拉菜单中。

此外，在“公式”选项卡的“函数库”组的“最近使用的函数”下拉菜单中罗列了用户最近使用过的函数，如图8-39所示，通过这两个菜单用户可以快速选择需要的函数。

8.6.2　如何快速查找需要的函数

系统内置了很多的工作表函数，如果要逐一记住所有的函数名称或者分类，将是一件很烦琐的事情，而且容易出错，此时可以使用系统提供的搜索函数功能快速查找到需要的函数。

◆根据名称查找函数：是指在“插入函数”对话框的“选择函数”列表框中任意选择一个函数，然后在键盘上按函数的前几个字母对应的键，便可自动跳到以该字母开头的函数处，如图8-40所示，并且在列表框的下方显示该函数的语法和功能。

◆根据功能查找函数：是指在“插入函数”对话框的“搜索函数”文本框中输入关键字，如“平均值”，单击“转到”按钮，系统会自动查找与关键字相关的函数，如图8-41所示。

8.6.3　查找函数的具体帮助

在Excel中，“插入函数”对话框除了查找需要使用的函数以外，还可以查看某个函数具体的使用帮助信息。

打开“插入函数”对话框，在“选择函数”列表框中找到需要的函数，如选择“AVERAGE”函数，然后单击对话框下方的“有关该函数的帮助”超链接，打开“Excel帮助”窗口，如图8-42所示，在其中即可查看该函数的具体使用帮助。

8.7　公式和函数使用中的常见错误

Excel具备自动纠错的功能，但是对于某些比较复杂的错误类型，需要用户手动进行纠错。在Excel中，常见的Excel错误类型及解决方法如表8-5所示。

续表