分类：函数的基本使用

有时，当用户打开一个工作簿但不做任何更改就关闭时，Excel却提醒是否保存。这是因为Excel文件用到了一些具有volatile特性的函数，即易失性函数。这种函数一个典型的特点是：使用这些函数以后，会引发工作表的重新计算。因此没激活一个单元格，或者在一个单元格输入数据，甚至只是打开工作簿，具有易失性的函数都会自动重新计算。常见的易失性函数有NOW（）、TODAY（）、RAND（）、CELL（）、OFFSET（）、INDIRECT（）、INFO（）、RANDBETWEEN（）等。

虽然易失性函数在实际应用中非常有用，但是如果大量使用易失性函数，则会因为重新计算工作量太大而影响表格的运行速度。

在函数的实际使用过程中，并非总是需要将一个函数的所有参数都写完整才可以计算，可以根据需要对参数进行省略和简化，以达到缩短公式长度或者减少计算步骤的目的。本节将具体讲解如何省略、简写以及简化函数参数。

函数的帮助文件会将其各个参数表达的意思和要求罗列出来，仔细看看就会发现，有很多参数的描述包括“忽略”、“省略”、“默认”等词，而且会注明，如果省略该参数，则表示默认该参数代表某个值。对于参数的省略，是指该参数连同该参数存在所需的逗号间隔都不出现在函数中。

例：判断B2是否与A2的值相等，如果是则返回TRUE，否则返回FALSE。

---

=IF(B2=A2,TRUE,FALSE)

---

可以省略为：

---

=IF(B2=A2,TRUE)

---

部分函数中的参数为TRUE或者FALSE，比如VLOOKUP函数的参数range_lookup。当指定其为FALSE的时候，可以用“0”来替代。甚至连“0”也不写，而只是用逗号占据参数位置。下面3个公式是等价的。

---

= VLOOKUP(A1,B1:C10,2,FALSE)
= VLOOKUP(A1,B1:C10,2,0)
= VLOOKUP(A1,B1:C10,2,)

---

此外，有些针对数值的逻辑判断，可利用“0=FALSE”和“非0数值=TRUE”的规则来简化，比如已知A1单元格的数据只可能是数值的前提下，可以将公式=IF（A1<>0，B1/A1，””），简化为=IF（A1，B1/A1，””）。

Excel在公式计算方面有其自身的标准与规范，这些规范对公式的编写有一定的限制，主要包括以下几个方面。

1. 公式内容的长度不能超过1024个字符。
2. 公式中函数的嵌套不能超过7层。
3. 公式中函数的参数不能超过30个。

正是因为上面的这些限制条件，使得用户在操作Excel的过程中存在诸多问题。下面以具体实例来详细讲解如何突破函数的7层嵌套以及30个函数参数的限制。

当函数B在函数A中用作参数的时候，函数B为第2级函数。如公式“=IF（A1>0，SUM（B1，G1），””）”，其中SUM函数是第2级函数，因为它是IF函数的参数。如果在SUM函数中继续嵌套函数则为第3级函数，以此类推，Excel函数公式可以包含多达7级的嵌套函数。函数的7层嵌套限制了使用多个函数，要解决这个问题，可以通过定义名称的方法来实现。

例如，要将A1中字符串“我113爱322学43习75E56x5353c256382e85626l54”的数字去掉，利用SUBSTITUTE函数来解决，在B1中输入公式“=SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（A1，0，），1，），2，），3，），4，），5，），6，），7，）”，之后就不能直接再套用函数了，因为从第2个SUBSTITUTE开始，每一个都是前一个的参数，已经达到了7级嵌套。此时，切换到“公式”选项卡，然后单击“定义名称”按钮，打开“新建名称”对话框，将上面的名称定义为X，然后在B1中输入“=SUBSTITUTE（SUBSTITUTE（X，8，），9，）”，这样就可以得到去掉数字的字符串“我爱学习Excel了！”。同样，利用名称定义的方法可解决用IF判断，以及其他函数因为嵌套层数超过7层而导致公式无法输入的问题。

上面介绍了如何突破函数的7层嵌套限制，在实际应用过程中，用户还会受到Excel中30个函数参数的限制，下面简单介绍如何突破30个函数参数的限制。

Excel规定的函数参数最多为30个，如SUM函数、COUNT函数、COUNTA函数、AVERAGE函数、CHOOSE函数等。例如需要计算某些特定单元格中数值的平均值，有以下公式“=AVERAGE（A1，A2，B1，B3，B6，C6，D8，…）”，其中，括号里面的参数多于30个。这个时候，可以在函数参数的两边加上一对括号，形成联合区域来作为参数，相当于只有1个参数。这样，公式就只受到字符个数的限制了。

逻辑判断是指有具体意义，并且可以判断真或假的陈述语句，是函数公式的基础，不仅关系到公式的正确与否，也关系到解题思路的简繁，只有逻辑条理清晰，才可以写出简洁有效的公式。常用的逻辑关系有3种，即“与”、“或”、“非”。在本节首先讲解这3种逻辑关系，接着讲解逻辑关系的嵌套和运算。

与（AND关系）

当两个或者多个条件必须同时成立才判定为真时，称判定与条件的关系为逻辑与关系，即平时说的“且”，AND函数常常用于逻辑与关系运算。

例1：用公式表示当A1单元格的值大于0且小于等于10的时候返回TRUE。

---

=AND(A1>0,A1<=10)

---

例2：B列是性格，C列是年龄，D列是职务，要在E列中输入公式，筛选出40岁以上的男教授的记录。其中，E1的公式为

