Excel公式教程 (2016-07-24 11:15:19)
转载
▼
标签： excel公式教程 简化公式 数据类型自动转换分类： Excel公式教程-原理篇
算术运算符的运算项，数据类型要求是数字值；
文本运算符的运算项，数据类型要求是文本值；
引用运算符的运算项，数据类型要求是单元格引用；
函数的参数，大多数都要求是特定的数据类型。
运算符的运算项、函数的参数，如果数据类型不符合要求时，Excel有时候能够对其自动转换。巧妙地利用数据类型自动转换的功能，可以简化Excel公式。
一、当需要数字值的时候，如何利用数据类型自动转换功能简化公式。
1、将空单元格、空参数作0使用。例如：
=TIME(12,0,0) 公式可以简化为：=TIME(12,,) 空参数作0使用。
=IF(A1<0,0,A1) 公式可以简化为：=IF(A1<0,,A1)
2、将文本型数字转成数字值，将文本型日期时间转成序列数。例如：
=1+VALUE("￥4,000.00") 公式可以简化为：=1+"￥4,000.00"
文本型数字"￥4,000.00"能够自动转换为数字值4000，无需使用VALUE函数。
=TODAY()-DATEVALUE("2016-7-1") 公式可以简化为：=TODAY()-"2016-7-1"
文本型日期"2016-7-1"能够自动转换为日期序列数，无需使用DATEVALUE函数。
输入这个公式后，如果单元格显示为日期格式，可把单元格格式改为常规。
同样地：
=MONTH(DATEVALUE("Aug"&1)) 公式可以简化为：=MONTH("Aug"&1)
3、将逻辑值TRUE转成1，FALSE转成0。例如：
=B1+IF(A2>0,1,0) 公式可以简化为：=B1+(A2>0)
公式中A2>0返回逻辑值TRUE或FALSE，然后转成数字值1或0。简化后的公式不需要使用任何函数！
同样道理，以下公式
=IF(A1="A",1,IF(A1="B",2,IF(A1="C",3,)))+IF(A1="D",4,IF(A1="E",5,IF(A1="F",6,)))
可以简化为：
=(A1="A")*1+(A1="B")*2+(A1="C")*3+(A1="D")*4+(A1="E")*5+(A1="F")*6
4、如果单元格A1是空单元格、文本型数字、文本型日期时间或逻辑值，以下式子都可以把A1转换成数字值：--A1、A1+0、A1-0、A1*1、A1/1、A1^1。
二、当需要文本值的时候，如何利用数据类型自动转换功能简化公式。
1、将空单元格、空参数作空文本使用。例如：
=REPLACE(A1,1,4,"") 公式可以简化为：=REPLACE(A1,1,4,) 空参数作空文本使用。
需要注意的是，如果C3是空单元格，=C3返回0，而=C3&""返回空文本。
2、将数字值、逻辑值转成文本，将日期、时间转成文本型序列数。例如：
="满分："&100 返回文本“满分：100”
假设单元格A1是文本“下班时间”，B1是时间12:00:00，
=A1&B1 返回文本"下班时间0.5"
如果想返回"下班时间12:00"，可改为以下公式：
=A1&TEXT(B1,"hh:mm")
三、当需要逻辑值的时候，如何利用数据类型自动转换功能简化公式。
1、将空单元格、空参数作FALSE使用。例如：
=VLOOKUP(A2,C:F,2,FALSE) 公式可以简化为：=VLOOKUP(A2,C:F,2,) 空参数作FALSE使用。
如果A1是空单元格，=NOT(A1)返回TRUE，空单元格作FALSE使用。
2、将数字0转成FALSE，非0数字转成TRUE。例如：
=OR(0)返回FALSE，=OR(1)、=OR(3)都返回TRUE。
假设单元格A1为数字值，以下公式，当A1为0返回0，当A1不为0返回1：
=--OR(A1)
该公式首先用OR函数把数字0转为FALSE，其他数字转为TRUE；然后通过算术运算--，把逻辑值FALSE转为0，TRUE转为1。
3、将文本型逻辑值转成逻辑值。
四、当函数参数需要数组的时候，自动进行数组扩展和数组运算，无需按三键。
例如：
=SUMPRODUCT((A1:A5>0)*A1:A5) 可自动进行数组运算而无需按Ctrl+Shift+Enter。
这种情况，同样适于使用数组或区域引用为参数的LOOKUP、INDEX等函数：
五、运用数据类型自动转换功能需要注意的地方。
1、如果运算项或函数参数无法转换成所需的数据类型，则返回错误值#VALUE!。
2、有些函数参数是不能缺省的，但可以是空参数。比如TIME(12,,)。
3、有些函数参数缺省时与空参数代表不同的值。
比如：
VLOOKUP函数的第四个参数，缺省值是TRUE（近似匹配），空参数则是FALSE（精确匹配）。
IF函数的第三个参数，缺省值是FALSE，空参数则是0。
4、IF和CHOOSE函数可以返回任意的数值类型，如果返回值是一个引用，而当它们与引用运算符相连的时候，就返回一个引用，其他情况下返回引用里的值。
例如，如果单元格A1、A2、A3的值分别是1、2、3，则以下公式
=IF(A3=0,A2,A3)
IF函数返回A3单元格的值3，而以下公式
=SUM(A1:IF(A3=0,A2,A3))
IF函数返回引用A3给联合运算符，变成=SUM(A1:A3)，然后SUM对A1到A3区域求和。
