如何在仅传入Range("A1")的UDF中计算移动后公式的结果？

要实现这个需求，核心是模拟单元格移动时的引用调整逻辑，通过Excel内置的公式转换工具，把原单元格的公式转换成目标位置（B1）的公式，再计算结果。以下是具体的VBA UDF实现：

Function GetMovedFormulaResult(sourceCell As Range) As Variant
    Dim originalFormula As String
    Dim convertedFormula As String
    Dim targetCell As Range
    
    ' 定义目标位置：原单元格右侧一列（即A1→B1）
    Set targetCell = sourceCell.Offset(0, 1)
    
    ' 获取原单元格的A1样式公式
    originalFormula = sourceCell.Formula
    
    ' 将原公式从sourceCell的视角，转换为targetCell视角的公式（模拟移动后的引用调整）
    convertedFormula = Application.ConvertFormula( _
        Formula:=originalFormula, _
        fromReferenceStyle:=xlA1, _
        toReferenceStyle:=xlA1, _
        toAbsolute:=xlRelative, _
        RelativeTo:=targetCell)
    
    ' 计算转换后的公式结果
    On Error Resume Next ' 处理公式错误的情况
    GetMovedFormulaResult = Evaluate(convertedFormula)
    If Err.Number <> 0 Then
        GetMovedFormulaResult = CVErr(xlErrValue)
    End If
    On Error GoTo 0
End Function

关键逻辑说明 #

• Application.ConvertFormula是核心：它能根据指定的目标单元格，自动调整公式中的相对引用，完全模拟单元格移动时Excel的引用更新规则。
• 示例中targetCell = sourceCell.Offset(0,1)表示移动到右侧相邻单元格（A1→B1），如果需要其他位置，修改Offset的参数即可。
• Evaluate函数用来计算转换后的公式结果，同时加入错误处理，避免公式报错导致UDF返回无效值。

测试验证 #

针对你给出的例子：

• 原A1公式：XLOOKUP(G$6, $C$4:$W$4, $C$21:$W$21,0)
• 转换为B1视角的公式后，会自动变为XLOOKUP(H$6, $C$4:$W$4, $C$21:$W$21,0)
• 在工作表中输入=GetMovedFormulaResult(A1)，就能得到移动到B1后的公式计算结果。

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