3.3 常用的公式与函数

公式和函数是电子表格的重要元素，是常用来对数据进行处理的工具。本章主要介绍了公式的基本定义、公式中的运算符，以及一些常用函数，如SUM函数、IF函数、VLOOKUP函数、FIND函数、财务函数等。通过了解掌握公式和函数的运算方法/原理，用户在处理数据时会更加快捷，从而提高工作效率。

3.3.1 认识公式与函数

在单元格内输入且可自动进行运算的算式称为公式，简单来说，是以“=”开头，按照一定规则进行数据计算的运算等式，如图3-122所示，E2单元格显示的是计算总成绩的公式。

 图3-122

函数是更高级的公式，它是依照系统提前设定好的某种规则，解决复杂繁琐的计算。一个完整的函数包括函数名称、括号、参数。函数的参数可以是数字、文本、逻辑值等，也可以是公式或其他函数。与自行设计的公式相比，函数能够简化公式，大大提高了运算的效率,如图3-123所示。

WPS表格为用户提供了多种类型的函数，在实际使用时，可以根据需要进行选择。

 图3-123

1. 公式中的运算符

运算符是构成公式的基本元素之一，不同的运算符代表了不同的运算方式。运算符包括四种不同的类型：算数运算符、比较运算符、文本运算符和引用运算符。如下表所示。  

比较运算符
符号	含义	示例
=	等于	若B2单元格的数值为5，B5单元格的数值为5，则=B2=B5=TRUE
>	大于	若B2单元格的数值为5，B5单元格的数值为5，则=B2>B5=FALSE
<	小于	若B3单元格的数值为5，B6单元格的数值为8，则=B3<B6=TRUE
>=	大于等于	若B4单元格的数值为7，B7单元格的数值为5，则=B4>=B7=TRUE
<=	小于等于	若B4单元格的数值为7，B7单元格的数值为5，则=B4<=B7=FALSE
<>	不等于	若B3单元格的数值为5，B6单元格的数值为8，则=B3<>B6=TRUE

 

算数运算符
符号	含义	示例
+	加	=3+5=8
-	减	=10-7=3
*	乘	=5*6=30
/	除	=9/3=3
%	百分比	=50*60%=30

 

文本运算符
符号	作用	示例
&	连字符：连接两个文本	="语文"&"数学"=语文数学

 

引用运算符
符号	作用	示例
:	冒号：区域运算符，在两个单元格之间，表示引用的区域	A1:A5 引用从A1到A5之间的所有单元格
,	逗号：联合运算符，将多个引用合并为一个引用	A1:A5，B1:B5 引用A1到A5与B1到B5两个单元格区域的所有单元格
	空格：交叉运算符，引用属于两个单元格区域之间相交的单元格	A1:A5 A2:E2 引用A1到A5与A2到E2两个单元格区域相交的A2单元格

2. 公式的使用

（1）输入公式

以计算某班学生的总成绩为例，具体操作方法如下：

单击G3单元格，首先输入“=”，然后依次单击D3、E3、F3单元格，并使用“+”连接，此时在G3单元显示计算总成绩的完整公式为“=D3+E3+F3”，如图3-124所示，输入完成后，按“Enter”键，单元格内显示出该学生的总成绩，如图3-125所示。

 图3-124

 图3-125

（2）修改公式

当输入的公式出错需要重新修改时，有以下几种方法：

方法一：双击公式所在的单元格，自动跳转到公式编辑状态，在单元格内修改公式。

方法二：单击公式所在的单元格，编辑栏中显示具体的公式，在编辑栏内修改。

3. 函数的使用

对于表格中的复杂数据计算，用户可以借助函数解决。

（1）插入函数

单击【公式】选项卡，可以看到WPS表格为用户提供了各种类型的函数，如逻辑函数、文本函数、查找与引用函数等，在【其他函数】中，还有统计、工程、信息函数三种类型，如图3-126所示。

 图3-126

如果不清楚所需函数的类别，可以单击【插入函数】按钮，打开【插入函数】对话框，在【查找函数】文本框中输入要查找的函数名称或者函数的功能，在【选择函数】列表框中选择所需要的函数，如图3-127所示，单击【确定】按钮，即可将函数插入到单元格内。

 图3-127

（2）手动输入函数

对于已经掌握的函数，用户可以直接在单元格内手动输入。

以手动输入SUM函数为例，具体操作方法如下：

单击J2单元格，在单元格内输入“=SUM”，自动跳出相匹配的函数作为备选，单击列表中需要的函数，按“Enter”键或“Tab”键，或者双击列表中需要的函数，即可将函数添加到单元格内，如图3-128所示。

 图3-128

4. 公式与函数的填充

以计算某班总成绩为例，选中已经输入公式的单元格，将鼠标光标移动至单元格右下角，当光标显示为黑色十字时，长按鼠标左键向下拖动至需要填充的单元格区域，如图3-129所示。

之后释放鼠标左键，所选择的单元格区域将会自动填充函数并生成结果，如图3-130所示。当光标显示为黑色十字时，双击填充柄，函数也可自动向下填充。

 图3-129

 图3-130

3.3.2 单元格的引用方式

在电子表格中进行数值运算时，有时会出现函数书写正确，但结果出错的情况，这可能是单元格区域的引用出现了错误，因此用户需要掌握单元格引用的相关知识，根据实际情况选择单元格的引用方式。

单元格的引用可以分为相对引用、绝对引用与混合引用三种不同的引用方式，按“F4”键可以切换引用方式，不同的引用方式可以用“$”进行区分。

1. 相对引用

相对引用是指公式在向下、向右复制时，单元格的引用位置是相对的，会随着公式位置的改变而改变。

