在某区域内查找某数值并返回这一数值所在行数OO问题升级OO

nbc

山版主提供的答案思路很有启发性，虽然这个答案并没有实际解决我所想要解决的问题，但至少也提供了非常宝贵的线索。

实际上我需要求得的是指定的任意数字的出现间隔的最大值，而山版主提供的似乎只是求出所有间隔的最大值所对应的数字本身。

目前，我正依照山版主提供的思路在寻找一个解决方案，如果其它达人愿意的话，也可以帮下忙，谢了。

山菊花

在区域B3:D100中查找等于F3的值，这些值的最大间隔行数：

F4=MAX(MMULT(SMALL(IF(B3:D100=F3,ROW(3:100)),ROW(INDIRECT("1:"&COUNTIF(B3:D100,F3)-1))+{0,1}),{-1;1}))

详见附件： 

QuIGbQbO.zip (7.66 KB, 下载次数: 23)

2007-3-28 21:48 上传

点击文件名下载附件
在某区域内查找某数值并返回这一数值所在行数OO问题升级OO

willin2000

small(.., {0,1}) mmult(..,{-1;1} [em17][em17]

nbc

真的是很谢谢山版主的公式呀！！！

下面是几个问题：

1.实际上，我根据山版主之前提供的思路，再加上自己的思索，已经通过加辅助行的方式取得了自己想要的结果，而且重复验证过是正确的。增加辅助行的方法虽然可行，可是寻找某一特定数字的最大间隔数就要加两列辅助行，如果我想分别显示1-50的最大间隔数，则需要加100列辅助行。

2.山版主提供的公式思考清晰明确，提供的答案在99%的情况下是正确的，但是仍有1%的情况会导致计算错误。根据山版主提供的EXCEL附件，以查找数字50为例，公式得出的结果29是正确的。但是在以下情况下，该公式的所得出的结果会与期望值不一样。

情况1：原EXCEL附件中在第14,15,19,44,73,82行内均有数字50,则行差分别为:1,4,25,29,9。此时公式得出结果29是正确的。但是，如果我们将第14,15,19行的50改为其它数字，则50只在第44,73,82行内出现。在这种情况下，针对50的最大间隔值应该是44，即前44行均未出现过数字50。而公式计算结果仍显示为29，与期望值44不一致。

情况2：原EXCEL附件中在第14,15,19,44,73,83行内均有数字50,如果在这些行中只保留一个数字50,假设只把第14行的数字50保留，其它行的数字50改为其它数字，则公式的计算结果显示为引用错误。虽然这种情况在本实例中属特例，而且在实际运用中大量数字中也极少会发生只出现一次的情况，但在这里提出可以作为一个讨论的点，比如说判断数字50在区域内的个数如果小于2个则返回空值等。

以上是本人的一些观点，还请各位达人提供意见。

再次谢谢山版主的公式，也感谢您对本人提出的这个问题的关注，谢谢了！

willin2000

情况1：.....

如果是和起始行的间隔应该是41:

=IF(COUNTIF($B$3:$D$100,$G$3)<2,"无",MAX(MMULT(SMALL(IF(($B$3:$D$100=$G$3)+(ROW($B$3:$D$100)=3),ROW($3:$100)),ROW(INDIRECT("1:"&COUNTIF($B$3:$D$100,$G$3)+2))+{0,1}),{-1;1})))

如果是和第一行的间隔才是44:

=IF(COUNTIF($B$3:$D$100,$G$3)<2,"无",MAX(MMULT(SMALL(IF(($B$3:$D$100=$G$3)+(ROW($B$3:$D$100)=3),ROW($3:$100)),ROW(INDIRECT("1:"&COUNTIF($B$3:$D$100,$G$3)+2))+{0,1}),{-1;1})))+3

nbc

willin提供的两个处理方案是针对情况二的，而且是正确处理的。但对于情况一仍有待解决。

补充一点：willin中的公式的最外层的判断函数中的小于个数应该是1,而不应该是2，因为如果是小于2,则实际上是还是会出现情况一所描述的问题，即期望值是该唯一次50出现所在行数减去源数据首行行数。

至于之前所说到的期望值44，严格上来说应该是41,只因为山版主提供的EXCEL附件中前两行是非源数据区域，所以实际的最大间隔值是41，即前41行的源数据区域内均未出现数字50.

针对情况一,应该是判断数字50在源数据中第一次出现的行数与数字50的各间隔值作比较，如果前者最大，则取前者的值，否则取后者中的最大值。

[此贴子已经被作者于2007-3-28 23:10:59编辑过]

nbc

针对刚才以上讨论的问题。

即使将willin提供的解决情况二的公式的最外层判断函数的小于个数改为1，还是会导致引用错误的出现。

实例：
数字50在源数据区域内出现2次或两次以上，只会出现情况一；
数字50在源数据区域内出现1次，出现情况一及情况二，此时的期望值应该是数字50出现的行数减去源数据首行行数；
数字50在源数据区域内出现0次，最特殊情况，此情况可以用最外层的判断函数解决。

山菊花

结果不满意，只因题目原来没这个要求。

min(IF(B3:D100=F3,ROW(1:97)))

这是第一个数的“间隔值”，用这个数与原公式结果相比较，取较大的值，即可。

注意，区域中存在目标数据的个数，分0个、1个、多个几种情况。

nbc

TO 版主

抱歉此前没把所有可能的情况说清楚，实际上我之前所列举的情况一和情况二在本人的实际运用中出现的机会是1/(9E+307)。呵呵

之所以把这两种情况提出来并不是刻意挑毛病，只是我觉得不如把问题的所有可能性都考虑一下来验证公式是否符合期望值。

山版主提供的公式已经非常完美地解决了我在实际运用中的需要的了。在此表示感谢！

willin2000

QUOTE:

以下是引用nbc在2007-3-28 23:26:06的发言：

针对刚才以上讨论的问题。

即使将willin提供的解决情况二的公式的最外层判断函数的小于个数改为1，还是会导致引用错误的出现。

实例：
数字50在源数据区域内出现2次或两次以上，只会出现情况一；
数字50在源数据区域内出现1次，出现情况一及情况二，此时的期望值应该是数字50出现的行数减去源数据首行行数；
数字50在源数据区域内出现0次，最特殊情况，此情况可以用最外层的判断函数解决。

最外层判断函数的小于个数改为1后: 不满足上面实例中的哪一个, 在此情景下公式的结果是什么?期望值是什么?