我希望创建一个遍历数组并返回 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 函数来返回数组范围 (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 编辑器中,根据要使用的条件筛选值:
现在使用“主页”>“关闭并加载”将数据放回工作簿:
最后一步是确保查询以适当的间隔刷新。
将光标放在查询结果中,然后转到查询>属性并根据您的喜好配置刷新属性: