我有一份 7 位专业人士的排名列表。排名第一是最高的。我正在尝试解决这个问题，以便能够找到每个月的最高排名和第二高排名。例如，在 1 月份，Marcus 的名字下有一个值，所以我希望最高排名列显示 20，第二高排名显示 Paul，因为他的名字下有一个非零值。我希望 Anthony 在 1 月份被忽略，因为他的名字下没有值。
答案1
重新排列了结果表，以提供一些灵活性。请参阅底部的图像以了解单元格引用和布局。我没有显示最高排名和第二排名，而是在其上方放置了一个标题，上面写着排名，然后在其下方放置一个代表排名位置的数字。我这样做是为了让您只需将公式拖到右侧，它就会根据标题中的排名数字来提取排名。
使用以下示例中的参考范围，将以下公式放在 J3 中，并根据需要向下和向右复制。
=IFERROR(INDEX($2:$2,AGGREGATE(15,6,COLUMN($B$3:$H$3)/(INDEX($B$3:$H$6,MATCH($I3,$A$3:$A$6,0),0)<>""),J$2)),"None")
更新 - 解释
让我们把这个公式分解成各个组成部分
让我们从包含几个公式的聚合公式开始：
AGGREGATE(15,6,COLUMN($B$3:$H$3)/(INDEX($B$3:$H$6,MATCH($I3,$A$3:$A$6,0),0)<>""),J$2)
我之所以把这部分拿出来，是因为其中发生的一切都是类似数组的操作。让我们再深入一点，看看匹配部分，然后再从那里回来：
MATCH($I3,$A$3:$A$6,0)
此公式将返回一个整数，表示 I3（一月）中的值在列表 $A$3:$A$6 中的位置。换句话说，我们试图找出您想要查看参考表中的哪一行。在本例中，列表中的第一项为 1。如果一月不在列表中，那么它将返回一个错误，该错误将通过公式向上传播。
接下来我们看一下指数公式：
INDEX($B$3:$H$6,MATCH($I3,$A$3:$A$6,0),0)
INDEX(TABLE, ROWS TO GO DOWN, COLUMNS TO MOVE OVER)
可以将索引公式视为定义表格，向下移动 Y 行，然后跨越 X 列。在本例中，$B$3:$H$6 表示没有标题/行标签的数据。公式MATCH告诉我们要向下移动多少行。末尾的 0 是一个巧妙的小技巧，有点特别。由于您无法跨越 0 列，因此在这种特殊情况下，0INDEX将其解释为获取所有列（也称为定义表格的整行）。顺便提一下，当您使用 0 表示要向下移动多少行时，也会发生类似的事情。
接下来就是数组操作和比较。
INDEX() <> ""
基本上，它会遍历每一列并检查条目是否为空白或其中包含某些内容。如果为空白，则返回 FALSE，非空白则返回 TRUE。现在我们这样做的原因是，下一步我们将除以 TRUE 或 FALSE 值。当 TRUE 和 FALSE 通过数学运算发送时，Excel 会分别将其转换为 1 和 0。所以这里发生了两件重要的事情。除以 1 不会改变任何东西的值。它本质上什么都不做。除以 0 会导致除以 0 的错误，该错误会向上传播。这实际上是我们需要的东西。现在我们实际上在除什么？
COLUMN($B$3:$H$3)
这仍然在聚合公式的数组部分。因此，当数组遍历每个单元格时，它将构建一个列表。所以基本上发生的事情是生成了一个电子表格列号列表。更重要的是，电子表格列号列表除以 1（这是我们想要的），除以 0 会产生我们不想要的错误。现在让我们看看聚合：
AGGREGATE(15,6,COLUMN($B$3:$H$3)/(INDEX($B$3:$H$6,MATCH($I3,$A$3:$A$6,0),0)<>""),J$2)
AGGREGATE(Function number, OPERATION #, formula, Option value)
基本上，我们选择函数 15，它将对函数结果列表进行排序，并从小到大排列。函数 15 还强制聚合执行数组操作。并非所有函数编号都会这样做。6 告诉聚合忽略错误值。因此，所有这些除以 0 的错误都将被忽略，匹配函数中潜在的未找到匹配错误也是如此。我们上面讨论了公式的作用，而本例中的选项值是您想要从列表顶部到哪个位置。1 返回最小的数字，2 返回第二小的数字，依此类推。在这种情况下，我将其设置为查看标题中的数字，而不是将其硬编码到公式中。最后，本例中的 AGGREGATE 将返回一个对应于您要查找的 SPREADSHEET 列号的整数。然后它回到 INDEX：
INDEX($2:$2,AGGREGATE(...))
在这种情况下，我将第 2 行全部作为表格。由于它只有 1 行，我不需要像上一个表格那样提供向下的行和向上的列。它看起来像一个列表，我只需要提供我想要在列表中向下移动多少。因此，由于我们有 SPREADSHEET 列号，我们需要查看标题所在的整个电子表格行，以便聚合返回的电子表格列号与索引中的电子表格表对齐。
在未找到任何值的转换中，即没有排名第一的排名，Aggregate 将返回错误。为了处理这个问题，我们使用 IFERROR 函数。如果没有错误，该函数将正常运行并返回其结果。如果有错误，则在我们的例子中返回“无”。