☰

使用 Excel，给定分组数据，如何估计四分位数？

tag-icon • tag-icon microsoft-excel
使用 Excel，给定分组数据，如何估计四分位数？

假设我有以下关于大量狗的分组数据：

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

使用 REPT

在底部，将每个字符串连接成一个大字符串：

长串

复制此字符串并特殊粘贴作为价值观在新工作表中。使用文本分列工具数据功能区选项卡将数据拆分为每个值一列。复制整个范围，然后特殊粘贴和转置将其翻转为垂直值列表。您的数据应如下所示：

转置数据

随意丢弃水平行 - 我们不需要它。现在我们想在 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 只狗的平均体重。

相关内容