[讨论]关于MATCH及LOOKUP的疑问

willin2000 · 发表于 2006-12-16 16:21

附件里LOOKUP/MATCH/FREQUENCY为什么对同一个数组的表现却不一样呢:

QUOTE:

另外一个问题 <关于SUM的疑问> 已单独开帖:

[讨论]关于SUM的疑问

[此贴子已经被作者于2006-12-17 20:57:58编辑过]

willin2000 · 发表于 2006-12-16 18:10

我自己隐约觉得这和EXCEL编码解释器的参数获得方式和计算顺序有关.

欢迎大家讨论交流.

[此贴子已经被作者于2006-12-17 16:16:04编辑过]

gouweicao78 · 发表于 2006-12-17 00:27

关于1楼的问题——定义名称至今仍有许多令人不解的秘密，尤其是定义名称对数组的运算机理到底是如何，尚未有一个明确的定论。有时直接用公式可以完成的，但把公式中某一部分定义为名称却不行。

关于2楼Lookup和Match出错的问题，经过反复测试比较，个人认为问题出在Indirect函数上。

一步步简化B1:B6中的公式，总之就是让它得到的还是1、2、……6，但公式不同就有情况出现了：

用了Indirect之后，哪怕是最最简单的=indirect("A1:A6")来得到1~6，C列的Lookup公式都义无反顾地返回了#N/A，用鼠标拖动任意一个单元格到另外的位置，E列的Match公式也义无反顾地返回#N/A错误，双击某单元格或者按F9等引起重新计算的动作之后，Match公式改邪归正。

但是，正如willin2000兄发现的那样，将B1:B6的公式代入C列的Lookup或E列的Match，都不会出现问题。

这说明了什么呢？是否与Indirect函数的易失性有关？

[此贴子已经被作者于2006-12-17 0:30:59编辑过]

willin2000 · 发表于 2006-12-17 00:37

QUOTE:

以下是引用gouweicao78在2006-12-17 0:27:24的发言：

关于1楼的问题——定义名称至今仍有许多令人不解的秘密，尤其是定义名称对数组的运算机理到底是如何，尚未有一个明确的定论。有时直接用公式可以完成的，但把公式中某一部分定义为名称却不行。

关于2楼Lookup和Match出错的问题，经过反复测试比较，个人认为问题出在Indirect函数上。

.....

这说明了什么呢？是否与Indirect函数的易失性有关？

我也有过怀疑易失性的问题,但F列的FREQUENCY一直稳稳当当的.

gouweicao78 · 发表于 2006-12-17 00:52

QUOTE:

以下是引用willin2000在2006-12-17 0:37:46的发言：

我也有过怀疑易失性的问题,但F列的FREQUENCY一直稳稳当当的.

把B1:B6改为=OFFSET(A1,,,6)、或者=NOW()^0*ROW()、或者=RAND()^0*ROW()、或=RAND()*0+ROW()等

也都是同样问题，这更能说明易失性函数的影响吧。

至于Frequency为何稳稳当当，Match为何见风使舵，Lookup为何执迷不悟——估计还是与函数本身有关了。

综合几个易失性函数的表现来看，倒是否定了我刚才认为是易失性引用函数在单元格拖动产生的影响的想法，现在觉得：

应该是Match函数对由易失性函数公式所在单元格的引用作为数组参数而且是第一参数时，则表现出一种类似宏表函数不能自动更新的特性（但绝对不是，比如=MATCH($B$1:$B$6,$B$1:$B$6,0)+0*NOW()得到的就是义无反顾的#N/A错误了）。

而Lookup表现出的特性、Frequency表现出的特性都不相同。

[此贴子已经被作者于2006-12-17 1:06:24编辑过]

willin2000 · 发表于 2006-12-17 01:28

似乎和易失性函数有关是肯定的了.

但为什么:

1.只要编辑单元格用ENTER输入MATCH一直是对的?

2.Match/Lookup/Frequency对易失性的表现不同呢?

3.同样是MATCH为什么D列一直是正确的?

[此贴子已经被作者于2006-12-17 16:17:08编辑过]

gouweicao78 · 发表于 2006-12-17 01:36

这只砂锅一时半会儿恐怕也破不了，呵呵。

在5楼黑体字部分，我给这个现象下了好几个定语，这些基本都是证实了的，希望对研究者有帮助。

1、包含易失性函数的单元格；2、这些单元格作为Lookup或者Match的第一参数；3、而且是作为数组参数的形式。

czzqb · 发表于 2006-12-17 14:54

应该是Match函数对由易失性函数公式所在单元格的引用作为数组参数而且是第一参数时，则表现出一种类似宏表函数不能自动更新的特性

这种“类似宏表函数不能自动更新的特性”，还有它自己的特点：

1,只有剪切、移动、行删除、列删除，才会变成#N/A，而其他的操作不会

2,宏表函数的不能更新可以+0*NOW()来“矫正”，而这个不行。

另外+0*A1(A1为常数)，没有改变它的性质（可以按F9或其他操作来更新）；但如果+0*B1(B1为易失函数公式得到的结果)，就义无反顾地#N/A了。

——而很有意思的是，如果加上的是“半易失函数”会怎样？

+0*(A1:INDEX(A1:A6,N))，N分别取1到6作6次试验，我们会得到5种不同的结果。

[真正形象地表现出“半”易失！]

+0*ROW(A1:An) n=1~6 ，也有相同的结果

而+0*COLUMN(A1:B1)等，却不会

而真正的半易失函数ROWS()、COLUMNS()却有不同的表现

类似的问题，已经有过多次了，如：

http://club.excelhome.net/viewthread.php?tid=206239&px=0

[此贴子已经被作者于2006-12-17 15:40:50编辑过]

gouweicao78 · 发表于 2006-12-17 15:21

谢谢czzqb兄也关注此问题，并补充了这么多测试。

我也意识到它与宏表函数不可自动更新特性的区别，所以加了“但绝对不是”以及+0*NOW()得到错误值来说明，这是曾经的思路过程，做好标记——有坑，请绕过，呵呵省得大家又费劲了。

这些测试让这个函数的谜团变得有意思了！建议：1、Lookup、Match这个问题列讨论系列。2、willin2000兄把1楼的问题跟帖到Offset的那个讨论帖。

[此贴子已经被作者于2006-12-17 15:22:54编辑过]

czzqb · 发表于 2006-12-17 15:35

再来个这个问题的老帖：

http://club.excelhome.net/viewthread.php?tid=197023&px=0

这次又有点奇怪了：问题不出在MATCH上，一直到了SMALL才出问题，而且是“义无反顾的#N/A”。

看看我下面作的分步分解：

j0T6km7X.rar (3.05 KB, 下载次数: 11)

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