[开_28] 如何取出字符串中的数字[已总结] ★★★★

chenjun

最后补充的例子中，既然A1中的字符串是以空格" "分隔，定义名称成x=";"&SUBSTITUTE(Sheet1!$A$1," ",";")&";"烦了点，直接用空格 x=" "&Sheet1!$A$1&" " 就可以了，以“;”分隔和以空格分隔原理不就是一样的吗！ 另请再看我77楼中说的LOOKUP函数排序问题。

yigepure

以下是引用chenjun在2005-6-4 16:58:00的发言： 最后补充的例子中，既然A1中的字符串是以空格" "分隔，定义名称成x=";"&SUBSTITUTE(Sheet1!$A$1," ",";")&";"烦了点，直接用空格 x=" "&Sheet1!$A$1&" " 就可以了，以“;”分隔和以空格分隔原理不就是一样的吗！ 另请再看我77楼中说的LOOKUP函数排序问题。

以下是引用yigepure在2005-6-4 16:03:00的发言：

当然了也可以不要SUBSTITUTE(," ",";")这个公式了.直接将&";"改为&" ",其它的依此法修改.只不过我看这个空格不舒服.所以才替换的.呵呵:)

是的,这个我已经说明了一下:)

其实大哥所指的正是LOOKUP()的关键之处.

我们用它常用在哪呢?仔细想想常规的用法除了LOOKUP(12,{0,15,50},{"a","b","C"})之外,我发现它再无什么好的用处了.为什么呢?正因为它所默认的数据的排列.否则出错.对于真正要处理的数据,很多都不会是排列好的.真正排列好的.也许VLOOKUP()等更适宜.而且公式的扩展性也很好.从这上LOOKUP()尚有局限性.

予盾总是双面性的.利也许是弊,弊也许是利.也正是利用这点.我们才可以完成一些另类的查找. 举一个相当简单的例子.如找到A1:A20的最后出现的数值.为了方便说明将它以数组的形式来表示出来{1;2;35;2;36;69;69;9.99999999999999E+307;2;1;0;"a";"b";"e";"";1;0;"";"";""}

诚然,我们不知道这个最后出现的数值是多少.所以要用最大数据来查找9.99999999999999E+307,而且正是利用它的这个"数据是按升序排列"的特点来做.(首先明确一点:它返回的是小于或等于它的最大数值).那么公式将是 =LOOKUP(9.99999999999999E+307,{1;2;35;2;36;69;69;9.99999999999999E+307;2;1;0;"a";"b";"e";"";1;0;"";"";""})

这里还要再说明一点的就是(LOOKUP()忽略错误值及空格(值).只查找第一个参数所对应数据类型) 那么上面的这个公式返回的是多少呢.返回值是0.为什么会是0了.9.99999999999999E+307不是里面最大的数据吗?应该返回它.呵呵.当然9.99999999999999E+307是最大的数值.但是你不要忘了.LOOKUP()是默认以升序排序的.9.99999999999999E+307之后还有数据出现.所以它认为最后出现的这个数值是最大的数值.正是利用它这个错误.我们来解决一些相应的问题.

将此问题再作修改一下.找到A1:A20的最后出现的数值对应的行号. =LOOKUP(9.99999999999999E+307,A1:A20,ROW(A1:A20)) 这样就找出了最后出现数值的行号(为17). 它的更深一层的利用在哪里呢?数据有效性等地方都见它的踪影. 文本也是一样的查找原理.不过对于一些特殊的问题.它具有更深一层的利用价值. 如:有四个表.要在总表里返回"A"所对应的型号,厂家.等.(有两种情况.可能没有或是只有唯一值)

对于数据等当然了可以用SUMIF()的三维引用来返回它的值.但是对于文本.怎么办.常用的方法是VLOOKUP(),相信大家都看过"定义名称来解决七层嵌套"这个帖子吧.但是如果用IF(ISNA())这样的方法是否过于繁杂了呢? 那么这里就体现了LOOKUP()的价值了.公式(说明经bosco_yip兄研究发现:REPT("龠",255)是最大的中文(对于英文同样也适用.我经过证实)大家也可以去证实一下 =LOOKUP(REPT("龠",255),CHOOSE({1,2,3,4,5},"",VLOOKUP("A",'表1'!A:B,2,0),VLOOKUP("A",'表2'!A:B,2,0),VLOOKUP("A",'表3'!A:B,2,0),VLOOKUP("A",'表4'!A:B,2,0)))

声明:此方法是bosco_yip兄第一个使用的.(至少在我看过的论坛上)

VLOOKUP()查找如果四个表都没有将返回N/A.然多增加一个"".这样将返回"".当然了要将它放在所有VLOOKUP()的最前面.而且最重要的一点是.LOOKUP()也是数组函数.功同SUMPRODUCT(),可以不用三键结束.当然了还有一些特殊的情况.有兴趣的朋友,请看讨论帖之"何谓数组公式".比较一下LOOKUP()的这个公式是更简洁明了了呢?呵呵:)

偶帮你编辑一下，空行太多了，看的好累。yigepure兄的功力与日俱增，佩服！:)——apolloh

[此贴子已经被作者于2005-6-4 17:52:31编辑过]

yigepure

说到这里.我们不能将MATCH()置之不理了.仔细看一下就会发现MATCH()与LOOKUP()的应用有非常相似之处.然现在论坛上利用MATCH()的这特性来解决数据有效性的问题真是比比皆是.

另LOOKUP()的这种用法也是非常之多的.

