![使用 Excel,给定分组数据,如何估计四分位数?](https://linux22.com/image/1538285/%E4%BD%BF%E7%94%A8%20Excel%EF%BC%8C%E7%BB%99%E5%AE%9A%E5%88%86%E7%BB%84%E6%95%B0%E6%8D%AE%EF%BC%8C%E5%A6%82%E4%BD%95%E4%BC%B0%E8%AE%A1%E5%9B%9B%E5%88%86%E4%BD%8D%E6%95%B0%EF%BC%9F.png)
假设我有以下关于大量狗的分组数据:
Mass Frequency
0 to 5 13
6 to 10 28
11 to 15 47
16 to 20 21
21 to 25 11
25 to 30 6
如何使用 Excel 估计第一个四分位数?
我知道如何制作一个曲线图(使用散点图,其中的点由平滑曲线连接),并且我可以使用曲线图和我的眼睛(也许还有放在屏幕上的尺子)粗略地找到第一个四分位数。但 Excel 能给我一个更严格的答案吗?
我不想使用“添加趋势线”功能,因为趋势线实际上并不是一个尖头线(趋势线不会经过所有点)。
答案1
这是分组数据,因此除非您有原始数据,否则您将不得不做一些事情来重现数据中的变化。为简单起见,我们可以假设一个平坦的分布 - 狗的概率为 0 和 5 是相等的。
使用该REPT()
函数输出代表每个组的以逗号分隔的数字列表。
=REPT(B2&",",C2) --- where B2 is your group upper bound and C2 is the group frequency
在底部,将每个字符串连接成一个大字符串:
复制此字符串并特殊粘贴作为价值观在新工作表中。使用文本分列工具数据功能区选项卡将数据拆分为每个值一列。复制整个范围,然后特殊粘贴和转置将其翻转为垂直值列表。您的数据应如下所示:
随意丢弃水平行 - 我们不需要它。现在我们想在 B 列中插入一些值,使用以下公式:
=(5*COUNTIF($A$4:A5,A5)/COUNTIF($A$4:$A$5000,A5))+(A5-5)
分解一下,我们有:
=(5* -- your groups are increments of 5
COUNTIF($A$4:A5,A5) -- how far down a row is within a group
/
COUNTIF($A$4:$A$5000,A5) -- what the frequency is for that group
)
+(A5-5) -- add this result to the lower bound
现在,您可以使用QUARTILE()
此估计值列表上的函数来近似四分位数:
=QUARTILE($B$5:$B$130,1)
答案2
从图形上看,你想制作一个直方图将“bins”设置为四分位数。您可以查阅如何执行此操作。
或者,您可以使用公式。我会用文字描述这一点,您可以自己算出其中的数学公式。
共有 126 只狗。其中 25% 为 31.5。您想要找出最轻的 31.5 只狗的体重。
最轻的箱子里有 13 只狗,所以它们被计算在内。剩下 18.5 只狗,但下一个箱子里有 28 只狗。所以问题是:第二个箱子里第 18.5 只最轻的狗的体重是多少?
使用所谓的线性插值法,您可以估算出未知重量是 6 磅和 10 磅之间的 18.5/28。即 6+4*(18.5/28)。
如果需要,您可以使用相同的方法来查找其他四分位数。
这里的语言有点别扭,但你明白我的意思了。希望这对你有帮助。
编辑:如果您知道所有狗的体重,只需按顺序排列它们,然后计算第 31 只和第 32 只狗的平均体重。