Google Sheets简化QUERY公式报错原因及替代方案咨询

报错原因 #

你简化后的公式无法正常工作，是因为MATCHES在Google Sheets的QUERY函数中要求完全匹配正则表达式。你写的'jumbo|lidl|albert|picnic'是要求单元格内容完全等于其中某个关键词，而非"包含"关键词，和原公式的CONTAINS逻辑不符，因此出现报错或结果不符合预期。

正确的QUERY简化公式 #

调整正则表达式，使其匹配"单元格任意位置包含指定关键词"的逻辑，同时补上原公式的F='Af'条件和去掉默认标题的参数：

=QUERY(Data!A:I, "SELECT SUM(G) WHERE LOWER(B) MATCHES '.*(jumbo|lidl|albert|picnic).*' AND F = 'Af' LABEL SUM(G)''", 1)

解释：.*表示任意长度的任意字符，包裹关键词组后，就能实现和原公式CONTAINS完全一致的"包含"判断逻辑。

其他替代方案 #

方案1：SUMPRODUCT函数（逻辑更直观）

无需QUERY的语法限制，直接结合正则判断实现多条件求和：

=SUMPRODUCT(Data!G:G, --(Data!F:F="Af"), --(REGEXMATCH(LOWER(Data!B:B), "jumbo|lidl|albert|picnic")))

• --(REGEXMATCH(...))将布尔判断结果转换为1（符合条件）或0（不符合条件）
• 三个数组相乘后求和，等价于对同时满足F列为"Af"、B列包含指定关键词的G列数值求和

方案2：SUMIFS结合数组

利用SUMIFS支持数组条件的特性，用SUM包裹实现多关键词求和：

=SUM(SUMIFS(Data!G:G, Data!F:F, "Af", Data!B:B, "*"&{"jumbo","lidl","albert","picnic"}&"*"))

若需要忽略大小写，可添加LOWER转换：

=SUM(SUMIFS(Data!G:G, Data!F:F, "Af", Data!B:B, "*"&LOWER({"jumbo","lidl","albert","picnic"})&"*"))

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