excel - 如何使用索引查找所有大于的值

Question

我一直在这个网站和其他网站上研究我的情况，这是最接近我的问题/解决方案:

Find all values greater or equal than a certain value

但是，在我的情况下使用该解决方案并不能给出正确的结果。我有一份 83 个名字的名单，每个名字都会受到处罚。在单独的选项卡上，我想显示具有任何惩罚 (>0) 的所有名称的输出。
我只有四种可能的惩罚，所以如果我需要在公式(匹配或查找)中引用它们，那也可以。缩短和虚拟数据，这是我所拥有的示例:

+----------+---------+
|  Name    | Penalty |
+----------+---------+
| Name 1   |    0    |
| Name 2   |    0    |
| Name 3   |    5    |
| Name 4   |    0    |
| Name 5   |    0    |
| Name 6   |    10   |
| Name 7   |    0    |
| Name 8   |    0    |
| Name 9   |    0    |
| Name 10  |    20   |
+----------+---------+

使用此公式，然后使用 CSE 并向下拖动:

=INDEX($R$4:$R$13,SMALL(IF($S$4:$S$13>0,ROW($S$4:$S$13)),ROW(1:1)))

它给了我这些结果:

+---------+
| Name 6  |
| Name 9  |
| #REF!   |
| #NUM!   |
| #NUM!   |
| #NUM!   |
| #NUM!   |
| #NUM!   |
| #NUM!   |
| #NUM!   |
+---------+

我将通过使用 IFERROR 并将其设为空白来处理错误，但仍然找不到罚分 >0 的人的正确名称

编辑:更改最后一个“ROW”部分给了我不同的答案，所以我认为我的问题不知何故就在那里，但我仍然不知道该怎么办。这应该是“SMALL”函数的“k”值。

非常感谢任何帮助。谢谢!

Answer 1

我更喜欢使用 MATCH() 而不是 SMALL():

=INDEX($R$4:$R$13,MATCH(1,($S$4:$S$13>0)*(COUNTIF($U$3:U3,$R$4:$R$13)=0),0))

这是一个数组公式，因此请使用 Ctrl-Shift-Enter。

此外，此公式要求它至少从第二行开始，因为 countif 需要引用上面的单元格以避免循环引用。

<小时/>

如果你确实想使用 SMALL() 那么你需要对起始行进行调整:

=INDEX($R$4:$R$13,SMALL(IF($S$4:$S$13>0,ROW($S$4:$S$13)-ROW($S$4)+1),ROW(1:1)))

或者正如 @dirk 指出的，数组部分是 SMALL() 而不是 INDEX，因此可以在 INDEX 部分中使用完整列并按原样使用 SMALL，因为它将返回实际的行号:

=INDEX($R:$R$,SMALL(IF($S$4:$S$13>0,ROW($S$4:$S$13)),ROW(1:1)))

也是一个数组公式，因此请使用 Ctrl-Shift-Enter 进行确认。

另一种方法是使用在没有 CSE 的情况下输入的 AGGREGATE 作为普通公式:

=INDEX($R:$R,AGGREGATE(15,6,ROW($R$4:$R$13)/($S$4:$S$13>0),ROW(1:1))

这将作为常规公式输入。它仍然是一个数组类型的公式，因此仍然需要仅使用数据集作为引用，并避免在公式的数组部分中引用完整的列。

当第一行需要第一个返回结果时，最后两个特别有用，因为它们不需要 COUNTIF() 来维护唯一返回。