Google Sheets QUERY查询结果多列批量替换特殊字符问题

错误原因 #

• 原有公式不符合SUBSTITUTE函数的参数规则，该函数正确参数顺序为 SUBSTITUTE(待处理文本, 要替换的旧内容, 替换后的新内容)，传入列范围作为参数属于语法错误
• SUBSTITUTE默认不支持对二维数组按不同列执行不同替换规则，直接嵌套QUERY返回的数组只会处理首个值，所以仅返回1

正确实现公式 #

直接在Sheet2的A1单元格输入以下单公式即可自动溢出得到全表替换结果：

新版Google Sheets写法（支持自动溢出） #

=BYROW(Sheet1!A2:D, LAMBDA(r, 
  {
    INDEX(r, 1),
    SUBSTITUTE(INDEX(r, 2), "/", ""),
    INDEX(r, 3),
    SUBSTITUTE(INDEX(r, 4), "*", "")
  }
))

如果需要过滤掉数据源里的空行，避免返回无效空值行，可以用优化版本：

=FILTER(
  BYROW(Sheet1!A2:D, LAMBDA(r, {INDEX(r,1), SUBSTITUTE(INDEX(r,2),"/",""), INDEX(r,3), SUBSTITUTE(INDEX(r,4),"*","")})),
  Sheet1!A2:A<>""
)

旧版Google Sheets兼容写法（无LAMBDA） #

=ARRAYFORMULA(
  {
    Sheet1!A2:A,
    SUBSTITUTE(Sheet1!B2:B, "/", ""),
    Sheet1!C2:C,
    SUBSTITUTE(Sheet1!D2:D, "*", "")
  }
)

公式说明 #

• 逐列处理数据源的四个字段：A、C列保留原始值，仅对B列执行/替换为空、D列执行*替换为空的操作
• 用数组结构把四列处理后的结果拼接为完整表格，ARRAYFORMULA/BYROW会自动批量处理所有行，不需要手动下拉填充

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