例如，在计算某班学生成绩的总成绩时，每个学生的总成绩都是一一对应且不相同的，因此在计算时需要用到相对引用。

在G2单元格中输入公式“=D2+E2+F2”，当公式向下进行复制时，G3、G4单元格的公式分别为“=D3+E3+F3”“=D4+E4+F4”，每个同学的总成绩也不相同，如图3-131所示。

 图3-131

2. 绝对引用

绝对引用是指公式在向下、向右复制时，单元格的引用位置是绝对的，不会随着公式位置的改变而改变。

例如，在计算某班学生成绩的总成绩时，在G2单元格中输入公式“=$D$2+$E$2+$F$2”，当公式向下进行复制时，可以看到公式的结果都是相同的，如图3-132所示。这说明在绝对引用条件下，单元格的引用不会随着公式位置的改变而改变。

 图3-132

3. 混合引用

混合引用是指公式在向下、向右复制时，仅保持所引用单元格的行或列某一方向的绝对不变，而另一个方向发生改变。因此混合引用分为两种情况：列相对引用、行绝对引用与列绝对引用、行相对引用。

Tip：默认的单元格引用方式是“相对引用”，按一次“F4”键可以切换到“绝对引用”，按第二次切换到“列相对引用、行绝对引用”，按第三次切换到“列绝对引用、行相对引用”，按第四次还原到“相对引用”。

3.3.3 统计类函数

WPS表格中内置有多种类型的函数，其中统计类函数主要用于对数据区域进行求和，寻找最大值、最小值等，此类型的函数主要分为基础统计函数与条件统计函数两大类。

1. 基础统计函数

基础统计函数主要包括SUM、MAX、MIN、AVERAGE、COUNT、COUNTA等函数。

（1）SUM函数

SUM函数是一个求和函数，用于计算某一单元格区域中所有数值之和。在做数据统计时，需要对某一区域进行求和，如果数据量较大，使用公式比较浪费时间，可以使用SUM函数进行计算。

SUM函数的函数公式为“=SUM(数值1,...)”，“数值1”可以是一个单元格，也可以是一个单元格区域。如果参数写成“数值1，数值2，数值3”模式，函数是对这三个单元格数值进行相加，如果参数写成“数值1：数值3”，函数将直接计算数值1到数值3之间的单元格中数值的总和。

以计算成绩单中每个同学的总成绩为例，具体操作方法如下：

单击J3单元格，插入函数“=SUM()”，单击D3单元格，长按鼠标左键拖动至I3单元格，操作完成后，按“Enter”键，将鼠标光标移动至单元格右下角，当光标显示为黑色十字时，双击填充柄，即可计算每个学生的总成绩，如图3-133所示。

 图3-133

（2）MAX函数

MAX函数是一个求最大值函数，用来计算某一单元格区域内的最大值。MAX函数的函数公式为“=MAX(数值1,...)”。

（3）MIN函数

MIN函数是一个求最小值函数，用来计算某一单元格区域内的最小值。MIN函数的函数公式为“=MIN(数值1,...)”。

（4）AVERAGE函数

AVERAGE函数是一个求平均值函数，用来计算某一单元格区域内的平均值。AVERAGE函数的函数公式为“=AVERAGE(数值1,...)”。

以计算单个学生6门学科的平均得分为例，具体操作方法如下：

单击M3单元格，输入函数公式“=AVERAGE(D3:I3)”，输入完成后，按“Enter”键，可显示出该名学生的平均分，双击填充柄，即可显示每个学生6门学科的平均成绩，如图3-134所示。

 图3-134

如果之前计算过总成绩，那么在计算平均分时，可以在M3单元格中输入公式“=J3/6”，同样可以计算平均分。

Tip：在计算平均值时，有时候会出现数值不能除尽，产生无限循环小数的情况，此时可以对单元格格式进行设置，如保留小数点后两位小数。

（5）COUNT和COUNTA函数

COUNT函数可以用来计算包含数字的单元格个数，此函数只对含有数字的单元格进行统计。COUNT函数的函数公式为“=COUNT(值1,...)”。

COUNTA函数可以用来计算非空单元格的个数，此函数可以对有内容的单元格进行统计。COUNTA函数的函数公式为“=COUNTA(值1,...)”。

以统计参加考试的总人数和实际人数为例，具体操作方法如下：

单击F2单元格，在单元格中输入函数“=COUNT(B2:B16)”，输入完成后，按“Enter”键，可显示本次考试的实际人数，如图3-135所示。

 图3-135

单击G2单元格，输入函数“=COUNTA(B2:B16)”，输入完成后，按“Enter”键，可显示本次考试的总人数，如图3-136所示。

 图3-136

2. 条件统计函数

条件统计类函数主要包括SUMIF、SUMIFS、COUNTIF、COUNTIFS等函数。

（1）SUMIF函数

SUMIF函数是单条件的求和函数，它的函数公式为“=SUMIF(区域,条件,[求和区域])”，第一个参数指的是用于条件判断的区域，第二个参数指的是规定的求和条件，第三个参数指的是求和区域。

以计算某表格中杭州地区的销售金额为例，具体操作方法如下：

单击G3单元格，插入函数“=SUMIF()”，第一参数选择B2到B12单元格区域，第二参数选择F3单元格作为条件，第三参数选择D2到D12单元格区域，完整的函数公式为“=SUMIF(B2:B12,F3,D2:D12)”，按“Enter”键，可显示杭州地区的总金额，如图3-137所示。

 图3-137

该函数表示如果B2到B12单元格中的地区有F3单元格的指定地区杭州，就对所有杭州地区对应的金额进行求和。

