Google Sheets QUERY如何用数组替换WHERE子句多列OR条件

问题描述 #

我的原型示例如下：

我目前有一段可正常运行的QUERY公式，但WHERE子句未做数组优化，重复写了大量OR判断逻辑，希望通过数组写法简化冗余代码，现有可运行公式如下：

=IFERROR(QUERY(F:N, "SELECT F WHERE G CONTAINS '"&A2&"' OR H CONTAINS '"&A2&"' OR I CONTAINS '"&A2&"' OR J CONTAINS '"&A2&"' OR K CONTAINS '"&A2&"' OR L CONTAINS '"&A2&"' OR M CONTAINS '"&A2&"' OR N CONTAINS '"&A2&"'"),"")

需求是用数组写法替换所有重复的OR条件子句。

我曾尝试以下写法但未成功：

SELECT ArrayFormula(textjoin(", ",TRUE,("Col"&row(indirect("A"&F1&":A"&O1)))))

之前写法失败的原因 #

QUERY的查询语句参数是纯文本，不会自动执行语句内部写的ArrayFormula、TEXTJOIN这类表格函数，必须把条件拼接逻辑放在查询字符串外部，先生成完整合法的查询语句，再传入QUERY执行。 #

可用写法 #

写法1：保留QUERY逻辑，自动拼接WHERE条件 #

用TEXTJOIN配合序列生成函数自动拼接多列OR判断条件，不需要手写重复逻辑：

=IFERROR(QUERY(F:N,"SELECT F WHERE "&TEXTJOIN(" OR ",1,"Col"&SEQUENCE(COLUMNS(G:N),1,2)&" CONTAINS '"&A2&"'")),"")

公式说明：

• SEQUENCE(COLUMNS(G:N),1,2)会自动生成从2开始的序列，对应QUERY查询范围内G到N的列编号（QUERY中传入范围的第一列F为Col1，后续列依次递增）
• TEXTJOIN会自动给每个列的判断条件加上OR连接符，生成和原手写逻辑完全一致的WHERE子句，后续调整判断列范围时，只需要修改G:N为实际范围即可，不需要逐行改OR条件

写法2：用FILTER+正则匹配，逻辑更短 #

如果不需要用到QUERY的聚合、排序等额外能力，直接用FILTER配合正则匹配写法更简洁：

=IFERROR(FILTER(F:F,REGEXMATCH(FLATTEN(QUERY(TRANSPOSE(G:N),,9^9)),A2)),"")

公式说明：

• 先通过TRANSPOSE+QUERY把每行G到N的单元格内容合并为单个字符串
• 用REGEXMATCH判断合并后的字符串是否包含A2的内容，只要任意一列包含就会匹配成功，直接返回对应行的F列值
• 如果A2中包含正则特殊字符（比如.、*、?等），可以把判断部分修改为REGEXMATCH(FLATTEN(QUERY(TRANSPOSE(G:N),,9^9)),"\Q"&A2&"\E")，避免正则匹配错误。

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