如何高效办公：Excel公式与函数其实很简单

孙晓南　著

本书由作者授权北京华章图文信息有限公司在全球范围内以网络出版形式出版发行本作品中文版，未经出版者书面许可，本书的任何部分不得以任何方式抄袭，翻录或翻印。

策划编辑：王龙元

封面设计：梁杰

客服热线：+ 86-10-68995265

客服信箱：service@bbbvip.com

官方网址：www.hzmedia.com.cn

新浪微博 @研发书局

腾讯微博 @yanfabook

目录

第1章 公式与函数基础

1.1 了解公式和函数

1.2 单元格引用

1.3 使用公式

1.4 使用函数

1.5 使用数组公式

1.6 使用定义名称

1.7 审核公式

1.8 错误分析与处理

第2章 逻辑与文本函数应用

2.1 逻辑函数应用

2.2 文本函数应用

2.3 文本转换

2.4 文本比较与删除

第3章 时间与日期函数应用

3.1 日期和时间的输入

3.2 计算期间差

3.3 对日期求和

第4章 财务函数应用

4.1 计算支付额和累计额

4.2 计算利率和资产

4.3 证券的计算

第5章 统计函数应用

5.1 计算平均值

5.2 其他统计函数应用

第6章 查找与引用函数应用

6.1 查找函数应用

6.2 引用函数应用

第7章 信息与工程函数应用

7.1 返回相应信息函数

7.2 数据检查与转换

7.3 数据的换算

第1章　公式与函数基础

在Excel中，利用公式和函数可以进行数据的运算和分析。一旦熟练掌握了公式和函数的使用，就能够大大提高办公效率。本章将介绍公式和函数的应用基础。

1.1　了解公式和函数

在Excel中利用公式可以对表格中的各种数据进行快速的计算。下面将简单介绍公式的组成、运算符、通配符和数组公式等，以便为熟练掌握公式的使用打下基础。

1.1.1　公式和函数的组成

公式由一系列单元格的引用、函数以及运算符等组成，是对数据进行计算和分析的等式。例如公式“=B3+SUM(A1:A3)”，其中“=“和“+”是运算符，“B3”是单元格引用，“SUM(A1:A3)”是函数。

而一个函数通常包含了标识符、函数名和参数这几部分，比如“=IF（B3>A3,1,0）”，其中“=“是标识符，“IF”是函数名，“B3>A3”、“1”和“0”是参数。”

下面来看看标识符、函数名和参数的具体功能。

·标识符：在Excel表格中输入函数式时，必须先输入“＝”号，“＝”号通常被称为函数式的标识符。

·函数名称：函数要执行的运算，位于标识符的后面，通常是其对应功能的英文单词缩写。

·函数参数：紧跟在函数名称后面的是一对半角圆括号“（）”，被括起来的内容是函数的处理对象，即参数表。

而进一步的，来看看函数参数，我们会发现，函数的参数既可以是常量（如“1”）或公式（如“B3>A3”），也可以为其他函数。常见的函数参数类型有以下几种。

·常量参数：主要包括文本(如“苹果”)、数值（如“1”）以及日期（如“2013-3-14”）等内容。

·逻辑值参数：主要包括逻辑真（如“TURE”）、逻辑假（如“FALSE”）以及逻辑判断表达式等。

·单元格引用参数：主要包括引用单个单元格（如A1）和引用单元格区域（如A1:C2）等。

·函数式：在Excel中可以使用一个函数式的返回结果作为另外一个函数式的参数，这种方式称为函数嵌套，如“=IF（A1>8,”优”,IF（A1>6,”合格”,”不合格”））”。

·数组参数：函数参数既可以是一组常量，也可以为单元格区域的引用。

提示：当一个函数式中有多个参数时，需要用英文状态的逗号将其隔开。

1.1.2　运算符的优先级

在使用公式计算数据时，运算符用于连接公式中的操作符，是工作表处理数据的指令。在Excel中，运算符的类型分为4种：算术运算符、比较运算符、文本运算符和引用运算符。

·常用的算术运算符主要有：加号“+”、减号“-”、乘号“*”、除号“/”、百分号“%”以及乘方“^”。

·常用的比较运算符主要有：等号“=”、大于号“>”、小于号“<”、小于等于号“<=”、大于等于号“>=”以及不等号“<>”。

·文本连接运算符只有与号“&”，该符号用于将两个文本值连接或串起来产生一个连续的文本值。

常用的引用运算符有：区域运算符“:”、联合运算符“,”以及交叉运算符“ ”（即空格）。

在公式的应用中，应注意每个运算符的优先级是不同的。在一个混合运算的公式中，对于不同优先级的运算，按照从高到低的顺序进行计算。对于相同优先级的运算，按照从左到右的顺序进行计算。

各种运算符的优先级（从高到低）为：冒号“:”、空格、逗号“,”、负数“-”、百分号“%”、乘方“^”、乘号“*”或除号“/”、加号“+”或减号“-”、连字符“&”、比较运算符“=”、“<”、“>”、“<=”、“>=”、“<>”。

例如公式：=A1+A2*(A3+A4)。其运算顺序就是(A3+A4)→A2*(A3+A4) →A1+A2*(A3+A4)。

1.1.3　函数的分类

在Excel的函数库中提供了多种函数，按函数的功能，通常可以将其分为以下几类。

·文本函数：用来处理公式中的文本字符串。如TEXT函数可将数值转换为文本，LOWER函数可将文本字符串的所有字母转换成小写形式等。

·逻辑函数：用来测试是否满足某个条件，并判断逻辑值。其中IF函数使用非常广泛。

·日期和时间函数：用来分析或操作公式中与日期和时间有关的值。如DAY函数可返回以序列号表示的某日期在一个月中的天数等。

·数学和三角函数：用来进行数学和三角方面的计算。其中三角函数采用弧度作为角的单位，如RADIANS函数可以把角度转换为弧度等。

·财务函数：用来进行有关财务方面的计算。如DB函数可返回固定资产的折旧值，IPMT函数可返回投资回报的利息部分等。

·统计函数：用来对一定范围内的数据进行统计分析。如MAX函数可返回一组数值中的最大值，COVAR函数可返回协方差等。

·查找与引用函数：用来查找列表或表格中的指定值。如VLOOKUP函数可在表格数组的首列查找指定的值，并由此返回表格数组当前行中其他列的值等。

·数据库函数：主要用来对存储在数据清单中的数值进行分析，判断其是否符合特定的条件。如DSTDEVP函数可计算数据的标准偏差。

·信息函数：用来帮助用户鉴定单元格中的数据所属的类型或单元格是否为空等。

·工程函数：用来处理复杂的数字，并在不同的计数体系和测量体系中进行转换，主要用在工程应用程序中。使用这类函数，还必须执行加载宏命令。

·其他函数：Excel还有一些函数没有出现在“插入函数”对话框中，它们是命令、自定义、宏控件和DDE等相关的函数。此外，还有一些使用加载宏创建的函数。

1.1.4　认识数组公式

所谓数组就是单元的集合或是一组处理的值集合。而数组公式就是对两组或多组名为数组参数的值进行多项运算，然后返回一个或多个结果的一种计算公式。

简单地说，可以把数组公式看成是有多重数值的公式。与单值公式最大的不同之处在于数组公式可以产生一个以上的结果。此外，一个数组公式可以占用一个或多个单元，数组的元素可多达6500个。

Excel中数组公式非常有用，尤其在不能使用工作表函数直接得到结果时，数组公式显得特别重要，它可以建立产生多值或对一组值而不是单个值进行操作的公式。

以求合计发放员工工资金额为例，使用数组公式{=SUM(B2:F2-B3:F3)}，意为将B2:F2单元格区域中的每个单元格，与B3:F3单元格区域中的每个对应的单元格相减，然后将每个结果加起来求和。

需要注意，在数组公式中，每个数组参数都要求必须有相同数量的行和列。同时，按下“Ctrl+Shift+Enter”组合键而不是“Enter”键进行确认，输入的才是数组公式。

提示：输入公式后按下“Ctrl+Shift+Enter”组合键，即可确认输入数组公式，完成后可以看到公式的两端出现一对大括号“{}”，这是数组公式的标志。

1.2　单元格引用

单元格的引用是指在Excel公式中使用单元格的地址来代替单元格及其数据。下面将介绍单元格引用样式、相对引用、绝对引用和混合引用的相关知识，以及在同一工作薄中引用单元格的方法和跨工作薄引用单元格的方法。

1.2.1　A1格式引用数据

A1引用样式是用地址来表示单元格引用的一种方式，是Excel默认的引用样式。在A1引用样式中，用列号（大写英文字母，如A、B、C）和行号（阿拉伯数字，如1、2、3）表示单元格的位置。

1.2.2　R1C1格式引用数据

R1C1引用样式是用地址来表示单元格引用的另一种方式。在R1C1引用样式中，用R加行数字和C加列数字表示单元格的位置。

R1C1引用样式不是Excel默认的引用样式，要在工作表中使用R1C1样式，需要进行如下设置：在工作表中切换到“文件”选项卡，单击“选项”命令，打开“Excel选项”对话框，切换到“公式”选项卡，在“使用公式”栏中勾选“R1C1引用样式”复选框，单击“确定”按钮，返回工作表，选中包含了引用的单元格或区域，即可看到使用R1C1引用样式后的效果了。

1.2.3　相对引用

单元格引用的作用是标识工作表上的单元格或单元格区域，并指明公式中所用的数据在工作表中的位置。单元格的引用通常分为相对引用、绝对引用和混合引用。默认情况下，Excel 2013使用的是相对引用。

使用相对引用，单元格引用会随公式所在单元格的位置变更而改变。如在相对引用中复制公式时，公式中引用的单元格地址将被更新，指向与当前公式位置相对应的单元格。

以“成绩表”为例：将F3单元格中的公式“=B3+C3+D3+E3” 通过“Ctrl+C”和“Ctrl+V”组合键复制到F4单元格中，可以看到，复制到F4单元格中的公式更新为“=B4+C4+D4+E4”，其引用指向了与当前公式位置相对应的单元格。

1.2.4　绝对引用

对于使用了绝对引用的公式，被复制或移动到新位置后，公式中引用的单元格地址保持不变。需要注意在使用绝对引用时，应在被引用单元格的行号和列标之前分别加入符号“$”。

以“学生成绩表”为例：在F3单元格中输入公式“=$B$3+$C$3+$D$3+$E$3”，此时再将F3单元格中的公式复制到F4单元格中，可发现两个单元格中的公式一致，并未发生任何改变。

1.2.5　混合引用

混合引用是指相对引用与绝对引用同时存在于一个单元格的地址引用中。如果公式所在单元格的位置改变，相对引用部分会改变，而绝对引用部分不变。混合引用的使用方法与绝对引用的使用方法相似，通过在行号和列标前加入符号“$”来实现。

以“学生成绩表”为例：在F3单元格中输入公式“=$B3+$C3+$D3+$E3”，此时再将F3单元格中的公式复制到G4单元格中，可发现两个公式中使用了相对引用的单元格地址改变了，而使用绝对引用的单元格地址不变。

1.2.6　快速切换引用模式

按“F4”键即可使单元格地址在相对引用、绝对引用与混合引用之间进行切换。

以“学生成绩表”为例：选中F5单元格，在编辑栏中将光标定位到“B3”后，按“F4”键，即在其行号和列标前加入符号“$”，用同样的方法在“C3”、“D3” 、“E3”的行号和列标前插入符号“$”，公式就转换成为了=$B$3+$C$3+$D$3+$E$3”。

提示：逐次按下“F4”键，可以使该单元格引用在$B$3、B$3、$B3、B3之间快速切换。

1.2.7　引用同一工作薄中的数据

Excel不仅可在同一工作表中引用单元格或单元格区域中的数据，还可引用同一工作簿中多张工作表上的单元格或单元格区域中的数据。在同一工作簿不同工作表中引用单元格的格式为“工作表名称！单元格地址”，如“Sheet1！F5”即为Sheet1”工作表中的F5单元格。

以在“职工工资统计表”工作薄的“Sheet2”工作表中引用“Sheet1”工作表中的单元格为例，方法如下。

步骤1　打开“职工工资统计表”工作簿，在“Sheet2”工作表的E3单元格中输入“=”。

步骤2　切换到“Sheet1”工作表，选中F4单元格。

步骤3　此时按下“Enter”键，即可将“Sheet1”工作表F4单元格中的数据引用到“Sheet2”工作表的E3单元格中。

1.2.8　跨工作薄引用数据

跨工作薄引用数据，即引用其他工作簿中工作表的单元格数据的方法，与引用同一工作簿不同工作表的单元格数据的方法类似。一般格式为：工作簿存储地址[工作簿名称]工作表名称！单元格地址。

以在“工作簿1”的“Sheet1”工作表中引用“职工工资统计表”工作簿的“Sheet1”工作表中的单元格为例，方法如下。

步骤1　同时打开“职工工资统计表”和“工作簿1”工作簿，在“工作簿1”的“Sheet1”工作表中选中F3单元格，输入“=”

步骤2　切换到“职工工资统计表”工作簿的“Sheet1”工作表，选中F4单元格。

步骤3　此时按下“Enter”键，即可将“职工工资统计表”工作簿的Sheet1工作表中F4单元格内的数据引用到“工作簿1”的Sheet1工作表F3单元格中了。

1.3　使用公式

公式是对数据进行计算和分析的等式。在Excel中要对数据进行快速的计算就离不开公式。下面将介绍公式的使用方法。

1.3.1　输入公式

公式可以在单元格或编辑栏中输入。输入公式都是以“=”开始，然后再输入运算项和运算符，输入完毕按下“Enter”键后计算结果就会显示在单元格内。手动输入和使用鼠标辅助输入为输入公式的两种常用方法，下面分别进行介绍。

1．手动输入

以在“职工工资统计表”中计算“应发工资”为例，手动输入公式的方法为：打开“职工工资统计表”工作薄，在F4单元格内输入公式“=C4+D4+E4”，按下“Enter”键，即可在F4单元格中显示计算结果。

2．使用鼠标辅助输入

在引用单元格较多的情况下，比起手动输入公式，有些用户更习惯使用鼠标辅助输入公式，方法如下。

步骤1　打开“职工工资统计表”工作簿，在F5单元格内输入等于符号“=”。

步骤2　单击C5单元格，此时该单元格周围出现闪动的虚线边框，可以看到C5单元格被引用到了公式中。

步骤3　在F5单元格中输入运算符“+”，然后单击D5单元格，此时D5单元格也被引用到了公式中。用同样的方法引用E5单元格。

步骤4　操作完毕后按下“Enter”键确认公式的输入，此时即可得到计算结果。

1.3.2　修改公式

在Excel中创建了公式后，如果想要修改公式，可以参照修改单元格数据的方法进行。选中要修改公式的单元格，将光标定位到编辑栏中，根据需要修改公式，然后按下“Enter”键确认即可。

1.3.3　以非“=”符号开头输入公式

一般情况下，我们使用“=”符号开头输入公式。其实使用“+”和“-”符号开头，也可以输入公式。

在要输入公式的单元格中，先输入“+”符号，再输入相关运算符和函数，然后按下“Enter”键，程序会自动在公式前加上“=”符号。

在要输入公式的单元格中，先输入“-”符号，再输入相关运算符和函数，然后按下“Enter”键，程序会自动在公式前加上“=”符号，并将第一个数据当做负值来计算。

1.3.4　复制公式

在Excel中创建了公式后，如果想要将公式复制到其他单元格中，可以参照复制单元格数据的方法进行复制。方法如下。

·将公式复制到一个单元格中：选中需要复制的公式所在的单元格，按下“Ctrl+C”组合键，然后选中需要粘贴公式的单元格，按下“Ctrl+V”组合键即可完成公式的复制，并显示出计算结果。

·将公式复制到多个单元格中：选中需要复制的公式所在的单元格，将光标指向该单元格的右下角，当鼠标指针变为
形状时按住左键向下拖动，拖至目标单元格时释放鼠标，即可将公式复制到鼠标指针所经过的单元格中，并显示出计算结果。

1.3.5　快速显示工作表中所有公式

利用Excel中的“公式审核”工具可以快速地显示出当前工作表中所有的公式，方法为：打开需要显示所有公式的工作表，切换到“公式”选项卡，单击“公式审核”组中的“显示公式”按钮，返回工作表，即可在工作表中看到所有的公式显示出来了。

1.3.6　隐藏公式

为了不让他人看到自己使用的公式，可以将其隐藏起来。公式被隐藏后，选定该单元格，其中使用的公式不会出现在编辑栏中。方法如下。

步骤1　在工作表中选中要隐藏公式的单元格或区域，单击“开始”选项卡的“字体”组右下角的功能扩展按钮 。

步骤2　弹出“设置单元格格式”对话框，切换到“保护”选项卡，勾选“隐藏”复选框，然后单击“确定”按钮。

步骤3　在“开始”选项卡的“单元格”组中，单击“格式”下拉按钮，在下拉列表中单击“保护工作表”命令。

步骤4　弹出“保护工作表”对话框，确认已选中“保护工作表及锁定的单元格内容”复选框，单击“确定”按钮。

完成上述设置后，返回工作表，使刚设置了隐藏功能的单元格或区域保持选中状态，在编辑栏中即可看到公式被隐藏了

1.3.7　保护公式

隐藏单元格中的公式后，仍可以对计算结果进行更改。要实现保护工作表中所有公式不被更改，可以通过下面的方法实现。

步骤1　在工作表中选中包含公式的单元格或区域，单击“开始”选项卡的“字体”组右下角的功能扩展按钮 。

步骤2　弹出“设置单元格格式”对话框，切换到“保护”选项卡，勾选“锁定”复选框，单击“确定”按钮。

步骤3　在“开始”选项卡的“单元格”组中，单击“格式”下拉按钮，在弹出的下拉列表中单击“保护工作表”命令。

步骤4　弹出“保护工作表”对话框，确认已选中“保护工作表及锁定的单元格内容”复选框，在文本框中输入密码，单击“确定”按钮。

步骤5　弹出“确认密码”对话框，在其中再次输入密码，单击“确定”按钮即可。

进行上述操作后，修改执行了保护操作的单元格中的公式时，将会弹出提示信息，保护公式不被修改。

1.3.8　将公式转换为值

在使用公式完成数据计算后，还可以将公式转换为值，只保留计算结果。方法如下。

·选中目标单元格区域，按下“Ctrl+C”组合键复制公式和计算结果，然后在“开始”选项卡的“粘贴”组中单击“粘贴”下拉按钮，在打开的下拉菜单中选择“值”选项，粘贴数值即可。

·选中目标单元格区域，按下“Ctrl+C”组合键复制公式和计算结果，然后按下“Ctrl+V”组合键粘贴，此时将出现“粘贴选项”按钮，单击该按钮，在打开的下拉菜单中选择“值”选项，粘贴数值即可。

1.3.9　设置公式计算迭代次数

在Excel中，可以根据需要设置公式计算迭代次数。方法为：切换到“文件”选项卡，单击“选项”命令，打开“Excel 选项”对话框，切换到“公式”选项卡，在“计算选项”栏中勾选“启用迭代计算”复选框，然后在“最多迭代次数”微调框和“最大误差”文本框中设置相应的参数，完成后单击“确定”按钮即可。

1.4　使用函数

在Excel中利用函数可以轻松完成各种复杂数据的处理工作，并简化公式的使用。下面将介绍函数的使用方法。

1.4.1　手动输入函数

如果知道函数名称及语法，可直接在编辑栏内按照函数表达式输入。

方法为：选择要输入函数的单元格，输入等号“=”，然后输入函数名和左括号，紧跟着输入函数参数，最后输入右括号，函数输入完成后单击编辑栏上的“输入”按钮
或按下“Enter”键即可。

例如，在单元格内输入“=SUM（F2:F5)”，意为对F2到F5单元格区域中的数值求和。

1.4.2　使用“自动求和”按钮输入函数

对于一些常用的函数式，如求和（SUM）、平均值（AVERAGE）、计数（COUNT）等，可以利用“开始”或“公式”选项卡中的快捷按钮来实现输入。下面以求和函数为例，介绍通过快捷按钮插入函数的方法。

·利用“开始”选项卡快捷按钮：选中需要求和的单元格区域，单击“开始”选项卡的“编辑”组中的“自动求和”下拉按钮，在弹出的下拉菜单中选择“求和”命令即可。

·利用“公式”选项卡快捷按钮：选中需要显示求和结果的单元格，然后切换到“公式”选项卡。在“函数库”组中单击“自动求和”下拉按钮，在弹出的下拉菜单中单击“求和”命令，然后拖动鼠标选中作为参数的单元格区域，按下“Enter”键即可将计算结果显示到该单元格中。

1.4.3　通过“函数库”输入函数

对于大多数常用的函数，都可以在功能区中的“公式”选项卡中找到，方便输入。下面以输入一个财务类函数为例，方法如下。

步骤1　选中需要输入函数的单元格，输入等号“=”，切换到“公式”选项卡，在“函数库”组中单击需要的函数类型，本例单击“日期和时间”下拉按钮，在弹出的下拉列表中单击需要的函数。

步骤2　弹出“函数参数”对话框，在其中设置好参数或参数所在单元格，然后单击“确定”按钮即可。

1.4.4　通过提示功能快速输入函数

如果用户对函数不是一无所知，能记住不少的常用函数名，那么就可以利用函数提示功能快速函数。

具体方法为：选中需要输入函数的单元格，输入“=”，然后输入函数的首字母，此时我们会得到系统提供的函数提示，在推荐函数中选中需要的那个，双击，即可将其输入到单元格中，输入函数后我们可以看到进一步的函数语法提示，里面有函数的参数信息，根据提示输入公式和参数，输入完成后，按下“Enter”键，就可以得到计算结果。

提示：在输入函数公式的过程中，如果需要在其中输入单元格地址，只需单击该单元格，就可以将单元格地址引用到公式中了。

1.4.5　查找函数

只知道某个函数的类别或者功能，不知道函数名，可以通过“插入函数”对话框快速查找函数。切换到“公式”选项卡，然后单击“插入函数”按钮，就会弹出“插入函数”对话框，在其中查找函数的方法主要有两种。

·方法一：单击下拉按钮打开“或选择类别”下拉列表框，按类别查找。

·方法二：在“搜索函数”文本框中输入需要函数的函数功能，然后单击“转到”按钮，然后在“选择函数”列表框中就会出现系统推荐的函数。

提示：在“选择函数”列表框中选中某个函数，该函数的相关信息就会出现在下方的说明栏。