（2）SUMIFS函数

SUMIFS函数是多条件的求和函数，它的函数公式为“=SUMIFS(求和区域,区域1,条件1,[区域2,条件2,]...)”，与SUMIF函数不同，SUMIFS函数的求和区域是第一个参数，第二个参数“区域1”是第一个用于条件判断的区域，第三个参数“条件1”是第一个规定的求和条件，之后的“区域2”“条件2”依次类推，因为“区域2”和“条件2”用“[]”引用，表明此部分内容如果没有，可以不用在函数中表达出来。

以计算某表格中杭州地区外套的销售金额为例，具体操作方法如下：

单击H7单元格，插入函数“=SUMIFS()”，第一参数求和区域是D2到D12单元格区域，第二参数是B2到B12单元格区域，第三参数选择F7单元格作为条件1，第四参数是C2到C12单元格区域，第五参数选择G7单元格作为条件2，完整的函数公式为“=SUMIFS(D2:D12,B2:B12,F7,C2:C12,G7)”，输入完成后，按“Enter”键，可显示杭州地区的外套的总金额，如图3-138所示。

 图3-138

（3）COUNTIF函数

COUNTIF函数是单条件的计数函数，它的函数公式为“=COUNTIF(区域,条件)”，第一个参数指的是统计区域，第二个参数指的是统计条件。

以计算杭州地区订单数为例，具体操作方法如下：

单击G4单元格，插入函数“=COUNTIF()”，第一参数是B2到B12单元格区域，第二参数选择F4单元格作为条件，完整的函数公式为“=COUNTIF(B2:B12,F4)”，输入完成后，按“Enter”键，可显示杭州地区的总订单数，如图3-139所示。

 图3-139

（4）COUNTIFS函数

COUNTIFS函数是多条件的计数函数，它的函数公式为“=COUNTIFS(区域1,条件1,[区域2,条件2],...)”第一个参数“区域1”指的是第一个用于条件判断所在的区域，第二个参数“条件1”指的是是第一个规定的求和条件，之后的参数依次类推。

以计算2021年9月16日杭州地区的订单数为例，具体操作方法如下：

单击H8单元格，插入函数“=COUNTIFS()”，第一参数是A2到A12单元格区域，第二参数选择F8单元格作为条件1，第三参数是B2到B12单元格区域，第四参数选择G8单元格作为条件2，完整的函数公式为“=COUNTIFS(A2:A12,F8,B2:B12,G8)”，输入完成后，按“Enter”键，可显示杭州地区在2021年9月16日的订单数，如图3-140所示。

 图3-140

3.3.4 逻辑类函数

逻辑类函数常用于对数据进行对比判断，如判断是否符合指定条件，如果符合条件返回一个值，不符合条件则返回另一个值。逻辑类函数中常用的有IF、AND、OR、IFERROR等函数。

1. IF函数

IF函数是最常用的逻辑函数，它的函数公式是“=IF(测试条件,真值,[假值])”，第一个参数指的是需要进行判断的条件，第二、三个参数指的是，当判断的条件成立时，返回真值，当判断的条件不成立时，返回假值。

（1）IF函数的单条件判断

以给语文成绩>=95的学生返回合格，其他成绩返回不合格为例，具体操作方法如下：

单击E2单元格，插入函数“=IF()”，第一参数需要输入判断条件，选中D2单元格，判断其是否“>=95”，第二参数输入返回真值“合格”，第三参数输入返回假值“不合格”，完整的函数公式为“=IF(D2>=95,"合格","不合格")”，输入完成后，按“Enter”键，可以在E2单元格看到返回的结果，将光标定位在E2单元格右下角，双击填充柄，函数将会自动填充，如图3-141所示。

 图3-141

Tip：在写函数的参数时，若有文本格式，则需要用英文的双引号将文本引用起来。

（2）IF函数的嵌套条件判断

在函数中嵌套使用函数时，首先要理清各部分之间的逻辑关系，再进行书写。

以给语文成绩>=100的学生定义为“优秀”，成绩>=75的学生定义为“良好”，其余为“不及格”为例，具体操作方法如下：

单击E2单元格，插入函数“=IF()”，第一参数输入判断条件，选中D2单元格，判断其是否“>=100”，第二参数输入返回真值“优秀”，接下来需要再次对D2单元格进行判断，第三参数嵌套使用IF函数，嵌套的IF函数的第一参数输入判断条件“D2>=75”，第二参数输入返回真值“良好”，第三参数输入返回假值“不及格”，完整的函数公式为“=IF(D2>=100,"优秀",IF(D2>=75,"良好","不及格"))”，输入完成后，按“Enter”键，可以在E2单元格看到返回的结果，将光标定位在E2单元格右下角，双击填充柄，函数将会自动填充，如图3-142所示。

该函数表示如果D2单元格的语文成绩大于等于100，则返回“优秀”，如果不成立，则函数公式继续对D2单元格进行判断，判断D2单元格中的数据是否大于等于75，条件成立，返回“良好”，否则返回“不及格”。

 图3-142

2. AND或OR函数与IF函数结合的多条件判断

在日常办公中，往往会遇到对多个条件进行判断的情况，有时需要条件全部成立，有时仅需要一个条件成立即可，此时需要使用AND或OR函数与IF函数进行嵌套。

AND函数的函数公式为“=AND(逻辑值1,...)”。在参数组中，任何一个逻辑值不成立，AND函数则返回FALSE；只有当所有逻辑值成立时，才返回TRUE。AND函数的所有参数是“与”的关系，表示多个条件同时满足。

