Excel SQL驱动表刷新无数据时如何保留非SQL列公式？

我之前也碰到过一模一样的糟心事！当SQL数据源刷新后没有返回数据时，Excel确实会把关联行的非SQL驱动列内容（包括公式）一股脑清空。这里有几个经过验证的解决方案，你可以根据自己的Excel版本和使用习惯选择：

方案1：用VBA宏自动恢复公式 #

这是最直接的办法，通过监听工作表或SQL表的刷新事件，在无数据时自动填充预设公式：

1. 按Alt + F11打开VBA编辑器
2. 在左侧工程窗口找到你的目标工作表，双击打开代码窗口
3. 粘贴以下代码（记得替换成你的实际表名、目标列和公式）：

' 当工作表激活时初始化公式
Private Sub Worksheet_Activate()
    RestoreFormulas
End Sub

' 监听SQL表刷新完成事件
Private Sub ListObject_AfterRefresh(ByVal Success As Boolean)
    RestoreFormulas
End Sub

' 核心恢复公式的子过程
Private Sub RestoreFormulas()
    Dim sqlTable As ListObject
    Dim targetCol As Range
    Dim formulaText As String
    
    ' 替换成你的SQL驱动表名称
    Set sqlTable = Me.ListObjects("SQL_Data_Table")
    ' 替换成你的非SQL驱动列（比如F列）
    Set targetCol = Me.Range("F:F")
    ' 替换成你需要保留的公式
    formulaText = "=IFERROR(VLOOKUP(A2,ReferenceData!A:B,2,FALSE),"""")"
    
    ' 如果SQL表无数据，重新填充公式
    If sqlTable.ListRows.Count = 0 Then
        ' 设置首行数据行的公式
        targetCol.Cells(2, 1).Formula = formulaText
        ' 自动填充到预设的行数（比如前100行）
        targetCol.Cells(2, 1).AutoFill Destination:=targetCol.Range("A2:A100")
    End If
End Sub

4. 保存文件为.xlsm格式（启用宏的工作簿），以后刷新SQL表时，宏会自动帮你恢复公式。

方案2：将非驱动列整合到Power Query中 #

如果你的SQL表是通过Power Query加载的，那可以直接在Power Query里添加自定义列，把Excel公式转换成M语言逻辑，这样刷新后即使无数据，列的定义也不会丢失：

1. 打开Power Query编辑器（数据选项卡 → 编辑查询）
2. 在查询编辑器中，点击「添加列」→「自定义列」
3. 输入类似的M语言公式（替换成你的实际逻辑）：

= try Table.Lookup(ReferenceData, {"ID"}, [ID], {"Value"}) otherwise null

4. 关闭并上载到Excel，以后无论SQL数据源有没有数据，这个自定义列的逻辑都会保留，不会被清空。

方案3：使用动态数组公式（仅适用于Excel 365/2021） #

如果你的Excel支持动态数组，这是最简便的方法：在非SQL驱动列的第一行（比如F2）输入动态数组公式，比如：

=IFERROR(INDEX(ReferenceData!B:B,MATCH(A:A,ReferenceData!A:A,0)),"")

按回车后，公式会自动溢出填充到所有关联行。即使SQL表刷新后无数据，这个公式本身会保留在F2单元格里，不会被Excel清空，下次有数据时会自动重新计算。

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