频率表的 Excel 公式

Question

任何用于计算频率表中数据平均值(众数、标准差、...)的简单 Excel 公式，如下所示:

value    frequency
5         3
8         5
4         11
....      ....

谢谢

Answer 1

为了使用 native 统计工作表函数，您必须将频率表扩展为一个数字数组。虽然 google 电子表格具有创建数组的 textjoin 和 split 函数，但 excel 没有这样的 native 功能。

使用用户定义函数将频率表扩展为元素数组。

Function freqArray(rngV As Range, rngF As Range, _
                   Optional bSorted As Long = 0)
    'usage: =freqArray(<value_range>, <frequency_range>, <-1 descending; 0 no sort (default); 1 ascending> 

    Set rngV = Intersect(rngV, rngV.Parent.UsedRange)
    Set rngF = rngF.Cells(1).Resize(rngV.Rows.Count, rngV.Columns.Count)

    Dim i As Long, j As Long, k As Long
    ReDim fa(1 To rngV.Rows.Count * Application.Sum(rngF))

    For i = 1 To rngV.Cells.Count
        For j = 1 To rngF.Cells(i).Value
            k = k + 1
            fa(k) = rngV.Cells(i)
        Next j
    Next i

    If bSorted Then
        Dim tmp As Variant, bDescending As Boolean
        bDescending = CBool(bSorted < 0)
        For i = LBound(fa) To UBound(fa) - 1
            For j = i + 1 To UBound(fa)
                If CBool(fa(i) < fa(j) And bDescending) Xor _
                   CBool(fa(i) > fa(j) And Not bDescending) Then
                    tmp = fa(j): fa(j) = fa(i): fa(i) = tmp
                End If
            Next j
        Next i
    End If

    freqArray = fa

End Function