Excel插入命名区域行时如何保持公式相对引用不变？

Excel在执行行插入操作时，仅会自动调整公式中完全落在插入位置同侧的单元格引用，对于跨插入位置的相对引用（比如公式引用自身上方相邻单元格，插入位置刚好在公式单元格和引用源之间），不会同步偏移引用源，直接导致引用错位。你现有代码通过复制粘贴传递公式的逻辑，是在引用已经被插行操作破坏的基础上执行的，自然会持续传递错误的引用关系。

方案1：修改VBA逻辑，用R1C1公式缓存规避自动引用调整（推荐，无性能损耗，兼容所有现有公式） #

R1C1是Excel原生的相对引用格式，比如你示例中A列的累计差公式=B2-B1，对应的R1C1写法为=RC[1]-R[-1]C[1]，含义是「当前行右侧1列单元格 减去 上一行右侧1列单元格」，只要直接给目标单元格写入该R1C1公式，相对引用关系永远不会偏移，完全不受插行、复制操作影响。
你只需要调整现有代码的执行顺序，在插行前先缓存最后一行的R1C1公式、值和格式，插行后直接给目标行写入缓存的R1C1公式即可，不需要依赖复制粘贴传递公式，从根源上避开Excel自动改引用的问题。
修改后的核心代码段如下，直接替换原有插行、复制粘贴的逻辑即可：

' 插行前缓存最后一行的公式、值，避免插行破坏引用
Dim lastRowFormulas As Variant
Dim lastRowValues As Variant
lastRowFormulas = last_row.FormulaR1C1
lastRowValues = last_row.Value

' 执行原有插行逻辑
IIf(insert_entire_sheet_row, last_row.Cells(1).EntireRow, last_row) _
    .Resize(num_rows).Insert Shift:=xlShiftDown, CopyOrigin:=xlFormatFromRightOrBelow

' 批量刷格式到新插入行和新的末尾行
last_row.Copy
last_row.Offset(-num_rows).Resize(num_rows + 1).PasteSpecial Paste:=xlPasteFormats

' 给新插入的行写入和原最后一行完全一致的内容、公式
With last_row.Offset(-num_rows)
    .FormulaR1C1 = lastRowFormulas
    .Value = lastRowValues
End With

' 给新的区域末尾行写入正确公式，清空常量内容留待输入
With last_row
    .FormulaR1C1 = lastRowFormulas
    .ClearContents
    On Error Resume Next
    .SpecialCells(xlCellTypeConstants).ClearContents
    On Error GoTo Cleanup
End With

原有代码中keep_formulas相关的判断逻辑可以直接删除，该写法本身就会完整保留所有公式的正确相对引用关系。

方案2：修改公式写法，用动态引用彻底规避插行影响 #

如果不想调整VBA逻辑，可以把所有引用上方单元格的公式改为非相对引用的动态写法，比如你示例中的累计差公式，从原来的=B2-B1改为：

=B2-INDEX(B:B,ROW()-1)

该写法通过ROW()获取当前单元格的行号，永远引用当前行上一行的同列位置单元格，无论怎么插行、删行、复制公式，引用关系都不会错乱。
注意不要用INDIRECT实现相同效果，INDIRECT是易失性函数，数据量大时会严重拖慢表格计算速度，INDEX是非易失性函数，性能和普通公式一致。
你之前尝试INDIRECT无效，基本是因为没有结合ROW()动态取行号，而是写死了单元格地址导致的。

• 不要依赖Excel插行时的自动引用调整，该逻辑本身就不处理跨插入点的相邻相对引用，批量操作场景下必然出现错位
• 批量写入FormulaR1C1的效率比复制粘贴、模拟填充柄操作高10倍以上，适合数十个命名区域批量扩容的场景
• 不需要将区域转为ListObject表格，完全兼容你现有的打印格式设置

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