---

=AND(B1="男"，C1>40，D1="教授")

---

或（OR关系）

当两个或多个条件只要有一个成立就判定为真时，称判定与条件的关系为逻辑或关系，OR函数经常用于逻辑或关系运算。

例：A、B、C三列分别是语文、数学、英语成绩，要在D列中输入公式筛选所有不及格的记录（假设60分及格），其中D1的公式为

---

=OR(A1<60,B1<60,C1<60)

---

非（NOT关系）

当条件只要成立就判定为假时，称判定与条件的关系为逻辑非关系。NOT函数用于将逻辑值反转。

例：A列存放着人员学历，分为中专、高中、大专、本科、硕士等，现在需要在B列输入公式筛选除硕士以外的记录，其中B1的公式为

---

=NOT(A1="硕士")

---

也可以利用下面的公式表示：

---

=A1<>"硕士"

---

上面简单介绍了几种逻辑关系，接下来就逻辑关系的嵌套展开论述。

函数IF（logical_test，value_if_true，value_if_false）的第一个参数判定真假，为真返回第2个参数，为假返回第3个参数。如果第2个参数和第3个参数还需要进一步判断，这时新的逻辑判断作为参数嵌套于原有判断，在执行原有判断的基础上进行。

例：A1为成绩，要求用公式在B1返回成绩等级，规则为“成绩低于60分为不及格，60~79分为及格，80~89分为良，90~100分为优”。

1）简单地堆积条件。例如分数在60~79分段时表达为AND（A1>=60，A1<80），其他类似。得到的公式如下所示。

---

=IF(A1<60,"不及格",IF(AND(A1>=60,A1<80),"及格"，IF(AND(A1>=80,A1<90),"良", IF(AND(A1>=90,A1<100),"优"))))

---

2）归纳整理。如果A1不低于60，即第1个参数为假的同时已经包含“A1>=60为真”的判定了。利用这个逻辑关系的嵌套，正确的公式表达如下：

---

=IF(A1<60,"不及格",IF(A1<80,"及格",IF(A1<90,"良","优")))

---

以上介绍了逻辑关系的嵌套，下面讲解逻辑关系的运算等有关技巧。

在实际应用中，逻辑值是可以直接参与函数公式计算的，通常可以看到以下实例的解法。

例1：查找B1在A1：A10数据区域中是否存在的公式1为

---

=IF(COUNTIF(A1:A10,B1),"存在","")

---

例2：统计A1：A10数据区域中大于“0”的数值个数的公式2为

---

=SUM((A1:A10>0)*1)

---

为什么公式1中IF的条件判断不用“COUNTIF（A1，A10，B1）>0”，而公式2中为什么要在SUM函数中使用“*1”？

为了解释以上问题，先来了解以下几种逻辑值之间的运算结果。

---

TRUE*1=1*1=1(或TRUE+0=1)
FALSE*1=0*1=0(或FALSE +0=1)
TRUE+FALSE=1+0=1
TRUE*TRUE=1*1=1
TRUE*FALSE =1*0=0
TRUE+TRUE=1+1=2

---

根据上面列举的计算准则，以上6项计算简单列举了常用逻辑运算的结果，这也是在数组公式运算中常见的、最常用的理论关系。

了解了以上的运算符，再来看公式1，由于COUNTIF函数统计结果只能为非负数（结果大于或等于0），那么如果B1存在，COUNTIF函数结果必定大于0，否则结果等于0，所以就可以不用添加“>0”进行判断。

而对于公式2，如果SUM函数的参数是数组，而且这个数组是由逻辑值组成，那么要对这些逻辑值求和，就必须先将逻辑值进行运算（*1或者+0），SUM函数才可以正确求和。

Excel数据一般可以分为文本、数值、日期、逻辑、错误等几种类型，其中，日期是数字的一个特殊格式。

此外，数字和数值是两个不同的概念，数字可以以文本的形式出现，也可以是数值、日期等。一般在明确指定的情况下，数字指的是数值型数字。

在Excel函数帮助文件中，经常会看到关于升序的描述为“数值必须按照升序排列：…-2、-1、0、1、2、…、A-Z、FALSE、TRUE”，这是Excel的一个规则，即数字小于文本，文本小于逻辑值，错误值不参与排序。

以上介绍了数据类型及排序规则，接下来介绍逻辑值与数值的关系。

在Excel中，逻辑值只有TRUE和FALSE两个，它们与数值的关系为：

1）在数值运算中，TRUE=1，FALSE=0。

2）在逻辑判断中，FALSE=0，TRUE=所有非0数值。

这两条准则非常重要，在Excel函数公式的简化及计算中用途很广。

介绍完逻辑值与数值的关系之后，接下来介绍一下数据类型转换的有关技巧。

以文本形式存储的数据，如在A1中输入“123”，或者将A1的单元格格式设置为文本的输入“123”，那么该数字不能直接作为数值参与函数计算。这时，公式“=A1=123”将返回FALSE，公式“=SUM（A1：A2）”将无法得到正确的计算结果。

通常用以下6种方法可将A1中以文本形式存储的数字转换为数值型。

---

=A1*1
=A1/1
=A1+1
=A1-1
=--A1
=value（A1）

---

注意：混淆文本型数字与数值型数字是许多用户经常犯而又不易觉察的错误，通过使用LEFT、MID、TEXT等文本函数，计算得到的结果都是文本型，如果未进行转换而代入下一步，与之进行不匹配的计算，将返回错误结果。