excel - 如何将 Excel 公式 =STDEV.S(IF(FREQUENCY(range,range),range)) 转换为 VBA 代码？

Question

我有一个对预先存在的数据范围进行操作的 Excel 公式。

Excel 公式为：=STDEV.S(IF(FREQUENCY(range,range),range))，其中“范围”是上述数据范围。

我的目标是将这个公式转换为 VBA 代码。

以下代码是我尝试将公式转换为 VBA 的尝试，以及我对过程的可视化，以尝试理解为什么它没有输出相同的结果：

Private Sub CommandButton1_Click()

    Dim diffArray() As Variant
    Dim i As Integer
    Dim x As Integer
    Dim array1() As Variant, size As Integer, j As Integer
    Dim freqArray1() As Variant
    Dim freqArray2() As Variant, size2 As Integer, j2 As Integer

    'assigns the data values to array1
    size = 0
    j = 0
    ReDim array1(size)
    For i = 3 To 15
        size = size + 1
        ReDim Preserve array1(size)
        array1(j) = Cells(i, 2)
        j = j + 1
    Next i
    Cells(20, 2).Value = UBound(array1)
    Cells(21, 2).Value = LBound(array1)
    If UBound(array1) > 1 Then Cells(19, 2).Value = WorksheetFunction.StDev_S(array1)

    'setting freqArray1 to frequency(array1, array1)
    freqArray1 = WorksheetFunction.Frequency(array1, array1)
    Cells(20, 3).Value = UBound(freqArray1)
    Cells(21, 3).Value = LBound(freqArray1)
    For i = LBound(freqArray1) To (UBound(freqArray1))
        Cells(2 + LBound(freqArray1) + i, 3).Value = freqArray1(i, 1)
    Next i
    If UBound(freqArray1) > 1 Then Cells(19, 3).Value = WorksheetFunction.StDev_S(freqArray1)

    'setting freqArray2 to if(frequency(array1, array1), array1)
    size2 = 0
    j2 = 0
    ReDim freqArray2(size2)
    For i = LBound(freqArray1) To (UBound(freqArray1))
        If freqArray1(i, 1) Then
            size2 = size2 + 1
            ReDim Preserve freqArray2(size2)
            freqArray2(j2) = freqArray1(i, 1)
            j2 = j2 + 1
        End If
    Next i
    Cells(20, 4).Value = UBound(freqArray2)
    Cells(21, 4).Value = LBound(freqArray2)
    For i = (LBound(freqArray2)) To UBound(freqArray2)
        Cells(2 + LBound(freqArray2) + i, 4).Value = freqArray2(i)
    Next i

    'takes the standard deviation of if(frequency(array1, array1), array1)
    If UBound(freqArray2) > 1 Then Cells(19, 4).Value = WorksheetFunction.StDev_S(freqArray2)

End Sub

正在操作的数据值位于橙色单元格列 B(array1) 中。数组 'frequency(array1, array1)' 位于黄色单元格列 C 中。数组 'if(frequency(array1, array1), array1)' 位于绿色单元格列 D 中。目标是两者中的值蓝色单元格（B18 和 D19）相同。

我不明白两件事：

1. 为什么蓝色单元格（B18 和 D19）中的值不一样？
2. 为什么数组的索引会改变？一个从'0'开始，下一个从'1'开始，最后一个从'-1'开始？

标签： excelvba

Answer 1

使用字典创建唯一列表并在 StDev_S 中使用

Private Sub CommandButton1_Click()

    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")

    Dim rngArray As Variant
    rngArray = ActiveSheet.Range("B3:B15")

    Dim i As Long
    For i = LBound(rngArray, 1) To UBound(rngArray, 1)
        On Error Resume Next
            dict.Add rngArray(i, 1), rngArray(i, 1)
        On Error Resume Next
    Next i

    If dict.Count > 0 Then
        Dim unqArr As Variant
        ReDim unqArr(1 To dict.Count) As Variant

        i = 1

        Dim key As Variant
        For Each key In dict.Keys
            unqArr(i) = key
            i = i + 1
        Next key

        ActiveSheet.Cells(19, 4).Value = Application.WorksheetFunction.StDev_S(unqArr)
    End If


End Sub