[已解决]一个神奇的公式，求高手详细解释

su0nils000 · 发表于 2011-9-3 17:50

=LOOKUP(9E+307,--MID(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1)))))
，该公式适用于提取文本中左起第一次出现开始的连续数字。
这里将我理解的和不理解的一并列出，共同学分享，求高手解释

1.9E+307,是excel能表示的接近最大数，可以看做无穷大吧，在文本中查找会落到一串数字的后面，因为不存在这个几乎无穷大的数。
2.--MID(),这个看不懂，为啥一定要加这个“--”？
3.后边的A1&1234567890是为了防止字符串不含数字而导致出错
4.ROW(INDIRECT("1:"&LEN(A1)))是用来表示一个数字的连续长度吗？怎么理解呢？没有看明白

最佳答案

月排行榜 / 总排行榜

lisachen

2011-9-3 22:31

本帖最后由 lisachen 于 2011-9-3 22:33 编辑

以A1为A1233举例：
如果A1为A1233,INDIRECT("1:"&LEN(A1))返回INDIRECT("1:5")
ROW(INDIRECT("1:"&LEN(A1)))返回1到A1单元格数据的长度，比A1为A1233则返回｛1；2；3；4；5｝
MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&1234567890)返回出现在A1单元格中的第一个数字的位置
MID(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1))))，则是从单元格A1中取从第一个出现数字的位置开始的1至单元格长度的字符串，如a1233,择取1，12，123，1233，1233，从小到大排列
‘--mid目的是把上一部取得的字符串转为数字
9E+307是数字中的极大值
最后通过lookup，找到以上步骤计算出的最后一个值，也就是1233

跳转到最佳答案楼层

su0nils000 · 发表于 2011-9-3 18:12

自己顶！不能沉啊！

qxb · 发表于 2011-9-3 18:47

公式太长了，看了都头晕

我一定会回来的 · 发表于 2011-9-3 18:58

--mid，是取中间从第几个开始共几个数，前面加两个负号可以把数字的文本类型运算为数值型；
ROW(INDIRECT("1:"&LEN(A1)))是代表从第一行到第N行，N代表A1单元格的长度。

su0nils000 · 发表于 2011-9-3 21:58

本帖最后由 su0nils000 于 2011-9-3 22:02 编辑

回复我一定会回来的的帖子

很感谢你的回答，帮我解答了关于“--”的问题，但是我仍然有疑惑，lookup（）函数应该是在一组数据或数组中查找特定值并返回相应数值，但上面给出的公式里面“--mid（。。。）”函数明显不是一个数组啊？lookup函数如何完成返回数据的工作的？另外,ROW(INDIRECT("1:"&LEN(A1))这一部分也是很困惑，这组函数是什么意思，您的解释我明白，在字面上也能理解，但是它包括在MID函数中的用途是什么？并不是一个数值的长处啊？它的返回的值好像是“1”，这有什么意义吗？

lisachen · 发表于 2011-9-3 22:31

这个最佳答案由 lisachen 给出，感谢 lisachen 的回答。

单击隐藏图章

本帖最后由 lisachen 于 2011-9-3 22:33 编辑

以A1为A1233举例：
如果A1为A1233,INDIRECT("1:"&LEN(A1))返回INDIRECT("1:5")
ROW(INDIRECT("1:"&LEN(A1)))返回1到A1单元格数据的长度，比A1为A1233则返回｛1；2；3；4；5｝
MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&1234567890)返回出现在A1单元格中的第一个数字的位置
MID(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1))))，则是从单元格A1中取从第一个出现数字的位置开始的1至单元格长度的字符串，如a1233,择取1，12，123，1233，1233，从小到大排列
‘--mid目的是把上一部取得的字符串转为数字
9E+307是数字中的极大值
最后通过lookup，找到以上步骤计算出的最后一个值，也就是1233

我一定会回来的 · 发表于 2011-9-3 23:10

首先查出数值0，1.....等的相应位置find（大括号里是数组），找到数值的位置后，用MIN求出最先出现的数值的位置，然后用ROW来截取数值长度，分N次截取（N为A1的长度），MID就是利用MIN先出现的数值开始截取数字，长度分别为1到N（这是一个数组，N个数，N取决于A1的长度），最后用LOOKUP求值。如A1的内容为ad23d56，FIND分别找出数值的位置，MIN求出最先出现为第3个，从第3个截取值，长度分别为1到7，最后用LOOKUP在这7个值中求相应的值。

我一定会回来的 · 发表于 2011-9-3 23:12

建议在单元格A1中输入一个值后，单击菜单的“工具”-“公式审核”-“公式求值”，一步步查看过程。

su0nils000 · 发表于 2011-9-3 23:42

灰常灰常感谢二位的耐心开导！excel用了这么多年，嗨，这才看见了差距啊！

su0nils000 · 发表于 2011-9-4 00:24

“我一定会回来的”提供的方法：公式求值非常好用，终于明白了这个函数的意思，驽钝了点，呵呵。。。。。

		自动登录	找回密码
密码			注册

[已解决]一个神奇的公式，求高手详细解释

评分