excel - How to count consecutive values in Excel?

Question

At this moment I have this formula that counts consecutive values:

=IF(AB5=0;0;IF(OR(AND(AB4>=100;AB5>=100);AND(AB4<=-100;AB5<=-100));AC4+1;1))

Basically it does this:

0           0
0           0
-110        1
-110        2
-110        3
-100        4
0           0
0           0
0           0
130         1
150         2
0           0
0           0
-100        1
0           0
0           0
0           0
0           0
-110        1
0           0
0           0
0           0
-220        1
-150        2
0           0
0           0

But I want it to do this:

0           0
0           0
-110        0
-110        0
-110        0
-100        4
0           0
0           0
0           0
130         0
150         2
0           0
0           0
-100        1
0           0
0           0
0           0
0           0
-110        1
0           0
0           0
0           0
-220        0
-150        2
0           0
0           0

Or this:

0           0
0           0
-110        4
-110        0
-110        0
-100        0
0           0
0           0
0           0
130         2
150         0
0           0
0           0
-100        1
0           0
0           0
0           0
0           0
-110        1
0           0
0           0
0           0
-220        2
-150        0
0           0
0           0

What is the formula to make this possible ?

I prefer not to have to add more columns since the file is already big...

Using only a formula to do this would be the best thing to do.

Thanks.

标签： excel

Answer 1

您可以尝试这样的匹配公式：

=IF(AND(ABS(AB5)>=100,AB4=0),MATCH(0,AB6:AB$1048576,0),0)

编辑

显然，这对于 1,000,000 个细胞来说是缓慢的。不精确匹配更快：

=IF(AND(AB5>=100,AB4=0),MATCH(0,AB6:AB$1048576,-1),IF(AND(AB5<=-100,AB4=0),MATCH(-100,AB5:AB$1048576,1),0))

讨论

为什么从表面上看 Match 这么慢，它应该只搜索前面的几个单元格来进行匹配？我想知道是否是因为 Match 将要搜索的整个范围复制到临时数组？一般来说，这可能是一个好的策略，但在特定情况下，当匹配值接近范围的开头并且范围非常大时，这是一个不好的策略。

另一种可能的方法是使用频率确定最长的序列（这很快），然后使用索引限制要搜索的范围，例如：

=MAX(FREQUENCY(IF(ABS(A:A)>=100,ROW(A:A)),IF(A:A=0,ROW(A:A))))

然后在 B1

...MATCH(0,AB6:INDEX(A:A,ROW()+B$1),0)