![如何从 Excel 中的频率分布估算第 N 个值(或第 N 个百分位数)?](https://linux22.com/image/1247679/%E5%A6%82%E4%BD%95%E4%BB%8E%20Excel%20%E4%B8%AD%E7%9A%84%E9%A2%91%E7%8E%87%E5%88%86%E5%B8%83%E4%BC%B0%E7%AE%97%E7%AC%AC%20N%20%E4%B8%AA%E5%80%BC%EF%BC%88%E6%88%96%E7%AC%AC%20N%20%E4%B8%AA%E7%99%BE%E5%88%86%E4%BD%8D%E6%95%B0%EF%BC%89%EF%BC%9F.png)
我有一个 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),因为它能让你物有所值(即努力)。