excel - 如何创建具有最低 80% 源数据值的数据透视表？

Question

我有以下数据:

PARAM    VALUE
p1       23
p1       11
p2       15
p1       37
p3       65
p1       12
...      ...

我想要创建一个数据透视表，其中包含每个 PARAM 的平均值，但不是来自完整源数据:我希望平均值仅占每个 PARAM 的 80% 最低值。

例如:如果 p1 具有以下值(已排序):

12
15
17
18
25

我希望数据透视表中的平均值考虑到前 80%，即从 12 到 18。通过这种方式，我试图过滤掉数据系列中的峰值。

Answer 1

该解决方案可能不是很有效，但它可以完成工作。

我假设您的数据从单元格 A1 开始.

首先我将评论所应用的策略，然后我将一一介绍步骤。

策略

• 最初对数据进行透视以获得每组的 VALUEs
• 致力于每个 PARAM 的枢纽获得80% PERCENTILE .
• 在初始数据中创建一个键并比较每个 VALUE与相应的PERCENTILE其中PARAM 。对新数据集进行透视，您就完成了。

步骤

1) C1单元格写入Key 。在单元格中C2输入公式=COUNTIF($A$1:A2,A2)并复制下来。

2) 透视此表并得到 PARAM如Column Labels , Key如Row Labels和VALUE在Σ values像这样:

3) 在 G1插入此公式即可得到 80% 处的值 =PERCENTILE(G4:G20,0.8) G20取决于数据透视表有多少行。

4) 在单元格中D1写key1并在单元格中 D2插入公式=(B2<INDEX($G$1:$I$1,MATCH(A2,$G$3:$I$3,0)))*1并复制下来。

5) 最后对新表进行透视并设置过滤器 key1等于 1 .