Google Sheets提取数据后保留结果：删除源数据无错误且无需脚本

一、无脚本优先方案 #

1. Power Query/查询编辑器（适配Excel/Google Sheets，大数据量友好） #

这是最适合的无脚本自动化方案，通过将提取的数据转为静态快照，避免源数据删除后出现公式错误：

Excel操作：

• 选中Sheet1的目标数据区域，点击「数据」选项卡→「从表格/区域」，进入Power Query编辑器。
• 在编辑器内设置筛选条件（匹配你原FILTER公式的筛选规则），完成后点击「关闭并上载」，选择将结果加载到Sheet2的指定位置。
• 核心特性：加载后的Sheet2数据为静态快照，Sheet1删除行后，只要不手动刷新，Sheet2数据会保持原样；如需同步最新数据，点击「数据」→「全部刷新」即可。还可右键查询表→「表格属性」设置自动刷新（如打开文件时刷新、定时刷新）。

Google Sheets操作：

• 点击「数据」→「数据连接器」→「Google表格」，选择当前工作簿的Sheet1导入查询编辑器。
• 设置筛选条件后，点击「加载到」选择Sheet2的目标位置。
• 同样，加载后的数据为静态，不刷新则保留历史状态，可通过「数据」→「刷新」同步新数据，也可配置自动刷新频率。

2. 辅助列+快照式公式（手动触发，适合小批量场景） #

如果不想用Power Query，可通过辅助列配合公式实现：

• 在Sheet1新增一列（如A列），用=ROW()生成行号标识（或=UNIQUEID()生成永久唯一ID，新版Excel/Google Sheets支持），确保每行数据有唯一标记。
• 在Sheet2用FILTER提取数据时，同时包含这个唯一ID列，比如原公式=FILTER(Sheet1!B:D, Sheet1!E:E="筛选条件")改为=FILTER({Sheet1!A:A, Sheet1!B:D}, Sheet1!E:E="筛选条件")。
• 当需要保留Sheet2数据时，选中Sheet2的提取结果区域，按Ctrl+C复制，右键选择「粘贴值」，将公式转为静态数据。此方法需手动触发，适合不需要完全自动化的场景。

二、脚本自动化方案（完全自动触发） #

当无脚本方案无法满足自动保留需求时，可使用脚本实现：

Excel VBA实现： #

• 按Alt+F11打开VBA编辑器，找到Sheet1的代码窗口，粘贴以下代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 检测行删除操作
    If Target.Rows.Count = Me.Rows.Count Then
        ' 将Sheet2的公式区域转为静态值
        Sheet2.UsedRange.Value = Sheet2.UsedRange.Value
    End If
End Sub

• 保存后，当Sheet1删除行时，Sheet2的公式会自动转为静态值，保留原数据。可根据实际需求调整Sheet2.UsedRange为具体的公式区域，避免覆盖无关数据。

Google Sheets Apps Script实现： #

• 点击「扩展」→「Apps脚本」打开编辑器，粘贴以下代码：

function onEdit(e) {
    const sheet1 = e.source.getSheetByName("Sheet 1");
    const sheet2 = e.source.getSheetByName("Sheet 2");
    // 监听行删除事件
    if (e.changeType === "REMOVE_ROW") {
        // 将Sheet2数据转为静态值
        const dataRange = sheet2.getDataRange();
        dataRange.setValues(dataRange.getValues());
    }
}

• 保存脚本并授权后，Sheet1删除行时，Sheet2的公式会自动转为静态数据，无错误保留原内容。

关键提示 #

• Power Query方案无需手动干预，且支持大数据量，是优先推荐的无脚本方案；
• 迭代计算类公式（如试图让公式保留原值）易引发循环引用，不建议使用；
• 脚本方案需要启用宏（Excel）或授权（Google Sheets），但能实现完全自动化触发。

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