在Excel数据透视表中使用QUARTILE函数按子群体汇总数据

Question

我在Excel电子表格中有一个大型数据表，这个表可以被视为一组属于不同亚群体身份的个体的值的集合：

  IndivID   SubPopID  Value
     1          A       33.56
     2          E       42.31
     3          D       16.35
     4          A       50.59
     5          F       80.63
     ...

这张表格中有超过10,000个个体和50多个亚种。

我想为每个亚种计算5数总结（最小值、第一四分位数、中位数、第三四分位数、最大值）。

在数据透视表中，最小值和最大值很容易计算。但是第一四分位数、中位数和第三四分位数似乎不可能计算。

问题：

有没有办法在Excel的数据透视表中为每个亚种计算四分位数？

答案应该看起来像这样：

SubPopID      Min     Q1     Q2        Q3      Max
   A         3.23    12.06   20.35   28.29     50.59
   B 
   C
   ...

- Assad Ebrahim

Answer 1

我的经验是在数据透视表中无法计算百分位数。我刚刚做了一次网络搜索，没有看到任何与此相矛盾的信息。我认为你能做的最好的办法是将源数据放在一个表格中，以便可以动态地引用它，然后在另一个表格中使用百分位数函数。

我看过一篇文章关于使用PowerPivot，但那只在Excel 2010中可用，而且看起来很难。我有2010年版的Excel，当面临类似问题时，我仍然选择了我在这里提出的方法。 编辑：我提出的替代方案的解释：

回答你在评论中提出的问题，以下是我从数据表格中计算百分位数的方法：

它依赖于一个单一的公式，该公式对于从F2到H6的每个单元格都相同。这是一个数组公式，意味着它使用Ctrl-Shift-Enter输入。它使用IF语句来过滤各种可能的SubPopIDs。这是F2的公式：

=PERCENTILE(IF(Table1[SubPopID]=F$1,Table1[Value],""),$E2/100)

- Doug Glancy

---

那是我担心的事情。我的网络搜索没有找到任何关于Excel 2007有价值的信息。然而，对于Excel 2010来说，AGGREGATE()函数看起来非常有希望，因为它内置了一个四分位数选项。（不幸的是，我的客户不使用Excel 2010，并且没有立即升级他们的组织计划）。我会保持这个活动几天，并希望我们被证明是错误的，有人找到了一个聪明的解决方法。 - Assad Ebrahim

Doug，能否详细说明一下您所说的“将源数据放在一个表格中，再将百分位函数放在另一个表格中”是什么意思？（我无法想象如何在Excel的“QUARTILE()”函数上对行子集进行操作，例如仅限于示例中的SubPopID = A）。谢谢。 - Assad Ebrahim

请查看我的编辑。它依赖于一个带有IF语句的数组公式。 - Doug Glancy

太棒了！这完美地运作了。顺便说一下，截图和链接都对于能够复制这种方法起到了关键作用。非常感谢！ - Assad Ebrahim

我担心这个筛选表达式没有忽略空值。它会将 "" 视为零，这意味着结果会有所不同。尝试查找最小值，你就会发现。 - ePascoal

Answer 2

如果您需要检查多个列的条件（a=1 AND b=2），可以使用 * 字符来扩展数组

=PERCENTILE((IF((Table1[SubPopID]=[condition1]) * (Table1[SubPopID2]=[condition2]),Table1[Value]),""),$E2/100)

- zer0links

---

嗨，我认为你的公式可能有问题——括号似乎放置不正确。 - Assad Ebrahim

是的，这应该是代码 =PERCENTILE(IF((Table1[SubPopID]=[condition1]) * (Table1[SubPopID2]=[condition2]),Table1[Value]),""),$E2/100 - pauljeba

Answer 3

我发现了这个答案链接，其中描述了一种解决方案。

它使用与Doug Glancy所描述的相同的方法，但将其与数据透视表中的计算字段关联起来。该答案提供了一个示例Excel文件。

- Alban

Answer 4

这是另一种使用度量的方法：

1. 创建一个数据透视表并选择 "将此数据添加到数据模型"
   
2. 在 数据透视表字段 上右键单击表格，选择 "添加度量值..."
   
3. 使用 DAX 公式创建一个度量值：=PERCENTILE.INC(Table1[Score],0.85)
   
4. 将新创建的度量值添加到数据透视表的值中
   

感谢@DougHExcel提出这个很棒的想法！请参考他的视频以了解这种方法的演示: https://youtu.be/dD-oLbOLEBU?si=6FjVqvze8XhcS4rR&t=180

- Elias Rabl