如果说明栏的函数信息不够详细、难以理解，在电脑连接了Internet网络的情况下，我们可以利用帮助功能：在“选择函数”列表框中选中某个函数后，单击“插入函数”对话框左下方的“有关该函数的帮助”链接，打开“Excel帮助”网页，其中对函数进行了十分详细的介绍并提供了示例，足以满足大部分人的需求。直接在该网页的“搜索联机帮助”文本框中输入函数名或函数功能然后按下“搜索”按钮 ，也可获得相应的帮助。

1.4.6　使用“插入函数”对话框输入函数

使用“插入函数”对话框输入函数的方法很简单：选中需要输入函数的单元格，切换到“公式”选项卡，然后单击“插入函数”按钮，弹出“插入函数”对话框，在其中选择需要的函数，单击“确定”按钮即可将函数插入到表格中。

1.4.7　输入嵌套函数

使用一个函数或者多个函数表达式的返回结果作为另外一个函数的某个或多个参数，这种应用方式的函数称为嵌套函数。

例如函数式“=IF(AVERAGE(A1:A3) >20,SUM(B1:B3),0)”，即一个简单的嵌套函数表达式。该函数表达式的意义为：在“A1:A3”单元格区域中数字的平均值大于20时，返回单元格区域“B1:B3”的求和结果，否则将返回“0”。

嵌套函数一般通过手动输入，输入时可以利用鼠标辅助引用单元格。以上面的函数式为例，输入方法为：选中目标单元格，输入“=IF(”，然后输入作为参数插入的函数的首字母“A”，在出现的相关函数列表中双击函数“AVERAGE”，此时将自动插入该函数及前括号，函数式变为“=IF(AVERAGE(”，手动输入字符“A1:A3) >20,”，然后仿照前面的方法输入函数“SUM”，最后输入字符“B1:B3),0)”，按下“Enter”键即可。

1.5　使用数组公式

数组公式与普通公式不同，是对两组或多组名为数组参数的值进行多项运算，然后返回一个或多个结果的一种计算公式。在Excel中数组公式非常有用，下面将介绍数组公式的使用方法。

1.5.1　输入数组公式

公式和函数的输入都是从“=”开始的，输入完成后按下“Enter”键，计算结果就会显示在单元格里。而要使用数组公式，在输入完成后，需要按下“Ctrl+Shift+Enter”组合键才能确认输入的是数组公式。正确输入数组公式后，才可以看到公式的两端出现数组公式标志性的一对大括号“{}”。

以求合计发放员工工资金额为例，使用数组公式计算，可以省略计算每个员工的实发工资这一步，直接得到合计发放工资金额，方法为：在F5单元格中输入数组公式“=SUM(B2:B6-C2:C6)”（意为将B2:B6单元格区域中的每个单元格，与C2:C6单元格区域中的每个对应的单元格相减，然后将每个结果加起来求和），按下“Ctrl+Shift+Enter”组合键确认输入数组公式即可。

1.5.2　修改数组公式

在Excel 2013中，对于创建完成的数组公式，如果需要进行修改，方法为：选中数组公式所在的单元格，此时数组公式将显示在编辑栏中，单击编辑栏的任意位置，数组公式将处于编辑状态，可对其进行修改，修改完成后按下“Ctrl+Shift +Enter”组合键即可。

如果需要将输入的数组公式删除，只需选中数组公式所在的单元格，然后按下“Delete”键即可。

1.5.3　了解数组常量

在普通公式中，可输入包含数值的单元格引用，或数值本身，其中该数值与单元格引用被称为常量。同样，在数组公式中也可输入数组引用，或包含在单元格中的数值数组，其中该数值数组和数组引用被称为数组常量。数组公式可以按与非数组公式相同的方式使用常量，但是必须按特定格式输入数组常量。

数组常量可包含数字、文本、逻辑值（如 TRUE、FALSE 或错误值 #N/A）。数字可以是整数型、小数型或科学计数法形式，文本则必须使用引号引起来，例如““星期一”。在同一个常量数组中可以使用不同类型的值，如{1，3，4；TRUE，FALSE，TRUE}。

数组常量不包含单元格引用、长度不等的行或列、公式或特殊字符 $（美元符号）、括弧或 %（百分号）。

在使用数组常量或者设置数组常量的格式时，需要注意以下几个问题。

·数组常量应置于大括号 ({ }) 中。

·不同列的数值用逗号 (,) 分开。例如，若要表示数值 10、20、30 和 40，必须输入 {10,20,30,40}。这个数组常量是一个 1 行 4 列数组，相当于一个 1 行 4 列的引用。

·不同行的值用分号 (;) 隔开。例如，如果要表示一行中的 10、20、30、40 和下一行中的 50、60、70、80，应该输入一个 2 行 4 列的数组常量：{10,20,30,40;50,60,70,80}。

1.5.4　创建多单元格数组公式

数组公式与普通公式一样，如果需要计算多个结果，只需要将数组公式输入到数组参数相同的列数和行数单元格区域，再使用数组公式进行计算即可。下面举例说明。

选中需要计算结果的E2:E5单元格区域，在编辑栏中输入数组公式{B2:B5*C2:C5}，按下“Ctrl+Shift+Enter”组合键确认，即可得到计算结果。

1.5.5　扩展或缩小多单元格数组公式

由于在数组公式中，每个数组参数都要求必须有相同数量的行和列，所以要扩展或缩小多单元格数组公式，就必须同时修改每个数组参数和计算结果显示区域，否则Excel将出现错误提示，无法进行修改。

1.6　使用定义名称

在Excel 2013中，可以定义名称来代替单元格地址，并将其应用到公式计算中，以便提高工作效率，方便公式审核，减少计算错误。

1.6.1　名称的作用范围与规则

通过Excel的定义名称功能，可以为单元格、数值、公式和常量等命名。需要注意的是，定义的名称不能是任意的字符，必须遵照以下规则。

·名称的第一个字符必须是字母、汉字、下划线或反斜杠（\），其他字符可以是字母、汉字、半角句号或下划线。

·名称不能与单元格名称（如A1、R1C1等）相同。

·定义名称时，不能用空格符来分隔名称，可以使用“.”或下划线，如1.1或1-1等。

·名称的长度不能超过255个字符，字符不区分大小写。

·同一个工作薄中不能定义相同的名称。

·不能把单独的字母“r”或“c”定义为名称，这两个字母会被Excel认为是行（row）或列（column）的简写。

定义的名称，可以根据需要进行设置，使其作用于当前工作表或当前工作薄。

1.6.2　命名单元格区域

在Excel中命名单元格区域的方法很简单，主要可以通过以下三种方法实现。

·选中要命名的单元格区域，在编辑栏左侧的名称框中输入要创建的名称，然后按下“Enter”键确认即可。

·选中要命名的单元格区域，切换到“公式”选项卡，单击“定义名称”?“定义名称”命令，在打开的“新建名称”对话框中，设置名称，可用范围以及说明信息，单击“确定”按钮即可。

·在工作表中选中要命名的单元格区域，其中必须包含要作为名称的单元格，切换到“公式”选项卡，单击“定义的名称”组中的“根据所选内容创建”按钮，在打开的“以选定区域创建名称”对话框中，根据需要勾选相应复选框，单击“确定”按钮即可。

1.6.3　命名数值、常量和公式

在公式计算中经常用到的数值，例如圆周率数值3.14159265，如果每次使用都在公式中输入这一长串数字，难免降低工作效率，因此可以为这样的常量定义一个名词，以便将其应用到公式中，提高输入效率。

方法为：打开工作薄，切换到“公式”选项卡，在“定义的名称”组中单击“定义名称”?“定义名称”命令，打开“新建名称”对话框，设置名称，然后在“引用位置”文本框中输入一个“=”符号和常量值，在Excel中命名公式和常量的方法与此类似，在“引用位置”文本框中输入需要命名的公式或常量，单击“确定”按钮即可。

1.6.4　将名称应用到公式中

在工作薄中定义名称之后，就可以将定义的名称应用到公式和函数中了。例如在工作薄中为产品名称、销售数量和单价等数据定义了名称后，要计算销售额，只需在相应单元格中输入公式“=销售数量*单价”，然后按下“Enter”键确认即可。

如果先输入了使用单元格引用的公式，然后定义了名称，可以切换到“公式”选项卡，在“定义的名称”组中单击“定义名称”?“应用名称”命令，打开“应用名称”对话框，选择需要应用到公式中的名称，然后单击“确定”按钮，将名称应用到公式中。

1.6.5　编辑与删除定义的名称

在Excel中定义名称之后，还可以根据需要，对定义的名称进行编辑和删除操作。方法如下。

·切换到“公式”选项卡，在“定义的名称”组中单击“名称管理器”按钮，弹出“名称管理器”对话框，选中需要删除的名称，单击“删除”按钮，即可将其删除。

·切换到“公式”选项卡，在“定义的名称”组中单击“名称管理器”按钮，弹出“名称管理器”对话框，选中需要编辑的名称，单击“编辑”按钮，在弹出的“编辑名称”对话框中根据需要进行设置，完成后单击“确定”按钮，即可修改定义的名称。

1.7　审核公式

在使用公式和函数计算数据的过程中，难免出现错误，Excel提供了“公式审核”工具，帮助我们快速“纠错”。下面将介绍在Excel中审核公式的方法。

1.7.1　使用“公式求值”检查计算公式

要在Excel中进行公式审查，有一个方法就是公式分步求值，即分步求出公式的计算结果（根据优先级求取）。如果公式没错误，使用该功能可以便于对公式的理解；如果公式有错误，则可以快速地找出导致错误的发生具体是在哪一步。

选中要分步求值的单元格，切换到“公式“选项卡，单击“公式审核”组中的“公式求值”按钮，即可打开“公式求值”对话框，连续单击“求值”按钮，即可对公式逐一求值，完成后单击“关闭”按钮即可。

1.7.2　使用“错误检查”功能检查公式

当使用的公式和函数出现错误时，选中出现错误的单元格，切换到“公式“选项卡，单击“公式审核”组中的“错误检查”按钮，即可打开“错误检查”对话框，其中可以看到提示信息，指出单元格出现错误及错误原因，辅助查找与修改公式错误。

提示：在“错误检查”对话框单击“下一个”按钮，将根据向导逐一检查错误值，并获取错误值产生的原因。

1.7.3　追踪引用单元格

在公式出现错误的时候，光让数据表格中的公式显示出来还不够，我们还得对错误原因追根究底。Excel提供了“追踪引用单元格”功能帮助我们查看当前公式是引用哪些单元格进行计算的，辅助我们对公式的错误原因进行查找。

选中要查看的单元格，在“公式”选项卡的“公式审核”组中单击“追踪引用单元格”按钮，即可使用箭头显示数据源引用指向。

1.7.4　追踪从属单元格

在公式出现错误的时候，光让数据表格中的公式显示出来还不够，我们还得对错误原因追根究底。Excel提供了“追踪从属单元格”功能帮助我们查看受当前所选单元格影响的单元格，辅助我们对公式的错误原因进行查找。

选中要查看的单元格，在“公式”选项卡的“公式审核”组中单击“追踪从属单元格”按钮，即可使用箭头显示受当前所选单元格影响的单元格数据从属指向。

1.7.5　移去追踪箭头

在Excel中进行追踪引用单元格或追踪从属单元格操作后，如果需要移去追踪箭头，方法为：在“公式”选项卡的“公式审核”组中单击“移去箭头”下拉按钮，在打开的下拉菜单中，根据需要单击相应命令，即可取消显示相应的“追踪”箭头。

1.8　错误分析与处理

如果工作表中的公式不能计算出正确的结果，系统会自动显示出一个错误值，如“####”、“#VALUE！”等。下面列出一些常见的错误字符的含义和解决方法，方便大家解决公式和函数使用中遇到的问题。

1.8.1　解决####错误

错误原因：日期运算结果为负值、日期序列超过系统允许的范围或在显示数据时，单元格的宽度不够。

解决办法：出现以上错误，可尝试以下的操作。

·更正日期运算函数式，使其结果为正值。

·使用输入的日期序列在系统的允许范围之内（1-2958465）。

·调整单元格到合适的宽度。

1.8.2　解决#DIV/0!错误

错误原因：当数字除以零 (0) 时，会出现此错误。如，用户在某个单元格中输函数式：＝A1/B1，如果B1单元格为“0”或为空时，确认后函数式将返回上述错误。

解决办法：修改引用的空白单元格或在作为除数的单元格中输入不为零的值即可。

1.8.3　解决#VALUE！错误

错误原因：出现#VALUE！错误的主要原因如下。

·为需要单个值（而不是区域）的运算符或函数提供了区域引用。

·当函数式需要数字或逻辑值时，输入了文本。

·输入和编辑的是数组函数式，但却用回车键进行确认等。

解决办法：更正相关的能数类型，如果输入的是数组函数式，则在输入过完成后，使用“Ctel+Shift+Enter”组合键进行确认。

例如：在某个单元可知中输入函数式：＝A1+A2，而A1或A2中有一个单元格内容是文本，确认后函数将会返回上述错误。

1.8.4　解决#NUM！错误

错误原因：公式或函数中使用了无效的数值，会出现此错误。

解决办法：根据实际情况尝试下面的解决方案。

（1）在需要数字参数的函数中使用了无法接受的参数

解决方法：请确保函数中使用的参数是数字，而不是文本、货币以及时间等其它格式。例如，即使要输入的值是￥1000，也应在公式中输1000。

（2）使用了进行迭代的工作表函数，且函数无法得到结果

解决方法：为工作表函数使用不同的起始值，或者更改Excel迭代公式的次数即可。

提示：迭代次数越高，Excel 计算工作表所需的时间就越长；最大误差值数值越小，结果就越精确，Excel 计算工作表所需的时间也越长。

（3）输入的公式所得出的数字太大或太小，无法在Excel中表示

解决方法：更改公式，使运算结果介于 “-1*10307”到“1*10307”之间。

1.8.5　解决#NULL！错误

错误原因：函数表达式中使用了不正确的区域运算符、不正确的单元格引用或指定两个并不相交的区域的交点等。

解决办法：如果使用了不正确的区域运算符，则需要将其进行更正，才能正确返回函数值，具体方法如下。

若要引用连续的单元格区域，可使用冒号分隔对区域中第一个单元格的引用和对最后一个单元格的引用。如SUM(A1:E1)引用的区域为从单元格A1到单元格E1。

若要引用不相交的两个区域，可使用联合运算符，即逗号“,”。如对两个区域求和，可确保用逗号分隔这两个区域，函数表达式为：SUM(A1:A5,D1:D5)。

提示：如果是因为指定了两个不相交的区域的交点，则更改引用使其相交即可。

1.8.6　解决#NAME？错误

错误原因：当Excel无法识别公式中的文本时，将出现此错误，例如使用了错误的自定义名称或名称已删除，函数名称拼写错误，引用文本时没有加引号（""），用了中文状态下的引号（“”）等；或者使用“分析工具库”等加载宏部分的函数，而没有加载相应的宏。

解决办法：首先针对具体的公式，逐一检查错误的对象，然后加以更正。如重新指定正确的名称、输入正确的函数名称、修改引号，以及加载相应的宏等，具体操作如下。

（1）使用了不存在的名称。

解决方法：用户可以通过以下操作查看所使用的名称是否存在。

切换到“公式”选项卡，在“定义的名称”组中单击“名称管理器”按钮，查看名称是否列出，若名称在对话框中未列出，可以单击“新建”按钮添加名称。

提示：如果函数名称拼写错误，也将不能返回正确的函数值，因此在输入时应仔细。

（2）在公式中引用文本时没有使用（英文）双引号。

解决方法：虽然用户的本意是将输入的内容作为文本使用，但Excel会将其解释为名称。此时只需将公式中的文本用英文状态下的双引号括起来即可。

（3）区域引用中漏掉了冒号“:”。

解决方法：请用户确保公式中的所有区域引用都使用了冒号“:”。

（4）引用的另一张工作表未使用单引号引起。

解决方法：如果公式中引用了其他工作表或者其他工作簿中的值或单元格，且这些工作簿或工作表的名字中包含非字母字符或空格，那么必须用单引号“'”将名称引起。如：='预报表　1月'!A1。

（5）使用了加载宏的函数，而没有加载相应的宏。

解决方法：加载相应的宏即可，具体操作方法如下。

切换到“文件”选项卡，单击“选项”命令，打开“Excel选项”对话框，切换到“加载项”选项卡，在右侧窗口的“管理”下拉列表中选择“Excel加载项”选项，然后单击“转到”按钮，在打开的“加载宏”对话框中勾选需要加载的宏，单击“确定”按钮，返回“Excel选项”对话框，单击“确定”按钮即可。

1.8.7　解决#REF！错误

错误原因：当单元格引用无效时，会出现此错误，如函数引用的单元格（区域）被删除、链接的数据不可用等。

解决办法：出现上述错误时，可尝试以下操作。

·修改函数式中无效的引用单元格。

·调整链接的数据，使其处于可用的状态。

1.8.8　解决#N/A错误

错误原因：错误值“#N/A”表示“无法得到有效值”，即当数值对函数或公式不可用时，当出现此错误。

解决办法：可以根据需要，选中显示错误的单元格，执行“公式”选项卡“公式审核”组中的“错误检查“命令，检查下列可能的原因并进行解决。

（1）缺少数据，在其位置输入了#N/A或NA()

解决方法：遇到这种情况，用新的数据代替“#N/A”即可。

（2）为MATCH、HLOOKUP、LOOKUP或VLOOKUP等工作表函数的lookup_value参数赋予了不正确的值

解决方法：确保lookup_value参数值的类型正确即可。

（3）在未排序的工作表中使用VLOOKUP、HLOOKUP或MATCH工作表函数来查找值

解决方法：默认情况下，在工作表中查找信息的函数必须按升序排序。但VLOOKUP函数和HLOOKUP函数包含一个range_lookup参数，该参数允许函数在未进行排序的表中查找完全匹配的值。若用户需要查找完全匹配值，可以将range_lookup参数设置为“FALSE”。

此外，MATCH函数包含一个match_type参数，该参数用于指定列表查找匹配结果时必须遵循的排序次序。若函数找不到匹配结果，可尝试更改match_type参数；若要查找完全匹配的结果，需将match_type参数设置为0。

（4）数组公式中使用的参数的行数（列数）与包含数组公式的区域的行数（列数）不一致。

解决方法：若用户已在多个单元格中输入了数组公式，则必须确保公式引用的区域具有相同的行数和列数，或者将数组公式输入到更少的单元格中。

例如，在高为10行的区域(A1:A10)中输入数组公式，但公式引用的区域(C1:C8)高为8行，则区域 C9:C10中将显示“#N/A”。要更正此错误，可以在较小的区域中输入公式如“A1:A8”，或者将公式引用的区域更改为相同的行数，如“C1:C10”。

（5）内置或自定义工作表函数中省略了一个或多个必需的参数

解决方法：将函数中的所有参数完整输入即可。

（6）使用的自定义工作表函数不可用

解决方法：请确保包含自定义工作表函数的工作簿已经打开，而且函数工作正常。

（7）运行的宏程序输入的函数返回#N/A

解决方法：请确保函数中的参数输入正确且位于正确的位置。

1.8.9　通过“Excel 帮助”获取错误解决办法

如果在使用公式和函数计算数据的过程中出现了错误，在电脑联网的情况下，可以通过“Excel”帮助获取错误值的相关信息，来学习和解决问题。

方法为：选中显示了错误值的单元格，单击错误值提示按钮
，在打开的下拉菜单中单击“关于此错误的帮助”命令，即可打开“Excel帮助”窗口，其中显示了该错误值的出现原因和解决方法，帮助用户学习和解决相关问题。

第2章　逻辑与文本函数应用

2.1　逻辑函数应用

例　创建条件公式

如果需要创建条件公式，可通过AND、OR或NOT函数以及运算符来实现。默认的计算结果为逻辑值TRUE或FALSE。

1．判断10是否大于9，并小于8

01 在“A2”单元格中输入数据“10”，在“A3”单元格中输入数据“9”，在“A4”单元格中输入数据“8”。

02 在需要显示结果的单元格中输入公式：=AND (A2>A3,A2<A4)，然后按下“Enter”键，得到结果“FALSE”。

2．判断10是否大于9或小于8

01 在“A2”单元格中输入数据“10”，在“A3”单元格中输入数据“9”，在“A4”单元格中输入数据“8”。

02 在需要显示结果的单元格中输入公式：=OR(A2>A3, A2<A4)，然后按下“Enter”键，得到结果“TRUE”。

3．判断10加9不等于24

01 在“A2”单元格中输入数据“10”，在“A3”单元格中输入数据“9”，在“A4”单元格中输入“24”。

02 在“B2”单元格中输入公式“= A2+A3”，按下“Enter”键确认。

03 在需要显示结果的单元格中输入公式：=NOT (B2=A4)，然后按下“Enter”键，得到结果“TRUE”。

例　对数据执行真假值判断

如果需要对数据执行真假值判断，并根据逻辑计算的真假值返回不同结果，可通过IF函数实现。

IF函数的语法为：IF(logical_test, value_if_true, value_if_false)，其中各个函数参数的含义如下。

·Logical_test：表示计算结果为TRUE或FALSE的任意值或表达式。如“A10=100”是一个逻辑表达式，若单元格A10中的值等于100，则计算结果为 TRUE，否则为FALSE。

·Value_if_true：是logical_test参数为TRUE时返回的值。例如，若此参数是文本字符串“预算内”，而且logical_test参数的计算结果为TRUE，则IF函数显示文本“预算内”；若logical_test为TRUE而value_if_true为空，则此参数返回0（零）。

·Value_if_false：是logical_test为FALSE时返回的值。例如，若此参数是文本字符串“超出预算”，而logical_test参数的计算结果为FALSE，则IF函数显示文本“超出预算”；若logical_test为FALSE而value_if_false被省略，即value_if_true后面没有逗号，则会返回逻辑值FALSE；若logical_test为FALSE且value_if_false为空，即value_if_true后面有逗号且紧跟着右括号，则会返回值0（零）。

例　不以TRUE或FALSE作为逻辑判断结果

逻辑判断的结果通常为TRUE或FALSE。如果不希望以TRUE或FALSE作为逻辑判断结果，可通过IF、AND或OR等函数实现。

例1：检查单元格中是否包含文本“画眉”。若是，返回“匹配”；若不是，返回“不匹配”。

01 在“B1”单元格中输入需要检查的内容。

