在Google Sheets中使用正则表达式查找并替换公式

核心原因 #

Google Sheets自带的查找替换默认匹配的是单元格显示值，而非底层的公式内容，所以直接用正则无法匹配到ArrayFormula(Source!X:Y)这类公式文本，必须针对公式本身操作。

方法一：用Google Apps Script批量处理（推荐） #

这是最高效的批量替换方式，步骤如下：

1. 打开目标表格，点击顶部菜单「扩展程序」→「Apps Script」
2. 删除默认代码，粘贴以下脚本：

function replaceArrayFormulas() {
  // 可修改为指定工作表，比如getSheetByName("Sheet1")
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // 可修改为指定范围，比如getRange("A2:D100")
  const targetRange = sheet.getDataRange();
  const formulas = targetRange.getFormulas();

  // 遍历所有单元格处理公式
  for (let row = 0; row < formulas.length; row++) {
    for (let col = 0; col < formulas[row].length; col++) {
      const originalFormula = formulas[row][col];
      if (!originalFormula) continue;

      // 正则替换：匹配ArrayFormula(Source!X:Y)格式，替换为ArrayFormula(W(Source!X:Y)Z)
      // 若需精确匹配行号1-4，可把正则改为 /ArrayFormula\((Source!\w{1,2}[1-4]\:\w{1,2}[1-4])\)/g
      const newFormula = originalFormula.replace(/ArrayFormula\((Source!\w+:\w+)\)/g, 'ArrayFormula(W($1)Z)');
      
      if (newFormula !== originalFormula) {
        targetRange.getCell(row + 1, col + 1).setFormula(newFormula);
      }
    }
  }
}

3. 点击脚本编辑器顶部的「运行」按钮，授权必要权限后，脚本会自动批量替换符合规则的公式。

方法二：用辅助列手动处理（无需代码） #

适合不想使用脚本的场景，步骤如下：

1. 插入新辅助列（比如B列），在B1输入公式=FORMULATEXT(A1)，下拉填充至所有需要处理的行，获取原单元格的公式文本。
2. 选中辅助列B，按Ctrl+H打开查找替换窗口，勾选「使用正则表达式」：
   • 查找内容：ArrayFormula\((Source!\w+:\w+)\)
   • 替换内容：ArrayFormula(W($1)Z)
3. 点击「全部替换」完成文本替换。
4. 选中辅助列B的所有内容，右键复制；再选中原数据列（比如A列），右键选择「选择性粘贴」→「公式」，将替换后的文本转为公式覆盖原单元格。
5. 删除辅助列B即可。

注意事项 #

• 操作前建议备份工作表，避免替换规则错误导致数据异常。
• 可根据实际需求调整正则表达式，比如精确匹配特定行号、列名范围。

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