☰

如何使用 Index 公式返回基于多个条件的列表

tag-icon • tag-icon microsoft-excel worksheet-function
如何使用 Index 公式返回基于多个条件的列表

我正在使用以下数组公式尝试返回职位编号列表（在 A 列中）。

我的条件基于单元格 A3 中的日期，与 L 列中匹配的日期相关，但仅当 m 列单元格的内容为“Booked”时才为真

这是我所拥有的，它似乎有效，但仅限于第一个条件，它返回所有工作，而不仅仅是 M 列中带有“已预订”的工作

=IFERROR(INDEX('TRUSS JOBS AND DELIVERY'!A:A,SMALL(IFS('TRUSS JOBS AND DELIVERY'!L:L=A3,ROW('TRUSS JOBS AND DELIVERY'!A:A),ROW('TRUSS JOBS AND DELIVERY'!M:M),"BOOKED"),ROWS(B$3:B3))),"")

谁能告诉我我在这里做错了什么。我觉得也许是 ifs 公式，也许有完全不同的方法可以做到这一点。

答案1

或者：

=FILTER('TRUSS JOBS AND DELIVERY'!A:M,('TRUSS JOBS AND DELIVERY'!L:L=A3)*('TRUSS JOBS AND DELIVERY'!M:M="Booked"))

自然，您会想用 替换 的第一个范围A:M引用A:whatever the last column you want is。

当然，您还需要对这些参考给出一些限制，这样您就不会评估数百万个单元格。

最能满足您需求的函数是FILTER()。您可以给它一个，IFERROR()因为它会返回#CALC!无匹配查找。

标准的堆积很简单，只需使用将条件放在括号内()并将括号组相互乘以的老技巧即可。您通常会看到技巧*1在其开头或结尾包含一个，但这不是必需的。只有在将技巧与单个条件一起使用时才需要它（因为必须将该数组乘以某个值以将其强制为 1 和 0，而不是 TREU 和 FALSE，但在这种情况下，它通常根本不需要，因为它所用的函数对 TRUE/FALSE 数组本身很满意。

奇怪的是，您在评论中给出的解决方案与您所说的要使用的列不匹配。好吧，根据需要调整引用。此公式设置为返回所用范围内的所有列。您对此有一些选择。您可以使用和包装公式（IFERROR()如果您希望有一个，包括）INDEX()：

  1. 让它返回所有列，尽管这有点毫无意义。
  2. 告诉它要返回哪些列。通常，您可以使用一个简单的数组常量来执行此操作，该常量类似于{1,3,4,5,8,21,23}（您想要返回的任何列）。如果它们具有整齐的模式，您甚至可以使用SEQUENCE()。请注意，除非您希望返回列 L 和 M（用于匹配），否则您不需要返回它们。它们在过滤中仍能很好地工作。
  3. 您不仅可以选择要返回哪些列，还可以选择它们的顺序，这些顺序不必与原始数据的列顺序相匹配。您甚至可以根据需要重复列。执行所有这些操作的数组常量可能看起来像{7,2,23,28,3,2,7,14,20}。

FILTER()本身就非常容易使用。无论您有多少条件，都可以轻松添加匹配条件。并且INDEX()它可以让您完全控制返回的列。

答案2

谢谢这个答案我最终得到了下面的公式，该公式必须针对每一行进行调整才能返回多个值。

对于从 C4 开始的第 1 行：

{=INDEX(IFERROR(FILTER('sheet1!'!A:M,('sheet1!'!L:L=A3)*('sheet1!'!M:M="Booked")),),ROWS(C4:C4))}

对于第 2 行等等：

{=INDEX(IFERROR(FILTER('sheet1!'!A:M,('sheet1!'!L:L=A3)*('sheet1!'!M:M="Booked")),),ROWS(C4:C5))}

Ctrl++获取并使数组公式起作用Shift。Enter{}

相关内容