如何按多条件查询筛选满足前缀组合的重复Title行？

需求说明 #

需从含Creator、Title、Barcode、Inv. No.列的数据集中筛选符合以下条件的所有行：

• Title列值重复；
• 重复行满足任一规则：
  1. Barcode列至少有一个值以014开头，且至少有一个以026开头；
  2. 若Barcode不满足上述规则，则Inv. No.列至少有一个值以POL02P14开头，且至少有一个以POL026开头。

问题重现 #

尝试多个公式均出现语法错误或数组维度不匹配问题，示例公式如下：

公式1 #

=FILTER(query(list!A1:AR, "Select * where C starts with 'POL02P'"), list!B1:B<>"",COUNTIF(list!B1:B,list!B1:B)>1)

公式2 #

={results!A1:AR1;array_constrain(
query(
{Filter({results!A2:AR,results!AR2:AR},REGEXMATCH(results!D2:D, "^POL02P14|POL02P26"));
countif(index(Filter({results!A2:AR,results!AR2:AR},REGEXMATCH(results!D2:D, "^POL02P14|POL02P26")),0,45),
index(Filter({results!A2:AR,results!AR2:AR},REGEXMATCH(results!D2:D, "^POL02P14|POL02P26")),0,45))}
,"Select * where Col46>1")
,9^9,44)}

公式3 #

=query(FILTER({list!A2:A&list!J2:J,list!A2:J,
   iferror(
   vlookup(list!A2:A&list!J2:J,query(query(filter(list!A2:A&
   list!J2:J,REGEXMATCH(list!C2:C, "^POL02P14|POL02P26")),
   "select Col4, count(Col4) where Col4 <> '' group by Col4"),
   "select Col4 where Col2 >1 "),1,false))},REGEXMATCH(list!C2:C, "^POL02P14|POL02P26")),
   "select Col1, Col2, Col3, Col5, Col6, Col7, Col8, Col9, Col10, Col11 where Col12 <> ''
   order by Col3 asc, Col11 asc")

可行公式 #

假设数据在list!A:D（对应Creator、Title、Barcode、Inv. No.），将以下公式放入空白单元格即可实现需求：

=FILTER(list!A:D,
  COUNTIF(list!B:B, list!B:B) > 1,
  BYROW(list!B:B, LAMBDA(title,
    LET(
      group, FILTER(list!C:D, list!B:B = title),
      barcodes, INDEX(group,,1),
      invnos, INDEX(group,,2),
      bar_cond, AND(COUNTIF(barcodes, "014*") > 0, COUNTIF(barcodes, "026*") > 0),
      inv_cond, AND(COUNTIF(invnos, "POL02P14*") > 0, COUNTIF(invnos, "POL026*") > 0),
      bar_cond OR inv_cond
    )
  ))
)

公式逻辑说明 #

1. 外层FILTER先筛选出Title重复的行；
2. BYROW+LAMBDA对每个Title对应的分组做条件判断；
3. LET定义变量简化逻辑：
   • group：当前Title对应的Barcode和Inv. No.数据组；
   • bar_cond：判断该组是否满足Barcode的规则；
   • inv_cond：判断该组是否满足Inv. No.的规则；
4. 最终保留满足任一规则的分组内所有行。

内容的提问来源于stack exchange，提问作者Claudio Corbellini