=LOOKUP(2,1/(A1:A20="a"),ROW(A1:A20))

它的原理与前面的公式的原理是一样的.先写到这里吧.其实说到这个LOOKUP()的用法.我也是发了几个星期的时间来了解它.现在只是有点了解.如有言之不妥处.请各位指出.

不知陈军大哥所指是否是这些.如有进一步的东西.可以探讨.

[此贴子已经被作者于2005-6-4 17:55:12编辑过]

apolloh

老大所说的是，与lookup所查找值对应的数组中，其数据类型与查找值对应的元素需要排序，其他类型的数据是忽略的。

yigepure所利用的则是lookup在找不到对应值的情况下，会返回最后一个类型匹配的对应值。

两者并不矛盾。

[此贴子已经被作者于2005-6-5 8:41:18编辑过]

chenjun

你82楼的解释结论是对的，但可能大部分人看不懂的，复杂了。 实际是LOOKUP函数对数值、文本、错误、空白格（只在以引用作第2参数时有效）是分别处理的，先忽略第2参数中的错误及空白格（如果是引用参数），然后看第1参数是文本还是数值，再忽略第2参数中的数值和文本、从第2参数的第1行或列（依据这个参数的行列数大小区分）向前搜索，查到第1次出现小于等于第1参数的值作为返回值。 这个实际上对第2参数是没有排序要求的，但MS帮助中说要升序是因为除了我们几个版主这样的用法外，绝大多数用户是用来真正查找数据的，不按升序排列就会返回错误的结果。

yigepure

以下是引用chenjun在2005-6-4 17:58:00的发言： 实际是LOOKUP函数对数值、文本、错误、空白格（只在以引用作第2参数时有效）是分别处理的，先忽略第2参数中的错误及空白格（如果是引用参数），然后看第1参数是文本还是数值，再忽略第2参数中的数值和文本、从第2参数的第1行或列（依据这个参数的行列数大小区分）向前搜索，查到第1次出现小于等于第1参数的值作为返回值。

对于后面的(如果是引用参数)这句话,我仍有一点不同意见

如输入此公式

=LOOKUP(9.99999999999999E+307,{1;2;35;2;36;69;69;9.99999999999999E+307;#N/A;#NAME?;0;"a";"b";"e";0;"";#N/A;#NAME?;#N/A;#N/A},{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20})

看一下结果是否是15.那当然了对于查找文本型数据,可以忽略错误值,并不忽略空值,如下面这个公式=LOOKUP(REPT("z",255),{1;2;35;2;36;69;69;9.99999999999999E+307;#N/A;#NAME?;0;"a";"b";"e";0;"";#N/A;#NAME?;#N/A;#N/A},{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20})

返回值为16.

[此贴子已经被作者于2005-6-4 18:11:57编辑过]

yigepure

增加两个说明LOOKUP()的例子.

7cN850jI.rar (3.57 KB, 下载次数: 155)

2005-6-4 19:19 上传

点击文件名下载附件
[函数19] 测试题:如何取出字符串中的数字[已总结并再次增加一例] ★★★★

这个是一个网友的问题.也不记得是哪个帖了.当然了用IF(ISNA(VLOOKUP()),)也可以.不过为了说明LOOKUP()当表多时使用时会更见效果的

apolloh

以下是引用yigepure在2005-6-4 19:18:00的发言：

增加两个说明LOOKUP()的例子.

这个是一个网友的问题.也不记得是哪个帖了.当然了用IF(ISNA(VLOOKUP()),)也可以.不过为了说明LOOKUP()当表多时使用时会更见效果的

第二个附件，你希望用if的方法如下，我稍微修改一下，没有考虑优化。

=LOOKUP(9.99E+307,IF({1;1;0},IF({1,0},0,VLOOKUP(E$6,data,4,0)),VLOOKUP($A9,材料!$A$1:$B$4,MATCH(Sheet1!E$6,材料!$1:$1,),0)))

chenjun

以下是引用yigepure在2005-6-4 18:11:00的发言：

对于后面的(如果是引用参数)这句话,我仍有一点不同意见 如输入此公式 =LOOKUP(9.99999999999999E+307,{1;2;35;2;36;69;69;9.99999999999999E+307;#N/A;#NAME?;0;"a";"b";"e";0;"";#N/A;#NAME?;#N/A;#N/A},{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}) 看一下结果是否是15.那当然了对于查找文本型数据,可以忽略错误值,并不忽略空值,如下面这个公式=LOOKUP(REPT("z",255),{1;2;35;2;36;69;69;9.99999999999999E+307;#N/A;#NAME?;0;"a";"b";"e";0;"";#N/A;#NAME?;#N/A;#N/A},{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}) 返回值为16.

2个公式的计算结果是15和16，没有问题，引用中的空白格是指引用单元格区域中的空单元格，上面公式中的""不叫空白格叫0长度字符实际是个文本，回头再看我的解释是不是好理解了。

yigepure

以下是引用apolloh在2005-6-4 19:50:00的发言： >

第二个附件，你希望用if的方法如下，我稍微修改一下，没有考虑优化。

=LOOKUP(9.99E+307,IF({1;1;0},IF({1,0},0,VLOOKUP(E$6,data,4,0)),VLOOKUP($A9,材料!$A$1:$B$4,MATCH(Sheet1!E$6,材料!$1:$1,),0)))

发现公式一点小问题.就是第二个IF({1,0}就为IF({1;0}

另.这里的LOOKUP()不用三键结束.是因为用的是常量数组吧:)