第9章　公式与函数的运用

Excel中有大量的公式函数可以供用户选择，使用Excel可以实现执

行计算，分析信息，管理电子表格或网页中的数据信息列表和制作数据资料图表等功能，带给用户方便。

·使用公式进行数据计算

·使用引用功能快速完成数据的计算

·审核公式

·认识和使用函数

·应用数组公式

·常用函数应用举例

9.1　使用公式进行数据计算

在Excel中建立财务报表后，通常都需要进行相关的数据计算，这

也是使用Excel程序的目的。在进行数据计算时，需要建立公式来实

现，而在使用公式时，需要引用单元格的值进行运算，还需要使用相关的函数来完成特定的计算。在公式中使用特定的函数可以简化公式的输入，同时完成一些特定的计算需求。

公式可以说成在Excel中由用户自行设计，对工作表进行计算和处理的计算式。如=SUM(A2:A10)*B1+100。

这种形式的表达式就称为公式。它要以等号“=”开始（不以“=”开头不能称为公式），等号后面可以包括函数、引用、运算符和常量。上式中的“SUM（A2:A10）”是函数，“B1”是对单元格B1值的引用（计算时使用B1单元格中显示的数据），“100”是常量，“*”和“+”则是算术运算符。

9.1.1　公式的运算符

运算符是公式的基本元素，也是必不可少的元素，每一个运算符代表一种运算。在Excel中有4类运算符类型，每类运算符和作用如表9-1所示。

表9-1　运算符种类和作用

9.1.2　运算符的优先级

当运算公式中使用了多个运算符时，Excel在计算时可能不再按照

从左向右的顺序进行运算，而是根据各运算符的优先级进行运算。对于同一级别的运算符，再按照从左至右的顺序计算，可见公式中运算符优先级的重要性，只有熟知各运算符的优先级别，才有可能避免公式编辑和运算中出现错误，各运算符优先顺序如表9-2所示。

表9-2　运算符的优先级

此外，括号的优先级高于表9-2中所有的运算符，故可以利用括号

来调整运算符号的优先级别。若公式中使用了括号，那么就应由最内层的括号逐级向外进行运算。

9.1.3　输入公式

在工作表的空白单元格中输入等号，Excel就默认该单元格将输入

公式。公式既可以直接手动输入，也可以通过单击或拖动鼠标来引用单元格或单元格区域输入。具体操作如下。

步骤1：打开Excel表格，选中F4单元格，并在其中输入“=B4-C4-

D4-E4”，输入完成后，按“Enter”键，如图9-1所示。步骤2：查看公式计算出的结果，如图9-2所示。

图9-1　输入公式

图9-2　计算结果

技巧点拨：用户不仅可直接在单元格中输入公式，还可在编辑栏中输入，输入完成后单击编辑栏左侧的“输入”按钮，或者按“Enter”键得出计算结果。

9.1.4　复制公式

为了提高工作效率，当需要完成相同计算时，在单元格中输入公式后，可使用拖动法或填充的方式将公式复制到其他单元格中。具体操作如下。

步骤1：打开Excel表格，选中F5单元格，在“开始”选项卡的“编

辑”组中单击“填充”按钮，在展开的下拉列表中选择“向下”选项，如图9-

3所示。

图9-3　选择“向下”选项

步骤2：F5单元格中显示了结果，并且在编辑栏中显示了复制的公

式。选中F5单元格，并将指针移至其右下角，按住鼠标左键不放并向下拖动，如图9-4所示。

步骤3：拖动鼠标时经过的单元格就显示了计算结果，选中任意复

制了公式的单元格，在编辑栏中显示了应用的公式，如图9-5所示。

图9-4　显示结果

图9-5　查看结果

技巧点拨：复制公式还可以利用复制和粘贴的功能，选中并右击保护公式的单元格，在弹出的快捷菜单中单击“复制”命令，然后选中需要粘贴公式的单元格并右击，在弹出的快捷菜单中单击“粘贴选中”组中的“公式”按钮即可。此外还可利用拖动鼠标法，将光标指向需要复制公式的单元格右下角，拖动填充柄，释放鼠标后就可完成公式的复制。

9.2　使用引用功能快速完成数据的计算