02 在需要显示处理结果的单元格中输入公式：=IF(B1= "画眉","匹配","不匹配")，然后按下“Enter”键确认即可。

例2：检查B1单元格中是否包含文字“水”。若是，显示“OK”；若否，显示“No”。

01 在“结果”单元格中输入需要检查的内容。

02 在单元格中输入公式：=IF(ISNUMBER (FIND("水", B1)),"OK", "NO")，按下“Enter”键确认即可。

例　判断支出是否在预算内

如果需要判断一笔开支是否在预算内，可通过IF函数实现。假设输入一个数字，若小于100，则显示“预算内”，否则显示“超支”，具体操作如下。

01 在“B1”单元格中输入需要计算的数据，如输入“90”。

02 在单元格中输入公式：=IF(B1<=100,"预算内","超支")，然后按下“Enter”键确认，得到结果“预算内”。

例　返回逻辑值FALSE或TRUE

如果需要在单元格中输入一个逻辑值FALSE，可直接在单元格中输入文字FALSE，或者通过FALSE函数来实现。FALSE函数的公式为：=FALSE()。

如果需要在单元格中输入一个逻辑值TRUE，可直接在单元格中输入文字TRUE，或者通过TRUE函数来实现。TRUE函数的公式为：=TRUE()。

例　自动给出成绩的优劣等级

利用IF函数的判断结果，可以对学生成绩给出优劣等级。假设等级条件为：总成绩大于600为优，550-599为良，500-549为中，450-499为差，小于450为劣。具体操作如下。

01 在工作表中中输入需要给优劣等级的成绩。

02 在G3单元格中输入公式：=IF(F3>600,"优",IF (F3>550,"良",IF(F3>500,"中",IF(F3>450,"差","劣"))))，然后按下“Enter”键确认。

03 利用填充柄将公式复制到其他单元格即可。

例　使用IF函数计算个人所得税

在计算个人所得税时可以使用IF函数。在实际应用中，不同的工资额应缴纳的个人所得税税率也有所不同。全月应纳税所得额的计算公式为：全月应纳税所得额=总工资-五险一金-免征额（3500）。

本例中对个人所得税的缴纳金额约定如下。

下面通过实例讲解使用IF函数计算个人所得税的方法。

01 选中要显示计算结果的单元格，在编辑框中输入函数表达式=IF(H3<=0,H3*0,IF(H3<=1500,H3*0.03,IF(H3<=4500, H3*0.1-105,IF(H3<=9000,H3*0.2-555,IF(H3<=35000,H3*0.25-1005)))))”, 按下“Enter”键确认。

02 利用填充功能将公式复制到其他单元格中，即可计算出其他人员的应缴个人所得税了。

在本案例的公式中，105、555和1005是标准的个人所得税速算扣除数。速算扣除数实际上是在级距和税率不变条件下，全额累进税率的应纳税额比超额累进税率的应纳税额多纳的一个常数。

速算扣除数的计算公式是：本级速算扣除额=上一级最高所得额×（本级税率-上一级税率）+上一级速算扣除数。如某人工资扣除3500元后的应纳税所得额是2200元，则税款计算方法为：1500*3%+700*10%=115元。

也可以将应纳税所得额直接按对应的税率来速算，但要扣除一个速算扣除数，否则会多计算税款。例如，如某人工资扣除3500元后的应纳税所得额是2200元，2200元对应的税率是10%，则税款速算方法为：2200*10%-105=115元。

2.2　文本函数应用

例　计算文本字符串的字符数

如果需要得到文本字符串中的字符数，可通过“LEN”函数实现。LEN函数的语法为：=LEN(text)，其中参数text为要查找其长度的文本，而空格将作为字符进行计数。

下面举例说明LEN函数的使用方法。

01 在“B1”单元格中输入需要计算字符数的字符串。

02 在需要显示结果的单元格中输入公式：=LEN(B1)，然后按下“Enter”键确认即可。

注意：函数LEN面向使用单字节字符集 (SBCS) 的语言，无论默认语言设置如何，函数LEN始终将每个字符（不管是单字节还是双字节）按1计数。

例　计算文本字符串中代表字符的字节数

如果需要得到文本字符串中用于代表字符的字节数，可通过“LENB”函数实现。LENB函数的语法为：=LENB（text），其中参数text为要查找其长度的文本，而空格也将作为字符进行计数。

下面举例说明LENB函数的使用方法。

01 在“B1”单元格中输入需要计算字符数的文字。

02 在需要显示结果的单元格中输入公式：=LENB(B1)，然后按下“Enter”键确认即可。

例　计算字符串的字符起始位置（区分大小写）

如果需要得到一个字符串在另一个字符串中的起始位置，且需要区分大小写，可通过“FIND”函数实现。FIND函数的语法为：=FIND（find_text,within_text,start_num），各参数的含义介绍如下。

·find_text：需要查找的文本或文本所在的单元格。

·within_text：包含要查找文本的文本或文本所在的单元格；该参数中的首字符是编号为1的字符，如果省略该参数，则假设其值为1。

·start_num：用数值或数值所在的单元格指定开始查找的字符。

下面以查询“B1”单元格中文本“长江”的字符起始位置为例，具体操作如下。

01 在“B1”单元格中输入需要的文字。

02 在需要显示结果的单元格中输入公式：=FIND("长江",B1)，然后按下“Enter”键确认即可。

例　计算字符串的字节起始位置（区分大小写）

如果需要得到一个字符串在另一个字符串中基于字节数的起始位置，可通过“FINDB”函数实现。FINDB函数的语法为：=FINDB（find_text,within_text,start_num），各参数的含义介绍如下。

·find_text：需要查找的文本或文本所在的单元格。

·within_text：包含要查找文本的文本或文本坐在的单元格。

·start_num：用数值或数值所在的单元格指定开始查找的字符，这里需查找的文本起始位置指定为一个字节数。

以查询“B1”单元格中文本“长江”的字节起始位置为例，具体操作如下。

01 在“B1”单元格中输入需要的文字。

02 在需要显示结果的单元格中输入公式：=FINDB("长江", B1)，按下“Enter”键确认。

提示：函数FIND与FINDB区分大小写并且不允许使用通配符。如果您不希望执行区分大小写的搜索或者要使用通配符，可以使用SEARCH和SEARCHB函数。

例　计算字符第1次出现的位置（不区分大小写）

如果需要得到一个指定字符在字符串中第1次出现的位置，且不用区分大小写，可通过“SEARCH”函数实现。

SEARCH函数的语法为：=SEARCH（find_text, within_text,start_num），各参数的含义介绍如下。

·find_text：需要查找的文本或文本所在的单元格。

·within_text：包含需要查找文本的文本或文本所在的单元格。

·start_num：指定开始查找的字符。

下面以返回字符“i”在单元格中第1次出现的位置为例，具体操作如下。

01 在“B1”单元格中输入需要的文字。

02 在单元格中输入公式：=SEARCH("i",B1)，按下“Enter”键确认即可。

例　计算字符基于字节数的起始位置（不区分大小写）

如果需要返回一个指定字符在字符串中的基于字节数的起始位置，且不用区分大小写，可通过“SEARCHB”函数实现。

SEARCHB函数的语法为：=SEARCHB（find_text, within_text,start_num），各参数的含义介绍如下。

·find_text：需要查找的文本或文本所在的单元格。

·within_text：包含需要查找文本的文本或文本所在的单元格。

·start_num：指定开始查找的字符。

下面以查询字符“t”基于字节的起始位置为例，具体操作如下。

01 在“B1”单元格中输入需要的文字。

02 在需要显示结果的单元格中输入公式：=SEARCHB ("t",B1)，然后按下“Enter”键确认即可。

提示：使用SEARCH函数和SEARCHB时，可以在find_text参数中使用通配符（包括问号(?)和星号(*)）。问号用于匹配任意单个字符；星号可以匹配任意一串字符。如果要查找实际的问号或星号，必须在该字符前键入波形符 (~)。

例　从第一个字符开始返回指定个数的字符

如果需要从字符串第一个字符开始返回指定个数的字符，可通过“LEFT”函数实现。LEFT函数的语法为：=LEFT（text,num_chars），各参数的含义介绍如下。

·Text：需要提取字符的文本字符串。

·num_chars：指定需要提取的字符数。

下面以返回单元格中前4个字符为例，具体操作如下。

01 在“B1”单元格中输入需要的文字。

02 在需要显示结果的单元格中输入公式：=LEFT(B1, 4)，然后按下“Enter”键确认即可。

例　从指定位置起返回指定字节数的字符

如果需要从字符串第一个字符开始返回指定字节数的字符，可通过“LEFTB”函数实现。LEFTB函数的语法为：=LEFTB（text,num_bytes），各参数的含义介绍如下。

·Text：需要提取字符的文本字符串。

·num_chars：指定需要提取的字节数。

下面以返回单元格中前4个字节为例，具体操作如下。

01 在“B1”单元格中输入需要的文字。

02 在需要显示结果的单元格中输入公式：=LEFTB (B1,4)，然后按下“Enter”键确认即可。

注意：使用LEFT函数和LEFTB函数时需要注意，参数“num_chars”和“num_bytes”必须大于或等于零。如果num_chars大于文本长度，则LEFT 返回全部文本。 如果省略num_chars参数，则假设其值为 1。

例　从指定位置起返回指定长度的字符

如果需要从字符串指定的起始位置开始返回指定长度的字符，可通过“MID”函数实现。MID函数的语法为：=MID（text,start_num,num_chars），各参数的含义介绍如下。

·Text：包含需要提取字符的文本字符串。

·start_num：文本中需要提取的第一个字符的位置。

·num_chars：指定希望从文本中返回字符的个数。

下面以从第3个字符开始，提取单元格中的5个字符为例，具体操作如下。

01 在“B1”单元格中输入需要的文字。

02 在单元格中输入公式：=MID(B1,3,5)，然后按下“Enter”键确认即可。

例　从指定位置起返回指定字节数的字符

如果需要从字符串指定的起始位置开始返回指定字节数的字符，可通过“MIDB”函数实现。MIDB函数的语法为：=MIDB（text,start_num,num_bytes），各参数的含义介绍如下。

·Text：包含需要提取字符的文本字符串。

·start_num：文本中需要提取的第一个字符的位置。

·num_bytes：指定希望从文本中返回字节的个数。

下面以从第5个字节开始，提取单元格中的6个字节为例，具体操作如下。

01 在“B1”单元格中输入需要的文字。

02 在需要显示结果的单元格中输入公式：=MIDB(B1,5,6)，然后按下“Enter”键确认即可。

注意：使用MID函数和MIDB函数时，如果start_num大于文本长度，则函数返回空文本 ("")；如果start_num小于文本长度，但start_num加上num_chars或num_bytes超过了文本的长度，则函数只返回至多直到文本末尾的字符；如果start_num小于1，则函数返回错误值#VALUE!。

例　从最后一个字符开始返回指定个数的字符

如果需要从字符串最后一个字符开始返回指定个数的字符，可通过“RIGHT”函数实现。RIGHT函数的语法为：=RIGHT（text,num_chars），各参数的含义介绍如下。

·Text：需要提取字符的文本字符串。

·num_chars：指定希望提取的字符数。

假设需要从最后一个字符开始提取5个字符，具体操作如下。

01 在“BI”单元格中输入需要的文字。

02 在需要显示结果的单元格中输入公式：=RIGHT (B1,5)，然后按下“Enter”键确认即可。

例　从最后一个字符起返回指定字节数的字符

如果需要从字符串最后一个字符开始返回指定字节数的字符，可通过“RIGHTB”函数实现。RIGHTB函数的语法为：=RIGHTB（text,num_bytes），各参数的含义介绍如下。

·Text：需要提取字符的文本字符串。

·num_bytes：指定希望提取的字节数。

假设需要从最后一个字符开始提取6个字节，具体操作如下。

01 在“B1”单元格中输入需要的文字。

02 在需要显示结果的单元格中输入公式：=RIGHTB (B1,6)，然后按下“Enter”键确认即可。

注意：使用RIGHT函数和RIGHTB函数时，参数Num_chars必须大于或等于零。如果num_chars大于文本长度，则函数返回所有文本；如果省略num_chars，则假设其值为1。

例　将部分字符用另一个字符串替换

如果需要将字符串中的部分字符用另一个字符串替换，可通过“REPLACE”函数实现。REPLACE函数的语法为：=REPLACE（old_text,start_num,num_chars,new_text），各参数的含义介绍如下。

·old_text：指定成为替换对象的文本或文本所在的单元格。

·start_num：指定开始替换的字符位置。

·num_chars：希望替换old_text参数中的字符个数。

·new_text：指定替换旧字符串的文本或文本所在的单元格。

下面以将单元格中的“长江”替换为“银河”为例，具体操作如下。

01 在“B1”单元格中输入需要的文字。

02 在单元格中输入公式：=REPLACE(B1,11,2,"银河")，按下“Enter”键确认。

例　将部分字符根据指定字节数用新字符串替换

如果需要将字符串中的部分字符用另一个字符串替换，可通过“REPLACEB”函数实现。REPLACEB函数的语法为：=REPLACEB（old_text,start_num,num_bytes,new_text），各参数的含义介绍如下。

·old_text：指定成为替换对象的文本或文本所在的单元格。

·start_num：指定开始替换的字符位置。

·num_bytes：希望替换old_text参数中字节的个数。

·new_text：指定替换旧字符串的文本或文本所在的单元格。

下面以将单元格中的前面4个字节替换为“片片”为例，具体操作步骤如下。

01 在“B1”单元格中输入需要的文字。

02 在需要显示结果的单元格中输入公式：=REPLACEB(B1,1,4,"片片")，然后按下“Enter”键确认即可。

2.3　文本转换

例　将半角字符转换为全角字符

如果需要将半角字符转换为全角字符，可通过“WIDECHAR”函数实现。WIDECHAR函数的语法为：=WIDECHAR（text）。

注意：其中参数“text”表示需要转换的字符或单元格，若为字符，则需要用英文状态的双引号引起。

以将字符“EXCEL”转换为全角为例，具体操作为：在需要显示结果的单元格中输入公式：=WIDECHAR("EXCEL")，然后按下“Enter”键确认，即可得到全角字符“ＥＸＣＥＬ”。

例　将全角字符转换为半角字符

如果需要将全角字符转换为半角字符，可通过“ASC”函数实现。ASC函数的语法为：=ASC(text)，

注意：其中参数“text”表示需要转换的字符或单元格，若为字符，则需要用英文状态的双引号引起。

以将全角字符串“ｅｘｃｅｌ”转换为半角为例，具体操作为：在需要显示结果的单元格中输入公式：=ASC("ｅｘｃｅｌ")，然后按下“Enter”键确认，即可得到半角字符“excel”。

例　将大写字母转换为小写字母

如果需要将字符串中的大写字母全部转换为小写字母，可通过“LOWER”函数实现。LOWER函数的语法为：=LOWER（text），其中text参数表示需要转换为小写形式的文本。

下面举例说明LOWER函数的使用方法。

01 在“B1”单元格中输入需要检查的内容。

02 在需要显示结果的单元格中输入公式：=LOWER (B1)，然后按下“Enter”键确认即可。

例　将小写转换为大写形式

如果需要将文本字符串中的小写全部转换为大写形式，可通过“UPPER”函数实现。UPPER函数的语法为：=UPPER（text），其中text参数表示需要转换为大写形式的文本。

下面举例说明UPPER函数的使用方法。

01 在“B1”单元格中输入需要检查的内容。

02 在需要显示结果的单元格中输入公式：=UPPER (B1)，然后按下“Enter”键确认即可。

例　将数值转换为按指定格式表示的文本

如果要将数值转换为按指定数值格式表示的文本，可通过TEXT函数实现，其语法为：=TEXT（value,format_text），各参数的含义介绍如下。

·Value：为数值、计算结果为数值的公式或者包含数值的单元格引用。

·format_text：指定的数值格式。

下面以将数值“1000”转换为小数位数为2位的货币格式为例，具体操作如下。

01 在“B1”单元格中输入数值，在“C1”单元格中输入指定格式，本例输入“￥#.#00”。

02 在需要显示结果的单元格中输入公式：=TEXT(B1,C1)，按下“Enter”键确认即可。

例　将字符串中的首字母转换为大写形式

如果需要将字符串中的首字母转换为大写形式，可通过“PROPER”函数实现。PROPER函数的语法为：=PROPER（text）。

注意：其中text参数表示文本字符串、返回文本值的公式或者包含文本的单元格引用。

下面举例说明PROPER函数的使用方法。

01 在“B1”单元格中输入需要检查的内容。

02 在需要显示结果的单元格中输入公式：=PROPER (B1)，然后按下“Enter”键确认即可。

例　将数字按指定位数取整

如果需要将数字按指定的小数位数取整，可通过“FIXED”函数实现。FIXED函数的语法为：=FIXED（number,decimals,no_commas），各参数的含义介绍如下。

·Number：需要进行四舍五入并转换为文本字符串的数字。

·Decimals：指定小数点右边的小数位数。

·no_commas：该参数为逻辑值，如果为TRUE，则会禁止FIXED函数在返回的文本中包含逗号。

例1：将小数四舍五入到小数点后第1位

01 在“B1”单元格中输入需要转换的数值。

02 在需要显示结果的单元格中输入公式：=FIXED(B1, 1)，然后按下“Enter”键确认即可。

例2：将小数四舍五入到小数点右边一位，且文本中不含逗号

01 在“B1”单元格中输入需要转换的数值。

02 在需要显示结果的单元格中输入公式：=FIXED(B1, 1,TRUE)，然后按下“Enter”键确认即可。

例　将数字转换为大写汉字

如果需要将输入的数字转换为大写的汉字形式，可通过“NUMBERSTRING”函数实现。NUMBERSTRING函数的语法为：=NUMBERSTRING（value,type），各参数的含义介绍如下。

·Value：指定数值或数值所在的单元格。

·Type：指定汉字的表示方法，为“1”表示“十百千万”方式，如“123”表示为“百二十三”；为“2”表示大写汉字方式，如“壹佰贰拾三”；为“3”表示不去位数，按原样表示，如“一二三”。

下面举例说明NUMBERSTRING函数的使用方法。

01 在“B1”单元格中输入需要转换为大写汉字的数字。

02 在单元格中输入公式：=NUMBERSTRING(B1,2)，按下“Enter”键确认即可。

例　将文本转换为数字

如果需要将文本内容转换为数字，可通过“VALUE”函数实现。VALUE函数的语法为：=VALUE（text）。

注意：其中“text”参数表示指定能转换为数值的文本或文本所在的单元格。

下面以将货币格式的1000转换为数字为例，具体操作如下。

01 在“B1”单元格中输入需要转换的文本，本例输入“￥1,000”。

02 在需要显示结果的单元格中输入公式：=VALUE (B1)，然后按下“Enter”键确认即可。

例　返回对应于数字代码的字符

如果需要返回对应于数字代码的字符，可通过“CHAR”函数实现。CHAR函数的语法为：=CHAR（number），其中参数number表示用于转换的数字代码，介于1到255之间。

下面以返回数字代码为“38”的字符为例，具体操作如下。

01 在B1单元格中输入需要转换为字符的数字代码，本例输入“38”。

02 在需要显示结果的单元格中输入公式：=CHAR(B1)，然后按下“Enter”键确认即可。

例　返回文本字符串中第一个字符的数字代码

如果需要返回文本字符串中第一个字符的数字代码，可通过“CODE”函数实现。CODE函数的语法为：=CODE（text）。

注意：其中参数text表示指定加双引号的文本或文本所在的单元格，该函数的功能与CHAR函数相反。

下面以返回符号“&”的数字代码为例，具体操作如下。

01 在“B1”单元格中输入需要转换为数字代码的字符，本例输入“&”。

02 在需要显示结果的单元格中输入公式：=CODE(B1)，然后按下“Enter”键确认即可。

例　返回值引用的文本

如果需要返回某个值所引用的文本，可通过“T”函数实现。T函数的语法为：=T（value），其中参数value为需要进行测试的数值。

下面举例说明T函数的使用方法。

1．将单元格中的文本转换为文本

01 在“B1”单元格中输入需要转换的内容。

02 在需要显示结果的单元格中输入公式：=T(B1)，然后按下“Enter”键确认即可。

2．将单元格中的数字转换为文本

01 在“B1”单元格中输入需要转换的内容。

02 在需要显示结果的单元格中输入公式：=T(B1)，然后按下“Enter”键确认即可。

注意：使用T函数时，若指定的值为文本，则返回文本，否则返回空文本。

例　四舍五入数值并转换为带﹩和位符号的文本

如果需要四舍五入数值，并将其转换为带﹩和位符号的文本，可通过“DOLLAR”函数实现。

DOLLAR函数的语法为：=DOLLAR（number, decimals），各参数的含义介绍如下。

·Number：该参数为数字、单元格引用或者计算结果为数字的公式。

·Decimals：指定小数点右边的位数，如果参数decimals为负数，则number从小数点往左按相应位数四舍五入。如果省略decimals，则假设其值为2。

假设以美元的货币格式及小数点右边2位数字的形式显示数据，具体操作如下。

01 在“B1”单元格中输入需要转换的数据。

02 在需要显示结果的单元格中输入公式：=DOLLAR (B1,2)，按下“Enter”键确认即可。

例　四舍五入数值并转换为带￥和位符号的文本

如果需要四舍五入数值，并将其转换为带￥和位符号的文本，可通过“RMB”函数实现。RMB函数的语法为：=RMB（number,decimals），各参数的含义介绍如下。

·Number：该参数为数字、单元格引用或者计算结果为数字的公式。

·Decimals：指定小数点后的位数。如果decimals为负数，则number从小数点往左按相应位数四舍五入。如果省略decimals，则假设其值为2。

假设以人民币的货币格式及小数点右边2位数字的形式显示数据，具体操作如下。

01 在“B1”单元格中输入需要转换的数据。

02 在单元格中输入公式：=RMB(B1,2)，然后按下“Enter”键确认即可。

2.4　文本比较与删除

例　在单元格中重复某个字符

如果需要在单元格中重复输入某个不容易输入的字符，可通过“REPT”函数实现。

REPT函数的语法为：=REPT（text,number_times），其中各参数的含义介绍如下。

Text：需要重复显示的文本。若直接指定文本，则需要加双引号。

number_times：重复显示文本的次数，范围为0~32767之间。

假设重复输入符号“#”6次，具体操作为：在需要显示结果的单元格中输入公式：=REPT("#",6)，然后按下“Enter”键确认即可。

例　计算非空白单元格的个数

