使用 OR 函数从数组返回 N 个匹配结果

使用 OR 函数从数组返回 N 个匹配结果

我希望创建一个遍历数组并返回 N 个匹配结果的 excel 公式。例如,我将首先在单元格 A1 中使用此公式(例如),它将返回第一个匹配结果。当我将其拖到单元格 A2、A3 等时,它分别返回第 2 个和第 3 个匹配结果。我目前有一个公式可以做这么多:

INDEX(Sheet2!$B$2:$B$10081,AGGREGATE(15,3,(Sheet2!$AA$2:$AA$10081="TRUE")/(Sheet2!$AA$2:$AA$10081="TRUE")*(ROW(Sheet2!$AA$2:$AA$10081)-ROW(Sheet2!$AA$1)),ROWS($R$73:R73))))

但是,我正在尝试使用 OR 函数,因此,我不仅仅想检查单元格范围是 TRUE 还是 FALSE(目前使用辅助单元格/列来完成),还想检查该范围是否符合以下任何条件:

  • 值超过 999999 的单元格
  • 值为 0 的单元格
  • 值为负数(<0)的单元格

我的尝试如下:

INDEX(Sheet2!$A$2:$A$10081,AGGREGATE(15,3,(OR(Sheet2!$B$2:$B$10081>999999,Sheet2!$B$2:$B$10081=0,Sheet2!$B$2:$B$10081<0))/(OR(Sheet2!$B$2:$B$10081>999999,Sheet2!$B$2:$B$10081=0,Sheet2!$B$2:$B$10081<0))*(ROW(Sheet2!$B$2:$B$10081)-ROW(Sheet2!$B$1)),ROWS($P$73:P73))))

但是,这不起作用,因为aggregate()的第3个参数需要是一个数组,而我使用了一个以布尔值计算的OR函数。话虽如此,我不确定如何正确集成OR函数来检查上述3个条件。

答案1

加法将或者在每个条目上都具有相同的功能。因此:

(myRng>999999)+(myRng<=0)

将返回一个数组,{1..0}具体取决于您的条件是否满足。

要为函数创建数组AGGREGATE,如下所示:

1/((myRng>999999)+(myRng<=0))*myRng

答案2

如果您使用的是 Excel 365 并且可以访问 FILTER 函数,则可以使用 FILTER 返回数组。每个 OR 条件都可以用括号括起来,并用 , 分隔+,如下所述:

FILTER 函数 - Office 支持

具体来说:

...我们使用带有加法运算符 (+) 的 FILTER 函数来返回数组范围 (A5:D20) 中包含 Apples 或位于东部地区的所有值...

如果您对数据了解不太多,类似这样的内容应该会返回正确的数组(如果您想返回所有匹配的行,实际上不需要与 small 进行聚合)。

=FILTER(Sheet2!$A$2:$A$10081,(Sheet2!$B$2:$B$10081>999999)+(Sheet2!$B$2:$B$10081=0)+(Sheet2!$B$2:$B$10081<0))

然而...我刚刚看到你的评论说这将在 Excel 2016 上使用,所以 FILTER 不起作用(我会把它留在答案中以防它对其他人有帮助)。

在 Excel 2016(和 Excel 365)中,您可以使用 Power Query,因为您似乎实际上只是想找到满足特定条件的行。

我创建了一些示例数据:

在此处输入图片描述

现在只需选择数据中的任何单元格,然后使用数据>获取和转换数据>从表/范围打开 Power Query 编辑器并对数据进行查询。

在 Power Query 编辑器中,根据要使用的条件筛选值:

在此处输入图片描述

现在使用“主页”>“关闭并加载”将数据放回工作簿:

在此处输入图片描述

最后一步是确保查询以适当的间隔刷新。

将光标放在查询结果中,然后转到查询>属性并根据您的喜好配置刷新属性:

在此处输入图片描述

相关内容