在使用公式进行计算时，常常需要使用单元格或单元格区域来引用工作表中的一个或多个单元格，或者引用其他工作表中的单元格，以达到快速计算的目的。根据引用方式的不同，可分为相对引用、绝对引用和混合引用三种。

9.2.1　相对引用

在利用相对引用来复制公式时，公式所在的单元格和当前单元格的位置是相对的，所以当公式所在的单元格位置发生改变时，引用的单元格也会随之改变。在默认情况下，公式中对单元格的引用都是相对引用。具体操作如下。

步骤1：打开Excel表格，在D3单元格中输入公式“=C3/B3”，该公式对单元格采用了相对应用，按“Enter”键得出计算结果，如图9-6所示。

步骤2：继续填充公式，此时公式中引用的单元格地址就会发生相

应的变化，如图9-7所示，如选中D6单元格，其公式为C6/B6，对原公式进行了相对引用。

图9-6　查看结果

图9-7　查看公式变化

9.2.2　绝对引用

当单元格列和行标签前同时添加$符号时，就表明该单元格使用了

绝对引用。在输入公式时，若不需要公式自动调整单元格的引用位置，则需使用绝对引用。具体操作如下。

步骤1：打开Excel表格，在E4单元格中输入公式“=D4*$B$2”，其中B2单元格应用了绝对引用，如图9-8所示。

步骤2：按“Enter”键在E4单元格中就显示了计算结果，向下复制公

式，其中D列引用的单元格会随着E列单元格的变换而变换，但应用了绝对引用的B2单元格不变，如图9-9所示。

图9-8　输入公式

图9-9　依次计算结果

9.2.3　混合引用

混合引用是指在利用公式引用单元格时，包含一个绝对引用坐标和一个相对引用坐标的单元格引用。既可以绝对引用行相对引用列，也可以相对引用列绝对引用行。具体操作如下。

步骤1：打开Excel表格，选中C6单元格，并输入公

式“=$B6*B$3”，公式中使用了混合引用，如图9-10所示。

步骤2：向下和向右复制公式后，得到不同日期、不同样式的手机价格，例如经过混合引用，D8单元格的公式为“$B8*C$3”，如图9-11所示。

图9-10　查看C6单元格公式

图9-11　查看D8单元格公式

9.2.4　对其他工作表数据的引用

在输入公式时，除了引用同一工作表中的数据，还可引用其他工作

表中的单元格，其一般格式是：工作表名！单元格地址。工作表名后的“！”是引用工作表时系统自动加上的。具体操作如下。

步骤1：打开Excel表格，切换至“2月”工作表，在B4单元格中输入“=”，如图9-12所示。

图9-12　在B4单元格中输入“=”

步骤2：单击“1月”工作表标签，选中E4单元格，编辑栏中自动显示了“‘1月！’E4”，如图9-13所示。

步骤3：按“Enter”键后，返回“2月”工作表中，此时B4单元格就显示

了引用的数据，选中B4单元格，并向下拖动填充柄，此时B4:B7单元格

区域就引用了“1月”工作表中E4:E7单元格区域中的数据，如图9-14所示。

图9-13　查看“1月”工作表

图9-14　自动引用1月结余

9.3　审核公式

在完成公式的输入后，用户可通过命令、宏或错误值发现公式计算是否有错。在发现错误后，可通过公式中返回的错误值的类型分析错误原因，还可使用Excel提供的公式审核功能对工作表中的错误进行检查或追踪，此外还可利用追踪箭头方式追查出错的起源。

9.3.1　认识公式中返回的错误值

在使用公式进行计算时，有时运算结果会以一些特殊的符号显示，这些符号就是公式返回的错误值，说明公式在计算过程中出错。用户可根据错误值的类型判断并找出出错的原因，从而用不同的方法来解决，表9-3列出了常见的公式错误值、原因和相应的解决方法。表9-3　常见的公式错误值、原因和解决方法

9.3.2　公式错误检查

当公式返回的是一个错误值时，除了利用错误值的类型判断公式出

错的原因，也可利用Excel中的错误检查来迅速找出错误，从而修改公式，得到正确的运算结果。具体操作如下。

步骤1：打开Excel表格，在E4单元格中利用公式算出结果后，向下

