☰

如何从 Excel 中的频率分布估算第 N 个值（或第 N 个百分位数）？

tag-icon • tag-icon microsoft-excel
如何从 Excel 中的频率分布估算第 N 个值（或第 N 个百分位数）？

我有一个 Excel 电子表格，其中包含频率计数而不是原始数据。我希望能够轻松找到第 653 个值或第 95 个百分位数之类的值。

举个例子，假设原始数据是每个数据包的延迟。我拥有的频率计数数据可能如下所示：

1ms = 1234567
3ms = 34254
5ms = 33034
7ms = 6901
10ms = 76

6901 个数据包落入“大于 5 毫秒，但不超过 7 毫秒”的桶中。

由于存储桶非常细粒度，我想我可以做一些小技巧来计算一些标准统计信息，就好像我有原始数据一样。计算平均值不需要这样做，但中位数或标准差或第 99 个百分位数都依赖于数据范围。有人能帮忙吗？

答案1

对于您拥有的频率，Excel 没有足够的行来保存原始数据。我已将频率缩小了 100 倍（最后一个除外），以便它们可以放在 Excel 工作表中。如果您运行此代码，您应该会得到原始数据，当使用以下公式时

{=FREQUENCY($A$2:$A$13095,{1,3,5,7,10})}

给你（我缩小的频率）12345、343、330、69、7。它会在你的范围内产生随机数。

Sub MakeRawData()

    Dim i As Long
    Dim dRaw As Double

    For i = 1 To 12345
        dRaw = Rnd
        Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = dRaw
    Next i

    For i = 1 To 343
        dRaw = (2 - 1 + 1) * Rnd + 1
        Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = dRaw
    Next i

    For i = 1 To 330
        dRaw = (4 - 3 + 1) * Rnd + 3
        Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = dRaw
    Next i

    For i = 1 To 69
        dRaw = (6 - 5 + 1) * Rnd + 5
        Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = dRaw
    Next i

    For i = 1 To 7
        dRaw = (9 - 7 + 1) * Rnd + 7
        Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = dRaw
    Next i

End Sub

它的效率不太高，可能需要几分钟才能运行，但如果您不需要经常这样做，那就不是什么大问题。

答案2

我参加这个聚会晚了大约 1.5 年，但我想我还是会发帖给任何偶然发现这个问题的人。

我认为最好的办法是从现有数据中插入累积分布函数。这可能需要一些认真的技巧和手动操作，特别是如果您提供的样本数据接近您正在处理的分布。但是，这肯定比从您最初只是猜测的分布中创建（并存储！）一百万个以上的假数据点要好得多。

要获得 CDF，您需要计算每个存储桶的累积概率。我假设总是存在一些延迟，因此将 0 作为频率为 0 的最小值。要找到每个存储桶上限的 CDF 值，请使用公式

(sum of frequencies in bucket and previous buckets)/(sum of all frequencies)

对于您提供的样本数据，累积概率点将是

{(0,0); (1;0.943); (3,0.969); (5,0.995); (7,0.999); (10,1)}

现在，开始进行细化。如果您对分布情况有一些常识性见解，例如，您认为大约 25% 的延迟小于 0.1 毫秒，您可以将这些直觉添加到您的数据中。如果您不知道分布应该是什么样子，那么您可以按照您已有的进行操作。

从这里开始，你有两个选择：要么 (a) 在你拥有的点之间进行线性插值，要么 (b) 对你的数据进行函数形式（比如 beta 分布）的拟合。 (a) 更简单，因为它不需要回归；然而，它不会给你比已有数据更精细的图像，而且频率的计算需要一点 Excel 公式功夫。 (b) 将为你提供一个比线性插值更接近底层数据的精细图像，而且它只需要直接、简单的 Excel 公式即可获得任何存储桶或百分位数的频率；然而，它需要回归，而这需要 Solver 插件。我更喜欢选项 (b)，因为它能让你物有所值（即努力）。

相关内容