OR函数的函数公式为“=OR(逻辑值1,...)”。在参数组中，只有当所有逻辑值都不成立，才返回FALSE；一个或多个逻辑值不成立，也可以返回TRUE。OR函数的所有参数是“或”的关系，表示多个条件满足其一即可。

（1）AND函数与IF函数结合的多条件判断

以给语文成绩>=100且数学成绩>=95学生定义为“优秀”，其余为“良好”为例。

题目要求对条件进行判断返回相应的内容，且条件需要同时满足，因此要使用到IF函数和AND函数。具体操作方法如下：

单击F2单元格，插入函数“=AND()”，输入两个逻辑值“D2>=100,E2>=95”，在AND函数外套用IF函数，则IF函数的第一参数为“AND(D2>=100,E2>=95)”，第二参数返回真值“优秀”，第三参数返回假值“良好”，完整的函数公式为“=IF(AND(D2>=100,E2>=95),"优秀","良好")”，输入完成后，按“Enter”键，可以在F2单元格看到返回的结果，将光标定位在F2单元格右下角，双击填充柄，函数将会自动填充，如图3-143所示。

 图3-143

（2）OR函数与IF函数结合的多条件判断

以给语文成绩>=100或数学成绩>=100的学生定义为“优秀”，判断学生是否优秀为例。

题目要求对条件进行判断返回相应的内容，且满足一个条件即可，因此要使用到IF函数和OR函数。具体操作方法如下：

单击F2单元格，插入函数“=OR()”，输入两个逻辑值“D2>=100,E2>=100”，在OR函数外套用IF函数，则IF函数的第一参数为“OR(D2>=100,E2>=100)”，第二参数返回真值“是”，第三参数返回假值“否”，完整的函数公式为“=IF(OR(D2>=100,E2>=100),"是","否")”，输入完成后，按“Enter”键，可以在F2单元格看到返回的结果，将光标定位在F2单元格右下角，双击填充柄，函数将会自动填充，如图3-144所示。

 图3-144

（3）AND、OR函数同时与IF函数结合的多条件判断

以给某公司生产部的男员工或后勤部的女员工分别补助300元为例。

题目要求对条件进行判断返回相应的内容，性别和对应的部门是需要同时满足的条件，如“男”对应“生产部”，“女”对应“后勤部”，但是发放补助这个条件对应“生产部的男员工”“后勤部的女员工”任意一个条件即可，因此要使用到IF、AND和OR函数。具体操作方法如下：