拖动鼠标进行填充，但是返回的都是错误值，选中某一错误值，单击“公式审核”组中的“错误检查”按钮，如图9-15所示。

步骤2：弹出“错误检测”对话框，单击“显示计算步骤”按钮，如图9-

16所示。

图9-15　单击“错误检查”按钮

图9-16　单击“显示计算步骤”按钮

步骤3：弹出“公式求值”对话框，在“求值”列表框中显示了所选单

元格的求值流程，如图9-17所示，检查公式出错的原因。

步骤4：单击“关闭”按钮后返回“错误检查”对话框中，然后单击“在编辑栏中编辑”按钮，如图9-18所示。

图9-17　检查公式出错的原因

图9-18　单击“在编辑栏中编辑”按钮

技巧点拨：在“错误检查”对话框中，用户单击“显示计算步骤”按钮后，会弹出“公式求值”对话框，可查看整个计算流程。若单击“选项”按钮会弹出“Excel选项”对话框，在“错误检查规则”可通过勾选复选框设置错误的检查规则。而在“错误检查”区域中，可勾选“允许后台错误检查”复选框，以保证及时查看错误并更正，还可在“使用此颜色标识错误”框中，选择标记误差发生位置的颜色。

步骤5：在编辑栏中将单元格的公式更改为“=C6+D6+B2”，如图9-

19所示。

步骤6：利用新公式向下填充单元格，此时错误得到修改，得到了

正确的计算结果，如图9-20所示。

图9-19　公式更改为“=C6+D6+B2”

图9-20　正确的计算结果

9.3.3　用追踪箭头标识公式

在检查公式时，可利用Excel中的追踪功能来查看公式所在单元格

的引用单元格和从属单元格，从而了解公式和值的关系。在完成公式的追踪后，可利用“移去箭头”功能选择要删除的追踪箭头。

1.追踪引用单元格

追踪引用单元格，是指利用箭头标识出公式所在单元格和公式所引用的单元格，其中箭头所在的单元格就是公式所在位置，而蓝色圆点所

在的单元格就是所有引用的单元格。具体操作如下。

步骤1：打开Excel表格，选中任意一个含有公式的单元格，如F4单元格，在“公式”选项卡下单击“追踪引用单元格”按钮，如图9-21所示。

步骤2：B4单元格和F4单元格之间出现一个箭头，蓝色圆点代表所在单元格，蓝色箭头表示所在单元格为引用单元格，如图9-22所示。

图9-21　单击“追踪引用单元格”按钮

图9-22　引用单元格

2.追踪从属单元格

若某一单元格公式中引用了其他单元格，那么被引用的单元格就是

从属单元格，例如A3单元格的公式引用了A2单元格，那么A2单元格就是A3单元格的从属单元格。具体操作如下。

步骤1：打开Excel表格，选中C2单元格，单击“追踪从属单元格”按钮，如图9-23所示。

步骤2：工作表中出现了从C2单元格出发的箭头，箭头指向的单元格就是C2单元格引用的从属单元格，如图9-24所示。

图9-23　单击“追踪从属单元格”按钮

图9-24　C2单元格引用的从属单元格

9.4　认识和使用函数

Excel中的函数是一些预定义的公式，它可运用一些参数并按照特

定的顺序和结构对数据进行复杂的计算。使用函数进行计算可以简化公式的输入过程，并且只需设置函数的必要参数就可进行正确的计算，所以与使用公式进行计算比较，使用函数占用的空间小，速度更快。

9.4.1　函数的构成

函数的类型虽然多样，但其结构却大同小异，输入函数时以等号开头，然后是函数名、括号、参数、参数分隔符，组成一个完成的函数结构。下面以函数“=SUM(A1,B2,C3)”为例，具体介绍函数的结构：

9.4.2　函数的参数及其说明

函数分为有参数函数和无参数函数。当函数有参数时，其参数就是指函数名称后圆括号内的常量值、变量、表达式或函数，多个参数间使用逗号分隔。无参数的函数只有函数名称与()组成，如NA()。在Excel中

绝大多数函数都是有参数的。

在使用函数时，如果想了解某个函数包含哪些参数，可以按如下方法查看。

