A-A+

“0”活多变的函数公式与格式

2011年05月28日 软件 暂无评论 阅读 浏览:2,267 次

如果你问一个学前班或者一年级的小朋友,0表示什么?他会毫不犹豫的告诉你,0表示没有,比如草地上一只羊也没有,老师就叫我们用0表示。早上爸爸给我买了两个苹果,我吃了一个,弟弟也吃了一个,现在一个也没有,就用0表示。这样的例子小朋友还可以说得很多。
小朋友说的没错,0表示“没有”可能是0最早的意思吧,也就是0的本义。古时候的人最初完全没有数量这个概念,后来由于记事和分配生活用品等方面的需要,才逐渐产生了数的概念。比如捕获了一头野兽,就用1块石子代表。捕获了3头,就放3块石子。假如什么都没有捕获,当然是0头了。这样就产生了数,各国的人们也学会了用不同的符号表示不同的数字,但人们最后学会的是怎么表示0,因为其他的数字都比较好表示,所以后来有人把铜钱摆在空位上,以免弄错,这就表示0。不过多数人认为,"0"这一数学符号的发明应归功于公元6世纪的印度人。他们最早用黑点(·)表示零,后来逐渐变成了"0"。
那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。
三、“0”补队员

①单元格A1输入数字12378945600123,如何将单元格内数字按顺序去重。
公式:
=MID(SUM((0&MID(A1,SMALL(FIND(ROW($1:$10)-1,A1&5^19),ROW($1:$10)),1))/10^ROW($1:$10))&"00",3,COUNT(FIND(ROW($1:$10)-1,A1)))
或者:
=MID(SUM(MID(A1&56^7,SMALL(FIND(ROW($1:$10)-1,A1&56^7),ROW($1:$10)),1)/10^ROW($1:$10))&0,3,COUNT(FIND(ROW($1:$10)-1,A1)))
解析:
”(0&MID(A1,SMALL(FIND(ROW($1:$10)-1,A1&5^19),ROW($1:$10)),1))”中,前面补0,是为了填补空值,这里不再赘述,式子:SUM((0&MID(A1,SMALL(FIND(ROW($1:$10)-1,A1&5^19),ROW($1:$10)),1))/10^ROW($1:$10))&"00"中&”00”的作用有两个,一是防止计算出的0在最后被忽略;二是单元格中仅输入一个或多个0时,最后能提取到一个0。
四、忘我(“0”)牺牲

