返回符合一个或多个条件的值列表

返回符合一个或多个条件的值列表

第一次在这里发帖。

我目前有一个包含两列数据的电子表格;一列是股票代码列表(B 列),另一列是其对应的行业(C 列)。我想使用公式返回一个新的股票代码列表,其行业与 J 列中指定的两个行业之一相匹配:“套装软件”或“信息技术服务”。

电子表格布局

在 E 列和 F 列中,我根据每个标准分别创建了此列表,通过分别在单元格 E2 和 F2 中输入下面的公式并向下拖动,即可按预期工作。

{=IFERROR(INDEX($B:$B,SMALL(IF($C:$C=$J$2,ROW($B:$B)),ROW(1:1)),1,1),"")} {=IFERROR(INDEX($B:$B,SMALL(IF($C:$C=$J$3,ROW($B:$B)),ROW(1:1)),1,1),"")}

但是,当我尝试在单元格 G2 中使用 IF(OR() 语句将两者合并时,问题出现在 G 列,如下所示。

{=IFERROR(INDEX($B:$B,SMALL(IF(OR($C:$C=$J$2,$C:$C=$J$3),ROW($B:$B)),ROW(2:2)),1,1),"")}

正如您在第一张图片中看到的,此公式返回的是整个公司列表,而不仅仅是符合两个条件之一的公司。我查看了许多类似的例子,但似乎没有一个能解决我遇到的长期问题,包括这个论坛上的一个老问题,标题为“返回符合几个条件之一的所有值的列表”。我从那篇文章中改编了这个公式,并彻底尝试了它,但出于某种原因,我似乎无法让它在我的电子表格中正常工作。

如果有人能提供帮助,我们将不胜感激;谢谢。

答案1

如果您最近升级了 Excel、SPILL 功能以及随之引入的功能,则以下公式应该适合您:

=SORT(UNIQUE(IF(C2:C9=J2,B2:B9,IF(C2:C9=J3,B2:B9,"")),,1))

它会将列表限制在您指定的标准内。您可以通过IF()在嵌套链中添加额外的 来延长列表。UNIQUE()会将其限制为仅成功匹配并按SORT()顺序排列。SPILL意味着您只需要单个单元格公式,无需选择将公式放入多少个单元格。当然,显示的范围需要根据您实际使用的行进行调整。

在您的帖子中,您使用了 {CSE} 输入方法,因此,可能Ron Rosenfeld存在问题,但也许这只是习惯。如果不是,以下 {CSE} 公式将为您提供匹配项列表,但也提供许多空白条目:

{=IF(C2:C9=J2,B2:B9,IF(C2:C9=J3,B2:B9,""))}

然后,您可以收集它们并删除空白单元格,以满足您的使用要求。

另一种可以作为基本数据收集器的方法是使用IFS()而不是 的嵌套链IF()。您不仅不会遇到嵌套限制,而且可以在单元格外部构建“测试/结果”对,并使用字符串构建技术将它们组合在一起。这将取代链IF()

=IFS(C2:C9=J2,B2:B9,C2:C9=J3,B2:B9,TRUE,"")

或 {CSE} 也是如此。

有趣的是,将单个测试放入一个函数中OR()不起作用,因为该函数会将结果数组折叠成单个结果,即使具有SPILL功能性也是如此。对于构建列表来说不太方便……通常有一种方法可以取回结果数组,奇怪的是,通过将折叠它的函数包装在一个乐于使用数组的函数中。“奇怪”是因为您认为当函数只接受第一个结果时,它会丢弃数组的其余部分,但如果包装它的函数仍然可以使用数组,它就不可能这样做,因为其余部分已经消失了。这表明即使它看起来已经折叠,整个数组仍然存在,Excel 会保留它直到公式完成。希望我能想出一种不明显的方法来使用它。

重点是,您也许能够找到密钥并使用OR(),但如果您不喜欢嵌套IF()链,使用IFS()就非常容易了,使用就这么简单,OR()所以何必呢……所以我没费心……

相关内容