☰

Excel 四分位数函数不起作用

tag-icon • tag-icon microsoft-excel
Excel 四分位数函数不起作用

我正在尝试弄清楚如何让 Excel 的统计函数为我工作，但是当我使用偶数计数数据集时，它们会抛出看似随机的数字。

例如，我们使用这个数据集：{4,6,8,12,14,16}

在此示例中，正确的值是：

  • 中位数：10
  • 问题 1：6
  • 问题3：14

median()但是，当您使用 Excel 时，您会通过和获得以下结果quartile.exc()：

  • 中位数：10
  • 问1：5.5
  • 问题3：14.5

是我方法不正确，还是 Excel 根本就没有能力处理偶数四分位数？

答案1

简而言之，Excel 将四分位数计算为百分位数。这与我们通常认为的四分位数（作为数据上半部分/下半部分的中位数）完全不同。下面使用您的数据作为示例，简要解释一下 Excel 的工作原理。我不能 100% 确定这是 Excel 使用的确切算法，但这会给出相同的结果。

  1. Excel 将百分位数分配给数组中的每个值。
    P(4) = 0; P(6)=0.20; P(8)=0.40; ... ; P(16)=1

  2. 然后，Excel 检查所请求的百分位数在数组中的位置。 对于 Q1，0.25 介于 6 和 8 之间。

  3. 然后，Excel 根据百分位数在这些值之间进行线性插值。
    0.25 百分位数比 0.20 百分位数高 0.05 百分位数。0.05
    /(P(8)-P(6)) = 0.05/0.20 = 1/4
    因此，第 25 百分位数是 6 和 8 之间的 1/4。因此，返回值为 6.5。（我知道您输入的是 5.5，但我在 Excel 中检查了您的数据，6.5 是返回的四分位数。同样，Q3 返回的是 13.5，而不是 14.5。）

这当然是计算四分位数的一种奇怪方法。

现在按照您想要的方式找到四分位数 - 我有两个建议。

  1. 尝试统计包插件。 我没有在这台计算机上安装它，但值得尝试看看它返回的四分位数是否与工作表函数返回的四分位数不同。

  2. 您可以使用拼凑的替代公式。虽然有些混乱，但我认为它抓住了你要找的东西。

对于 Q1，您可以使用：

=IF(ISEVEN(ROUNDDOWN(COUNT(A1:A8)/2,0)),AVERAGE(SMALL(A1:A8,ROUNDDOWN(COUNT(A1:A8)/2,0)/2),SMALL(A1:A8,ROUNDDOWN(COUNT(A1:A8)/2,0)/2+1)),SMALL(A1:A8,ROUNDUP(ROUNDDOWN(COUNT(A1:A8)/2,0)/2,0)))

对于 Q3，您可以使用：

=IF(ISEVEN(ROUNDDOWN(COUNT(A1:A8)/2,0)),AVERAGE(LARGE(A1:A8,ROUNDDOWN(COUNT(A1:A8)/2,0)/2),LARGE(A1:A8,ROUNDDOWN(COUNT(A1:A8)/2,0)/2+1)),LARGE(A1:A8,ROUNDUP(ROUNDDOWN(COUNT(A1:A8)/2,0)/2,0)))

答案2

Excel 的内置四分位数函数使用插值来计算四分位数。那么，它如何在您的示例中找到 5.5 和 14.5？假设您的样本大小 (n) 为 6，它会按如下方式计算第一个分位数：

 = (n + 1) / 4 = 7 / 4 = 1.75

由于 1.75 介于值 1 和 2 之间，因此 Excel 会对数据进行插入以得出结果 5.5。

它按如下方式计算第三个分位数：

 = 3 * (n + 1) / 4 = 21 / 4 = 5.25

由于 5.25 介于值 5 和 6 之间，因此 Excel 会对数据进行插入，得出结果 14.5。

可以编写一个简单的宏来实现您想要的结果。使用ROUND()上述 1.75 和 5.25 值的函数将生成 Q1 和 Q3 作为数据集的第 2 和第 5 个元素，即 6 和 14。

至于 Excel 为什么会这样，目前还没有统一的看法四分位数值. Excel 使用方法 2而你使用方法 1在你的例子中。

答案3

Excel 2010 引入了 QUARTILE.INC 和 QUARTILE.EXC。

QUARTILE.INC 与 Excel 的旧 QUARTILE 函数相同，并在 N-1 的基础上进行插值，而 QUARTILE.EXC 与 Minitab 和其他一些统计软件包中使用的函数相匹配，并在 N+1 的基础上进行插值。

请注意，这两种方法都无法给出您期望的值。基于 N 进行插值可以达到此目的，但您可能正在考虑原始 Tukey 方法，它是确定四分位数的几种“铰链”方法之一。

如果你想了解更多，我写了一篇关于计算四分位数的详尽教程，箱线图的四分位数，重点介绍了 Excel 的使用。本主题中其他地方引用的维基百科文章相当简单。

答案4

由于 excel 函数没有输出预期值，我按照以下简单步骤查找四分位数

步骤1：按升序对数据集进行排序 

第2步：如果 n（数据点）为偶数，则将数据集分成两个相等的部分；如果 n 为奇数，则将数据集精确地分成两半；排除中位数，然后分割数据集（两半中都不包括中位数）

步骤3：找到上半部分的中位数，得到第一四分位数（Q1），找到下半部分的中位数，得到第三四分位数（Q3）

相关内容