[讨论]数组公式的思路?

lukelu

在《精粹》291页， 技巧170-3， 文本和数字的分离， 其中公式：

c2=right(a2,sum(len(a2)-len(substitute(a2,{0,1,2,3,4,5,6,7,8,9},))))

在这个公式的总体思路，根据前面技巧的学习，理解起来比较容易。

可是，我开始的时候，把len(substitute(a2,{0,1,2,3,4,5,6,7,8,9},)理解为，就是a2中的数字去掉之后的长度，

于是就想到，为什么要用sum函数， 不明白。 只有用公式审核，看看，计算的过程。才发现，我的理解有误。

len(substitute(a2,{0,1,2,3,4,5,6,7,8,9},)返回的是：len({"JD345","JD00345","JD00345","JD0045","JD0035","JD0034","JD00345","JD00345","JD00345","JD00345"})

进而返回：len({5,7,7,6,6,6,7,7,7,7})

len(a2)-len({5,7,7,6,6,6,7,7,7,7}) = len({2,0,0,1,1,1,0,0,0,0})

 

以上的过程是在公式审核中看到的。 让我大为感叹数组的威力。

可是，如果让我来思考，我不知如何才能想出这样好的办法，根本没有这样的思路。

 

请问《精粹》的编者和诸位专家们， 这样的思路要如何培养？

lukelu

还有，这个公式的思路如何产生的？

多谢各位高手指点。

gouweicao78

谢谢lukelu朋友对《精粹》的关注！

1、Len()-Len(Substitute())用于计算单元格中某字符的个数（参考：《精粹》P288技巧169-1）

2、Substitute()函数支持数组，但返回的也是数组。Substitute(a2,{0,1,2,3,4,5,6,7,8,9},)——就是分别把A2中的0、1、2……9替换掉。

若是把A2中的数字全部替换掉，则应该是Substitute(Substitute(A2,1,),2,)……因超过7层嵌套，需定义名称解决。（参考：《精粹》P269 技巧153-2）

3、思路的培养：宝宝刚上下来不是立刻就可以做好任何事的，包括吃奶也得练习一阵子，呵呵。

学而不思则罔，思而不学则殆——学习、思考、训练，多来论坛看看一些精华帖和解释帖，寻找别人的灵感，产生自己的灵感。

lukelu

QUOTE:

以下是引用gouweicao78在2007-4-26 10:54:18的发言：

谢谢lukelu朋友对《精粹》的关注！

1、Len()-Len(Substitute())用于计算单元格中某字符的个数（参考：《精粹》P288技巧169-1）

2、Substitute()函数支持数组，但返回的也是数组。Substitute(a2,{0,1,2,3,4,5,6,7,8,9},)——就是分别把A2中的0、1、2……9替换掉。

若是把A2中的数字全部替换掉，则应该是Substitute(Substitute(A2,1,),2,)……因超过7层嵌套，需定义名称解决。（参考：《精粹》P269 技巧153-2）

3、思路的培养：宝宝刚上下来不是立刻就可以做好任何事的，包括吃奶也得练习一阵子，呵呵。

学而不思则罔，思而不学则殆——学习、思考、训练，多来论坛看看一些精华帖和解释帖，寻找别人的灵感，产生自己的灵感。

非常感谢guoweicao78的回复！您的以上解释，通过《精粹》292页之前的内容学习， 已经明白了。

刚才好好想了一下， 发现这里的核心思路是:

通过len，substitute的组合应用，找到a2单元格中被替换数字的个数， ie， 0有两个被替换，3、4、5各有一个被替换，

被替换数字的个数，就是right函数要取值的个数。

简直太妙了！

再次感谢！

guangyp

谢谢lukelu朋友的话题，已独发一贴，[经验分享]公式思路是怎么来的？

gdliyy

QUOTE:

以下是引用lukelu在2007-4-26 8:39:07的发言：

在《精粹》291页， 技巧170-3， 文本和数字的分离， 其中公式：

c2=right(a2,sum(len(a2)-len(substitute(a2,{0,1,2,3,4,5,6,7,8,9},))))

进而返回：len({5,7,7,6,6,6,7,7,7,7})

len(a2)-len({5,7,7,6,6,6,7,7,7,7}) = len({2,0,0,1,1,1,0,0,0,0})

其实这个问题这样可能好理解一些，试想假设请问“JD00345”中几个0？
1、我们首先可能会想到Find函数，来查找0是否存在？但用Find只能定位到第1个0，不能确定有几个；同样，如果使用COUNTIF的模糊查找也只能确定是否有0，也不能确定有几个。
2、既然查找函数无法知道具体的个数，那么可能会想到使用替换函数来将0替换掉，替换为空，因此就有了Substitute的用法，使用原字符长度减去替换掉0后的长度，就是具体包含有0的个数。于是得出了Len(A1)-Len(Substitute(A1,0,))的解法。
3、有这个思路，所以才会想，要想得到所有数字个数，是否只需要将0-9的个数计算出来，就可以了。于是就有了：
=Len(A1)-Len(Substitute(A1,0,))+Len(A1)-Len(Substitute(A1,1,))+Len(A1)-Len(Substitute(A1,2,))+Len(A1)-Len(Substitute(A1,3,))...+Len(A1)-Len(Substitute(A1,9,))
4、但这样书写是否很繁杂，公式很冗长？所以才将0－9的数字写成常量数组作为参数，这样就有了：
=len(a2)-len(substitute(a2,{0,1,2,3,4,5,6,7,8,9},))，但由于参数是数组，结果只能得到各个数字的个数的数组，所以必须使用SUM函数来汇总，才能得出具体的数字总数，最后再用Right来取得连续的数字字符串。
5、通过上面的思路汇总，才产生了最后的公式：=right(a2,sum(len(a2)-len(substitute(a2,{0,1,2,3,4,5,6,7,8,9},))))

因此，这样的设计思路并非一步到位的，也是经过层层的将复杂问题分解成若干个小问题，通过逐个小问题的解决，最后汇集成最终思路，通过分解过程学习到解题的思路，逐步对函数的应用进行掌握，以后才能举一反三的运用。

当然，解决这个问题的方法有多种，例如下面的公式：
1、{=Right(A1,COUNT(--MID(A1,row(indirect("1:"&Len(A1))),1)))}
2、{=Right(A1,Match(1,0*Right(A1,row(indirect("1:"&Len(A1))))))}
3、=Mid(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),Len(A1))

[此贴子已经被作者于2007-4-26 21:58:10编辑过]

lukelu

非常感谢G版主的透彻解析， 的确， 我想大多数的学习者需要的是：

您提供的这样的思路 + 函数的用法 + 多实践 ==》熟练应用

我想我会继续深入学习《精粹》， 她给了我很多的启发， 在书店里， 基本找不到和实践如此接近的经典书籍。

我也想对于公式的思路继续和各位编者和版主探讨和请教， 应该对广大《精粹》读者提供些启发。

我在第二次生命来临之前， 得到很多的好人的支持和帮助， 让我体会到了生命的意义。 看到各位版主如此无私地帮助他人， 我也想加入这个行列， 奈何知识太浅薄了。 于是只有不停地学习。 我愿意和各位版主一道进入无我的境界。

gouweicao78

QUOTE:

以下是引用lukelu在2007-4-27 6:54:40的发言：

……我在第二次生命来临之前， 得到很多的好人的支持和帮助， 让我体会到了生命的意义。 看到各位版主如此无私地帮助他人， 我也想加入这个行列， 奈何知识太浅薄了。 于是只有不停地学习。 我愿意和各位版主一道进入无我的境界。

感动ing！不用担心知识，在我曾经认为自己是高手（那时就会IF、SUM，连SUMIF都不会）时，后来有了一点基础加入Excelhome想展示一下，发现原来Excel还有那么多自己不知道的东西，于是就在这里开始了“助人”式学习，乐此不疲。

catty_zhou

Admire!

linux118

gou版主的头像好有趣，是你家宝宝么？？？看了好几年了

QUOTE:

gouweicao78：不要简称哦，中文是“狗尾草”。呵呵。我的头像是我儿子，2周岁多 ^_^

[此贴子已经被gouweicao78于2007-11-6 17:03:58编辑过]