单击E2单元格，输入函数“=AND()”，在AND函数中输入两个逻辑值“(C2="男",D2="生产部")”，再次输入函数“AND()”，输入两个逻辑值“(C2="女",D2="后勤部")”，两个AND函数满足其一即可进行补助的发放，因此在AND函数外套用OR函数。最后套用IF函数，使对应的条件返回相应的内容，因此IF函数的第一参数为“=IF(OR(AND(C2="男",D2="生产部"),AND(C2="女",D2="后勤部"))”，第二参数输入“300”，第三参数输入“0”，完整的函数公式为“=IF(OR(AND(C2="男",D2="生产部"),AND(C2="女",D2="后勤部")),300,0)”，输入完成后，按“Enter”键，可以在E2单元格看到返回的结果，将光标定位在E2单元格右下角，双击填充柄，函数将会自动填充，如图3-145所示。

 图3-145

Tip：AND和OR函数代表的逻辑关系分别是“与”和“或”，在写函数时也可以用乘号“*”与加号“+”代表AND与OR函数。在写复杂的多条件逻辑关系时，用符号表示逻辑关系能更加清晰，操作也更加简便。如函数“=IF(OR(AND(C2="男",D2="生产部"),AND(C2="女",D2="后勤部")),300,0)”就可以“=IF((C2="男")*(D2="生产部")+(C2="女")*(D2="后勤部"),300,0)”表示。

3. IFERROR函数

在计算数据时，为了美观，有时需要屏蔽错误值，不让其显示在数据中，这时可以使用IFERROR函数。IFERROR函数是指如果函数或公式计算出现错误值，则返回指定的值，它的函数公式为“=IFERROR(值,错误值)”。

以在销售记录表计算某地区商品的单价为例，具体操作方法如下：

F2到F12使用了公式，但是F5与F11单元格中返回了错误值，如图3-146所示。

 图3-146

单击F2单元格，套用IFERROR函数，完整的函数公式为“=IFERROR(D2/E2,0)”，双击填充柄向下复制公式，即可屏蔽错误值，如图3-147所示，F5单元格不再显示为错误值。

 图3-147

3.3.5 查找引用类函数

查找引用类函数常用于在指定单元格区域完成查找相关命令，本节重点介绍查找引用类函数中常用函数VLOOKUP、MATCH、INDEX等函数。

1. VLOOKUP函数

VLOOKUP函数是常用的查找引用函数，它的函数公式为“=VLOOKUP(查找值,数据表,列序数,[匹配条件])”。

第一个参数指的是指查找的目标，即要查找的某个值。

第二个参数指的是查找的范围，即要到哪里去找。这对所选取的数据区域有两个要求：查找值必须为所选数据区域的首列；包含返回值列。

第三个参数指的是返回的值位于所选数据区域的列数。

第四个参数指的是函数的匹配模式，包括精确匹配与模糊匹配两种类型。精确匹配用“0”或“FALSE”表达，模糊匹配用“1”或“TRUE”表达。

（1）精确匹配

使用精确匹配查找时，查找值必须是唯一的，否则无法返回正确的结果。

以在销售记录表中，根据工号查找出对应的人员的学历为例，具体操作方法如下：

单击L2单元格，插入函数“=VLOOKUP()”，第一参数选择“I2”单元格作为查找值，第二参数选择“A：D”单元格区域，第三参数输入学历所在的序列数“4”，第四参数输入匹配条件“FALSE”，完整的函数公式为“=VLOOKUP(I2,A:D,4,FALSE)”，输入完成后，按“Enter”键，可以在L2单元格看到返回的结果，将鼠标光标定位在L2单元格右下角，双击填充柄，函数将会自动填充，如图3-148所示。

该函数表示以I2单元格中指定的查找工号,在A到D序列的首列进行查找，并返回该区域中与之对应的第4列的学历信息。

 图3-148

（2）模糊匹配

模糊匹配是根据查找范围中区间的下限查找数据，VLOOKUP函数在使用模糊匹配方式时需要提前将查找区域按升序进行排列。

以根据对照表查询出对应人员的销售等级为例，具体操作方法如下：

单击G2单元格，插入函数“=VLOOKUP()”，第一参数选择F2单元格，第二参数选择K1到M6单元格区域，第三参数输入销售等级所在的序列数“3”，第四参数输入匹配条件“TRUE”，完整的函数公式为“=VLOOKUP(F2,$K$1:$M$6,3,TRUE)”，输入完成后，按“Enter”键，可以在G2单元格看到返回的结果，将鼠标光标定位在G2单元格右下角，双击填充柄，函数将会自动填充，如图3-149所示。

该函数表示以F2单元格中的销售额65300为查找值，在K1到M6单元格区域的首列查找该内容。由于单元格区域中没有一模一样的数值，因此要与小于65300的最大值50001进行匹配，并返回对应的销售等级。

 图3-149

（3）通配符查找

VLOOKUP函数进行精确匹配查找时，还可以在函数中使用通配符进行查找。星号“*”为通配符，表示任意长度的字符，当所查找的内容与对照表中的不完全一样时，可给查找内容前后添加通配符帮助匹配对照表中的内容。

以根据公司的简称找出对接人为例，具体操作方法如下：

单击G2单元格，插入函数“=VLOOKUP()”，第一参数输入“"*"&F2&"*"”，第二参数选择“B1:C7”单元格区域，第三参数输入对接人所在的序列数“2”，第四参数输入匹配条件“0”，完整的函数公式为“=VLOOKUP("*"&F2&"*",B1:C7,2,0)”，输入完成后，按“Enter”键，可以在G2单元格看到返回的结果,将鼠标光标定位在G2单元格右下角，双击填充柄，函数将会自动填充，如图3-150所示。

 图3-150

此例中函数公式里“"*"&F2&"*"”是使用连字符将星号与单元格内容连接起来，指只要被查询的公司名称中包含“电子科技”四字，VLOOKUP函数能返回所对应的值。

2. MATCH函数

MATCH函数可以根据查找值在查找范围内找出该值所在的行号，其目的是为了找查找值的位置。它的函数公式为“=MATCH(查找值,查找区域,[匹配类型])”。

第一个参数指的是规定的查找对象。

第二个参数指的是包含查找对象的单元格区域，只选择查找值所在的那一列即可。

第三个参数指的是匹配的类型，包括精确匹配，模糊匹配升序查找，模糊匹配降序查找，可分别用0,1，-1表示。

（1）精确匹配

以姓名查找在销售记录表中的位置为例，具体操作方法如下：

单击J3单元格，插入函数“=MATCH()”，第一参数选择I3单元格，第二参数为B2到B23单元格区域，第三参数输入匹配类型“0”，完整的函数公式为“=MATCH(I3,B2:B23,0)”，输入完成后，按“Enter”键，可以在J3单元格看到返回的结果,如图3-151所示。

该函数表示返回在B2到B23单元格区域中I3单元格内容所在的行数。

 图3-151

返回值“10”是行数，与第二参数引用的单元格区域有直接关联，如果第二参数选择“B1:B23”区域，则返回值为“11”如图3-152所示。

 图3-152

（2）模糊匹配降序查找

降序查找是指查找大于或等于查找值的最接近的值，其中查找区域必须进行降序排序，否则将会出错。

以查找销售量大于7680的值的行数为例，具体操作方法如下：

对销售量F列进行降序排列，单击J4单元格，插入函数“=MATCH()”，第一参数输入查找值“7680”，第二参数为F2到F23单元格区域，第三参数输入匹配类型“-1”，完整函数公式为“=MATCH(7680,F2:F23,-1)”，输入完成后，按“Enter”键，可以在J4单元格看到返回的结果,如图3-153所示。

该函数表示在F2到F23单元格区域中找到销售量最接近7680的值，并将其所在的行数返回在J4单元格中。

 图3-153

模糊匹配升序查找是查找小于或等于查找值中最接近的值，其中查找区域必须进行升序排序。

Tip：在使用MATCH函数时，如果查找区域包含多个查找值，则公式结果只返回第一个值的所在位置。

3. INDEX函数

INDEX函数是根据指定的行列号返回数据区域中对应位置的内容。INDEX函数的函数公式为“=INDEX(数组,行序数,[列序数],[区域序数])”。

第一个参数指的是要查找的值所在的单元格区域。

第二个参数指的是返回值所在的行数。

第三个参数指的是返回值所在的列数。

第四个参数指的是返回要引用的区域序数，在查找不连续的单元格区域时填写。

以根据指定的行列数返回销售记录表中的内容为例，具体操作方法如下：

单击I7单元格，插入函数“=INDEX()”，第一参数选择A1到F23单元格区域，第二参数选择I3单元格，第三参数选择I5单元格，完整的函数公式为“=INDEX(A1:F23,I3,I5)”，输入完成后，按“Enter”键，可以在I7单元格看到返回的结果,如图3-154所示。

该函数表示在A1到F23单元格区域中查找第五行，第六列的单元格数值，并将结果返回在I7单元格中。

 图3-154

4. MATCH函数，INDEX函数的结合使用

与MATCH函数不同，INDEX函数能够把指定区域内的数值在结果中显示出来，MATCH函数则只显示查找值的位置，所以一个是“取”，一个是“找”，二者结合使用的效果和VLOOKUP函数一样，但比VLOOKUP函数更加灵活。

以查找江苏省美食为例，具体操作方法如下：

单击H5单元格，插入函数“=INDEX()”，第一参数为A1到D8单元格区域，第二参数套用了MATCH函数，查找“江苏省”所在行数，第三参数套用MATCH函数，查找“美食”所在的列数，完整的函数公式为“=INDEX(A1:D8,MATCH(H1,A1:A8,0),MATCH(H3,A1:D1,0))”，输入完成后，按“Enter”键，可以在H7单元格看到返回的结果,如图3-155所示。

该嵌套函数中INDEX函数根据MATCH函数返回的位置信息，最终得到A1到D8单元格区域中对应位置的查询结果，并将结果返回在H5单元格中。

 图3-155

3.3.6 文本处理类函数

文本函数通常用来提取或查找内容，拆分或合并字符，该类函数的处理对象都是文本数据。文本数据指的是汉字、英文、其他字符和文本型的数字串。

文本处理类函数有很多种，LEFT函数、RIGHT函数、MID函数就是其中的一员，用户可根据需求选择使用的函数。

1. 提取字符串个数

（1）LEN函数

LEN函数用于返回文本字符串的字符数。文本字符串中的中文字符、英文字符、数字、空格等均按1计算。

以计算“孙尚香9512”字符数为例，具体操作方法如下：

单击B1单元格，输入函数“=LEN()”，选中A1单元格，按“Enter”键。计算出字符个数为“7”，如图3-156所示。

 图3-156

（2）LENB函数

LENB函数用于返回文本字符串的字节数。文本字符串中的中文字符与全角状态下的标点符号按2计算，数字、英文字符和半角状态下的标点符号按1计算。

以计算“孙尚香9512”字节数为例，具体操作方法如下：

单击B1单元格，输入函数“=LENB()”，选中A1单元格，按“Enter”键。计算出字节个数为“10”，如图3-157所示。

 图3-157

2. 提取字符串内容

LEFT函数、RIGHT函数、MID函数用于截取字符串的内容，根据不同的要求，使用的函数也不同。

（1）LEFT函数

LEFT函数指从文本字符串的第一个字符开始，返回指定个数的字符。它的函数公式为“=LEFT(字符串,[字符个数])”。

以提取姓名为例，具体操作方法如下：

方法一：单击B2单元格，输入函数“=LEFT()”，第一参数选中A2单元格，第二参数为“3”，输入完整函数公式为“=LEFT(A2,3)”，按“Enter”键，就截取出“孙尚香”这个文本，如图3-158所示。

 图3-158

方法二：除了直接使用LEFT函数提取外，还可以套用LEN、LENB函数计算提取的字符数。单击B2单元格，输入函数“=LEFT()”，选中A2单元格，再插入LENB和LEN函数，参数选中A2单元格，输入完整函数公式为“=LEFT(A2,LENB(A2)-LEN(A2))”,按“Enter”键，就截取出“孙尚香”这个文本，如图3-159所示。

 图3-159

（2）RIGHT函数

RIGHT函数指从文本字符串的最后一个字符开始，返回指定个数的字符。它的函数公式为“=RIGHT(字符串,[字符个数])”。

以提取“9512孙尚香”中“孙尚香”为例，具体操作方法如下：

单击B1单元格，输入函数公式“=RIGHT(A1,3)”，选中A1单元格，按“Enter”键，就截取出“孙尚香”这个文本，如图3-160所示。

 图3-160

（3）MID函数

MID函数指从文本字符串中指定位置开始，返回指定长度的字符串。它的函数公式为“=MID(字符串,开始位置,字符个数)”。

以提取身份证中的出生日期为例，具体操作方法如下：

单击B2单元格，输入函数“=MID()”，第一参数选择A2单元格，第二参数为“7”，第三参数为“8”，输入函数公式为“=MID(A2,7,8)”，该函数表示从A2单元格文本字符串的第7位开始，截取8个字符。按“Enter”键，即可得到身份证里的出生日期，如图3-161所示。

 图3-161

3.3.7 日期和时间函数

在各种类型的电子表格中，日期和时间是十分常见的统计项目，二者本质上是数值，根据需求，有时也需要对日期和时间进行计算，此时就要用到一些日期和时间的函数帮助处理数据。

1. 基本的日期函数

（1）DATE、YEAR、MONTH、DAY函数

DATE函数根据指定的年月日，返回对应的日期，返回值为标准日期格式。其函数公式为“=DATE(年,月,日)”。YEAR函数用于获取日期中的年份，其函数公式为“=YEAR(日期序号)”；MONTH函数用于获取日期中的月份，其函数公式为“=MONTH(日期序号)”；DAY函数用于获取日期中的天数，其函数公式为“=DAY(日期序号)”。

DATE函数可用于修改单元格的格式，也可用于将分散的日期整合成以年月日表示的日期。以使用DATE函数将“2022”“5”“19”整合为“2022/5/19”为例，具体操作方法如下：

单击D1单元格，输入函数公式“=DATE()”,第一参数选择A1单元格，第二参数选择B1单元格，第三参数选择C1单元格，得到完整函数公式为“=DATE(A1,B1,C1)”，按“Enter”键，可整合为“2022/5/19”且套用日期格式，如图3-162所示。

 图3-162

以使用YEAR函数获取“2022/5/19”的年份为例，具体操作方法如下：

单击B1单元格，输入函数公式“=YEAR(A1)”，按“Enter”键，得到“2022”这个年份，如图3-163所示。

 图3-163

MONTH函数和DAY函数的操作方法和YEAR相似，就不再一一举例说明。

（2）TODAY函数

TODAY函数用于返回当前日期，返回值以日期格式呈现。这个函数是没有参数的，用户直接在单元格内输入函数名称，将括号补齐，按“Enter”键即可，如图3-164所示。函数获取的是电脑中的日期，想要保证正确，就要确保电脑中的日期是正确的。

 图3-164

（3）DATEDIF函数

DATEDIF函数用于计算两个日期之间的天数、月数或年数的间隔，其函数公式为“=DATEDIF(开始日期,终止日期,比较单位)”。

第三参数“比较单位”包括“Y、M、D、MD”，其中“Y”计算年份差值、“M”计算月份差值、“D”计算天数差值、“MD”计算日期之间的间隔天数（忽略日期中的月份和年份）、“YD”计算日期中的间隔天数（忽略日期中的年份）、“YM”计算日期之间的间隔月数（忽略日期中年份）。“比较单位”需要使用英文状态下的双引号引用，大小写都不影响函数计算的最终结果。

以计算2022/3/8至2022/5/19的月份和天数间隔为例，具体操作方法如下：

单击C2单元格，输入函数公式“=DATEDIF(A2,B2,"m")”，按“Enter”键，计算出来相隔2个月，如图3-165所示。

 图3-165

单击D2单元格，输入函数公式“=DATEDIF(A2,B2,"d")”，按“Enter”键，计算出来的间隔天数为72天，如图3-166所示。

 图3-166

2. 基本的时间函数

（1）TIME、HOUR、MINUTE、SECOND函数

TIME函数根据指定的参数，返回对应时间，其函数公式为“=TIME(小时,分,秒)”。HOUR函数用于获取时间中的小时，其函数公式为“=HOUR(日期序号)”；MINUTE函数用于获取时间中的分钟，其函数公式为“=MINUTE(日期序号)”；SECOND函数用于获取时间中的秒，其函数公式为“=SECOND(日期序号)”。

以使用TIME函数返回上午7点7分为例，具体操作方法如下：

单击A1单元格，输入函数公式“=TIME(7,7,28)”，就得到指定时间上午的7：07，如图3-167所示。

 图3-167

Tip：单元格默认的时间格式为“0:00 AM/PM”,且TIME函数中秒并不显示。

以使用HOUR函数提取“7:07 AM”中的小时数为例，具体操作方法如下：

单击B1单元格，输入函数公式“=HOUR(A1)”，就可将A1单元格中的小时数“7”提取出来，如图3-168所示。MINUTE函数和SECOND函数的操作方法和HOUR相似，这里就不一一举例说明。

 图3-168

（2）NOW函数

NOW函数用于获取电脑系统当前日期及时间，这个函数同TODAY函数一样，没有参数，直接在单元格内输入函数公式即可，如图3-169所示，单元格内返回电脑系统默认的日期和时间。

 图3-169

3. 星期函数

（1）WEEKDAY函数

WEEKDAY函数常用于计算指定日期是星期几。其函数公式为“=WEEKDAY(日期序号,[返回值类型])”。

第二参数“[返回值类型]”有10种，对应10种返回值：

1或者省略——从1（星期日）到7（星期六）的数字；

2——从1（星期一）到7（星期日）的数字；

3——从0（星期一）到6（星期日）的数字，

11——数字1（星期一）至7（星期日）；

12——数字1（星期二）至7（星期一）；

13——数字1（星期三）至7（星期二）；

14——数字1（星期四）至7（星期三）；

15——数字1（星期五）至7（星期四）；

16——数字1（星期六）至7（星期五）；

17——数字1（星期日）至7（星期六）。

返回值类型中输入不同的数字，函数最终的返回值也不一样。

以计算2022/5/20是星期几，返回值类型选择‘2’为例，具体操作方法如下：

单击B1单元格，输入函数“=WEEKDAY()”,第一参数选择A1单元格，第二参数输入“2”，得到完整函数公式为“=WEEKDAY(A1,2)”，按“Enter”键，根据第二参数对应的返回值，可以得到2022/5/20是星期“5”，如图3-170所示。

 图3-170

（2）WEEKNUM函数

WEEKNUM函数常用于确认指定日期是当年的第几周。其函数公式为“=WEEKNUM(日期序号,[返回值类型])”。

以计算2022/5/20是2022年第几周为例，具体操作方法如下：

单击B1单元格，输入函数“=WEEKNUM()”，第一参数选择A1单元格，第二参数输入默认值“1”，得到完整函数公式为“=WEEKNUM(A1,1)”，按“Enter”键，根据第二参数对应的返回值，可以得到2022/5/20是2022年第21周，如图3-171所示。

 图3-171

4. 计算工作日

（1）WORKDAY函数

WORKDAY函数可返回某一日期之前或之后，相隔规定工作日数的一个日期，这里需要注意工作日不包含周末和假期的天数。其函数公式为：“=WORKDAY(开始日期,天数,[假期])”。

以计算距离某日10个工作日的日期为例，具体操作方法如下：

单击D2单元格，输入函数“=WORKDAY()”，第一参数选择A2单元格作为开始日期；第二参数选择B2单元格；第三参数选择C1至C4单元格作为假期，得到完整函数公式为“=WORKDAY(A2,B2,C2:C4)”，按“Enter”键，即可得到10个工作日后的日期，如图3-172所示。

 图3-172

（2）NETWORKDAYS函数

NETWORKDAYS函数用来计算两个日期之间的工作日天数。其函数公式为“=NETWORKDAYS(开始日期,终止日期,[假期])”。

以计算2022/5/5到2022/5/31之间的工作日数为例，具体操作方法如下：

单击C2单元格，输入函数“=NETWORKDAYS()”，第一参数选择A2单元格；第二参数选择B2单元格；因为开始日期和结束日期之间没有假期，第三参数[假期]可不填，函数公式可自动将周末排除，最后得到完整函数公式为“=NETWORKDAYS(A2,B2)”，按“Enter”键，得到工作日天数为“19”天，如图3-173所示。

 图3-173

Tip：NETWORKDAYS函数可自动排除周末，如果涉及假期，需要自行设置数据区域，在输入函数公式时，第三参数需选择设置的假期数据区域。

3.3.8 数学函数

在日常的数学计算中，我们可根据需求对运算结果进行取舍。考虑到用户在电子表格中处理数据时，也会有此种需求，WPS表格提供了MOD、INT、ROUND、ROUNDUP、ROUNDDOWN等函数来对数值进行处理。

1. MOD函数

MOD函数又叫做“取余函数”，常用于取两数相除后计算结果中的余数部分。MOD函数公式为“=MOD（数值,除数）”。

MOD函数返回值的正负号跟除数一致，除数是负数，计算出的结果就是负数。除数为正数，计算结果就是正数。

以“计算4除以3，4除以-3”为例，具体操作方法如下：

单击A1单元格输入函数公式“=MOD(4,3)”，按“Enter”键，A1单元格显示为“1”；如图3-174所示。

 图3-174

单击A1单元格，输入函数公式“=MOD(4,-3)”，按“Enter”键，A1单元格显示为“-2”。如图3-175所示。

 图3-175

2. INT函数

INT函数常用于将数字的小数部分向下舍去，截取整数，返回值小于或等于原有数值，因此也被称为“取整函数”。其函数公式为“=INT（数值）”。

以截取“5.169”的整数部分为例，在单元格内输入函数公式“=INT(5.169)”，返回值为“5”。

3. ROUND函数

ROUND函数常用于根据指定保留的小数位数对数字进行四舍五入。该函数对数值进行四舍五入时，会对需要保留小数位数的右边1位数值进行判定，此数值大于等于5，则向上舍入，此数值小于5，则向下舍去。ROUND函数公式为“=ROUND(数值,小数位数)”。

以四舍五入保留5.616565的4位小数为例，具体操作方法如下：

单击B1单元格，输入函数公式“=ROUND(A1,4)”，按“Enter”键，结果显示为5.6166，如图3-176所示。

 图3-176

4. ROUNDUP函数

ROUNDUP函数是保留指定小数位数时，后一位数值向上舍入，且不管整数部分的数值是正数还是负数。其函数公式为“=ROUNDUP(数值,小数位数)”。

以保留5.616565的1位小数为例，具体操作方式如下：

单击B1单元格，输入函数公式“=ROUNDUP(A1,1)”，按“Enter”键，结果显示为5.7，如图3-177所示。

 图3-177

5. ROUNDDOWN函数

ROUNDDOWN函数是保留指定小数位数时后一位数值向下舍去，且不管整数部分的数值是正数还是负数。其函数公式为“=ROUNDDOWN(数值,小数位数)”。

以保留5.616565的2位小数为例，具体操作方式如下：

单击B1单元格，输入函数公式“=ROUNDDOWN(A1,2)”，按“Enter”键，结果显示为5.61，如图3-178所示。

 图3-178

3.3.9 对公式和函数进行审查

当用户在使用公式和函数对数据进行处理时，有时会出现遇到错误值的情况，这表明数值运算某一个步骤可能出了差错。

当出现错误值时，用户可以通过检查公式结构是否完整，函数参数是否正确，对错误进行排查。为了节省排查错误的时间，提高办公效率，需要掌握一些常见的错误值类型，了解出错原因。用户也可以使用函数对错误值进行屏蔽，来保证电子表格数据的整洁、规范。

1. 常见函数错误值及出错原因

（1）错误值“####”指显示错误，表明列宽不够，无法显示全部内容。

（2）错误值“#DIV/0!”指除零错误，表明公式中使用了0或空单元格作为除数。

（3）错误值“#N/A”指值不可用错误，表明公式和函数中引用的值不可用。如VLOOKUP函数的查找区域里不存在查找值。

（4）错误值“#NAME?”指名称错误，表明公式或函数里使用WPS表格无法辨认的文本或名称。如公式或函数中的文本未添加双引号，内容书写不规范。

（5）错误值“#NULL!”表明公式或函数里没有正确使用公式运算符，使用空格连接不相交的单元格区域。

（6）错误值“#NUM!”表明公式或函数的计算结果数值过大或过小。

（7）错误值“#REF!”表明公式或函数里引用的单元格或工作表被删除，导致引用的区域或参数变为无效数据。

（8）错误值“#VALUE!”表明公式中所用的数据类型是错误的，函数中使用参数类型是错误。

2. 屏蔽函数错误值

公式和函数返回的错误值一方面会导致表格不美观，另一方面也对下一步的数据运算产生不利影响。所以当出现错误值时，首先是要对公式和函数进行检查，从源头解决问题。

但如果公式和函数返回的错误值，只是因为单元格中没有录入数据导致的出错，这时就可以使用IFERROR函数对错误值进行屏蔽。