|
21世纪教育网微视频制作招聘启事
八、瞒天过海(ISERR与ISERROR)
IS类函数的运用,诸如:
=ISERROR(#N/A)
=ISERROR(DATE(2006,1,9))
=ISERR(-"Good")
例如:将单元格A1:A10求和:
150
#N/A
#VALUE!
#REF!
#DIV/0!
#NUM!
#NAME?
#NULL!
9
北京
对于不能求和的项目,系统显示#N/A,但这样说给上司算不出来,未免显得太菜了。用什么方法,可以算出正确值呢?对了,先来一招投石问路,对各单元格返回的值做一个判断,看看系统到底能不能作出正确的判断。再来一招左右逢源(IF),对于满足的就显示原值,不满足(出错的)的,就干脆让它为0,(当然,这个0也能省略),岂不妙哉?
因此,常规的求和是绝对不能解决问题的,单元格区域中本身就是EXCEL认为的错误字符,所以可以结合IF和IS函数来使用。大家可能已学习过,对于投石问路(IS类函数),共有九种变化,其中第三式(ISERROR)或第二式(ISERR)是比较常用的,可以使用。因此,组合后的公式就变成:
公式:
=SUM(IF(ISERROR(A1:A10+0),,A1:A10+0))
以上数字如为数值型,则可简化为:
=SUM(IF(ISERROR(A1:A10),,A1:A10))
或者(干脆避开IS类函数):
=SUMIF(A:A,”<9E307”)
(注公式中的”,,”是将两个英文逗号间的0省略了,不省略就写为 “,0,”)
ISERROR如果为真,说明真的出错,返回0,如果为假(没有出错),返回原值.这里又是0和1的变戏法。
九、“0”的消失(遇到&""时)
0作为分母可以让0消失,TEXT中的条件参数也可以让0真正消失,T函数可以让数值型数字真正消失。N函数则可以让字符变身为0。空值(真空)当与数值大小比较时显示值为0,与字符比较时为空(””)例如:单元格A2:A10输入:
名称
A
A
B
C
B
C
A
D
B
要列所有的字母“A”,公式为:
=INDEX(A:A,SMALL(IF($A$2A$10="A",ROW($2:$10),4^8),ROW(A1)))
查询区域中只有3个A,当往下填充时显示为0,因65536行为空,所以返回数值0,当公式末尾加上” &""”后,返回文本””,所以0消失。完美公式如下:
=INDEX(A:A,SMALL(IF($A$2:$A$10="A",ROW($2:$10),4^8),ROW(A1)))&""
十、“0”蛋的安全
例①单元格A1输入: 大理aw白族ws自00124.36hcw治州
问题:想在B1单元格中提取出数字00124.36
公式:
=MID(LOOKUP(1,-(1&MID(A1,MIN(FIND(ROW($1:$10)-1,A1&1/17)),ROW($1:$15)))),3,15)
解析:常规的公式如:=-LOOKUP(1,-MID(A1,MIN(FIND(ROW($1:$10)-1,A1&1/17)),ROW($1:$15))),会使有效数字前面的0丢失,我们不得不聘请“1”来做“安全卫士”,防止“0”逃走,用“1&”之后,将它们一并提出来,由于数字是负值,后面又有1,所以从第3个字符提取到15位。
例②单元格A2:A8输入:
数字串
593670
012690
12789.3648
0.36998
(空白)
12789.3648
(空白)
问题:要求将各单元格的数字反转。公式:
=RIGHT(REPLACE(SUM((0&MID(SUBSTITUTE(A2,".",)&1,ROW($1:$15),1))*10^ROW($2:$16))%,LEN(A2)-FIND(".",A2&".")+2,,"."),LEN(A2))
解析:公式中的“&1”,就是怕原数字串的最后一个0丢失,因反转后,最后的0变成了最前面的,因数值前面的0无效而丢失,“&1”中的1就象护栏一样(根据问题的具体情况,有时用“1&”),防止边缘上的0滚蛋,滚蛋了不就成了“卖鸡蛋的跌倒—没有一个好的”,这“云南十八怪中的鸡蛋用草串着卖”也就怕滚蛋了。 |
|