举例说明Excel如何计算连续的正数/负数？

假设你有由正数和负数组成的数据。你需要在Excel中计算连续负数的数量。计算列表中的负数很容易，但这里我们需要关注的是consecutive 负数。这是一个有点挑战性的问题陈述。请看下面的解决方案和例子。

例子

让我们举一个现实世界的例子。你有每周的股票市场数据，你被要求计算市场连续遭受损失的周数。

我们在A列和B列有数据，标题在第1行，数据范围从A2单元格开始到B10单元格结束。

在C2单元格中输入以下公式，按Ctrl + Shift + Enter键确认该公式为数组公式。

=MAX(FREQUENCY(IF(B2:B10<0,ROW(B2:B10)),IF(B2:B10>=0,ROW(B2:B10))))

结果

有连续六周的股票市场处于红色状态。

它是如何工作的？

让我们一步步了解它是如何返回所需结果的。

第1步：数值小于0的行数

IF(B2:B10<0,ROW(B2:B10))

returns

{2;3;4;5;6;7;FALSE;FALSE;FALSE}

第2步：大于或等于0的数值的行数

IF(B2:B10>=0,ROW(B2:B10))

returns

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;8;9;10}

第3步：连续负数的数组

这里，FREQUENCY函数被用来计算负数在一行中出现的次数（连续的）。

=FREQUENCY({2;3;4;5;6;7;FALSE;FALSE;FALSE},{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;8;9;10})

returns

{6;0;0;0}

第4步：上述数组的最大值

结果是6，因为它是数组中最大的值。

VBA解决方案：计算负数的UDF

下面的解决方案是上述excel公式的一个替代方案。这是一个用户定义的函数来计算连续负数。将下面的程序粘贴在VBA编辑器中（按Alt+F11）

Function ConsNegative(rng As Range)

    Dim r As Range
    Dim c As Long
    Dim m As Long
 
    Application.Volatile
    c = 0
    m = 0
    On Error Resume Next
    For Each r In rng.Cells
        If r.Value < 0 Then
            c = c + 1
            If c > m Then
                m = c
            End If
        Else
            c = 0
        End If
    Next r
 
    ConsNegative = m
End Function

要使用上述程序，只需在C2单元格中输入=ConsNegative(B2:B10) 。它返回与公式相同的输出。

Excel：连续正数

同样地，我们也可以计算正数连续出现的次数。公式几乎与上述相同，唯一不同的是符号。在这里，我们关注的是正值。

=MAX(FREQUENCY(IF(B2:B10>0,ROW(B2:B10)),IF(B2:B10<=0,ROW(B2:B10))))

如何计算连续负值的总和

下面的例子只是为了演示。请注意，增加百分比值是没有意义的。

=MIN((COUNTIF(OFFSET(B2:B10,ROW(B2:B10)-ROW(B2),0,C2),"<0")=C2)*SUBTOTAL(9,OFFSET(B2:B10,ROW(B2:B10)-ROW(B2),0,C2)))