如果需要知道某单元格区域中非空白单元格的个数，可通过“COUNTA”函数实现。COUNTA函数的语法为：=COUNTA(value1, [value2], ...)，其中的参数表示要计数的值或单元格区域。

假设需要统计“A1:C7”单元格区域中非空白单元格的个数，具体操作为：在需要显示处理结果的单元格中输入公式：=COUNTA(A1:C7)，按下“Enter”键确认即可。

例　比较单元格的内容

如果需要比较两个单元格中的内容是否相同，可通过“EXACT”函数实现。

EXACT函数的语法为：=EXACT（text1,text2），其中的参数表示需要比较的内容。若比较结果完全相同，返回逻辑值TRUE，否则返回逻辑值FALSE。

以比较A2和A3单元格中的内容是否一致为例，具体操作如下。

01 在A2、A3两个单元格中分别输入内容。

02 在需要显示结果的单元格中输入公式：=EXACT(A2,A3)，然后按下“Enter”键确认即可。

例　从文本左侧删除指定数量的字符

如果需要从文本左侧删除指定数量的字符，可通过LEN和RIGHT函数实现。RIGHT函数的语法为：=LEFT(string,n)，其中参数string指定要提取子串的字符串；参数n指定子串长度返回值。

假设需要删除B1单元格中单词“Within”的前面4个字符，具体操作如下。

01 在“B1”单元格中输入需要处理的字符串，本例输入“Within”。

02 在单元格中输入公式：=RIGHT(B1,LEN (B1)-4)，按下“Enter”键确认，即可得到结果“in”。

例　从文本右侧删除指定数量的字符

如果需要从文本右侧删除指定数量的字符，可通过LEN和LEFT函数实现。

LEFT函数的语法为：=LEFT(string,n)，其中参数string指定要提取子串的字符串；参数n指定子串长度返回值。

假设需要删除B1单元格中单词“Within”的最后两个字符，具体操作如下。

01 在“B1”单元格中输入需要处理的字符串，本例输入“Within”。

02 在单元格中输入公式：=LEFT(B1,LEN (B1)-2)，然后按下“Enter”键确认，即可得到结果“With”。

例　删除单元格开头和结尾的空格

在工作表中，如果有的单元格中含有空白的开头或结尾，可通过“TRIM”函数将空格删除。TRIM函数的语法为：=TRIM（text），其中“text”参数表示需要清除其中空格的文本。

通过“TRIM”函数将空格删除，具体操作如下。

01 在“B1”单元格中输入需要处理的字符串，本例输入“　Within　”。

02 在单元格中输入公式：= TRIM (B1)，然后按下“Enter”键确认，即可得到结果“With”。

例　合并电话号码的区号和号码

如果需要将多个字符串合并为一个字符串，可通过“CONCATENATE”函数实现。

CONCATENATE函数的语法为：=CONCATENATE（text1,text2,…）。括号中的参数表示需要合并的文本或文本所在的单元格，如果需要直接输入文本，则需要用双引号引起来，否则将返回错误值。下面以合并电话号码的区号和号码为例，具体操作如下。

01 在A列中输入电话号码的区号，在B列中输入电话号码。

02 在单元格中输入公式：=CONCATENATE（A2,B2），然后按下“Enter”键确认。

03 使用填充柄功能复制公式到该列的其他单元格中即可。

例　只显示身份号码后四位数

为了保证用户的账户安全，一些常用的证件号码，如身份证、银行卡号码等，可以只显示后面四位号码，其他号码用星号代替。此时可通过CONCATENATE、RIGHT和REPT函数实现。

例1：将保险号码的后四位数与“***-**-”文本字符串合并

01 在“B2”单元格中输入完整的保险号码，本例输入“162-54-5679”。

02 在需要显示结果的单元格中输入公式：=CONCATENATE("***-**-",RIGHT(B2,4))，然后按下“Enter”键确认，得到结果“***-**-5679”。

例2：将字符串“****-”重复3次的将结果与信用卡号码后四位合并

01 在“B2”单元格中输入信用卡号码，本例输入“9558-4698-8259-6425”。

02 在需要显示结果的单元格中输入公式：=CONCATENATE(REPT("****-",3),RIGHT(B2,4))，按下“Enter”键确认得到结果“****-****-****-6425”。

例　从身份证号中提取出生日期和性别

使用MID和TRUNC函数可以从身份证号中快速提取出生日期和性别。

·MID函数的语法为：=MID(text, start_num, num_chars)，其中3个参数都为必需项。参数“text”包含要提取字符的文本字符串；参数“start_num”为文本中要提取的第一个字符的位置；参数“num_chars”用于 指定希望 MID 从文本中返回字符的个数。

·TRUNC函数的语法为：= TRUNC(number, [num_digits])，其中参数“number”为必需项，表示需要截尾取整的数字；参数“num_digits”为可选项，用于指定取整精度的数字，Num_digits的默认值为0（零）。

具体操作步骤如下。

·01 选中需要输入身份证号的单元格区域，在“开始”选项卡中单击“数字格式”下拉列表框，选择“文本”格式。

·02 输入身份证号后选中“D2”单元格，输入表达式=MID(B2,7,4)&"年"&MID(B2,11,2)&"月"&MID(B2,13,2)&"日"后确认，得到计算结果，然后使用填充柄功能将公式复制到本列的其他单元格中。

·03 选中“E2”单元格，输入函数表达式：=IF(MID(B2,17,1)/2=TRUNC(MID(B2,17,1)/2),"女","男")，按下“Enter”键即可得到学生性别。

·04 选中E2单元格，使用填充柄功能复制公式，即可计算出其他学生的性别。

第3章　时间与日期函数应用

3.1　日期和时间的输入

例　插入当前日期和时间

如果需要插入静态的当前日期或时间，可通过下面的方法实现。

·插入当前日期：选中单元格，直接按下“Ctrl+;”组合键即可。

·插入当前时间：选中单元格，按下“Ctrl+Shift+;”组合键即可。

例　插入可更新的日期和时间

如果需要插入一个可以随系统日期和时间变化的日期和时间，可通过下面的方法实现。

·插入当前日期：在要显示结果的单元格中输入公式“=TODAY()”，然后按下“Enter”键确认即可。

·插入当前时间：在要显示结果的单元格中输入公式“=NOW()”，然后按下“Enter”键确认即可。

例　在时间单位之间转换时间

如果需要将一个时间单位转换为另一个时间单位，可通过“CONVERT”函数实现。

CONVERT函数用于将数字从一个度量系统转换到另一个度量系统中，该函数的语法为：CONVERT(number, from_unit, to_unit)，各参数的含义介绍如下。

·参数Number，指以from_units 为单位的需要进行转换的数值。

·参数From_unit为数值 number 的单位。

·参数To_unit为结果的单位。

下面以两个小实例为例进行介绍。

1．将5小时转换为分钟

01 在单元格“B1”中输入需要转换的数据，本例输入数字“5”。

02 在需要显示结果的单元格中输入公式：=CONVERT(A2,"hr","mn")，按下“Enter”键确认即可。

2．将8年转换为天数

01 在单元格“B1”中输入需要转换的数据，本例输入数字“8”。

02 在单元格中输入公式：=CONVERT(A2,"yr", "day")，按下“Enter”键确认。

例　输入时间值的小时数

如果需要计算时间值的小时数，可使用HOUR函数实现。HOUR函数的语法为：=HOUR(serial_number)。其中参数serial_number为一个时间值，其中包含要查找的小时。

下面以计算车辆的停车时间为例，介绍HOUR函数的用法。

01 在工作表的B列和C列中分别输入车辆的停车开始时间和结束时间。

02 在D2单元格中输入公式=HOUR(C2-B2)，然后按下“Enter”键得到计算结果。

03 使用填充柄功能复制该公式到此列中的其他单元格区域，即可得到所有车辆的停车小时数。

例　把时间值换算成秒数

如果需要将时间值换算为秒数，可通过“SECOND”、“MINUTE”或“HOUR”函数实现。

SECOND函数用于返回时间的秒数，其语法为：=SECOND(serial_number)，其中参数“Serial_number　”是必需的，表示一个时间值，其中包含要查找的秒数。

下面输入一个包含小时、分钟和秒的时间值，将其换算成秒数的具体操作如下。

01 在单元格“B1”中输入时间值，本例输入数字“1:30:18”。

02 在需要显示换算结果的单元格中输入公式：=SECOND(B1)+MINUTE(B1)*60+HOUR(B1)*60*60，按下“Enter”键确认。

提示：由于1分钟等于60秒，1小时等于60分钟，因此计算秒数时，函数MINUTE需要乘以60，函数HOUR需要乘以3600。

例　将小时从十进制数转换为标准时间格式

如果需要将小时从十进制转换为标准时间格式，可以通过除数操作符“/”和“TEXT”函数实现。TEXT 函数可将数值转换为文本，其语法为：=TEXT(value, format_text)，其中各参数的含义解释如下。

·参数value 为要转换的数值，或对包含数值的单元格的引用。

·参数format_text使用双引号括起来作为文本字符串的数字格式，例如"m/d/yyyy" 或 "#,##0.00"。

下面将数值“5.5”转换为标准时间格式，具体操作如下。

·01 在单元格“B1”中输入需要转换的数字。

·02 在“B2”单元格中输入公式：=TEXT (B1/24,"h:mm")，然后按下“Enter”键确认即可。

例　快速输入有规律的日期系列

如果需要在工作表中输入有固定间隔数的日期系列，常用的方法是在表格中输入两个日期，然后利用填充柄功能拖动进行复制。除此之外，还可以使用日期和时间函数实现。

下面举例说明，输入间隔3个月的日期系列的具体操作如下。

01 在“A1”单元格中输入需要的起始日期。

02 在“A2”单元格中输入公式：=DATE(YEAR(A1), MONTH(A1)+3,DAY(A1))，然后按下“Enter”键确认。

03 利用填充柄功能，将公式复制到其他单元格区域即可。

例　将日期转换为日期序列号

如果需要将某个日期转换为日期序列号，可通过“DATEVALUE”和“TEXT”函数实现。其中TEXT函数用于将数值转换为文本型数据；DATEVALUE函数用于将存储为文本的日期转换为 Excel 识别为日期序列号，该函数的语法为：=DATEVALUE(date_text)。

下面举例说明，将日期“2011/1/15”转换为日期序列号的具体操作如下。

01 在“A2”单元格中输入需要转换的日期。

02 在“B2”单元格中输入公式：=TEXT(A1,"yyyy-mm-dd")，然后确认，得到该日期的文本型数据。

03 在“C2”单元格中输入公式：=DATEVALUE(B2)，然后确认，即可得到该日期的序列号。

提示：本例是以分步进行解释，用户也可以直接输入嵌套函数，输入公式为=DATEVALUE(TEXT(A1,"yyyy-mm-dd"))。

例　显示员工进入公司的年份

如果需要返回某个日期所对应的年份数，可以使用YEAR函数实现。

YEAR函数的语法为：=YEAR(serial_number)，其中参数serial_number为一个日期值，也可以指定为加双引号的表示日期的文本，或者包含日期值的单元格的引用；如果参数serial_number为日期以外的文本，则将返回错误值#VALUE。

下面以统计员工进入公司的年份为例，介绍YEAR函数的使用方法。

01 在B列中输入员工进入公司的时间，数据可用任何时间格式显示。

02 选中C2单元格，在其中输入公式=YEAR(B2)，按下“Enter”键，可得到该员工进入公式的年份。

03 选中该单元格，使用填充柄功能复制公式到该列中的其他单元格区域即可。

例　显示商品的销售月份

如果需要返回某个日期对应的月份，可使用MONTH函数。函数的语法为：=MONTH(serial_number)，其中参数serial_number为一个日期自，也可以指定为加双引号的表示日期的文本，或者包含日期值的单元格的引用。

下面以统计每件商品的销售月份为例，介绍MONTH函数的使用方法。

01 在工作表中输入每件商品的销售日、商品名、单价等详细信息。

02 选中要显示销售月的某个单元格，输入公式=MONTH(A2)，按下“Enter”键确认。

03 使用填充柄功能复制公式到该列中的其他单元格中，可得到所有商品的销售月份。

3.2　计算期间差

例　计算两个日期之间的工作日

如果需要计算某两个日期之间的天数，可以使用NETWORKDAYS函数或减号运算符“-”实现。

1．使用NETWORKDAYS函数计算

NETWORKDAYS函数用于返回两个日期之间的工作日数值，该函数的语法为：=NETWORKDAYS(start_date, end_date, [holidays])，各参数的含义介绍如下。

·参数Start_date，表示一个代表开始日期的日期。

·参数End_date，表示一个代表终止日期的日期。

·参数Holidays为可选项，指不在工作日历中的一个或多个日期所构成的可选区域，例如省/市/自治区和国家/地区的法定假日以及其他非法定假日。该列表可以是包含日期的单元格区域，或者是表示日期序列号的数组常量。

下面举例计算。

01 分别在“A2”和“B2”单元格中输入需要参与计算的两个日期。

02 在需要显示结果的单元格中输入公式：=NETWORKDAYS (A2,B2)，按下“Enter”键确认即可。

2．使用减号运算符“-”计算

如果需要计算两个日期间的时间间隔天数，即包含节假日的间隔天数，可直接用减号运算符“-”计算，具体操作如下。

01 分别在“A3”和“B3”单元格中输入需要参与计算的两个日期，将单元格格式设置为日期格式。

02 在需要显示结果的单元格中输入等式：=B3-A3，按下“Enter”键确认即可。

例　计算两个日期之间的年份数

如果需要计算两个日期之间的年份数，可通过“YEAR”函数实现，具体操作如下。

01 分别在“A3”和“B3”单元格中输入需要参与计算的两个日期。

02 在需要显示结果的单元格中输入公式：=YEAR(B2)-YEAR(A2)，然后确认即可。

例　计算两个日期之间的月份数

如果需要计算某两个日期之间的月份数，可分为计算日期在同一年和不在同一年两种情况，下面分别介绍。

1．要计算间隔月份数的年份在同年

如果需要计算间隔月份的两个日期在同年，可使用MONTH函数实现。下面举例介绍。

01 在A2和B2单元格中分别输入同一年中的两个日期。

02 在需要显示间隔月份的单元格中输入公式：=MONTH(B2)+MONTH(A2)，按下“Enter”键确认。

2．要计算间隔月份数的年份不在同年

如果要计算间隔月份数的两个日期不在同一年，则需要使用MONTH函数和YEAR函数共同实现，具体操作如下。

01 在A2和B2单元格中分别输入两个不在同一年的日期。

02 在需要显示间隔月份的单元格中输入公式：=(YEAR(B3)-YEAR(A3))*12+MONTH(B3)-MONTH(A3)，然后按下“Enter”键确认即可。

例　计算两个时间之间的小时数

如果需要计算两个时间之间间隔的小时数，可通过减号运算符“-”求时间之间的差值，然后使用“TEXT”函数设置时间格式。

下面举例计算时间“8：05 am”和“5：30 pm”之间的小时数。

01 分别在“A9”和“B9”单元格中输入需要参与计算的两个时间。

02 在需要显示结果的单元格中输入公式：=TEXT(B9 -A9,"h")，然后按下“Enter”键确认即可。

例　计算两个时间间隔的小时和分钟数

如果需要计算两个时间间隔的小时和分钟数，也可通过减号运算符“-”求时间之间的差值，然后使用“TEXT”函数设置时间格式。

下面以计算时间“8：05 am”和“5：30 pm”之间的小时和分钟数为例，具体操作如下。

01 分别在“A9”和“B9”单元格中输入需要参与计算的两个时间。

02 在需要显示结果的单元格中输入公式：=TEXT(B9 -A9, "h:mm")，按下“Enter”键确认。

例　计算两个时间间隔的精确时间

如果需要计算两个时间之间间隔的精确时间，即准确到秒数，也可通过减号运算符“-”求时间之间的差值，然后使用“TEXT”函数设置时间格式。

下面举例计算时间“8：05：29 am”和“5：30：05 pm”之间间隔的精确时间。

01 分别在“A9”和“B9”单元格中输入需要参与计算的两个时间。

02 在需要显示结果的单元格中输入公式：=TEXT(B9 -A9,"h:mm")，按下“Enter”键确认。

例　计算某个日期之前的天数

如果需要计算某个日期之前有多少天，可通过“TODAY”函数实现。

1．计算当前日期距离2011/6/1还有多少天

01 在“A2”单元格中输入日期“2011/6/1”。

02 在单元格中输入公式：=A2-TODAY()，然后按下“Enter”键确认即可。

2．计算两个日期间的天数

01 在“A2”和“B2”单元格中分别输入需要计算的日期。

02 在单元格中输入公式：=A3-A2，按下“Enter”键确认即可。

例　计算月底日期

在Excel中，利用“DATE”函数可以输入无效日期参数。DATE函数的语法为：=DATE(year,month,day)，各参数的含义介绍如下。

·参数year：如果year介于0到1899之间包含这两个值），则Excel会将该值与1900相加来计算年份；如果 year 介于1900到9999之间包含这两个值），则Excel将使用该数值作为年份；如果year小于0或大于等于10000，则Excel将返回错误值#NUM。

·参数month：如果month大于12，则month从指定年份的一月份开始累加该月份数；如果month小于1，month则从指定年份的一月份开始递减该月份数，然后再加上 1 个月。

·参数day：若大于指定月份的天数，则day从指定月份的第一天开始累加该天数；若小于 1，则day从指定月份的第一天开始递减该天数，然后再加上1天。

例如在月份参数中输入18或者在日期参数中输入大于31的数值，DATE函数都会自动转换计算结果。

利用DATE函数的这一特殊用法，通过在月参数上加一个增量值1，在日参数中使用值0，即用计算月份下一月的“第0”天的方法得到需要计算月份的月底日期，具体操作如下。

01 在“A2”单元格中输入需要计算月底日期的日期。

02 在单元格中输入公式：=DATE(YEAR(A2),MONTH (A2)+1,)，按下“Enter”键确认即可。

例　计算某一年的天数

如果需要计算某一年的天数，可通过“YEAR”和“DATE”函数实现。下面以计算2012年的天数为例，具体操作如下。

01 在“A2”单元格中以日期格式输入2012年的任何一个日期。

02 在“B2”单元格中输入公式：=DATE(YEAR(A2), 12,31)-DATE(YEAR(A2),1,0)，然后按下“Enter”键确认即可。

例　计算日期对应的星期数

若希望计算某个日期对应的星期数，可通过“CHOOSE”和“WEEKDAY”函数实现。下面简单介绍这两个函数的用法。

CHOOSE函数：用于返回数值参数列表中的数值，其语法为= CHOOSE(index_num, value1, [value2], ...)。其中参数index_num为指定所选定的值参数，若该参数为1则返回value1，若为2则返回value2，以此类推。

WEEKDAY函数：用于返回某日期为星期几，其语法为=WEEKDAY(serial_number,[return_type])。其中参数Serial_number代表尝试查找的那一天的日期，参数return_type为可选项，用于确定返回值类型的数字。参数return_type的值与其返回数字及对应星期数如下。

·Return_type为1或省略，返回数字1（星期日）到数字7（星期六）。

·Return_type为2，返回数字1（星期一）到数字7（星期日）。

·Return_type为3，返回数字0（星期一）到数字6（星期日）。

·Return_type为11，返回数字1（星期一）到数字7（星期日）。

·Return_type为12，返回数字1（星期二）到数字7（星期一）。

·Return_type为13，返回数字1（星期三）到数字7（星期二）。

·Return_type为14，返回数字1（星期四）到数字7（星期三）。

·Return_type为15，返回数字1（星期五）到数字7（星期四）。

·Return_type为16，返回数字1（星期六）到数字7（星期五）。

·Return_type为17，返回数字1（星期日）到数字7（星期六）。

下面以数字1-7表示星期一到星期日为例，计算日期对应的星期数。

01 在“A2”单元格中输入需要计算星期数的日期。

02 在“B2”单元格中输入公式：=CHOOSE(WEEKDAY(A2,2),"星期一","星期二","星期三","星期四","星期五","星期六","星期日")，按下“Enter”键确认。

例　计算两个日期之间的周数

如果需要计算两个日期之间的周数，可通过“WEEKNUM”函数实现。WEEKNUM函数用于返回指定日期的周数，其语法为：=WEEKNUM(serial_number, [return_type])。其中参数serial_number代表一周中的日期，参数return_type为可选项，用于确定星期从哪一天开始。

下面介绍return_type的值及其对应的一周的第一天为星期几的对应关系如下。

·return_type为1或省略，一周的第一天为星期日。

·return_type为2，一周的第一天为星期一。

·return_type为11，一周的第一天为星期一。

·return_type为12，一周的第一天为星期二。

·return_type为12，一周的第一天为星期三。

·return_type为14，一周的第一天为星期四。

·return_type为15，一周的第一天为星期五。

·return_type为16，一周的第一天为星期六。

·return_type为17，一周的第一天为星期日。

·return_type为21，一周的第一天为星期一。

下面以计算“2011/6/20”和“2011/12/27”两个日期之间间隔的周数为例，具体操作如下。

01 在“A2”和“B2”单元格中分别输入需要计算的日期。

02 在“C2”单元格中输入公式：=WEEKNUM(B2,1)-WEEKNUM(A2,1)，然后按下“Enter”键确认，即可得到两个日期的间隔周数。

注意：如果要计算间隔周数的两个日期不在同一年，则不能用WEEKNUM函数，否则会计算错误。

例　根据生日计算员工年龄

如果已知出生年月，需要计算年龄，可通过“DAYS360”函数实现。下面以统计员工的年龄为例，具体操作如下。

01 在表格中输入相关数据，包含员工的生日列。

02 在“D2”单元格中输入公式：=DAYS360(B2,today())/360，然后按下“Enter”键确认。

03 使用填充柄功能复制公式到该列的其他单元格中，即可统计所有员工的年龄。

例　计算员工的工龄

利用YEAR函数和TODAY函数可快速计算出员工的工龄，具体操作方法如下。

01 打开需要编辑的工作簿，选中要显示计算结果的单元格，在编辑框输入函数表达式：=YEAR(TODAY())-YEAR(F3)。

02 完成输入后按下“Enter”键确认，此时该计算结果显示的是日期格式，需要在“数字”组的“数字格式”下拉列表中选择“常规”选项，以转换为常规数值。

03 将鼠标指针指向含义公式的单元格右下角，待指针变为黑色十字状 时按下鼠标左键不放并向下拖动，当拖动到目标单元格后释放鼠标，即可计算出其他员工的工龄。

