本帖最后由 胡剑0227 于 2012-7-4 20:57 编辑
宿主单元格-隐形的参数？
名词解释：
宿主单元格：此处指代公式所在的单元格；
在函数公式的实际运行中，我偶尔会碰到一些奇异的现象。随着对这些奇异现象的揣摩分析，我大胆地有了一个想法，我称为“宿主单元格-隐形的参数”。揣摩分析都比较浅显，希望大家一起探讨，让奇异的现象变的不再离奇。我觉得之所以感到奇异，应该就是我们对之不了解的缘故。
先就上一个ROW函数吧，这是一个比较简单的函数，但里面蕴藏了玄机。初学函数时可能对这个玄机有过思考，但只要过了初学的阶段就不太会留意这个细节了。因为，很多事物到最后都熟视无睹了。我们先来看一个图吧，这个是大家非常熟悉的。
大家看了应该没有什么感觉吧，呵呵。选中A1:A5单元格区域，在活动单元格A1中输入公式“=ROW()”，然后按<Ctrl+Enter>组合键完成公式的批量录入。这时，在A1:A5单元格中是同一个公式“=row()”，但返回值却不同!!
使用“归一化”原则（即限定其他各条件保持一致，只研究某一单独条件的变化给结果带来的影响），我们不难发现这里的不同点就是“宿主单元格”，函数的返回值受“宿主单元格”的影响，从这一点上我们是否可以大胆的猜想“宿主单元格”是一个“隐形的参数”？
也许，有些同学会说，查看帮助，“row(reference),如果省略 reference，则假定是对函数 ROW 所在单元格的引用”，这很正常呀，这就是这个函数的用法。是的，但这并没有和我们上面的猜想相违背，相反正好说明在返回函数值的过程中，“宿主单元格”的因素是被考虑进去的。因为，在公式“=row()”中根本没有透露任何引用单元格的信息。
一个ROW函数只是给了我们一个暗示，我们可以通过这个暗示来解释很多奇异的现象，下面我们再来看另一个奇怪的现象。
在上图中，我们看到COUNTIF函数的返回值同宿主单元格与参数单元格的相对位置密切相关。从COUNTIF函数的原理来看，不管宿主单元格和参数单元格的相对位置如何调整返回值应该保持一致才对，但Excel就是给我们开玩笑，而且表现出的特性非常具有规律性，可以排除巧合的可能。
于是我猜想是否可以用ROW函数的暗示来解释这个问题。ROW函数的暗示表明，函数返回值还收“宿主单元格”的位置影响，是个隐形的参数。此处，虽然“宿主单元格”巍然不动，但参数单元格的位置在调整。如果函数返回值确实是个“隐形的参数”，则必然能探测到宿主单元格和参数单元格之间的相对位置变化，于是根据其相对位置变化而调整返回值也就可以理解了。
如果这种对应关系是固定的，我们还可以好好利用。这种“数组公式”的普通录入方式（特指没有使用<Ctrl+Shift+Enter>组合键，而是用了<Ctrl+Enter>组合键）还是非常亲切的，个人感觉要比多单元格数组公式要轻便的多。它可以直接拖拉，单个删除，而多单元格数组公式就有点“组织性”太强，必须统一行动，好扩充或删除单个单元格公式比较麻烦。
不过，碰到新事物我们总是怕怕的，那有什么办法可以杜绝此类“聊斋事件”呢？对症下药！上面我们判定是由于宿主单元格和参数单元格之间的相对位置引起的，于是我们尝试把其中的一个单元格属性去除。
于是就有了两种方案：第一，屏蔽宿主的单元格属性影响；第二，剥离参数的单元格属性。起初，对于屏蔽宿主的单元格属性并没有想到什么办法。但凭经验，联想到使用F9求值同直接运用多单元格数组公式返回的函数值有时会偏差，本着“又枣没枣先打一竿子”的策略，试探着用F9求值来屏蔽宿主的单元格属性。我们发现确实达到了预想的目的。剥离参数的单元格属性，这个还不简单，最直接的方式当然是把单元格引用的参数转换成常量，方法么就是直接用F9对其求值就好了。我们发现，剥离单元格引用属性后确实不再因相对位置变化而调整了，但返回值也不再神奇。
当然，对于第二种方法我们还可以使用数组封装的方式，即通过一个数组公式将单元格引用属性封闭起来。具体如：LOOKUP(ROW($C$54:$C$57),ROW($C$54:$C$57),$C$54:$C$57) 来封装 $C$54:$C$57，可以达到和转换常量数组一样的效果，但显然要比直接转换成常量数组要具有灵性。
当然啦，上面这种情况在批量录入公式时使用的是<Ctrl+Enter>组合键，而不是<Ctrl+Shift+Enter>组合键。在上述情况中，只要使用正真的数组公式录入方式就不会出现奇异现象了。但如果所有的情况都可以用<Ctrl+Shift+Enter>组合键来终结聊斋事件，那么问题似乎也不大,但是...印象中并不是这样。
上图：
从上图我们看到，四个明明相同的公式，就是因为所处的单元格位置不同，返回值不但不同，而且还非常诡异的变幻。我判断这是他们之间互相制约引起的，是很特殊的“循环引用”，但在公式上根本就看不到“循环引用”的蛛丝马迹。大家可以用屏蔽宿主的单元格属性进行测试（就是用F9直接运算数组公式），发现返回值将是正常的。要杜绝此类聊斋事件，依然可以从两个方法入手。
第一，屏蔽宿主的单元格属性，当然啦，总不能老是用F9来计算吧。当然不是这样，我们将这个数组公式嵌套在其他公式中就可以了，详见附件（破解聊斋事件）。
第二，剥离参数的单元格引用属性，上图演示的即时通过这种方式得到的。
好了，解决方案也提出了，是否就完结了？我觉得，解决方案只是一个具体的技巧，更重要的是上面的猜想，总的一个结论就是：函数的返回值是和宿主单元格密切相关的，是函数公式的“隐形参数”。把握这一点，可以从理论上来解释一些事情。最直接的就是解释，某些多单元格数组公式的返回值和直接使用F9求得的值不同的问题。如果考虑到直接使用F9是屏蔽的宿主单元格这个重要的参数，而多单元格数组公式返回在单元格中，自然能利用宿主单元格的属性，因此他们的不同就比较容易理解了。INDEX，就是这样的一个列子，看图
上图
其实，一个多单元格数组公式，在各个单元格中公式其实是完全相同的，但返回值确实不同的
，这应该就是在返回到单元格时是考虑的宿主单元格位置的因素的。
猜想比较随意，希望大家一起讨论.
[ 本帖最后由 胡剑0227 于 2010-11-17 08:35 编辑 ]
| 
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2010-1-10 08:20
发表于 2010-1-10 10:16
楼主
，知道诡异的原因和解决办法就暂告段落。当然，有兴趣的可以继续。我觉得确实很多函数可以进行加强...