如何使用索引查找所有大于某个值的数

Question

如何使用索引查找所有大于某个值的数

4

我已经在这个网站和其他地方对我的情况进行了相当多的研究，这是最接近我的问题/解决方案：

然而，在我的情况下使用该解决方案并不能给出正确的结果。我有一个包含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”值。

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

- Brian

1

只需将 INDEX 中的 $R$4:$R$13 更改为 $R:$R。这是因为您的 Small 从 ROW($S$4:$S$13) 获取值，因此第一个条目将是 4，但 INDEX($R$4:$R$13,4) 将是 R7 而不是 R4。但使用 INDEX($R:$R,4) 将输出正确的 R4 ;) - Dirk Reichel

这个解决方案可行！谢谢你！ - Brian

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Scott Craner · Accepted Answer

我更喜欢使用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进行确认。

另一种方法是使用AGGREGATE函数，无需将其作为CSE公式输入，而是作为普通公式输入：

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

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

当希望在第一行返回第一个结果时，后两个特别有用，因为它们不需要COUNTIF()函数来保持唯一性返回。