在 Excel 数据透视表中使用 QUARTILE 按子群汇总数据 [英] Using QUARTILE in an Excel pivot table to summarise data by sub-populations

问题描述

我在 Excel 电子表格中有一个很大的数据表，从本质上讲，它可以被认为是被识别为属于不同亚群的个人的值的集合:

 IndivID SubPopID 值1 A 33.562 东 42.313D 16.354 50.595 华氏度 80.63...

此表中有 10,000 多个个体，以及 50 多个亚群.

我想计算每个亚群的 5 数汇总(最小值、Q1、中值、Q3、最大值).

Min 和 Max 在数据透视表中很容易.但 Q1、Q2(中位数)和 Q3 似乎不太可能.

问题

有什么方法可以计算 Excel 数据透视表中每个子群体的四分位数?

答案应该是这样的:

SubPopID Min Q1 Q2 Q3 MaxA 3.23 12.06 20.35 28.29 50.59乙C...

解决方案

我的经验是不能在数据透视表中设置百分位数.我只是做了一个网络搜索，并没有看到任何与此相矛盾的内容.我认为您能做的最好的事情是将源数据放在一个表中，以便您可以动态引用它，然后在另一个表中使用百分位函数.

我看过 ，意味着它是用 Ctrl-Shift-Enter 输入的代码>.它使用 IF 语句来过滤各种可能的 SubPopID.这是 F2 的公式:

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

I've got a large table of data in an Excel spreadsheet that, essentially, can be considered to be a collection of values for individuals identified as belonging to various subpopulations:

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

There are over 10,000 individuals in this table, and over 50 subpopulations.

I would like to calculate the 5-number summary (Min, Q1, Median, Q3, Max) for each of the subpopulations.

Min and Max are easy in a pivot table. But Q1, Q2 (median), and Q3 don't seem to be possible.

Question

Is there any way to calculate the quartiles for each subpopulation in a pivot table in Excel?

The answer should look like this:

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

解决方案

My experience is that you can't do percentiles in a pivot table. I just did a web search and don't see anything to contradict that. I think the best you can do is have your source data in a table so you can refer to it dynamically, and then use the percentile function in another table.

I've seen one article about using PowerPivot, but that's only available in Excel 2010, and it looks hard. I have 2010, and when faced with a similar problem, I still opted to go the route I'm suggesting here.

EDIT: Explanation of my proposed alternative:

In answer to your question in the comments, here's how I'd calculate percentiles from a table of data:

It relies on an single formula, which is the same for every cell from F2 to H6. It's an array formula, meaning it's entered with Ctrl-Shift-Enter. It uses an IF statement to filter on the various possible SubPopIDs. Here's the formula for F2:

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

这篇关于在 Excel 数据透视表中使用 QUARTILE 按子群汇总数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！