步骤1：选中单元格，在公式编辑栏中输入“=函数名(”，此时可以看到显示出函数参数名称，如图9-25所示。

步骤2：如果想更加清楚地了解每个参数该如何设置，可以单击公式编辑栏前的“”按钮，打开“函数参数”对话框，将光标定位到不同参数编辑框中，则可以看到该参数设置的提示文字，如图9-26所示。

图9-25　公式编辑栏

图9-26　“函数参数”对话框

函数参数类型举例如下：

1. 公式“=SUM(B2:B10)”中，括号中的“B2:B10”就是函数的参数，且是一个变量值。
2. 公式“=IF(D3=0,0,C3/D3)”中，括号中“D3=0”“0”“C3/D3”分别为IF函数的3个参数，且参数为常量和表达式两种类型。
3. 公式“=VLOOKUP($A$9,$A$2:$D$6,COLUMN(B1))”中，除了使用了变量值作为参数，还使用了函数表达式“COLUMN(B1)”作为参数

（以该表达式返回的值作为VLOOKUP函数的3个参数），这个公式是函数嵌套使用的例子。函数可以嵌套使用，嵌套使用时将某个函数的返回结果作为另一个函数的参数来使用。有时为了达到某一计算要求，需要在设置公式时嵌套多个函数，需要用户对各个函数的功能及其参数有详细的了解。

9.4.3　插入函数

在Excel 2016中，可以使用“插入函数”对话框来完成函数的输入。对于不太熟练的读者来说，一般使用“插入函数”对话框来利用函数的强大功能。插入函数的操作步骤如下。

步骤1：打开Excel表格，选中C7单元格，单击编辑栏中的“插入函数”按钮，打开“插入函数”对话框，在“选择函数”下方的列表框中选择

SUM选项，然后单击“确定”按钮，如图9-27所示。

图9-27　“插入函数”对话框

步骤2：打开“函数参数”对话框，自动选择计算的单元格区域为

C1:C6，单击“确定”按钮，如图9-28所示。

步骤3：返回该单元格区域的计算结果，如图9-29所示。

图9-28　“函数参数”对话框

图9-29　计算结果

9.4.4　函数的种类

不同的函数可以达到不同的计算目的，在Excel提供了300多个内置函数，为满足不同的计算需求，划分了多个函数类别。

1.了解函数的类别及其包含的函数

具体操作如下。

步骤1：单击“公式”菜单，在“函数库”选项组中显示了多个不同的

函数类别，单击函数类别可以查看该类别下所有的函数（按字母顺序排列），如图9-30所示。

步骤2：单击“其他函数”按钮，可以看到还有其他几种类别的函数，如图9-31所示。

步骤3：单击“插入函数”按钮打开“插入函数”对话框，在“或选择类

别”框下拉菜单中显示了各个函数类别，选择类别后，在下面的列表中将显示出该类别下的所有函数，如图9-32所示。

图9-30　查看该类别下所有的函数

图9-31　查看其他函数

图9-32　“插入函数”对话框

2.“自动求和”的使用

在输入函数时，Excel系统还提供了“自动求和”功能，可以快速插入求和、平均值、计数、最大值、最小值等函数，方便用户快速输入使用频率较高的函数。具体操作如下。

步骤1：打开Excel表格，选中要显示自动求和的单元格，切换

至“公式”选项卡，单击“函数库”组中的“自动求和”右侧的下三角按钮，在展开的下拉列表中单击“求和”选项，如图9-33所示。

步骤2：自动在所选单元格中输入求和公式“=SUM()”，输入需要引用的单元格，按“Enter”键即可快速计算出结果，如图9-34所示。

图9-33　单击“求和”选项

图9-34　查看计算结果

3.通过“有关函数的帮助”学习函数

如果想了解某个函数的详细用法，可以通过Excel帮助实现查看。具体操作如下。

步骤1：单击“公式”菜单，然后单击“插入函数”按钮，打开“插入函

数”对话框，在“选择函数”列表中选中需要了解的函数（如EFFECT），单击对话框左下角的“有关该函数的帮助”链接，如图9-35所示。

步骤2：进入“Microsoft Excel帮助”窗口中，显示该函数的作用、语法及使用示例（向下滑动窗口可以看到），如图9-36所示。

图9-35　“插入函数”对话框

图9-36　函数的作用、语法

9.5　应用数组公式

应用数组公式，可以方便地对列表以及数组进行计算，同时也可提高计算大量数据的速度。本节将介绍一些数组公式的应用实例，主要包括N个最大/小值的求和、为指定范围内的数值分类的操作方法等。

9.5.1　N个最大/小值求和

LARGE函数可用于计算N个最大/小值的和，可返回一个范围内N个

最大/小值的和。下面以计算某班级期末考试各科成绩最高分的和为

例，介绍使用数组公式对N个最大/小值求和的操作步骤。本例的原始数据如图9-37所示。

图9-37　原始数据

步骤1：选中H3单元格，在编辑栏中输

入“=SUM(LARGE(B3:G9,ROW(INDIRECT(“1:6”))))”，如图9-38所示。

步骤2：按“Ctrl+Shift+Enter”组合键，计算出各科成绩最大值的和，如图9-39所示。

图9-38　输入公式

图9-39　计算各科成绩最大值的和

技巧点拨：在公

式“=SUM(LARGE(B3:G9,ROW(INDIRECT(“1:6”))))”中，LARGE函数共计算6次，返回“数学”“语文”“英语”“物理”“化学”和“生物”的最大值，并将计算结果存储在数组中，将其作为SUM函数的参数进行计算。

步骤3：如果计算各科成绩最小值的和，需要将LARGE函数更换为

SMALL函数，此时编辑栏中的公式更改

为“=SUM(SMALL(B3:G9,ROW(INDIRECT(“1:6”))))”，如图9-40所示。

步骤4：按“Ctrl+Shift+Enter”组合键，计算出各科成绩最小值的和，

如图9-41所示。

图9-40　输入公式

图9-41　计算各科成绩最小值的和

9.5.2　为指定范围内的数值分类

使用数组公式可以对单列中的数值进行分类，并按照从高到低或从

低到高的顺序进行排列。要实现上述功能，需要使用LARGE函数嵌套

INDIRECT函数及ROW函数。使用数组为指定范围内的数值分类时，如果遇到未包含数值的单元格，则会返回错误值“#NUM！”。

为了避免返回错误值，可以使用IF函数进行判断，未包含数值的返回空单元格。为指定范围中的数值进行分类的操作步骤如下所述。本例的原始数据如图9-42所示。

图9-42　原始数据

步骤1：选中B3:B15单元格区域，然后在编辑栏中输入“=IF()”，如图9-43所示。

步骤2：在公式的括号中输

入“ISERR(LARGE(A3:A16,ROW(INDIRECT(“1:”&ROWS(A3:A16))))),”，作为IF函数的第一个参数，如图9-44所示。

图9-43　输入公式

图9-44　输入IF函数的第一个参数

技巧点拨：在IF函数的第一个参数中，LARGE函数用于对指定单

元格区域中的数值进行排序，然后使用ISERR函数去除因空白单元格而返回的错误值。

步骤3：继续输入“””,”作为IF函数的第二个参数，如图9-45所示。

步骤4：最后输

入“LARGE(A3:A16,ROW(INDIRECT(“1:”&ROWS(A3:A16))))”，作为IF 函数的第三个参数，如图9-46所示。

技巧点拨：第二个参数表示如果指定的单元格区域中有未包含数值

的单元格，返回空白单元格。在IF函数的第三个参数中，INDIRECT函数用于对指定的数组进行计算，并根据IF函数判断结果值使用LARGE 函数返回分类后的数值。

步骤5：按“Ctrl+Shift+Enter”组合键，将公式转换为数组公式，并显

示分类结果，如图9-47所示。

图9-45　输入IF函数的第二个参数

图9-46　输入IF函数的第三个参数

技巧点拨：在上述实例中，如果确定数组中未包含空白单元格，可以将公式简化

为“=LARGE(A3:A15,ROW(INDIRECT(“1:”&ROWS(A3:A15))))”，如图 9-48所示。

图9-47　计算结果

图9-48　未包含空白单元格的公式

9.6　常用函数应用举例

为了实现工作表中复杂的数字和文本处理，Excel提供了200多个内

置函数供用户选择使用。这些函数分为财务函数、统计函数、文本函数、信息函数以及数学和三角函数等类别，本节将列举一些函数的应用实例，帮助读者更好地掌握函数的应用方法。

9.6.1　应用MAX函数计算参数列表中的最大值

MAX函数用于计算一组值中的最大值。MAX函数的语法如下：

MAX(number1,number2,…)

其中参数number1,number2,…是要从中找出最大值的1到255个数字参数。

【典型案例】

已知一组给定的数据，计算数据列表中的最大值。

具体操作如下。

步骤1：打开工作簿，在单元格A8中输入公式“=MAX(A2:A6)”，用于计算上面一组数字中的最大值，如图9-49所示。

步骤2：在单元格A9中输入公式“=MAX(A2:A6,85)”，用于计算上面一组数字和85中的最大值。计算结果如图9-50所示。

图9-49　在单元格A8中输入公式

图9-50　计算结果

技巧点拨：参数可以是数字或者是包含数字的名称、数组或引用。逻辑值和直接键入到参数列表中代表数字的文本被计算在内。如果参数为数组或引用，则只使用该数组或引用中的数字。数组或引用中的空白单元格、逻辑值或文本将被忽略。如果参数不包含数字，函数MAX返回0。如果参数为错误值或为不能转换为数字的文本，将会导致错误。

如果要使计算包括引用中的逻辑值和代表数字的文本，则需要使用

MAXA函数。

9.6.2　应用ACCRINTM函数计算在到期日支付利息的债券的应计利息

ACCRINTM函数用于计算到期一次性付息有价证券的应计利息。

ACCRINTM函数的语法如下：

ACCRINTM(issue,settlement,rate,par,basis)

其中参数issue为有价证券的发行日。settlement为有价证券的到期

日。rate为有价证券的年息票利率。par为有价证券的票面价值，如果省略par，函数ACCRINTM视par为￥1000。basis为日计数基准类型。

【典型案例】

已知某债券的发行日、到期日、息票利率、票面值等信息，计算满足这些条件的应计利息。

具体操作如下。

步骤1：打开工作簿，在单元格A8中输入公

式“=ACCRINTM(A2,A3,A4,A5,A6)”用于计算，如图9-51所示。

步骤2：计算结果如图9-52所示。

图9-51　在单元格A8中输入公式

图9-52　计算结果

技巧点拨：参数issue、settlement和basis将被截尾取整。如果issue或

settlement不是有效日期，函数ACCRINTM返回错误值#VALUE!。如果

利率为0或票面价值为0，函数ACCRINTM返回错误值#NUM!。如果 basis<0或basis>4，函数ACCRINTM返回错误值#NUM!。如果

issue≥settlement，函数ACCRINTM返回错误值#NUM!。ACCRINTM的计算公式如下：

ACCRINTM=par×rate×A/D

式中：A=按月计算的应计天数；在计算到期应付的利息时指发行日与到期日之间的天数；D=年基准数。

9.6.3　应用SUM函数求和

SUM函数的功能是计算某一单元格区域中所有数字之和。其语法如下：

SUM(number1,number2,…)

其中参数number1，number2，…是要对其求和的1到255个参数。

【典型案例】

某地发生水灾，当地一家公司为发生水灾的地方组织捐款，计算这

个公司三个分公司的捐款数额，以及公司总的捐款数额。具体操作如下。

打开工作簿，在B6单元格中输入公式“=SUM(B2:B5)”，计算公司一

部的捐款总额；在D6单元格中输入公式“=SUM(D2:D5)”，计算公司二部的捐款总额；在F6单元格中输入公式“=SUM(F2:F5)”，计算公司三部的捐款总额；在G2单元格中输入公式“=SUM(B6,D6,F6)”，计算公司总捐款额，最终结果如图9-53所示。

图9-53　计算捐款数额

技巧点拨：SUM函数的用途比较广泛。在学校中可以求学生的总成绩，在会计部门可以求账务的总和等。对SUM函数来说，直接键入的参数表中的数字、逻辑值及数字的文本表达式将被计算。如果参数是一个

数组或引用，则只计算其中的数字。数组或引用中的空白单元格、逻辑值或文本将被忽略。如果参数为错误值或为不能转换为数字的文本，将会导致错误。