Google Sheets多选下拉框匹配两列数据的QUERY公式优化

需求说明 #

数据源在Sample_Data标签页，通过Sample_Search_Form!B4的多选下拉框筛选，要求选中的所有值同时出现在Col3（部门1分配组）或Col4（部门2分配组）中，返回全列数据。

示例数据：

预期筛选结果 #

• 选中Group A：显示Project 1、3、4
• 选中Group B：显示Project 2、3
• 选中Group A、Group B：仅显示Project 3（需同时包含两个组）
• 选中Group A、Group C：显示Project 3、4

现有公式问题 #

尝试的两个QUERY公式逻辑有误，多选时无法实现“所有选中值都匹配”的要求。实际复杂公式中，问题出在O5对应的筛选行：

& if(O5<>"", "and Col10 matches '.*"&TEXTJOIN("|",TRUE,SPLIT(O5,", ",FALSE))&".*' or Col18 matches '.*"&TEXTJOIN("|",TRUE,SPLIT(O5,", ",FALSE))&".*'", "")

当前逻辑是“匹配任意一个选中值”，而非“所有选中值都匹配”。

修正方案 #

示例场景修正公式 #

针对Sample_Data的筛选，修正后的QUERY公式：

=QUERY(Sample_Data!A9:D, "Select Col1,Col2,Col3,Col4 where Col1 is not null "
& IF(B4<>"", 
   "AND " & TEXTJOIN(" AND ", TRUE, 
      ARRAYFORMULA(
         "(Col3 matches '.*"&SPLIT(B4,", ",FALSE,TRUE)&".*' OR Col4 matches '.*"&SPLIT(B4,", ",FALSE,TRUE)&".*')"
      )
   ), 
   ""
))

逻辑说明：

1. 用SPLIT拆分多选值为单个组，自动去除空值和多余空格
2. 对每个组生成(Col3包含该组 OR Col4包含该组)的条件
3. 用TEXTJOIN(" AND ", ...)串联所有组的条件，实现“所有选中组都满足在Col3/Col4中存在”

实际复杂公式对应修正 #

将原公式中O5对应的筛选行替换为以下内容：

& IF(O5<>"", 
   " AND " & TEXTJOIN(" AND ", TRUE, 
      ARRAYFORMULA(
         "(Col10 matches '.*"&SPLIT(O5,", ",FALSE,TRUE)&".*' OR Col18 matches '.*"&SPLIT(O5,", ",FALSE,TRUE)&".*')"
      )
   ), 
   ""
)

完整修正后的公式：

=IFERROR(VSTACK( { "", "", "", "", "", "", "" , "" , "" , "" , "" , "" , "" , "" , "" , "" },
IFERROR(SORT(QUERY(ARRAYFORMULA( REDUCE( TOCOL(,1), TOCOL(Sheet_Names!C3:C,1),
LAMBDA(a,c,VSTACK(a,IFERROR(INDIRECT(c)))))), 
"Select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col11,Col12,Col13,Col14,Col15,Col17,Col10 where Col1 <>''" 
    & IF(F5<>"", " AND Col19 = '"&F5&"'", "") 
    & IF(H5<>"", " AND Col11 = '"&H5&"'", "") 
    & IF(J5<>"", " AND Col30 matches '"&TEXTJOIN("|",TRUE,SPLIT(J5,", ",FALSE))&"'", "")
    & IF(L5<>"", " AND Col13 = '"&L5&"'", "") 
    & IF(O5<>"", 
       " AND " & TEXTJOIN(" AND ", TRUE, 
          ARRAYFORMULA(
             "(Col10 matches '.*"&SPLIT(O5,", ",FALSE,TRUE)&".*' OR Col18 matches '.*"&SPLIT(O5,", ",FALSE,TRUE)&".*')"
          )
       ), 
       ""
    )),8,TRUE,9,TRUE),)),"—")

关键修正点 #

• 原公式用OR串联多选值，实现“匹配任意一个”；修正后对每个选中值单独生成判断条件，再用AND串联，实现“所有选中值都满足存在于目标列中”
• SPLIT(...,FALSE,TRUE)自动处理拆分后的空值和空格，避免无效条件
• ARRAYFORMULA批量生成每个选中值的判断逻辑，再通过TEXTJOIN拼接成合法的QUERY语句

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