3.3　对日期求和

例　为日期添加天数

如果需要为某日期添加一个天数，可通过下面的方法实现。

01 在两个单元格中分别输入需要设置的日期和需要追加的天数。

02 在需要显示结果的单元格中输入等式，如“=A3+B3”，然后按下“Enter”键确认即可。

例　为日期添加月数

如果要为某日期追加一个月数，可通过DATE、YEAR、MONTH和DAY函数来实现。

下面以给日期“2011/5/8”加上8个月为例，具体操作如下。

01 在两个单元格中分别输入需要设置的日期和需要追加的月数。

02 在单元格中输入公式：=DATE(YEAR (A2),MONTH (A2)+B2,DAY(A2))，然后按下“Enter”键确认即可。

例　为日期添加年数

如果要为某日期添加一个年数，可通过DATE、YEAR、MONTH和DAY函数来实现。

下面以给日期“2011/6/20”加上10年为例，具体操作如下。

01 在两个单元格中分别输入需要设置的日期和需要追加的年数。

02 在需要显示结果的单元格中输入公式：=DATE (YEAR(A2)+B2,MONTH(A2),DAY(A2))，然后按下“Enter”键确认即可。

例　对24小时制时间求和

如果需要对24小时制的时间求和，可通过“SUM”函数实现，下面举例说明。

01 在“B2:B6”单元格区域中输入需要计算的时间值。

02 在单元格中输入公式：=SUM(B2:B6)，然后按下“Enter”键确认即可。

例　按12小时制给时间添加小时

如果需要对12小时制的时间求和，可通过“TIME”函数实现。TIME函数的语法为：=TIME(hour, minute, second)。

下面计算给“A2”单元格的时间加上5小时后的时间值。

01 选中需要计算和显示结果的单元格，在“开始”选项卡的“数字”组中，单击右下角的展开按钮。

02 弹出“设置单元格格式”对话框，在“数字”选项卡中选中“分类”列表框中的“时间”选项，在右侧的“类型”列表框中选择一种12小时制格式，单击“确定”按钮。

03 在表格中输入需要计算的时间值和添加的小时数，在要显示结果的单元格中输入公式：=A2+TIME (B2, 0,0)，然后按下“Enter”键确认即可。

例　按12小时制给时间添加分钟

如果需要给12小时制时间添加分钟，也通过“TIME”函数实现。下面以“A2”单元格的时间加上30分钟后的时间值为例，具体操作如下。

01 将单元格格式设置为12小时制格式。

02 在表格中输入需要计算的时间值和添加的分钟数。

03 在需要显示结果的单元格中输入公式：=A2+TIME(0,B2,0)，然后按下“Enter”键确认即可。

例　按12小时制给时间添加秒

如果需要给一个12小时制时间添加秒数，首先需要将参与计算的时间单元格设置为包含秒数的12小时制格式，然后进行计算即可。

下面以给12小时时间添加50秒为例，具体操作如下。

01 选中需要计算和显示结果的单元格，在“开始”选项卡的“数字”组中，单击右下角的展开按钮。

02 弹出“设置单元格格式”对话框，在“数字”选项卡中选中“分类”列表框中的“时间”选项，在右侧的“类型”列表框中选择“1:30:55 PM”格式，然后单击“确定”按钮。

03 在表格中输入需要计算的时间值和添加的秒数，在要显示结果的单元格中输入公式：=A2+TIME(0,0,B2)，然后按下“Enter”键确认即可。

第4章　财务函数应用

4.1　计算支付额和累计额

例　贷款在给定期间累计须偿还的利息数

如果需要计算一笔贷款在指定期间累计需要偿还的利息数额，可通过“CUMIPMT”函数实现。

CUMIPMT函数的语法为：= CUMIPMT(rate,nper,pv, start_period,end_period,type)，各参数的含义介绍如下。

·Rate：利率

·Nper：总付款期数

·Pv：现值

·start_period：计算中的首期，付款期数从1开始计数

·end_period：计算中的末期

·type：付款时间类型

假设贷款100000元，年利率为9%，贷款期限为20年，下面计算利息。

1．计算贷款在第一个月所付的利息

01 在“B1”单元格中输入利率值，在“B2”单元格中输入付款期数，在“B3”单元格中输入现值10万元。

02 在单元格中输入公式：=CUMIPMT(B1/12,B2*12, B3,1,1,0)，然后按下“Enter”键确认即可。

2．计算贷款在第二年中所付的总利息

01 在“B1”单元格中输入利率值，在“B2”单元格中输入付款期数，在“B3”单元格中输入现值10万元。

02 在单元格中输入公式：=CUMIPMT(B1/12,B2*12,B3, 13,24,0)，然后按下“Enter”键确认即可。

提示：本例计算第二年第一个月到第二年最后一个月的利息，因此参数“start_period”为13，参数“end_period”为24。

例　贷款在给定期间累计偿还的本金数额

如果需要计算一笔贷款，如房贷，在给定期间需要累计偿还的本金数额，在可通过“CUMPRINC”函数实现。CUMPRINC函数的语法为：= CUMPRINC(rate,nper,pv, start_period, end_period,type)，其中各参数的含义与CUMIPMT函数中各参数的含义相同。下面举例说明。

假设贷款10万元，年利率为9%，贷款期限为20年，下面计算累计偿还的本金数额。

1．计算贷款在第一个月偿还的本金

01 在“B1”单元格中输入利率值，在“B2”单元格中输入付款期数，在“B3”单元格中输入现值10万元。

02 在单元格中输入公式：= CUMPRINC(B1/12,B2*12, B3,1,1,0)，然后按下“Enter”键确认即可。

2．计算贷款在第二年偿还的本金之和

01 在“B1”单元格中输入利率值，在“B2”单元格中输入付款期数，在“B3”单元格中输入现值10万元。

02 在单元格中输入公式：=CUMPRINC(B1/12,B2*12,B3, 13,24,0)，然后按下“Enter”键确认即可。

例　计算贷款的每期付款额

如果需要基于固定利率及等额分期付款方式，计算贷款的每期付款额，如房贷月供，可通过“PMT”函数实现。

·PMT 函数的语法为：=PMT(rate,nper,pv,fv,type)，其中各参数的含义介绍如下。

·Rate：贷款利率。

·Nper：该项贷款的付款总数。

·Pv：现值，或一系列未来付款的当前值的累积和，也称本金。

·Fv：未来值。

·Type：指定各期的付款时间是在期初（1）还是期末（0或省略）。

下面举例计算在指定条件下的贷款月支付额。

01 在“B1”单元格中输入贷款利率，在“B2”单元格中输入付款的月份数，在“B3”单元格中输入贷款额。

02 在单元格中输入公式：=PMT(B1/12, B2, B3)，然后按下“Enter”键确认即可。

例　计算给定期限内投资的利息偿还额

如果需要基于固定利率及等额分期付款方式，返回给定期数内对投资的利息偿还额，可通过“IPMT”函数实现。IPMT函数的语法为：=IPMT(rate,per,nper,pv,fv,type)，各参数的含义介绍如下。

·Rate：各期利率。

·Per：用于计算其利息数额的期数，必须在1到nper之间。

·Nper：总投资期，即该项投资的付款期总数。

·Pv：现值，即从该项投资开始计算时已经入帐的款项，也称本金。

·Fv：未来值，或在最后一次付款后希望得到的现金余额。如果省略fv，则假设其值为零。

·Type：数字0或1，用以指定各期的付款时间是在期初还是期末。如果省略，则假设其值为零。

假设贷款8万元，年利率为10%，贷款期数为1，贷款年限为3年，分别计算第一个月和最后一年的利息。

1．计算第一个月的利息

01 在B1:B4单元格区域中分别输入利率、期数、总投资期以及现值等数据。

02 在单元格中输入公式：=IPMT(B1/12,B2*3,B3,B4)，然后按下“Enter”键确认即可。

2．计算最后一年的利息

01 在B1:B4单元格区域中分别输入利率、期数、总投资期以及现值等数据。

02 在单元格中输入公式：=IPMT(B1,3,B3,B4)，然后按下“Enter”键确认即可。

4.2　计算利率和资产

例　计算资产在给定期间内的折旧值

如果需要计算一笔资产在给定期间内的折旧值，可通过“DB”函数实现。“DB”函数的语法为：=DB(cost,salvage, life,period,month)，各参数的含义介绍如下。

·Cost：资产原值。

·salvage：资产在折旧期末的价值，也称为资产残值。

·life：折旧期限（有时也称作资产的使用寿命）。

·period：需要计算折旧值的期间，Period参数必须使用与life参数相同的单位。

·month：第一年的月份数，若省略则假设为“12”。

假设资产原值为100万元，使用6年后资产残值为10万元，计算资产在第一年7个月内的折旧值。

01 在B1:B3单元格中区域中分别输入资产原值、资产残值、折旧期限等数据。

02 在需要显示结果的单元格中输入公式：=DB (B1,B2,B3,1,7)，然后按下“Enter”键确认即可。

例　计算有效的年利率

如果需要利用给定的名义年利率和每年的复利期数，计算有效的年利率，可通过“EFFECT”函数实现。“EFFECT”函数的语法为：= EFFECT(nominal_rate,npery)，各参数的含义介绍如下。

·nominal_rate：名义利率。

·npery：每年的复利期数。

下面举例计算指定条件下的有效年利率。

01 在“B1”单元格中输入名义利率，在“B2”单元格中输入每年的复利期数。

02 在要显示结果的单元格中输入公式：=EFFECT(B1, B2)，按下“Enter”键确认。

例　计算某项投资的未来值

如果需要知道某项投资的未来收益情况，如N年后的存款总额，可通过“FV”函数实现。FV函数的语法为：=FV(rate,nper,pmt,pv,type)，各参数的含义介绍如下。

·Rate：各期利率。

·Nper：总投资期，即该项投资的付款期总数。

·Pmt：各期所应支付的金额，其数值在整个年金期间保持不变，通常pmt包括本金和利息，但不包括其他费用及税款，如果忽略pmt，则必须包括pv参数。

·Pv：现值，即从该项投资开始计算时已经入帐的款项，或一系列未来付款的当前值的累积和，也称为本金，如果省略PV，则假设其值为零，并且必须包括pmt参数。

·Type：数字0或1，用以指定各期的付款时间是在期初还是期末。如果省略type，则假设其值为零。

注意：由于投资是先付出金额，因此在输入计算公式时，参数“Pmt”和参数“Pv”应为负数，这样得出的计算结果才为正数，即未来的收益金额。

下面举例说明。给定条件：年利率为6%，总投资期为10年，各期应付500元，现值为500元，计算未来值。

01 在B1：B4单元格区域中分别输入年利率、付款期数、各期应付金额、现值等数据。

02 在需要显示结果的单元格中输入公式：=FV(B1/12, B2, B3,B4)，按下“Enter”键确认即可。

例　计算一组现金流的内部收益率

如果需要计算由数值代表的一组现金流的内部收益率，可通过“IRR”函数实现。IRR函数的语法为：=IRR(values,guess)，各参数的含义介绍如下。

·Values：为数组或单元格的引用，包含用来计算返回的内部收益率的数字。

·Guess：为对函数IRR计算结果的估计值。

下面分别举例计算投资n年后不包含估计值和包含估计值的内部收益率。

1．计算投资四年后的内部收益率

01 在“B1:B5”单元格区域中分别输入初期成本和第一年到第四年的净收入。

02 在单元格中输入公式：=IRR(B1:B5)，按下“Enter”键确认即可。

2．计算两年后的内部收益率（需包含估计值）

01 在“B1:B3”单元格区域中分别输入初期成本和第一年到第二年的净收入，在“B4”单元格中输入一个估计值。

02 在需要显示结果的单元格中输入公式：=IRR(B1:B3,B4)，按下“Enter”键确认即可。

例　计算连续期间内现金流的修正内部收益率

如果需要计算某一连续期间内现金流的修正内部收益率，可通过“MIRR”函数实现。MIRR 函数的语法为：=MIRR(values,finance_rate,reinvest_rate)，其中各参数的含义介绍如下。

·Values：一个数组或对包含数字的单元格的引用，代表着各期的一系列支出（负值）及收入（正值）。

·Finance：现金流中使用的资金支付的利率。

·Rate：年利率。

·reinvest_rate：将现金流再投资的收益率。

下面给定资产原值和三年的收益额，计算三年后现金流的修正收益率。

01 在“B1:B4”单元格区域中分别输入资产原值，以及第一年到第三年的收益，在“B5”单元格中输入年利率，在“B6”单元格中输入再投资的收益率。

02 在单元格中输入公式：=MIRR(B1:B4,B5,B6)，然后按下“Enter”键确认即可。

例　计算名义年利率

如果需要计算某条件下的名义利率，可通过“NOMINAL”函数实现。NOMINAL函数的语法为：=NOMINAL(effect_rate,npery) ，各参数的含义介绍如下。

·effect_rate：实际利率。

·npery：每年的复利期数。

假设指定实际利率为5.35%，每年的复利期数为4，计算名义年利率。

01 在“B1”单元格中指定实际利率，在“B2”单元格中指定每年的复利期数。

02 在单元格中输入公式：=NOMINAL(B1, B2)，然后按下“Enter”键确认即可。

例　计算投资总期数

如果需要基于固定利率及等额分期付款方式，计算某项投资的总期数，可通过“NPER”函数实现。NPER函数的语法为：=NPER(rate,pmt,pv,fv,type)，其中各参数的含义介绍如下。

·Rate：各期利率，为固定值。

·Pmt：各期所应支付的金额，其数值在整个年金期间保持不变。通常情况下，pmt包括本金和利息，但不包括其他的费用及税款。

·Pv：现值。

·Fv：未来值。

·Type：用以指定各期的付款时间是在期初（1）还是期末（0）。

下面分别计算包含未来值和不包含未来值的某项投资的总期数。

1．计算指定条件下投资的总期数（包含未来值）

01 在“B1：B5”单元格区域中分别指定年利率、各期所付金额、现值、未来值以及各期的付款时间。

02 在单元格中输入公式：=NPER(B1/12,B2,B3,B4,1)，然后按下“Enter”键确认即可。

2．计算指定条件下投资的总期数（不包括未来值）

01 在“B1:B5”单元格区域中分别指定年利率、各期所付金额、现值、未来值以及各期的付款时间。

02 在单元格中输入公式：=NPER(B1/12, B2,B3)，然后按下“Enter”键确认即可。

例　计算投资净现值

如果需要通过使用贴现率以及一系列未来支出（负值）和收入（正值），计算一项投资的净现值，可通过“NPV”函数实现。

NPV函数的语法为：=NPV(rate,value1,value2,...)，其中各参数的含义介绍如下。

·Rate：某一期间的贴现率，为固定值。

·value1,value2,...：为1到29个参数，代表支出及收入。

下面根据表格中的指定条件，计算投资的净现值。

01 在“B1:B5”单元格区域中分别指定年贴现率、一年前的初期投资以及第一年至第三年的收益。

02 在需要显示结果的单元格中输入公式：=NPV(B1,B2, B3,B4,B5)，按下“Enter”键确认即可。

例　计算年金的各期利率

如果需要计算年金的各期利率，可通过“RATE”函数实现。RATE函数的语法为：=RATE(nper,pmt,pv,fv,type, guess)，其中各参数的含义介绍如下。

·Nper：总投资期。

·Pmt：各期付款额。

·Pv：现值。

·Fv：未来值。

·Type：用以指定各期的付款时间是在期初（1）还是期末（0）。

·Guess：预期利率。

假设贷款金额为1万元，贷款期限为10年，每月还款500元，下面分别计算贷款的月利率和年利率。

1．计算贷款的月利率

01 在“B1:B3”单元格区域中分别输入贷款期限、每月支付额和贷款额。

02 在单元格中输入公式：=RATE(B1*12,B2,B3)，然后按下“Enter”键确认即可。

2．计算贷款的年利率

01 在“B1:B3”单元格区域中分别输入贷款期限、每月支付额和贷款额。

02 在单元格中输入公式：=RATE(B1*12,B2,B3)*12，然后按下“Enter”键确认即可。

例　计算资产的线性折旧值

如果需要计算某项资产在一个期间中的线性折旧值，可通过“SLN”函数实现。SLN函数的语法为：=SLN(cost, salvage,life)，其中各参数的含义介绍如下。

·Cost：资产原值。

·Salvage：资产在折旧期末的价值（也称为资产残值）。

·Life：折旧期限（也称资产的使用寿命）。

假设一项资产的原值为3万元，使用10年后，残值为7500元，计算该资产每年的折旧值。

01 在“B1:B3”单元格区域中分别输入资产原值、资产残值和折旧期限。

02 在单元格中输入公式：=SLN(B1,B2,B3)，然后按下“Enter”键确认即可。

例　按年限折旧法计算折旧值

如果需要计算某项资产按年限总和折旧法计算的指定期间的折旧值，可通过“SYD”函数实现。SYD函数的语法为：=SYD(cost,salvage,life,per)，其中各参数的含义介绍如下。

·Cost：资产原值。

·Salvage：资产在折旧期末的价值。

·Life：折旧期限。

·Per：期间。

假设一项资产的原值为3万元，使用10年后，残值为7500元，下面分别计算第一年和第十年的折旧值。

1．计算第一年的折旧值

01 在“B1:B3”单元格区域中分别输入资产原值、资产残值和折旧期限。

02 在单元格中输入公式：=SYD(B1,B2,B3,1)，按下“Enter”键确认。

2．计算第十年的折旧值

01 在“B1:B3”单元格区域中分别输入资产原值、资产残值和折旧期限。

02 在单元格中输入公式：=SYD(B1,B2,B3,10)，按下“Enter”键确认即可。

例　计算任何期间内的折旧值

如果需要使用双倍余额递减法或其他指定的方法，计算指定的任何期间内的资产折旧值，可通过“VDB”函数实现。VDB函数的语法为：=VDB(cost,salvage,life, start_period,end_period,factor,no_switch)，其中各参数的含义介绍如下。

·Cost：资产原值。

·Salvage：资产在折旧期末的价值（也称为资产残值）。

·Life：折旧期限（也称作资产的使用寿命）。

·Start_Period：进行折旧计算的起始期间。

·end_period：进行折旧计算的截止期间。

·factor：余额递减速率（折旧因子）。

·no_switch：逻辑值。

1．计算第一天的折旧值

01 在“B1:B3”单元格区域中分别输入资产原值、资产残值和折旧期限。

02 在单元格中输入公式：=VDB(B1,B2,B3*365,0,1)，然后按下“Enter”键确认即可。

2．计算第6个月与第18个月间的折旧值

01 在“B1:B3”单元格区域中分别输入资产原值、资产残值和折旧期限。

02 在单元格中输入公式：=VDB(B1,B2,B3*12,6,18)，按下“Enter”键确认。

例　计算现金流的净现值

如果需要计算一组现金流的净现值，可通过“XNPV”函数实现。XNPV函数的语法为：= XNPV(rate,values,dates)，各参数含义介绍如下。

·Rate：应用于现金流的贴现率；

·Values：与dates中的支付时间相对应的一系列现金流转；

·Dates：与现金流支付相对应的支付日期表，第一个支付日期代表开始。

假设指定贴现率及一系列的现金流，并输入现金流对于的支付日期，计算成本和收益的投资净现值。

01 在“B1”单元格中输入贴现率，在“B2:B6”单元格区域中输入一系列现金流，在“C2:C6”单元格区域中输入现金流对应的支付日期。

02 在单元格中输入公式：=XNPV(B1,B2:B6,C2:C6)，然后按下“Enter”键确认即可。

4.3　证券的计算

例　计算定期付息有价证券的应计利息

如果需要计算定期付息的有价证券的应计利息，可通过“ACCRINT”函数实现。ACCRINT函数的语法为：=ACCRINT(issue,first_interest,settlement,rate,par,frequency,basis)。各参数的含义介绍如下。

·Issue：有价证券的发行日。

·first_interest：有价证券的起息日。

·settlement：有价证券的成交日。

·rate：有价证券的年息票利率。

·par：有价证券的票面价值。

·frequency：年付息次数。

·basis：日计数基准类型（0、1、2、3、4）。

假设年息票利率为10%，票面价值为1000元，年付息次数为2次（即半年一付），以30/360为日计算基准类型，分别计算含发行日和不包含发行日的应付利息。

1．计算含发行日的应付利息

01 在“B1:B7”单元格区域中依次输入有价证券的发行日、起息日、成交日、年息票利率、票面价值、年付息次数以及日计数基准类型。

02 在单元格中输入公式：=ACCRINT (B1,B2,B3,B4,B5, B6,B7)，按下“Enter”键确认即可。

2．计算发行日除外的应付利息

01 在“B1:B7”单元格区域中依次输入有价证券的发行日、起息日、成交日、年息票利率、票面价值、年付息次数以及日计数基准类型。

02 输入公式：=ACCRINT(DATE(2013,2,5),B2,B3,B4,B5, B6,B7)，然后按下“Enter”键确认即可。

例　计算到期一次性付息的有价证券的应计利息

如果需要计算到期一次性付息的有价证券的应计利息，可通过“ACCRINTM”函数实现。ACCRINTM 函数的语法为：=ACCRINTM(issue,maturity,rate,par,basis)，其中个参数的含义介绍如下。

·Issue：有价证券的发行日。

·Maturity：有价证券的到期日。

·Rate：有价证券的年息票利率。

·Par：有价证券的票面价值。

·Basis：日计数基准类型（0、1、2、3、4）。

假设指定有价证券的发行日为2013/1/1，到期日为2013/6/15，年息票利率为10%，票面价值1000元，日计数基准类型为3（即实际天数/360），计算应计利息。

01 在“B1:B5”单元格区域中依次输入一次性付息的有价证券的发行日、到期日、息票利率、票面价值以及日计数基准类型。

02 在需要显示结果的单元格中输入公式：=ACCRINTM (B1,B2,B3,B4,B5)，按下“Enter”键确认。

例　计算当前付息期内截止到成交日的天数

如果需要计算从债券付息期开始到成交日的天数，可通过“COUPDAYBS”函数实现。COUPDAYBS函数的语法为：=COUPDAYBS(settlement,maturity,frequency,basis)，其中个参数的含义介绍如下。

·Settlement：证券的成交日。

·Maturity：证券的到期日。

·Frequency：为年付息次数，按年支付为1；按半年期支付为2；按季支付为4。

·Basis：日计数基准类型（0、1、2、3、4）。

