Excel 按多个条件过滤表值

Question

我有 2 张 table :

一个包含一组属性:它们的唯一 ID、报价类型和引用点(用于本地化) - 名为 Table_Prop

包含来自正在搜索属性的客户的请求:他们的唯一 ID、报价类型和他们感兴趣的引用点。 - 命名为 Table_Req

属性表

Property ID Offer type  Reference points
      P1        sell        RP1, RP2, RP3
      P2        sell        RP1, RP4
      P3        rent        RP5
      P4        rent        RP5, RP6
      P5        sell        RP3, RP2

请求表

Request ID  Offer type  Reference point
    R1         sell          RP1
    R2         rent          RP6
    R3         sell          RP3

我想要的只是为每个请求过滤匹配属性的 ID。所以我用这个公式做了:

=TRANSPOSE(FILTER(Table_Prop[Property ID],
(ISNUMBER(SEARCH(Tabel_Req[@[Offer type]], Table_Prop[Offer type], 1))=TRUE)*
(ISNUMBER(SEARCH(Tabel_Req[@[Reference point]], Table_Prop[Reference points]))=TRUE), "NO MATCHES"))

这工作得很好，对于每个请求，它都会返回:

R1: P1      P2
R2: P4
R3: P1      P5

但是 1 个客户有一个或多个他感兴趣的引用点，因此请求表如下所示:

Request ID  Offer type  Reference points
    R1         sell          RP1, RP2
    R2         rent          RP6, RP3
    R3         sell          RP3, RP4

基本上，现在，我需要一个公式来返回所有匹配的 ID 属性，其中请求的引用点之一包含在属性的引用点中。
它应该返回这个:

R1: P1       P2        P5
R2: P4
R3: P1       P2        P5

我发现这个公式用“，”分隔符分割单元格值:

=FILTERXML("<t><s>"&SUBSTITUTE(Tabel_Req[@[Reference points]],",","</s><s>")&"</s></t>","//s")

我尝试了这个公式:

=FILTER(Table_Prop[Property ID], OR(COUNTIF(Table_Prop[@Reference points], "*"&FILTERXML("<t><s>"&SUBSTITUTE(Tabel_Req[@[Reference points]],",","</s><s>")&"</s></t>","//s")&"*"))=TRUE, "NO MATCHES")

但正如我所料，它不起作用。
我怎样才能达到我的目标，有什么解决方法吗？任何帮助表示赞赏。
注意:我不想使用 VBA，只是单元格中的一个公式，它返回匹配属性的列表。

Answer 1

也许以下内容会有所帮助:

=LET(X,FILTERXML("<t><s>"&SUBSTITUTE(G2,",","</s><s>")&"</s></t>","//s"),TRANSPOSE(FILTER(A$2:A$6,MMULT(ISNUMBER(FIND(", "&TRANSPOSE(X)&", ",", "&C$2:C$6&", "))*(B$2:B$6=F2),SEQUENCE(COUNTA(X),,,0)))))

让我试着解释一下(思想)过程:

FILTERXML()将首先将引用点(输入)“拆分”成一个数组。有关其工作原理的更多信息，我想引用 this较旧的帖子。

在第二步(过程)中，我们将使用一系列已知的函数来测试字符串是否包含特定的子字符串； ISNUMBER(SEARCH()) .前一个返回数组的元素得到前导/尾随逗号，并将与比较 Offer 类型的结果相乘。

MMULT()然后将在“引用点”下生成每个选项的运行总计。

最终结果(如下图所示)是 FILTER() 下的第二个参数的输入。因此只返回那些感兴趣的ID。