那E氏函数家族中的“0”也真像小朋友所说的那样表示什么也没有吗?不然,0的活用与不用蕴藏着很多意想不到的玄机。到底有怎样的玄机呢?那我就0机一动开处方,虽然不算什么0当妙药”,闲话少说,E切从0开始,一起来看看0牙利齿吧!
一、活“0”活现
(一)简单文本求和中0的作用(+0或-0)
例子:将A列A1:A10的数字相加,其中可能还有文本型的数字也需要相加。公式:
=SUMPRODUCT(A1:A10+0)
或者:
=SUMPRODUCT(A1:A10-0)
解析:初级用户会觉得+0,-0不就等于没有增加,没有减少嘛,为何要这样呢?是啊,要的就是这个效果,既要改变原数据的性质(文本转变为数值),又要准确计算,所以只有用+0,-0,这一“+”或“-”符号就是改变原数据的性质的。这一带符号的0犹如一个“小石头”,从后面抛出去将昏睡中的“大石头”(数字)砸醒。
参考文章:文本转数值的十一种方法(百度一下可查询到)
(二)“0”嶺先锋
例①、如:单元格A1中输入数字12304568579213(15位以下,文本或数值型均可)要将这个数的每一位相加,公式:
=SUM(--(0&MID(A1,COLUMN(1:1),1)))
解析:因单元格字符串长度只有14位提取长度为1至256位的长度,所以从15位开始,只能提取到空值。效果如下:
=SUMPRODUCT(--(0&{"1","2","3","0","4","5","6","8","5","7","9","2","1","3","",……,""}))
前面补0后的效果如下:
=SUMPRODUCT(--{"01","02","03","00","04","05","06","08","05","07","09","02","01","03","0",……,"0"})
此时没有空值,只有14个文本数字和文本0,前面加2个负号后,转化为数值,看效果:
=SUMPRODUCT({1,2,3,0,4,5,6,8,5,7,9,2,1,3,0,……,0})
没有空值,且全部为数值就可以相加了,结果为56。
例②、单元格A1输入:123大理789,要将这个单元格的每一位数字相加,公式:
=SUMPRODUCT(--(0&MIDB(A1,COLUMN(1:1),1)))
与上例不同的是,MIDB会将每个双字节(如汉字就是双字节)字符按2计数,否则,函数MIDB会将每个字符按1计数。当只提取1个字节时,遇到汉字(双字节),只能提取到半个汉字(也就是空值),效果如下:
=SUMPRODUCT(--(0&{"1","2","3"," "," ……,""}))
0&后填补空值。
二、脱胎换骨—化“文”为“0”
单元格A1输入123abcABC789,要将这个单元格的每一位数字相加,公式:
=SUMPRODUCT(--TEXT(MID(A1,COLUMN(1:1),1),"0;;0;\0"))
解析:由于字符串中有“abcABC”,是单字节字符,所以不能象上例那样用MIDB提取半个汉字的办法来处理。此时,我们仍用MID来提取的基础上,再请出“霸道,聪明”的TEXT函数,将非数字字符强行改为0,若为数值则不变。条件参数"0;;0;\0"中第一个0神通广大,代表了除0之外的任意正整数,也就是假0(是通配数值的0),第二个则是“苍蝇嘴巴狗鼻子—真0”,第三个0是强行做“变性”手术后的0。