假设证券的发行日为2013/1/25，到期日为2013/6/18，年付息次数为2次，以实际天数/实际天数为日基数基准类型，计算从证券付息期开始到成交日的天数。

01 在“B1:B4”单元格区域中依次输入证券的成交日、到期日、年付息次数以及日计数基准类型。

02 在单元格中输入公式：=COUPDAYBS(B1,B2,B3, B4)，按下“Enter”键确认。

例　计算从成交日到下一付息日之间的天数

如果需要计算从成交日到下一个付息日的天数，可通过“COUPDAYSNC”函数实现。COUPDAYSNC函数语法为：=COUPDAYSNC(settlement,maturity,frequency,basis)。计算方法如下。

01 在“B1:B4”单元格区域中依次输入证券的成交日、到期日、年付息次数以及日计数基准类型。

02 在单元格中输入公式：=COUPDAYSNC(B1,B2,B3, B4)，然后按下“Enter”键确认即可。

例　计算成交日和到期日之间的付息次数

如果需要计算成交日和到期日之间的付息次数，可通过“COUPNUM”函数实现。COUPNUM函数的语法为：=COUPNUM(settlement,maturity,frequency,basis)。计算方法如下。

01 在“B1:B4”单元格区域中依次输入证券的成交日、到期日、年付息次数以及日计数基准类型。

02 在单元格中输入公式：=COUPNUM(B1,B2,B3,B4)，然后按下“Enter”键确认即可。

例　计算成交日之前的付息日期

如果需要计算成交日之前的付息日的付息日期，可通过“COUPPCD”函数实现。COUPPCD函数的语法为：=COUPPCD(settlement,maturity,frequency,basis)。计算方法如下。

01 在“B1:B4”单元格区域中依次输入证券的成交日、到期日、年付息次数以及日计数基准类型。

02 将需要显示结果的单元格设置为日期格式，输入公式：=COUPPCD(B1,B2,B3,B4)，按下“Enter”键确认。

例　计算有价证券的贴现率

如果需要计算有价证券的贴现率，可通过“DISC”函数实现。DISC函数的语法为：=DISC(settlement,maturity, pr,redemption,basis)，其中各参数的含义介绍如下。

·Settlement：证券的成交日。

·Maturity：证券的到期日。

·Pr：面值￥100的有价证券的价格。

·Redemption：面值￥100的有价证券的清偿价值。

·Basis：日计数基准类型（0、1、2、3、4）。

假设证券的价格为￥97.975，以实际天数/360为日计数基准类型，计算有价证券的贴现率的方法如下。

01 在“B1:B5”单元格区域中依次输入证券的成交日、到期日、价格、清偿价值以及日计数基准类型。

02 将要显示结果的单元格设置为百分比格式，输入公式：=DISC(B1,B2,B3,B4,B5)，按下“Enter”键确认即可。

例　计算一次性付息证券的利率

如果需要计算一次性付息证券的利率，可通过“INTRATE”函数实现。语法为：=INTRATE(settlement, maturity,investment,redemption,basis)，其中各参数的含义介绍如下。

·Settlement：证券的成交日。

·Maturity：有价证券的到期日。

·Investment：有价证券的投资额。

·Redemption：有价证券到期时的清偿价值。

·Basis：日计数基准类型（0、1、2、3、4）。

计算一次性付息证券利率的方法如下。

01 在“B1:B5”单元格区域中依次输入证券的成交日、到期日、投资额、清偿价值以及日计数基准类型。

02 在单元格中输入公式：=INTRATE(B1,B2,B3,B4,B5)，然后按下“Enter”键确认即可。

例　计算首期付息日不固定的有价证券价格

如果需要返回首期付息日不固定的面值￥100的有价证券价格，可通过“ODDFPRICE”函数实现。ODDFPRICE函数的语法为：=ODDFPRICE(settlement,maturity,issue, first_coupon,rate,yld,redemption,frequency,basis)，其中各参数的含义介绍如下。

·Settlement：证券的成交日。

·Maturity：有价证券的到期日。

·Issue：证券的发行日。

·first_coupon：证券的首期付息日。

·rate：证券的利率。

·yld：证券的年收益率。

·redemption：面值￥100的有价证券的清偿价值。

·frequency：年付息次数。

·basis：日计数基准类型。

下面假设在指定条件下，计算首期付息日不固定的面值￥100的有价证券的价格。

01 在“B1:B9”单元格区域中依次输入证券的成交日、到期日、发行日、首期付息日、付息利率、年收益率、年付息次数以及日计数基准类型。

02 在单元格中输入公式：=ODDFPRICE(B1,B2,B3,B4, B5,B6,B7,B8,B9)，然后按下“Enter”键确认即可。

例　计算首期付息日不固定的有价证券的收益率

如果需要计算首期付息日不固定的有价证券的收益率，可通过“ODDFYIELD”函数实现。ODDFYIELD函数的语法为：=ODDFYIELD(settlement,maturity,issue,first_coupon, rate,pr,redemption,frequency,basis)。

对于指定条件下的债券，计算首期付息日不固定的有价证券的收益率，具体操作如下。

01 在“B1:B9”单元格区域中依次输入证券的成交日、到期日、发行日、首期付息日、付息利率、年收益率、年付息次数以及日计数基准类型。

02 在单元格中输入公式：=ODDFYIELD(B1,B2,B3,B4, B5,B6,B7,B8,B9)，然后按下“Enter”键确认即可。

例　计算末期付息日不固定的有价证券价格

如果需要计算末期付息日不固定的面值￥100的有价证券的价格，可通过“ODDLPRICE”函数实现。ODDLPRICE 函数的语法为：=ODDLPRICE(settlement,maturity, last_interest,rate,yld,redemption,frequency,basis)。公式中的部分参数与前面介绍的参数含义相同，其中参数“last_interest”为有价证券的末期付息日。

对于指定条件下的债券，计算末期付息日不固定的面值￥100的有价证券价格，具体操作如下。

01 在“B1:B8”单元格区域中依次输入证券的结算日、到期日、末期付息日、付息利率、年收益率、清偿价值、年付息次数以及日计数基准类型。

02 在单元格中输入公式：=ODDLPRICE(B1,B2,B3,B4, B5,B6,B7,B8)，然后按下“Enter”键确认即可。

例　计算末期付息日不固定的有价证券的收益率

如果需要计算末期付息日不固定的有价证券的收益率，可通过“ODDLYIELD”函数实现。ODDLYIELD函数的语法为：=ODDLYIELD(settlement,maturity,last_interest,rate,pr,redemption,frequency,basis)。

对于指定条件下的证券，计算末期付息日不固定的有价证券的收益率，具体操作如下。

01 在“B1:B8”单元格区域中依次输入证券的成交日、到期日、末期付息日、付息利率、价格、清偿价值、年付息次数以及日计数基准类型。

02 在单元格中输入公式：=ODDLYIELD(B1,B2,B3,B4, B5,B6,B7,B8)，然后按下“Enter”键确认即可。

例　计算有价证券到期收回的金额

如果需要计算有价证券到期收回的金额，可通过“RECEIVED”函数实现。RECEIVED函数的语法为：=RECEIVED(settlement,maturity,investment,discount,basis)。

下面计算指定条件下有价证券到期收回的金额。

01 在“B1:B5”单元格区域中依次输入证券的成交日、到期日、投资额、贴现率以及日计数基准类型。

02 在单元格中输入公式：=RECEIVED(B1,B2,B3,B4, B5)，然后按下“Enter”键确认即可。

例　计算折价发行的有价证券的价格

如果需要计算折价发行的面值￥100的有价证券的价格，可通过“PRICEDISC”函数实现。PRICEDISC函数的语法为：=PRICEDISC(settlement,maturity,discount, redemption,basis)，各参数的含义介绍如下。

·Settlement：证券的成交日。

·Maturity：有价证券的到期日。

·Discount：有价证券的贴现率。

·Redemption：面值￥100的有价证券的清偿价值。

·basis：日计数基准类型。

假设贴现率为5.25%，计算折价发现的有价证券的价格。

01 在“B1:B5”单元格区域中依次输入证券的成交日、到期日、贴现率、清偿价值以及日计数基准类型。

02 在单元格中输入公式：=PRICEDISC(B1,B2,B3,B4, B5)，然后按下“Enter”键确认即可。

例　计算折价发行的有价证券的年收益率

如果需要计算折价发行的有价证券的年收益率，可通过“YIELDDISC”函数实现。YIELDDISC函数的语法为：=YIELDDISC(settlement,maturity,pr,redemption,basis)。下面举例计算指定条件下有价证券的年收益率。

01 在“B1:B5”单元格区域中依次输入证券的成交日、到期日、价格、兑换值以及日计数基准类型。

02 在需要显示结果的单元格中输入公式：=YIELDDISC (B1,B2,B3,B4,B5)，然后按下“Enter”键确认即可。

例　计算到期付息的有价证券的价格

如果需要计算到期付息的面值￥100的有价证券的价格，可通过“PRICEMAT”函数实现。PRICEMAT函数的语法为：=PRICEMAT(settlement,maturity,issue,rate,yld, basis)。下面计算指定条件下的证券价格。

01 在“B1:B6”单元格区域中依次输入证券的成交日、到期日、发行日、戏票年利率、收益率以及日计数基准类型。

02 在单元格中输入公式：=PRICEMAT(B1,B2,B3,B4, B5,B6)，按下“Enter”键确认。

例　计算到期付息的有价证券的年收益率

如果需要计算到期付息的有价证券的年收益率，可通过“YIELDMAT”函数实现。YIELDMAT函数的语法为：=YIELDMAT(settlement,maturity,issue,rate,pr,basis)。下面举例计算。

01 在“B1:B6”单元格区域中依次输入证券的成交日、到期日、发行日、息票年利率、价格以及日计数基准类型。

02 在单元格中输入公式：=YIELDMAT(B1,B2,B3,B4, B5,B6)，按下“Enter”键确认。

例　计算面值￥100的国库券价格

如果需要计算面值￥100的国库券的价格，可通过“TBILLPRICE”函数实现。TBILLPRICE函数的语法为：=TBILLPRICE(settlement,maturity,discount)。

假设国库券的贴现率为9.14%，计算面值￥100的国库券价格。

01 在“B1:B3”单元格区域中依次输入国库券的成交日、到期日和贴现率。

02 在单元格中输入公式：=TBILLPRICE(B1,B2,B3)，确认即可。

例　计算国库券的收益率

如果需要计算国库券的收益率，可通过“TBILLYIELD”函数实现。TBILLYIELD函数的语法为：=TBILLYIELD(settlement,maturity,pr)，各参数的含义介绍如下。

Settlement：国库券的成交日。

Maturity：国库券的到期日。

pr：面值￥100的国库券价格。

假设面值￥100的国库券的价格为98.45，计算国库券的收益率。

01 在“B1:B3”单元格区域中依次输入国库券的成交日、到期日和每￥100面值的价格。

02 在单元格中输入公式：=TBILLYIELD(B1,B2,B3)，然后确认即可。

例　计算国库券的等效收益率

如果需要计算国库券的等效收益率，可通过“TBILLEQ”函数实现。TBILLEQ函数的语法为：TBILLEQ(settlement,maturity,discount)，各参数的含义介绍如下。

·Settlement：国库券的成交日。

·Maturity：国库券的到期日。

·Discount：国库券的贴现率。

假设贴现率为9.14%，计算国库券的等效收益率。

01 在“B1:B3”单元格区域中依次输入国库券的成交日、到期日和贴现率。

02 在单元格中输入公式：=TBILLEQ(B1,B2,B3)，然后按下“Enter”键确认即可。

第5章　统计函数应用

5.1　计算平均值

例　计算满足条件的单元格的平均值

如果需要返回满足给定条件的单元格的平均值，可使用AVERAGEIF函数实现。

AVERAGEIF函数的语法为：=AVERAGEIF(range,criteria,[average_range])，各参数的含义介绍如下。

·range：要计算平均值的一个或多个单元格，其中包括数字或包含数字的名称、数组或引用。如果range为空值或文本值，则AVERAGEIF会返回#DIV0!错误值。

·criteria：数字、表达式、单元格引用或文本形式的条件，用于定义要对哪些单元格计算平均值。

·average_range：要计算平均值的实际单元格集，为可选项。如果忽略该参数，则使用range。如果条件中的单元格为空单元格，AVERAGEIF就会将其视为0值；如果区域中没有满足条件的单元格，则AVERAGEIF会返回#DIV/0!错误值。

下面以统计总成绩大于500的学生的平均成绩为例，使用AVERAGEIF函数的具体操作如下。

01 打开工作表，输入学生姓名和各科成绩，并计算出各学生总成绩。

02 选中需要显示结果的单元格，在其中输入公式：=AVERAGEIF(F3:F9,">500")，然后按下“Enter”键确认，可得到成绩表中总成绩大于500的学生的平均成绩。

例　计算参数列表中数值的平均值

如果需要计算列表中数值的平均值，可通过AVERAGEA函数实现。AVERAGEA函数的语法为：=AVERAGEA(value1,value2,...)。其中，Value1,value2,...为需要计算平均值的1到30个单元格、单元格区域或数值。

使用该函数时需要注意以下问题。

·包含文本的数组或引用参数将作为0（零）计算。例如公式：=AVERAGEA(100,"100")，其结果为100，即忽略参数中的文本。

·空文本("")也作为 0（零）计算。例如B1单元格为文本型数字“‘100”，输入公式：=AVERAGEA(100, B1)，其结果为50，即将引用的文本当做0计算。

·包含TRUE的参数作为1计算，包含FALSE的参数作为0计算。例如输入公式：=AVERAGEA(100, TRUE,FALSE)，其结果为33.667。

·直接在参数中录入文本则返回错误值。例如输入公式：=AVERAGEA(100, "AB")，其结果为#VALUE!，即错误值。

假设给出一组人员的日平均工资，其中请假、工伤等无薪人员也在内，要求计算平均工资，且四舍五入保留2位小数。

01 打开工作表，输入一组人员的姓名及其对于的日平均工资。

02 在需要显示结果的单元格中输入公式：=ROUND (AVERAGEA(B2:B7),2)，然后按下“Enter”键确认即可。

提示：本例是利用AVERAGEA函数计算所有人员的平均工资，对于请假、工伤等无薪人员的工资按照0元来参与求平均值，最后将结果保留两位小数。

例　计算满足多重条件的单元格的平均值

如果需要返回满足多重条件的单元格的平均值，可使用AVERAGEIFS函数实现。

·AVERAGEIFS函数的语法为:=AVERAGEIFS(average_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)，各参数的含义介绍如下。

·average_range：要计算平均值的一个或多个单元格，其中包括数字或包含数字的名称、数组或引用。

·criteria_range1、criteria_range2...：criteria_range1是必需的，随后的criteria_range是可选的。在其中计算关联条件的1至127个区域。

·criteria1、criteria2...：criteria1是必需的，随后的criteria是可选的。数字、表达式、单元格引用或文本形式的1至127个条件，用于定义将对哪些单元格求平均值。

·使用AVERAGEIFS函数时需要注意以下事项。

·如果average_range为空值或文本值，则AVERAGEIFS会返回#DIV0!错误值。

·如果average_range中的单元格无法转换为数字，则AVERAGEIFS会返回错误值#DIV0!。

·仅当average_range中的每个单元格满足为其指定的所有相应条件时，才对这些单元格进行平均值计算。

·如果条件区域中的单元格为空，AVERAGEIFS将其视为0值。

·区域中包含TRUE的单元格计算为1，包含FALSE的单元格计算为0（零）。

·如果没有满足所有条件的单元格，AVERAGEIFS会返回#DIV/0!错误值。

假设比赛有8个评委打分，需要去掉一个最高分和一个最低分，然后再求平均值。

01 打开工作表，在其中分别输入8个评委的评分。

02 在要显示平均值的单元格，输入公式：=AVERAGEIFS(B2:B9,B2:B9,">"&MIN(B2:B9),B2:B9,"<"&MAX(B2:B9))，然后按下“Enter”键确认。

03 选中显示结果的单元格，打开“设置单元格格式”对话框，将数值格式设置为“2”位小数，然后在返回的工作表中即可看到本例的结果为“8.58”。

例　计算数据与其均值的绝对偏差的平均值

如果需要评测一组数据的离散度，可通过AVEDEV函数实现。AVEDEV函数的语法为：=AVEDEV(number1,number2,...)。其中，Number1,number2,...为用于计算绝对偏差平均值的一组参数，参数的个数可有1到30个，可用单一数组代替用逗号分隔的参数。

下面给定一列数据，要求放回数据与其均值的绝对偏差的平均值，方法如下。

01 打开工作表，数据一列数据。

02 选中需要显示结果的单元格，在其中输入公式：=AVEDEV(A2:A6)，然后按下“Enter”键确认即可。

例　计算几何平均值

如果需要计算正数数组或区域的几何平均值，可通过GEOMEAN函数实现。GEOMEAN函数的语法为：=GEOMEAN(number1,number2,...)。其中参数“number1,number2, ...”为用于计算平均值的一组参数。

下面指定一列数据，计算数据集的几何平均值。

01 在“A2:A6”单元格区域中输入需要计算的数据。

02 在需要显示结果的单元格中输入公式：=GEOMEAN(A2:A6)，然后按下“Enter”键确认即可。

例　计算数据集合的调和平均值

调和平均值与倒数的算术平均值互为倒数。如果需要计算数据集合的调和平均值，可通过HARMEAN函数实现。HARMEAN函数的语法为：=HARMEAN(number1, number2,...)。其中参数“number1,number2, ...”为用于计算平均值的一组参数。

下面指定一列数据，计算数据集合的调和平均值

01 在工作表中输入需要计算的数据。

02 在单元格中输入公式：=HARMEAN(A2:A6)，然后按下“Enter”键确认即可。

5.2　其他统计函数应用

例　统计空白单元格数目

如果需要计算区间内的空白的个数，可通过COUNTBLANK函数实现。COUNTBLANK函数的语法为：=COUNTBLANK(range)。其中Range为需要计算其中空白单元格个数的区域。

下面举例计算COUNTBLANK函数的使用方法。

01 在“A1: A6”单元格区域中输入需要计算的内容。

02 在需要显示结果的单元格中输入公式：=COUNTBLANK(A2:A6)，按下“Enter”键确认。

注意：使用COUNTBLANK函数时，即使单元格中含有返回值为空文本("")的公式，也会计算在内，但包含零值的单元格不计算在内。

例　计算区域中满足给定条件的单元格的个数

如果需要计算区域中满足指定条件的单元格的个数，可通过COUNTIF函数实现。

COUNTIF函数的语法为：=COUNTIF(range,criteria)，各参数的含义介绍如下。

·Range：需要计算其中满足条件的单元格数目的单元格区域。

·Criteria：确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式、单元格引用或文本。

下面以计算“语文”成绩小于90的人数为例，介绍COUNTIF函数的使用方法。

01 打开需要计算的工作表，选中需要显示结果的单元格。

02 输入公式：=COUNTIF(B3:B9,"<90")，然后按下“Enter”键确认即可。

例　使用通配符

在COUNTIF函数中可以使用通配符，即问号 (?) 和星号 (*)。可使得该函数的处理能力大大增强。其中问号匹配任意单个字符，星号匹配任意一系列字符。若要查找实际的问号或星号，则需要在该字符前键入波形符(~)。

下面举例介绍通配符的使用方法。

例1：计算以“王”开头的姓名人数

01 打开需要计算的工作表，选中需要显示结果的单元格。

02 输入公式：=COUNTIF (A3:A9,"王*")，然后按下“Enter”键确认即可。

例2：计算不包含文本的单元格个数

01 在“A1:B7”单元格区域中输入需要计算的内容。

02 在需要显示结果的单元格中输入公式：=COUNTIF (A1:B7,"<>"&"*")，然后按下“Enter”键确认即可。

例　计算数据集的峰值

如果需要计算数据集的峰值，可通过KURT函数实现。KURT函数的语法为：=KURT(number1,number2,...)。其中Number1,number2,...是用于计算峰值的1到255个参数。

提示：峰值反映与正态分布相比某一分布的尖锐度或平坦度。正峰值表示相对尖锐的分布。负峰值表示相对平坦的分布。

下面给定一列数据，需要返回数据集的峰值。

01 在“A2:A6”单元格区域中输入需要计算的内容。

02 在需要显示结果的单元格中输入公式：=KURT(A2: A6)，然后按下“Enter”键确认即可。

例　计算数据集中出现最多的值

如果需要统计某一数组或数据区域中出现频率最多的数值，即所谓的众数，可通过MODE函数实现。MODE函数的语法为：=MODE(number1,number2,...)。其中Number1,number2,...是用于众数计算的2到255个参数。

下面举例说明函数的使用方法。

01 在指定单元格区域中输入需要计算的数据。

02 在需要显示结果的单元格中输入公式：=MODE(A2:A7)，然后按下“Enter”键确认即可。

例　计算数据集中第k个最大值

如果需要查找数据集总第k个最大值，可通过LARGE函数实现。LARGE函数的语法为：=LARGE(array,k)，其中参数Array为需要从中选择第k个最大值的数组或数据区域，参数K为返回值在数组或数据单元格区域中的位置。

下面指定一列数据，返回数据集中第3个最大值的具体操作如下。

01 在“A2:A6”单元格区域中输入需要计算的内容。

02 在需要显示结果的单元格中输入公式：=LARGE(A2:A6,3)，然后按下“Enter”键确认即可。

提示：如果区域中数据点的个数为n，则函数LARGE(array,1)返回最大值，函数LARGE(array,n)返回最小值。

例　返回分布的偏斜度

如果需要返回数据集分布的偏斜度，也叫不对称度，可通过SKEW函数实现。SKEW函数的语法为：=SKEW(number1,number2,...)，其中，Number1,number2...为需要计算偏斜度的1到255个参数。

提示：不对称度反映以平均值为中心的分布的不对称程度。正不对称度表示不对称部分的分布更趋向正值。负不对称度表示不对称部分的分布更趋向负值。

下面指定一列数据，计算数据分布的偏斜度的具体操作如下。

01 在“A2:A6”单元格区域中输入需要计算的数据，本例依次输入1、2、3、4、5。

02 在需要显示结果的单元格中输入公式：=SKEW(A2:A6)，按下“Enter”键确认即可。

例　返回参数列表中的最大值

如果需要返回参数列表中的最大值，可通过MAXA函数实现。MAXA函数的语法为：=MAXA（value1,value2,...）。其中value1,value2,... 为需要从中查找最大数值的1到255个参数。

下面举例介绍函数的使用方法。

