|
|
楼主 |
发表于 2010-9-30 23:18
|
显示全部楼层
本帖最后由 wshcw 于 2014-5-8 14:32 编辑
（上接35楼：http://club.excelhome.net/viewthread.php?tid=614168&page=3#pid4175592）
七、“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、LARGE(ISERR(FIND(MID(B1,COLUMN(1:1),1),A1))*COLUMN(1:1),COLUMN(1:1))+1
这一步是算出查找不到的第1至256个最大值。运行后的效果：
{6,5,2,1……,1}
代入公式：
=SUM(MID(0&B1,{6,5,2,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
123ＯＰ4ABYTＱＲＴＯＮٷ
【】龥县丮云南大理丂☆‰℃%
问题：要求取出单元格内的汉字字符串：
=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)))&""
转118楼：
http://club.excelhome.net/viewthread.php?tid=614168&page=8#pid4317698
[ 本帖最后由 wshcw 于 2010-10-14 21:39 编辑 ]
|
|
楼主: wshcw
[复制链接]
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2010-9-30 12:29
发表于 2010-9-30 19:31
