excel - 在不同的标准中排名前 5 名

Question

我有一张表，我想在每个不同的组中找到前 X 人。

Unique Names       Number           Group
a                   30               1
b                   4                2
c                   19               3
d                   40               2
e                   1                1
f                   9                2
g                   15               3

我使用 =index($A$2:$A$8,match(large($B$2:$B$8,1),$B$2:$B$8,0)) 按人数排名前 5 位。 LARGE 函数中的 1 我链接到一个排名范围，因此当我向下拖动它时，它会改变数字。

接下来我想做的是对每个组中的前 x 人进行排名。所以第1组的前3名。

我试过 =index($A$2:$A$8,match("1"&large($B$2:$B$8,1),$C$2:$C$8&$B$2:$B$8,0))但它似乎没有用。

谢谢

编辑:查看下面的答案后，我意识到为什么它们不适合我。我想使用公式的实际数据有多个数字条目。我已经调整了示例数据以显示这一点。我遇到的问题是，如果有重复的数字，那么即使其中一个不在组中，它也会返回两个名称。

 Unique Names       Number           Group
    a                   30               1
    b                   30               2
    c                   19               3
    d                   40               2
    e                   1                1
    f                   30               2
    g                   15               3

Answer 1

概念证明



在单元格 F2 中使用上面示例中的以下公式，并根据需要向下和向右复制。

=IFERROR(INDEX($A$2:$A$8,MATCH(AGGREGATE(14,6,($C$2:$C$8=F$1)*($B$2:$B$8),ROW($A2)-1),$B$2:$B$8,0)),"")

在标题行中提供组号。或者想出一个公式来增加和重置组编号，因为您根据您问题中的 X 编号向下复制。

解释:

AGGREGATE 函数与大函数不同，它是一个数组函数，不需要使用 CSE。因此，我们可以为我们想要使用的内容添加标准。在这种情况下，只使用了 1 个标准，那就是组号。在公式中，它是以下部分:

($C$2:$C$8=F$1)

如果有多个条件，我们将使用 + 运算符作为 OR 或使用 * 运算符作为 AND。

聚合函数中的 6 选项允许我们忽略错误。这在尝试获得较小的值时很有用。它对于处理可能导致不需要担心的错误的其他信息也很有用。

由于这在技术上是一个数组操作，因此请避免使用完整的列/行引用，因为它们可能会使您的系统陷入困境。

整个公式所做的基础是构建一个与您感兴趣的组号匹配的列表。过滤您的数字后，它会根据您复制到的行来确定哪个是最大的、第二大的等等。然后通过match函数判断第n大数出现在哪一行，最后用index函数返回该行对应的名字。