arrays - 在VBA中用零替换异常值

Question

我想编写函数 outliers_std(table, std) 用零替换异常值。
让我们将异常值定义为满足以下不等式的变量:

所以我想遍历整个表，如果有任何元素满足上面的不等式，我想用 0 替换它。
我到目前为止的工作

Function outliers_std(table As Variant, std As Double)
    Dim row As Range
    For Each row In [table].Rows
        If Abs(table(row) - Application.WorksheetFunction.Average(table)) / Application.WorksheetFunction.StDev(table) > std Then
            table(row) = 0
        End If
    Next
    outliers_std = table
End Function

但是当试图运行时

outliers_std(E10:E14,1)

在哪里 E10:E14下面是数据

我收到错误 #ARG!你知道问题出在哪里吗？
编辑
我跑了代码

Function outliers_std(table As Variant, std As Double) As Variant
    Dim row As Range
    For Each row In [table].Rows
        If Abs(row - Application.WorksheetFunction.Average(table)) / Application.WorksheetFunction.StDev(table) > std Then
            table(row) = 0
        End If
    Next
    outliers_std = table
End Function

并且错误仍然保持不变。我写错了什么吗？

Answer 1

代码:

Function outliers_std(table As Range, std As Double)
    Dim temp() As Variant
    temp = table.Value
    
    Dim mean As Double
    mean = Application.WorksheetFunction.Average(temp)
    
    Dim stdv As Double
    stdv = Application.WorksheetFunction.StDev(temp)
    
    Dim i As Long
    
    For i = 1 To UBound(temp, 1)
        If Abs(temp(i, 1) - mean) / stdv > std Then
            temp(i, 1) = 0
        End If
    Next
    outliers_std = temp
End Function

现在，根据您的版本，可以将其放在一个单元格中，它会溢出结果，或者他们需要突出显示与表格相同数量的单元格并使用 Ctrl-Shift-Enter 返回所有值。


但是，如果在工作表中这样做，则不会为此 vba:

=IF(ABS(E10:E14-AVERAGE(E10:E14))/STDEV(E10:E14)>1,0,E10:E14)

像上面一样，取决于一个人的版本，它会自动溢出，或者需要突出显示相同数量的单元格并使用 Ctrl-Shift-Enter