01 根据需要在指定单元格区域输入要计算的内容。

02 在需要显示结果的单元格中输入公式：=MAXA(C3:C13)，然后按下“Enter”键确认即可。

例　返回参数列表中的最小值

如果需要返回列表中的最小值，包含数字、文本和逻辑值，可通过MINA函数实现。MINA函数的语法为：=MINA（value1,value2,...）。其中Value1,value2,...为需要从中查找最小值的1到255个数值。

提示：使用MAXA、MEDIAN和MINA函数进行计算时，包含TRUE的参数作为1计算；包含文本或FALSE的参数作为0计算。

下面举例介绍MINA函数的使用方法。

01 根据需要在指定单元格区域中输入需要计算的数据。

02 在需要显示结果的单元格中输入公式：=MINA(C3: C9)，按下“Enter”键确认。

例　返回给定数值集合的中值

如果需要返回指定数值集合中的中值，可通过MEDIAN函数实现。MEDIAN函数的语法为：=MEDIAN（number1,number2,...）。其中number1,number2,...为需要计算中值的1到255个数值。

下面给定一列数据，返回数值集合的中值的具体操作如下。

01 在单元格区域中输入需要计算的数据。

02 在要显示结果的单元格中输入公式：=MEDIAN(A2:A6)，按下“Enter”键确认。

例　计算给定数目对象的排列数

PERMUT函数用于返回从给定数目的对象集合中选取的若干对象的排列数。

PERMUT函数的语法为：=PERMUT (number,number_chosen)，各参数的含义介绍如下。

·Number：表示对象个数的整数。

·number_chosen：表示每个排列中对象个数的整数。

假设每个彩票号码分别有3位数，每个数的范围为0到99之间，包括0和99，计算彩票中奖的可能性。

01 在“A2”单元格中输入对象总数“100”，在“A3”单元格中输入每个排列的对象数“3”。

02 在需要显示结果的单元格中输入公式：=PERMUT (A2, A3)，然后按下“Enter”键确认即可。

例　基于样本估算标准偏差

如果需要估算样本的标准偏差，可通过STDEV函数实现。STDEV函数的语法为：=STDEV(number1,number2,...)。其中Number1,number2,...为对应于总体样本的2到255个参数。

假设有5件工具在制造过程中是由同一台机器制造出来，并取样为随机样本进行抗断强度检验。

01 根据需要在指定单元格区域中输入需要计算的数据。

02 在需要显示结果的单元格中输入公式：=STDEV(A2: A9)，然后按下“Enter”键确认即可。

例　计算基于整个样本总体的标准偏差

如果需要计算以参数形式给出的整个样本总体的标准偏差，可通过STDEVP函数实现。STDEVP函数的语法为：=STDEVP(value1,value2,...)。

假设仅生产了5件工具，计算其抗断强度的标准偏差。

01 在指定单元格区域中输入需要计算的数据。

02 在需要显示结果的单元格中输入公式：=STDEVP (A2: A9)，然后按下“Enter”键确认即可。

例　基于整个样本总体计算方差

如果需要计算基于整个样本总体的方差，可通过VARP函数实现。VARP函数的语法为：=VARP(number1,number2,...)。

假设所有5件工具在制造过程中都是由同一台机器制造出来的，并取样进行抗断强度检验。

01 根据需要在指定单元格区域中输入需要计算的数据。

02 在需要显示结果的单元格中输入公式：=VARP (A2:A9)，然后按下“Enter”键确认即可。

第6章　查找与引用函数应用

6.1　查找函数应用

例　基于索引号返回参数列表中的数值

使用函数CHOOSE可以基于索引号返回最多29个待选数值中的任一数值。CHOOSE函数的语法为：=CHOOSE（index_num, value1,value2,…），其中参数index_num用于指定所选定的值参数，参数value1,value2,…用数值、文本、单元格引用、已定义的名等形式指定数值参数。输入index_num参数时需要注意以下几点。

·如果index_num为1，函数CHOOSE返回value1；如果为2，函数CHOOSE返回value2，以此类推。

·如果index_num小于1或大于列表中最后一个值的序号，函数CHOOSE返回错误值#VALUE!。

·如果index_num为小数，则在使用前将被截尾取整。

假设用1到7表示一个星期的7天，当用1到7之间的数字作索引号时，需要返回其中的某一天。具体操作为：在需要显示结果的单元格中输入公式：=CHOOSE(1,"星期一","星期二","星期三","星期四","星期五","星期六","星期日")，按下“Enter”键确认即可。

例　设置数字格式以隐藏零值

编辑工作表时可能遇到含有大量零值的情况，若觉得影响美观，可将工作表中的所有零值隐藏起来，使用数字格式实现的操作如下。

01 选中包含要隐藏的零值（0）的单元格。

02 在“开始”选项卡中单击“数字”组右下角的展开按钮，打开“设置单元格格式”对话框。

03 在“分类”列表框中选择“自定义”选项，在右侧的“类型”文本框中键入“0;-0;;@”，单击“确定”按钮。

04 在返回的工作表中可看到选中区域中的所有零值即被隐藏了。

例　通过与首行的值对比来查找值

当比较值位于数据表的首行，并且要查找下面给定行中的数据时，可通过HLOOKUP函数实现。HLOOKUP函数的语法为：=HLOOKUP（lookup_value,table_array, row_index_num,range_lookup），各参数的含义介绍如下。

·lookup_value：用数值或数值所在的单元格指定在数组第一行中查找的数值。

·table_array：指定查找范围，即需要在其中查找数据的信息表。如果range_lookup为TRUE，则table_array的第一行的数值必须按升序排列：...-2、-1、0、1、2、…、A-Z、FALSE、TRUE；否则，函数HLOOKUP将不能给出正确的数值。如果range_lookup为FALSE，则table_array不必进行排序。

·row_index_num：为table_array中待返回的匹配值的行号。row_index_num为1时，返回table_array第一行的数值，row_index_num为2时，返回table_array第二行的数值，以此类推。如果row_index_num小于 1，则HLOOKUP返回错误值#VALUE!；如果row_index_num大于table_array的行数，则HLOOKUP返回错误值#REF。

·range_lookup：用TRUE或FALSE指定查找方法。

下面以查询成绩表中姓名为“张明”的学生的“数学”成绩为例，具体操作如下。

01 打开成绩表，选中需要显示结果的单元格。

02 在其中输入公式：=HLOOKUP（"数学",A2:F8,3），然后按下“Enter”键确认，即可得到“张明”的“数学”成绩为“60”。

例　通过与首列值对比来查找值

若需要查找的值与其首列中的值有对应关系，可通过VLOOKUP函数实现。VLOOKUP函数的语法为：=VLOOKUP（lookup_value,table_array,col_index_num,range_lookup）。

·lookup_value：用数值或数值所在的单元格指定在数组第一列中查找的数值。如果为lookup_value参数提供的值小于table_array参数第一列中的最小值，则VLOOKUP将返回错误值#N/A。

·table_array：指定查找范围。

·col_index_num：为table_array中待返回的匹配值的列号。当col_index_num参数为1 时，返回table_array第一列中的值；col_index_num为2时，返回table_array第二列中的值，依此类推。

·range_lookup：一个逻辑值，指定希望VLOOKUP查找精确匹配值还是近似匹配值。如果range_lookup为TRUE或被省略，则返回精确匹配值或近似匹配值。如果找不到精确匹配值，则返回小于lookup_value的最大值。如果range_lookup为TRUE或被省略，则必须按升序排列table_array第一列中的值；否则，VLOOKUP可能无法返回正确的值。

下面举例说明VLOOKUP函数的使用方法：某学校规定学生的综合得分为60分以下为D级，60分（包含60）至80分为C级，80分（包含80）至90分为B级，90分（包含90）以上为A级，现在需要将B列中的得分转换为等级评价。

01 打开工作表，在A列中输入学生姓名，在B列中输入学生的综合得分。

02 选中C2单元格，在其中输入公式：=VLOOKUP(B2,{0,"D";60,"C";80,"B";90,"A"},2)。

03 使用填充柄功能复制公式到该列的其他单元格中，即可得到所有学生的得分等级结果。

例　在一个未排序的区域中查找值

如果需要在一个未排序的区域中查找值，可通过INDEX和MATCH函数实现。

·INDEX函数的含义前面已经介绍了，这里将不再介绍。MATCH函数的语法为：=MATCH(lookup_value, lookup_array, [match_type])，各参数的含义介绍如下。

·lookup_value：需要在lookup_array中查找的值。该参数可以为值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。

·lookup_array：要搜索的单元格区域。

·match_type：指定Excel如何在lookup_array中查找lookup_value的值，该参数的值为-1、0或1。如果省略此参数，则默认值为1。

注意：match_type参数为1或省略，MATCH函数会查找小于或等于lookup_value的最大值；match_type参数为0，MATCH,函数会查找等于lookup_value的第一个值；match_type参数为-1，MATCH,函数会查找大于或等于lookup_value的最小值。

下面以在比赛成绩表中根据姓名查询成绩为例，介绍在未排序的单元格区域中查找某个值的具体操作。

01 打开成绩表，输入姓名和比赛成绩等相关数据。

02 选中“B9”单元格，在其中输入查询条件，即需查询成绩的选手姓名。

03 在单元格中输入公式：=INDEX(A2:B7,MATCH(B9,A2:A7,0),2)，然后按下“Enter”键确认，即可得到该选手的比赛成绩。

例　在未排序且大小不定的区域里查找值

如果需要在一个未排序且大小不定的区域里查找值，可通过OFFSET和MATCH函数实现。OFFSET函数的语法为：=OFFSET(reference,rows,cols,[height],[width])，各参数的含义介绍如下。

·Reference：作为偏移量参照系的引用区域。Reference必须为对单元格或相连单元格区域的引用；否则，OFFSET返回错误值#VALUE!。

·Rows：相对于偏移量参照系的左上角单元格，上（下）偏移的行数，行数可为正数（代表在起始引用的下方）或负数（代表在起始引用的上方）。加入使用5作为参数Rows，则说明目标引用区域的左上角单元格比reference低5行。

·Cols：相对于偏移量参照系的左上角单元格，左（右）偏移的列数，列数可为正数（代表在起始引用的右边）或负数（代表在起始引用的左边）。假如使用5作为参数Cols，则说明目标引用区域的左上角的单元格比reference靠右5列。

·Height：表示高度，即所要返回的引用区域的行数，为可选项。Height必须为正数。

·width：表示宽度，即所要返回的引用区域的列数，为可选项。Width必须为正数。如果行数和列数偏移量超出工作表边缘，函数OFFSET返回错误值#REF!。如果省略height或width，则假设其高度或宽度与reference 相同。

下面以查找成绩表中奖牌为“季军“的选手比赛成绩为例，操作如下。

01 打开比赛成绩表，选中“B11”单元格，在其中输入查询条件，即需查询成绩的奖牌，本例输入“季军”。

02 选中需要显示结果的单元格，在其中输入公式：=OFFSET (A1,MATCH("季军",C2:C7,0),1)，然后按下“Enter”键确认，即可得到奖牌为“季军”的选手成绩。

例　计算某个值重复出现的次数

如果需要计算某个值或字符重复出现的次数，可通过COUNTIF函数实现。COUNTIF函数的语法为：=COUNTIF(range,criteria)，各参数的含义介绍如下。

·Range：要对其进行计数的一个或多个单元格，其中包括数字或名称、数组或包含数字的引用。空值和文本值将被忽略。

·Criteria：用于定义将对哪些单元格进行计数的数字、表达式、单元格引用或文本字符串。

下面以计算成绩表中“92”分出现的次数为例，举例说明函数的使用方法。

01 打开比赛成绩表，输入选手姓名、比赛成绩等相关数据。

02 选中需要显示结果的单元格，在其中输入公式：=COUNTIF(B2:B7, "92")，然后按下“Enter”键确认，即可得到成绩“92”分的出现次数。

例　计算多个值重复出现的次数

如果需要计算多个值重复出现的次数，可通过IF函数和SUM函数实现。下面举例说明。

例1：计算表格中记录的甲和丙的发票次数

01 在“A2:B7”单元格区域中输入需要的相关数据。

02 在单元格中输入公式：=SUM(IF ((A2:A7="甲")+(A2:A7="丙"),1,0))，按下“Ctrl+Shift+Enter”组合键确认。

例2：计算面额大于9000且小于19000的发票数

01 在“A2:B7”单元格区域中输入需要的相关数据。

02 在需要显示结果的单元格中输入公式：=SUM(IF ((B2:B7<9000)+(B2:B7>19000),1,0))，然后按下“Ctrl+Shift+Enter”组合键确认即可。

例　提取表格中的姓名

如果需要提取表格中的姓名，可使用INDEX函数实现。假设学生资料表中奇数行用来存放学号，偶数行用来存放姓名，现在需要单独提取学生姓名，具体操作如下。

01 打开学生资料表，在奇数行输入学生的学号，偶数行输入学生的姓名。

02 选中要显示学生姓名的单元格，在其中输入公式：=INDEX(B:B,ROW()*2)&""，按下“Enter”键确认。

03 使用填充柄功能将公式复制到该列中的其他单元格中，即可提取学生资料表中的所有学生姓名。

例　计算60分到90分之间的人数

如果需要计算某个得分区间内的人员个数，也可以使用INDEX函数实现。本例以计算外语成绩60分到90分之间的学生人数为例，计算时还需要使用FREQUENCY函数，该函数的语法为：=FREQUENCY(data_array, bins_array)，各参数的含义介绍如下。

·data_array：一个值数组或对一组数值的引用，要为它计算频率。如果data_array中不包含任何数值，函数FREQUENCY将返回一个零数组。

·bins_array：一个区间数组或对区间的引用，该区间用于对data_array中的数值进行分组。如果bins_array中不包含任何数值，函数FREQUENCY返回的值与data_array中的元素个数相等。

提示：使用FREQUENCY函数时，返回的数组中的元素个数比bins_array中的元素个数多1个。多出来的元素表示最高区间之上的数值个数。例如为3个单元格中输入的3个数值区间计数，需要在四个单元格中输入函数获得计算结果。多出来的单元格将返回data_array中第3个区间值以上的数值个数。

01 打开工作表，在其中输入学生的各科成绩。

02 选中需要显示结果的单元格，输入公式：=INDEX(FREQUENCY(C2:C7,{60,90}),2)，然后按下“Enter”键，即可得到此工作表中外语成绩在60到90分之间的人数结果。

提示：本例首先利用FREQUENCY函数计算60分以下的人数、60分到90分之间的人数和90分以上的人数，组成一个纵向的一维数组，然后利用INDEX函数从内存数组中提取第2行的数据，即得分在60分到90分之间的人数。

例　根据姓名查找身份证号

如果需要从向量中查找一个值，可使用LOOKUP函数。此时在单行区域或单列区域（称为“向量”）中查找值，然后返回第二个单行区域或单列区域中相同位置的值。

·LOOKUP函数的语法为：=LOOKUP（lookup_value,lookup_vector,result_vector），各参数的含义介绍如下。

·lookup_value：函数在第一个向量中搜索的值。

·lookup_vector：指定检查范围，只包含一行或一列的区域。lookup_vector 中的值必须以升序排列：...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE。否则，函数可能无法返回正确的值。而大写的文本和小写文本是等同的。

·result_vector：指定函数返回值的单元格区域，只包含一行或一列的区域。下面以根据姓名查找身份证号码为例，介绍LOOKUP函数的使用方法。

01 打开工作表，在其中输入姓名、身份证号等数据。

02 在“A11”单元格中输入要查询其身份证号的员工姓名，作为查询条件。

03 在单元格中输入公式：=LOOKUP (A11, A2:A8,B2:B8)，按下“Enter”键确认，即可得到A11单元格中员工姓名对应的身份证号了。

例　将字母转换为评分

假设某比赛规定评委评分是使用A、B、C、D和E这5个标准。现在需要将评分字母转换为得分，其中A为10分，B为9分，C为8分，D为7分以及E为6分。要计算选手的平均分，也可使用LOOKUP函数实现，具体操作如下。

01 打开工作表，在其中输入姓名、评委评分等数据。

02 在需要显示评分平均分的单元格中输入公式：=AVERAGE(LOOKUP(B2:D2,{"A","B","C","D","E"},{10,9,8,7,6}))，然后按下“Ctrl+Shift+Enter”组合键，即可得到该选手的评分平均分。

03 选中该单元格，打开“设置单元格格式”对话框，在“分类”列表框中选择“数值”选项，接着将右侧的小数位数设为“2”，单击“确定”按钮。

04 使用填充柄功能将显示计算结果的单元格中的公式和格式复制到该列中的其他单元格中，即可得到所有选手的评分平均分了。

提示：本例首先建立两个对应的数组，一个为字母评价，一个为对应的数值评分，使用LOOKUP函数在第一个数值中查找选手的所有字母评分，并返回其对应的得分，然后使用AVERAGE函数计算平均分。此外，本例的函数也可只使用两个参数，公式为：=AVERAGE(LOOKUP(B2:D2,{"A",10;"B",9;"C",8;"D",7;"E", 6}))。

6.2　引用函数应用

例　返回由文本字符串指定的引用

如果需要按照给定的行号合列标建立文本类型的单元格地址，可通过ADDRESS函数实现。

ADDRESS函数的语法为：=ADDRESS(row_num, column_num,abs_num,a1,sheet_text)，其中各参数的含义介绍如下。

·row_num：在单元格引用中使用的行号。

·column_num：在单元格引用中使用的列号。

·abs_num：指定返回的单元格引用类型，为1或省略时返回绝对引用；为2时返回绝对行号，相对列标；为3时返回相对行号，绝对列标；为4时返回相对引用。

·a1：用以指定A1或R1C1引用格式的逻辑值。若为TRUE或省略，返回A1样式的引用；若为FALSE，返回R1C1样式的引用。

·sheet_text：为一文本，用于指定作为外部引用的工作表的名称。

下面举例说明函数的是否方法。

例1：返回“C2”单元格的绝对引用

具体操作：在需要显示结果的单元格中输入公式：=ADDRESS(2,3)，然后按下“Enter”键确认即可。

例2：在R1C1引用样式中的绝对行号，相对列标

具体操作：在需要显示结果的单元格中输入公式：=ADDRESS(2,3,2,FALSE)，然后按下“Enter”键确认即可。

例　查询抽查的产品编号地址

质检部门对市场或超市的产品进行质量抽查时，这个过程是随机的。

下面利用公式根据抽查产品的编号来统计该产品编号所在的单元格位置，具体操作如下。

01 打开工作表，输入一批产品的编号，并从中挑选出几个要抽查的产品编号。

02 选中“C3”单元格，在其中输入公式：=ADDRESS (3,1)，按下“Enter”键确认，即可显示产品编号“B-T121”的单元格地址为“$A$5”。

03 按照上一步操作，在“C4”和“C5”单元格中输入计算地址的公式来确定其他抽查产品编号的地址，结果如图所示。

例　计算引用中包含的区域个数

如果需要知道一个公式引用中包含的单元格区域或单元格的个数，可通过AREAS函数实现。

AREAS函数的语法为：=AREAS(reference)，其中参数reference为对某个单元格或单元格区域的引用，也可以引用多个区域。

注意：如果需要将几个引用指定为一个参数，则多个引用间需要用逗号隔开，而且必须用括号“（）”括起来，以免Excel将逗号视为字段分隔符。

假设某个销售公司在市内多个区都有分销处，现在将几个分销处合并为一个区域分布，要求统计区域的总数。

01 打开工作表，在每个单元格区域中输入区域名、负责人和员工人数等信息。

02 在需要显示统计区域个数的单元格中输入公式：=AREAS ((A4:B6, D4:E6,A8:B10,D8:E10))，然后按下“Enter”键确认即可。

例　返回给定引用的列标

如果需要返回给定引用的列标，可通过COLUMN函数实现。COLUMN函数的语法为：=COLUMN（reference），其中reference参数为需要得到其列标的单元格或单元格区域。

需要注意的是，若省略参数或该参数为一个单元格区域，且函数以水平数组公式形式输入，则函数将以水平数组形式返回参数的列号；若参数为一个单元格区域，并且函数不是以水平数组公式的形式输入的，则函数将返回最左侧列的列号；若省略参数，则假定该参数为对COLUMN函数所在单元格的引用。

下面举例说明函数的使用方法：Excel工作表的列表默认显示为“A”、“B”、“C”等，若超过26列则以两个字母表示，如“AB”、“IV”等，若超过702列则以3位字符表示。现在需要查询单元格中的字母对应的是第几列。

01 在“A9”单元格中输入公式：=COLUMN(INDIRECT (A9&1))。

02 按下“Enter”键确认，公式返回指定列表为工作表中的第几列。

提示：本例首先利用INDIRECT函数将单元格中的字母连接数字1，将其转换为单元格引用，即将字符串“AB1”转换为引用单元格“AB1”，然后再利用COLUMN函数技术单元格“AB1”位于工作表中的第几列。

例　返回指定行列交叉处引用的单元格

如果需要返回表或区域中的值或值的引用，可通过INDEX函数实现。INDEX函数的语法为：=INDEX(array,row_num,column_num)，各参数的含义介绍如下。

·Array：指定数组。

·row_num：数组中某行的行序号。

·column_num：数组中某列的列序号。

下面举例说明如何返回指定行列交叉处引用的单元格。

例1：返回名为“平原”的选手的比赛成绩

01 在“A1:C8”单元格区域中，根据需要输入相关数据。

02 在需要显示结果的单元格中输入公式：=INDEX(A1:C8,4,2)，然后按下“Enter”键确认即可。

例2：返回数组常量中第二列、第一行的值

01 在“A1:C8”单元格区域中，根据需要输入相关数据。

02 在需要显示结果的单元格中输入公式：=INDEX({1,2;3,4}, 0,2)，然后按下“Enter”键确认即可。

例　多区域计数

假设工作表中有2个班级的成绩，现在需要统计成绩不及格的人数，具体操作如下。

01 打开工作表，在其中输入2个班级的学生成绩。

02 选中需要统计不及格人数的单元格，在其中输入公式：=SUM(COUNTIF(INDIRECT({"C2:C9","F2:F9"}),"<60"))，然后按下“Enter”键确认，即可得到结果。

例　计算多个工作表中成绩的最大平均值

假设3个班的成绩分别置于同一个工作簿的3个工作表中，现在需要计算3个班的最高平均成绩是多少。

01 打开工作簿，分别在3个工作表中输入3个班的成绩，其中单元格区域须一致，如A2:A6为学生姓名，B2:B6为成绩。

