计算匹配行值的统计数据

Question

我正在尝试为一个格式不太理想的大型数据集计算一些关于出价数据的统计数据，但修复格式本身就成了一个难题（我似乎必须学习复杂的 Power Query 东西才能使其工作）所以我看看是否有可以直接解决问题并省去中间重新格式化步骤的答案。

我有许多竞标活动，不同竞标者的出价数量不一致。之内每个竞标事件（例如，对于竞标事件 101 的所有价格），我想要计算：平均值、标准差、两个最低出价之间的差额，以及所有竞标事件的标准差失败出价（即除最低出价之外的所有出价）。

我的数据看起来与示例中的数据类似，但要大得多。解决方案的格式如何并不重要，它可能很丑陋或重复，以至于 Bid Event 101 的多个行会一遍又一遍地得到相同的答案，这并不重要。我需要在计算这些之前弄清楚如何对其进行旋转，或者有没有办法做到这一点，而不需要进行复杂而奇怪的旋转，我需要编写一个强大的查询来创建多个维护排名的自定义列？

版本：Office 365 for Government，“Microsoft® Excel® for Microsoft 365 MSO（版本 2111）”

谢谢你！！！

请参阅示例数据图像的链接以了解我所追求的内容。 两列数据，一列是投标事件，一列是投标价格

Answer 1

如果您的数据只是图像中表格的较长表格，请尝试以下操作：

1. 创建唯一的出价事件 ID 列表

   =UNIQUE(A2:A7)

2. 为您想要的统计数据创建一个公式。这依赖于 LET 和 FILTER 函数。

   =LET( d,FILTER($B$2:$B$7,$A$2:$A$7=$E2), 一,SMALL(d,1), 二,SMALL(d,2), av,AVERAGE(d), std,STDEV.S(d), diff,二-一, losingstd,STDEV.S(FILTER(d,d>一)), CHOOSE({1,2,3,4},av,std,diff,losingstd) )

首先，我们过滤原始数据以获得唯一事件 ID，并将其存储在名称“d”中。我们使用 SMALL 函数将 d 中的最小值和第二小值分别返回到“一”和“二”。我们计算 d 的平均值和标准差，以及两个最小值之间的差值。最后，我们重新过滤 d 以删除最小值并取结果的标准差。

最后，我们使用 CHOOSE 将上述统计数据水平连接到一行。

然后，我们复制唯一列表中尽可能多的唯一出价事件的公式。