excel2003如何用函数或VBA自动统计两列或多列中(注意：不是单独只有一列)某个指定数字的最大间隔数？

Question

excel2003如何用函数或VBA自动统计两列或多列中(注意：不是单独只有一列)某个指定数字的最大间隔数？比如BC列中99最大间隔数是6（注意：是BC列合并计不是单独C列的8），DE列中100最大间隔数是8，FG列中111最大间隔数是7请解释详细些，最好能配动图讲解，谢谢！

Answer 1

B16输入99，BC列中99最大间隔数是6：
=MAX(ABS(MAX(IF($C$2:$C$13=$B$16,ROW($2:$13),0))-MIN(IF($B$2:$B$13=$B$16,ROW($2:$13),""))),ABS(MIN(IF($C$2:$C$13=$B$16,ROW($2:$13),""))-MAX(IF($B$2:$B$13=$B$16,ROW($2:$13),0))))-1追问

你的公式计算出的结果怎么会有些对有些错？不信你修改一下任意数值套来看！怎么会这样呢？

追答

试了，没错。请举例说明

追问

举个例子，把B2的109换成100,那么BC两列100之间最大间隔数应该是10才对。但在B16输入100，却得出结果12！把B7换成100，两个100只隔了5行也得出12出来！要在C列填入100才正确！但我是要两列看成一个整体来计算结果的！
你后来上传的又换了不同的公式？

追答

没有考虑单独一列的情况，下面为增加B列单独对比公式：
=MAX(FREQUENCY(ROW(INDIRECT(MIN(IF($B$2:$B$13=$B$16,ROW($2:$13),""))&":"&MAX(IF($B$2:$B$13=$B$16,ROW($2:$13),"")))),IF($B$2:$B$13=$B$16,ROW($2:$13),"")))-1
同理增加C列，然后修改为INDIRECT，然后三个选最大即可。
=MAX(MAX(ABS(MAX(IF(INDIRECT("$"&D16&"$2:$"&D16&"$13")=$B$16,ROW($2:$13),0))-MIN(IF(INDIRECT("$"&C16&"$2:$"&C16&"$13")=$B$16,ROW($2:$13),""))),ABS(MIN(IF(INDIRECT("$"&D16&"$2:$"&D16&"$13")=$B$16,ROW($2:$13),""))-MAX(IF(INDIRECT("$"&C16&"$2:$"&C16&"$13")=$B$16,ROW($2:$13),0))))-1,MAX(FREQUENCY(ROW(INDIRECT(MIN(IF(INDIRECT("$"&C16&"$2:$"&C16&"$13")=$B$16,ROW($2:$13),""))&":"&MAX(IF(INDIRECT("$"&C16&"$2:$"&C16&"$13")=$B$16,ROW($2:$13),"")))),IF(INDIRECT("$"&C16&"$2:$"&C16&"$13")=$B$16,ROW($2:$13),"")))-1,MAX(FREQUENCY(ROW(INDIRECT(MIN(IF(INDIRECT("$"&D16&"$2:$"&D16&"$13")=$B$16,ROW($2:$13),""))&":"&MAX(IF(INDIRECT("$"&D16&"$2:$"&D16&"$13")=$B$16,ROW($2:$13),"")))),IF(INDIRECT("$"&D16&"$2:$"&D16&"$13")=$B$16,ROW($2:$13),"")))-1)

Answer 2

=MAX(FREQUENCY(IF((B2:B13=99)+(C2:C13=99),"",ROW(2:13)),IF((B2:B13=99)+(C2:C13=99),ROW(2:13),"")))
按ctrl+shift+enter输入数组公式，统计B2:C13中99的最大间隔。追问

能否让它变“活”？但是这点要固定：比如始终是由B2,C2开始计算起，而不是小十字下拉后又变成由B3,C3、B4,C4……开始计算起！最好加上$，无论把公式复制到哪一列都不会改变参数！
还有能否把三列、四列、五列、六列等列的函数公式都写出来！因为一列就代表一个人，不可能只有这三几个人啊！你最好写个类似方程式那样的总公式，解释一下各参数代表什么，这样我就能直接套用来计算N列了！