02 在单元格中输入公式：=MAX (SUBTOTAL(1, INDIRECT({"1","2","3"}&"班!B2:B6")))，按下“Enter”键确认，得到3个班中的最高平均成绩。

提示：本例先利用常量数组分别表示3个工作表的数据区，接着利用INDIRECT函数将字符串转换为区域引用，而AVERAGE函数无法跨表计算平均值，故使用SUBTOTAL函数计算平均值，最后则使用MAX函数求取最大值。

例　返回引用的行号

如果需要返回引用的行号，可通过ROW函数实现。ROW函数的语法为：=ROW(reference)，其中参数reference为指定需要得到其行号的单元格或单元格引用。

注意：如果省略reference，则假定是对函数ROW所在单元格的引用。如果reference为一个单元格区域，并且函数ROW作为垂直数组输入，则函数ROW将以垂直数组的形式返回reference的行号。

下面举例说明ROW函数的使用方法。

·返回公式所在行的行号。具体操作为：在需要显示结果的单元格中输入公式：=ROW()，然后按下“Enter”键确认即可。

·返回引用中第一行、第二行、第三行的行号。具体操作为：选中需要显示结果的单元格区域，在其中输入公式：=ROW (B2:D5)，然后按下“Ctrl+Shift+Enter”组合键确认即可。

例　产生每两行空一行后累加1的编号

假设需要在A列产生自然数序列编号，从1开始，且每个两行空一行，即让每个组的成员之间产生一个空行，以便查看。

01 打开工作表，选中“A1”单元格，在其中输入公式：=IF(ROW()=1,1,IF(MOD(ROW(),3),COUNT(OFFSET(A$1,,,ROW()-1))+1,""))。

02 按下“Enter”键确认，公式将返回1。

03 用填充柄功能填充，在A列每两行空一行产生递加1的序列。

04 在编号右侧输入成员姓名即可。

在本例中，需要对当前单元格前面的区域进行计数，而A1单元格前不存在单元格，OFFSET函数则会产生错误引用。此时利用IF函数配合ROW函数避开此问题，直接用IF函数为公式赋值，而不会计算OFFSET函数的引用了。

本例使用ROW函数判断当前行的行号，若等于1 则返回1，对于其他行，当行号为3的整数倍数时，返回空白，相邻的下一行则从A1开始至当前行的上一行结束的区域中的数字个数加1。

提示：本例还可以采用公式：=IF(ROW()=1,1,IF(MOD (ROW(),3),MAX(A$1:A1)+1,""))，此时公式直接对前一个非空白单元格递加1来完成。

例　检查每个志愿是否与前面的重复

假设每个考生需要填写9个志愿，且不能重复，现在需要检查区域中的志愿是否和前面的重复。

01 打开工作表，填写好志愿和编号。选中需要显示检测结果的单元格，在其中输入公式：=MATCH(C2,$C$2:$C$10,)<>ROWS($2:2)。

02 按下“Enter”键确认，公式将返回对第一个志愿是否与前面的志愿相重复进行判断的逻辑值。

03 使用填充柄功能将公式向下填充，即可得到所有填写的志愿是否与前面的志愿重复的逻辑结果。其中“FALSE”表示无重复，“TRUE”表示有重复。

提示：本例中的ROWS函数的参数采用混合引用，引用区域随着公式的填充变化，从而使引用区域的行数逐个累加1，再用MATCH函数计算每个志愿的排位，并用排位与第2行开始、当前行前面的区域行数进行比较，故使用“<>”判断是否与前面的志愿重复。

例　返回数组或引用的行数

如果需要返回引用或数组的行数，可通过ROWS函数实现。ROWS函数的语法为：=ROWS（array），其中array参数为指定需要得到其行数的数组、数组公式或对单元格区域的引用。

假设需要返回“A2:H5”单元格区域的行数，具体操作为：在需要显示结果的单元格中输入公式：=ROWS(A2:H5)，然后按下“Enter”键确认即可。

例　将行转置成列

如果需要返回转置单元格区域，即将一行单元格区域转置成一列单元格区域，可通过TRANSPOSE函数实现。TRANSPOSE函数的语法为：=TRANSPOSE（array），其中array参数为指定需要转置的单元格区域或数组。

下面举例说明函数的使用方法。

例1：将A列中的星期数转换为一行显示

01 在“A2:A8”单元格区域中输入星期数。

02 选中需要显示转换结果的区域，如“B1:H1”。

03 在编辑栏中输入公式：=TRANSPOSE($A$2:$A$8)，然后按下“Ctrl+Shift+Enter”组合键确认即可。

例2：将第1行中的星期数转换为一列显示

01 在“B1:H1”单元格区域中输入星期数。

02 选中需要显示转换结果的区域，如“A2:A8”。

03 在编辑栏中输入公式：=TRANSPOSE($B$1:$H$1)，然后按下“Ctrl+Shift+Enter”组合键确认。

第7章　信息与工程函数应用

7.1　返回相应信息函数

例　返回数值的类型

当某一个函数的计算结果取决于特定单元格中数值的类型时，可使用TYPE函数。

TYPE函数的语法为：=TYPE（value），其中value参数可以为任意Excel数值，具体介绍如下。

·参数的数据类型为数值时，返回值为1。

·参数的数据类型为文本时，返回值为2。

·参数的数据类型为逻辑值时，返回值为4。

·参数的数据类型为错误值时，返回值为16。

·参数的数据类型为数组时，返回值为64。

下面举例说明TYPE函数的用法。

1．返回“Mr.Smith”的数据类型

01 在“A2”单元格中输入“Mr.Smith”。

02 在需要显示结果的单元格中输入公式：=TYPE (A2)，然后按下“Enter”键确认即可。

2．返回数组常量的数据类型

具体操作：在需要显示结果的单元格中输入公式：=TYPE({1,2,3,4})，然后按下“Enter”键确认即可。

例　判断公式的错误类型

如果一个单元格中的公式发生了错误，可通过ERROR.TYPE函数判断其错误类型。

ERROR.TYPE函数的语法为：=ERROR.TYPE（error_val），其中Error_val参数为需要得到其标号的一个错误值，主要有以下几种情况。

·参数值为“#NULL!”，函数返回“1”。

·参数值为“#DIV/0!”，函数返回“2”。

·参数值为“#VALUE!”，函数返回“3”。

·参数值为“#REF!”，函数返回“4”。

·参数值为“#NAME?”，函数返回“5”。

·参数值为“#NUM!”，函数返回“6”。

·参数值为“#N/A”，函数返回“7”。

·参数值为其他值，函数返回错误值“#N/A”。

下面举例说明函数的使用方法：判断A2单元格是否包含#NULL!或#DIV/0!错误值，若包含错误值，则显示相应的提示信息；若不包含错误值，则返回#N/A错误值。

1．单元格包含错误值

01 在“A2:A3”单元格中输入需要计算的数据，本例输入1和0。

02 在“B2”单元格中输入公式：=A2/A3，然后按下“Enter”键确认。

03 在需要显示判断结果的单元格中输入公式：=IF(ERROR.TYPE(B2)<3,CHOOSE(ERROR.TYPE(B2),"区域没有交叉","除数为零"))，按下“Enter”键确认即可。

2．单元格不包含错误值

01 在“A2:A3”单元格中输入需要计算的数据，本例输入6和2。

02 在“B2”单元格中输入公式：=A2/A3，然后按下“Enter”键确认。

03 在需要显示判断结果的单元格中输入公式：=IF(ERROR.TYPE(B2)<3,CHOOSE(ERROR.TYPE(B2),"区域没有交叉","除数为零"))，按下“Enter”键确认即可。

例　返回错误值#N/A

如果需要直接返回错误值，可通过NA函数实现。NA函数的语法为：=NA（）。此函数将直接返回错误值#N/A。NA函数中没有参数，但必须有括号，若在括号中输入了参数，将返回错误信息。

以在单元格中强制产生#N/A错误值为例，具体操作为：在需要输入#N/A错误值的单元格中输入公式：=NA（），然后按下“Enter”键确认即可。

例　返回一个字符串的格式代码

为了不让他如果想知道一个单元格内字符串的格式代码，可通过CELL函数实现。CELL函数的语法为：=CELL(info_type,reference)，各参数的含义介绍如下。

·“reference”参数表示要获取其有关信息的单元格。如果忽略该参数，则在info_type中所指定的信息将返回给最后更改的单元格。

·“info_type”参数一个文本值，指定所需要的单元格信息的类型，该参数的可能值及结果如下。

·值为address时，返回引用中第一个单元格的引用，文本类型。

·值为col时，返回引用中单元格的列标。

·值为color时，若单元格中的负值以不同颜色显示，则为1，否则返回0。

·值为contents时，返回引用中左上角单元格的值。

·值为filename时，返回包含引用的文件名（包括全部路径），为文本类型。若包含目标引用的工作表尚未保存，则返回空文本 ("")。

·值为format时，返回与单元格中不同的数字格式相对应的文本值。如单元格中负值以不同颜色显示，则在返回的文本值的结尾处加“-”；如果单元格中为正值或所有单元格均加括号，则在文本值的结尾处返回“()”。

·值为parentheses时，若单元格中为正值或全部单元格均加括号，则为1，否则返回0。

·值为prefix时，返回与单元格中不同的“标志前缀”相对应的文本值。若单元格文本左对齐，返回单引号 (')；若单元格文本右对齐，返回双引号 (")；若单元格文本居中，返回插入字符 (^)；若单元格文本两端对齐，返回反斜线 (\)；如果是其他情况，则返回空文本 ("")。

·值为protect时，如果单元格没有锁定，则为0；如果单元格锁定，则为1。

·值为row时，返回引用中单元格的行号。

·值为type时，返回与单元格中的数据类型相对应的文本值。若单元格为空，返回“b”；若包含文本常量，则返回“l”；如果单元格包含其他内容，则返回“v”。

·值为width时，返回取整后的单元格的列宽。

当参数info_type的值为format，且参数reference为用内置数字格式设置的单元格时，函数CELL返回的文本值如下表所示。

以返回一个时间值的文本值为例，具体操作如下。

01 在“A2”单元格中输入数据，如时间值“5:30 PM”。

02 输入公式：=CELL ("format",A2)，然后按下“Enter”键确认即可。

例　返回当前操作环境的信息

如果希望知道当前使用的Excel版本或操作系统的种类信息，可通过INFO函数实现。

INFO函数的语法为：=INFO(type-text)，其中参数type-text为用加引号的半角文本指定要返回的信息类型，该参数的值及函数的返回值介绍如下。

·参数值为“directory”：返回目录或文件夹的路径。

·参数值为“memavail”：以字节为单位返回可用的内存空间。

·参数值为“memused”：返回数据占用的内存空间。

·参数值为“numfile”： 返回打开的工作簿中活动工作表的个数。

·参数值为“origin”： 用A1样式的绝对引用，返回窗口中可见的最右上角的单元格。

·参数值为“osversion”： 返回当前操作系统的版本号。

·参数值为“recalc”： 用“自动”或“手动”文本表示当前的重新计算方式。

·参数值为“release”：返回表示Excel的版本号。

·参数值为“system”：返回操作系统的名称，其中“mac”表示Macintosh版本，“pcdos”表示Windows版本。

·参数值为“totmem”：以字节为单位返回全部内存空间，包括已经占用的内存空间。

下面举例说明INFO函数的用法。

1．返回当前打开工作簿中活动工作表的个数

01 在A2单元格中输入文本“numfile”。

02 在需要显示结果的单元格中输入公式：=INFO(A2)，按下“Enter”键确认即可。

2．返回当前操作系统的版本号

01 在“A3”单元格中输入文本“osversion”。

02 在需要显示结果的单元格中输入公式：=INFO(A3)，然后按下“Enter”键确认即可。

7.2　数据检查与转换

例　检测一个值是否为文本

如果需要检测一个值是否为文本，可通过ISTEXT函数实现。ISTEXT函数的语法为：=ISTEXT(value)，其中value参数为指定用于检测是否为文本的数据。若测试对象为文本，将返回逻辑值TRUE，否则返回FALSE。

下面举例说明ISTEXT函数的使用方法。

01 在“A2”单元格中输入需要测试的数据值，本例输入数据“信息函数”。

02 在需要显示结果的单元格中输入公式：=ISTEXT(A2)，然后按下“Enter”键确认即可。

例　判断测试对象是否为数值

如果需要判断测试对象是否为数值，可通过ISNUMBER函数实现。ISNUMBER函数的语法为：=ISNUMBER(value)，其中value参数为需要进行检验的数值。若测试对象为数值，将返回逻辑值TRUE，否则返回FALSE。

下面举例说明ISNUMBER函数的使用方法。

01 在“A2”单元格中输入需要测试的数据值，本例输入数字“128”。

02 在需要显示结果的单元格中输入公式：=ISNUMBER(A2)，然后按下“Enter”键确认即可。

例　判断数值是否为奇数

如果需要判断单元格中的数值是否为奇数，可通过ISODD函数实现。

ISODD函数的语法为：=ISODD(number)，其中number参数为指定用于检测是否为奇数的数据。若测试对象为奇数，将返回逻辑值TRUE，否则返回FALSE。

下面举例说明ISODD函数的使用方法。

01 在“A2”单元格中输入需要测试的数据值，本例输入数字“128”。

02 在单元格中输入公式：=ISODD(A2)，然后按下“Enter”键确认即可。

例　判断测试对象是否为逻辑值

如果需要判断测试对象是否为逻辑值，可使用ISLOGICAL函数。ISLOGICAL 函数的语法为：=ISLOGICAL（value），其中value参数为需要进行检验的数值。若测试对象为逻辑值，将返回逻辑值TRUE，否则返回FALSE。

以判断“A2”单元格中的值是否为逻辑值为例，具体操作如下。

01 在“A2”单元格中输入需要测试的数据值。

02 在需要显示结果的单元格中输入公式：=ISLOGICAL(A2)，然后按下“Enter”键确认即可。

例　判断测试对象是否为空单元格

如果需要判断测试对象是否为空单元格，可使用ISBLANK函数实现。ISBLANK函数的语法为：=ISBLANK（value），其中value参数为需要进行检验的数值。若测试对象为空单元格，将返回逻辑值TRUE，否则返回FALSE。

如果不知道用哪个函数进行处理，还可以通过“插入函数”对话框来选择，在其中可看到所选函数的功能。下面以判断“A2”单元格是否为空单元格为例，具体操作如下。

01 选中需要输入公式的单元格。

02 切换到“公式”选项卡，单击“函数库”选项组中的“插入函数”按钮，弹出“插入函数”对话框。

03 在“类别”下拉列表框中选择“信息”函数类型，在下方的列表框中选择ISBLANK函数，单击“确定”按钮。

04 弹出“函数参数”对话框，输入需要检查的单元格，然后单击“确定”按钮即可。

例　检测指定单元格是否为错误值

如果需要检测单元格中的值是否为错误值，可通过ISERROR函数实现。

ISERROR函数的语法为：=ISERROR(value)，其中value参数为指定用于检测是否为错误值的数据。若检测参数为错误值，将返回逻辑值TRUE，否则返回FALSE。

下面举例说明ISERROR函数的使用方法。

01 在“A2”单元格中输入需要测试的数据，本例输入值“#N/A”。

02 在需要显示结果的单元格中输入公式：=ISERROR(A2)，然后按下“Enter”键确认即可。

例　判断测试对象是否为#N/A错误值

如果需要检测单元格中的值是否为#N/A错误值，可通过ISNA函数实现。

ISNA函数的语法为：=ISNA(value)，其中value参数为需要进行检验的数值。若检测参数为#N/A错误值，将返回逻辑值TRUE，否则返回FALSE。

下面举例说明ISNA函数的使用方法。

01 在“A2”单元格中输入需要测试的数据，本例输入值“#N/A”。

02 在需要显示结果的单元格中输入公式：=ISNA(A2)，然后按下“Enter”键确认即可。

例　判断对象是否为除#N/A外的错误值

如果需要检测一个值是否为除#N/A以外的错误值，可通过ISERR函数实现。

ISERR函数的语法为：=ISERR(value)，其中value参数为需要进行检验的数值。若检测参数为除#N/A以外的错误值，将返回逻辑值TRUE，否则返回FALSE。

下面举例说明ISERR函数的使用方法。

01 在“A2”单元格中输入需要测试的数据，本例输入值“#NUM！”。

02 在需要显示结果的单元格中输入公式：=ISERR(A2)，然后按下“Enter”键确认即可。

例　将参数中指定的值转换为数值形式

如果需要将参数中指定的不是数值形式的值转换为数值形式，可通过N函数实现。N函数的语法为：=N(value)，其中value参数为指定转换为数值的值。

使用N函数将不同类型的数据转换后的返回值如下。

·数据类型为数字，返回值将为数字。

·数据类型为日期，返回值将为该日期的序列号。

·数据类型为逻辑值TRUE，返回值将为1。

·数据类型为逻辑值FALSE,返回值将为0。

·数据类型为错误值，返回值也为错误值。

·数据类型为文本，返回值将为0。

下面以将单元格中的文本数据转换为数值形式为例，操作如下。

01 在“A2”单元格中输入一个文本数据。

02 在需要显示结果的单元格中输入公式：=N(A2)，然后按下“Enter”键确认，即可得到返回值“0”。

7.3　数据的换算

例　换算数值的单位

如果需要将某个数值的单位转换为同类的其他单位，可通过CONVERT函数实现。CONVERT函数的语法为：=CONVERT(number，from_nuit，to_unit)，各参数的含义介绍如下。

·Number：以from_nuit为单位的需要进行转换的数值或数值所在的单元格。

·from_nuit：数值Number的单位，指定显示在表中的单位符号，并区分大小写。

·to_unit：转换后的单位。

下面举例介绍CONVERT函数的用法。

1．将英寸转换为厘米

01 在单元格中输入要转换的数据。

02 在需要显示转换结果的单元格中输入公式：=CONVERT（B3, "in","cm"），按下“Enter”键确认。

2．将千克转换为镑

01 在单元格中输入需要转换的数据。

02 在单元格中输入公式：=CONVERT(B9,"kg", "lbm")，按下“Enter”键确认。

例　将二进制数换算为十进制数

如果需要将二进制转换为十进制，可通过BIN2DEC函数来实现。BIN2DEC函数的语法为：=BIN2DEC(number)，其中参数number为待转换的二进制数。

下面举例说明BIN2DEC函数的使用方法。

01 在单元格中输入需要转换的二进制数，本例输入“00001010”。

02 在需要显示转换结果的单元格中输入公式：=BIN2OCT(A2)，然后按下“Enter”键确认即可。

例　将二进制换算为十六进制数

如果需要将二进制转换为十六进制，可通过BIN2HEX函数来实现。BIN2HEX函数的语法为：=BIN2HEX(number,places)，各参数的含义介绍如下。

·Number：待转换的二进制数。

·Places：指定1-10之间的换算后的位数。

下面以将二进制数转换为4个字符的十六进制数为例，具体操作如下。

01 在单元格中输入需要转换的二进制数，本例输入“00001010”。

02 在需要显示转换结果的单元格中输入公式：=BIN2HEX(A2,4)，然后按下“Enter”键确认即可。

例　将十进制数换算为二进制数

如果需要将二进制转换为十六进制，可通过BIN2HEX函数

如果需要将十进制数转换为八进制数，可通过DEC2BIN函数来实现。DEC2BIN函数的语法为：=DEC2BIN(number,places)，各参数的含义介绍如下。

·Number：待转换的十进制数。

·Places：要使用的字符数。如果省略，函数DEC2BIN用能表示此数的最少字符来表示。

下面举例说明DEC2BIN函数的使用方法。

1．将十进制数55转换为二进制数为例

01 在单元格中输入需要转换的十进制数，本例输入“55”。

02 在需要显示转换结果的单元格中输入公式：=DEC2BIN(A2)，然后按下“Enter”键确认即可。

2．将十进制数55转换为8个字符的二进制数

01 在单元格中输入需要转换的十进制数，如输入“55”。

02 在单元格中输入公式：=DEC2BIN(A2,8)，然后按下“Enter”键确认即可。

例　将十进制数换算为十六进制数

如果需要将十进制数转换为八进制数，可通过DEC2HEX函数实现。

DEC2HEX函数的语法为：=DEC2HEX(number,places)，各参数含义介绍如下。

·Number：待转换的十进制数

·Places：要使用的字符数。如果省略，函数DEC2HEX用能表示此数的最少字符来表示。

下面以将十进制数20转换为3个字符的八进制数为例，具体操作如下。

01 在单元格中输入需要转换的十进制数，本例输入“55”。

02 在需要显示转换结果的单元格中输入公式：=DEC2HEX(A2,3)，然后按下“Enter”键确认即可。

例　将十六进制数换算为二进制数

如果需要将十六进制数转换为二进制数，可通过HEX2BIN函数实现。HEX2OCT函数的语法为：=HEX2BIN(number，places)。下面举例说明HEX2BIN函数的使用方法。

1．将十六进制数1F转换为二进制数

01 在单元格中输入需要转换的十六进制数，本例输入“1F”。

02 在需要显示转换结果的单元格中输入公式：=HEX2BIN(A2)，然后按下“Enter”键确认即可。

2．将十六进制数1F转换为8个字符的二进制数

01 在单元格中输入需要转换的十六进制数，本例输入“1F”。

02 在需要显示转换结果的单元格中输入公式：=HEX2BIN(A2,8)，然后按下“Enter”键确认即可。

例　将十六进制数换算为十进制数

如果需要将十六进制数转换为十进制数，可通过HEX2DEC函数实现。HEX2OCT函数的语法为：=HEX2DEC(number，places)。下面举例说明HEX2DEC函数的使用方法。

01 在单元格中输入需要转换的十六进制数，本例输入“1F”。

02 在需要显示转换结果的单元格中输入公式：=HEX2DEC(A2)，然后按下“Enter”键确认即可。

例　测试两个数值是否相等

如果需要测试两个值是否相等，可通过DELTA函数实现。DELTA函数的语法为：=DELTA(number1,number2)。其中参数number1为要测试的第一个数值，参数number2为要测试的第二个数值。

提示：使用DELTA函数时，如果number1=number2，则返回1，否则返回0。如果参数number2省略，则默认该值为0。

下面以测试A2和B2单元格中的数值是否相等为例，具体操作如下。

01 分别在A2和B2单元格中输入要测试的数值。

02 在需要显示结果的单元格中输入公式：=DELTA(A2,B2)，按下“Enter”键确认。