Excel如何批量修改公式引用以适配数据源表的格式变更？

方法1：VBA宏批量修改（精准高效） #

用宏可自动遍历所有公式，将inputs表中AD列的引用行号统一减2：

1. 打开目标工作表，按Alt+F11打开VBA编辑器
2. 右键当前工作簿 → 「插入」→「模块」
3. 粘贴以下代码到模块窗口：

Sub AdjustInputReferences()
    Dim cell As Range
    Dim formulaText As String
    Dim startPos As Integer, endPos As Integer
    Dim rowNum As String, newRowNum As Integer
    
    For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
        formulaText = cell.Formula
        startPos = InStr(formulaText, "inputs!AD")
        
        Do While startPos > 0
            endPos = startPos + 7 '定位行号起始位置
            '提取连续数字的行号
            Do While Mid(formulaText, endPos, 1) Like "[0-9]"
                endPos = endPos + 1
            Loop
            rowNum = Mid(formulaText, startPos + 7, endPos - startPos - 7)
            
            '计算新行号并替换原引用
            If IsNumeric(rowNum) Then
                newRowNum = CInt(rowNum) - 2
                formulaText = Replace(formulaText, "inputs!AD" & rowNum, "inputs!AD" & newRowNum)
            End If
            
            '查找下一个目标引用
            startPos = InStr(endPos, formulaText, "inputs!AD")
        Loop
        
        cell.Formula = formulaText
    Next cell
    MsgBox "引用调整完成！"
End Sub

4. 按F5运行宏，等待提示完成即可

方法2：查找替换+辅助列（无需编程） #

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

1. 在目标工作表插入空白辅助列（如B列）
2. 在B1单元格输入公式，提取原引用行号并减2：
   =IFERROR(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(FORMULATEXT(A1),"inputs!AD",""),")","</s><s>")&"</s></t>","//s[number(.)=.]")-2,"")
   下拉填充至所有有公式的行
3. 插入第二列辅助列（如C列），生成新公式：
   =IFERROR(REPLACE(FORMULATEXT(A1),FIND("inputs!AD",FORMULATEXT(A1))+7,LEN(B1)+2,B1),A1)
   注意：若单元格存在多个inputs!AD引用，此方法仅修改第一个，优先推荐VBA方案
4. 复制C列所有内容，选中原公式列（如A列），右键→「粘贴选项」→「值」，完成后删除辅助列

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