excel - 如何评估依赖于类别组内的统计数据的条件？

Question

首先，我将展示我的数据和迄今为止的代码的最小示例，这样会更容易解释我的问题。

考虑以下数据:

ID  Esp         DBH     Cod
55  E_grandis   9.00    
55  E_grandis   9.71    7
55  E_grandis   10.00   
55  E_grandis   1.00    7
55  E_grandis   7.00    7
55  E_grandis           1

我正在尝试验证 Cod = 7 的行的值是否大于:

 average of DBH - 1 * standard deviation of DBH.

在上面的示例中，DBH 的平均值为 7.34，标准差为 3.73。因此，当标记为 Cod 7 时，DBH 值不应大于 3.61 (7.34 - 3.73)。

单元格 D3 和 D6 未通过标准，因为它们的 DBH(C3 和 C6)大于 3.61。在所有 Cod 为 7 的行中，只有 C5 小于 3.61。

我编写了下面的代码，当不满足这些条件时，它会显示一个消息框:

Sub Cod7()

Dim msg As String 'msg box
Dim ID As Range
Dim dbh_stdev As Double 'standard deviation of dbh
Dim dbh_avg As Double 'average of dbh
Dim not_dominated As Double 'criteria threshold (upper bound)
Dim cell_i As Range 'initial of array to compute average and standard deviation
Dim cell_e As Range 'end of array to compute average and standard deviation

    msg = ""
    Set cell_i = Range("C2")
    Set cell_e = Range("C7")

    dbh_stdev = WorksheetFunction.StDev(Range(cell_i, cell_e)) 'dbh standard deviation
    dbh_avg = WorksheetFunction.Average(Range(cell_i, cell_e)) 'dbh average
    not_dominated = dbh_avg - dbh_stdev 'upper bound

'searches cells with cod 7 on column Cod, and it displays a message box if
'DBH is greater than the 'not_dominated' variable value
For Each ID In Range("A2", Range("A2").End(xlDown))
    If ID.Offset(0, 3) = 7 And _
       ID.Offset(0, 2) <> 0 And _
       ID.Offset(0, 2) > not_dominated Then
             msg = msg & "Cod 7 on " & ID.Offset(0, 3).Address() & " is incorrect" & vbLf
    End If
Next ID

If Len(msg) > 0 Then MsgBox msg

End Sub

现在的问题是，在我的真实数据中，Esp(物种)列下有多个类别，我需要评估标准，计算每组物种内 DBH 的平均值和标准差。
物种群体是聚集的，即一个物种出现在相邻的行中。

例如，这是 Esp 列下有两个类别的最小数据:E_grandis 和 E_citriodora。

ID  Esp           DBH    Cod
55  E_grandis     9.00  
55  E_grandis     9.71   7
55  E_grandis     10.00 
55  E_grandis     1.00   7
55  E_grandis     7.00   7
55  E_grandis            1
55  E_citriodora  3.00  
55  E_citriodora  2.00   7
55  E_citriodora  2.00   7
55  E_citriodora         1      
55  E_citriodora         1
55  E_citriodora  0.50   7

E_citriodora 的胸径平均值为 1.88，标准差为 1.03。 Cod = 7 的行的 DBH 不能大于 0.85 (1.88-1.03)。在本例中，单元格 C9 e C10 未通过标准，而单元格 C13 通过。

如何调整代码以在“Esp”组中应用此类标准？

Answer 1

我相信下面的代码可以满足您的要求。请注意，只有当所有物种都“分组”在一起时，这才有效。

我添加了一个外部循环，允许代码迭代所有具有数据的行(具体来说，具有 ID 值的行)。

起始单元格 (cell_i) 的初始值为 C2 ，如原始代码所示，但我更改了它计算结束单元格 (cell_e):现在基于 B 列中与 cell_i 的当前物种具有相同值的行数(这就是 CountIf 正在做，这就是为什么这只有在所有物种聚集在一起时才有效)。

与Set cell_i = cell_e.Offset(1)一起，使循环从一个物种的第一行跳转到下一个物种，依此类推。

例如，第一次针对您的示例数据运行时，cell_i 将是 C2，cell_e 将是 C7 因为 B 列中具有 E_grandis 的行数为 6，从 cell_i 中减去 1 和 offset 的当前行意味着它将选择当前单元格下 5 行的单元格。

第二次将从C8开始并经过C12。等等

在循环体内，我放置了您的原始代码(大部分“未受到伤害”)。我刚刚调整了 For 循环，以便它迭代范围内的单元格(cell_i 到 cell_e，如 groupRange 变量)，而不是在 A 列中具有值的所有行之间进行迭代。

我添加了几个 Select 调用，以便您在逐步执行代码时可以遵循 ccell 和 groupRange 的值.

Option Explicit

Public Sub Cod7()

    Dim msg As String 'msg box
    Dim dbh_stdev As Double 'standard deviation of dbh
    Dim dbh_avg As Double 'average of dbh
    Dim not_dominated As Double 'criteria threshold (upper bound)
    Dim cell_i As Range 'initial of array to compute average and standard deviation
    Dim cell_e As Range 'end of array to compute average and standard deviation
    Dim ccell As Range 'current cell
    Dim groupRange As Range

    msg = ""
    Set cell_i = Range("C2")

    Do While cell_i.Offset(, -2) <> ""

        Set cell_e = cell_i.Offset(WorksheetFunction.CountIf(Range("B:B"), cell_i.Offset(, -1).Value) - 1)

        Set groupRange = Range(cell_i, cell_e)
        groupRange.Select

        dbh_stdev = WorksheetFunction.StDev(groupRange) 'dbh standard deviation
        dbh_avg = WorksheetFunction.Average(groupRange) 'dbh average
        not_dominated = dbh_avg - dbh_stdev 'upper bound

        'searches cells with cod 7 on column Cod, and it displays a message box if
        'DBH is greater than the 'not_dominated' variable value
        For Each ccell In groupRange
            ccell.Select
            If ccell.Offset(, 1).Value = 7 And _
                ccell.Value <> 0 And _
                ccell.Value > not_dominated Then
                     msg = msg & "Cod 7 on " & ccell.Offset(, 1).Address() & " is incorrect" & vbLf
            End If
        Next

        Set cell_i = cell_e.Offset(1)

    Loop

    If Len(msg) > 0 Then MsgBox msg

End Sub