①如:单元格A1:A5中有字符串,也有文本数字。
01
03
0
#VALUE!
大理789
问题:统计A1:A5中非0数字(非0文本型数字和数值都算)有几个?
数组公式:
=COUNT(0/A1:A5)
解析:
由于0和文字不能做除数,我们将违背这一原理,把A1:A5作为除数,让0和文字出现错误值。效果:
{0;0;#DIV/0!;#VALUE!;#VALUE!}
按我兄弟顺溜的话来说,让他们(0和文字)都死球。这活下来的“英雄”就是我们要数的“人”(非0数字个数)了。于是我们让SUM,SUMPRODUCT,ISERR,ISERROR,ISNUMBER等几位大侠先“下岗”,只聘请数数高手“COUNT”大侠。
=COUNT({0;0;#DIV/0!;#VALUE!;#VALUE!})
=2

五、隐居山“0”

如单元格A2中输入字符串”?ABC?wshcw中国云南大理abc?OWY?Excelhome?”

问题:
如何提取汉字:"中国云南大理"
公式:
=MID(LEFT(A2,MATCH(,0/(MID(A2,COLUMN(2:2),1)>="吖"))),MATCH(,0/(MID(A2,COLUMN(2:2),1)>="吖"),),99)
没有用简写的原公式:=MID(LEFT(A2,MATCH(0,0/(MID(A2,COLUMN(2:2),1)>="吖"))),MATCH(0,0/(MID(A2,COLUMN(2:2),1)>="吖"),0),99)
解析:
1、公式中“0/(MID(A2,COLUMN(2:2),1)>="吖")”由于汉字最小是"吖",只要大小等于"吖",就说明它是汉字,这部分的作用是将小于“吖”的字符经判断后作为分母(分母为0),继而出错(也就排除了小于“吖”的部分,换句话说,也就是牺牲非0的字符),由于分子为0,继而赢得汉字演变为0的胜利。
2、MATCH(,0/(MID(A2,COLUMN(2:2),1)>="吖"))这部分是定位最后一个汉字的位置,值得注意的是:前一个英文“,”前省略了一个0,作用是定位最后一个0的位置。
3、MATCH(,0/(MID(A2,COLUMN(2:2),1)>="吖"),)这部分是定位最前一个汉字的位置,值得注意的是:最后一个反括号前“)”前省略了一个0,作用是定位最前一个0的位置。
MATCH(,0/(MID(A2,COLUMN(2:2),1)>="吖"))与MATCH(,0/(MID(A2,COLUMN(2:2),1)>="吖"),)看上去只有一逗(“,”)之差,但“差以毫厘,谬以千里”。前者:目标远大,把潜力发挥到极限,后者因被眼前的”,”号所诱惑,目标只定位在眼前,目光短浅。这函数也象人生一样,只有小智慧与大智慧的结合,才能使函数家族兴旺发达。

六、居高“0”上(0次方的用法)

例A2:A7输入:
字符串
bbbccew-58人LK民
AYUBMMM主人123965
ABCR
(空白)
lBMMM主人-1
mc76yk 中国
问题:要将A2:A7的单元格数据汇总求和。
公式:
=SUM(-TEXT(MID(A2:A7&"@",COLUMN(1:1),MMULT(1-ISERR(-MID(A2:A7&"a1",COLUMN(1:1),2)),ROW(1:256)^0)),"-0%;0%;0;!0"))
解析:
1、先算出每个单元格含有的数字个数,再按这个个数分别逐个提取。
2、公式中:MMULT(1-ISERR(-MID(A2:A7&"a1",COLUMN(1:1),2)),ROW(1:256)^0)就是算出每个单元格含有的数字个数,那么” ^0”为何爬得如此高呢?这是因为ROW(1:256)^0)是常量数组{1;……;1;1}的缩写。是序列数1到256的0次幂,也就是256个1的数组。这0次方的妙用是E友对EXCEL不断开拓创新的结果。

七、“0”的伪装(TRUE,FALSE)

例①A1单元格是785,B1单元格是358017。如何从B1中将A1的7、8、5替换掉,在C1得出301
=SUM(MID(0&B1,LARGE(ISERR(FIND(MID(B1,COLUMN(1:1),1),A1))*COLUMN(1:1),COLUMN(1:1))+1,1)*10^COLUMN(1:1))/10

公式分析:
1、FIND(MID(B1,COLUMN(1:1),1),A1)
=FIND(MID(B1,{1,2,3,4,5,6,7,……,256},1),A1)
=FIND({"3","5","8","0","1","7","",……,""},785)
={#VALUE!,3,2,#VALUE!,#VALUE!,1,……,1}
用MID分解字符串,得到一个数组,大家已经很熟悉了,由B1单元格数字得到一个数组:{"3","5","8","0","1","7","",……,""}。然后用FIND查找数组中每个数据在A1单元格数字中的位置,先查找"3",A1中没有3,那么结果是错误值#VALUE!,接下来找"5",在A1单元格数字的第3个位置,结果便是3,再找"8",结果是2,依次找下去,当查找空值""时,结果都是1,这可以理解为用FIND找空值,空值永远在字符串第1个位置。
2、ISERR(FIND(MID(B1,COLUMN(1:1),1),A1))
=ISERR({#VALUE!,3,2,#VALUE!,#VALUE!,1,……,1})
={TRUE,FALSE,FALSE,TRUE,TRUE,FALSE,……,FALSE}
我们又遇到一个信息函数ISERR,它是检测一个数据是否为错误值(#N/A以外),如果是错误值返回TRUE,不是错误值返回FALSE,形象地理解为:错的就是对的,对的成了错的,真是“真亦假来假亦真,假亦真来真亦假”。
3、ARGE(ISERR(FIND(MID(B1,COLUMN(1:1),1),A1))*COLUMN(1:1),COLUMN(1:1))+1
这一步是算出查找不到的第1至256个最大值。运行后的效果:
{8,7,4,3,1,……,1}
代入公式:
=SUM(MID(0&B1,{8,7,4,3,1,……,1},1)*10^COLUMN(1:1))/10
进一步提取得到:
=SUM({"1","0","3","0"……"0"}*10^COLUMN(1:1))/10
再运算:
=SUM({10,0,3000,0,……,0})/10
=301
例②A2:A7单元格输入:
★★★★★★★★★欢迎光临我的百度空间★★★★★★★★★★
∽∽∽∽∽∽∽∽∽∽我的函数主题与大家分享∽∽∽∽∽∽∽∽∽∽
1235云南
【】☆‰℃丂丮云南大理镕DAW12
123OP4ABYTQRTON?
【】龥县丮云南大理丂☆‰℃%
问题:要求取出单元格内的汉字字符串:
=MID(A2,MATCH(TRUE,MID(A2&"咗",ROW($1:$50),1)>="吖",),SUM(N(MID(A2,ROW($1:$50),1)>="吖")))
变通为:
=MID(A2,MATCH(1>0,MID(A2&"咗",ROW($1:$50),1)>="吖",),SUM(N(MID(A2,ROW($1:$50),1)>="吖")))
例③分数评级
假定考分>=85的为”A”,>=70的为”B”, >=60的为”C” 其余的为”D”
则公式为(当然有好多公式可写,但这是本文需要这样写):
=CHAR((A1<=100)+(A1<85)+(A1<70)+(A1<60)+64)
解析: 假定A1中输入成绩80,则公式在运算中演变成:
=CHAR(TRUE+TRUE+FALSE+FALSE+64),试中TRUE参与计算则为1,FALSE参与运算则0,由于我们知道大写字母从”A”开始,它的字符集数字代码是从65开始的。因此当满足一个条件时是1,再加64刚好就是65,然后用CHAR函数返回字母。

八、瞒天过海(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$2:$A$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滚蛋,滚蛋了不就成了“卖鸡蛋的跌倒—没有一个好的”,这“云南十八怪中的鸡蛋用草串着卖”也就怕滚蛋了。

七、“0”的伪装(TRUE,FALSE)

例①A1单元格是785,B1单元格是358017。如何从B1中将A1的7、8、5替换掉,在C1得出301
=SUM(MID(0&B1,LARGE(ISERR(FIND(MID(B1,COLUMN(1:1),1),A1))*COLUMN(1:1),COLUMN(1:1))+1,1)*10^COLUMN(1:1))/10

公式分析:
1、FIND(MID(B1,COLUMN(1:1),1),A1)
=FIND(MID(B1,{1,2,3,4,5,6,7,……,256},1),A1)
=FIND({"3","5","8","0","1","7","",……,""},785)
={#VALUE!,3,2,#VALUE!,#VALUE!,1,……,1}
用MID分解字符串,得到一个数组,大家已经很熟悉了,由B1单元格数字得到一个数组:{"3","5","8","0","1","7","",……,""}。然后用FIND查找数组中每个数据在A1单元格数字中的位置,先查找"3",A1中没有3,那么结果是错误值#VALUE!,接下来找"5",在A1单元格数字的第3个位置,结果便是3,再找"8",结果是2,依次找下去,当查找空值""时,结果都是1,这可以理解为用FIND找空值,空值永远在字符串第1个位置。
2、ISERR(FIND(MID(B1,COLUMN(1:1),1),A1))
=ISERR({#VALUE!,3,2,#VALUE!,#VALUE!,1,……,1})
={TRUE,FALSE,FALSE,TRUE,TRUE,FALSE,……,FALSE}
我们又遇到一个信息函数ISERR,它是检测一个数据是否为错误值(#N/A以外),如果是错误值返回TRUE,不是错误值返回FALSE,形象地理解为:错的就是对的,对的成了错的,真是“真亦假来假亦真,假亦真来真亦假”。
3、ARGE(ISERR(FIND(MID(B1,COLUMN(1:1),1),A1))*COLUMN(1:1),COLUMN(1:1))+1
这一步是算出查找不到的第1至256个最大值。运行后的效果:
{8,7,4,3,1,……,1}
代入公式:
=SUM(MID(0&B1,{8,7,4,3,1,……,1},1)*10^COLUMN(1:1))/10
进一步提取得到:
=SUM({"1","0","3","0"……"0"}*10^COLUMN(1:1))/10
再运算:
=SUM({10,0,3000,0,……,0})/10
=301
例②A2:A7单元格输入:
★★★★★★★★★欢迎光临我的百度空间★★★★★★★★★★
∽∽∽∽∽∽∽∽∽∽我的函数主题与大家分享∽∽∽∽∽∽∽∽∽∽
1235云南
【】☆‰℃丂丮云南大理镕DAW12
123OP4ABYTQRTON?
【】龥县丮云南大理丂☆‰℃%
问题:要求取出单元格内的汉字字符串:
=MID(A2,MATCH(TRUE,MID(A2&"咗",ROW($1:$50),1)>="吖",),SUM(N(MID(A2,ROW($1:$50),1)>="吖")))
变通为:
=MID(A2,MATCH(1>0,MID(A2&"咗",ROW($1:$50),1)>="吖",),SUM(N(MID(A2,ROW($1:$50),1)>="吖")))
例③分数评级
假定考分>=85的为”A”,>=70的为”B”, >=60的为”C” 其余的为”D”
则公式为(当然有好多公式可写,但这是本文需要这样写):
=CHAR((A1<=100)+(A1<85)+(A1<70)+(A1<60)+64)
解析: 假定A1中输入成绩80,则公式在运算中演变成:
=CHAR(TRUE+TRUE+FALSE+FALSE+64),试中TRUE参与计算则为1,FALSE参与运算则0,由于我们知道大写字母从”A”开始,它的字符集数字代码是从65开始的。因此当满足一个条件时是1,再加64刚好就是65,然后用CHAR函数返回字母。

八、瞒天过海(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$2:$A$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滚蛋,滚蛋了不就成了“卖鸡蛋的跌倒—没有一个好的”,这“云南十八怪中的鸡蛋用草串着卖”也就怕滚蛋了。

七、“0”的伪装(TRUE,FALSE)

例①A1单元格是785,B1单元格是358017。如何从B1中将A1的7、8、5替换掉,在C1得出301
=SUM(MID(0&B1,LARGE(ISERR(FIND(MID(B1,COLUMN(1:1),1),A1))*COLUMN(1:1),COLUMN(1:1))+1,1)*10^COLUMN(1:1))/10

公式分析:
1、FIND(MID(B1,COLUMN(1:1),1),A1)
=FIND(MID(B1,{1,2,3,4,5,6,7,……,256},1),A1)
=FIND({"3","5","8","0","1","7","",……,""},785)
={#VALUE!,3,2,#VALUE!,#VALUE!,1,……,1}
用MID分解字符串,得到一个数组,大家已经很熟悉了,由B1单元格数字得到一个数组:{"3","5","8","0","1","7","",……,""}。然后用FIND查找数组中每个数据在A1单元格数字中的位置,先查找"3",A1中没有3,那么结果是错误值#VALUE!,接下来找"5",在A1单元格数字的第3个位置,结果便是3,再找"8",结果是2,依次找下去,当查找空值""时,结果都是1,这可以理解为用FIND找空值,空值永远在字符串第1个位置。
2、ISERR(FIND(MID(B1,COLUMN(1:1),1),A1))
=ISERR({#VALUE!,3,2,#VALUE!,#VALUE!,1,……,1})
={TRUE,FALSE,FALSE,TRUE,TRUE,FALSE,……,FALSE}
我们又遇到一个信息函数ISERR,它是检测一个数据是否为错误值(#N/A以外),如果是错误值返回TRUE,不是错误值返回FALSE,形象地理解为:错的就是对的,对的成了错的,真是“真亦假来假亦真,假亦真来真亦假”。
3、ARGE(ISERR(FIND(MID(B1,COLUMN(1:1),1),A1))*COLUMN(1:1),COLUMN(1:1))+1
这一步是算出查找不到的第1至256个最大值。运行后的效果:
{8,7,4,3,1,……,1}
代入公式:
=SUM(MID(0&B1,{8,7,4,3,1,……,1},1)*10^COLUMN(1:1))/10
进一步提取得到:
=SUM({"1","0","3","0"……"0"}*10^COLUMN(1:1))/10
再运算:
=SUM({10,0,3000,0,……,0})/10
=301
例②A2:A7单元格输入:
★★★★★★★★★欢迎光临我的百度空间★★★★★★★★★★
∽∽∽∽∽∽∽∽∽∽我的函数主题与大家分享∽∽∽∽∽∽∽∽∽∽
1235云南
【】☆‰℃丂丮云南大理镕DAW12
123OP4ABYTQRTON?
【】龥县丮云南大理丂☆‰℃%
问题:要求取出单元格内的汉字字符串:
=MID(A2,MATCH(TRUE,MID(A2&"咗",ROW($1:$50),1)>="吖",),SUM(N(MID(A2,ROW($1:$50),1)>="吖")))
变通为:
=MID(A2,MATCH(1>0,MID(A2&"咗",ROW($1:$50),1)>="吖",),SUM(N(MID(A2,ROW($1:$50),1)>="吖")))
例③分数评级
假定考分>=85的为”A”,>=70的为”B”, >=60的为”C” 其余的为”D”
则公式为(当然有好多公式可写,但这是本文需要这样写):
=CHAR((A1<=100)+(A1<85)+(A1<70)+(A1<60)+64)
解析: 假定A1中输入成绩80,则公式在运算中演变成:
=CHAR(TRUE+TRUE+FALSE+FALSE+64),试中TRUE参与计算则为1,FALSE参与运算则0,由于我们知道大写字母从”A”开始,它的字符集数字代码是从65开始的。因此当满足一个条件时是1,再加64刚好就是65,然后用CHAR函数返回字母。

八、瞒天过海(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$2:$A$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滚蛋,滚蛋了不就成了“卖鸡蛋的跌倒—没有一个好的”,这“云南十八怪中的鸡蛋用草串着卖”也就怕滚蛋了。

十一、眼见为虚,验证为实(单元格格式简单运用)
单元格格式的设置顺序excel默认:"正数;负数;零;文本",中间用英文分号相隔。并且还可以设置颜色(颜色是TEXT函数不具备的),如单元格格式:"[红色]我;[绿色]爱;[黄色]\exc\el;[蓝色]\ho\m\e"设置后分别输入"正数;负数;零;文本"试试,是不是很有趣哦!输入正数时显示红色的"我";负数时显示绿色的"爱";零时显示黄色的"excel";文本时显示蓝色的"home",显示结果不是真实存在的,是你的眼睛在欺骗你,它并没有改变其本身的值或字符,所以眼见为虚,验证为实。是每个excel 人所必须弄清的。
1、单元格格式中的"0",往往是通配数字。如:
输入19980823,要显示为日期格式,则单元格格式:"0-00-00"(即月份和日期都是两位数,剩下的位数为年份),单元格格式也可以写成:" 0年00月00日"
2、单元格格式中的隐藏大法
①隐藏0值,单元格格式:"[=]g"
②隐藏负值:"G/通用格式;"
③隐藏小于0的值:"G/通用格式;;"
④隐藏正值:" ;-G/通用格式;0"
⑤隐藏0值和正值:" ;-G/通用格式"
⑥隐藏数值:" ;;;@"(仅显示文本)
另外,经测试,隐藏数值的单元格格式还可以写成:""""(只输入""),但缺陷是当输入负值时会显示"-"号没有完全隐藏负值。
⑦隐藏文本:" [<>]G/通用格式;;0;" ,也可以:"G/通用格式;-G/通用格式;0;"
⑧全部隐藏:";;;"
3、改变默认设置法:
例如:
学生成绩<60的为不及格,<85的为及格,>=85的为优秀,则单元格格式为:" [<60]不及格;[<85]及格;优秀",单元格格式还可以再加上颜色:" [红色][<60]不及格;[黄色][<85]及格;[蓝色]优秀",还可以按照条件只设置颜色,不显示等级,单元格格式:" [红色][<60];[黄色][<85];[蓝色]",如果中间再加一个等级,>=70且<85的为良,单元格格式就无能为力了,因为单元格格式最多能将数值分为三段,第四段是文本格式.
4、占位符(”\”和”!”)

是指EXCEL规定有特殊含义或者说有特殊用途的字符而言的,当在单元格格式中(或者说在TEXT条件参数中)输入这些字符时,EXCEL赋予“她”特殊使命,当不需要“她”完成特殊使命,只作“平民”身份出现时,就用占位符(”\”和”!”)命令“她”,使“她”显示出本来面目。具有特殊使命的字符有:
A星期
B佛历年份
D日期
E科学计数,小"e"是年份(使用时要注意区分)
H小时
M月份和分钟
S秒
Y年份
@文本
#数字
0数字
如:A1:A3单元格中都输入40000(日期:2009-7-6的序列数),单元格格式分别设置为:e,m,d,是不是分别显示为:2009(年),7(月),6(日)了,如果要将“她”还原为“平民”身份,就分别用\e,\m,\d(用!e,!m,!d也是一样的),结果就显示字母本身了。
如上述,学生成绩<60的为不及格,<85的为及格,>=85的为优秀,单元格格式:” [<60]不及格;[<85]及格;优秀”,就是人为的定义格式,当然还可以” [>=85]优秀;[<60]不及格;及格”和” [>=85]优秀;[>=60]及格;不及格”这些格式的顺序由用户自己定义,改变EXCEL的默认设置,也就是我们EXCEL人自己赋予“她”的用途。由于这些字符EXCEL没有特殊含义或者说没有特殊用途,所以不需要用占位符。

EXCEL博大精深,仅就一个0,也只写了冰山一角,如果没有养成独立思考习惯,平时又不知道积累知识点,只会发一堆废铁(贴),缺乏探索创新,是永远成不了好钢的。在困难面前誓不低头,世上才有了徒手攀岩的“蜘蛛人”,作为合格的EXCEL人应该具备“蜘蛛人”应有的品质,面对技术难题,从0开始,不畏艰险,敢于“亮剑”,勇攀“E”峰。只要坚持,坚持,再坚持,才能享受到“举头红日白云低”的成功喜悦。

标签:

给我留言

Copyright © 龙网博客 保留所有权利.   Theme  Ality

用户登录